1+x练习题1

CREATE TABLE students (
sno char(7) ,
sname char(8) NOT NULL,
class char(10),
ssex char(2),
bday date ,
bplace char(10) ,
IDNum char(18) ,
sdept char(16),
phone char(11),
PRIMARY KEY (sno)
) ;

CREATE TABLE sc (
sno char(7) ,
cno char(7) ,
score decimal(4,1),
point decimal(2,1),
PRIMARY KEY (sno,cno)
) ;
CREATE TABLE teachers (
tno char(3) ,
tname char(8),
ps char(10),
tbirthday date ,
tdept char(16) ,
tsex char(2),
PRIMARY KEY (tno)
) ;

CREATE TABLE teaching (
sid int ,
cterm int,
class char(10) DEFAULT NULL,
cno char(7) NOT NULL,
tno char(3) DEFAULT NULL,
period int DEFAULT NULL,
PRIMARY KEY (sid)
) ;

CREATE TABLE course (
cno char(7) ,
cname varchar(20) NOT NULL,
cpno char(7),
ccredit int NOT NULL,
PRIMARY KEY (cno)
) ;

1.检索出students表中所有学生的姓名以及到2050年时的年龄的记录，
要求结果中列名显示中文名。

SELECT sname as 姓名,(year('2050-01-01')-year(bday)) as 年龄 from students;

2.检索出students表中至2050年所有年龄小于等于55岁的女生的学号和姓名的记录。

select sno as 学号,sname as 姓名,ssex as 性别
from students
where ssex = '女' and year(bday) >= 1995

3.检索出students表中“信息学院”的学生姓名、性别和出生日期的记录。

select sname,ssex,bday from students where sdept="信息学院";

4.，将Student表中所有符合id>50的记录的name字段改成"test"。
检索出Student表中所有符合id > 50的记录。
在Student表中插入一条id=99, name="test"的记录。
删除Student表中所有id>50的记录。

update Student set name="test" where id>50;
select *  from Student where id>50;
insert into Student values(99,"test");
delete from Student where id>50;

5.检索出students表中所有系名的记录，要求结果中系名不重复。

select distinct sdept from students;

6.检索出course表中“0000010”课程的课名、先修课号和学分记录。

select cname,cpno,ccredit from course where cno=0000010;

7.检索出``sc```表中成绩在80～90分之间的选课成绩情况

select * from sc where score between 80 and 90;

8.检索出``sc```表中成绩为69分、79分或89分的记录。

select * from sc where score in(69 ,79 , 89);

9.检索出``sc```表中有成绩的学生学号和课号的记录。

select sno,cno from sc where score is not null;

10.检索出``sc```表中至少选修一门课程的学生学号的记录。

select sno
from sc
group by sno
having count(cno)>0;

11.统计``sc```表中，每个学生的选课门数和考试总成绩，并按选课门数升序排列，要求结果中列名分别显示"学号“、“选课门数”、“考试总成绩”。

select sno as 学号,count(*) as 选课门数,sum(score) as 考试总成绩
from sc
group by sno

12.统计出students表中各班男、女生人数。

select class 班级,ssex 性别,count(*) as 人数
from students
group by class,ssex;```

13.检索出students表、sc表中信息学院女学生的学生学号、姓名、课号及考试成绩。

```javascript
select students.sno,sname,cno,score from students join sc on students.sno=sc.sno where sdept="信息学院" and ssex="女";

14.检索出students表、sc表中“陈红”同学所选课程的成绩，列出课号和成绩(不考虑重名)。

select sc.cno,score from students join sc on students.sno=sc.sno where sname="陈红";

15检索出``teachers 、teaching、course```表中“王珊”老师所授课程的课程名称。

select distinct cname
from course 
inner join teaching on teaching.cno=course.cno
inner join teachers on teachers.tno=teaching.tno
where tname='王珊';

16.检索出``teachers 、teaching、course```表中女教师所授课程的课程号和课程名称。

select distinct course.cno,cname
from course 
inner join teaching on teaching.cno = course.cno
inner join teachers on teachers.tno = teaching.tno
where tsex='女';

17.检索出students表、sc表中至少选修2门课程的女生姓名

select distinct sname
from students 
inner join sc on sc.sno=students.sno
where ssex='女'
group by sname 
having count(*)>=2;

18.检索出sc表、course表中选修课名中含有“数据库”三个字的课程且成绩在80～90分之间的学生学号及成绩。

// An highlighted block
var foo = 'bar';

select sno,score
from sc
where cno in
(
select cno
from course
where cname like '%数据库%'
)
and score between 80 and 90

19.检索出students表、sc表中选修“0000011”课程的学生至2050年时平均年龄，要求结果中列名显示“平均年龄”。

select avg(2050-year(bday)) as 平均年龄
from students 
where sno in (select sno from sc where cno = '0000011');

20.检索出``teachers 、teaching、sc```表中“谭浩强”教师任课的课程号，选修其课程的学生的学号和成绩，结果中包括该老师没有被选修的课程。

select b.cno cno,c.sno sno,c.score
from teachers as a
left join teaching as b on b.tno=a.tno
left join sc as c on b.cno=c.cno 
where a.tname='谭浩强'

21.检索出students表和sc表中所有学生的选课情况（包括学号，姓名，课号，成绩），结果中包括没有选课的学生。

select students.sno,sname,cno,score
from students
left join sc on students.sno = sc.sno;

22.检索出students表和sc表中没有选课的学生学号和姓名

select sno,sname
from students
where sno not in (select sno from sc)

23.检索出students表和sc表中信息学院学生所选修的课程号和成绩

select cno,score
from sc
where sno in
(
select sno
from students
where sdept = '信息学院'
) 

24.检索出students表中与“陆毅”同一个系的同学姓名。

select sname
from students
where sdept = (select sdept from students where sname = '陆毅') and sname != '陆毅'

25.检索出students表和sc表中“19信管2”班的学生所选修的课程号。

select cno
from students,sc
where students.sno = sc.sno and class='19信管2';

26.检索出students表和sc表中“陈晓东”同学所选课程的课号及成绩。

select cno,score
from students
inner join sc on students.sno = sc.sno
where sname = '陈晓东'

27.检索出students表和sc表中选修了课号为“0000034”的学生学号和姓名。

select sc.sno,sname
from students,sc
where students.sno = sc.sno and cno = '0000034';

28.检索出students表和sc表中“0000008”号课程不及格的学生信息。

select *
from students
where sno in (select sno from sc where score < 60 and cno = '0000008');

29.

select cname
from course
where cno in
(
    select cno
    from sc
    where sno = (select sno from students where sname = '李小鹏')
);

30.检索出``teachers 、teaching、course```表中“王珊”老师所授课程的课程名称。

select cname 
from course
where cno in 
(
    select cno
    from teaching
    where tno = (select tno from teachers where tname = '王珊')
);

31.把````sc```表中“李小鹏”同学对应选课成绩全部删除。

delete from sc
where sno in (select sno from students where sname = '李小鹏');