DBMS LAB 4

1.mysql> select name from instructor where dept_name='Comp. Sci.';
+------------+
| name       |
+------------+
| Srinivasan |
| Katz       |
| Brandt     |
+------------+
3 rows in set (0.03 sec)

2.mysql> select course_id,title from course where course.dept_name in(select dept_name from instructor where name='Srinivasan');
+-----------+----------------------------+
| course_id | title                      |
+-----------+----------------------------+
| CS-101    | Intro. to Computer Science |
| CS-190    | Game Design                |
| CS-315    | Robotics                   |
| CS-319    | Image Processing           |
| CS-347    | Database System Concepts   |
+-----------+----------------------------+
5 rows in set (0.04 sec)


3.mysql> select ID,name from instructor where dept_name='Comp. Sci.';
+-------+------------+
| ID    | name       |
+-------+------------+
| 10101 | Srinivasan |
| 45565 | Katz       |
| 83821 | Brandt     |
+-------+------------+
3 rows in set (0.00 sec)


4.mysql> select name from instructor where instructor.ID in(select ID from teaches where semester='Spring' and year='2009');
+--------+
| name   |
+--------+
| Brandt |
| Kim    |
+--------+
2 rows in set (0.00 sec)


5.mysql> select SUM(capacity) from classroom where building='Watson';
+---------------+
| SUM(capacity) |
+---------------+
|            80 |
+---------------+
1 row in set (0.00 sec)

6.mysql> select name from student where student.dept_name in(select dept_name from instructor where name='Singh');
+--------+
| name   |
+--------+
| Chavez |
+--------+
1 row in set (0.01 sec)


7.mysql> select title from course where course.course_id in(select course_id from teaches where semester='Spring' and year='2010');
+----------------------------+
| title                      |
+----------------------------+
| Intro. to Computer Science |
| Robotics                   |
| Image Processing           |
| Investment Banking         |
| World History              |
| Music Video Production     |
+----------------------------+
6 rows in set (0.00 sec)


8.mysql> select ID,name from instructor where salary in(select MAX(salary) from instructor);
+-------+----------+
| ID    | name     |
+-------+----------+
| 22222 | Einstein |
+-------+----------+
1 row in set (0.00 sec)


9.mysql> select * from classroom where room_number in(select room_number from section where course_id in(select course_id from course where title='Robotics'));
+----------+-------------+----------+
| building | room_number | capacity |
+----------+-------------+----------+
| Watson   | 120         |       50 |
+----------+-------------+----------+
1 row in set (0.02 sec)


10.mysql> select ID,name from instructor where ID in(select ID from teaches where course_id in(select course_id from course where title='Database System Concepts'));
+-------+------------+
| ID    | name       |
+-------+------------+
| 10101 | Srinivasan |
+-------+------------+
1 row in set (0.00 sec)

11. mysql> select dept_name, count(ID) as Number from instructor group by dept_name order by Number desc;
+------------+--------+
| dept_name  | Number |
+------------+--------+
| Comp. Sci. |      3 |
| Finance    |      2 |
| History    |      2 |
| Physics    |      2 |
| Music      |      1 |
| Biology    |      1 |
| Elec. Eng. |      1 |
+------------+--------+
7 rows in set (0.00 sec)

12. mysql> select dept_name, AVG(salary) from instructor group by dept_name;
+------------+--------------+
| dept_name  | AVG(salary)  |
+------------+--------------+
| Biology    | 72000.000000 |
| Comp. Sci. | 77333.333333 |
| Elec. Eng. | 80000.000000 |
| Finance    | 85000.000000 |
| History    | 61000.000000 |
| Music      | 40000.000000 |
| Physics    | 91000.000000 |
+------------+--------------+
7 rows in set (0.00 sec)

13. mysql> select dept_name, AVG(salary) as avg_salary from instructor group by dept_name having avg_salary > 42000;
+------------+--------------+
| dept_name  | avg_salary   |
+------------+--------------+
| Biology    | 72000.000000 |
| Comp. Sci. | 77333.333333 |
| Elec. Eng. | 80000.000000 |
| Finance    | 85000.000000 |
| History    | 61000.000000 |
| Physics    | 91000.000000 |
+------------+--------------+
6 rows in set (0.00 sec)

14. mysql> select title from course where course.course_id in (select course_id from teaches where (semester="Fall" and year="2009") OR (semester="Spring" and year="2010"));
+----------------------------+
| title                      |
+----------------------------+
| Intro. to Computer Science |
| Robotics                   |
| Image Processing           |
| Database System Concepts   |
| Investment Banking         |
| World History              |
| Music Video Production     |
| Physical Principles        |
+----------------------------+
8 rows in set (0.01 sec)

15. mysql> select title from course where course.course_id in (select course_id from teaches where (semester="Fall" and year="2009") OR (semester!="Spring" and year!="2010"));
+----------------------------+
| title                      |
+----------------------------+
| Intro. to Biology          |
| Intro. to Computer Science |
| Database System Concepts   |
| Physical Principles        |
+----------------------------+
4 rows in set (0.00 sec)



16. mysql> select name from instructor where salary > (select MAX(salary) from instructor where dept_name="Biology");+----------+
| name     |
+----------+
| Wu       |
| Einstein |
| Gold     |
| Katz     |
| Singh    |
| Brandt   |
| Kim      |
+----------+
7 rows in set (0.02 sec)

17. mysql> select dept_name , avg_salary from (select dept_name, AVG(salary) as avg_salary from instructor group by dept_name having avg_salary > 42000) as T;
+------------+--------------+
| dept_name  | avg_salary   |
+------------+--------------+
| Biology    | 72000.000000 |
| Comp. Sci. | 77333.333333 |
| Elec. Eng. | 80000.000000 |
| Finance    | 85000.000000 |
| History    | 61000.000000 |
| Physics    | 91000.000000 |
+------------+--------------+
6 rows in set (0.03 sec)

18. mysql> update instructor set salary= case when salary>80000 then salary*1.03 else salary*1.05 end;
Query OK, 12 rows affected (0.40 sec)
Rows matched: 12  Changed: 12  Warnings: 0