1.把所有低于75分的女生成绩提高5%;
表结构:
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:
输出样例:
代码:
UPDATE sc
SET sc.grade=sc.grade+sc.grade*0.05
WHERE sc.sno IN(SELECT sno
FROM stu
WHERE sex=0)
AND grade<75;
2.在SC表中修改‘C001’课程的成绩,若成绩小于70分则提高5%,若成绩在70分以上(含70分)则提高4%
表结构:
CREATE TABLE `sc` (
`sno` char(4) NOT NULL,
`cno` char(4) NOT NULL,
`grade` decimal(6,1) DEFAULT NULL,
PRIMARY KEY (`sno`,`cno`)
);
表样例:
sc:
输出样例:
代码:
UPDATE sc
SET grade=
CASE
WHEN grade<70 THEN grade*1.05
WHEN grade>=70 THEN grade*1.04
ELSE grade
END
WHERE cno="C001";
3.删除选修’C语言’课程的女生的选课记录。
表结构:
CREATE TABLE `stu` (
`sno` char(4) NOT NULL,
`sname` char(8) NOT NULL,
`sex` tinyint(1) DEFAULT NULL, --0为女生,1为男生。
`mno` char(2) DEFAULT NULL,
`birdate` datetime DEFAULT NULL,
`memo` text,
PRIMARY KEY (`sno`)
);
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`),
CONSTRAINT `fk_sc_sno` FOREIGN KEY (`sno`) REFERENCES `stu` (`sno`)
);
表样例:
stu:
cou:
sc:
输出样例:
代码:
DELETE
FROM sc
WHERE sno IN(SELECT sno
FROM stu
WHERE sex=0) AND
cno IN(SELECT cno
FROM cou
WHERE cname="C语言" );
4.检索出 stu 表中学生的学号、姓名、性别. 注意:性别为’1’时显示‘男’,性别为‘0’时显示 ‘女’
表结构:
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,sname,
case sex
when 1 then '男'
when 0 then '女'
end as sex
from
stu
5.在SPJ数据库中,查询同时使用红色的螺母零件和蓝色的螺丝刀零件的工程项目名称jname,输出结果以jname升序排序。 注:假定红色的螺母零件和蓝色的螺丝刀零件分别只有一个pno来标识。
表结构:
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(6) 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(6) 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 jname
FROM j
WHERE jno IN(
SELECT p1.jno
FROM (SELECT pname,color,sno,spj.jno,p.pno
FROM p,spj
WHERE p.pno=spj.pno) AS p1,
(SELECT pname,color,sno,spj.jno,p.pno
FROM p,spj WHERE p.pno=spj.pno) AS p2
WHERE (p1.pname='螺母' AND p1.color='红') AND
(p2.pname='螺丝刀' and p2.color='蓝') AND
p1.sno=p2.sno
)
ORDER BY jname ASC;
6.检索出 stu 表中所有的女生记录。注意:sex为1时表示 男生,sex为0时表示女生
表结构:
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 姓名
FROM stu
WHERE sex=0;
7.检索出stu表中所有年龄大于19岁的女生的记录。注意:sex为1时表示 男生,sex为0时表示女生。 计算年龄时以年计算,不考虑出生月份。 提示:假定当前日期为‘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) >19 AND
sex=0;
8.检索Student表中与‘张三’在同一个专业的学生记录。
表结构:
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 姓名
FROM stu
WHERE mno in (SELECT mno
FROM stu
WHERE sname="张三")
AND sno<>(SELECT sno
FROM stu
WHERE sname="张三");
9.本题目要求编写 Insert语句,在stu表中添加一条学生记录: 学号:S012,姓名:周强,性别:1,其它属性为NULL
.表结构:
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:
输出样例:
代码:
INSERT
INTO stu(sno,sname,sex,mno,birdate,memo)
VALUES ("S012","周强",1,NULL,NULL,NULL);
10.在sc表中,为学号为S012的学生添加以下学生选课成绩记录。 选修C001课程,成绩为90。 选修C002课程,成绩为空。 注意:每条inser语句应以";"结束。
表结构:
CREATE TABLE `sc` (
`sno` char(4) NOT NULL,
`cno` char(4) NOT NULL,
`grade` decimal(6,1) DEFAULT 0,
PRIMARY KEY (`sno`,`cno`)
);
表样例:
sc
输出样例:
代码:
INSERT
INTO sc(sno,cno,grade)
VALUES("S012","C001","90.0"),
("S012","C002",NULL);