mysql新手进级02分分快三全天计划网站

作者:分分快三全天计划网站

6.输出student中不姓王的同学的姓名。
命令:
select name from Student where name not like '王%';
分分快三全天计划网站 1

在表中输入数据

37.列出没有实际授课的教师的姓名和系别
命令:select distinct name, depart from Teacher where no not in (select tno from Course, Score where Course.cno = Score.cno);

Select tname,depart from teacher where tno in (select tno from course where cno in (select cno from score group by cno))

12.输出95001班级的学生人数
命令:
select count(*) from Student where class = 95001;
分分快三全天计划网站 2

  

35.输出选修3-105课程,其成绩高于109号同学在此课程所得成绩的所有同学的学号,姓名
命令:select a.no, Student.name from Score a, Score b, Student where a.cno = '3-105' and b.cno = '3-105'and b.no = 109 and a.degree > b.degree and a.no = Student.no;

表(二)Course(课程表)

10.输出男生人数及这些男生分布在多少个班级中
命令:
select count(*), count(distinct class) from Student where sex = '男';
分分快三全天计划网站 3

create database zy
go
use zy
go

Student表:
分分快三全天计划网站 4

union

命令:
insert into Student values(5001,'李勇','男','1987-07-22 00:00:00.000',95001);
insert into Student values(5002,'刘晨','女','1987-11-15 00:00:00.000',95002);
insert into Student values(5003,'王敏','女','1987-10-5 00:00:00.000',95001);
insert into Student values(5004,'李好尚','男','1987-9-25 00:00:00.000',95003);
insert into Student values(5005,'李军','男','1987-7-17 00:00:00.000',95004);
insert into Student values(5006,'范新位','女','1987-6-18 00:00:00.000',95005);
insert into Student values(5007,'张霞东','女','1987-8-29 00:00:00.000',95006);
insert into Student values(5008,'赵薇','男','1987-6-15 00:00:00.000',95007);
insert into Student values(5009,'钱民将','女','1987-6-23 00:00:00.000',95008);
insert into Student values(5010,'孙俪','女','1987-9-24 00:00:00.000',95002);
insert into Student values(108,'赵里','男','1987-6-15 00:00:00.000',95007);
insert into Student values(109,'丘处机','男','1987-6-23 00:00:00.000',95008);
insert into Student values(107,'杨康','男','1987-9-24 00:00:00.000',95001);

表(四)Teacher(教师表)

insert into Teacher values('1','李卫','男','1957-11-5','教授','电子工程');
insert into Teacher values('2','刘备','男','1967-10-9','副教授','math');
insert into Teacher values('3','关羽','男','1977-9-20','讲师','cs');
insert into Teacher values('4','李修','男','1957-6-25','教授','elec');
insert into Teacher values('5','诸葛亮','男','1977-6-15','教授','计算机系');
insert into Teacher values('6','殷素素','女','1967-1-5','副教授','cs');
insert into Teacher values('7','周芷若','女','1947-2-23','教授','cs');
insert into Teacher values('8','赵云','男','1980-6-15','副教授','计算机系');
insert into Teacher values('9','张敏','女','1985-5-5','助教','cs');
insert into Teacher values('10','黄蓉','女','1967-3-22','副教授','cs');
insert into Teacher values('11','张三','男','1967-3-22','副教授','cs');

select tname,tsex,tbirthday from Teacher where Tsex='女'

27.列出所讲课已被选修的教师的姓名和系别
命令:select distinct name, depart from Teacher, Course, Score where Teacher.no = Course.tno and Course.cno = Score.cno;
分分快三全天计划网站 5

3、查询Student表的所有记录。

5.列出student表中所有记录的name、sex和class列
命令:
select name, sex, class from Student;
分分快三全天计划网站 6

设有一数据库,包括四个表:学生表(Student)、课程表(Course)、成绩表(Score)以及教师信息表(Teacher)。四个表的结构分别如表1-1的表(一)~表(四)所示,数据如表1-2的表(一)~表(四)所示。用SQL语句创建四个表并完成相关题目。

22.列出存在有85分以上成绩的课程名称
命令:
select cname from Course where cno in (select cno from Score where degree > 85 group by cno);

28、查询出“计算机系“教师所教课程的成绩表。

insert into Score values(5001,'3-105',69);
insert into Score values(5001,'5-102',55);
insert into Score values(5003,'4-108',85);
insert into Score values(5004,'3-105',77);
insert into Score values(5005,'3-245',100);
insert into Score values(5006,'3-105',53);
insert into Score values(5003,'4-109',45);
insert into Score values(5008,'3-105',98);
insert into Score values(5004,'4-109',68);
insert into Score values(5010,'3-105',88);
insert into Score values(5003,'3-105',98);
insert into Score values(5005,'4-109',68);
insert into Score values(5002,'3-105',88);
insert into Score values(107,'3-105',98);
insert into Score values(108,'4-109',68);
insert into Score values(109,'3-105',88);
insert into Score values(109,'4-109',80);
insert into Score values(107,'3-111',88);
insert into Score values(5003,'3-111',80);
单表查询
3.以class降序输出student的所有记录(student表全部属性)
命令:
select * from Student order by class desc;
分分快三全天计划网站 7

4、查询Score表中成绩在60到80之间的所有记录。

4.列出教师所在的单位depart(不重复)。
命令:
select count(depart) from Teacher;

(1)select * from score where degree =(select max(degree)from score)

Score表:
分分快三全天计划网站 8

union

23.列出“计算机系”教师所教课程的成绩表(课程编号,课程名,学生名,成绩)。
命令:
select Score.cno, cname, Student.name, degree from Teacher, Course, Student, Score where Teacher.depart = '计算机系' and Teacher.no = Course.tno and Course.cno = Score.cno and Student.no = Score.no;
分分快三全天计划网站 9

select Sname,Sno,degree from Score join course on Score.Cno =Course.Cno

24.列出所有可能的“计算机系”与“电子工程系”不同职称的教师配对信息,要求输出每个老师的姓名(name)和(职称)
命令:select a.name, a.prof, b.name, b.prof from Teacher a, Teacher b where a.depart in ('计算机系', '电子工程') and b.depart in('计算机系', '电子工程') and a.prof != b.prof and a.depart != b.depart;

select * from Student

14.输出student中最大和最小的birthday日期值
命令:
select max(birthday), min(birthday) from Student;

select class,sbirthday from Student order by Class desc,Sbirthday asc

分分快三全天计划网站 10

6、查询Student表中“95031”班或性别为“女”的同学记录。

38.列出选修了编号为‘3-105’课程且其成绩高于‘4-109’课程最高成绩的同学的 课程编号,学号和成绩
命令:
select cno, no, degree from Score where degree > (select max(degree) from Score where cno = '4-109') and cno = '3-105';
分分快三全天计划网站 11

select * from Score where Degree in('85','86','88')

26.显示‘张三’教师任课的学生姓名,课程名,成绩
命令:select Student.name, cname, degree from Teacher, Student, Course, Score where Teacher.name = '张三' and Teacher.no = Course.tno and Course.cno = Score.cno and Student.no = Score.no;
分分快三全天计划网站 12

20、查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录。

31.出与“李军”同性别的所有同学的name。
命令:
select a.name from Student a, Student b where a.sex = b.sex and b.name = '李军';
分分快三全天计划网站 13

insert into grade values(0,59,’E’)

19.输出’95001’班级所选课程的课程号和平均分
命令:select cno, avg(degree) from Score where no in (select no from Student where class = 95001) group by cno;
分分快三全天计划网站 14

select Sname,Ssex,Sbirthday from Student

子查询
34.输出score中成绩最高的学号和课程号
命令:select no, cno from Score where degree = (select max(degree) from Score);

select cname,sname,degree  from score ,student,course where student.sno=score.sno and score.cno=course.cno

聚合查询
16.输出至少有5个同学选修的并以3开头的课程的课程号,课程平均分,课程最高分,课程最低分。
命令:select cno, avg(degree), max(degree), min(degree) from Score where cno like '3%' group by cno having count(*) >= 5;
分分快三全天计划网站 15

23、查询和学号为108的同学同年出生的所有学生的Sno、Sname和Sbirthday列。

分分快三全天计划网站 16

27、查询存在有85分以上成绩的课程Cno.

30.输出男教师所上课程名称。
命令:
select cname from Teacher, Course where Teacher.sex = '男' and Course.tno = Teacher.no;
分分快三全天计划网站 17

select Degree from Score where Cno in (select Cno from Course where Tno in (select Tno from Teacher where Tname='张旭'))

25.列出所有处于不同班级中,但具有相同生日的学生,要求输出每个学生的学号和姓名。(提示:使用datediff函数,具体用法可以参考:)
命令:select a.no, a.name, b.no, b.name from Student a, Student b where a.class != b.class and datediff(day, a.birthday, b.birthday) = 0;
分分快三全天计划网站 18

select cname,sname,degree  from score join student  on student.sno=score.sno join course on score.cno=course.cno

命令:
create table Student (no int, name varchar(30), sex char(2), birthday datetime, class int);
create table Teacher (no int, name varchar(30), sex char(2), birthday datetime, prof varchar(20), depart varchar(30));
create table Course (cno varchar(10), cname varchar(30), tno int);
create table Score (no int, cno varchar(10), degree float);
DML
2.给出相应的INSERT语句来完成题中给出数据的插入。

select sno,cno,rank from score ,grade where score.degree between low and upp

29.列出所有任课教师的name和depart。(从课程选修和任课两个角度考虑)
命令:select distinct name, depart from Teacher, Course where Teacher.no = Course.tno;
select distinct name, depart from Teacher, Course, Score where Teacher.no = Course.tno and Score.cno = Course.cno;
分分快三全天计划网站 19

39、查询Student表中最大和最小的Sbirthday日期值。

8.输出班级为95001或性别为‘女’ 的同学(student表全部属性)
命令:
select * from Student where class = 95001 or sex = '女';
分分快三全天计划网站 20

24、查询“张旭“教师任课的学生成绩。

17.输出所选修课程中最低分大于70分且最高分小于90分的学生学号及学生姓名
命令:
select no,name from Student where no in(select no from Score group by no having min(degree) > 70 and max(degree) < 90);
分分快三全天计划网站 21

37、查询至少有2名男生的班号。

11.列出存在有85分以上成绩的课程编号。
命令:
select cno from Score where degree > 85 group by cno;

17、查询“95033”班学生的平均分。

根据上面描述完成下面问题:
(注意:注意保存脚本,尤其是DDL和DML,以便进行数据还原)
DDL
1.写出上述表的建表语句。

41、查询“男”教师及其所上的课程。

分分快三全天计划网站 22

表(一)Student (学生表)                        

20.输出至少有两名男同学的班级编号。
命令:select class from Student where sex = '男' group by class having count(*) >= 2;
分分快三全天计划网站 23

select sname from Student where Ssex=(select Ssex from Student where Sname='李军')

7.输出成绩为85或86或88或在60-80之间的记录(no,cno,degree)
命令:
select no, cno, degree from Score where degree in(85, 86, 88) or degree between 60 and 80;
分分快三全天计划网站 24

select Sname,Cno,degree from score join student on score.sno =student.sno  ---连接查询

9.以cno升序、degree降序输出score的所有记录。(score表全部属性)
命令:
select * from Score order by cno asc, degree desc;

创建数据库和表

云想衣裳花想容,春风拂槛露华浓。
若非群玉山头见,会向瑶台月下逢。

select tname,cname from teacher ,course where teacher.tno=course.tno and tsex='男'

分分快三全天计划网站 25

38、查询Student表中不姓“王”的同学记录。

分分快三全天计划网站 26

16、查询所有学生的Sname、Cname和Degree列。

分分快三全天计划网站 27

select tname,prof from Teacher where Depart= '电子工程系' and prof not in(select prof from Teacher where Depart='计算机系')

select cname from Course, (select cno from Score where Score.degree > 85 group by cno) t where Course.cno = t.cno;
分分快三全天计划网站 28

15、查询所有学生的Sno、Cname和Degree列。

36.列出成绩比该课程平均成绩低的同学的学号,成绩和该门课的平均成绩
命令:
select no, degree, avg_degree from Score, (select cno, avg(degree) avg_degree from Score group by cno) t where Score.degree < t.avg_degree and Score.cno = t.cno;
分分快三全天计划网站 29

select Cno,avg(degree) from Score where cno like '3%' group by Cno having count(Sno)>=5  ----聚合函数

分分快三全天计划网站 30

select * from student where  class='95031' or ssex='女'

Teacher表:
分分快三全天计划网站 31

表(三)Score

32.输出选修“数据结构”课程的男同学的成绩。
命令:
select degree from Score, Course, Student where Score.cno = Course.cno and Course.cname = '数据结构' and Student.no = Score.no and Student.sex = '男';
分分快三全天计划网站 32

Cno

Cname

Tno

3-105

计算机导论

825

3-245

操作系统

804

6-166

数字电路

856

9-888

高等数学

831

18.显示所教课程选修人数多于5人的教师姓名
命令:
select name from Teacher where no in(select tno from
Course where cno in (select cno from score
group by cno having count(cno) > 5));
分分快三全天计划网站 33

insert into grade values(60,69,’D’)

insert into Course values('3-101','数据库',1);
insert into Course values('5-102','数学',3);
insert into Course values('3-103','信息系统',4);
insert into Course values('3-104','操作系统',6);
insert into Course values('3-105','数据结构',4);
insert into Course values('3-106','数据处理',5);
insert into Course values('4-107','Pascal语言',5);
insert into Course values('4-108','c ',7);
insert into Course values('4-109','Java',8);
insert into Course values('3-245','数据挖掘',10);
insert into Course values('3-111','软件工程',11);

12、查询Score表中至少有5名学生选修的并以3开头的课程的平均分数。

多表查询
21.列出与108号同学同年出生的所有学生的学号、姓名和生日
命令:select a.no, a.name, a.birthday from Student a, Student b where b.no = 108 and year(b.birthday) = year(a.birthday)

11、查询每门课的平均成绩。

39.**列出符合下述条件的所有可能的同学配对(sno1,sname1,sno2,sname2,difference)。其中要求学号为sno1的sname1同学的所学课程的平均分大于学号为sno2的sname2同学的所学课程平均分,两个同学的课程平均分的差值difference为(sno1同学平均分-sno2同学平均分)
命令:
select a.no, a.name, b.no, b.name, c.avgdegree - d.avgdegree difference from Student a, Student b, (select no, avg(degree) avgdegree from Score group by no) c,(select no, avg(degree) avgdegree from Score group by no) d where a.no = c.no and b.no = d.no and c.avgdegree > d.avgdegree;
分分快三全天计划网站 34

10、查询Score表中的最高分的学生学号和课程号。(子查询或者排序)

28.输出所有学生的name、no和degree。(degree为空的不输出和为空的输出两种情况)。
命令:select name, Student.no, degree from Student, Score where Student.no = Score.no;
select name, Student.no, degree from Student left join Score on Student.no = Score.no;
分分快三全天计划网站 35

select tname from teacher where tno in (select tno from Course where Cno in (select Cno from Score group by Cno having COUNT(*)>5))

33.列出选修编号为‘3-105’课程且该门课程成绩比课程‘3-111’的最高分要高的cno,no和degree。
命令:select Score.cno, no, degree from Score, (select cno, max(degree) maxdegree from Score group by cno) t where Score.cno = '3-105' and t.cno = '3-111' and Score.degree > t.maxdegree;
分分快三全天计划网站 36

select sname as 姓名,year(getdate())-year(sbirthday) as 年龄 from student

15.显示95001和95004班全体学生的全部个人信息(不包括选课)。(student表全部属性)
命令:
select * from Student where class = 95001 or class = 95004;
分分快三全天计划网站 37

select Sname,Ssex,Sbirthday from Student where Ssex='女'

分分快三全天计划网站 38

select AVG(degree) from score where sno in(select sno from student where class='95033')

现在有一教学管理系统,具体的关系模式如下:
Student (no, name, sex, birthday, class)
Teacher (no, name, sex, birthday, prof, depart)
Course (cno, cname, tno)
Score (no, cno, degree)
其中表中包含如下数据:
Course表:
分分快三全天计划网站 39

Sno

13.输出‘3-105’号课程的平均分
命令:
select avg(degree) from Score where cno = '3-105';
分分快三全天计划网站 40

insert into grade values(90,100,’A’)

分分快三全天计划网站 41

主码:Sno Cno

select * from Score where cno='3-105' and degree>any(select degree from Score where cno='3-245')**order by degree desc——any其中任何一个、all所有**

select tname,tsex,tbirthday from Teacher

Select tname,depart from teacher where tno in (select tno from course where cno in (select distinct cno from score))

Sno

Cno

Degree

103

3-245

86

105

3-245

75

109

3-245

68

103

3-105

92

105

3-105

88

109

3-105

76

101

3-105

64

107

3-105

91

108

3-105

78

101

6-166

85

107

6-166

79

108

6-166

81

select tname,prof from teacher a where prof not in (select prof from teacher b where b.depart!=a.depart)

 

select * from student where class in('95033','95031')

insert into grade values(70,79,’C’)

select degree from Score where Sno in(select Sno from Student where Ssex='男')and cno in (select Cno from Course where Cname='计算机导论')

分分快三全天计划网站 42分分快三全天计划网站 43分分快三全天计划网站 44

select sname from Student where Sname not like '王%'

45、查询所有选修“计算机导论”课程的“男”同学的成绩表。

Degree

35、 查询所有任课教师的Tname和Depart.

25、查询选修某课程的同学人数多于5人的教师姓名。

表(四)Teacher

select * from score where cno='3-105' and degree>(select degree from score where sno='109' and cno='3-105')

表1-2数据库中的数据

课程号(外码)

create table Student
(
Sno varchar (20) not null primary key,
Sname varchar (20) not null,
Ssex varchar (20) not null,
Sbirthday datetime,
Class varchar (20)
)
go
create table Teacher
(
Tno varchar (20) not null primary key,
Tname varchar (20) not null,
Tsex varchar (20) not null,
Tbirthday datetime,
Prof varchar (20),
Depart varchar (20) not null
)
go
create table Course
(
Cno varchar (20) not null primary key,
Cname varchar (20) not null,
Tno varchar (20) not null references Teacher(Tno)
)
go
create table Score
(
ids int identity primary key,
Sno varchar (20) not null references Student(Sno),
Cno varchar (20) not null references Course(Cno),
Degree Decimal(4,1)
)

varchar (20)

26、查询95033班和95031班全体学生的记录。

select class from Student where Ssex='男' group by Class having COUNT (*)>=2

 

表(二)Course

13、查询分数大于70,小于90的Sno列。

select tname,prof from Teacher where Depart= '计算机系' and prof not in(select prof from Teacher where Depart='电子工程系')

select Cno,Sno,Degree from Score where cno='3-105' and degree>(select max(degree) from Score where cno='3-245')

select * from score where cno='3-105' and degree >(select max(degree) from score where sno=’109’)

属性名

数据类型

可否为空

含 义

Sno

varchar (20)

学号(主码)

Sname

varchar (20)

学生姓名

Ssex

varchar (20)

学生性别

Sbirthday

datetime

学生出生年月

Class

varchar (20)

学生所在班级

8、以Cno升序、Degree降序查询Score表的所有记录。

1、查询Student表中的所有记录的Sname、Ssex和Class列。

union

Select * from score a where degree<(select avg(degree) from score b where a.cno=b.cno)

select * from Score where Degree=85 or Degree=86 or Degree=88;

select sname from Student where Ssex=(select Ssex from Student where Sname='李军')and class=(select Class from Student where Sname='李军')

 

39、查询Student表中每个学生的姓名和年龄。

14、查询所有学生的Sname、Cno和Degree列。

44、查询和“李军”同性别并同班的同学Sname.

select cno,avg(degree) from Score group by cno

属性名

数据类型

可否为空

含 义

Tno

varchar (20)

教工编号(主码)

Tname

varchar (20)

教工姓名

Tsex

varchar (20)

教工性别

Tbirthday

datetime

教工出生年月

Prof

varchar (20)

职称

Depart

varchar (20)

教工所在部门

36、查询所有未讲课的教师的Tname和Depart.

42、查询最高分同学的Sno、Cno和Degree列。

select * from Score order by Cno asc,Degree desc

21、查询score中选学多门课程的同学中分数为非最高分成绩的记录。

表(三)Score(成绩表)

select Sname,Ssex,Class from Student

可否为空

2、查询教师所有的单位即不重复的Depart列。

varchar (20)

22、查询成绩高于学号为“109”、课程号为“3-105”的成绩的所有记录。

select Sno from Score where Degree >70and Degree<90

表1-1数据库的表结构

5、查询Score表中成绩为85,86或88的记录。

select distinct cno from Score where Degree>85

insert into grade values(80,89,’B’)

19、现查询所有同学的Sno、Cno和rank列。

分分快三全天计划网站 45

create table grade(low  int(3),upp  int(3),rank  char(1))

select * from score where cno='3-105' and degree>(select degree from score where sno='109' and cno='3-105')

成绩

含 义

Select tname,depart from teacher where tno in (select tno from course where cno not in (select distinct **cno from score))**

43、查询和“李军”同性别的所有同学的Sname.

select * from Score where degree between 60 and 80

select * from student where year(sbirthdy)=(select year(sbirthday) from student where sno='108')

数据类型

40、以班号和年龄从大到小的顺序查询Student表中的全部记录。

 

18、假设使用如下命令建立了一个grade表:

表(一)Student

29、查询“计算机系”与“电子工程系“不同职称的教师的Tname和Prof。

select distinct Depart from  Teacher -----去重查询

Cno

select * from Score where Cno in(select Cno from Course where Tno in(select tno from Teacher where Depart='计算机系'))

select * from Student order by Class desc

(2)select top 1 * from score order by degree desc    —只适用于一个最高分

30、查询选修编号为“3-105“课程且成绩至少高于选修编号为“3-245”的同学的Cno、Sno和Degree,并按Degree从高到低次序排序。

属性名

数据类型

可否为空

含 义

Cno

varchar (20)

课程号(主码)

Cname

varchar (20)

课程名称

Tno

varchar (20)

教工编号(外码)

select sno,cno from Score where Degree=(select max(degree) from Score)  ---子查询

33、查询所有“女”教师和“女”同学的name、sex和birthday.

select * from score a where sno in (select sno from score group by sno having count(*)>1) and degree <(select max(degree) from score b where b.cno=a.cno )

select sname as 姓名,datediff(year,sbirthday,getdate()) as 年龄 from student

 

34、查询成绩比该课程平均成绩低的同学的成绩表。

7、以Class降序查询Student表的所有记录。

Tno

Tname

Tsex

Tbirthday

Prof

Depart

804

李诚

1958-12-02

副教授

计算机系

856

张旭

1969-03-12

讲师

电子工程系

825

王萍

1972-05-05

助教

计算机系

831

刘冰

1977-08-14

助教

电子工程系

select cname from course where tno in(select tno from teacher where tsex=’男’)

属性名

学号(外码)

select * from score where sno in (select sno from score group by sno having count(*)>1) and degree <(select max(degree) from score )

Sno

Sname

Ssex

Sbirthday

class

108

曾华

1977-09-01

95033

105

匡明

1975-10-02

95031

107

王丽

1976-01-23

95033

101

李军

1976-02-20

95033

109

王芳

1975-02-10

95031

103

陆君

1974-06-03

95031

32、查询所有教师和同学的name、sex和birthday.

select * from Student where Class='95033' or class='95031'

 

Decimal(4,1)

Select count(*) from student where class='95031'

31、查询选修编号为“3-105”且成绩高于选修编号为“3-245”课程的同学的Cno、Sno和Degree.

select MAX(sbirthday) as '最大值',MIN(sbirthday)'最小值' from Student

9、查询“95031”班的学生人数。

本文由分分快三计划发布,转载请注明来源

关键词: 分分快三计划