Opening the paper…
Database Management Systems, Quiz 1
Consider the table instructor shown in Table 1.
| id | name | salary |
|---|---|---|
| 6001 | Oliver | 45000 |
| 6002 | Jack | 30000 |
| 6003 | Oliver | 45000 |
| 6004 | Jack | 30000 |
| 6005 | Jacob | 70000 |
| 6006 | Tommy | 60000 |
| 6007 | Joseph | 65000 |
| 6008 | Jacob | 70000 |
Table 1: instructor
What will be the output of the following query ?
SELECT nameFROM instructor AS aWHERE( SELECT COUNT(*) FROM instructor b WHERE b.salary>a.salary)>2
EXCEPT ALL
SELECT DISTINCT(name)FROM instructorConsider the table **instructor** shown in Table 1. | id | name | salary | |---|---|---| | 6001 | Oliver | 45000 | | 6002 | Jack | 30000 | | 6003 | Oliver | 45000 | | 6004 | Jack | 30000 | | 6005 | Jacob | 70000 | | 6006 | Tommy | 60000 | | 6007 | Joseph | 65000 | | 6008 | Jacob | 70000 | Table 1: **instructor** What will be the output of the following query ? SELECT name FROM instructor AS a WHERE( SELECT COUNT(*) FROM instructor b WHERE b.salary>a.salary)>2 EXCEPT ALL SELECT DISTINCT(name) FROM instructor Consider the table **instructor** given below. | id | name | dept_name | salary | |---|---|---|---| | 10101 | Srinivasan | Comp. Sci. | 65000 | | 12121 | Wu | Finance | 90000 | | 15151 | Mozart | Music | 40000 | | 22222 | Einstein | Physics | 95000 | | 32343 | El Said | History | 60000 | | 33456 | Gold | Physics | 87000 | | 45565 | Katz | Comp. Sci. | 75000 | | 58583 | Califieri | History | 62000 | | 76543 | Singh | Finance | 80000 | | 76766 | Crick | Biology | 72000 | | 83821 | Brandt | Comp. Sci. | 92000 | | 98345 | Kim | Elec. Eng. | 80000 | Table 2: **instructor** What will be the output of the following query? with dept_total (dept_name, value) as (select dept_name, sum(salary) from instructor group by dept_name), dept_total_avg(value) as (select avg(value) from dept_total) select dept_name from dept_total, dept_total_avg where dept_total.value > dept_total_avg.value Figure from the original question paper