数据库原理与应用
DDL和DML练习
- 创建一个名为students 的表，包含id （主键，自增长），name （字符串类型，长度为````````
20），age （整数类型）和class （字符串类型，长度为20）。
CREATE TABLE students (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(20),
age INT,
class VARCHAR(20)
);
- 向students 表中插入一条记录。
INSERT INTO students (name, age, class) VALUES ('张三', 18, '计算机一班');
- 查询students 表中所有记录。
SELECT * FROM students;
- 更新students 表中name 为'张三'的学生的年龄为19。
UPDATE students SET age = 19 WHERE name = '张三';
- 删除students 表中name 为'张三'的学生记录。
DELETE FROM students WHERE name = '张三';
- 创建一个名为courses 的表，包含id （主键，自增长），name （字符串类型，长度为
20）和teacher （字符串类型，长度为20）。
CREATE TABLE courses (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(20),
teacher VARCHAR(20)
);
- 向courses 表中插入一条记录。
INSERT INTO courses (name, teacher) VALUES ('计算机基础', '李四');
- 查询courses 表中所有记录。
SELECT * FROM courses;
- 更新courses 表中name 为'计算机基础'的课程的教师为'王五'。
UPDATE courses SET teacher = '王五' WHERE name = '计算机基础';
- 删除courses 表中name 为'计算机基础'的课程记录。
DELETE FROM courses WHERE name = '计算机基础';
- 创建一个名为scores 的表，包含id （主键，自增长），student_id （外键，关联学生
表的id），course_id （外键，关联课程表的id）和score （整数类型）。
CREATE TABLE scores (
id INT AUTO_INCREMENT PRIMARY KEY,
student_id INT,
course_id INT,
score INT,
FOREIGN KEY (student_id) REFERENCES students(id),
FOREIGN KEY (course_id) REFERENCES courses(id)
);
- 向scores 表中插入一条记录。
INSERT INTO scores (student_id, course_id, score) VALUES (1, 1, 90);
- 查询scores 表中所有记录。
SELECT * FROM scores;
- 更新scores 表中学生ID为1的课程ID为1的成绩为95。
UPDATE scores SET score = 95 WHERE student_id = 1 AND course_id = 1;
- 删除scores 表中学生ID为1的课程ID为1的记录。
DELETE FROM scores WHERE student_id = 1 AND course_id = 1;
- 创建一个名为teachers 的表，包含id （主键，自增长），name （字符串类型，长度为
20）和age （整数类型）。
CREATE TABLE teachers (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(20),
age INT
);
- 向teachers 表中插入一条记录。
INSERT INTO teachers (name, age) VALUES ('李四', 35);
- 查询teachers 表中所有记录。
SELECT * FROM teachers;
- 更新teachers 表中姓名为'李四'的老师的年龄为36。
UPDATE teachers SET age = 36 WHERE name = '李四';
- 删除teachers 表中姓名为'李四'的老师记录。
DELETE FROM teachers WHERE name = '李四';
- 创建一个名为users 的表，包含id （主键，自增长），username （字符串类型，长度
为20），age （整数类型），password （字符串类型，长度为20）和email (字符串类
型，长度为30)。
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(20),
age INT,
password VARCHAR(20),
email VARCHAR(30)
);
- 查询所有用户信息：
SELECT * FROM users;
- 查询年龄大于18的用户信息：
SELECT * FROM users WHERE age > 18;
- 查询用户名为"张三"的用户信息：
SELECT * FROM users WHERE username = '张三';
- 插入一条新用户信息：
INSERT INTO users (username, password, email) VALUES ('李四', '123456','[email protected]');
- 更新用户名为"张三"的用户密码为"abcdef"：
UPDATE users SET password = 'abcdef' WHERE username = '张三';
- 删除用户名为"李四"的用户信息：
DELETE FROM users WHERE username = '李四';
- 查询年龄在18到30之间的用户信息，并按照年龄降序排列：
SELECT * FROM users WHERE age BETWEEN 18 AND 30 ORDER BY age DESC;
- 查询用户名包含"张"的用户信息，并限制结果数量为10：
SELECT * FROM users WHERE username LIKE '%张%' LIMIT 10;
- 查询用户名姓"张"的用户信息，并限制显示第11到第20条结果：
SELECT * FROM users WHERE username LIKE '张%' LIMIT 10,10;
ORDER BY 子句练习
31. 查询员工表中所有员工的姓名和工资，按照工资降序排列。
SELECT name, salary FROM employees ORDER BY salary DESC;
- 查询订单表中所有订单的订单号、客户名和总金额，按照总金额升序排列。
SELECT order_id, customer_name, total_amount FROM orders ORDER BY
total_amount ASC;
- 查询产品表中所有产品的类别和价格，按照价格升序排列。
SELECT category, price FROM products ORDER BY price ASC;
- 查询客户表中所有客户的城市和注册日期，按照注册日期降序排列。
SELECT city, registration_date FROM customers ORDER BY registration_date DESC;
- 查询供应商表中所有供应商的名称和联系方式，按照联系方式升序排列。
SELECT name, contact_info FROM suppliers ORDER BY contact_info ASC;
- 查询订单表中所有已完成的订单的订单号、客户名和总金额，按照总金额升序排列。
SELECT order_id, customer_name, total_amount FROM orders WHERE status =
'completed' ORDER BY total_amount ASC;
- 查询产品表中所有库存大于100的产品的类别和价格，按照价格升序排列。
SELECT category, price FROM products WHERE stock > 100 ORDER BY price ASC;
- 查询客户表中所有来自北京的客户的城市和注册日期，按照注册日期降序排列。
SELECT city, registration_date FROM customers WHERE city = '北京' ORDER BY
registration_date DESC;
- 查询供应商表中所有提供电子产品的供应商的名称和联系方式，按照联系方式升序排列。
SELECT name, contact_info FROM suppliers WHERE product_type = 'electronics'
ORDER BY contact_info ASC;
- 查询订单表中所有已取消的订单的订单号、客户名和总金额，按照总金额升序排列。
SELECT order_id, customer_name, total_amount FROM orders WHERE status =
'cancelled' ORDER BY total_amount ASC;
limit子句练习
- 查询员工表中前5条记录：
SELECT * FROM employees LIMIT 5;
- 查询订单表中总金额大于1000的订单的前3条记录：
SELECT * FROM orders WHERE total_amount > 1000 ORDER BY order_date DESC LIMIT 3;
- 查询产品表中库存大于50的产品的前10条记录：
SELECT * FROM products WHERE stock > 50 ORDER BY price ASC LIMIT 10;
- 查询客户表中年龄大于30的客户的前5条记录：
SELECT * FROM customers WHERE age > 30 ORDER BY registration_date DESC LIMIT 5;
- 查询供应商表中提供电子产品的供应商的前3条记录：
SELECT * FROM suppliers WHERE product_type = 'electronics' ORDER BY
contact_info ASC LIMIT 3;
- 查询订单表中总金额在1000到2000之间的订单的前5条记录：
SELECT * FROM orders WHERE total_amount BETWEEN 1000 AND 2000 ORDER BY
order_date DESC LIMIT 5;
- 查询产品表中价格在50到100之间的产品的前10条记录：
SELECT * FROM products WHERE price BETWEEN 50 AND 100 ORDER BY price ASC
LIMIT 10;
- 查询客户表中注册日期在2020年之前的客户的前5条记录：
SELECT * FROM customers WHERE registration_date < '2020-01-01' ORDER BY
registration_date DESC LIMIT 5;
- 查询供应商表中联系方式包含"@"符号的供应商的前3条记录：
SELECT * FROM suppliers WHERE contact_info LIKE '%@%' ORDER BY contact_info
ASC LIMIT 3;
- 查询订单表中总金额在1000到2000之间的订单，按照订单日期降序排列的前5条记
录：
SELECT * FROM orders WHERE total_amount BETWEEN 1000 AND 2000 ORDER BY
order_date DESC LIMIT 5;
GROUP BY 子句练习
51. 查询每个部门的平均工资。
SELECT department, AVG(salary) FROM employees GROUP BY department;
- 查询每个城市的人口数量。
SELECT city, COUNT(*) FROM residents GROUP BY city;
- 查询每个产品的销售总量。
SELECT product_id, SUM(quantity) FROM sales GROUP BY product_id;
- 查询每个月的总销售额。
SELECT MONTH(sale_date) AS month, SUM(amount) FROM sales GROUP BY month;
- 查询每个班级的平均分。
SELECT class, AVG(score) FROM scores GROUP BY class;
- 查询每个供应商提供的产品种类数。
SELECT supplier_id, COUNT(DISTINCT product_id) FROM products GROUP BY supplier_id;
- 查询每个年龄段的人数。
SELECT age_range, COUNT(*) FROM people GROUP BY age_range;
- 查询每个地区的总销售额。
SELECT region, SUM(amount) FROM sales GROUP BY region;
- 查询每个部门的员工数量和工资总和。
SELECT department, COUNT(*), SUM(salary) FROM employees GROUP BY department;
- 查询每个月的订单数量和总金额。
SELECT MONTH(order_date) AS month, COUNT(*), SUM(amount) FROM orders GROUP BY month;
- 查询销售额大于10000的部门及其总销售额：
SELECT department, SUM(sales) as total_sales
FROM sales_data
GROUP BY department
HAVING total_sales > 10000;
- 查询平均成绩大于80分的学生及其平均成绩：
SELECT student_name, AVG(score) as average_score
FROM scores
GROUP BY student_name
HAVING average_score > 80;
- 查询库存数量小于10的商品：
SELECT product_name, stock_count
FROM products
WHERE stock_count < 10;
- 查询年龄大于30岁的员工及其平均工资：
SELECT employee_name, AVG(salary) as average_salary
FROM employees
WHERE age > 30
GROUP BY employee_name;
- 查询订单金额大于1000的顾客及其订单总金额：
SELECT customer_name, SUM(order_amount) as total_order_amount
FROM orders
GROUP BY customer_name
HAVING total_order_amount > 1000;
- 查询商品分类中至少有3个商品的分类：
SELECT category_name, COUNT(product_id) as product_count
FROM products
GROUP BY category_name
HAVING product_count >= 3;
- 查询至少有两个员工的部门：
SELECT department, COUNT(employee_id) as employee_count
FROM employees
GROUP BY department
HAVING employee_count >= 2;
- 查询每个月的平均销售额：
SELECT MONTH(sale_date) as month, AVG(sales) as average_sales
FROM sales_data
GROUP BY month;
- 查询每个城市的最高气温：
SELECT city, MAX(temperature) as max_temperature
FROM weather_data
GROUP BY city;
- 查询每个类别中最贵的商品价格：
SELECT category, MAX(price) as max_price
FROM products
GROUP BY category;
- 查询每个部门的员工数量：
SELECT department, COUNT(*) as employee_count
FROM employees
GROUP BY department;
- 查询每个部门的员工数量，并使用WITH ROLLUP子句显示总计：
SELECT department, COUNT(*) as employee_count
FROM employees
GROUP BY department
WITH ROLLUP;
- 查询每个部门的销售总额：
SELECT department, SUM(sales) as total_sales
FROM sales_data
GROUP BY department;
- 查询每个部门的销售总额，并使用WITH ROLLUP子句显示总计：
SELECT department, SUM(sales) as total_sales
FROM sales_data
GROUP BY department
WITH ROLLUP;
- 查询每个产品的销售额：
SELECT product_id, SUM(sales) as product_sales
FROM sales_data
GROUP BY product_id;
- 查询每个产品的销售额，并使用WITH ROLLUP子句显示总计：
SELECT product_id, SUM(sales) as product_sales
FROM sales_data
GROUP BY product_id
WITH ROLLUP;
- 查询每个客户的订单数量：
SELECT customer_id, COUNT(*) as order_count
FROM orders
GROUP BY customer_id;
- 查询每个客户的订单数量，并使用WITH ROLLUP子句显示总计：
SELECT customer_id, COUNT(*) as order_count
FROM orders
GROUP BY customer_id
WITH ROLLUP;
- 查询每个员工的销售总额：
SELECT employee_id, SUM(sales) as total_sales
FROM sales_data
GROUP BY employee_id;
- 查询每个员工的销售总额，并使用WITH ROLLUP子句显示总计：
SELECT employee_id, SUM(sales) as total_sales
FROM sales_data
GROUP BY employee_id
WITH ROLLUP;
聚合函数练习
- 查询员工表中所有员工的平均工资：
SELECT AVG(salary) FROM employees;
- 查询订单表中所有订单的总金额：
SELECT SUM(amount) FROM orders;
- 查询产品表中所有产品的库存总和：
SELECT SUM(stock) FROM products;
- 查询客户表中所有客户的订单数量：
SELECT customer_id, COUNT(*) as order_count FROM orders GROUP BY customer_id;
- 查询供应商表中所有供应商的产品数量：
SELECT supplier_id, COUNT(*) as product_count FROM products GROUP BY
supplier_id;
- 查询订单表中每个月份的订单数量：
SELECT MONTH(order_date) as month, COUNT(*) as order_count FROM orders GROUP
BY month;
- 查询产品表中每个类别的产品数量：
SELECT category, COUNT(*) as product_count FROM products GROUP BY category;
- 查询客户表中每个城市的订单金额总和：
SELECT city, SUM(amount) as total_amount FROM customers INNER JOIN orders ON
customers.id = orders.customer_id GROUP BY city;
-
查询供应商表中每个国家的供应商数量：
SELECT country, COUNT(*) as supplier_count FROM suppliers GROUP BY country; -
查询订单表中每个状态的订单数量：
SELECT status, COUNT(*) as order_count FROM orders GROUP BY status;
综合练习
- 教师-班级-上课
教师：teacher(no，name，dept)； 三个属性分别表示教师编号、姓名和所属的系。
班级：class(title，number，sir_no)；三个属性分别表示班级的名称（char(3)）、人数、
班主任的教师编号。
上课：course(course_name，teacher_no，class_ti，time，room)；五个属性分别表示
课程名称、教师编号、班级名称、时间和地点。
试按下列查询要求写出SELECT-SQL命令。
(1) 列出‘图论’课的时间和地点；
SELECT time,room
FROM course
WHERE course_name ='图论';
(2)列出教师表中教师数<20的系及教师数；
SELECT dept,count(*)
FROM teacher
GROUP BY dept
HAVING count(*)<20;
(3) 列出编号为4411的教师所在系的全体教师姓名；
SELECT no,name
FROM
teacher
WHERE dept = ( SELECT dept FROM teacher WHERE no=4411);
(4) 创建一个视图teacher_only，列出不是班主任的教师的编号、姓名和所属系；
CREATE VIEW teacher_only
AS
SELECT no,name,dept FROM teacher
WHERE no NOT IN(SELECT sir_no FROM class);
(5) 计算机系新增加了一个教师，工号为‘4422’，姓名为‘吴为’；
INSERT INTO teacher VALUES(4422,'吴为','计算机系') ;
(6) 将原由‘王静’老师担任‘数据库原理’的授课任务改成由“李勇”老师担任；
UPDATE course
SET teacher_no=(SELECT no FROM teacher WHERE name=’李勇’)
WHERE teacher_no=(SELECT no FROM teacher WHERE name=’王静’);
- 工资等级-职位-部门-员工
工资等级表grades(grade_level,high_sal,low_sal) 属性表示：工资等级，最高工资，最低
工资
职位表：jobs(job_id,job_name) 两个属性表示：职位号，职位名
部门：departments（depart_id，depart_name） 两个属性表示：部门号，部门名
员工：employees(emp_id,ename,salary,job_id,depart_id,hire_date,manager_id) 属性
表示：员工号，姓名，工资，职位号，部门号，入职时间，经理号。
(1) 查询employees表中所有员工的ename,job_id,salary,新工资。当job_id为 IT_PROG时
工资增加10%，当job_id为ST_CLERK时工资增加15%，当job_id为SA_REP时 工资增加
20%，其他job_id工资不变。
select ename,job_id,salary,
case
when job_id='IT_PROG' then salary*1.1
when job_id='ST_CLERK' then salary*1.15
when job_id='SA_REP' then salary*1.2
else salary
end as 新工资
from employees;
或
select ename,job_id,salary,
case job_id
when 'IT_PROG' then salary*1.1
when 'ST_CLERK' then salary*1.15
when 'SA_REP' then salary*1.2
else salary
end as 新工资
from employees;
(2) 查询ename以ing结尾的员工的ename,depart_id,dname。
select e.ename,d.depart_id,d.dname
from employees e join departments d
on e.depart_id=d.depart_id
where ename like ‘%ing’;
(3) 查询工龄在20-25年之间的员工信息，显示：ename,salary,工龄,job_name。
select e.ename,e.salary,year(curdate())-year(e.hire_date) as 工龄,j.job_name
from employees e join jobs j on e.job_id=j.job_id
where year(curdate())-year(e.hire_date) between 20 and 25;
(4) 查询员工的ename,job_id,salary,grade_level。
select e.ename,e.job_id,e.salary,j.grade_level
from employees e,grades j
where e.salary between j.low_sal and j.high_sal;
(5) 查询salary大于10000并且ename中包含'a'的员工信息，显示：
ename,salary,depart_name,查询结果根据salary降序排序。
select e.ename,e.salary,d.depart_name
from employees e join departments d on e.depart_id=d.depart_id
where e.salary>10000 and e.ename like '%a%'
order by 2 desc;
(6) 查询所有员工的ename,depart_name。
select e.ename,d.depart_name
from employees e left join departments d on e.depart_id=d.depart_id;
(7) 查询employees表中员工的员工编号，员工姓名，经理编号，经理姓名。
select e.emp_id,e.ename,m.emp_id,m.ename
from employees e join employees m on e.manager_id=m.emp_id;
(8) 查询每个职位的平均工资，显示：job_name,平均工资，查询结果根据平均工资降序排
序。
select j.job_name,avg(e.salary) as 平均工资
from employees e join jobs j on e.job_id=j.job_id
group by j.job_name
order by 2 desc;
(9) 查询每年入职员工的人数，显示：年份，人数，查询结果根据年份升序排序。
from employees
group by year(hire_date)
order by 1 asc;
(10) 查询人数在3人或3人以上的部门信息，显示：depart_name,人数。结果根据人数 降
序排序。
from employees e join departments d on e.depart_id=d.depart_id
group by d.depart_name
having count(*)>=3
order by 2 desc;
(11) 查询employees表中哪些员工的工资大于176号员工的工资，显示：
ename,job_id,salary。查询的结果根据salary降序排序。
from employees
where salary>(select salary
from employees
where emp_id=176
)
order by salary desc;
(12) 查询employees表中工资最高的员工信息。显示：ename,hire_date,salary。
from employees
where salary=(select max(salary) from employees);
(13) 查询employees表中入职最早的员工信息。显示：ename,hire_date。
from employees
where hire_date=(select min(hire_date) from employees);
(14) 查询employees表中与最早入职员工在同一个部门工作的员工信息。显示：
ename,salary,depart_id。
select ename,salary,depart_id
from employees
where depart_id in(select depart_id
from employees
where hire_date=(select min(hire_date) from employees)
);
(15) 平均工资最高的前3个部门的信息，显示：depart_name,平均工资。结果根据平均工
资 降序排序。
select d.depart_name,avg(e.salary) as 平均工资
from employees e join departments d on e.depart_id=d.depart_id
group by d.depart_name
order by 2 desc limit 0,3;
3.学生-教师-课程-选课
学生表：student（sid,sname,sage,ssex）
属性含义为：学号，姓名，年龄，性别
教师：teacher(tid,tname)
属性含义为：教师号，姓名
课程：course(cid,cname,tid)
属性含义为：课程号，课程名，教师号
选课表：sc(sid,cid,score)
属性含义为：学号，课程号，成绩
(1) 查询“001”课程比”002″课程成绩高的所有学生的学号。
select a.sid
from (select * from sc where cid='001') as a,
(select * from sc where cid = '002')as b
where a.sid = b.sid and a.score>b.score;
(2) 查询平均成绩大于60分的同学的学号和平均成绩
select sid,avg(score)
from sc
group by sid
having avg(score)>60;
(3) 查询所有的同学的学号、姓名、选课数、总成绩
select student.sid,student.sname,COUNT(*),sum(score)
from sc join student
on sc.sid = student.sid
group by sid;
(4) 查询姓“李”的老师的个数
select COUNT(*) from teacher where tname LIKE '李%';
(5) 查询没学过“叶平“老师的课程的同学的学号、姓名
select sid,sname
from student
where sid not in(
select sid from sc join course
on sc.cid = course.cid
join teacher
on teacher.tid = course.tid
where teacher.tname = '叶平'
);
(6) 统计各科成绩，各分数段人数：课程ID、课程名称，100-85,85-70,70-60,<60 。
select sc.cid as '课程ID',cname as '课程名称',
SUM(case when score between 85 and 100 then 1 else 0 end) as '85-100',
SUM(case when score between 70 and 84 then 1 else 0 end) as '70-84',
SUM(case when score between 69 and 60 then 1 else 0 end) as '60-69',
SUM(case when score between 0 and 100 then 59 else 0 end) as '0-59'
from course,sc
where sc.cid=course.cid
group by sc.cid;
(7) 查询每门课程的课程名和选修的学生数
select cname,count(*)
from sc,course
where course.cid=sc.cid
group by sc.cid;
(8) 查询出只选修了一门课程的全部同学的学号、姓名
select sc.sid,sname
from sc,student
where student.sid=sc.sid
group by sc.sid
having count(*)=1;
(9) 查询男生、女生的人数
(select ssex as '性别',count(*) from student where ssex='男')
union
(select ssex as '性别', count(*) from student where ssex='女');
(10) 查询姓“李”的师生名单
(select sname as '名单' from student where sname like '李%')
union
(select tname as '名单' from teacher where tname like '李%');
4.学生-课程-选课
学生表：student（sno,sname,sage,ssex,sdept）--（学号，姓名，年龄，性别，系别）
课程表：course(cno,cname,credit) -- (课程号，课程名，学分)
选课表：sc(sno,cno,grade) --（学号，课程号，成绩）
(1) 写出选修了数据结构的同学的学号和姓名
select sno,sname from student
where sno in(
select sno
from sc,course
where sc.cno=course.cno
and cname=’数据结构’ );
(2) 统计每门课的选课人数，包括没有人选的课程，列出课程号及选课情况，其中选课情况
为，如果此门课的选课人数超过100人，则显示人多，40-100显示较多，1-40显示较少，
0人显示无人选。
select course.cno ,
case
when (count(*)>100) then ‘人多’
when (count(*)>=40 and count(*)<=100) then ‘较多’
when (count(*)>1 and count(*)<40) then ‘较少’
else ‘无人选’
end as ‘选课情况’
from course left join sc on course.cno=sc.cno
group by cno;
(3) 查询计算机有哪些学生没有选课，列出姓名和学号（用外连接）
select stduent.sno,sname
from student left join sc on sc.sno=student.sno
where sdept='计算机' and sc.sno is null;
(4) 成绩小于60的学生姓名,课程,成绩
select sname,cname,grade
from student,sc,course
where student.sno=sc.sno
and sc.cno=course.cno
and grade<60;
(5) 统计每个学生的选课数和考试总成绩，并按照选课门数升序排列
select sno,count(*) '选课门数' ,sum(grade) '总成绩'
from sc
group by sno
order by 选课门数 DESC;
5.学生-教师-科目-成绩
------------创建数据库---------------
create database data charset=utf8;
------------ 建表语句-----------------
学生表Student;
create table Student(
SId varchar(10) primary key,
Sname varchar(10),
Sage datetime,
Ssex varchar(10));
教师表Teacher
create table Teacher(
TId varchar(10 primary key,
Tname varchar(10));
科目表 Course
create table Course(
CId varchar(10) primary key,
Cname varchar(10),
TId varchar(10));
成绩表 SC
create table SC(
SId varchar(10),
CId varchar(10),
score decimal(18,1),
primary key(SId,CId));
(1) 查询“01”课程比“02”课程成绩高的学生的信息及课程分数
SELECT *
FROM student AS a
INNER JOIN sc AS b
ON a.SId = b.SId
INNER JOIN sc AS c
ON a.SId = c.SId AND b.CId = '01' AND c.CId = '02'
WHERE b.score >c.score;```
或
SELECT a.*, b.score_01, b.score_02
FROM Student AS a
INNER JOIN (SELECT a.SId,a.score AS score_01, b.score AS score_02 FROM SC AS
a INNER JOIN SC AS b ON a.SId = b.SId AND a.CId = '01' AND b.CId =
'02'WHERE a.score > b.score) AS b
ON a.SId = b.SId;
(2) 在SC表中查询同时选修了“01”课程和“02”课程的选课情况（不需要查询学生信
息，只在SC表中操作）
SELECT * FROM sc AS a INNER JOIN sc AS b ON a.SId = b.SId
WHERE a.CId = '01' AND b.CId = '02';
(3) 查询选修了“01”课程但可能不w未选修“02”课程的选课情况(不存在时显示为null)
SELECT * FROM sc AS a LEFT JOIN sc b ON a.Sid = b.Sid AND b.Cid = '02' WHERE
a.Cid = '01';
(4) 查询未选修“01”课程但选修了“02”课程的选课情况
SELECT * FROM sc WHERE Sid NOT IN (SELECT Sid FROM sc WHERE
Cid = '01') AND Cid = '02';
(5) 查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
SELECT a.SId,a.Sname,avg_score FROM student AS a INNER JOIN
(SELECT SId,AVG(score) AS avg_score FROM sc GROUP BY SId HAVING
AVG(score) >= 60) AS b ON a.SId = b.SId;
(6) 查询在SC表存在成绩的学生信息
SELECT b.* FROM (SELECT SId FROM sc GROUP BY SId) AS a LEFT
JOIN student AS b ON a.SId = b.SId;
或
SELECT a.* FROM Student AS a INNER JOIN (SELECT DISTINCT SId FROM SC WHERE
score IS NOT NULL) AS b ON a.SId =b.SId;
(7) 查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为
null)
SELECT a.SId,a.Sname,b.ct,b.sum_score FROM student AS a LEFT
JOIN (SELECT SId,COUNT(CId) AS ct,SUM(score) AS sum_score FROM sc GROUP
BY SId) AS b ON a.SId = b.SId;
(8) 查有成绩的学生信息
SELECT * FROM student WHERE SId IN(SELECT SId FROM sc GROUP BY
SId);
或
--解法1
SELECT * FROM Student WHERE SId IN(SELECT DISTINCT SId FROM
SC WHERE score IS NOT NULL);
--解法2
SELECT a.* FROM Student AS a INNER JOIN (SELECT DISTINCT
SId FROM SC WHERE score IS NOT NULL)AS b ON a.SId = b.SId;
(9) 查询[李]姓老师的数量
SELECT COUNT(*) FROM teacher WHERE Tname LIKE '李%';
(10) 查询学过[张三]老师授课的同学的信息
SELECT DISTINCT a., Tname FROM student AS a INNER JOIN (SELECT
a.,Tname FROM sc AS a INNER JOIN (SELECT a.*,Tname FROM course AS
a INNER JOIN teacher AS b ON a.TId = b.TId) AS b ON a.CId =b.CId) AS b ON
a.SId =b.SId WHERE Tname ='张三';
或
SELECT DISTINCT a.*,d.Tname FROM student AS a INNER JOIN sc AS b ON
a.SId = b.SId INNER JOIN course AS c ON b.CId = c.CId INNER JOIN teacher
AS d ON c.TId = d.TId WHERE Tname = '张三';
(11)查询没有学全所有课程的同学的信息
SELECT a.*,ct FROM student AS a LEFT JOIN (SELECT
SId,COUNT(CId) AS ct FROM sc GROUP BY SId HAVING ct < (SELECT COUNT(CId)
FROM course)) AS b ON a.SId = b.SId;
(12)查询至少有一门课与学号为“01”的同学所学相同的同学的信息
SELECT DISTINCT a.* FROM student AS a INNER JOIN (SELECT * FROM
sc WHERE CId IN(SELECT CId FROM sc WHERE SId = '01')) AS b ON a.SId = b.SId;
(13)查询和“01”号的同学学习的课程完全相同的其他同学的信息
SELECT b.* FROM (SELECT SId FROM sc WHERE SId NOT IN (SELECT SId FROM sc
WHERE CId NOT IN (SELECT CId FROM sc WHERE SId = '01')) AND
SId != '01' GROUP BY SId HAVING COUNT(CId) = (SELECT COUNT(CId) FROM sc
WHERE SId = '01')) AS a INNER JOIN student AS b ON a.SId = b.SId;
(14)查询没学过“张三”老师讲授的任一门课程的学生姓名
SELECT Sname FROM student AS a WHERE SId NOT IN (SELECT
SId FROM sc AS a LEFT JOIN course AS b ON a.CId = b.CId INNER JOIN
teacher AS c ON b.TId = c.TId WHERE Tname = '张三');
或
SELECT Sname FROM student WHERE Sid NOT IN(SELECT Sid FROM
sc WHERE Cid IN(SELECT Cid FROM course WHERE Tid IN(SELECT Tid FROM
teacher WHERE Tname = '张三')));
(15)查询两门及以上不及格课程的同学的学号，姓名及其平均成绩
SELECT c.SId,d.Sname,avg_score FROM(SELECT a.SId,AVG(score)
AS avg_score FROM sc AS a INNER JOIN(SELECT SId FROM sc WHERE score <
60 GROUP BY SId HAVING COUNT(CId) >= 2) AS b ON a.SId = b.SId GROUP BY
a.SId) AS c LEFT JOIN student AS d ON c.SId = d.SId;
或
SELECT a.Sid,a.Sname,AVG(c.score) AS avg_score FROM student
AS a INNER JOIN (SELECT Sid,COUNT(Cid) AS num FROM sc WHERE score <
60 GROUP BY Sid HAVING num >= 2)AS b ON a.Sid = b.Sid INNER JOIN sc AS
c ON b.Sid = c.Sid GROUP BY a.Sid;
(16)检索“01”课程分数小于60分，按分数降序排列的学生信息
SELECT b.*,a.score FROM(SELECT SId,score FROM sc WHERE CId = '01' AND score <
60) AS a LEFT JOIN student AS b ON a.SId = b.SId ORDER
BY a.score DESC;
(17)按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
SELECT a.SId,a.CId,a.score,avg_score FROM(SELECT a.SId,b.CId,b.score FROM
student AS a LEFT JOIN sc AS b ON a.SId = b.SId) AS a LEFT JOIN (SELECT
SId,AVG(score) AS avg_score FROM sc GROUP BY SId) AS b ON a.SId = b.SId ORDER
BY b.avg_score DESC;
(18)查询各科成绩最高分、最低分和平均分，以如下形式显示:课程ID，课程name，选修
人数，最高分，最低分，平均分，及格率，中等率，优良率，优秀率及格为:>=60,中等为:
[70,80),优良为:[80,90),优秀为:>=90；要求输出课程号和选修人数，查询结果按人数降序
排列，若人数相同，按课程号升序排列
SELECT a.,b.Cname FROM(
SELECT CId,COUNT() AS 选修人数,
MAX(score) AS 最高分,
MIN(score) AS 最低分,
AVG(score) AS 平均分,
SUM(CASE
WHEN score >= 60 THEN 1 ELSE 0 END) / COUNT() AS 及格率,
SUM(CASE
WHEN score >= 70 AND score < 80 THEN 1 ELSE 0 END) / COUNT() AS 中等率,
SUM(CASE
WHEN score >= 80 AND score < 90 THEN 1 ELSE 0 END) / COUNT() AS 优良率,
SUM(CASE
WHEN score >= 90 THEN 1 ELSE 0 END) / COUNT() AS 优秀率
FROM sc GROUP BY CId ORDER BY COUNT(*) DESC,CId ASC)
AS a LEFT JOIN course AS b ON a.CId = b.CId;