SQL复试面试经典题 已实操

创表：

drop table if exists sc;
drop table if EXISTS  student ;

create table student (
sno char(13) primary key,
sname varchar(20),
ssex char(2),
sage SMALLINT
);
 
drop table if exists course;
 create table  course(
 cno char(13) PRIMARY KEY,
 cname varchar(20),
 tno char(9)
);
drop table if exists teacher;
create table teacher(
tno char(13) primary key,
tname varchar(20)
);

drop table if exists sc;
 create table sc(
 sno char(13) ,
 cno char(13),
 grade int ,
 primary key(sno,cno),
 FOREIGN KEY (sno) REFERENCES student(sno),
 FOREIGN KEY (cno) REFERENCES course(cno)
 );


 
 

插入数据：本来想用Tsql可是navicat不能执行

INSERT INTO student VALUES ( '201512215', '穆丽娜', '女', 23 );
INSERT INTO student VALUES ( '201512214', '李欣燃', '女', 22 );
INSERT INTO student VALUES ( '201512213', '李四', '男', 21 );
INSERT INTO student VALUES ( '201512212', '王五', '男', 50 );
INSERT INTO student VALUES ( '201512210', '张华', '男', 26 );
INSERT INTO student VALUES ( '201512211', '欧阳娜娜', '女', 23 );
INSERT INTO student VALUES ( '201512216', '陶茜茜', '女', 23 );
INSERT INTO student VALUES ( '201512217', '周明杰', '女', 22 );
INSERT INTO student VALUES ( '201512219', '马乐', '男', 21 );
INSERT INTO student VALUES ( '201512218', '杨晓娣', '女', 24 );
INSERT INTO student VALUES ( '201512220', '欧阳锋', '男', 26 );
INSERT INTO student VALUES ( '201512221', '赵明', '男', 25 );



INSERT INTO teacher VALUES ( '201510', '赵海萍');
INSERT INTO teacher VALUES ( '201511', '叶平');
INSERT INTO teacher VALUES ( '201512', '李四');
INSERT INTO teacher VALUES ( '201513', '王永江' );




INSERT INTO course VALUES ( '100', '数据结构','201510');
INSERT INTO course VALUES ( '101', '数据库','201510');
INSERT INTO course VALUES ( '102', '计算机网络','201511');
INSERT INTO course VALUES ( '103', '计算机操作系统' ,'201512');


INSERT INTO sc VALUES ( '201512217', '103', 98 );
INSERT INTO sc VALUES ( '201512217', '101', 90 );
INSERT INTO sc VALUES ( '201512217', '102', 89 );
INSERT INTO sc VALUES ( '201512217', '100', 80 );
INSERT INTO sc VALUES ( '201512210', '103', 88 );
INSERT INTO sc VALUES ( '201512210', '101', 99 );
INSERT INTO sc VALUES ( '201512210', '102', 100 );
INSERT INTO sc VALUES ( '201512211', '100', 53 );
INSERT INTO sc VALUES ( '201512211', '101', 67 );
INSERT INTO sc VALUES ( '201512211', '102', 80 );
INSERT INTO sc VALUES ( '201512212', '100', 53 );
INSERT INTO sc VALUES ( '201512212', '101', 50 );
INSERT INTO sc VALUES ( '201512212', '102', 20 );
INSERT INTO sc VALUES ( '201512211', '103', 53 );

查询题目：选了几道，理解一下。

/*查询所有姓李的同学*/
select * from student where sname like '李%';
/*查询所有课程小于60的同学的学号和姓名*/
select student.sno,sname from student,sc where student.sno=sc.sno group by student.sno,student.sname having max(grade)<60;
/*删除学习赵老师课的sc表的记录*/
delete sc from sc,course,teacher where sc.cno=course.cno and course.tno=teacher.tno and tname like'赵%';

/*查询所有同学的学号，姓名，选棵数，总成绩*/
select student.sno ,sname,count(cno)'选棵数',sum(grade)'总成绩' from sc,student where student.sno=sc.sno group by student.sno;   /*只是出现了选了课的同学的数据*/
select student.sno,sname,count(cno)'选棵数',sum(grade)'总成绩' from student left outer join sc on student.sno=sc.sno group by student.sno;/*满足题意*/

/*查询学生的平均成绩及其名次*/
create view T as (select sno ,avg(grade)'平均成绩' from sc group by sno);
select sno,平均成绩,1+(select count(distinct 平均成绩) from T t1 where 平均成绩>t2.平均成绩)'名次' from T t2 order by 平均成绩 desc;