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;
- END -想要更多相关代码和笔记?找找这个仓库:
https://gitee.com/QMMMS/reading-notes
收录于合集 #技术 23个 上一篇聊聊NLP的基石之一:BiLSTM双向长短期记忆神经网络下一篇Database Concepts: Relational Algebra 阅读 542 文章已于2023-05-14修改