首页 > 数据库 >SQL查询实战：大学数据库

SQL查询实战：大学数据库

时间：2023-06-12 14:37:04浏览次数：53  
标签：实战 Sci name course Comp 数据库 dept SQL id

SQL查询实战：大学数据库

原创 升哥 赛博升哥 2023-05-14 18:46 发表于北京 收录于合集 #技术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篇原创内容  

标签：实战,Sci,name,course,Comp,数据库,dept,SQL,id
From： https://www.cnblogs.com/cherishthepresent/p/17474913.html

相关文章

  • MySQL 8.0.29 instant DDL 数据腐化问题分析
    前言Instantaddordropcolumn的主线逻辑表定义的列顺序与row存储列顺序阐述引入row版本的必要性数据腐化问题原因分析Bug重现与解析MySQL8.0.30修复方案前言DDL相对于数据库的DML之类的其他操作，相对来说是比较耗时、相对重型的操作;因此对业务的影比较严重。M......
  • SQL基础教程（第二版）学习笔记(三)
    SQL基础教程（第二版）学习笔记(三)sanzk 工控笔记 2023-05-1700:26 发表于浙江收录于合集#数据库5个#SQL5个#PostgreSQL5个    用来管理数据库的计算机系统称为数据库管理系统（DBMS）DBMS种类： SQL语句及其种类 知识回顾：//创建表，至少需要的语句CREAT......
  • mysql启动报can't create/write to file 'var/run/mysqld/mysqld.pid 错误解决办法
    msql启动报错，启动不了。进入mysql日志默认的路径为/var/log/mysqld.log查看日志，发现报错信息如下：can'tcreate/writetofile'var/run/mysqld/mysqld.pid解决办法：将/var/run/mysqld/权限赋给mysql执行以下命令即可：chown-Rmysql/var/run/mysqldchgrp-Rmysql/var/ru......
  • FaaS应用实战
    宋文龙，花名闻可，阿里云全球技术服务部高级交付专家，有多年云原生应用开发架构经验以及多年高性能服务器研发经验。目前专注于运用阿里云中间件技术、结合阿里巴巴中台架构经验为客户构建符合行业需要的业务中台解决方案并完成交付。一、什么是无服务计算无服务器计算（ServerlessCompu......
  • 十分钟了解Mongodb数据库
    前言:本文可能比较长，主要分为3个部分。1.mongodb的简介。2.mongodb的安装。3.mongodb查询指令，大家可以根据选择进行阅读。 1.2什么是MongoDBMongoDB是一个跨平台的，面向文档的数据库，是当前NoSQL数据库产品中最热门的一种。它介于关系数据库和非关系数据库之间，是非关系数据库当......
  • 京东到家订单系统高可用架构的迭代实战
    京东到家是达达集团旗下中国最大的本地即时零售平台之一，目标就是实现一个小时配送到家的业务。一直到2019年京东到家覆盖700个县区市，合作门店近10万家，服务数千万消费者。随着订单量的增长、业务复杂度的提升，订单系统也在不断演变进化，从早期一个订单业务模块到现在分布式可扩展的高......
  • hj_screw导出数据库表文档
    #要不要排除掉依赖,需要视项目导入的依赖情况而定.#这要排除掉依赖,主要是因为项目使用的log4j2排除了boot自身的日志具体如下:<dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-web</artifactId>......
  • 一文解读MySQL事务
    经常提到数据库的事务，那你知道数据库还有事务隔离的说法吗，事务隔离还有隔离级别，那什么是事务隔离，隔离级别又是什么呢？本文就帮大家梳理一下。MySQL事务本文所说的MySQL事务都是指在InnoDB引擎下，MyISAM引擎是不支持事务的。数据库事务指的是一组数据操作，事务内的操作要么就是全......
  • mysql的基础语法
    启动/关闭数据库服务——mysqlnetstartmysql-开netstopmysql-关mysql-uroot-p密码showdatabases;——显示数据库dropdatabase数据库名;——删除数据库createdatabase数据库名;——创建数据库use数据库名;——使用数据库showtables;——查数据库中表selec......
  • 出海如何从0到1？融云《社交泛娱乐出海作战地图》实战经验揭秘
    经过近几年的发展，如今的互联网出海已经是截然不同的命题。关注【融云全球互联网通信云】了解更多从粗放到精细，风浪越来越猛烈。如何契合自己的基因选择赛道和地区、如何打造有获客抓手的独特产品、如何拿下第一个客户，是每个出海人都需要关心的问题。融合200+实战家一线宝贵经验，融......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99