Question 1: SQL Query to find second highest salary of Employee
Answer : There are many ways to find second highest salary of Employee in SQL, you can either use SQL Join or Subquery to solve this problem. Here is SQL query using Subquery :
select MAX(Salary) from Employee WHERE Salary NOT IN (select MAX(Salary) from Employee );
Question 2: SQL Query to find Max Salary from each department.
Answer : You can find maximum salary for each department by grouping all records by DeptId and then using MAX() function to calculate maximum salary in each group or each department.
SELECT DeptID, MAX(Salary) FROM Employee GROUP BY DeptID.
This questions become more interesting if Interviewer will ask you to print department name instead of department id, in that case you need to join Employee table with Department using foreign key DeptID, make sure you do LEFT OUTER JOIN to include departments without any employee as well. Here is the query
SELECT DeptName, MAX(Salary) FROM Employee e LEFT JOIN Department d ON e.DeptId = d.DeptID;
Question 3: Write SQL Query to display current date.
Answer : SQL has built in function called GetDate() which returns current timestamp. This will work in Microsoft SQL Server, other vendors like Oracle and MySQL also has equivalent functions.
SELECT GetDate();
Question 4: Write an SQL Query to check whether date passed to Query is date of given format or not.
Answer : SQL has IsDate() function which is used to check passed value is date or not of specified format ,it returns 1(true) or 0(false) accordingly. Remember ISDATE() is a MSSQL function and it may not work on Oracle, MySQL or any other database but there would be something similar.
SELECT ISDATE('1/08/13') AS "MM/DD/YY";
It will return 0 because passed date is not in correct format.
Question 5: Write a SQL Query to print the name of distinct employee whose DOB is between 01/01/1960 to 31/12/1975.
Answer : This SQL query is tricky but you can use BETWEEN clause to get all records whose date fall between two dates.
SELECT DISTINCT EmpName FROM Employees WHERE DOB BETWEEN ‘01/01/1960’ AND ‘31/12/1975’;
Question 6: Write an SQL Query find number of employees according to gender whose DOB is between 01/01/1960 to 31/12/1975.
Answer :
SELECT COUNT(*), sex from Employees WHERE DOB BETWEEN '01/01/1960' AND '31/12/1975' GROUP BY sex;
Question 7: Write an SQL Query to find employee whose Salary is equal or greater than 10000.
Answer :
SELECT EmpName FROM Employees WHERE Salary>=10000;
Question 8: Write an SQL Query to find name of employee whose name Start with ‘M’
Answer :
SELECT * FROM Employees WHERE EmpName like 'M%';
Question 9: find all Employee records containing the word "Joe", regardless of whether it was stored as JOE, Joe, or joe.
Answer :
SELECT * from Employees WHERE UPPER(EmpName) like '%JOE%';
Question 10: Write a SQL Query to find year from date.
Answer : Here is how you can find Year from a Date in SQL Server 2008
SELECT YEAR(GETDATE()) as "Year";
Question 11 : Write SQL Query to find duplicate rows in a database? and then write SQL query to delete them?
Answer : You can use following query to select distinct records :
SELECT * FROM emp a WHERE rowid = (SELECT MAX(rowid) FROM EMP b WHERE a.empno=b.empno)
to Delete:
DELETE FROM emp a WHERE rowid != (SELECT MAX(rowid) FROM emp b WHERE a.empno=b.empno);
Question 12 : There is a table which contains two column Student and Marks, you need to find all the students, whose marks are greater than average marks i.e. list of above average students.
Answer : This query can be written using sub query as shown below :
SELECT student, marks from table where marks > SELECT AVG(marks) from table)
Question 13 : How do you find all employees which are also manager? .
You have given an standard employee table with an additional column mgr_id, which contains employee id of manager.
Answer : You need to know about self join to solve this problem. In Self Join, you can join twoinstances of same table to find out additional details as shown below
SELECT e.name, m.name FROM Employee e, Employee m WHERE e.mgr_id = m.emp_id;
this will show employee name and manger name in two column e.g.
name manager_name
John David
One follow-up is to modify this query to include employees which doesn't have manager. To solve that, instead of using inner join, just use left outer join, this will also include employees without managers.
Question 14 : You have a composite index of three columns, and you only provide value oftwo columns in WHERE clause of a select query? Will Index be used for this operation? For example if Index is on EmpId, EmpFirstName and EmpSecondName and you write query like
SELECT * FROM Employee WHERE EmpId=2 and EmpFirstName='Radhe'
If the given two columns are secondary index column then index will not invoke, but if the given 2 columns contain primary index(first col while creating index) then index will invoke. In this case Index will be used because EmpId and EmpFirstName are primary columns.
UNION and UNION ALL Example in SQL Server
Let's see one simple example of UNION and UNION ALL, this will not only show you how they work but also where you can use it. This example is from my sample database and following screenshot is from SQL Server Management Studio 2014. We have two tables, Employee andCustomer. In order to use UNION and UNION ALL, I have kept same persons as employee and customer, so you will see same id on emp_id and customer_id, and same name as well. If you look at result of first two select queries, you will see that first query returns two rows and second query returns three rows, where two rows have exactly same data as first query. Key things to note is that column names are different in both result set, first one has emp_id and emp_name, while second data set has customer_id and customer_name, but most important both dataset has only two columns. This is must in order to combine them using UNION and UNION ALL keywords. Third query is an example of how to use UNION clause in SQL, you can see that combined result has just three columns, all are unique. Duplicate columns from second result set was not included. This is more like how you do UNION in Set theory, where final result contains data from both set. Fourth query is how you should use UNION ALL, it contains five rows, two from first query and three from second query. It has not removed duplicate rows from second query, that's why you see Ken andBob repeating twice. This example teaches us core concept that UNION doesn't depend upon column name but the data. You can combine result of as many queries as possible until number of columns in all of them is same and data is from same set.
Difference between UNION and UNION ALL command in SQL
Now we know how union and union all works and has some background by following above examples, let's summarise the similarities and difference between them for quick revision :1) Both UNION and UNION ALL are used to combine result of two separate SQL query, it could be on same table or different table but data should be same. E.g. if product_id is used in two table e.g. Product and Order, then two sql queries which pulls product_id from these two table can be combined using UNION or UNION ALL.
2) Key difference between UNION and UNION ALL is that former will remove duplicates but later will keep them. In another words, UNION is equal to running distinct on output of UNION ALL. For example, if product_id 10 is returned by both of SQL query then it will only appear once if you use UNION and appear twice if you use UNION ALL.
3) Due to above difference query execution time of UNION ALL is smaller than UNION, which means former runs faster than later. So if you want faster output and don't care of duplicates useUNION ALL.
4) Keep in mind that benefits gained by not removing duplicates can be easily wiped out by transferring more data over a poor bandwidth network connection. That's why in practice some time UNION ALL appear slower than UNION because it return lot of data with duplicates which require more time to travel from database server to client machine. So evaluate performance ofUNION and UNION ALL case by case.
5) Another worth noting thing while using UNION and UNION ALL is that all queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists. For example if result of query 1 has three column and result of query 2 has two column then you cannot combine them using UNION command.
That's all on difference between UNION and UNION ALL command in SQL. It's one of the useful command to combine result of two SELECT queries when they contain same data. There are many practical scenarios where UNION is very useful, for example when you need to create list out of different tables containing data from same set. Main difference between UNION and UNION ALL is about duplicates, former removes it while later keeps it, other difference between them on performance and networking bandwidth usage can be easily derived by knowing this difference. Also keep in mind that it is well supported big three database e.g. MySQL, Oracle and SQL Server.
No comments:
Post a Comment