一道SQL面试题
有三张表,学生表S,课程表C,学生课程表SC,学生可以选修多门课程,一门课程可能被多个学生选修,通过SC表关联。
(1) 写出建表以及插入语句;
(2) 写出SQL语句,查询选修了所有选修课程的学生;
(3) 写出SQL语句,查询选修了至少2门以上的课程的学生。
ANSWER:
(1) 写出建表以及插入语句;
/
create table student (
id number(10) primary key,
name varchar2(20));
create table course (
id number(10) primary key,
name varchar2(20));
create table sc(
sid number(10) references student(id),
cid number(10) references course(id),
grade number(42));
INSERT VALUES:
2) 写出SQL语句,查询选修了所有选修课程的学生;
select s.id,s.name
from student s
where s.id in(
select sid
from sc
group by sid
having count(*)=
(select count(*)
from course)
);
(3) 写出SQL语句,查询选修了至少2门以上的课程的学生。
select sid,count(*) from sc group by sid having count(*)>=2;
SELECT S.ID,S.NAME
FROM STUDENT S
WHERE S.ID IN(
select sid
from sc
group by sid
having count(*)>=2
)
drop table student cascade constraints;
drop table course cascade constraints;
drop table sc;
commit;
create table student(
id number(10) primary key,
name varchar2(15)
);
create table course(
id number(10) primary key,
name varchar2(15)
);
create table sc(
sid number(10) references student(id),
cid number(10) references course(id)
);
commit;
insert into student values(1,’star’);
insert into student values(2,’moon’);
insert into student values(3,’oracle’);
insert into student values(4,’sun’);
insert into course values(1,’Java’);
insert into course values(2,’C++’);
insert into course values(3,’Chinese’);
insert into course values(4,’English’);
insert into sc values(1,1);
insert into sc values(1,2);
insert into sc values(1,3);
insert into sc values(1,4);
insert into sc values(2,1);
insert into sc values(2,2);
insert into sc values(2,3);
insert into sc values(3,1);
insert into sc values(3,2);
insert into sc values(4,3);
insert into sc values(4,4);
commit;
;
(1) 写出建表以及插入语句;
(2) 写出SQL语句,查询选修了所有选修课程的学生;
(3) 写出SQL语句,查询选修了至少2门以上的课程的学生。
ANSWER:
(1) 写出建表以及插入语句;
/
create table student (
id number(10) primary key,
name varchar2(20));
create table course (
id number(10) primary key,
name varchar2(20));
create table sc(
sid number(10) references student(id),
cid number(10) references course(id),
grade number(42));
INSERT VALUES:
2) 写出SQL语句,查询选修了所有选修课程的学生;
select s.id,s.name
from student s
where s.id in(
select sid
from sc
group by sid
having count(*)=
(select count(*)
from course)
);
(3) 写出SQL语句,查询选修了至少2门以上的课程的学生。
select sid,count(*) from sc group by sid having count(*)>=2;
SELECT S.ID,S.NAME
FROM STUDENT S
WHERE S.ID IN(
select sid
from sc
group by sid
having count(*)>=2
)
drop table student cascade constraints;
drop table course cascade constraints;
drop table sc;
commit;
create table student(
id number(10) primary key,
name varchar2(15)
);
create table course(
id number(10) primary key,
name varchar2(15)
);
create table sc(
sid number(10) references student(id),
cid number(10) references course(id)
);
commit;
insert into student values(1,’star’);
insert into student values(2,’moon’);
insert into student values(3,’oracle’);
insert into student values(4,’sun’);
insert into course values(1,’Java’);
insert into course values(2,’C++’);
insert into course values(3,’Chinese’);
insert into course values(4,’English’);
insert into sc values(1,1);
insert into sc values(1,2);
insert into sc values(1,3);
insert into sc values(1,4);
insert into sc values(2,1);
insert into sc values(2,2);
insert into sc values(2,3);
insert into sc values(3,1);
insert into sc values(3,2);
insert into sc values(4,3);
insert into sc values(4,4);
commit;
;
【一道SQL面试题】相关文章
1. 一道SQL面试题
2. 一道SQL存储过程面试题
3. 金蝶的一道SQL笔试题
5. 一些高难度的SQL面试题
6. SQL面试题
7. MySQL面试题
8. 一个SQL面试题
9. SQL面试题
10. 一组SQL面试题
本文来源:https://www.mianshiwenti.com/a13339.html
进入下载页面
﹝一道SQL面试题﹞相关内容
- Sql面试题
- 北京华建集团SQL面试题
- MySQL面试题目集锦
- 十一个高级MySql面试题
- mysql_pconnect()和mysql_connect()有什么区别
- 写一个在SQL Server创建表的SQL语句
- C#和SQL Server的面试题
- 几个SQL的面试题
- Oracle面试题库-PL/SQL
- Oracle面试题库-SQL