Select and update SQL query exercises with answers

  Given Tables are 

    

  Instructor table

Course table

Department table

Teaches table

SQL queries Create Table, Select, Insert, Update, Rename a table, Modifying existing column, ALTER TABLE, DELETE, DROP TABLE, TRUNCATE Syntax

1) Display the course_ids, titles, and credits of course that are offered in any of

the departments namely: Physics, Music, Finance, and Biology.

SELECT SUB_CODE, TITLE, CREDITS FROM COURSE WHERE DEPT_NAME    IN('PHYSICS', 'MUSIC','FINANCE','BIOLOGY');

2) Display records with instructor name starting with “K” and salary more than

65000.

SELECT * FROM INSTRUCTOR WHERE NAME LIKE 'K%' AND SALARY>=65000;

3) Display name, dept, gross salary, and net salary of instructors with 105% DA,

20% HRA, 30% IT. (gross salary = salary + DA + HRA, net salary = gross salary –

IT).

SELECT NAME, DEPARTMENT,1.05*SALARY+0.2*SALARY+SALARY"GROSS SALARY",1.05*SALARY+0.2*SALARY+SALARY-0.3*SALARY "NET SALARY" FROM INSTRUCTOR;

4) Display records with salary range 60000 – 80000.

SELECT * FROM INSTRUCTOR WHERE SALARY  BETWEEN 60000 AND 80000;

5) Display the records having the second letter in the instructor's name as ‘r’.

SELECT * FROM INSTRUCTOR WHERE NAME LIKE '_R%';

6) Display the names of the instructors of the Comp. Sci. Department in the

descending order of their salary.

SELECT NAME, SALARY FROM INSTRUCTOR WHERE DEPARTMENT='COMP.SCI.' ORDER BY SALARY DESC;

7) Update all records of the Instructor table with a salary hike of 15%.

UPDATE INSTRUCTOR SET SALARY=SALARY+0.15*SALARY;

8) Update the records with a salary hike of 3% for comp. Sc. Dept instructors

having salary less than 70000.

UPDATE INSTRUCTOR SET SALARY=SALARY+0.03*SALARY WHERE SALARY<70000 AND DEPARTMENT='COMP.SCI.';

9) Display the annual salary of each instructor.

SELECT NAME, SALARY*12 "ANNUAL SALARY" FROM INSTRUCTOR;

10) Display the Avg. salary of instructors of the Physics department.

SELECT AVG(SALARY)"AVG. SALARY" FROM INSTRUCTOR WHERE DEPARTMENT='PHYSICS';

11) Display the ID, Name & Department of the instructor drawing the highest

Salary.

SELECT ID,NAME,DEPARTMENT FROM INSTRUCTOR WHERE SALARY=(SELECT MAX(SALARY) FROM INSTRUCTOR);

12) Display the number of instructors available in comp. sci. Department.

SELECT COUNT(ID) FROM INSTRUCTOR WHERE DEPARTMENT='COMP.SCI.';

13) Display the total credits of all courses offered in comp.sci. Department.

SELECT SUM(CREDITS) "TOTAL CREDITS" FROM COURSE WHERE DEPT_NAME='COMP.SCI.';

14) Display the number of instructors and total salary drawn by physics and

comp.sci. Departments.

SELECT COUNT(ID),SUM(SALARY)"TOTAL SALARY" FROM INSTRUCTOR WHERE DEPARTMENT IN('PHYSICS','COMP.SCI.') GROUP BY DEPARTMENT;

15) Display the total credits of comp.sc and biology departments from the course

Table.

SELECT SUM(CREDITS) "TOTAL CREDITS" FROM COURSE WHERE  DEPT_NAME IN('COMP.SCI.','BIOLOGY');

16) Display building-wise total budget values.

SELECT BUILDING, SUM(BUDGET) "TOTAL BUDGET" FROM DEPARTMENT GROUP BY BUILDING ORDER BY BUILDING DESC;

17) Display the number of instructors of each department.

SELECT DEPARTMENT, COUNT(*) "NO.OF. PROFESSORS"  FROM INSTRUCTOR GROUP BY DEPARTMENT;

18) Display the number of instructors of each department sorted in high to low.

SELECT DEPARTMENT, COUNT(DEPARTMENT) "NO.OF. PROFESSORS"  FROM INSTRUCTOR GROUP BY DEPARTMENT ORDER BY COUNT(DEPARTMENT) DESC;

19) Display the number of courses offered semester-wise.

SELECT SEMESTER, COUNT(ID) FROM TEACHES GROUP BY SEMESTER;

20) Display the name of departments having a number of instructors less than 2;

SELECT DEPARTMENT, COUNT(DEPARTMENT) "NO.OF. PROFESSORS"  FROM INSTRUCTOR GROUP BY DEPARTMENT HAVING  COUNT(*)<2; 

21) List the number of instructors of each department having 2 or more than 2

instructors except for the finance department, sorted in high to a low order of their number.

SELECT DEPARTMENT,COUNT(DEPARTMENT) FROM INSTRUCTOR GROUP BY DEPARTMENT HAVING COUNT(DEPARTNAME)>=2 AND DEPARTMENT<> 'FINANCE' ORDER BY DEPARTMENT;