sdnu-dboj-Student-Movie

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

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