Important Queries in SQL Part3 #DOTC_Mdb

Important Queries in SQL Part3.

In my previous blogs I discussed about several sql queries which commonly used in SQL and today also I will discuss about some important queries in SQL . Before that I suggest you to go through my SQL and PL/SQL blogs in archive May and June folder for your understanding.  Also you can see my below previous blog about Queries in SQL.

 https://dheeraj60.blogspot.com/2020/07/important-queries-in-sql-part1-dotcmdb.html

https://dheeraj60.blogspot.com/2020/07/important-sql-queries-part-2-dotcmdb.html

1. Self Join Example :  Suppose I have an employee table and need to fetch Employee_name and his Manager_name from one table only. As we know self Join is used to join the table itself . So finding this we need to write below query.

Select a.emp_name as Employee,b.emp_name as Manager From

Employee a,Employee b where a.emp_id=b.Manager_id;

This query is important as we join the table itself and get the requirement.

Below is the output of above query in image :

 Output of Employee name as well as Manager name is shown in output.

2.  Write a Query to fetch employee data which exist in employee_salary table .

SELECT * FROM Employee a WHERE

EXISTS

(SELECT * FROM Employee_Salary b WHERE a.emp_Id = b.Emp_Id);

 

Here we use subquery to get the required data and use EXISTS keyword. Please refer my Subquery and co-related subquery blog for your reference.

Below is the output of above query.

3. Find out the Duplicate rows in a table without using rowid and distinct in SQL.

 

For this we can use aggregate function with group by and Having clause in SQL query .

 

SELECT emp_id, emp_name, Salary,

 COUNT(*) FROM Employee_Salary

 GROUP BY Emp_id, emp_name, Salary

 HAVING COUNT(*) > 1;

Below is the output :

Here we will get no result as in my table there is no duplicate records.

You can check with inserting duplicate records in table . 

4. How to find current date in database in SQL :

In oracle we use sysdate . Below is the query

select sysdate from dual;

In My SQL we can use below query to see the current date :

Select now();

Below is the output of above query :

Note : This functions is very important to find the current date in SQL query and can be used anywhere inside the long query as well.

5. How to get the values from employee table where name starts with D.

 

For this we  need to use the like operator to fetch the above requirement from employee table . Below is the Query

Select * from employee

 where emp_name

Like 'D%';

Below is the output of above query :

 

6.  SQL query to print the emp_name from employee table after removing white spaces from the right side.

 

In SQL we can use RTRIM function to retrieve the vaue . Below is the query .

Select RTRIM(emp_name) from employee;

Same we can apply for removing white spaces from left side by using LTRIM . Below is the Query.

Select LTRIM(emp_name) from employee;

Below is the output of above query :

 

 

7 . Create one table by taking data from two table with common records no need to declare data type as it will directly take it from both the table .

 

For this we simply need to use equi/inner Join and through create statement we can get this requirement.

Below is the query :

Create table employee1

As
select a.emp_name, a.salary , b.location

From employee a , department b

Where a.emp_id=b.emp_id;

 

Here new table is created as employee1 without declaring any data types and also we can get common records from both the table.

Below is the output for your reference.

Now we can see the data output also in new table employee1 . 

Hope you are able to understand this queries which is commonly used and asked in interview. Please try to practice this query and also see my previous blogs for queries and let me know if you have any doubt.

Please go through this blog carefully as in next blog I will come up with new topics related to complex queries in SQL.

Thanks.