阿里等大公司SQL数据库面试题.docx

阿里等大公司 SQL 数据库面试题

Student(Sid,Sname,Sage,Ssex)学生表

Sid：学号

Sname：学生姓名

Sage：学生年龄

Ssex：学生性别

Course(Cid,Cname,T#)课程表

Cid：课程编号

Teacher(Tid,Tname)教师表

Tid：教师编号：

Tname：教师名字

问题：

创建数据库中的表如下：

create database ali;

use ali;

create table ali.Student(Sid varchar(10),Sname varchar(10),Sage

datetime,Ssex nvarchar(10));

insert into Student values('1001' , '赵一' , '1980-06-23' , '男');

insert into Student values('1008' , '赵九' , '1981-03-15' , '女');

insert into Student values('1009' , '赵十' , '1981-04-20' , '女');

insert into Student values('1010' , '张一' , '1981-04-25' , '女');

insert into Student values('1011' , '张二' , '1981-04-29' , '女');

insert into Student values('1012' , '张三' , '1981-05-01' , '女');

insert into Student values('1013' , '张四' , '1981-05-10' , '女');

insert into Student values('1014' , '张五' , '1981-05-20' , '女');

insert into Student values('1015' , '张五' , '1981-06-30' , '女');

create table ali.Course(Cid varchar(10),Cname varchar(10),Tid varchar(10));

insert into Course values('001' , '企业管理' , '02');

insert into Teacher values('02' , '李四');

insert into Teacher values('03' , '叶平');

insert into Teacher values('04' , '李芳');

create table ali.SC(Sid varchar(10),Cid varchar(10),score decimal(18,1));

insert into SC values('1001' , '001' , 80);

insert into SC values('1001' , '002' , 90);

insert into SC values('1001' , '003' , 99);

insert into SC values('1002' , '001' , 70);

insert into SC values('1002' , '002' , 60);

insert into SC values('1002' , '003' , 80);

insert into SC values('1005' , '001' , 76);

insert into SC values('1005' , '002' , 87);

insert into SC values('1006' , '001' , 31);

insert into SC values('1006' , '003' , 34);

insert into SC values('1007' , '001' , 71);

insert into SC values('1007' , '002' , 89);

insert into SC values('1007' , '003' , 98);

insert into SC values('1008' , '001' , 59);

insert into SC values('1008' , '003' , 60);

insert into SC values('1008' , '004' , 95);

insert into SC values('1011' , '001' , 97);

insert into SC values('1011' , '002' , 90);

insert into SC values('1011' , '003' , 75);

insert into SC values('1011' , '004' , 48);

insert into SC values('1012' , '001' , 32);

insert into SC values('1012' , '005' , 65);

insert into SC values('1013' , '001' , 81);

insert into SC values('1013' , '004' , 80);

insert into SC values('1014' , '001' , 90);

insert into SC values('1015' , '001' , 45);

select sid,avg(score) from sc

group by sid

having avg(score)>60;

3、查询所有同学的学号、姓名、选课数、总成绩

from Student left Outer join SC on Student.Sid=SC.sid

group by Student.Sid,Sname;

4、查询姓‘李’的老师的个数：

select count(distinct(Tname))

from Teacher

where Tname like '李%';

3、 查询没有学过“叶平”老师课的同学的学号、姓名：

select Student.Sid,Student.Sname

where Sid in (select Sid from SC,Course,Teacher

where SC.Cid=Course.Cid and Teacher.Tid=Course.Tid

and Teacher.Tname='叶平' group by Sid

having count(SC.Cid)=(select count(Cid) from Course,Teacher

where Teacher.Tid=Course.Tid and Tname='叶平'));

7、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名：

select Student.Sid,Student.Sname

from Student,SC

where Student.Sid=SC.Sid

and SC.Cid='001' and

exists(Select * from SC as SC_2 where SC_2.Sid=SC.Sid and SC_2.Cid='002');

8、查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名：

select Sid,Sname