SQL Server

Useful T-SQL Queries.

1.  2 tables
Employee      Phone

empid         empid

empname       phnumber   

salary

mgrid

2. Select all employees who doesn't have phone?

SELECT empname

FROM Employee

WHERE (empid NOT IN

(SELECT DISTINCT empid

FROM phone))

3. Select the employee names who is having more than one phone numbers.

SELECT empname

FROM employee

WHERE (empid IN

(SELECT empid

FROM phone

GROUP BY empid

HAVING COUNT(empid) > 1))

4. Select the details of 3 max salaried employees from employee table.

SELECT TOP 3 empid, salary

FROM employee

ORDER BY salary DESC

5.

SELECT empname

FROM employee

Display all managers from the table. (manager id is same as emp id)

2

2

WHERE (empid IN

(SELECT DISTINCT mgrid

FROM employee))

6.

Write a Select statement to list the Employee Name, Manager Name under a particular manager?

SELECT e1.empname AS EmpName, e2.empname AS ManagerName

FROM Employee e1 INNER JOIN

Employee e2 ON e1.mgrid = e2.empid

ORDER BY e2.mgrid

7.

emp fields are - empid, name

Ph fields are - empid, ph (office, mobile, home). Select all employees who doesn't have any ph nos.

2 tables emp and phone.

SELECT *

FROM employee LEFT OUTER JOIN

phone ON employee.empid = phone.empid

WHERE (phone.office IS NULL OR phone.office = ' ')

AND (phone.mobile IS NULL OR phone.mobile = ' ')

AND (phone.home IS NULL OR phone.home = ' ')

8. Find employee who is living in more than one city.

Two Tables:
Emp              City

Empid           Empid

empName

Salary

                       City

9.

FROM employee

WHERE (empid IN

(SELECT empid

FROM city

GROUP BY empid

HAVING COUNT(empid) > 1))

SELECT empname, fname, lname

10.

Find all employees who is living in the same city. (table is same as above)

11. There is a table named MovieTable with three columns - moviename, person and role. Write a query which

gets the movie details where Mr. Amitabh and Mr. Vinod acted and their role is actor.

SELECT DISTINCT m1.moviename

FROM MovieTable m1 INNER JOIN

MovieTable m2 ON m1.moviename = m2.moviename

WHERE (m1.person = 'amitabh' AND m2.person = 'vinod' OR

m2.person = 'amitabh' AND m1.person = 'vinod') AND (m1.role = 'actor') AND

(m2.role = 'actor')

ORDER BY m1.moviename

12. There are two employee tables named emp1 and emp2. Both contains same structure (salary details). But

Emp2 salary details are incorrect and emp1 salary details are correct. So, write a query which corrects salary

details of the table emp2

update a set a.sal=b.sal from emp1 a, emp2 b where a.empid=b.empid

13. Given a Table named “Students” which contains studentid, subjectid and marks. Where there are 10 subjects

and 50 students. Write a Query to find out the Maximum marks obtained in each subject.

14. In this same tables now write a SQL Query to get the studentid also to combine with previous results.

15. Three tables – student , course, marks – how do go at finding name of the students who got max marks in the

diff courses.

SELECT student.name, course.name AS coursename, marks.sid, marks.mark

FROM marks INNER JOIN

student ON marks.sid = student.sid INNER JOIN

course ON marks.cid = course.cid

WHERE (marks.mark =

(SELECT MAX(Mark)

FROM Marks MaxMark

WHERE MaxMark.cID = Marks.cID))

16. There is a table day_temp which has three columns dayid, day and temperature. How do I write a query to get

the difference of temperature among each other for seven days of a week?

SELECT a.dayid, a.dday, a.tempe, a.tempe - b.tempe AS Difference

FROM day_temp a INNER JOIN

day_temp b ON a.dayid = b.dayid + 1

OR

Select a.day, a.degree-b.degree from temperature a, temperature b where

a.id=b.id+1

17. There is a table which contains the names like this. a1, a2, a3, a3, a4, a1, a1, a2 and their salaries. Write a

query to get grand total salary, and total salaries of individual employees in one query.

SELECT empid, SUM(salary) AS salary

FROM employee

GROUP BY empid WITH ROLLUP

ORDER BY empid

18. How to know how many tables contains empno as a column in a database?

SELECT COUNT(*) AS Counter

FROM syscolumns

WHERE (name = 'empno')

19. Find duplicate rows in a table? OR I have a table with one column which has many records which are

not distinct. I need to find the distinct values from that column and number of times it’s repeated.SELECT sid, mark, COUNT(*) AS Counter

FROM marks

GROUP BY sid, mark

HAVING (COUNT(*) > 1)

20. How to delete the rows which are duplicate (don’t delete both duplicate records).

SET ROWCOUNT 1

DELETE yourtable

FROM yourtable a

WHERE (SELECT COUNT(*) FROM yourtable b WHERE b.name1 = a.name1 AND b.age1 =

a.age1) > 1

WHILE @@rowcount > 0

DELETE yourtable

FROM yourtable a

WHERE (SELECT COUNT(*) FROM yourtable b WHERE b.name1 = a.name1 AND b.age1 =

a.age1) > 1

SET ROWCOUNT 0

1. 2 tables

SELECT fname

FROM employee

WHERE (empid IN

(SELECT empid

FROM city a

WHERE city IN

(SELECT city

FROM city b

GROUP BY city

HAVING COUNT(city) > 1)))