SQL面试经典题目详细参考
48道SQL练习题(Oracle+MySQL)
练习主要涉及四张表,分别如下:
student(sid,sname,sage,ssex) 学生表
course(cid,cname,tid) 课程表
score(sid,cid,score) 成绩表
teacher(tid,tname) 教师表
首先建立表结构
CREATE TABLE student
(
sid INT,
sname varchar (32),
sage INT,
ssex varchar (8)
);
CREATE TABLE course
(
cid INT,
cname varchar(32),
tid INT
);
CREATE TABLE score
(
sid INT,
cid INT,
score INT
);
CREATE TABLE teacher
(
tid INT,
tname varchar(16)
);
插入数据
--oracle
insert into student select 1,'刘一',18,'男' FROM dual union all
select 2,'钱二',19,'女' FROM dual union all
select 3,'张三',17,'男' FROM dual union all
select 4,'李四',18,'女' FROM dual union all
select 5,'王五',17,'男' FROM dual union all
select 6,'赵六',19,'女' FROM dual;
insert into teacher select 1,'叶平' FROM dual union all
select 2,'贺高' FROM dual union all
select 3,'杨艳' FROM dual union all
select 4,'周磊' FROM dual;
insert into course select 1,'语文',1 FROM dual union all
select 2,'数学',2 FROM dual union all
select 3,'英语',3 FROM dual union all
select 4,'物理',4 FROM dual;
insert into score
select 1,1,56 FROM dual union all
select 1,2,78 FROM dual union all
select 1,3,67 FROM dual