学生表s(sno,sname,sage)
课程表C(cno,cname,credit);
选课表SC(sno,cno,grade);
最好不用exists!但是也可以用!
练习题一
create database mydb
go
use mydb
create table student
(
--学号
sno varchar(3) not null primary key,
--姓名
sname varchar(4) not null,
--性别
ssex varchar(2) not null,
--出生年月
sbirthday datetime,
--所在班级
class varchar(5)
)
create table teacher
(
--教工编号
tno varchar(3) not null primary key,
--教工姓名
tname varchar(4) not null,
--教工性别
tsex varchar(2) not null,
--教工出生日期
tbirthday datetime,
--职称
prof varchar(6),
--所在部门
depart varchar(10)
)
create table course
(
--课程号
cno varchar(5) not null primary key,
--课程名称
cname varchar(10) not null,
--教工编号
tno varchar(3) references teacher(tno)
)
create table score
(
--学号
sno varchar(3) not null references student(sno),
--课程号
cno varchar(5) not null references course(cno),
--成绩
degree decimal(4,1)
)
insert into student
values('108','曾华','男','1977-09-01','95033')
insert into student
values('105','匡明','男','1975-10-02','95031')
insert into student
values('107','王丽','女','1976-01-23','95033')
insert into student
values('101','李军','男','1976-02-20','95033')
insert into student
values('109','王芳','女','1975-02-10','95031')
insert into student
values('103','陆君','男','1974-06-03','95031')
insert into teacher
values('804','李诚','男','1958-12-02','副教授','计算机系')
insert into teacher
values('856','张旭','男','1969-03-12','讲师','电子工程系')
insert into teacher
values('825','王萍','女','1972-05-05','助教','计算机系')
insert into teacher
values('831','刘冰','女','1958-08-14','助教','电子工程系')
insert into course
values('3-105','计算机导论','825')
insert into course
values('3-245','操作系统','804')
insert into course
values('6-166','数字电路','856')
insert into course
values('9-888','高等数学','831')
insert into score
values('103','3-245','86')
insert into score
values('105','3-245','75')
insert into score
values('109','3-245','68')
insert into score
values('103','3-105','92')
insert into score
values('105','3-105','88')
insert into score
values('109','3-105','76')
insert into score
values('101','3-105','64')
insert into score
values('107','3-105','91')
insert into score
values('108','3-105','78')
insert into score
values('101','6-166','85')
insert into score
values('107','6-166','79')
insert into score
values('108','6-166','81')
select * from student
select * from teacher
select * from course
select * from score
--1、 查询Student表中的所有记录的Sname、Ssex和Class列。
--2、 查询教师所有的单位即不重复的Depart列。
--3、 查询Student表的所有记录。
--4、 查询Score表中成绩在60到80之间的所有记录。
--5、 查询Score表中成绩为85,86或88的记录。
--6、 查询Student表中“95031”班或性别为“女”的同学记录。
--7、 以Class降序查询Student表的所有记录。
--8、 以Cno升序、Degree降序查询Score表的所有记录。
--9、 查询“95031”班的学生人数。
--10、查询Score表中的最高分的学生学号和课程号。
--11、查询‘3-105’号课程的平均分。
--12、查询Score表中至少有5名学生选修的并以3开头的课程的平均分数。
--13、查询最低分大于70,最高分小于90的Sno列。
--14、查询所有学生的Sname、Cno和Degree列。
--15、查询所有学生的Sno、Cname和Degree列。
--16、查询所有学生的Sname、Cname和Degree列。
--17、查询“95033”班所选课程的平均分。
--18、假设使用如下命令建立了一个grade表:
--create table grade(low int,upp int,rank varchar(1))
--insert into grade values(90,100,'A')
--insert into grade values(80,89,'B')
--insert into grade values(70,79,'C')
--insert into grade values(60,69,'D')
--insert into grade values(0,59,'E')
--现查询所有同学的Sno、Cno和rank列。
--19、查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录。//无关子查询
--20、查询score中选学多门课程的同学中分数为非最高分成绩的记录。
--21、查询成绩高于学号为“109”、课程号为“3-105”的成绩的所有记录。
--22、查询和学号为108的同学同年出生的所有学生的Sno、Sname和Sbirthday列。
--23、查询“张旭“教师任课的学生成绩。
--24、查询选修某课程的同学人数多于5人的教师姓名。
--25、查询95033班和95031班全体学生的记录。
--26、查询存在有85分以上成绩的课程Cno.
--27、查询出“计算机系“教师所教课程的成绩表。
--28、查询“计算机系”与“电子工程系“不同职称的教师的Tname和Prof。
--29、查询选修编号为“3-105“课程且成绩至少高于选修编号为“3-245”的同学的Cno、Sno和Degree,并按Degree从高到低次序排序。
--30、查询选修编号为“3-105”且成绩高于选修编号为“3-245”课程的同学的Cno、Sno和Degree.
--31、查询所有教师和同学的name、sex和birthday.
--32、查询所有“女”教师和“女”同学的name、sex和birthday.
--33、查询成绩比该课程平均成绩低的同学的成绩表。
--34、查询所有任课教师的Tname和Depart.
--35 查询所有未讲课的教师的Tname和Depart.
--36、查询至少有2名男生的班号。
--37、查询Student表中不姓“王”的同学记录。
--38、查询Student表中每个学生的姓名和年龄。
--39、查询Student表中最大和最小的Sbirthday日期值。
--40、以班号和年龄从大到小的顺序查询Student表中的全部记录。
--41、查询“男”教师及其所上的课程。
--42、查询最高分同学的Sno、Cno和Degree列。
--43、查询和“李军”同性别的所有同学的Sname.
--44、查询和“李军”同性别并同班的同学Sname.
--45、查询所有选修“计算机导论”课程的“男”同学的成绩表。
--46、查询score表中分数最高的学生的信息。//多层嵌套
--47、查询score表中的平均分在80分以上的学生信息。//相关查询。无关查询
练习题二
创建以下三个表:
学生表: Student(Sno,Sname,Ssex,Sbirthday,Sage,Sdept)
Student 由学号 (Sno)、姓名 (Sname)、性别 (Ssex)、出生日期 (Sbirthday)、年龄 (Sage),
所在系 (Sdept) 五个属性组成,其中 Sno 为主键.
课程表: Coures(Cno,Cname,Cpno,Ccredit)
Coures 由课程号 (Cno)、课程名 (Cname)、选修课号 (Cpno)、学分 (Ccredit) 四个属性组成,
其中 Cno 为主键.
学生选课表: SC(Sno,Cno,Grade)
SC 由学号 (Sno)、 课程号 (Cno)、 成绩 (Grade) 三个属性组成, 主键为 (Sno,Cno).
按照以上三个表来做如下的习题:
1.查找全体学生的学号与姓名
2.查找全体学生的姓名、学号与所在系
3.查找全体学生的详细纪录
4.查找全体学生的姓名和其出生日期
5.查找全体学生的姓名、出生日期和所有系,要求用小写字母表示所有系名
6.查找全体学生的姓名、出生日期和所有系,要求给这几列起别名
7.查找选修了课程的学生学号(不能重复)
8.查找计算系全体学生的名单
9.查找所有年龄在20岁以下的学生姓名和其年龄(2种做法)
10.查找考试成绩有不及格的学生的学号(不能重复)
11.查找年龄在20--30岁(包括20岁和23岁)之间的学生的姓名、系别和年龄
12.查找年龄不在20--30岁(包括20岁和23岁)之间的学生的姓名、系别和年龄
13.查找信息系、数学系和计算机系学生的姓名与性别(用in做)
14.查找不是信息系、数学系,也不是计算机系的学生的姓名与性别(用not in做)
15.查找学号以为95001开头的学生的详细情况(用2种方法做)
16.查找所有以“刘”开头的学生的姓名、学号和性别
17.查找以“欧阳”开头的且三个汉字的学生的姓名
18.查找名字中第2个字为“阳”字的学生的姓名和学号
19.查找所有不以“刘”开头的学生姓名
20.查找以“C”开头的课程的课程号和学分
21.查找以“H”开头,且倒数第三个字符为“T”的课程的课程号和学分
22.某些学生选修课程后没有参加考试,所以有选课纪录,但没有考试成绩。查找缺少成绩的学生的学号和相应
的课程号
23.查找除了无成绩的学生的学号和课程号
24.查找计算机系年龄在20岁以下的学生姓名
25.查找是计算机系 或者是 数学系 或者是 信息系的学生姓名和性别
26.查找选修了3号课程的学生的学号与其成绩,其查找结果按分数的降序排列
27.查找全体学生情况,查找结果按所在系的系号升序排列,同一系中的学生按年龄降序排列
28.查找学生总人数
29.查找选修了课程的学生人数
30.计算1号课程的学生平均成绩
SQL语句练习题参考答案
1、 select Sname,Ssex,Class from Student;
2、 select distinct depart from teacher;
3、 select Sno as '学号',Sname as '姓名',Ssex as '性别',Sbirthday as'出生日期',Class as'班号'from student;
或
select Sno as 学号,Sname as 姓名,Ssex as 性别,Sbirthday as 出生日期,Class as 班号 from student;
4、 select * from score where degree between 60 and 80;
或select * from score where degree>=60 and degree<=80;
5、 select * from score where degree in (85,86,88);
6、 select * from student where class='95031'or Ssex='女';
7、 select * from student order by class desc;
8、 select * from score order by cno asc ,degree desc;
或select * from score order by cno ,degree desc;
9、 select count(*) as CNT from student where class='95031';
10、select Sno as '学号',cno as '课程号', degree as '最高分' from score
where degree=(select max(degree) from score)
11、select avg(degree)as 课程平均分 from score where cno='3-105';
12、select cno,avg(degree) from score where cno like'3%'group by cno having count(*) >5;
13、select Sno from score group by Sno having min(degree)>70 and max(degree)<90;
14、select student.Sname,score.Cno,score.degree from student,score where student.Sno=score.Sno;
15、select x.Sno,y.Cname,x.degree from score x,course y where x.Cno=y.Cno;
16、select x.Sname,y.Cname,z.degree from student x,course y,score z where x.Sno=z.Sno and z.Cno=y.Cno;
17、select y.Cno,avg(y.degree) from student x,score y where x.Sno=y.Sno and x.class='95033'group by y.cno;
18、select Sno,Cno,rank from score,grade where degree between low and upp order by rank;
19、select x.Cno,x.Sno,x.degree from score x,score y
where x.cno='3-105' and x.degree>y.degree and y.sno='109'and y.cno='3-105';
20、
1,查询成绩非本科最高 select * from score b where degree <(select max(degree) from score a where a.cno=b.cno);
2,查询成绩非本科最高并且选2门以上的学生的成绩:
21、select x.cno,x.Sno,x.degree from score x,score y where x.degree>y.degree and y.sno='109'and y.cno='3-105';
select cno,sno,degree from score where degree >(select degree from score where sno='109' and cno='3-105')
22、select sno,sname,sbirthday from student where to_char(sbirthday,'yyyy')=(select to_char(sbirthday,'yyyy') from student where sno='108');
23、select cno,sno,degree from score where cno=(select x.cno from course x,teacher y where x.tno=y.tno and y.tname='张旭');
24、select tname from teacher where tno in(select x.tno from course x,score y where x.cno=y.cno group by x.tno having count(x.tno)>5);
25、select * from student where class in('95033','95031');
26、select distinct cno from score where degree in (select degree from score where degree>85);
27、select * from score where cno in(select x.cno from course x,teacher y where y.tno=x.tno and y.depart='计算机系');
28、select tname,prof from teacher where depart='计算机系' and prof not in (select prof from teacher where depart='电子工程系');
29、select * from score where cno='3-105' and degree>any (select degree from score where cno='3-245')order by degree desc;
30、select * from score where cno='3-105' and degree>all(select degree from score where cno='3-245');
31、select tname,tsex,tbirthday from teacher
union select sname,ssex,sbirthday from student;
32、select tname,tsex,tbirthday from teacher where tsex='女'
union select sname,ssex,sbirthday from student where ssex='女';
33、select * from score a where degree<(select avg(degree)
from score b where a.cno=b.cno);
34、select tname,depart from teacher a where exists
(select * from course b where a.tno=b.tno);
35、select tname,depart from teacher a where not exists
(select * from course b where a.tno=b.tno);
36、select class from student where ssex='男'group by class having count(*)>=2;
37、select * from student where sname not like'王_';
38、select sname as 姓名,(to_char(sysdate,'yyyy')-to_char(sbirthday,'yyyy')) as 年龄 from student
39、select sname,sbirthday as 最大 from student where sbirthday =(select min (sbirthday) from student)
union select sname,sbirthday as 最小 from student where sbirthday =(select max(sbirthday) from student)
40、select class,sname,sbirthday from student order by class desc,sbirthday;
41、select x.tname,y.cname from teacher x,course y where x.tno=y.tno and x.tsex='男';
42、select * from score where degree=(select max(degree)from score);
43、select sname from student where ssex=(select ssex from student where sname='李军');
44、select sname from student where ssex=(select ssex from student where sname='李军') and class=(select class from student where sname='李军');
45、select * from score where sno in(select sno from student where ssex='男') and cno=(select cno from course
where cname='计算机导论');
select sname from S,C,SC where s.sno=SC.sno and C.cno=SC.cno and
cname not in (select aname from S,C,SC where s.sno=SC.sno and C.cno=SC.cno
and cname='zhangfan')
select
a.sname
from s a
inner join sc b on a.sno=sc.sno
inner join c on c.cno=sc.cno
where
not exists(select 1 from s inner join sc on s.sno=sc.sno where s.sname='zhangfan' and not exists(select 1 from sc as d where d.sno=a.sno and d.cno=sc.cno))
group by sname
having count(*)=(select count(1) from s inner join sc on s.sno=sc.sno where s.sname='zhangfan')
select
s.sname
from
学生表 s
where
sno in
(
select
s.sno
from
选课表sc ,课程表 c ,学生表 s
where
sc.cno=c.cno and s.sno=sc.sno and s.sname='zhangfan'
group by
s.sno
having count(1)=(select count(1) from 学生表 s,选课表 sc where s.sno=sc.sno where s.sname='zhangfan')
第1章 SQL语句查询 1.1 排序 通过order by语句,可以将查询出的结果进行排序。放置在select语句的最后。 格式: SELECT * FROM 表名 ORDER BY 排序字段ASC|DESC; ASC 升序 (默认) DESC 降序 1.查询所有商品信息,...
一、单表查询 1.1选择表中的若干列 1.1.1查询指定列 1.1.2查询全部列 1.1.3查询经过计算的值 1.1.4使用列别名改变查询结果的列标题 1.1.5更名运算 1.2选择表中的若干元组 1.2.1消除取值重复的行 1.2.2查询...
一、认识数据库 1、数据库的基本概念 2、数据库常用对象 3、数据库的组成 ...数据库主要由文件和文件组组成。数据库中所有的数据和对象都被存储在文件中。...(1)选择一个数据库——展开 表——...
sql查询当天时间 like关键字 将date转换成字符类型 where CONVERT(varchar,createTime,21) like ‘%2021-01-12%’ between 和 and 如果是date where createTimebetween ‘2019-05-01’ and ‘2019-05-31’ 如果...
一、单表查询 二、多表查询 三、综合查询 一、单表查询 1. 查询订购日期在1996年7月1日至1996年7月15日之间的订单的订购日期、订单ID、客户ID和雇员ID等字段的值 SELECT订购日期,订单ID,客户ID,雇员IDFROM`...
-- 1、??“c001”?程比“c002”?程成?高的所有学生的学号; SELECT S1.SNO FROM SC S1 JOIN SC S2 ON S1.SNO=S2.SNO WHERE S1.CNO='c001' AND S2.CNO='c002' AND S1.SCORE>S2.SCORE;...SELECT SNO,A
1.查询语句模板: 需要注意的是: FROM 才是 SQL 语句执行的第一步,并非 SELECT 。 SELETC 是在WHERE语句执行之后执行的,所以不能再WHERE语句后使用SELECT中设置的别名 WHERE是对分组前进行的过滤,...
1、复杂SQL查询 1.1、单表查询 (1)选择指定的列 [例]查询全体学生的学号和姓名 select Sno as 学号,Sname as 姓名 from student; select Sno,Sname from student; (2)查询全部列 [例]查询全体学生的详细...
SQL查询优化 一、获取有性能问题SQL的三种方法 通过用户反馈获取存在性能问题的SQL 通过慢查询日志获取存在性能问题的SQL 实时获取存在性能问题的SQL 二、慢查询日志介绍 1、使用慢查询日志获取有性能问题...
在 SQL 中,字符 “*” 具有特殊的含义,使用用它,将从指定的表中返回每一列,在这里由于没有使用 where 子句,所以将会返回每一行,即是 查询表中所有的行与列,就是所有的数据 。 还有一种写法就是分别列出每一列...
sql查询语句的处理步骤如下:--查询组合字段 (5)select (5-2) distinct(5-3) top()(5-1) --连表 (1)from (1-J)<left_table><join_type> join <right_table> on (1-A)<left_table>
SELECT Company, OrderNumber FROM Orders ORDER BY Company DESC, OrderNumber ASC #ASC升序,DESC倒序
执行sql语句: SELECT @@VERSION ...查询结果: Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64) Apr 2 2010 15:48:46 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit...
所有数据 使用语句 SELECT * FROM fishwords.words where rightratio=(SELECT max(rightratio) FROM fishwords.words); 查询结果
MYSQL: select * from tbl limit 100;ORACLE: select * from tbl where rownumSQL SERVER:select top 100 * from tblSYBASE: SET ROWCOUNT N GOSELECT * FROM TABLE1
SELECT * FROM 表名 WHERE 字段名 IS NOT NULL AND 字段名 <> '';
select * from 表名 order by 列名 desc (降序) limit 显示的条数
SQL Server 下 1 ...SELECT ROW_NUMBER() OVER(ORDER BY ID) ROWNU,A1 FROM TA1 ...解说:在这里,TA1是一个表,A1是表中的一个字段,表的...SQL Server 中的 ROW_NUMBER() 得到一个查询出的顺序,但这个函数
通过sql语句实现数量累加查询
使用中为了结果使用的方便,常常需要查询结果两列合并成一列,查询语句如下 select b+c as a from Table where d is not null 说明:a、b、c、d均为列名,Table为表名称,查询条件是列值不能为空
上图,因为自增列(b_Id)会断开,在做分页的时候不方便。可以人为地加一列行号,如下代码:SELECT * , ROW_NUMBER() OVER ( ORDER BY b_time ) AS 行号 FROM 表名结果如下图:
SELECT * FROM table1 UNION ALL SELECT * FROM table2
1 sql 查询某字段id为空 select * from 表名 where id is null ; 2 sql 查询某字段id不为空 select * from 表名 where id is not null; 或 select * from 表名...
sql 查询结果增加一列,列名固定,值也固定(mysql可行,其他未知)
SQL查询某字段最小值:(使用min() max()) SELECT * FROM stutable WHERE (age IN (SELECT MIN(age) FROM stutable)); SQL查询某字段最小值:(不使用min() max()) 例如数据表中有一列为...
2种方法, 1.在select后面加 distinct 可以过滤重复的...2.利用group by ,把查询到的字段都放在group by后面,点评:效率比distinct高,使用也比distinct麻烦点!sql语句: select name from ZonCanKu group by name
俗称嵌套查询。 看示例: select * from (select * from Table) as A 需要注意的是,在子查询的后面一定要指定子查询的结果,命名为A。否则会报错。
asc 按升序排列 (不用写,默认使用这个) desc 按降序排列 例: select * from yourtable where 查询条件 order by id desc limit 0,10; 按id倒序排列,且取前10条。