@SovietPower 2022-03-18T23:06:26.000000Z 字数 23005 阅读 2255

数据库系统实验2 SQL练习

ECNU

数据库系统实验2 SQL练习

创建数据表并导入数据

运行实验1创建的mygauss容器，创建一个新文件夹：

PS H:\> docker start opengauss
PS H:\> docker exec -it opengauss bash
root@97375c3acbd1:/# su omm
omm@97375c3acbd1:/$ cd /home/omm
omm@97375c3acbd1:~$ mkdir db2022
omm@97375c3acbd1:~$ cd db2022
omm@97375c3acbd1:~/db2022$

在一个新powershell窗口中，将下载的SQL脚本文件复制到容器中的相应目录：

PS H:\> docker cp DDL+drop.sql opengauss:/home/omm/db2022/
PS H:\> docker cp largeRelationsInsertFile.sql opengauss:/home/omm/db2022/
omm@97375c3acbd1:~/db2022$ ls -l
total 2248
-rwxr-xr-x 1 root root    3693 Mar 15 01:53 DDL+drop.sql
-rwxr-xr-x 1 root root 2296465 Mar 15 01:53 largeRelationsInsertFile.sql

使用客户端工具gsql通过脚本文件DDL+drop.sql, largeRelationsInsertFile.sql向数据表中插入数据：

omm@97375c3acbd1:~/db2022$ gsql -d db2022 -f DDL+drop.sql
DROP TABLE
DROP TABLE
DROP TABLE
DROP TABLE
DROP TABLE
DROP TABLE
DROP TABLE
DROP TABLE
DROP TABLE
DROP TABLE
DROP TABLE
gsql:DDL+drop.sql:21: NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "classroom_pkey" for table "classroom"
CREATE TABLE
...
gsql:DDL+drop.sql:130: NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "prereq_pkey" for table "prereq"
CREATE TABLE
total time: 8857  ms
omm@97375c3acbd1:~/db2022$ gsql -d db2022 -U gxb -W 'Gxb12345' -f largeRelationsInsertFile.sql
...
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
total time: 3509318  ms

连接数据库，然后检查一下记录的数量：

omm@97375c3acbd1:~/db2022$ gsql -d db2022 -U gxb -W 'Gxb12345' -r
gsql ((openGauss 2.1.0 build 590b0f8e) compiled at 2021-09-30 14:29:04 commit 0 last mr  )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.
db2022=# select count(*) from advisor;
 count
-------
  2000
(1 row)
db2022=# select count(*) from classroom;
 count
-------
    30
(1 row)
...
db2022=# select count(*) from time_slot;
 count
-------
    20
(1 row)

SQL练习 13-19

gsql -d [db] -c 'select * from table;' -o output.csv 可用客户端工具gsql执行单条SQL语句，并将查询结果保存到文件output.csv中。

omm@97375c3acbd1:~/db2022$ gsql -d db2022 -c 'select * from department;' -o department.csv
omm@97375c3acbd1:~/db2022$ ls -l
total 2252
-rwxr-xr-x 1 root root    3693 Mar 15 01:53 DDL+drop.sql
-rw-rw-r-- 1 omm  omm      871 Mar 15 03:25 department.csv
-rwxr-xr-x 1 root root 2296465 Mar 15 01:53 largeRelationsInsertFile.sql
omm@97375c3acbd1:~/db2022$ cat department.csv
  dept_name  |  building   |  budget
-------------+-------------+-----------
 Civil Eng.  | Chandler    | 255041.46
 Biology     | Candlestick | 647610.55
 History     | Taylor      | 699140.86
 Physics     | Wrigley     | 942162.76
 Marketing   | Lambeau     | 210627.58
 Pol. Sci.   | Whitman     | 573745.09
 English     | Palmer      | 611042.66
 Accounting  | Saucon      | 441840.92
 Comp. Sci.  | Lamberton   | 106378.69
 Languages   | Linderman   | 601283.60
 Finance     | Candlestick | 866831.75
 Geology     | Palmer      | 406557.93
 Cybernetics | Mercer      | 794541.46
 Astronomy   | Taylor      | 617253.94
 Athletics   | Bronfman    | 734550.70
 Statistics  | Taylor      | 395051.74
 Psychology  | Thompson    | 848175.04
 Math        | Brodhead    | 777605.11
 Elec. Eng.  | Main        | 276527.61
 Mech. Eng.  | Rauch       | 520350.65
(20 rows)

13. Find the names of all the instructors from Biology department.

db2022=# select name
db2022-# from instructor
db2022-# where dept_name='Biology';
   name
----------
 Queiroz
 Valtchev
(2 rows)

14. Find the names of courses in Computer science department which have 3 credits.

db2022=# select title
db2022-# from course
db2022-# where dept_name='Comp. Sci.' and credits=3;
         title
-----------------------
 International Finance
 Japanese
 Computability Theory
(3 rows)

15. For the student with ID 13403 (or any other value), show all course_id and title of all courses taken by the student.

db2022=# select course_id, title
db2022-# from takes natural join course
db2022-# where id=13403;
 course_id |             title
-----------+-------------------------------
 486       | Accounting
 349       | Networking
 696       | Heat Transfer
 319       | World History
 400       | Visual BASIC
 258       | Colloid and Surface Chemistry
 443       | Journalism
 158       | Elastic Structures
 192       | Drama
 489       | Journalism
 795       | Death and Taxes
 493       | Music of the 50s
 352       | Compiler Design
 338       | Graph Theory
 366       | Computational Biology
 400       | Visual BASIC
 748       | Tort Law
(17 rows)

16. As above, but show the total number of credits for such courses (taken by that student). Don’t display the tot_creds value from the student table, you should use SQL aggregation on courses taken by the student.

db2022=# select sum(credits)
db2022-# from takes natural join course
db2022-# where id=13403;
 sum
-----
  61
(1 row)
db2022=# select tot_cred
db2022-# from student
db2022-# where id=13403;
 tot_cred
----------
       82
(1 row)

17. Display the total credits for each of the students, along with the ID of the student; don’t bother about the name of the student. (Don’t bother about students who have not registered for any course, they can be omitted).

db2022=# select id, sum(credits)
db2022-# from takes natural join course
db2022-# group by id;
  id   | sum
-------+-----
 3039  |  40
 86327 |  44
 22050 |  39
 89551 |  71
 12326 |  36
 19321 |  48
 68516 |  62
 7035  |  56
 81396 |  67
 ...

18. Find the names of all students who have taken any Comp. Sci. course ever (there should be no duplicate names).

db2022=# select distinct name
from student natural join takes join course using (course_id)
where course.dept_name='Comp. Sci.';
       name
-------------------
 Mowbray
 Mendelzon
 Umehara
 Kamae
 Srivastava
 ...

19. Display the IDs of all instructors who have never taught a couse (interpret “taught” as “taught or is scheduled to teach”).

db2022=# (select id
db2022(# from instructor)
db2022-# except
db2022-# (select id
db2022(# from instructor natural join teaches);
  id
-------
 37687
 35579
 74426
 78699
 64871
 50885
 59795
 97302
 57180
 79653
 52647
 31955
 96896
 58558
 63395
 72553
 16807
 95030
 40341
(19 rows)

SQL练习 20-29

20. As above, but display the names of the instructors also, not just the IDs.

db2022=# select id, name
db2022-# from instructor
db2022-# where id not in (select id
db2022(#     from instructor natural join teaches);
  id   |       name
-------+-------------------
 63395 | McKinnon
 78699 | Pingr
 96896 | Mird
 40341 | Murata
 50885 | Konstantinides
 79653 | Levine
 97302 | Bertolino
 57180 | Hau
 35579 | Soisalon-Soininen
 31955 | Moreira
 37687 | Arias
 16807 | Yazdi
 95030 | Arinb
 74426 | Kenje
 58558 | Dusserre
 59795 | Desyl
 52647 | Bancilhon
 72553 | Yin
 64871 | Gutierrez
(19 rows)

21. You need to create a movie database. Create three tables, one for actors(AID, name), one for movies(MID, title) and one for actor_role(MID, AID, rolename). Use appropriate data types for each of the attributes, and add appropriate primary/foreign key constraints.

db2022=# create table actors
db2022-#     (AID varchar(20),
db2022(#     name varchar(50) not null,
db2022(#     primary key (AID));
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "actors_pkey" for table "actors"
CREATE TABLE
db2022=# create table movies
db2022-#     (MID varchar(20),
db2022(#     title varchar(50),
db2022(#     primary key (MID));
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "movies_pkey" for table "movies"
CREATE TABLE
db2022=# create table actor_role
db2022-#     (MID varchar(20),
db2022(#     AID varchar(20),
db2022(#     rolename varchar(20) not null,
db2022(#     primary key (MID, AID, rolename),
db2022(#     foreign key (MID) references movies,
db2022(#     foreign key (AID) references actors);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "actor_role_pkey" for table "actor_role"
CREATE TABLE

22. Insert data to the above tables (approx 3 to 6 rows in each table), including data for actor “Charlie Chaplin”, and for yourself (using your student number as ID).

db2022=# insert into actors values
db2022-#     ('10205', 'GXB'),
db2022-#     ('10000', 'Charlie Chaplin'),
db2022-#     ('10001', 'Paulette Goddard'),
db2022-#     ('10002', 'Emma Watson');
INSERT 0 4
db2022=# insert into movies values
db2022-#     ('10000', 'City Lights'),
db2022-#     ('10001', 'Modern Times'),
db2022-#     ('10002', 'The Gold Rush'),
db2022-#     ('10003', 'Harry Potter');
INSERT 0 4
db2022=# insert into actor_role values
    ('10000', '10000', 'A Tramp'),
    ('10000', '10000', 'Director'),
    ('10001', '10000', 'Charles'),
    ('10001', '10001', 'An Orphan'),
    ('10002', '10000', 'Charles'),
    ('10003', '10002', 'Hermione Granger');
INSERT 0 6

23. Write a query to list all movies in which actor “Charlie Chaplin” has acted, along with the number of roles he had in that movie.

db2022=# select MID, title, cnt
db2022-# from movies natural join (select MID, count(*) as cnt
db2022(#     from actor_role
db2022(#     where AID='10000'
db2022(#     group by MID);
  mid  |     title     | cnt
-------+---------------+-----
 10000 | City Lights   |   2
 10001 | Modern Times  |   1
 10002 | The Gold Rush |   1
(3 rows)

24. Write a query to list the names of actors who have not acted in any movie.

db2022=# select AID, name
db2022-# from actors
db2022-# where AID not in (select AID from actor_role);
  aid  | name
-------+------
 10205 | GXB
(1 row)

25. List names of actors, along with titles of movies they have acted in. If they have not acted in any movie, show the movie title as null. (Do not use SQL outerjoin syntax here, write it from scratch.)

with temp(AID, name, title) as
    (select AID, name, title
    from actors natural join actor_role natural join movies)
(select name, 'null' as title from actors
where AID not in (select AID from temp))
union
(select name, title from temp);

db2022=# with temp(AID, name, title) as
db2022-#     (select AID, name, title
db2022(#     from actors natural join actor_role natural join movies)
db2022-# (select name, 'null' as title from actors
db2022(# where AID not in (select AID from temp))
db2022-# union
db2022-# (select name, title from temp);
       name       |     title
------------------+---------------
 Charlie Chaplin  | The Gold Rush
 GXB              | null
 Emma Watson      | Harry Potter
 Charlie Chaplin  | City Lights
 Charlie Chaplin  | Modern Times
 Paulette Goddard | Modern Times
(6 rows)

26. Find the maximum and minimum enrollment across all sections, considering only sections that had some enrollment, don’t worry about those that had no students taking that section.
一个学生在一个section内上了两节课，是算2还是算1？感觉两种都有意义？按2计算了。

db2022=# select min(cnt), max(cnt)
db2022-# from (select count(*) as cnt
db2022(#     from takes
db2022(#     group by sec_id);
 min |  max
-----+-------
 322 | 25512
(1 row)
db2022=# select min(cnt), max(cnt)
db2022-# from (select count(*) as cnt
db2022(#     from (select distinct ID, sec_id from takes)
db2022(#     group by sec_id);
 min | max
-----+------
 322 | 2000
(1 row)

27. Find all sections that had the maximum enrollment (along with the enrollment).

with max_enroll as
    (select max(cnt) as mx
    from (select sec_id, count(*) as cnt
        from takes
        group by sec_id))
select sec_id, count(*) as cnt
from takes
group by sec_id
having cnt in (select mx from max_enroll);

db2022=#  with max_enroll as
db2022-#     (select max(cnt) as mx
db2022(#     from (select sec_id, count(*) as cnt
db2022(#         from takes
db2022(#         group by sec_id))
db2022-# select sec_id, count(*) as cnt
db2022-# from takes
db2022-# group by sec_id
db2022-# having cnt in (select mx from max_enroll);
 sec_id |  cnt
--------+-------
 1      | 25512
(1 row)

28. As in step 26, but now also include sections with no students taking them; the enrollment for such sections should be treated as 0. Do this in two different ways.
a. Using a scalar subquery;

select min(cnt), max(cnt)
from (
    select distinct sec_id,
        (select count(*)
        from takes
        where section.sec_id=takes.sec_id)
        as cnt
    from section);

b. Using aggregation on a left outer join (use the SQL natural left outer join syntax);

select min(cnt), max(cnt)
from (select count(grade) as cnt
    from section natural left outer join takes
    group by sec_id);

db2022=# select min(cnt), max(cnt)
db2022-# from (
db2022(#     select distinct sec_id,
db2022(#         (select count(*)
db2022(#         from takes
db2022(#         where section.sec_id=takes.sec_id)
db2022(#         as cnt
db2022(#     from section);
 min |  max
-----+-------
   0 | 25512
(1 row)
db2022=# select min(cnt), max(cnt)
db2022-# from (select count(grade) as cnt
db2022(#     from section natural left outer join takes
db2022(#     group by sec_id);
 min |  max
-----+-------
   0 | 25512
(1 row)

29. Find all courses whose title starts with the string “Comp”.

db2022=# select course_id, title
db2022-# from course
db2022-# where title like 'Comp%';
 course_id |           title
-----------+----------------------------
 814       | Compiler Design
 328       | Composition and Literature
 366       | Computational Biology
 781       | Compiler Design
 805       | Composition and Literature
 919       | Computability Theory
 877       | Composition and Literature
 352       | Compiler Design
 584       | Computability Theory
 348       | Compiler Design
(10 rows)

SQL练习 30-39

30. Find instructors who have taught all courses in Comp. Sci. department.
a. Using the “not exists … except …” structure;
注意要用instructor.ID，ID, t.ID都指t.ID。

select ID, name
from instructor as I
where not exists (
    (select course_id from course where dept_name='Comp. Sci.')
    except
    (select course_id from teaches as t where I.ID=t.ID));

b. Using matching of counts (don’t forget the distinct clause!).
如果用with cnt代替select count(*) from course where dept_name='Comp. Sci.'，则不能直接写cnt=...，还是要写(select tot from cnt)=...。

select ID, name
from instructor as I
where (select count(*) from course where dept_name='Comp. Sci.')=(select count(distinct course_id) from teaches as t where I.ID=t.ID);

db2022=# select ID, name
db2022-# from instructor as I
db2022-# where not exists (
db2022(#     (select course_id from course where dept_name='Comp. Sci.')
db2022(#     except
db2022(#     (select course_id from teaches as t where I.ID=t.ID));
  id   | name
-------+-------
 34175 | Bondi
(1 row)
db2022=# select * from teaches where ID=34175;
  id   | course_id | sec_id | semester | year
-------+-----------+--------+----------+------
 34175 | 747       | 1      | Spring   | 2004
 34175 | 274       | 1      | Fall     | 2002
 34175 | 571       | 1      | Spring   | 2004
 34175 | 539       | 1      | Spring   | 2022
 34175 | 949       | 1      | Spring   | 2022
 34175 | 647       | 1      | Spring   | 2022
 34175 | 584       | 1      | Spring   | 2022
 34175 | 276       | 1      | Spring   | 2022
 34175 | 359       | 1      | Spring   | 2022
 34175 | 284       | 1      | Spring   | 2022
(10 rows)
db2022=# select ID, name
db2022-# from instructor as I
db2022-# where (select count(*) from course where dept_name='Comp. Sci.')=(select count(distinct course_id) from teaches as t where I.ID=t.ID);
  id   | name
-------+-------
 34175 | Bondi
(1 row)

31. Insert each instructor as a student, with tot_creds = 0, in the same department.

db2022=# insert into student
db2022-#     select ID, name, dept_name, 0
db2022-#     from instructor;
INSERT 0 50
db2022=# select count(*) from instructor;
 count
-------
    50
(1 row)

32. Now delete all the newly added “students” above (note: already existing students who happened to have tot_creds = 0 should not get deleted).

db2022=# delete from student
db2022-#     where (ID, name) in (select ID, name from instructor);
DELETE 50

33. Some of you may have noticed that the tot_creds value for students did not match the credits from courses they have taken. Write and execute query to update tot_creds based on the credits passed, to bring the database back to consistency.

update student
set tot_cred=(
    select sum(credits)
    from takes natural join course
    where takes.id=student.id);
select sum(credits)
from takes natural join course
where id=13403;
select tot_cred
from student
where id=13403;

db2022=# update student
db2022-# set tot_cred=(
db2022(#     select sum(credits)
db2022(#     from takes natural join course
db2022(#     where takes.id=student.id);
UPDATE 2000
db2022=# select sum(credits)
db2022-# from takes natural join course
db2022-# where id=13403;
 sum
-----
  61
(1 row)
db2022=# select tot_cred
db2022-# from student
db2022-# where id=13403;
 tot_cred
----------
       61
(1 row)

34. Increase the salaries of instructors by 1000 times the number of course sections they have taught.

select salary from instructor where ID=34175;
select count(*) from teaches where ID=34175;
update instructor
set salary=salary+1000*
    (select count(*) from teaches where teaches.ID=instructor.ID);
select salary from instructor where ID=34175;

db2022=# select count(*) from teaches where ID=34175;
 count
-------
    10
(1 row)
db2022=# select salary from instructor where ID=34175;
  salary
-----------
 115469.11
(1 row)
db2022=# select count(*) from teaches where ID=34175;
 count
-------
    10
(1 row)
db2022=# update instructor
db2022-# set salary=salary+1000*
db2022-#     (select count(*) from teaches where teaches.ID=instructor.ID);
UPDATE 50
db2022=# select salary from instructor where ID=34175;
  salary
-----------
 125469.11
(1 row)

35. The university rules allow an F grade to be overridden by any pass grade (A, A-, B+, B, B-, C+, C, C-, D, D-). Now, create a view that lists information about all fail grades that have not been overridden (the view should contain all attributes from the takes relation).

db2022=# create view failed_takes as
db2022-#     select * from takes
db2022-#     where grade='F';
CREATE VIEW
db2022=# select *
db2022-# from failed_takes;
  id   | course_id | sec_id | semester | year | grade
-------+-----------+--------+----------+------+-------
 79446 | 808       | 1      | Fall     | 2003 | F
 99710 | 169       | 2      | Fall     | 2002 | F
 56598 | 192       | 1      | Fall     | 2002 | F
 41890 | 599       | 1      | Spring   | 2003 | F
 ...
 52019 | 200       | 2      | Fall     | 2002 | F
 19321 | 200       | 2      | Fall     | 2002 | F
(961 rows)

36. Find all students who have 2 or more non-overridden F grades, and list them along with the F grades.

db2022=# select ID, name, 'F' as grade
from student
where 1<(
    select count(*)
    from failed_takes
    where failed_takes.ID=student.ID)
order by ID;
  id   |    name     | grade
-------+-------------+-------
 1018  | Colin       | F
 10481 | Grosch      | F
 107   | Shabuno     | F
 ...
 99710 | Savolainen  | F
 99949 | Samo        | F
(176 rows)
db2022=# select * from failed_takes where ID=1018;
  id  | course_id | sec_id | semester | year | grade
------+-----------+--------+----------+------+-------
 1018 | 274       | 1      | Fall     | 2002 | F
 1018 | 599       | 1      | Spring   | 2003 | F
(2 rows)

37. Grades are mapped to a grade point as follows.
Create a table to store these mappings, and write a query to find the GPA (Grade Point Average) of each student, using this table. Make sure students who have not got a non-null grade in any course are displayed with a GPA of null.
'A '和'A'是不一样的，而原表中用的'A ', 'B ', 'C ', 'D ', 'F'，所以必须要一致才可正确比较？
原表中有A+，给的表没有。

create table grade_point
    (grade varchar(2),
    point numeric(2,1),
    primary key (grade));
insert into grade_point values
    ('A+', 4.3), ('A ', 4.0), ('A-', 3.7), ('B+', 3.3), ('B ', 3.0), ('B-', 2.7), ('C+', 2.3), ('C ', 2.0), ('C-', 1.5), ('D ', 1.3), ('D-', 1.0), ('F', 0);
select * from grade_point;
create or replace function gradeToPoint(in g varchar(2))
returns numeric(2,1) as $$
    declare
        res numeric(2,1);
    begin
        select point into res
        from grade_point
        where grade=g;
        return res;
    end;
       $$ LANGUAGE plpgsql;
select ID, sum(gradeToPoint(coalesce(grade, 'F')))/(select count(*) as GPA from takes as S where S.ID=T.ID)
from takes as T
group by ID;

db2022=# create table grade_point
db2022-#     (grade varchar(2),
db2022(#     point numeric(2,1),
db2022(#     primary key (grade));
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "grade_point_pkey" for table "grade_point"
CREATE TABLE
db2022=# insert into grade_point values
db2022-#     ('A+', 4.3), ('A ', 4.0), ('A-', 3.7), ('B+', 3.3), ('B ', 3.0), ('B-', 2.7), ('C+', 2.3), ('C ', 2.0), ('C-', 1.5), ('D ', 1.3), ('D-', 1.0), ('F', 0);
INSERT 0 12
db2022=# select * from grade_point;
 grade | point
-------+-------
 A+    |   4.3
 A     |   4.0
 A-    |   3.7
 B+    |   3.3
 B     |   3.0
 B-    |   2.7
 C+    |   2.3
 C     |   2.0
 C-    |   1.5
 D     |   1.3
 D-    |   1.0
 F     |   0.0
(12 rows)
db2022=# create or replace function gradeToPoint(in g varchar(2))
db2022-# returns numeric(2,1) as $$
db2022$#     declare
db2022$#         res numeric(2,1);
db2022$#     begin
db2022$#         select point into res
db2022$#         from grade_point
db2022$#         where grade=g;
db2022$#         return res;
db2022$#     end;
db2022$#        $$ LANGUAGE plpgsql;
CREATE FUNCTION
db2022=# select ID, sum(gradeToPoint(coalesce(grade, 'F')))/(select count(*) as GPA from takes as S where S.ID=T.ID)
db2022-# from takes as T
db2022-# group by ID;
  id   |      ?column?
-------+--------------------
 3039  | 2.8000000000000000
 86327 | 2.9333333333333333
 22050 | 2.9272727272727273
...
 53496 | 3.0750000000000000
 25785 | 3.2533333333333333
(2000 rows)

38. Find all classrooms that have been assigned to more than one section at the same time. Display the rooms along with the assigned sections; Using a with clause or a view to simplify this query.

with conflicted(building, room_number) as
    (select building, room_number
    from classroom as C
    where 1<(
        select count(*) from section as S
        where S.building=C.building and S.room_number=C.room_number))
select building, room_number, course_id, sec_id, semester, year
from section natural join conflicted
order by building, room_number;

db2022=# with conflicted(building, room_number) as
db2022-#     (select building, room_number
db2022(#     from classroom as C
db2022(#     where 1<(
db2022(#         select count(*) from section as S
db2022(#         where S.building=C.building and S.room_number=C.room_number))
db2022-# select building, room_number, course_id, sec_id, semester, year
db2022-# from section natural join conflicted
db2022-# order by building, room_number;
 building  | room_number | course_id | sec_id | semester | year
-----------+-------------+-----------+--------+----------+------
 Alumni    | 547         | 362       | 2      | Fall     | 2006
 Alumni    | 547         | 445       | 1      | Spring   | 2001
 Alumni    | 547         | 581       | 1      | Spring   | 2005
 Bronfman  | 700         | 604       | 1      | Spring   | 2009
 Bronfman  | 700         | 362       | 3      | Spring   | 2008
 ...
 Whitman   | 434         | 482       | 1      | Fall     | 2021
 Whitman   | 434         | 482       | 1      | Fall     | 2005
(114 rows)

39. Create a view faculty showing only the ID, name, and department of instructors.

db2022=# create view faculty as
db2022-#     select ID, name, dept_name
db2022-#     from instructor;
CREATE VIEW
db2022=# select * from faculty;
  id   |       name        |  dept_name
-------+-------------------+-------------
 63395 | McKinnon          | Cybernetics
 78699 | Pingr             | Statistics
 ...
 99052 | Dale              | Cybernetics
(50 rows)

SQL练习 40-43

40. Create a view CSinstructors, showing all information about instructors from the Comp. Sci. department.

db2022=# create view CSinstructors as
db2022-#     select ID, name, dept_name, salary
db2022-#     from instructor
db2022-#     where dept_name='Comp. Sci.';
CREATE VIEW
db2022=# select * from CSinstructors;
  id   |   name   | dept_name  |  salary
-------+----------+------------+-----------
 34175 | Bondi    | Comp. Sci. | 125469.11
 33351 | Bourrier | Comp. Sci. |  91797.83
(2 rows)

41. Insert appropriate tuple into each of the views faculty and CSinstructors, to see what updates your database allows on views; explain what happens.
不能直接插入，必须声明有instead of的触发器，指定insert时要干什么。

insert into faculty values (0, 'new', 'Comp. Sci.');
create or replace rule r1_faculty_insert as
    on insert to faculty do instead
    insert into instructor values(new.ID, new.name, new.dept_name)
    returning instructor.ID, instructor.name, instructor.dept_name;
insert into faculty values (0, 'new', 'Comp. Sci.');
select * from faculty order by ID;
create or replace rule r1_CSinstructors_insert as
    on insert to CSinstructors do instead
    insert into instructor values(new.ID, new.name, new.dept_name, new.salary);
insert into CSinstructors values
    (0, 'new', 'Unknown Dept', 100);
insert into CSinstructors values
    (0, 'new', 'Unknown Dept', 100000);
insert into CSinstructors values
    (1, 'new', 'Unknown Dept', 100000);
insert into CSinstructors values
    (1, 'new', 'Biology', 100000);
insert into CSinstructors values
    (2, 'new', 'Comp. Sci.', 100000);
select * from CSinstructors order by ID;

对CSinstructors的前三条插入均失败，第一条不满足salary范围限制，第二条0重复，第三条违反instructor对department的外键引用。
第四条成功，但其实不满足视图的where子句，需要自己在rule中修改。
第五条成功。

db2022=# insert into faculty values
db2022-#     (0, 'new', 'Comp. Sci.');
ERROR:  cannot insert into view "faculty"
HINT:  You need an unconditional ON INSERT DO INSTEAD rule or an INSTEAD OF INSERT trigger.
db2022=# create or replace rule r1_faculty_insert as
db2022-#     on insert to faculty do instead
db2022-#     insert into instructor values(new.ID, new.name, new.dept_name)
db2022-#     returning instructor.ID, instructor.name, instructor.dept_name;
CREATE RULE
db2022=# insert into faculty values (0, 'new', 'Comp. Sci.');
INSERT 0 1
db2022=# select * from faculty order by ID;
  id   |       name        |  dept_name
-------+-------------------+-------------
 0     | new               | Comp. Sci.
 14365 | Lembr             | Accounting
 15347 | Bawa              | Athletics
 ...
db2022=# create or replace rule r1_CSinstructors_insert as
db2022-#     on insert to CSinstructors do instead
db2022-#     insert into instructor values(new.ID, new.name, new.dept_name, new.salary)
db2022-# ;
CREATE RULE
db2022=# insert into CSinstructors values
db2022-#     (0, 'new', 'Unknown Dept', 100);
ERROR:  new row for relation "instructor" violates check constraint "instructor_salary_check"
DETAIL:  N/A
db2022=# insert into CSinstructors values
    (0, 'new', 'Unknown Dept', 100000);
ERROR:  duplicate key value violates unique constraint "instructor_pkey"
DETAIL:  Key (id)=(0) already exists.
db2022=# insert into CSinstructors values
db2022-#     (1, 'new', 'Unknown Dept', 100000);
ERROR:  insert or update on table "instructor" violates foreign key constraint "instructor_dept_name_fkey"
DETAIL:  Key (dept_name)=(Unknown Dept) is not present in table "department".
db2022=# insert into CSinstructors values
db2022-#     (1, 'new', 'Biology', 100000);
INSERT 0 1

42. Create a new user and grant permission to the user to view all data in your student relation.

create user user1 with password 'Password123';
grant select on student to user1;
gsql -d db2022 -U user1 -W 'Password123' -r
select * from student;
select * from faculty;

db2022=# create user user1 with password 'Password123';
CREATE ROLE
db2022=# grant select on student to user1;
GRANT
db2022=# \q
omm@97375c3acbd1:/$ gsql -d db2022 -U user1 -W 'Password123' -r
gsql ((openGauss 2.1.0 build 590b0f8e) compiled at 2021-09-30 14:29:04 commit 0 last mr  )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.
db2022=> select * from student;
  id   |        name        |  dept_name  | tot_cred
-------+--------------------+-------------+----------
 5925  | Maw                | Languages   |       44
 25611 | Sve                | English     |       57
 94569 | Yip                | English     |       46
 ...
db2022=> select * from faculty;
ERROR:  permission denied for relation faculty
DETAIL:  N/A

43. Now grant permission to all users to see all data in your faculty view.

grant select on faculty to public;
select * from faculty;

db2022=> grant select on faculty to public;
ERROR:  permission denied for relation faculty
DETAIL:  N/A
db2022=> \q
omm@97375c3acbd1:/$ gsql -d db2022 -r
gsql ((openGauss 2.1.0 build 590b0f8e) compiled at 2021-09-30 14:29:04 commit 0 last mr  )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.
db2022=# grant select on faculty to public;
GRANT
db2022=# \q
omm@97375c3acbd1:/$ gsql -d db2022 -U user1 -W 'Password123' -r
gsql ((openGauss 2.1.0 build 590b0f8e) compiled at 2021-09-30 14:29:04 commit 0 last mr  )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.
db2022=> select * from faculty;
  id   |       name        |  dept_name
-------+-------------------+-------------
 63395 | McKinnon          | Cybernetics
 78699 | Pingr             | Statistics
 ...