LIST OF ASSIGNMENTS TO BE DONE/WRITTEN ON THE PRACTICAL FILE
SUBJECT: INFORMATION TECHNOLOGY(802)
SL.N QUESTIONS ON STRUCTURED QUERY LANGUAGE
O
Consider the following tables and write SQL Codes based on the tables:
Table: Employee
EMPNO NAME DOJ DEPT DESIG
E019 Ajay Kumar Singh 01-03-2009 Sales Executive
E183 Binod Shriwas 03-04-2011 Sales Manager
E656 Mohan Soni 11-05-1999 Marketing Manager
E763 Dilip Sharma 07-11-2006 Marketing Advisor
E263 Shreya Basu 08-12-2003 Technical Assistant
E711 Ronita Chowdhury 12-01-2013 Sales Executive
E561 Pallabi Sharma 03-12-2008 Finance Officer
E113 Vikram Kumar 11-11-2005 Technical Officer
Table: Pay
EMPNO MONTH BASIC TOTAL
E019 November 6900 8200
E019 December 6900 8400
E763 October 5200 6500
E711 September 8000 9600
E561 October 8000 10000
E711 November 16000 28000
E561 December 16000 29500
E263 August 12500 15600
1. Write SQL Code to display the details of all Marketing Advisor.
Ans:
2. Write SQL Code to display the name of the employees in the descending order
of their DOJ.
Ans:
3. Write SQL Code to display the sum of total in the month of November.
Ans:
4. Write SQL Code to display the Dept and Desig of those employees whose name
starts with ‘P’.
Ans:
5. Write SQL Code to display Desig of those employees whose name is not ending
with ‘a’.
Ans:
6. Write SQL Code to display the total of the employees in the ascending order of
basic in the month of October.
Ans:
7. Write SQL Code to count the number of unique department.
Ans:
8. Write SQL Code to display the name of the employees in Sales department.
Ans:
9. Write SQL Code to display the average total of those whose basic is above 8000.
Ans:
10. Write SQL Code to display the name and total number of employees department
wise.
Ans:
11. Write SQL Code to display the name and basic of the employees with their
matching EMPNO in both the tables.
Ans:
12. Write SQL Code to display the name of the employees who got the maximum.
total.
Ans:
13. Write SQL Code to display the basic of all Executive, Manager and Advisor.
Ans:
14. Write SQL Code to display the name of all Officers in Upper Case.
Ans:
15. Write SQL Code to display the sum of basic of all employees except E561.
Ans:
16. Write SQL Code to display the name and desig of the employees joined in the
year 2013.
Ans:
17. Write SQL Code to display the name of the employees whose dept is entered.
Ans:
18. Write SQL Code to display the name of the employees whose total is not
entered.
Ans:
Write SQL Code to count the number of Managers.
19. Ans:
20. Write SQL Code to increase the total of all employees in Finance department by
500.
Ans:
