8 SQL 基础面试题

SQL基础面试题
两个表：

TableX有三个字段 Code、Name、Age、其中Code为主键：

TableY有三字字段 Code、Course（课程）、Score(成绩)，其中Code+Courese为主键：
------------TableX-------------
Code Name Age
97001 张三 22
97002 赵四 21
97003 张飞 20
97004 李五 22
-------------TableY-----------
Code Course Score
97001 数学 80
97002 计算机 59
97003 计算机 60
97004 数学 55
------------------------------
CREATE TABLE TableX(
CODE number(8) PRIMARY KEY,
NAME VARCHAR2(30),
age NUMBER(8)
);

CREATE TABLE TableY(
CODE NUMBER(8),
course VARCHAR2(30),
Score NUMBER(8),
CONSTRAINT pk_tb PRIMARY KEY(CODE,course)
);

INSERT INTO TableX VALUES(97001,'张三',22);
INSERT INTO TableX VALUES(97002,'赵四',21);
INSERT INTO TableX VALUES(97003,'张飞',20);
INSERT INTO TableX VALUES(97004,'李五',22);
COMMIT;
INSERT INTO TableY VALUES(97001,'数学',80);
INSERT INTO TableY VALUES(97002,'计算机',59);
INSERT INTO TableY VALUES(97003,'计算机',60);
INSERT INTO TableY VALUES(97004,'数学',55);
COMMIT;

SELECT * FROM TableX;
SELECT * FROM TableY;

1、用一条SQL，找出所有姓张的学生，并按年龄从小到大排列。
SELECT * FROM TableX WHERE NAME LIKE '张%' ORDER BY age;

2、用一条SQL，取出计算机科考成绩不及格的学生。
SELECT *
FROM TableX x, tableY y
WHERE x.code = y.code
AND y.course = '计算机'
AND y.score < 60;
3、通过等值连接，取出Name、Course、Score，请写出SQL即输出结果。
SELECT x.name,y.course,y.score
FROM TableX x,TableY y
WHERE x.code=y.code;

4、通过外连接，取出每个学生的Name、Course、Score，请写出SQL输出结果。
SELECT x.name,y.course,y.score
FROM TableX x LEFT JOIN TableY y
ON x.code=y.code;

5、请写SQL，在TableX表中增加一条学生记录（学号：97005 姓名：赵六 年龄：20）
INSERT INTO TableX(CODE,NAME,age) VALUES(97005,'赵六',20);
COMMIT;

6、李五的年龄记录错误，应该是21，请写SQL，根据主键进行更新。
UPDATE TableX SET age=21 WHERE NAME='李五';
COMMIT;
7、请写SQL,删除TableX中没有考试成绩的学生记录，请使用 not in 条件。
DELETE FROM TableX
WHERE CODE NOT IN(SELECT CODE FROM TableY);
COMMIT;
8、用一条SQL语句，查询出各个班级的名称及人数。
Student
Sid Name Class
2010001 李天一 一班
2010002 哈士二 二班
2010003 富仕三 三班
2010004 白马四 二班
2010005 肚皮五 三班
2010006 曹德六 三班

CREATE TABLE student(
SID VARCHAR2(30) PRIMARY KEY,
NAME VARCHAR2(30),
CLASS VARCHAR2(30)
);

INSERT INTO student(SID,NAME,CLASS)
SELECT '2010001','李天一','一班' FROM dual
UNION ALL SELECT '2010002','哈士二','二班' FROM dual
UNION ALL SELECT '2010003','富仕三','三班' FROM dual
UNION ALL SELECT '2010004','白马四','二班' FROM dual
UNION ALL SELECT '2010005','肚皮五 ','三班' FROM dual
UNION ALL SELECT '2010006','曹德六','三班' FROM dual
COMMIT;

SELECT * FROM student;

SELECT CLASS,COUNT(*) FROM student GROUP BY CLASS;

9、学生考试成绩如下（引用上题中的表）：

Grade
Sid Course Marks
2010001 语文 58
2010001 数学 14
2010002 语文 80
2010002 数学 75
2010004 语文 100
2010004 数学 55
2010005 语文 90
2010005 数学 100
2010005 英语 70

CREATE TABLE grade(
SID VARCHAR2(30),
course VARCHAR2(20),
marks NUMBER(8),
CONSTRAINT pk_gd PRIMARY KEY(SID,course)
);
INSERT INTO grade(Sid,Course,Marks)
SELECT '2010001','语文',58 FROM dual
UNION ALL SELECT '2010001','数学',14 FROM dual
UNION ALL SELECT '2010002','语文',80 FROM dual
UNION ALL SELECT '2010002','数学',75 FROM dual
UNION ALL SELECT '2010004','语文',100 FROM dual
UNION ALL SELECT '2010004','数学',55 FROM dual
UNION ALL SELECT '2010005','语文',90 FROM dual
UNION ALL SELECT '2010005','数学',100 FROM dual
UNION ALL SELECT '2010005','英语',70 FROM dual;
COMMIT;
SELECT * FROM grade;

（1）用一条SQL语句查出，每门课程都在70及以上的学生的名称和班级；
方法1：
SELECT NAME,CLASS
FROM student s, (SELECT SID FROM grade GROUP BY SID HAVING MIN(marks)>=70) a
WHERE s.sid=a.sid;

方法2：
SELECT s.name,s.class
FROM student s,grade g
WHERE s.sid=g.sid
GROUP BY s.name,s.class
HAVING MIN(marks)>=70;

（2）以60分为及格线，75及以上评为优秀，用一条SQL查询name course marks level,如下列结果；
Name Course Marks Level
李天一 语文 58 不及格
李天一 数学 14 不及格
哈士二 语文 80 优秀
哈士二 数学 75 优秀
白马四 语文 100 优秀
白马四 数学 55 不及格
肚皮五 语文 90 优秀
肚皮五 数学 100 优秀
肚皮五 英语 70 及格

SELECT s.name,g.course,g.marks,
CASE
WHEN g.marks < 60 THEN '不及格'
WHEN g.marks >= 60 AND g.marks < 75 THEN '及格'
ELSE '优秀'
END LEVEL1
FROM student s, grade g
WHERE s.sid = g.sid;

10、用一条SQL语句，查询所有语文成绩比数学成绩高的学生的学号；
方法1：
SELECT g1.sid,g1.marks,g2.marks
FROM grade g1, grade g2
WHERE g1.sid = g2.sid
AND g1.course = '语文'
AND g2.course = '数学'
AND g1.marks > g2.marks;
方法2：
SELECT s.sid,g1.marks,g2.marks
FROM student s,
(SELECT SID,marks FROM grade WHERE course='语文') g1,
(SELECT SID,marks FROM grade WHERE course='数学') g2
WHERE s.sid=g1.sid
AND s.sid=g2.sid
AND g1.marks>g2.marks;

11、用一条SQL语句，查出学生的学号、平均分、排名，如果分数相同，则按学号排序，学号小的名次在前。
SELECT T.SID,ROUND(T.avg_mark,1),row_number() OVER(ORDER BY t.avg_mark)
FROM
(SELECT SID,AVG(marks) avg_mark FROM grade GROUP BY SID) T;

12、用一条SQL语句，查出学生的学号，平均分、排名，如果分数相同，则并列排名。

SELECT T.SID,ROUND(T.avg_mark,1),RANK() OVER(ORDER BY t.avg_mark)
FROM
(SELECT SID,AVG(marks) avg_mark FROM grade GROUP BY SID) T;