1.
Write SQL commands for (i) to (vi) on the basis of relations given below :
Table : BOOK
Book_id Book_name Author_name Publisher Price Type Qty
K0001 Let us C Sanjay mukharjee EPB 450 Comp 15
P0001 Genuine J. mukhi FIRST. PUBL 755 Fiction 24
M0001 Mastering C++ Kanetker EPB 165 Comp 60
N0002 VC++ Adv [Link] TDH 250 Comp 45
K0002 Prog. Python Sanjeev FIRST. PUBL 350 fiction 30
Table : ISSUED
Book_id Qyt_issued
L02 13
L04 5
L05 21
1. To show the books of FIRST PUBL publishers written by P. purohit.
2. To display cost of all the books published for FIRST PUBL.
3 Depreciate the price of all books of EPB publishers by 5%
4. To display the book_name, price of the books more than 13 copies of which have been issued.
5. To show total cost of books of each type.
6. To show the details of the costliest book.(use MAX)
2. write SQL commands for (i) to (vi) and write output for (vi) on the basis of PRODUCTS relation given
below
Table : PRODUCTS
PCODE PNAME COMPANY PRICE STOCK MANUFACTURER WARRANTY
P001 TV BPL 10000 200 12/JUNE/18 3
P002 TV SONY 12000 150 23/MAR/17 4
P003 PC LENOVO 39000 100 09/APR/18 2
P004 PC BPL 38000 120 20/JUNE/19 2
P005 HANDYCAM SONY 18000 250 23/MAR/17 3
1) To show details of all PC’s with stock more than 110.
2) To list the company which gives warranty of more than 2 years.
3) To find stock value of BPL company where stock value is sum of the price and stock.
4) To show the details of company BPL , SONY.
5) Give the output of the following statements:
6) Display the list of all companies product price by increasing 500rs.
a. Select COUNT(distinct company) from PRODUCT
b. Select MAX(price) from PRODUCT where warranty <=3
Table – Worker
WORKER_ID FIRST_NAM LAST_NAME SALARY JOINING_DATE DEPARTMEN
E T
001 Monika Arora 100000 2014-02-20 HR
09:00:00
002 Niharika Verma 80000 2014-06-11 Admin
09:00:00
003 Vishal Singhal 300000 2014-02-20 HR
09:00:00
004 Amitabh Singh 500000 2014-02-20 Admin
09:00:00
005 Vivek Bhati 500000 2014-06-11 Admin
09:00:00
006 Vipul Diwan 200000 2014-06-11 Account
09:00:00
007 Satish Kumar 75000 2014-01-20 Account
09:00:00
008 Geetika Chauhan 90000 2014-04-11 Admin
09:00:00
Table – Bonus
WORKER_REF BONUS_DATE BONUS_AMO
_ID UNT
1 2016-02-20 00:00:00 5000
2 2016-06-11 00:00:00 3000
3 2016-02-20 00:00:00 4000
1 2016-02-20 00:00:00 4500
2 2016-06-11 00:00:00 3500
Table – Title
WORKER_REF WORKER_TITLE AFFECTED_FROM
_ID
1 Manager 2016-02-20 00:00:00
2 Executive 2016-06-11 00:00:00
8 Executive 2016-06-11 00:00:00
5 Manager 2016-06-11 00:00:00
4 Asst. Manager 2016-06-11 00:00:00
7 Executive 2016-06-11 00:00:00
6 Lead 2016-06-11 00:00:00
3 Lead 2016-06-11 00:00:00
Q-1. Write an SQL query to fetch “FIRST_NAME” from Worker table using
the alias name as <WORKER_NAME>.
Q-2. Write an SQL query to fetch “FIRST_NAME” from Worker table in
upper case.
Q-3. Write an SQL query to fetch unique values of DEPARTMENT from
Worker table.
Q-8. Write an SQL query that fetches the unique values of DEPARTMENT
from Worker table and prints its length.
Q-11. Write an SQL query to print all Worker details from the Worker table
order by FIRST_NAME Ascending.
Q-12. Write an SQL query to print all Worker details from the Worker table
order by FIRST_NAME Ascending and DEPARTMENT Descending. (order
by)
Q-13. Write an SQL query to print details for Workers with the first name
as “Vipul” and “Satish” from Worker table.(in)
Q-14. Write an SQL query to print details of workers excluding first
names, “Vipul” and “Satish” from Worker table. (not in)
Q-15. Write an SQL query to print details of Workers with DEPARTMENT
name as “Admin”.
Ans.
The required query is:
Select * from Worker where DEPARTMENT like 'Admin%';
Q-16. Write an SQL query to print details of the Workers whose
FIRST_NAME contains ‘a’.
Ans.
The required query is:
Select * from Worker where FIRST_NAME like '%a%';
Q-17. Write an SQL query to print details of the Workers whose
FIRST_NAME ends with ‘a’.
Ans.
The required query is:
Select * from Worker where FIRST_NAME like '%a';
Q-18. Write an SQL query to print details of the Workers whose
FIRST_NAME ends with ‘h’ and contains six alphabets.
Ans.
The required query is:
Select * from Worker where FIRST_NAME like '_____h';
Q-19. Write an SQL query to print details of the Workers whose SALARY
lies between 100000 and 500000.
Ans.
The required query is:
Select * from Worker where SALARY between 100000 and 500000;
Q-20. Write an SQL query to print details of the Workers who have joined
in Feb’2014.
Ans.
The required query is:
Select * from Worker where year(JOINING_DATE) = 2014 and
month(JOINING_DATE) = 2;
Q-21. Write an SQL query to fetch the count of employees working in the
department ‘Admin’.
Ans.
The required query is:
SELECT COUNT(*) FROM worker WHERE DEPARTMENT = 'Admin';
Q-22. Write an SQL query to fetch worker names with salaries >= 50000
and <= 100000.
Ans.
The required query is:
SELECT CONCAT(FIRST_NAME, ' ', LAST_NAME) As Worker_Name,
Salary
FROM worker
WHERE WORKER_ID IN
(SELECT WORKER_ID FROM worker
WHERE Salary BETWEEN 50000 AND 100000);
Q-23. Write an SQL query to fetch the no. of workers for each department
in the descending order.
Ans.
The required query is:
SELECT DEPARTMENT, count(WORKER_ID) No_Of_Workers
FROM worker
GROUP BY DEPARTMENT
ORDER BY No_Of_Workers DESC;
Q-24. Write an SQL query to print details of the Workers who are also
Managers.
Ans.
The required query is:
SELECT DISTINCT W.FIRST_NAME, T.WORKER_TITLE
FROM Worker W
INNER JOIN Title T
ON W.WORKER_ID = T.WORKER_REF_ID
AND T.WORKER_TITLE in ('Manager');
Q-25. Write an SQL query to fetch duplicate records having matching
data in some fields of a table.
Ans.
The required query is:
SELECT WORKER_TITLE, AFFECTED_FROM, COUNT(*)
FROM Title
GROUP BY WORKER_TITLE, AFFECTED_FROM
HAVING COUNT(*) > 1;
Q-26. Write an SQL query to show only odd rows from a table.
Ans.
The required query is:
SELECT * FROM Worker WHERE MOD (WORKER_ID, 2) <> 0;
Q-27. Write an SQL query to show only even rows from a table.
Ans.
The required query is:
SELECT * FROM Worker WHERE MOD (WORKER_ID, 2) = 0;
Q-28. Write an SQL query to clone a new table from another table.
Ans.
The general query to clone a table with data is:
SELECT * INTO WorkerClone FROM Worker;
The general way to clone a table without information is:
SELECT * INTO WorkerClone FROM Worker WHERE 1 = 0;
An alternate way to clone a table (for MySQL) without is:
CREATE TABLE WorkerClone LIKE Worker;
Q-29. Write an SQL query to fetch intersecting records of two tables.
Ans.
The required query is:
(SELECT * FROM Worker)
INTERSECT
(SELECT * FROM WorkerClone);
Q-30. Write an SQL query to show records from one table that another
table does not have.
Ans.
The required query is:
SELECT * FROM Worker
MINUS
SELECT * FROM Title;
Q-31. Write an SQL query to show the current date and time.
Ans.
Following MySQL query returns the current date:
SELECT CURDATE();
Following MySQL query returns the current date and time:
SELECT NOW();
Following SQL Server query returns the current date and time:
SELECT getdate();
Following Oracle query returns the current date and time:
SELECT SYSDATE FROM DUAL;
Q-32. Write an SQL query to show the top n (say 10) records of a table.
Ans.
Following MySQL query will return the top n records using the LIMIT method:
SELECT * FROM Worker ORDER BY Salary DESC LIMIT 10;
Following SQL Server query will return the top n records using the TOP command:
SELECT TOP 10 * FROM Worker ORDER BY Salary DESC;
Following Oracle query will return the top n records with the help of ROWNUM:
SELECT * FROM (SELECT * FROM Worker ORDER BY Salary DESC)
WHERE ROWNUM <= 10;
Q-33. Write an SQL query to determine the nth (say n=5) highest salary
from a table.
Ans.
The following MySQL query returns the nth highest salary:
SELECT Salary FROM Worker ORDER BY Salary DESC LIMIT n-1,1;
The following SQL Server query returns the nth highest salary:
SELECT TOP 1 Salary
FROM (
SELECT DISTINCT TOP n Salary
FROM Worker
ORDER BY Salary DESC
ORDER BY Salary ASC;
Q-34. Write an SQL query to determine the 5th highest salary without
using TOP or limit method.
Ans.
The following query is using the correlated subquery to return the 5th highest salary:
SELECT Salary
FROM Worker W1
WHERE 4 = (
SELECT COUNT( DISTINCT ( [Link] ) )
FROM Worker W2
WHERE [Link] >= [Link]
);
Use the following generic method to find nth highest salary without using TOP or limit.
SELECT Salary
FROM Worker W1
WHERE n-1 = (
SELECT COUNT( DISTINCT ( [Link] ) )
FROM Worker W2
WHERE [Link] >= [Link]
);
Q-35. Write an SQL query to fetch the list of employees with the same
salary.
Ans.
The required query is:
Select distinct W.WORKER_ID, W.FIRST_NAME, [Link]
from Worker W, Worker W1
where [Link] = [Link]
and W.WORKER_ID != W1.WORKER_ID;
Q-36. Write an SQL query to show the second highest salary from a table.
Ans.
The required query is:
Select max(Salary) from Worker
where Salary not in (Select max(Salary) from Worker);
Q-37. Write an SQL query to show one row twice in results from a table.
Ans.
The required query is:
select FIRST_NAME, DEPARTMENT from worker W where
[Link]='HR'
union all
select FIRST_NAME, DEPARTMENT from Worker W1 where
[Link]='HR';
Q-38. Write an SQL query to fetch intersecting records of two tables.
Ans.
The required query is:
(SELECT * FROM Worker)
INTERSECT
(SELECT * FROM WorkerClone);
Q-39. Write an SQL query to fetch the first 50% records from a table.
Ans.
The required query is:
SELECT *
FROM WORKER
WHERE WORKER_ID <= (SELECT count(WORKER_ID)/2 from Worker);
Q-40. Write an SQL query to fetch the departments that have less than
five people in it.
Ans.
The required query is:
SELECT DEPARTMENT, COUNT(WORKER_ID) as 'Number of Workers' FROM
Worker GROUP BY DEPARTMENT HAVING COUNT(WORKER_ID) < 5;
Q-41. Write an SQL query to show all departments along with the number
of people in there.
Ans.
The following query returns the expected result:
SELECT DEPARTMENT, COUNT(DEPARTMENT) as 'Number of Workers'
FROM Worker GROUP BY DEPARTMENT;
Q-42. Write an SQL query to show the last record from a table.
Ans.
The following query will return the last record from the Worker table:
Select * from Worker where WORKER_ID = (SELECT max(WORKER_ID)
from Worker);
Q-43. Write an SQL query to fetch the first row of a table.
Ans.
The required query is:
Select * from Worker where WORKER_ID = (SELECT min(WORKER_ID)
from Worker);
Q-44. Write an SQL query to fetch the last five records from a table.
Ans.
The required query is:
SELECT * FROM Worker WHERE WORKER_ID <=5
UNION
SELECT * FROM (SELECT * FROM Worker W order by W.WORKER_ID
DESC) AS W1 WHERE W1.WORKER_ID <=5;
Q-45. Write an SQL query to print the name of employees having the
highest salary in each department.
Ans.
The required query is:
SELECT [Link],t.FIRST_NAME,[Link] from(SELECT
max(Salary) as TotalSalary,DEPARTMENT from Worker group by
DEPARTMENT) as TempNew
Inner Join Worker t on [Link]=[Link]
and [Link]=[Link];
Q-46. Write an SQL query to fetch three max salaries from a table.
Ans.
The required query is:
SELECT distinct Salary from worker a WHERE 3 >= (SELECT
count(distinct Salary) from worker b WHERE [Link] <=
[Link]) order by [Link] desc;
Q-47. Write an SQL query to fetch three min salaries from a table.
Ans.
The required query is:
SELECT distinct Salary from worker a WHERE 3 >= (SELECT
count(distinct Salary) from worker b WHERE [Link] >=
[Link]) order by [Link] desc;
Q-48. Write an SQL query to fetch nth max salaries from a table.
Ans.
The required query is:
SELECT distinct Salary from worker a WHERE n >= (SELECT
count(distinct Salary) from worker b WHERE [Link] <=
[Link]) order by [Link] desc;
Q-49. Write an SQL query to fetch departments along with the total
salaries paid for each of them.
Ans.
The required query is:
SELECT DEPARTMENT, sum(Salary) from worker group by
DEPARTMENT;
Q-50. Write an SQL query to fetch the names of workers who earn the
highest salary.
Ans.
The required query is:
SELECT FIRST_NAME, SALARY from Worker WHERE SALARY=(SELECT
max(SALARY) from Wo