selectname, i.dept_name, buildingfrom instructor i , department dwhere 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;
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 as
(
select max(budget) value
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 as
(
select dept_name, sum(salary) value
from instructor
group by dept_name
),
dept_total_avg as
(
select avg(value) 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,
(
select count(*)
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 |
+---+
DML
Deletion
Delete all instructors
delete from instructor;
Delete all instructors from the Finance department
delete from instructor
where dept_name= 'Finance';
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 <= 100000 then salary * 1.05
else salary * 1.03
end;
函数补充
nvl(exper1, exper2)：如果exper1为NULL，用exper2代替
length(exper)：对于字符串字段求其长度
round(exper, n)：对于小数字段exper，保留位小数
concat(exper1, exper2)：字符串拼接，拼接多个就嵌套。
综合练习
Find the students who have registered some course at least 2 times. For each student as such, following information should be listed:
Name of the student
Title of the course
Count of registered record of the student for the course
WITH sdi_cid_c AS
(
SELECT ID, course_id, count(*) cnt
FROM takes
GROUP BY ID, course_id
HAVING count(*)>=2
)
SELECT name, title, cnt
FROM sdi_cid_c
JOIN student ON sdi_cid_c.ID = student.ID
JOIN course ON sdi_cid_c.course_id = course.course_id;
Find the students who have never registered any course. For each student as such, following information should be listed:
ID of the student
Name of the student
WITH target AS
(
(
SELECT ID
FROM student
)
except
(
SELECT ID
FROM takes
)
)
SELECT s.ID, name
FROM student s
WHERE s.ID in
(
SELECT ID
FROM target
);
Find the sections which have register records having unknown grade information. For each section as such, following information should be listed:
Title of the course corresponding to the section
Id of the section
Year of the section
Semester of the section
WITH target AS
(
SELECT s.course_id cid, s.sec_id sid, s.year year, s.semester semester
FROM section s JOIN takes t ON s.course_id = t.course_id AND s.sec_id = t.sec_id AND s.semester = t.semester AND s.year = t.year
WHERE grade is NULL
)
SELECT title, sid, year, semester
FROM target JOIN course ON target.cid = course.course_id;
Find the students who enroll to the department having 4 instructors at least. For each student as such, following information should be listed:
Id of the student
Name of the student
Name of the department the student enrolled to
WITH depts AS
(
SELECT dept_name
FROM instructor
GROUP BY dept_name
HAVING count(*)>=4
)
SELECT ID, name, s.dept_name
FROM student s
WHERE s.dept_name IN
(
SELECT dept_name
FROM depts
);
Find the department which hired instructors whose name contain ‘世’. For each department as such, following information should be listed:
Name of the department
Building of the department
Instructor count of the department
WITH depts AS
(
SELECT dept_name
FROM instructor
WHERE name LIKE '%世%'
),
d_cnt AS
(
SELECT dept_name, count(*) cnt
FROM instructor
GROUP BY dept_name
)
SELECT d.dept_name, building, cnt
FROM department d JOIN d_cnt ON d.dept_name=d_cnt.dept_name
WHERE d.dept_name IN
(
SELECT dept_name
FROM depts
);
Find the courses which have no ‘F’ grade record ever. Note: the courses which have never be registered should be included. For each course as such, following information should be listed:
Course_id the course
Title of the course
Count of section corresponding to the course
WITH cid_cnt AS
(
select course_id, count(*) cnt
from section
GROUP BY course_id
),
cid_g AS
(
SELECT course_id, grade
FROM takes
)
SELECT c.course_id, title, nvl(cnt,0)
FROM course c LEFT OUTER JOIN cid_cnt ON c.course_id = cid_cnt.course_id
WHERE c.course_id not IN
(
SELECT course_id
FROM cid_g
WHERE grade = 'F'
);
Get the grade distribution of all the sections having records in table takes Note: there’s some grade like ‘A-’. For each section grade distribution as such, following information should be listed:
Course_id of the section
Year of the section
Semester of the section
ID number of the section
Count of grade A records
Count of grade B records
Count of grade C records
Count of grade D records
Count of grade F records
Count of records without grade information
WITH grade_info AS
(
SELECT course_id, sec_id, semester, year,
CASE
WHEN grade LIKE 'A%' THEN 1
ELSE 0
END A_,
CASE
WHEN grade LIKE 'B%' THEN 1
ELSE 0
END B_,
CASE
WHEN grade LIKE 'C%' THEN 1
ELSE 0
END C_,
CASE
WHEN grade LIKE 'D%' THEN 1
ELSE 0
END D_,
CASE
WHEN grade LIKE 'E%' THEN 1
ELSE 0
END E_,
CASE
WHEN grade LIKE 'F%' THEN 1
ELSE 0
END F_,
CASE
WHEN grade IS NULL THEN 1
ELSE 0
END nv
from takes
)
SELECT g.course_id, g.year, g.semester, g.sec_id, SUM(A_) A_s, SUM(B_) B_s, SUM(C_) C_s, SUM(D_) D_s, SUM(F_) F_s, SUM(nv) nvs
FROM grade_info g JOIN section s ON g.course_id = s.course_id AND g.sec_id = s.sec_id AND g.semester = s.semester AND g.year=s.year
GROUP BY g.course_id, g.sec_id, g.semester, g.year;
WITH grade_info AS
(
SELECT course_id, sec_id, semester, year,
CASE WHEN grade LIKE 'A%' THEN 1 END A_,
CASE WHEN grade LIKE 'B%' THEN 1 END B_,
CASE WHEN grade LIKE 'C%' THEN 1 END C_,
CASE WHEN grade LIKE 'D%' THEN 1 END D_,
CASE WHEN grade LIKE 'E%' THEN 1 END E_,
CASE WHEN grade LIKE 'F%' THEN 1 END F_,
CASE WHEN grade is NULL THEN 1 END nv
from takes
)
SELECT course_id, year, semester, sec_id,
nvl(SUM(A_),0) A_s,
nvl(SUM(B_),0) B_s,
nvl(SUM(C_),0) C_s,
nvl(SUM(D_),0) D_s,
nvl(SUM(F_),0) F_s,
nvl(SUM(nv),0) nvs
FROM grade_info NATURAL JOIN section
GROUP BY course_id, sec_id, semester, year;
提醒：column used in NATURAL join cannot have qualifier
不能写成：
SELECT g.course_id, g.year, g.semester, g.sec_id,
nvl(SUM(A_),0) A_s,
nvl(SUM(B_),0) B_s,
nvl(SUM(C_),0) C_s,
nvl(SUM(D_),0) D_s,
nvl(SUM(F_),0) F_s,
nvl(SUM(nv),0) nvs
FROM grade_info g NATURAL JOIN section s
GROUP BY g.course_id, g.sec_id, g.semester, g.year
Get the total credit hours in a week for every course which has has corresponding sections in every semester. For each course as such , following information should be listed:
ID of the course
Year of the course ’s sections
Semester of the course ’s sections
Count of the Count of the course ’s sections in the corresponding year and semester
Total Credit hours in a week. Note: 1 credit hour = 50 minutes
SELECT section.course_id, section.year, section.semester,
COUNT(distinct time_slot.time_slot_id) cnt,
SUM((end_hr-start_hr)*60+(end_min-start_min))/50 credit_hour
FROM section JOIN time_slot ON section.time_slot_id=time_slot.time_slot_id
GROUP BY section.course_id, section.year, section.semester
Find the pairs of student and instructor which the student registered the section that taught by the instructor and the student ’s advisor is the instructor. For each pair as such , following information should be listed:
Name of the student
Name of the Instructor
WITH pairs AS
(
(
SELECT takes.ID s_ID, teaches.ID i_ID
FROM takes JOIN teaches ON takes.course_id = teaches.course_id AND takes.sec_id = teaches.sec_id AND takes.semester = teaches.semester AND takes.year = teaches.year
)
INTERSECT
(
SELECT *
FROM advisor
)
)
SELECT student.name, instructor.name
FROM pairs
JOIN student ON pairs.s_ID = student.ID
JOIN instructor ON pairs.i_ID = instructor.ID;
Find the students who have passed exams of all the courses opened by the department the students are enrolled in. For each student as such , following information should be listed:
name of the student
name of the department which student is enrolled in
WITH require AS
(
SELECT id, course_id
FROM student JOIN course ON student.dept_name=course.dept_name
),
pass AS
(
SELECT require.id id, require.course_id course_id
FROM require JOIN takes ON require.course_id=takes.course_id AND takes.id=require.id
WHERE takes.grade NOT LIKE 'F%' AND takes.grade IS NOT NULL
),
fail AS
(
(
SELECT *
FROM require
)
except
(
SELECT *
FROM pass
)
)
SELECT name, dept_name
FROM student
WHERE id NOT IN
(
SELECT id
FROM fail
WHERE fail.id=student.id
);
Get information of 6th to 10th students according to the sorting rule that order students by students’ id ascending. For each student as such , following information should be listed:
id of the student
name of the student
WITH rk_student AS
(
SELECT ROW_NUMBER() OVER (ORDER BY ID ASC) rk, ID, name
FROM student
)
SELECT ID, name
FROM rk_student
WHERE rk >= 6 AND rk <= 10
Find students that has failed exam records that have no corresponding pass ones. For each student as such, following information should be listed:
id of the student
Name of the student
Count of courses that still have failed exam records un-overwrited.
WITH fail AS
(
SELECT *
FROM takes
WHERE grade = 'F'
),
unoverwite AS
(
SELECT ID, count(DISTINCT course_id) cnt
FROM fail
WHERE NOT EXISTS
(
SELECT *
FROM takes
WHERE takes.ID=fail.ID AND takes.course_id=fail.course_id AND takes.grade!='F'
)
GROUP BY ID
)
SELECT unoverwite.ID, student.name, unoverwite.cnt
FROM unoverwite JOIN student ON unoverwite.ID=student.ID;
Find students that has taked some courses more than one time. For each student as such, following information should be listed:
id of the student
Name of the student
ID of the course
The times of the student taking the course
Count of the failed exam records of the student on the course.
WITH course_cnt AS
(
SELECT ID, course_id, count(*) cnt
FROM takes
GROUP BY ID, course_id
HAVING count(*)>=2
),
fail_cnt AS
(
SELECT ID, course_id, count(*) fail_cnt
FROM takes
WHERE grade='F'
GROUP BY ID, course_id
)
SELECT course_cnt.ID, name, course_cnt.course_id, cnt, fail_cnt
FROM course_cnt
JOIN fail_cnt ON course_cnt.ID=fail_cnt.ID AND course_cnt.course_id=fail_cnt.course_id
JOIN student ON student.ID=course_cnt.ID;
