dbms-coursework/university.session.sql at main · vidhatrihr/dbms-coursework · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
select
  name,
  dept_name,
  salary
FROM
  instructor
GROUP BY
  name,
  dept_name,
  salary
ORDER BY
  dept_name DESC,
  name ASC,
  salary asc;

SELECT
  name,
  dept_name,
  salary
from
  instructor
ORDER BY
  dept_name DESC,
  salary ASC,
  name asc;

SELECT
  *
from
  instructor
where
  dept_name = 'Accounting'
  and salary > any (
    select
      salary
    from
      instructor
    WHERE
      dept_name = 'Psychology'
  );

select
  title
from
  course
where
  course_id in (
    select
      course_id
    from
      section
    where
      semester = 'Fall'
    intersect
    select
      course_id
    from
      section
    where
      semester = 'Spring'
  );

select
  building
from
  department
GROUP BY
  building;

select
  building,
  count(ID)
from
  student
  natural join department
GROUP BY
  building;

select
  building,
  count(DISTINCT ID)
from
  takes
  inner join student using (ID)
  inner join section using (course_id, sec_id, semester, year)
where
  year in (2005, 2006, 2007, 2008)
GROUP BY
  building;

select
  max(salary)
from
  instructor
where
  dept_name = 'Psychology';

select
  dept_name,
  avg(salary)
from
  instructor
GROUP BY
  dept_name
HAVING
  avg(salary) > (
    select
      max(salary)
    from
      instructor
    where
      dept_name = 'Psychology'
  );

select
  dept_name,
  max(salary)
from
  instructor
  natural join department
GROUP BY
  dept_name