61.Write SQL Query to find duplicate rows in a database? and then write SQL query to delete them?
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)
DELETE FROM emp a WHERE rowid != (SELECT MAX(rowid) FROM emp b WHERE a.empno=b.empno);
62. 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.
This query can be written using sub query as shown below :
SELECT student, marks from table where marks > SELECT AVG(marks) from table)
63.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.
You need to know about self join to solve this problem. In Self Join, you can join two instances 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.
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.
64.You have a composite index of three columns, and you only provide value of two 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.
65.You have an Employee table with id, name, salary, manager_id and dept_id and you have a Deparatment table with dept_id and dept_name. Write SQL queries to
1) Print name of Employee and their Manager’s name
2) Print All deparatment and number of employees on it
3) Print all employees who has higher salary than average salary of their department.
1) SELECT e1.name, e2.name as manager FROM Employee e1 JOIN Employee e2 WHERE e1.manager_id = e2.id
2)SELECT d.dept_name, count(*) as NumOfEmploye FROM Depatment d LEFT JOIN Employee e ON d.dept_id = e.dept_id
3) SELECT emp_name, salary FROM Employee e1 WHERE salary >= (SELECT AVG(salary) FROM Employee e2 where e1.department = e2.department).