SQL面试题
注：测试库为MySQL
----------------------------------------------------------1---------------------------------------------------------------------------
1、用一条SQL 语句，查询出每门课都大于80 分的学生姓名
-- 学生课程分数表 建表
CREATE TABLE student_1( s_name VARCHAR(20) -- 姓名 ,kecheng VARCHAR(20) -- 课程 ,fenshu DECIMAL(3) -- 分数 );
-- 学生课程分数表 插入测试数据
INSERT INTO student_1(s_name,kecheng,fenshu) VALUES ('张三','语文',81) ,('张三','数学',75) ,('李四','语文',76) ,('李四','数学',90) ,('王五','语文',81) ,('王五','数学',100) ,('王五','英语',90) ;
-- 学生课程分数表 查看数据
SELECT * FROM student_1;
用一条SQL 语句，查询出每门课都大于80 分的学生姓名
-- 写法一：
SELECT s_name FROM student_1 GROUP BY s_name HAVING MIN(fenshu)>80;
-- 写法二
SELECT DISTINCT s_name FROM student_1 WHERE s_name NOT IN (SELECT DISTINCT s_name FROM student_1 WHERE fenshu < 80);
----------------------------------------------------------2---------------------------------------------------------------------------
2、删除除了自动编号不同, 其他都相同的学生冗余信息
-- 学生表 创建
CREATE TABLE student_2( code_no DECIMAL(2) ,stu_no DECIMAL(7) ,s_name VARCHAR(10) ,course_no DECIMAL(4) ,course_name VARCHAR(10) ,fenshu DECIMAL(3) );
-- 学生表 插入测试数据
INSERT INTO student_2 VALUES (1,2005001,'张三',0001,'数学',69) ,(2,2005002,'李四',0001,'数学',89) ,(3,2005001,'张三',0001,'数学',69) ;
-- 学生表 查询全部数据
SELECT * FROM student_2;
删除除了自动编号不同, 其他都相同的学生冗余信息
DELETE FROM student_2 WHERE code_no NOT IN (SELECT MIN(code_no) FROM student_2 GROUP BY stu_no,s_name,course_no,course_name,fenshu);
MySQL当中执行此语句会报错：它的意思是说，不能先select出同一表中的某些值，再update这个表(在同一语句中)，即不能依据某字段值做判断再来更新某字段的值。
这种情况只会在MySQL中出现，Oracle是没有这种情况的。
所以，这里使用中间表，将select的结果再select一次
DELETE FROM student_2 WHERE code_no NOT IN (SELECT a.code_no FROM (SELECT MIN(code_no) code_no FROM student_2 GROUP BY stu_no,s_name,course_no,course_name,fenshu) a );
----------------------------------------------------------3---------------------------------------------------------------------------
3、一个叫 team 的表，里面只有一个字段name, 一共有4 条纪录，分别是a,b,c,d, 对应四个球对，现在四个球对进行比赛，用一条sql 语句显示所有可能的比赛组合。
创建team表
CREATE TABLE team( NAME VARCHAR (2) );
插入数据
INSERT INTO team VALUES ('a') ,('b') ,('c') ,('d') ;
查看数据
SELECT * FROM team;
查看结果
SELECT a.name,b.name FROM team a,team b WHERE a.name>b.name ;
----------------------------------------------------------4---------------------------------------------------------------------------
4、从TestDB 数据表中查询出所有月份的发生额都比101 科目相应月份的发生额高的科目。请注意：TestDB 中有很多科目，都有1 －12 月份的发生额。
创建表
create table testdb( accid decimal(3) -- 科目代码 ,occmonth decimal(2) -- 发生额月份 ,debitoccur decimal(16,2) -- 发生额 );
插入测试数据
insert into testdb values (101,01,718232.23) ,(101,02,728432.23) ,(101,03,738332.23) ,(101,04,748232.23) ,(101,05,758432.23) ,(101,06,768432.23) ,(101,07,77832.23) ,(101,08,7883562.23) ,(101,09,798132.23) ,(101,10,708432.23) ,(101,11,71286532.23) ,(101,12,7118832.23) ,(102,01,655632.23) ,(102,02,65632.23) ,(102,03,65392.23) ,(102,04,655632.23) ,(102,05,65328.23) ,(102,06,65342.23) ,(102,07,65332.23) ,(102,08,65320.23) ,(102,09,65328.23) ,(102,10,653122.23) ,(102,11,65321.23) ,(102,12,65321.23) ,(103,01,815312.23) ,(103,02,825323.23) ,(103,03,835324.23) ,(103,04,845325.23) ,(103,05,855332.23) ,(103,06,865326.23) ,(103,07,875327.23) ,(103,08,885432.23) ,(103,09,895329.23) ,(103,10,805632.23) ,(103,11,8125732.23) ,(103,12,8115832.23) ;
从TestDB 数据表中查询出所有月份的发生额都比101 科目相应月份的发生额高的科目。请注意：TestDB 中有很多科目，都有1 －12 月份的发生额。
SELECT a.* FROM testdb a ,(SELECT occmonth,MAX(debitoccur) debitoccur_101 FROM testdb WHERE accid='101' GROUP BY occmonth) b WHERE a.occmonth=b.occmonth AND a.debitoccur>b.debitoccur_101 ;
查询结果
----------------------------------------------------------5---------------------------------------------------------------------------
5、怎么把这样一个表儿
year month amount
1991 1 1.1
1991 2 1.2
1991 3 1.3
1991 4 1.4
1992 1 2.1
1992 2 2.2
1992 3 2.3
1992 4 2.4
查成这样一个结果
year m1 m2 m3 m4
1991 1.1 1.2 1.3 1.4
1992 2.1 2.2 2.3 2.4
创建表
CREATE TABLE y_a( t_year DECIMAL(4) ,t_month DECIMAL(2) ,amount VARCHAR(3) );
插入测试数据
INSERT INTO y_a VALUES (1991,1,'1.1') ,(1991,2,'1.2') ,(1991,3,'1.3') ,(1991,4,'1.4') ,(1992,1,'2.1') ,(1992,2,'2.2') ,(1992,3,'2.3') ,(1992,4,'2.4') ;
查看数据
SELECT * FROM y_a;
执行查询
SELECT t_year,
(SELECT amount FROM y_a m WHERE t_MONTH=1 AND m.t_year=b.t_year) AS m1,
(SELECT amount FROM y_a m WHERE t_MONTH=2 AND m.t_year=b.t_year) AS m2,
(SELECT amount FROM y_a m WHERE t_MONTH=3 AND m.t_year=b.t_year) AS m3,
(SELECT amount FROM y_a m WHERE t_MONTH=4 AND m.t_year=b.t_year) AS m4
FROM y_a b GROUP BY t_year
;
查询结果
----------------------------------------------------------6---------------------------------------------------------------------------
6、复制y_a表到y_b表
CREATE TABLE y_b AS SELECT * FROM y_a WHERE 1=1;
----------------------------------------------------------7---------------------------------------------------------------------------
7、原表:
courseid coursename score
-------------------------------------
1 Java 70
2 oracle 90
3 xml 40
4 jsp 30
5 servlet 80
-------------------------------------
为了便于阅读, 查询此表后的结果显式如下( 及格分数为60):
courseid coursename score mark
---------------------------------------------------
1 Java 70 pass
2 oracle 90 pass
3 xml 40 fail
4 jsp 30 fail
5 servlet 80 pass
创建表
CREATE TABLE course_1( courseid DECIMAL(1) ,coursename VARCHAR(10) ,score DECIMAL(3) );
插入数据
INSERT INTO course_1 VALUES (1,'Java',70) ,(2,'oracle',90) ,(3,'xml',40) ,(4,'jsp',30) ,(5,'servlet',80) ;
查询
SELECT courseid,coursename,score ,CASE WHEN score>60 THEN 'pass' ELSE 'fail' END AS mark FROM course_1;
结果显示
