第三章作业

3.1

a.

1
2
3
SELECT title
  FROM course
 WHERE credits = 3 AND dept_name = 'Comp. Sci.'

b.

1
2
3
4
5
6
7
8
9
10
11
12
SELECT distinct ID
  FROM takes
 WHERE (course_id,
        sec_id,
        semester,
        year) IN (SELECT course_id,
                         sec_id,
                         semester,
                         year
                    FROM instructor i, teaches j
                   WHERE i.ID = j.ID AND i.name = 'Einstein');

c.

1
SELECT MAX (salary) FROM instructor

d.

1
2
3
SELECT ID
  FROM instructor
 WHERE salary >= ALL (SELECT salary FROM instructor);

e.

1
2
3
4
  SELECT course_id, COUNT (DISTINCT ID)
    FROM takes
   WHERE semester = 'Fall' AND year = 2017
GROUP BY course_id

f.

1
2
3
4
  SELECT MAX (COUNT (DISTINCT ID))
    FROM takes
   WHERE semester = 'Fall' AND year = 2017
GROUP BY course_id

g.

1
2
3
4
5
6
7
8
9
SELECT course_id
  FROM (  SELECT course_id, COUNT (DISTINCT ID) cnt_ID
            FROM takes
           WHERE semester = 'Fall' AND year = 2017
        GROUP BY course_id)
 WHERE cnt_ID >= ALL (  SELECT COUNT (DISTINCT ID)
                          FROM takes
                         WHERE semester = 'Fall' AND year = 2017
                      GROUP BY course_id)

3.2

a.

1
2
3
4
5
6
SELECT SUM (j.credits * k.points)
  FROM takes i, course j, grade_points k
 WHERE     i.ID = '12345'
       AND i.grade IS NOT NULL
       AND i.course_id = j.course_id
       AND i.grade = k.grade

b.

1
2
3
4
5
6
SELECT SUM (j.credits * k.points) / SUM (j.credits) GPA
  FROM takes i, course j, grade_points k
 WHERE     i.ID = '12345'
       AND i.grade IS NOT NULL
       AND i.course_id = j.course_id
       AND i.grade = k.grade

c.

1
2
3
4
5
6
  SELECT i.ID, SUM (j.credits * k.points) / SUM (j.credits) GPA
    FROM takes i, course j, grade_points k
   WHERE     i.course_id = j.course_id
         AND i.grade IS NOT NULL
         AND i.grade = k.grade
GROUP BY i.ID

d.

1
--有效，因为我在where谓词里面加了takes.grade is not null限定takes.grade不为空。

3.3

a.

1
2
3
UPDATE instructor
   SET salary = salary * 1.1
 WHERE dept_name = 'Comp. Sci.';

b.

1
2
3
4
5
DELETE FROM course
      WHERE course_id IN (SELECT course_id
                            FROM ( (SELECT course_id FROM course)
                                  MINUS
                                  (SELECT course_id FROM section)));

c.

1
2
3
4
5
6
7
INSERT INTO instructor
   SELECT ID,
          name,
          dept_name,
          10000
     FROM student
    WHERE tot_cred > 100;

3.4

a.

1
2
3
SELECT SUM (i.driver_id)
  FROM participated i, accident j
 WHERE i.report_number = j.report_number AND j.year = 2017;

b.

1
2
3
4
5
DELETE FROM car
      WHERE     year = 2010
            AND license_plate IN (SELECT license_plate
                                    FROM owns
                                   WHERE owns.driver_id = '12345');

3.5

a.

1
2
3
4
5
6
7
8
9
10
SELECT ID,
       CASE
          WHEN score < 40 THEN 'F'
          WHEN score >= 40 AND score < 60 THEN 'C'
          WHEN score >= 60 AND score < 80 THEN 'B'
          WHEN score >= 80 THEN 'A'
          ELSE 'Z'
       END
          AS div
  FROM marks;

b.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
WITH student(ID, div)
     AS (SELECT ID,
                CASE
                   WHEN score < 40 THEN 'F'
                   WHEN score >= 40 AND score < 60 THEN 'C'
                   WHEN score >= 60 AND score < 80 THEN 'B'
                   WHEN score >= 80 THEN 'A'
                   ELSE 'Z'
                END
                   AS div
           FROM marks)
  SELECT i.div, COUNT (*)
    FROM student i
GROUP BY i.div

3.6

1
2
3
SELECT dept_name
  FROM department
 WHERE LOWER (dept_name) LIKE '%sci%';

3.7

1
2
3
4
5
6
select p.a1
from p, r1, r2
where p.a1=r1.a1 and p.a1=r2.a1

-- 在r1.a1与r2.a1没有相同元素的情况下并且r1.a1并上r2.a1包含了p.a1的所有元素，p.a1只在r1.a1或r2.a1中的一个中。
-- 由于笛卡尔积的特性，r1或r2为空不影响结果。

3.8

a.

1
2
3
SELECT ID
  FROM depositor
 WHERE ID NOT IN (SELECT ID FROM borrower);

b.

1
2
3
4
5
6
SELECT i.ID
  FROM customer i
 WHERE (i.customer_city, i.customer_street) IN (SELECT j.customer_city,
                                                       j.customer_street
                                                  FROM customer j
                                                 WHERE j.ID = '12345');

c.

1
2
3
4
5
6
7
8
WITH Harrison_customer (ID)
     AS (SELECT i.ID
           FROM customer i
          WHERE i.customer.city = 'Harrison')
SELECT DISTINCT i.branch_name
  FROM account i, depositor j
 WHERE     i.account_number = j.account_number
       AND j.ID IN (SELECT ID FROM Harrison_customer);

3.9

a.

1
2
3
SELECT j.ID, j.person_name, j.city
  FROM works i, employee j
 WHERE i.ID = j.ID AND i.company_name = 'First Bank Corporation';

b.

1
2
3
4
5
SELECT j.ID, j.person_name, j.city
  FROM works i, employee j
 WHERE     i.ID = j.ID
       AND i.company_name = 'First Bank Corporation'
       AND i.salary > 10000;

c.

1
2
3
4
5
6
7
WITH work_for_First_Bank_corporation (ID)
     AS (SELECT j.ID
           FROM works i, employee j
          WHERE i.ID = j.ID AND i.company_name = 'First Bank Corporation')
SELECT ID
  FROM employee i
 WHERE i.ID NOT IN (SELECT ID FROM work_for_First_Bank_corporation);

d.

1
2
3
4
5
6
SELECT j.ID
  FROM works i, employee j
 WHERE     i.ID = j.ID
       AND i.salary > (SELECT MAX (k.salary)
                         FROM works k
                        WHERE k.company_name = 'Small Bank Corporation');

e.

1
2
3
4
5
6
7
WITH SBC_city (city)
     AS (SELECT city
           FROM company
          WHERE company_name = 'Small Bank Corporation')
SELECT i.company_name
  FROM company i
 WHERE i.city IN (SELECT city FROM SBC_city);

f.

1
2
3
4
5
6
7
8
9
WITH max_employee
     AS (  SELECT MAX (COUNT (*)) employee_number
             FROM works
         GROUP BY company_name)
SELECT i.company_name
  FROM (  SELECT company_name, COUNT (*) employee_number
            FROM works
        GROUP BY company_name) i
 WHERE i.employee_number = (SELECT employee_number FROM max_employee);

g.

1
2
3
4
5
6
7
8
9
WITH FBC_avg_salary (avg_salary)
     AS (SELECT SUM (salary) / COUNT (*)
           FROM works
          WHERE company_name = 'First Bank Corporation')
SELECT i.company_name
  FROM (  SELECT company_name, SUM (salary) / COUNT (*) avg_salary
            FROM works
        GROUP BY company_name) i
 WHERE i.avg_salary > (SELECT avg_salary FROM FBC_avg_salary);

3.10

a.

1
2
3
UPDATE employee
   SET city = 'Newtown'
 WHERE ID = '12345';

b.

1
2
3
4
5
6
7
8
UPDATE works
   SET salary =
          CASE
             WHEN salary <= 100000 THEN salary = 1.1 * salary
             ELSE salary = 1.03 * salary
          END
 WHERE     company_name = 'First Bank Corporation'
       AND ID IN (SELECT manager_id FROM managers);

3.11

a.

1
2
3
4
5
6
7
WITH Comp_course
     AS (SELECT course_id
           FROM course
          WHERE dept_name = 'Comp. Sci.')
SELECT DISTINCT i.ID, i.name
  FROM student i, takes j
 WHERE i.ID = j.ID AND j.course_id IN (SELECT course_id FROM Comp_course);

b.

1
2
3
4
5
6
(SELECT DISTINCT i.ID, i.name
   FROM student i)
MINUS
(SELECT DISTINCT i.ID, i.name
   FROM student i, takes j
  WHERE i.ID = j.ID AND j.year < 2017);

c.

1
2
3
  SELECT dept_name, MAX (salary)
    FROM instructor
GROUP BY dept_name;

d.

1
2
3
  SELECT MIN (MAX (salary))
    FROM instructor
GROUP BY dept_name;

3.12

a.

1
2
3
4
5
INSERT INTO course
     VALUES ('CS-001',
             'Weekly Seminar',
             NULL,
             0);

b.

1
2
3
4
5
6
7
8
INSERT INTO section
     VALUES ('CS-001',
             1,
             'Fall',
             2017,
             NULL,
             NULL,
             NULL);

c.

1
2
3
4
5
6
7
8
9
INSERT INTO takes i
   SELECT i.ID,
          j.course_id,
          j.sec_id,
          j.semester,
          j.year,
          NULL
     FROM section j
    WHERE j.course_id = 'CS-001';

d.

1
2
DELETE FROM takes
      WHERE ID = '12345';

e.

1
2
3
4
DELETE FROM course
      WHERE course_id = 'CS-001';
      
-- 会删不掉，因为子关系约束了

f.

1
2
3
4
5
6
7
8
-- 先找takes中的course_id中的title有‘advanced’的,再删除

DELETE FROM takes
      WHERE course_id IN (SELECT i.course_id
                            FROM takes i, course j
                           WHERE     i.course_id = j.course_id
                                 AND LOWER (j.title) LIKE
                                        LOWER ('%advanced%'));

3.13

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
CREATE TABLE person
(
   driver_id   NUMERIC (20, 0),
   name        VARCHAR (20),
   address     VARCHAR (20),
   PRIMARY KEY (driver_id)
);

CREATE TABLE car
(
   license_plate   VARCHAR (20),
   model           VARCHAR (10),
   year            NUMERIC (4, 0),
   PRIMARY KEY (license_plate)
);

CREATE TABLE accident
(
   report_number   VARCHAR (20, 0),
   year            NUMERIC (4, 0),
   location        VARCHAR (20),
   PRIMARY KEY (report_number)
);

CREATE TABLE owns
(
   driver_id       NUMERIC (20, 0),
   license_plate   VARCHAR (20),
   PRIMARY KEY (driver_id, license_plate),
   FOREIGN KEY (driver_id) REFERENCES person
);

CREATE TABLE participated
(
   report_number   VARCHAR (20, 0),
   license_plate   VARCHAR (20),
   driver_id       NUMERIC (20, 0),
   damage_amount   NUMERIC (20, 0),
   PRIMARY KEY (report_number, license_plate),
   FOREIGN KEY (driver_id) REFERENCES owns
);

3.14

a.

1
2
3
4
5
6
7
8
-- 先找到‘John Smith’拥有的所有车，再找这些车中出事故的车

SELECT COUNT (*)
  FROM paticipated i
 WHERE i.license_plate IN (SELECT j.license_plate
                             FROM person i, owns j
                            WHERE     i.driver_id = j.driver_id
                                  AND i.name = 'John Smith');

b.

1
2
3
UPDATE participated
   SET damage_account = 3000
 WHERE report_number = 'AR2197' AND license_plate = 'AABB2000';

3.15

a.

1
2
3
4
5
6
7
8
9
10
11
12
-- 先找到ID选的所有银行;再找所有有的银行;再找所有银行minus所选id的选择的所有银行

SELECT DISTINCT k.customer_name
  FROM customer k
 WHERE     k.customer_city = 'Brooklyn'
       AND NOT exit (
                  ( (SELECT DISTINCT branch_name FROM branch)
                   MINUS
                   (SELECT DISTINCT i.branch_name
                      FROM account i, depositor j
                     WHERE     j.ID = k.ID
                           AND i.account_number = j.account_number)));

b.

1
2
3
  SELECT branch_name, SUM (amount)
    FROM loan
GROUP BY branch_name

c.

1
2
3
4
5
6
7
-- 先找到位于'Brooklyn'的所有银行的资产

SELECT branch_name
  FROM branch
 WHERE assets > SOME (SELECT assets
                        FROM branch
                       WHERE branch_city = 'Brooklyn');

3.16

a.

1
2
3
SELECT i.ID, i.person_name
  FROM employee i, works j, company k
 WHERE i.ID = j.ID AND j.company_name = k.company_name AND i.city = k.city;

b.

1
2
3
4
5
6
7
8
-- 先找到雇员对应的管理者的住处

SELECT i.ID, i.person_name
  FROM employee i
 WHERE (i.ID, i.street, i.city) IN (SELECT k.ID, k.street, k.city
                                      FROM employee j, manages k
                                     WHERE     j.ID = k.manager_id
                                           AND k.ID = i.ID);

c.

1
2
3
4
5
6
SELECT i.ID, i.person_name
  FROM employee i, works j
 WHERE     i.ID = j.ID
       AND j.salary > (SELECT SUM (k.salary) / COUNT (*)
                         FROM works k
                        WHERE k.company_name = j.company_name);

d.

1
2
3
4
5
6
7
8
9
-- 先找到所有公司的总和salary

WITH company_sum_salary
     AS (  SELECT company_name, SUM (salary) sum_salary
             FROM works
         GROUP BY company_name)
SELECT i.company_name
  FROM company_sum_salary i
 WHERE i.sum_salary <= ALL (SELECT sum_salary FROM company_sum_salary);

3.17

a.

1
2
3
UPDATE work
   SET salary = salary * 1.1
 WHERE company_name = 'First Bank Corporation';

b.

1
2
3
4
5
6
UPDATE works
   SET salary = salary * 1.1
 WHERE ID IN (SELECT i.manager_id
                FROM manages i, works j
               WHERE     i.manager_id = j.ID
                     AND j.company_name = 'First Bank Corporation');

c.

1
2
DELETE FROM works
      WHERE company_name = 'Small Bank Corporation';

3.18

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
/* Formatted on 2024/4/8 13:27:10 (QP5 v5.256.13226.35538) */
CREATE TABLE employee
(
   ID            NUMERIC (20, 0),
   person_name   VARCHAR (20),
   street        VARCHAR (20),
   city          VARCHAR (10),
   PRIMARY KEY (ID)
);

CREATE TABLE works
(
   ID             NUMERIC (20, 0),
   company_name   VARCHAR (20),
   salary         NUMERIC (10, 2),
   PRIMARY KEY (ID),
   FOREIGN KEY (ID) REFERENCES employee,
   FOREIGN KEY (company_name) REFERENCES company
);

CREATE TABLE company
(
   company_name   VARCHAR (20),
   city           VARCHAR (10),
   PRIMARY KEY (comapny_name)
);

CREATE TABLE manages
(
   ID           NUMERIC (20, 0),
   manager_id   NUMERIC (20, 0),
   PRIMARY KEY (ID),
   FOREIGN KEY (ID) REFERENCES employee,
   FOREIGN KEY (manager_id) REFERENCES employee
);

3.21

a.

1
2
3
4
5
SELECT i.memb_no, i.name
  FROM member i, book j, borrowed k
 WHERE     k.memb_no = i.memb_no
       AND k.isbn = j.isbn
       AND j.publisher = 'McGraw-Hill';

b.

1
2
3
4
5
6
7
8
9
10
11
-- 先找到由McGraw-Hill出版的所有书，再找到一个人借的所有书

SELECT i.memb_no, i.name
  FROM MEMBER i
 WHERE NOT exit ( ( (SELECT isbn
                       FROM book
                      WHERE publisher = 'McGraw-Hill')
                   MINUS
                   (SELECT isbn
                      FROM borrowed
                     WHERE memb_no = i.memb_no)));

c.

1
2
3
4
5
  SELECT k.pubicsher, i.memb_no, i.name
    FROM member i, borrowed j, book k
   WHERE i.memb_no = j.memb_no AND j.isnb = k.isnb
GROUP BY i.memb_no, i.name, k.publisher
  HAVING COUNT (*) > 5;

d.

1
2
SELECT (SELECT COUNT (*) FROM borrowed) / (SELECT COUNT (*) FROM MEMBER)
  FROM DUAL

3.22

1
2
3
4
5
6
-- 利用想唯一的元素分组，看组内元素是否唯一即可

WHERE title IN (  SELECT title
                     FROM course
                 GROUP BY title
                   HAVING COUNT (*) = 1);

3.23

1
2
3
4
5
6
SELECT dept_name
  FROM (  SELECT dept_name, SUM (salary) VALUE
            FROM instructor
        GROUP BY dept_name) dept_total,
       (SELECT AVG (VALUE) VALUE FROM dept_total) dept_total_avg
 WHERE dept_total.VALUE >= dept_total_avg.VALUE;

3.24

1
2
3
4
5
6
SELECT k.name, k.ID
  FROM instructor i, advisor j, student k
 WHERE     i.dept_name = 'Physics'
       AND i.ID = j.i_id
       AND j.s_id = k.ID
       AND k.dept_name = 'Accounting';

3.25

1
2
3
4
5
SELECT dept_name
  FROM department
 WHERE budget > (SELECT DISTINCT budget
                   FROM department
                  WHERE dept_name = 'Philosophy');

3.26

1
2
3
4
5
  SELECT course_id, ID
    FROM takes
GROUP BY ID, course_id
  HAVING COUNT (*) >= 3
ORDER BY course_id ASC;

3.27

1
2
3
4
5
6
7
  SELECT ID
    FROM (  SELECT ID, course_id
              FROM takes
          GROUP BY ID, course_id
            HAVING COUNT (*) >= 2)
GROUP BY ID
  HAVING COUNT (*) >= 3;

3.28

1
2
3
4
5
6
  SELECT DISTINCT k.name, k.ID
    FROM teaches i, course j, instructor k
   WHERE     i.course_id = j.course_id
         AND i.ID = k.ID
         AND j.dept_name = k.dept_name
ORDER BY k.name ASC;

3.29

1
2
3
4
5
6
7
8
9
10
11
12
-- 先找到选过至少5门music课的学生ID，再找学生

SELECT name, ID
  FROM student
 WHERE     ID NOT IN (  SELECT i.ID
                          FROM takes i, course j
                         WHERE     i.course_id = j.course_id
                               AND j.dept_name = 'Music'
                      GROUP BY i.ID
                        HAVING COUNT (*) >= 5)
       AND name LIKE 'D%'
       AND dept_name = 'History';

3.30

1
2
3
-- 当instructor中某一个元组存在空值时，二者不一样。
-- 因为当存在空值时，sum(salary)还是正常总和，空值作为0，但是count(*)把空值的元组也算进来了。故更小一些。
-- avg(salary)会忽略空值的元组

3.31

1
2
3
4
5
6
7
8
9
10
11
-- 先找到哪些给过A的老师ID

SELECT ID, name
  FROM instructor
 WHERE ID NOT IN (SELECT i.ID
                    FROM teaches i, takes j
                   WHERE     i.course_id = j.course_id
                         AND i.sec_id = j.sec_id
                         AND i.semester = j.semester
                         AND i.year = j.year
                         AND j.grade = 'A');

3.32

1
2
3
4
5
6
7
8
9
10
SELECT ID, name
  FROM instructor
 WHERE     ID NOT IN (SELECT i.ID
                        FROM teaches i, takes j
                       WHERE     i.course_id = j.course_id
                             AND i.sec_id = j.sec_id
                             AND i.semester = j.semester
                             AND i.year = j.year
                             AND j.grade = 'A')
       AND ID IN (SELECT ID FROM teaches);

3.33

1
2
3
4
5
6
SELECT DISTINCT i.course_id, i.title
  FROM course i, section j, time_slot k
 WHERE     i.dept_name = 'Comp. Sci.'
       AND i.course_id = j.course_id
       AND j.time_slot_id = k.time_slot_id
       AND K.end_hr >= 12;

3.34

1
2
3
4
5
6
7
8
9
10
11
12
13
14
  SELECT i.course_id,
         i.sec_id,
         i.year,
         i.semester,
         COUNT (*)
    FROM section i, takes j
   WHERE     i.course_id = j.course_id
         AND i.sec_id = j.sec_id
         AND i.semester = j.semester
         AND i.year = j.year
GROUP BY i.course_id,
         i.sec_id,
         i.semester,
         i.year;

3.35

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
WITH course_diff_section (course_id,
                          sec_id,
                          year,
                          semester,
                          num)
     AS (  SELECT i.course_id,
                  i.sec_id,
                  i.year,
                  i.semester,
                  COUNT (*)
             FROM section i, takes j
            WHERE     i.course_id = j.course_id
                  AND i.sec_id = j.sec_id
                  AND i.semester = j.semester
                  AND i.year = j.year
         GROUP BY i.course_id,
                  i.sec_id,
                  i.semester,
                  i.year)
SELECT course_id,
       sec_id,
       year,
       semester,
       num
  FROM course_diff_section
 WHERE num >= ALL (SELECT num FROM course_diff_section);