尼采般地抒情

公告栏

此网站主题为本人手写主题, 主题待开源···

站点信息

文章总数目: 298
已运行时间: 991
目录
  1. 成绩管理相关题目
    1. Student表
    2. Course表
    3. SC表
    4. 初始化SQL语句
    5. 问题代码
  2. Movie 相关题目

尼采般地抒情

尼采般地抒情

公告栏

此网站主题为本人手写主题, 主题待开源···

站点信息

文章总数目: 298
已运行时间: 991

成绩管理相关题目

Student表

Course表

SC表


初始化SQL语句

SET NAMES utf8mb4;

SET FOREIGN_KEY_CHECKS = 0;

/_学生表_/
CREATE TABLE Student
(
Sno INT(9) PRIMARY KEY,
Sname VARCHAR(10),
Ssex CHAR(2),
Sage TINYINT(3),
Sdept VARCHAR(20)
);
INSERT INTO Student
VALUES (201215121, '李勇', '男', 20, 'CS'),
(201215122, '刘晨', '女', 19, 'CS'),
(201215123, '王敏', '女', 18, 'MA'),
(201215125, '张立', '男', 19, 'IS'),
(201215133, '张三', '男', 21, 'TE'),
(201215137, '赵四', '男', 23, 'TE'),
(201215139, '田二', '女', 24, 'CS'),
(201215140, '李四', '男', 21, 'CS'),
(201215141, '郑五', '女', 22, 'IS');

/_课程表_/
CREATE TABLE Course
(
Cno INT(4) PRIMARY KEY,
Cname VARCHAR(40),
Cpno INT(4),
Ccredit TINYINT(3),
FOREIGN KEY (Cpno) REFERENCES Course (Cno)
);
INSERT INTO Course
VALUES (1, '数据库', 5, 4),
(2, '数学', NULL, 2),
(3, '信息系统', 1, 4),
(4, '操作系统', 6, 3),
(5, '数据结构', 7, 4),
(6, '数据处理', NULL, 2),
(7, 'PASCAL 语言', 6, 4);

/_选课表_/
CREATE TABLE SC
(
Sno INT(9),
Cno INT(4),
Grade SMALLINT(3),
PRIMARY KEY (Sno, Cno),
/_ 主码由两个属性构成,必须作为表级完整性进行定义_/
FOREIGN KEY (Sno) REFERENCES Student (Sno),
/_ 表级完整性约束条件,Sno 是外码,被参照表是 Student _/
FOREIGN KEY (Cno) REFERENCES Course (Cno)
/_ 表级完整性约束条件, Cno 是外码,被参照表是 Course_/
);
INSERT INTO SC
VALUES (201215121, 1, 92),
(201215121, 2, 85),
(201215121, 3, 88),
(201215122, 2, 90),
(201215122, 3, 80),
(201215122, 6, 59),
(201215123, 1, 84),
(201215125, 1, 60),
(201215125, 3, 90),
(201215133, 4, 87),
(201215137, 2, 79),
(201215139, 2, 80),
(201215140, 2, 81);

SET FOREIGN*KEY_CHECKS = 1;

问题代码

0
select Sno, Sname
from Student
1
select Sname, Sno, Sdept
from Student
2
select Sname, Sno, Sdept
from Student
3
select distinct Sno
from SC
4
select distinct Sname
from Student
where Sdept='CS'
5
select Sname, Sage
from Student
where Sage<20
6
select Sno
from SC
where Grade<60
7
select Sname, Sdept, Sage
from Student
where Sage>=20 and Sage<=23
8
select Sname, Sdept, Sage
from Student
where Sage<20 or Sage>23
9
select Sname, Ssex
from Student
where Sdept='CS' or Sdept='MA' or Sdept='IS'
10
select Sname, Ssex
from Student
where Sdept not in ('CS', 'MA', 'IS')
11
select Sname, Sno, Ssex
from Student
where Sname like '刘*'
12
select Sname, Sno
from Student
where Sname like '_立'
13
select Sname, Sno, Ssex
from Student
where Sname not like '刘_'
14
select Sno, Cno
from SC
where Grade is not null
15
select Sname
from Student
where Sdept='CS' and Sage<20
16
select Sno, Grade
from SC
where Cno='3'
order by Grade desc
17
select \*
from Student
order by Sdept asc, Sage desc
18
select count(Sno) as 'COUNT'
from Student
19
select count(Sno) as COUNT
from (
select Sno
from SC
group by sno) as test
20
select avg(Grade) as AVG
from SC
where Cno='1'
21
select max(Grade) as MAX
from SC
where Cno='1'
22
select SUM(Ccredit) as SUM
from Course, SC
where Course.Cno=SC.Cno
and Sno='201215121'
23
select Cno, count(Grade) as COUNT
from SC
group by Cno
24
select Sno
from(
select Sno, count(Grade) as COUNT
from SC
group by Sno
)as test
where COUNT>2
25
select Sno, AVG(Grade) as AVG
from SC
group by Sno
having AVG(Grade)>=88
26
select Student.Sno, Sname, Ssex, Sage, Sdept, Cno, Grade
from Student inner join SC
on Student.Sno=SC.Sno
27
SELECT x.Cno, y.Cpno
FROM Course x, Course y
WHERE x.Cpno = y.Cno
28
select Student.Sno, Student.Sname
from Student, SC
where Student.Sno=SC.Sno
and SC.Cno='2'
and SC.Grade>80
29
select test.Sno, test.Sname, Course.Cname, test.Grade
from Course join
(select SC.Sno, SC.Cno, Student.Sname, SC.Grade
from Student join SC
on Student.Sno=SC.Sno)as test
on Course.Cno=test.Cno

Movie 相关题目

30
select title
from movie
where director='Steven Spielberg'
31
select year
from movie
where mID in (
select mID
from rating
where stars='4' or stars='5'
)
order by year asc
32
select title
from movie
where mID not in (
select mID
from rating)
33
select name
from reviewer
where rID in (
select rID
from rating
where ratingDate is null)
34
select reviewer.name, test.title, test.stars, test.ratingDate
from reviewer join (
select movie.title, rating.rID, rating.stars, rating.ratingDate
from movie join rating
on movie.mID=rating.mID
) as test
on reviewer.rID=test.rID
order by reviewer.name, test.title, test.stars
35

37
select movie.title, rating_2.stars_avg as stars
from movie join (
select mID, AVG(stars) as stars_avg
from rating
group by mID) as rating_2
on movie.mID=rating_2.mID
order by stars desc, title
38
select name
from reviewer
where rID in(
select rID
from rating
group by rID
having COUNT(rID)>=3)
39
select name
from reviewer
where rID in (
select distinct rID
from rating
where mID in (
select mID
from movie
where title='Gone with the Wind'))
40
select temp.name as name, movie.title as title, temp.stars as stars
from movie join (
select rating.stars, reviewer.name, rating.mID
from rating join reviewer
on rating.rID=reviewer.rID) as temp
on movie.mID=temp.mID
where movie.director=temp.name

评论区

Twikoo giscus