SQL Assignment

SQL Assignment

1. Create the table PROGRAMMER with the given information using SQL CREATE TABLE commands:

Attribute Description/Data type/Constraint

EmpNo Employee’s Unique ID. Max 5 characters should be

numeric

ProjId Project in which programmer participates. Max 3

characters should be numeric

LastName Surname of employee. Max 30 characters Required.

FirstName Employee’s first name. Max 30 characters

HireDate Date on which employee was hired. Date data type

Language Programming Language used by programmer. Max 15

characters

TaskNo Number of the rask associated with the project.

Numeric column, max 2 digits

Privilege Type of privilege given to programmer. Max 25

characters

2. Insert the following data into the PROGRAMMER table

EmpNo LastName FirstName Hiredate ProjId Language TaskNo Privilege

201 Gupta Saurav 1/1/95 NPR VB 52 Secret
390 Ghosh Pinky 1/5/93 KCW JAVA 11 TopSecret
789 Agarwal Praveen 8/3/98 RNC VB 11 Secret
134 Chaudhury Supriyo 7/15/95 TIPPS C++ 52 Secret
896 Jha Ranjit 6/15/97 KCW JAVA 10 TopSecret
345 John Peter 11/15/99 TIPPS JAVA 52

563 Anderson Andy 08/15/94 NITTS C++ 89 Confidential

 3. Write SQL queries to:

a. Saurav Gupta is assigned a different project with id NITTS and he would work with C++ now. Update
this change in the PROGRAMMER table.

b. Supriyo Chaudhury has resigned his job. Incorporate this in the table PROGRAMMER.

c. The column TaskNo in the PROGRAMMER table is no longer needed. Delete the column.

4. Create table Department

Attribute Description/Data type/Constraint

Name
DeptNo Department number is Unique ID.

Dname Department name of a particular department, Dname should not be null.

Loc Location of the department, loc should not be null.

5. In DEPARTMENT table, increase the field width of DNAME from 20 to 50

6. Insert the following data into the Department table

DEPTNO DNAME LOC

10 ACCOUNTS NEWYORK
20 MARKETING CHICAGO
30 SALES ATLANTA
40 RESEARCH OHIO

7. Create table Employee

Attribute Description/Data type/Constraint

Name
EmpNo Employee number is Primary Key.
 Ename Name of the employee, Cannot be null
Job Cannot be null, The job of the employee can be
MANAGER, CLERK, PRESIDENT
Sal Cannot be null
Hiredate Cannot be null
Deptno Should reference Department Table, Deptno

8. In EMPLOYEE table add a new attribute MGR(Manager ID) consisting 4 Characters.

9. Insert the following DATA into the Employee Table

EMPNO ENAME JOB SAL HIREDATE DEPTNO

7001 JAMES CLERK 3000 6/5/2005 10
7002 MASON PRESIDENT 10000 6/6/2005 20
7003 CLARK MANAGER 5000 6/5/2004 20
7004 JOHN MANAGER 6000 6/8/2005 10
7005 BLAKE CLERK 3500 6/9/2005 30

10. Create table Grade

Attribute Description/Data
Name type/Constraint
GradeNo Grade number is
Primary Key.
Hi_sal Cannot be null
Lo_sal Cannot be null

11.Drop the column Lo_sal from Grade Table

12.Add column Low_sal in Grade table

13.Insert the following data into the table

GRADE_NO HI_SAL LO_SAL

1 2000 500
2 3500 2100
3 6000 3600
4 15000 6100
14.Create table EMPLOYEE_BACK from employee table

15.Increase the salary of JAMES from 3000 to 3500

16.Increase the salary of all MANAGER by 1000

17.Decrease the salary of DEPTNO 10 by 100

18.Add a new field COMM in EMPLOYEE table

19.Initialize the value of COMM to zero in EMPLOYEE table.

20.Remove the employees who have joined before 6-Jun-2005

21.Remove employees whose salary is less than 3000

22.List all employees who are working in department 10.

23.List all employees of department 10 and are MANAGER

24.List all employees whose salary is between 3000 and 5000

25. List all employees who have joined after 10th July 2005

26.List all employees who are MANAGER or PRESIDENT

27.List all employees who are in deptno 10 or 20 and who are MANAGERS

28.Update the commission of employees in deptno 10 to 500.

29.List all employees whose commission is null.

30.List the employees who are not a PRESIDENT or MANAGER

31.List all employees whose name begin with J

32.List all employees whose name consists of A

33.List the employee sal, comm. and bonus (Bonus is sal+comm.)

34.Display the salary of employees of MANAGER increased by 10%. The output should display salary and
increased salary.

35.Update the salary of MANAGER by 10%.

36.Display the employees in the desscending order of names

37.Display the employees in the ascending order of deptno, Job

38.Display all the employee names with the first letter in capitals and all the other characters in lower case

39. Display all the employee names in lower case

40.Display the employee name and the position of letter A in each name
41.Extract the last 3 characters in employee name and display them.

42.Display the employee name and the length of the name

43.Display the current system date and time

44.Display the employee name and the date when each employee completes 5 years in the company.

45.Display the last day of the month for the current system date

46.Display the last day of the month for all the hiredates in EMPLOYEE table

47. Display the employee name, hiredate and the total months of experience as on 8th June 2008

48.Display the employee name, sal and comm. for all employees. Employees having commission as null
should be displayed as 99

49.Drop table employee EMPLOYEE_BACK

50.Display the number of employees in department 10

51.Display the number of employees in each department

52.Display the number of employees in each department job wise

53.Display the total number of employees in the table

54.Display the employee earning the highest salary

55.Display the employee earning the highest commission

56.Display the employee earning the lowest salary

57.Display the average salary for each department

58.Display distinct jobs in the table

59.Display the deptno where the number of employees is greater than 3.

60.Display the total salary department wise

61. Display the employee name, job and rank. If the job is PRESIDENT then rank is 1, If the job is MANAGER
then rank is 2 , if the job is CLERK then the rank is 3.

62. Display the job and total salary for each job having number of employees greater than 5

63. There is a proposed deduction of 5000 from every employee’s salary. Check for the balance salary and
display the appropriate message. If the deduction causes value to be less than zero display “Insufficient for
Deduction”, if greater than zero “Can be Deducted”

64.Create a table EMPLOYEE_DUP from EMPLOYEE. The new table should only have the structure and should
not have any data values.

65.List the employees who are in the same department as that of CLARK

66.List the employees who drawing the same salary as that of BLAKE

67.List the employees whose salary is greater than the average salary
68.List the employees who are located in NEWYORK or CHICAGO

69. List the employees whose salary is greater than the salary of BLAKE or CLARK.

70. Increase the salary of employees by 10% , who are located in CHICAGO

71.List the employees whose salary is greater than the average salary of his own department.

72. List the employees who are not in the same department as that of BLAKE or CLARK

73. Display the department number, name and location for the department for which there exists employees

74. Create a view emp_vw consisting of ename, sal, job of employees in department 10

75.Create a view emp_vw1 consisting of employee names, sal and job of employees located in CHICAGO

76.Create a view emp_dept consisting of employee names, job, sal , department name and location