实验五 单表查询

今天总算考完了数电，你平时是什么样子，当来事儿的时候就是什么样子。你看！平时不好好学习，考试就汗流浃背了吧。没错，正是说的我本人自己。话不多说，开始复习明天的数据库考试，直接开始！

实验内容

（注：每个查询都需要查询结果，因此需要造相应的记录）

1. 1. （1）打开“实验四”中的学生-课程数据库(S_T),完成以下查询
      1. 查询全体学生的姓名、出生年份及所在系。（掌握计算列的显示结果）
      2. 查询选修了课程的学生学号。（掌握distinct的用法）
      3. 查询年龄在18-20岁间的学生姓名及学号。
      4. 查询“计算机系”、“数学系”、“信息系”学生的学号及姓名。
      5. 查找所有姓李的学生的信息。
      6. 查找所有已选修但没有成绩的学生学号。
      7. 对所有已选课的学生按学号进行升序排列，同时要求每个学生按成绩降序排列。（掌握order by的用法）
      8. 统计每门课程的选课人数。（掌握group by用法）
      9. 统计重名的学生姓名及人数。
      10. 统计男生与女生的人数。
      11. 查询“计算机系”年龄最大的学生的基本信息。

1. 1. （2）打开“实验四”中的SPJ数据库,完成以下查询：
      1. 打出所有供应商的姓名和所在城市。
      2. 找出所有零件的名称及重量。
      3. 统计每个供应商供应的各种零件数量。
      4. 求供应工程J1零件的供应商号码SNO。
      5. 求供应工程J1零件P1的的供应商号码SNO。

插入数据到学生-课程数据库S_T

--插入数据
insert into Student
values
('202215121','李勇','男',20,'CS'),
('202215122','刘晨','女',19,'CS'),
('202215123','王敏','女',18,'MA'),
('202215125','张立','男',19,'IS');

insert into Course
values
('1','数据库','5',4),
('2','数学',NULL,2),
('3','信息系统','1',4),
('4','操作系统','6',3),
('5','数据结构','7',4),
('6','数据处理','',2),
('7','PASCAL语言','6',4);

insert into SC
values
('202215121','1',92),
('202215121','2',85),
('202215121','3',88),
('202215122','2',90),
('202215121','3',80);

学生-课程数据库(S_T)的查询

--查询全体学生的姓名、出生年份及所在系。（掌握计算列的显示结果）
select Sname 姓名,
year(getdate())-Sage 出生年份,
lower(Sdept) 系	--系名用小写表示
from Student;

--查询选修了课程的学生学号。（掌握distinct的用法）
SELECT
DISTINCT Sno--查询选修了课程的学生学号，并用DISTINCT去掉重复行
FROM SC;

--查询年龄在18-20岁间的学生姓名及学号。
SELECT
Sname,--查询学生姓名
Sno--查询学生学号
FROM Student
WHERE Sage BETWEEN 18 AND 20;--使用谓词BETWEEN...AND...

--查询“计算机系”、“数学系”、“信息系”学生的学号及姓名。
SELECT
Sno,--查询学号
Sname--查询姓名
FROM Student
WHERE Sdept IN('CS','MA','IS');--IN 确定集合

--查找所有姓李的学生的信息。
SELECT*--SELECT*查询学生所有信息
FROM Student
WHERE Sname LIKE '李%';--查询所有姓李的学生信息

--查找所有已选修但没有成绩的学生学号。
SELECT--查找所有已选修但没有成绩的学生学号
Sno
FROM SC
WHERE Grade IS NULL;

--对所有已选课的学生按学号进行升序排列，同时要求每个学生按成绩降序排列。（掌握order by的用法）
SELECT
Sno,
Grade
FROM SC
ORDER BY Sno, Grade DESC;--学号升序，成绩降序

--统计每门课程的选课人数。（掌握group by用法）
SELECT--统计每门课程的选课人数
Cno,
Count(Sno)'人数'--设置列别名
FROM SC
GROUP BY Cno;

--统计重名的学生姓名及人数。
SELECT
Sname,
COUNT(*)'重名学生人数'
FROM Student
GROUP BY Sname
HAVING COUNT(*)>1;

--统计男生与女生的人数。
SELECT
Ssex,
COUNT(*)'人数'
FROM Student
GROUP BY Ssex;

--查询“计算机系”年龄最大的学生的基本信息。
select *
from Student
where Sage>=all        -->all选出比子查询中每个值都大的记录
(select Sage from Student
where Sdept='CS');

--查询“计算机系”年龄最大的学生的基本信息。
Select *
from Student
where Sage =
(
Select Max(Sage)
from Student
where Sdept='CS'
);

--查询“计算机系”年龄最大的学生的基本信息。
SELECT top (1)*			--top(x)显示前x行，此题当有多个同为最大年龄时不适用
FROM Student
WHERE Sdept IN('CS')
ORDER BY Sage DESC;

插入数据到SPJ数据库

insert into S
values
('S1','精益','20','天津'),
('S2','盛锡','10','北京'),
('S3','东方红','30','北京'),
('S4','丰泰盛','20','天津'),
('S5','为民','50','上海');

insert into P
values
('P1','螺母','红',12),
('P2','螺栓','绿',17),
('P3','螺丝刀','蓝',14),
('P4','螺丝刀','红',14),
('P5','凸轮','蓝',40),
('P6','齿轮','红',30);

insert into J
values
('J1','三建','北京'),
('J2','一汽','长春'),
('J3','弹簧厂','天津'),
('J4','造船厂','天津'),
('J5','机车厂','唐山'),
('J6','无线电厂','常州'),
('J7','半导体厂','南京');

insert into SPJ
values
('S1','P1','J1',200),
('S1','P1','J3',100),
('S1','P1','J4',700),
('S1','P2','J2',100),
('S2','P1','J1',400),
('S2','P3','J2',200),
('S2','P3','J4',500),
('S2','P5','J2',100),
('S2','P5','J3',400),
('S2','P5','J5',400),
('S3','P1','J1',200),
('S3','P3','J1',200),
('S4','P5','J1',100),
('S4','P6','J3',300),
('S4','P6','J4',200),
('S5','P3','J1',200),
('S5','P5','J4',100),
('S5','P6','J2',200),
('S5','P6','J4',500);

SPJ数据库的查询

1. 打出所有供应商的姓名和所在城市。
SELECT
SNAME,--查询姓名
CITY--查询城市
FROM S;

2. 找出所有零件的名称及重量
SELECT
PNAME,--查找姓名
WEIGHT--查找重量
FROM P;

3. 统计每个供应商供应的各种零件数量。
SELECT
SNO 供应商号,
PNO 零件号,
SUM(QTY)供应总量
FROM SPJ
GROUP BY PNO,SNO

4. 求供应工程J1零件的供应商号码SNO
SELECT
DISTINCT SNO
FROM SPJ
WHERE JNO='J1'
ORDER BY SNO;

5. 求供应工程J1零件P1的的供应商号码SNO。
SELECT
DISTINCT SNO
FROM SPJ
WHERE JNO='J1' AND PNO='P1'
ORDER BY SNO;