SQL查询实战：大学数据库

SQL查询实战：大学数据库

收录于合集
#技术23个
#数据库2个

hello 大家好，我是升哥。

最近在学数据库，把重要主题笔记分享给大家。

这次是大学数据库SQL查询实战，在MySQL上测试通过。

关于导入数据库用到的SQL文件：

  • 后台输入1002获取DDL.sql
  • 后台输入1003获取smallRelationsInsertFile.sql

目录：

  • 导入数据

  • The select Clause

  • The where Clause

  • The from Clause

    • join原理

    • inner join 与 outer join

  • The Rename Operation

  • String Operations

  • Ordering the Display of  Tuples

  • Where Clause Predicates

  • Set Operations

  • Null Values

  • Aggregate Functions

  • Nested Subqueries嵌套子查询

    • Set Membership

    • Set Comparison

    • Test for Empty Relations

    • 相关子查询

    • Subqueries in the From Clause

    • With Clause

    • Scalar Subquery

  • 特殊例子

  • Modification of the Database

    • Deletion

    • Insertion

    • Updates

 

导入数据

在MySQL中导入DDL.sql，DDL.sql具体内容如下：

create table classroom
	(
        building		varchar(15),
        room_number		varchar(7),
        capacity numeric(4,0),
        primary key (building, room_number)
	);

create table department
	(
        dept_name		varchar(20), 
        building		varchar(15), 
        budget		        numeric(12,2) check (budget > 0),
        primary key (dept_name)
    );

create table course
	(
        course_id		varchar(8), 
        title			varchar(50), 
        dept_name		varchar(20),
        credits		numeric(2,0) check (credits > 0),
        primary key (course_id),
        foreign key (dept_name) references department (dept_name)
        on delete set null
	);

create table instructor
	(
        ID varchar(5), 
        name varchar(20) not null, 
        dept_name		varchar(20), 
        salary			numeric(8,2) check (salary > 29000),
        primary key (ID),
        foreign key (dept_name) references department (dept_name)
        on delete set null
	);

create table section
	(
        course_id		varchar(8), 
        sec_id			varchar(8),
        semester		varchar(6)
        check (semester in ('Fall', 'Winter', 'Spring', 'Summer')), 
        year numeric(4,0) check (year > 1701andyear < 2100), 
        building		varchar(15),
        room_number		varchar(7),
        time_slot_id		varchar(4),
        primary key (course_id, sec_id, semester, year),
        foreign key (course_id) references course (course_id)
        on delete cascade,
        foreign key (building, room_number) references classroom (building, room_number)
        on delete set null
	);

create table teaches
	(
        ID varchar(5), 
        course_id		varchar(8),
        sec_id			varchar(8), 
        semester		varchar(6),
        year numeric(4,0),
        primary key (ID, course_id, sec_id, semester, year),
        foreign key (course_id, sec_id, semester, year) references section (course_id, sec_id, semester, year)
        on delete cascade,
        foreign key (ID) references instructor (ID)
        on delete cascade
	);

create table student
	(
        ID varchar(5), 
        name varchar(20) not null, 
        dept_name		varchar(20), 
        tot_cred		numeric(3,0) check (tot_cred >= 0),
        primary key (ID),
        foreign key (dept_name) references department (dept_name)
        on delete set null
	);

create table takes
	(
        ID varchar(5), 
        course_id		varchar(8),
        sec_id			varchar(8), 
        semester		varchar(6),
        year numeric(4,0),
        grade		        varchar(2),
        primary key (ID, course_id, sec_id, semester, year),
        foreign key (course_id, sec_id, semester, year) references section (course_id, sec_id, semester, year)
        on delete cascade,
        foreign key (ID) references student (ID)
        on delete cascade
	);

create table advisor
	(
        s_ID			varchar(5),
        i_ID			varchar(5),
        primary key (s_ID),
        foreign key (i_ID) references instructor (ID)
        on delete set null,
        foreign key (s_ID) references student (ID)
        on delete cascade
	);

create table time_slot
	(
        time_slot_id		varchar(4),
        day varchar(1),
        start_hr		numeric(2) check (start_hr >= 0 and start_hr < 24),
        start_min		numeric(2) check (start_min >= 0 and start_min < 60),
        end_hr			numeric(2) check (end_hr >= 0 and end_hr < 24),
        end_min		numeric(2) check (end_min >= 0 and end_min < 60),
        primary key (time_slot_id, day, start_hr, start_min)
	);

create table prereq
	(
        course_id		varchar(8), 
        prereq_id		varchar(8),
        primary key (course_id, prereq_id),
        foreign key (course_id) references course (course_id)
        on delete cascade,
        foreign key (prereq_id) references course (course_id)
	);

图片

The select Clause

Find the department names of all instructors, and remove duplicates

select distinct dept_name
from instructor;

The where Clause

To find all instructors' names in Comp. Sci. dept with salary > 80000

select name
from instructor
where dept_name = 'Comp. Sci.'and salary > 80000;

The from Clause

Find the names of all instructors who have taught some course and the course_id

select name, course_id
from instructor , teaches
where instructor.ID = teaches.ID;

join原理

join原理的伪代码实现：

select *
from r1, r2, r3 .......;
for(tuple t1 : r1)
    for(tuple t2 : r2)
        for(tuple t3 : r3)
            ......
            tuple t = t1 拼接 t2 拼接 t3 ......
            r.push_back(t);

join的一个实例：

select *
from instructor;
+-------+------------+------------+----------+
| ID    | name       | dept_name  | salary   |
+-------+------------+------------+----------+
| 10101 | Srinivasan | Comp. Sci. | 65000.00 |
| 12121 | Wu         | Finance    | 90000.00 |
| 15151 | Mozart     | Music      | 40000.00 |
| 22222 | Einstein   | Physics    | 95000.00 |
| 32343 | El Said    | History    | 60000.00 |
| 33456 | Gold       | Physics    | 87000.00 |
| 45565 | Katz       | Comp. Sci. | 75000.00 |
| 58583 | Califieri  | History    | 62000.00 |
| 76543 | Singh      | Finance    | 80000.00 |
| 76766 | Crick      | Biology    | 72000.00 |
| 83821 | Brandt     | Comp. Sci. | 92000.00 |
| 98345 | Kim        | Elec. Eng. | 80000.00 |
+-------+------------+------------+----------+
select *
from department;
+------------+----------+-----------+
| dept_name  | building | budget    |
+------------+----------+-----------+
| Biology    | Watson   |  90000.00 |
| Comp. Sci. | Taylor   | 100000.00 |
| Elec. Eng. | Taylor   |  85000.00 |
| Finance    | Painter  | 120000.00 |
| History    | Painter  |  50000.00 |
| Music      | Packard  |  80000.00 |
| Physics    | Watson   |  70000.00 |
+------------+----------+-----------+
select *
from instructor,department;
+-------+------------+------------+----------+------------+----------+-----------+
| ID    | name       | dept_name  | salary   | dept_name  | building | budget    |
+-------+------------+------------+----------+------------+----------+-----------+
| 10101 | Srinivasan | Comp. Sci. | 65000.00 | Physics    | Watson   |  70000.00 |
| 10101 | Srinivasan | Comp. Sci. | 65000.00 | Music      | Packard  |  80000.00 |
| 10101 | Srinivasan | Comp. Sci. | 65000.00 | History    | Painter  |  50000.00 |
| 10101 | Srinivasan | Comp. Sci. | 65000.00 | Finance    | Painter  | 120000.00 |
| 10101 | Srinivasan | Comp. Sci. | 65000.00 | Elec. Eng. | Taylor   |  85000.00 |
| 10101 | Srinivasan | Comp. Sci. | 65000.00 | Comp. Sci. | Taylor   | 100000.00 |
| 10101 | Srinivasan | Comp. Sci. | 65000.00 | Biology    | Watson   |  90000.00 |
| 12121 | Wu         | Finance    | 90000.00 | Physics    | Watson   |  70000.00 |
| 12121 | Wu         | Finance    | 90000.00 | Music      | Packard  |  80000.00 |
| 12121 | Wu         | Finance    | 90000.00 | History    | Painter  |  50000.00 |
| 12121 | Wu         | Finance    | 90000.00 | Finance    | Painter  | 120000.00 |
| 12121 | Wu         | Finance    | 90000.00 | Elec. Eng. | Taylor   |  85000.00 |
| 12121 | Wu         | Finance    | 90000.00 | Comp. Sci. | Taylor   | 100000.00 |
| 12121 | Wu         | Finance    | 90000.00 | Biology    | Watson   |  90000.00 |
| 15151 | Mozart     | Music      | 40000.00 | Physics    | Watson   |  70000.00 |
| 15151 | Mozart     | Music      | 40000.00 | Music      | Packard  |  80000.00 |
| 15151 | Mozart     | Music      | 40000.00 | History    | Painter  |  50000.00 |
| 15151 | Mozart     | Music      | 40000.00 | Finance    | Painter  | 120000.00 |
| 15151 | Mozart     | Music      | 40000.00 | Elec. Eng. | Taylor   |  85000.00 |
| 15151 | Mozart     | Music      | 40000.00 | Comp. Sci. | Taylor   | 100000.00 |
| 15151 | Mozart     | Music      | 40000.00 | Biology    | Watson   |  90000.00 |
| 22222 | Einstein   | Physics    | 95000.00 | Physics    | Watson   |  70000.00 |
| 22222 | Einstein   | Physics    | 95000.00 | Music      | Packard  |  80000.00 |
| 22222 | Einstein   | Physics    | 95000.00 | History    | Painter  |  50000.00 |
| 22222 | Einstein   | Physics    | 95000.00 | Finance    | Painter  | 120000.00 |
| 22222 | Einstein   | Physics    | 95000.00 | Elec. Eng. | Taylor   |  85000.00 |
| 22222 | Einstein   | Physics    | 95000.00 | Comp. Sci. | Taylor   | 100000.00 |
| 22222 | Einstein   | Physics    | 95000.00 | Biology    | Watson   |  90000.00 |
| 32343 | El Said    | History    | 60000.00 | Physics    | Watson   |  70000.00 |
| 32343 | El Said    | History    | 60000.00 | Music      | Packard  |  80000.00 |
| 32343 | El Said    | History    | 60000.00 | History    | Painter  |  50000.00 |
| 32343 | El Said    | History    | 60000.00 | Finance    | Painter  | 120000.00 |
| 32343 | El Said    | History    | 60000.00 | Elec. Eng. | Taylor   |  85000.00 |
| 32343 | El Said    | History    | 60000.00 | Comp. Sci. | Taylor   | 100000.00 |
| 32343 | El Said    | History    | 60000.00 | Biology    | Watson   |  90000.00 |
| 33456 | Gold       | Physics    | 87000.00 | Physics    | Watson   |  70000.00 |
| 33456 | Gold       | Physics    | 87000.00 | Music      | Packard  |  80000.00 |
| 33456 | Gold       | Physics    | 87000.00 | History    | Painter  |  50000.00 |
| 33456 | Gold       | Physics    | 87000.00 | Finance    | Painter  | 120000.00 |
| 33456 | Gold       | Physics    | 87000.00 | Elec. Eng. | Taylor   |  85000.00 |
| 33456 | Gold       | Physics    | 87000.00 | Comp. Sci. | Taylor   | 100000.00 |
| 33456 | Gold       | Physics    | 87000.00 | Biology    | Watson   |  90000.00 |
| 45565 | Katz       | Comp. Sci. | 75000.00 | Physics    | Watson   |  70000.00 |
| 45565 | Katz       | Comp. Sci. | 75000.00 | Music      | Packard  |  80000.00 |
| 45565 | Katz       | Comp. Sci. | 75000.00 | History    | Painter  |  50000.00 |
| 45565 | Katz       | Comp. Sci. | 75000.00 | Finance    | Painter  | 120000.00 |
| 45565 | Katz       | Comp. Sci. | 75000.00 | Elec. Eng. | Taylor   |  85000.00 |
| 45565 | Katz       | Comp. Sci. | 75000.00 | Comp. Sci. | Taylor   | 100000.00 |
| 45565 | Katz       | Comp. Sci. | 75000.00 | Biology    | Watson   |  90000.00 |
| 58583 | Califieri  | History    | 62000.00 | Physics    | Watson   |  70000.00 |
| 58583 | Califieri  | History    | 62000.00 | Music      | Packard  |  80000.00 |
| 58583 | Califieri  | History    | 62000.00 | History    | Painter  |  50000.00 |
| 58583 | Califieri  | History    | 62000.00 | Finance    | Painter  | 120000.00 |
| 58583 | Califieri  | History    | 62000.00 | Elec. Eng. | Taylor   |  85000.00 |
| 58583 | Califieri  | History    | 62000.00 | Comp. Sci. | Taylor   | 100000.00 |
| 58583 | Califieri  | History    | 62000.00 | Biology    | Watson   |  90000.00 |
| 76543 | Singh      | Finance    | 80000.00 | Physics    | Watson   |  70000.00 |
| 76543 | Singh      | Finance    | 80000.00 | Music      | Packard  |  80000.00 |
| 76543 | Singh      | Finance    | 80000.00 | History    | Painter  |  50000.00 |
| 76543 | Singh      | Finance    | 80000.00 | Finance    | Painter  | 120000.00 |
| 76543 | Singh      | Finance    | 80000.00 | Elec. Eng. | Taylor   |  85000.00 |
| 76543 | Singh      | Finance    | 80000.00 | Comp. Sci. | Taylor   | 100000.00 |
| 76543 | Singh      | Finance    | 80000.00 | Biology    | Watson   |  90000.00 |
| 76766 | Crick      | Biology    | 72000.00 | Physics    | Watson   |  70000.00 |
| 76766 | Crick      | Biology    | 72000.00 | Music      | Packard  |  80000.00 |
| 76766 | Crick      | Biology    | 72000.00 | History    | Painter  |  50000.00 |
| 76766 | Crick      | Biology    | 72000.00 | Finance    | Painter  | 120000.00 |
| 76766 | Crick      | Biology    | 72000.00 | Elec. Eng. | Taylor   |  85000.00 |
| 76766 | Crick      | Biology    | 72000.00 | Comp. Sci. | Taylor   | 100000.00 |
| 76766 | Crick      | Biology    | 72000.00 | Biology    | Watson   |  90000.00 |
| 83821 | Brandt     | Comp. Sci. | 92000.00 | Physics    | Watson   |  70000.00 |
| 83821 | Brandt     | Comp. Sci. | 92000.00 | Music      | Packard  |  80000.00 |
| 83821 | Brandt     | Comp. Sci. | 92000.00 | History    | Painter  |  50000.00 |
| 83821 | Brandt     | Comp. Sci. | 92000.00 | Finance    | Painter  | 120000.00 |
| 83821 | Brandt     | Comp. Sci. | 92000.00 | Elec. Eng. | Taylor   |  85000.00 |
| 83821 | Brandt     | Comp. Sci. | 92000.00 | Comp. Sci. | Taylor   | 100000.00 |
| 83821 | Brandt     | Comp. Sci. | 92000.00 | Biology    | Watson   |  90000.00 |
| 98345 | Kim        | Elec. Eng. | 80000.00 | Physics    | Watson   |  70000.00 |
| 98345 | Kim        | Elec. Eng. | 80000.00 | Music      | Packard  |  80000.00 |
| 98345 | Kim        | Elec. Eng. | 80000.00 | History    | Painter  |  50000.00 |
| 98345 | Kim        | Elec. Eng. | 80000.00 | Finance    | Painter  | 120000.00 |
| 98345 | Kim        | Elec. Eng. | 80000.00 | Elec. Eng. | Taylor   |  85000.00 |
| 98345 | Kim        | Elec. Eng. | 80000.00 | Comp. Sci. | Taylor   | 100000.00 |
| 98345 | Kim        | Elec. Eng. | 80000.00 | Biology    | Watson   |  90000.00 |
+-------+------------+------------+----------+------------+----------+-----------+

简单验证一下：

  • select count(*) from instructor;得12
  • select count(*) from department;得7
  • select count(*) from instructor , department;得84

，符合笛卡尔乘积。

现在我们加上限制：

select name, i.dept_name, building
from instructor i , department d
where i.dept_name = d.dept_name;
+------------+------------+----------+
| name       | dept_name  | building |
+------------+------------+----------+
| Crick      | Biology    | Watson   |
| Srinivasan | Comp. Sci. | Taylor   |
| Katz       | Comp. Sci. | Taylor   |
| Brandt     | Comp. Sci. | Taylor   |
| Kim        | Elec. Eng. | Taylor   |
| Wu         | Finance    | Painter  |
| Singh      | Finance    | Painter  |
| El Said    | History    | Painter  |
| Califieri  | History    | Painter  |
| Mozart     | Music      | Packard  |
| Einstein   | Physics    | Watson   |
| Gold       | Physics    | Watson   |
+------------+------------+----------+

inner join 与 outer join

Outer Join：An extension of the join operation that avoids loss of information. Computes the join and then adds tuples form one relation that does not match tuples in the other relation to the result of the join.

对比如下：

select *
from course inner join prereq on course.course_id = prereq.course_id;
+-----------+---------------------------+------------+---------+-----------+-----------+
| course_id | title                     | dept_name  | credits | course_id | prereq_id |
+-----------+---------------------------+------------+---------+-----------+-----------+
| BIO-301   | Genetics                  | Biology    |       4 | BIO-301   | BIO-101   |
| BIO-399   | Computational Biology     | Biology    |       3 | BIO-399   | BIO-101   |
| CS-190    | Game Design               | Comp. Sci. |       4 | CS-190    | CS-101    |
| CS-315    | Robotics                  | Comp. Sci. |       3 | CS-315    | CS-101    |
| CS-319    | Image Processing          | Comp. Sci. |       3 | CS-319    | CS-101    |
| CS-347    | Database System Concepts  | Comp. Sci. |       3 | CS-347    | CS-101    |
| EE-181    | Intro. to Digital Systems | Elec. Eng. |       3 | EE-181    | PHY-101   |
+-----------+---------------------------+------------+---------+-----------+-----------+
select *
from course left outer join prereq on course.course_id = prereq.course_id;
+-----------+----------------------------+------------+---------+-----------+-----------+
| course_id | title                      | dept_name  | credits | course_id | prereq_id |
+-----------+----------------------------+------------+---------+-----------+-----------+
| BIO-101   | Intro. to Biology          | Biology    |       4 | NULL      | NULL      |
| BIO-301   | Genetics                   | Biology    |       4 | BIO-301   | BIO-101   |
| BIO-399   | Computational Biology      | Biology    |       3 | BIO-399   | BIO-101   |
| CS-101    | Intro. to Computer Science | Comp. Sci. |       4 | NULL      | NULL      |
| CS-190    | Game Design                | Comp. Sci. |       4 | CS-190    | CS-101    |
| CS-315    | Robotics                   | Comp. Sci. |       3 | CS-315    | CS-101    |
| CS-319    | Image Processing           | Comp. Sci. |       3 | CS-319    | CS-101    |
| CS-347    | Database System Concepts   | Comp. Sci. |       3 | CS-347    | CS-101    |
| EE-181    | Intro. to Digital Systems  | Elec. Eng. |       3 | EE-181    | PHY-101   |
| FIN-201   | Investment Banking         | Finance    |       3 | NULL      | NULL      |
| HIS-351   | World History              | History    |       3 | NULL      | NULL      |
| MU-199    | Music Video Production     | Music      |       3 | NULL      | NULL      |
| PHY-101   | Physical Principles        | Physics    |       4 | NULL      | NULL      |
+-----------+----------------------------+------------+---------+-----------+-----------+

The Rename Operation

Find the names of all instructors who have a higher salary than some instructor in 'Comp. Sci'.

select distinct T.name
from instructor as T, instructor as S
where T.salary > S.salary and S.dept_name = 'Comp. Sci.';

String Operations

Find the names of all instructors whose name includes the substring “dar”.

select name
from instructor
where name like'%dar%';

Pattern matching examples:

  • 'Intro%' matches any string beginning with “Intro”.
  • '%Comp%' matches any string containing “Comp” as a substring.
  • '_ _ _' matches any string of exactly three characters.
  • '_ _ _ %' matches any string of at least three characters.

Ordering the Display of  Tuples

List in reverse alphabetic order the names of all instructors

select distinct name
from instructor
order by name desc;

Where Clause Predicates

Find the names of all instructors with salary between 100,000 (that is, ≥ 100,000)

select name
from instructor
where salary between 90000 and 100000;

Set Operations

  • union
  • intersect
  • except

Find courses that ran in Fall 2017 but not in Spring 2018

(select course_id from section where semester = 'Fall' and year = 2017)
except
(select course_id from section where semester = 'Spring' and year = 2018);

有些数据库不支持关键字except，改成minus

(select course_id from section where semester = 'Fall' and year = 2017)
minus
(select course_id from section where semester = 'Spring' and year = 2018);

Null Values

  • The result of any arithmetic expression involving null is null，（Example: 5 + null returns null）
  • SQL treats as unknown the result of any comparison involving a null value （Example*: 5 <* null or null <> null or null = null）
  • instead, use predicates is null and is not null

Find all instructors whose salary is null

select name
from instructor
where salary is null

TRUE > UNKNOWN > FALSE：

  • (true and unknown) = unknown

  • (false and unknown) = false

  • (unknown and unknown) = unknown

  • (unknown or true) = true

  • (unknown or false) = unknown

  • (unknown or unknown) = unknown

Aggregate Functions

Find the total number of instructors who teach a course in the Spring 2018 semester

select count(distinct ID)
    from teaches
    where semester = 'Spring' and year = 2018;

Find the average salary of instructors in each department

select dept_name, avg(salary) as avg_salary
from instructor
group by dept_name;

Find the names and average salaries of all departments whose average salary is greater than 42000

select dept_name, avg (salary) as avg_salary
from instructor
group by dept_name
having avg (salary) > 42000;

Nested Subqueries嵌套子查询

Set Membership

Name all instructors whose name is neither “Mozart” nor Einstein”

select distinct name
from instructor
where name not in ('Mozart', 'Einstein');

Find courses offered in Fall 2017 and in Spring 2018

select distinct course_id
from section
where semester = 'Fall' and year= 2017 and
course_id in (	
    select course_id
    from section
    where semester = 'Spring' and year= 2018
);

或者

select course_id
from section as S
where semester = 'Fall' and year = 2017 and
exists (
    select *
    from section as T
    where semester = 'Spring'and year = 2018
    and S.course_id = T.course_id
);

Set Comparison

Find names of instructors with salary greater than that of some (at least one) instructor in the Biology department.

select distinct T.name
from instructor as T, instructor as S
where T.salary > S.salary and S.dept_name = 'Biology';

或者：

select name
from instructor
where salary > some (
    select salary
    from instructor
    where dept_name = 'Biology'
);

Find the names of all instructors whose salary is greater than the salary of all instructors in the Biology department.

select name
from instructor
where salary > all (
    select salary
    from instructor
    where dept_name = 'Biology'
);

Test for Empty Relations

Find all students who have taken all courses offered in the Biology department.

select distinct S.ID, S.name
from student as S
where not exists ( 
    (select course_id
     from course
     where dept_name = 'Biology')
    except
    (select T.course_id
     from takes as T
     where S.ID = T.ID)
);

相关子查询

  • 相关子查询：内部子查询不独立，比如借用到外层的表。
  • 不相关子查询：内部子查询独立。

不相关子查询可以理解为先执行内部独立的子查询，再和外部查询。

相关子查询举一个例子：

SELECT *
FROM section a
WHERE sec_id = 1 AND EXISTS(
	SELECT *
	FROM section b
	WHERE sec_id = 2 and a.course_id = b.course_id
);

我们知道WHERE是以行为单位进行保留或去除，比如在处理section表的第一行时：

+-----------+--------+----------+------+----------+-------------+--------------+
| course_id | sec_id | semester | year | building | room_number | time_slot_id |
+-----------+--------+----------+------+----------+-------------+--------------+
| BIO-101   | 1      | Summer   | 2017 | Painter  | 514         | B            |
+-----------+--------+----------+------+----------+-------------+--------------+

子查询“退化”为：

SELECT *
FROM section b
WHERE sec_id = 2 and 'BIO-101' = b.course_id;

查询结果为：

Empty set (0.00 sec)

为空，EXISTS返回FALSE，这一行被去除。

以此类推，在处理每一行时都要进行一次子查询，效率较低。

Subqueries in the From Clause

Find the average instructors’ salaries of those departments where the average salary is greater than $42,000.”

select dept_name, avg (salary) as avg_salary
from instructor
group by dept_name
having avg (salary) > 42000;

或者：

select dept_name, avg_salary
from ( 
    select dept_name, avg (salary) as avg_salary
    from instructor
    group by dept_name
) tmp
where avg_salary > 42000;

With Clause

Find all departments with the maximum budget

with max_budget (value) as
(
    selectmax(budget)
    from department
)
select department.dept_name
from department, max_budget
where department.budget = max_budget.value;

Find all departments where the total salary is greater than the average of the total salary at all departments

with dept_total (dept_name, value) as
(
    select dept_name, sum(salary)
    from instructor
    groupby 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;

Scalar Subquery

List all departments along with the number of instructors in each department

select dept_name, count(*) as num_instructors
from instructor
group by dept_name;

或者：

select dept_name,
( 
    selectcount(*)
    from instructor
    where department.dept_name = instructor.dept_name
) as num_instructors
from department;

特殊例子

select '437' as FOO;
+-----+
| FOO |
+-----+
| 437 |
+-----+
select 'A'
from instructor;
+---+
| A |
+---+
| A |
| A |
| A |
| A |
| A |
| A |
| A |
| A |
| A |
| A |
| A |
| A |
+---+

Modification of the Database

Deletion

Delete all instructors

delete from instructor;

Delete all instructors from the Finance department

delete from instructor
where dept_name= 'Finance';

Insertion

Add a new tuple to course

insert into course (course_id, title, dept_name, credits)
values ('CS-437', 'Database Systems', 'Comp. Sci.', 4);

Make each student in the Music department who has earned more than 144 credit hours an instructor in the Music department with a salary of $18,000.

insert into instructor
select ID, name, dept_name, 18000
from student
where dept_name = 'Music' and total_cred > 144;

Updates

Give a 5% salary raise to instructors whose salary is less than average

update instructor
set salary = salary * 1.05
where salary < (
    select avg (salary)
    from instructor
);

update instructor set salary = salary * 1.05 where salary < (select avg (salary) from instructor);

update instructor
set salary = 
    case
        when salary <= 100000then salary * 1.05
        else salary * 1.03
    end;

想要更多相关代码和笔记？找找这个仓库：

https://gitee.com/QMMMS/reading-notes

- END -

 

收录于合集 #技术
 23个
上一篇聊聊NLP的基石之一：BiLSTM双向长短期记忆神经网络下一篇Database Concepts: Relational Algebra
阅读 542
文章已于2023-05-14修改
赛博升哥
27篇原创内容
 
posted @ 2023-06-12 14:19  往事已成昨天  阅读(118)  评论(0)  编辑  收藏  举报