1.检索出stu表中所有学生记录。
表结构:
CREATE TABLE stu(
sno char(4) NOT NULL,
sname char(8) NOT NULL,
sex tinyint NULL,
mno char(2) NULL ,
birdate datetime NULL,
memo text NULL,
PRIMARY KEY (sno)
);
表样例:
stu表:
输出样例:
代码:
SELECT *
FROM stu;
2.检索出stu表中所有姓‘李’的学生记录。
CREATE TABLE `stu` (
`sno` char(4) NOT NULL,
`sname` char(8) NOT NULL,
`sex` tinyint(1) DEFAULT NULL,
`mno` char(2) DEFAULT NULL,
`birdate` datetime DEFAULT NULL,
`memo` text,
PRIMARY KEY (`sno`)
);
表样例:
stu
输出样例:
代码:
SELECT sno AS 学号,sname AS 姓名,sex AS 性别,mno AS 专业,birdate AS 出生日期,memo AS 备注
FROM stu
WHERE sname Like "李%";
3.检索出sc表中‘C001’课程未登记成绩的学生学号
表结构:
CREATE TABLE `sc` (
`sno` char(4) NOT NULL, -- 学号
`cno` char(4) NOT NULL, -- 课程号
`grade` decimal(6,1) DEFAULT NULL, -- 成绩
PRIMARY KEY (`sno`,`cno`)
);
表样例:
sc:
输出样例:
代码:
SELECT sno
FROM sc
WHERE cno="C001" AND
grade IS NULL;
4.检索出 stu表中年龄在18-20之间的学生记录。 注意: 计算年龄时以年计算,不考虑出生月份。假定当前日期为‘2020-03-01’。
表结构:
CREATE TABLE `stu` (
`sno` char(4) NOT NULL,
`sname` char(8) NOT NULL,
`sex` tinyint(1) DEFAULT NULL,
`mno` char(2) DEFAULT NULL,
`birdate` datetime DEFAULT NULL,
`memo` text,
PRIMARY KEY (`sno`)
);
表样例:
stu:
输出样例:
代码:
SELECT sno AS 学号,sname AS 姓名,sex AS 性别,mno AS 专业,2020-YEAR(birdate) AS 年龄,memo AS 备注
FROM stu
WHERE 2020-YEAR(birdate) BETWEEN 18 AND 20;
5.查询选修课程成绩的平均分高于80分的学生姓名。
表结构:
CREATE TABLE `stu` (
`sno` char(4) NOT NULL,
`sname` char(8) NOT NULL,
`sex` tinyint(1) DEFAULT NULL,
`mno` char(2) DEFAULT NULL,
`birdate` datetime DEFAULT NULL,
`memo` text,
PRIMARY KEY (`sno`)
);
CREATE TABLE `sc` (
`sno` char(4) NOT NULL,
`cno` char(4) NOT NULL,
`grade` decimal(6,1) DEFAULT NULL,
PRIMARY KEY (`sno`,`cno`),
CONSTRAINT `fk_sc_sno` FOREIGN KEY (`sno`) REFERENCES `stu` (`sno`)
);
表样例:
stu:
sc:
输出样例:
代码:
SELECT sname
FROM stu
WHERE 80<(SELECT AVG(grade)
FROM sc
WHERE sc.sno=stu.sno
GROUP BY sno);
6.在SPJ数据库中,查询供应工程号为j1零件号为p1的供应商号 sno。 提示:请使用SELECT语句作答。
表结构:
CREATE TABLE j (
jno char(3) NOT NULL, -- 工程项目号
jname varchar(10) DEFAULT NULL,
city varchar(10) DEFAULT NULL,
PRIMARY KEY ( jno )
) ;
CREATE TABLE p (
pno char(3) NOT NULL, -- 零件号
pname varchar(10) DEFAULT NULL,
color char(2) DEFAULT NULL,
weight smallint DEFAULT NULL,
PRIMARY KEY ( pno )
) ;
CREATE TABLE s (
sno char(3) NOT NULL, -- 供应商号
sname varchar(10) DEFAULT NULL,
status char(2) DEFAULT NULL,
city varchar(10) DEFAULT NULL,
PRIMARY KEY ( sno )
) ;
CREATE TABLE spj (
sno char(3) NOT NULL,
pno char(3) NOT NULL,
jno char(3) NOT NULL,
qty smallint DEFAULT NULL,
PRIMARY KEY ( sno , pno , jno ),
CONSTRAINT fk_jno FOREIGN KEY ( jno ) REFERENCES j ( jno ),
CONSTRAINT fk_pno FOREIGN KEY ( pno ) REFERENCES p ( pno ),
CONSTRAINT fk_sno FOREIGN KEY ( sno ) REFERENCES s ( sno )
);
表样例:
s:
p:
j:
spj:
输出样例:
代码:
SELECT sno
FROM spj
WHERE jno="j1" AND
pno="p1";
7.在SPJ数据库中,查询供应工程j1的红色零件的供应商号sno。 提示:请使用SELECT语句作答。
表结构:
CREATE TABLE j (
jno char(3) NOT NULL, -- 工程项目号
jname varchar(10) DEFAULT NULL,
city varchar(10) DEFAULT NULL,
PRIMARY KEY ( jno )
) ;
CREATE TABLE p (
pno char(3) NOT NULL, -- 零件号
pname varchar(10) DEFAULT NULL,
color char(2) DEFAULT NULL,
weight smallint DEFAULT NULL,
PRIMARY KEY ( pno )
) ;
CREATE TABLE s (
sno char(3) NOT NULL, -- 供应商号
sname varchar(10) DEFAULT NULL,
status char(2) DEFAULT NULL,
city varchar(10) DEFAULT NULL,
PRIMARY KEY ( sno )
) ;
CREATE TABLE spj (
sno char(3) NOT NULL,
pno char(3) NOT NULL,
jno char(3) NOT NULL,
qty smallint DEFAULT NULL,
PRIMARY KEY ( sno , pno , jno ),
CONSTRAINT fk_jno FOREIGN KEY ( jno ) REFERENCES j ( jno ),
CONSTRAINT fk_pno FOREIGN KEY ( pno ) REFERENCES p ( pno ),
CONSTRAINT fk_sno FOREIGN KEY ( sno ) REFERENCES s ( sno )
);
表样例:
s:
p:
j:
spj:
输出样例:
代码:
SELECT sno
FROM j,p,spj
WHERE j.jno=spj.jno AND
spj.pno=p.pno AND
p.color="红" AND
spj.jno="j1";
8.在SPJ数据库中,查找在同一个城市的所有工程项目jname,输出结果集以城市名city升序排序, 城市 名相同的再以工程项目jname升序排序。
表结构:
CREATE TABLE j ( -- 工程项目表
jno char(3) NOT NULL, -- 工程项目号
jname nvarchar(10) DEFAULT NULL,
city nvarchar(10) DEFAULT NULL,
PRIMARY KEY ( jno )
) ;
CREATE TABLE p ( -- 零件表
pno char(3) NOT NULL, -- 零件号
pname nvarchar(10) DEFAULT NULL,
color nchar(2) DEFAULT NULL,
weight smallint DEFAULT NULL,
PRIMARY KEY ( pno )
) ;
CREATE TABLE s ( -- 供应商表
sno char(3) NOT NULL, -- 供应商号
sname nvarchar(10) DEFAULT NULL,
status char(2) DEFAULT NULL,
city nvarchar(10) DEFAULT NULL,
PRIMARY KEY ( sno )
) ;
CREATE TABLE spj ( -- 零件供应表
sno char(3) NOT NULL,
pno char(3) NOT NULL,
jno char(3) NOT NULL,
qty smallint DEFAULT NULL,
PRIMARY KEY ( sno , pno , jno ),
CONSTRAINT fk_jno FOREIGN KEY ( jno ) REFERENCES j ( jno ),
CONSTRAINT fk_pno FOREIGN KEY ( pno ) REFERENCES p ( pno ),
CONSTRAINT fk_sno FOREIGN KEY ( sno ) REFERENCES s ( sno )
);
表样例:
s:
p:
j:
spj:
输出样例:
代码:
SELECT city,jname
FROM j
WHERE city IN(SELECT city
FROM j
GROUP BY city
HAVING COUNT(city)>1
)
ORDER BY city ASC,jname ASC;
9.检索所授每门课程平均成绩均大于70分的教师姓名,并将检索的值插入一个新的教师表faculty表中
表结构:
CREATE TABLE `cou` (
`cno` char(4) NOT NULL,
`cname` varchar(30) NOT NULL,
`credit` smallint(6) DEFAULT NULL,
`ptime` char(5) DEFAULT NULL,
`teacher` char(10) DEFAULT NULL,
PRIMARY KEY (`cno`)
);
CREATE TABLE `sc` (
`sno` char(4) NOT NULL,
`cno` char(4) NOT NULL,
`grade` decimal(6,1) DEFAULT NULL,
PRIMARY KEY (`sno`,`cno`),
CONSTRAINT `fk_sc_cno` FOREIGN KEY (`cno`) REFERENCES `cou` (`CNO`)
);
CREATE TABLE `faculty` (
`tname` char(10) DEFAULT NULL
);
表样例:
cou:
sc:
输出样例:
代码:
INSERT
INTO faculty(tname)
SELECT DISTINCT cou.teacher
FROM cou,sc
WHERE cou.cno=sc.cno AND cou.teacher NOT IN(
SELECT cou.teacher
FROM sc,cou
WHERE sc.cno=cou.cno
GROUP BY sc.cno
HAVING avg(grade)<=70);
10.将sc表中“高等数学”课程不及格的成绩全改为60分
表结构:
CREATE TABLE `cou` (
`cno` char(4) NOT NULL,
`cname` varchar(30) NOT NULL,
`credit` smallint(6) DEFAULT NULL,
`ptime` char(5) DEFAULT NULL,
`teacher` char(10) DEFAULT NULL,
PRIMARY KEY (`cno`)
);
CREATE TABLE `sc` (
`sno` char(4) NOT NULL,
`cno` char(4) NOT NULL,
`grade` decimal(6,1) DEFAULT NULL,
PRIMARY KEY (`sno`,`cno`),
CONSTRAINT `fk_sc_cno` FOREIGN KEY (`cno`) REFERENCES `cou` (`cno`)
);
表样例:
cou:
sc:
加粗样式
输出样例:
代码:
UPDATE sc
SET grade=60
WHERE grade<60 AND
cno=(SELECT cno
FROM cou
WHERE cname="高等数学" AND
cou.cno=sc.cno);