sql查询语句练习

create table student(

sno varchar2(30) not null,

sname varchar2(40) not null,ssex varchar2(20) not null,

sbirthday date,class varchar2(50));

create table course(

cno varchar2(50) not null,

cname varchar2(100) not null,

tno varchar2(100) not null);

create table score(

sno varchar2(30) not null,

cno varchar2(50) not null,

degree number(10, 1) not null);

create table teacher(

tno varchar2(30) not null,

tname varchar2(40) not null,

tsex varchar2(20) not null,

tbirthday date not null,

prof varchar2(60),

depart varchar2(100) not null);

insert into student (sno,sname,ssex,sbirthday,class) values (108 ,'曾华' ,男' ,to_date('1977-09-01','yyyy-mm-dd'),95033);

insert into student (sno,sname,ssex,sbirthday,class) values (105 ,'匡明' ,男' ,to_date('1975-10-02','yyyy-mm-dd'),95031);

insert into student (sno,sname,ssex,sbirthday,class) values (107 ,'王丽' ,女' ,to_date('1976-01-23','yyyy-mm-dd'),95033);

insert into student (sno,sname,ssex,sbirthday,class) values (101 ,'李军' ,男' ,to_date('1976-02-20','yyyy-mm-dd'),95033);

insert into student (sno,sname,ssex,sbirthday,class) values (109 ,'王芳' ,女' ,to_date('1975-02-10','yyyy-mm-dd'),95031);

insert into student (sno,sname,ssex,sbirthday,class) values (103 ,'陆君' ,男' ,to_date('1974-06-03','yyyy-mm-dd'),95031);

insert into course(cno,cname,tno)values ('3-105' ,计算机导论',825);

insert into course(cno,cname,tno)values ('3-245' ,操作系统' ,804);

insert into course(cno,cname,tno)values ('6-166' ,数据电路' ,856);

insert into course(cno,cname,tno)values ('9-888' ,高等数学' ,100);

insert into score(sno,cno,degree)values (103,'3-245',86);

insert into score(sno,cno,degree)values (105,'3-245',75);

insert into score(sno,cno,degree)values (109,'3-245',68);

insert into score(sno,cno,degree)values (103,'3-105',92);

insert into score(sno,cno,degree)values (105,'3-105',88);

insert into score(sno,cno,degree)values (109,'3-105',76);

insert into score(sno,cno,degree)values (101,'3-105',64);

insert into score(sno,cno,degree)values (107,'3-105',91);

insert into score(sno,cno,degree)values (108,'3-105',78);

insert into score(sno,cno,degree)values (101,'6-166',85);

insert into score(sno,cno,degree)values (107,'6-106',79);

insert into score(sno,cno,degree)values (108,'6-166',81);

insert into teacher(tno,tname,tsex,tbirthday,prof,depart)

values (804,'李诚','男',to_date('1958-12-02','yyyy-mm-dd'),副教授','计算机系');

insert into teacher(tno,tname,tsex,tbirthday,prof,depart)

values (856,'张旭','男',to_date('1969-03-12','yyyy-mm-dd'),讲师','电子工程系');

insert into teacher(tno,tname,tsex,tbirthday,prof,depart)

values (825,'王萍','女',to_date('1972-05-05','yyyy-mm-dd'),助教','计算机系');

insert into teacher(tno,tname,tsex,tbirthday,prof,depart)

values (831,'刘冰','女',to_date('1977-08-14','yyyy-mm-dd'),助教','电子工程系');

1、查询student表中的所有记录的sname、ssex和class列。

select sname,ssex,class from student

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

select distinct depart from teacher

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

select * from student

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

select * from score where degree between 60 and 80

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

select * from score where degree in(85,86,88)

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

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

7、以class降序查询student表的所有记录。

select * from student order by class desc

8、以cno升序、degree降序查询score表的所有记录。

select * from score order by cno asc,degree desc

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

select count(*)from student where class = 95031

10、查询score表中的最高分的学生学号和课程号。

select sno 学生号，cno 课程号 from score where degree =(select max(degree) from score)

11、查询‘3-105’号课程的平均分。

select **g(degree) 平均分 from score where cno = 3-105'

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

select cno,**g(degree) 平均分 from score where cno like '3%' group by cno h**ing count(*)5

13、查询最低分大于70，最高分小于90的sno列。

select sno from score group by sno h**ing max(degree)<90 and min(degree)>70

14、查询所有学生的sname、cno和degree列。

select sname,cno,degree from student,score where

15、查询所有学生的sno、cname和degree列。

select sno,cname,degree from score,course where

16、查询所有学生的sname、cname和degree列。

select sname,cname,degree from student,score,course where and

17、查询“95033”班所选课程的平均分。

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

create table grade(low number(3,0),upp number(3),rank char(1));

insert into grade values(90,100,’a’);

insert into grade values(80,89,’b’);

sql查询语句练习

SQL查询语句练习

SQL语句查询练习

sql查询语句练习

其他用户还读了