一些面试的SQL题答案及建表全部齐全！YYYYYYY1998的博客-

这是我自己去面试的一点sql题

在这里给大家！

希望能尽快找到工作

其中第十一题 我不是很懂 （我用的是mysql 这个题好像是orc的 不知道是不是）

其次第十题的第四小题 我把count < 3 和老师 not like ‘刘%’的放在一起结果就有些不对

希望有大佬能帮帮我指正一下 十分感谢！

上面是图片 然后创表语句和答案我都将放在下面 

感谢支持！

##由于是面试做过的 自己添加一些数据就不加很多了

创表和添加数据语句：

第一题：建表
CREATE TABLE IF NOT EXISTS STUDENTS
(
ID INT COMMENT ‘编号’,
NAME VARCHAR(20) COMMENT ‘姓名’,
GENDER VARCHAR(1) COMMENT ‘性别男为M,女为F’,
JOIN_DATE  DATE COMMENT ‘入学日期’,
BIRTHDAY DATE COMMENT ‘出生日期’
);

自己插入一些数据
INSERT INTO `STUDENTS` VALUES(‘1′,’小王’,’M’,’2020-06-10′,’1995-04-01′)
(‘2′,’小叶’,’F’,’2020-06-10′,’1998-01-01′),
(‘3′,’小李’,’M’,’2020-06-10′,’1995-04-01′),
(‘4′,’小刘’,’F’,’2020-06-10′,’1998-01-01′),
(‘5′,’小一’,’M’,’2020-06-10′,’1995-04-01′),
(‘6′,’小二’,’M’,’2020-06-10′,’1998-01-01′),
(‘7′,’小三’,’F’,’2020-06-10′,’1998-01-01′),
(‘8′,’小四’,’F’,’2020-06-10′,’1995-04-01′),
(‘9′,’小五’,’M’,’2020-06-10′,’1998-01-01′),
(’10’,’小六’,’M’,’2020-06-10′,’1998-01-01′)

第二题：
UPDATE STUDENTS SET GENDER = ‘女’,BIRTHDAY = ‘1995-5-1′ WHERE ID = ’10’

第三题：
SELECT 
    ID,
    NAME,
    (CASE WHEN GENDER = ‘M’ THEN 0 ELSE 1 END )AS GENDER,
    JOIN_DATE,
    BIRTHDAY
FROM STUDENTS;

第四题：
SELECT * FROM (
(SELECT COUNT(*) AS ‘MAN’ FROM students 
WHERE GENDER = ‘M’ 
AND BIRTHDAY > ‘1995-05-01’ )a
INNER JOIN
(
SELECT COUNT(*) AS ‘WOMEN’ FROM students 
WHERE GENDER = ‘F’ 
AND BIRTHDAY > ‘1995-05-01’ 
)b
)

第五题：

存储过程 ：往往存储过程中不单单只包含这种简单的查询语法，还会嵌套循环语句、变量、报错处理、事务等
函数：一般以函数名称+参数列表+return返回类型+sql语句+end

第六题：

表和索引所占空间：
truncate:当表被TRUNCATE 后，这个表和索引所占用的空间会恢复到初始大小
DELETE操作不会减少表或索引所占用的空间。

应用范围：
TRUNCATE 只能对TABLE；        
DELETE可以是table和view

题外话：drop语句将表所占用的空间全释放掉。

##这里不写全了 想看完整的我转载一个博客
https://www.cnblogs.com/zhizhao/p/7825469.html

第七题：

关于索引的一些创建方法：
普通的索引的创建：
CREATE INDEX  (自定义)索引名  ON  数据表(字段);
复合索引的创建：
CREATE INDEX  (自定义)索引名  ON  数据表(字段，字段，。。。);
删除索引：DROP INDEX 索引名;

（1）Mysql目前主要有以下几种索引类型：FULLTEXT，HASH，BTREE，RTREE

(2)优点：
1.通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。 
2.可以大大加快数据的检索速度，这也是创建索引的最主要的原因。 
3.可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。 
4.在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。 
5.通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。 
缺点：
1.创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。 
2.索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚集索引那么需要的空间就会更大。 
3.当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。

（3):
1、like 以%开头，索引无效；当like前缀没有%，后缀有%时，索引有效
2、or语句前后没有同时使用索引。当or左右查询字段只有一个是索引，该索引失效，
只有当or左右查询字段均为索引时，才会生效
3、组合索引，不是使用第一列索引，索引失效
4、数据类型出现隐式转化。如varchar不加单引号的话可能会自动转换为int型，
使索引无效，产生全表扫描。
5.在索引列上使用 IS NULL 或 IS NOT NULL操作。索引是不索引空值的，
所以这样的操作不能使用索引，可以用其他的办法处理，例如：数字类型，判断大于0，
字符串类型设置一个默认值，判断是否等于默认值即可。
8、当全表扫描速度比索引速度快时，mysql会使用全表扫描，此时索引失效

第八题：
创表：CREATE TABLE department(NAME VARCHAR(5))
语句：SELECT a.name,b.name FROM department a,department b 
        WHERE a.name < b.name

第九题：
创表：
CREATE TABLE IF NOT EXISTS STUDENT2
(
name VARCHAR(10) not null COMMENT ‘姓名’，
sub VARCHAR(10) NOT NULL COMMENT ‘学科’,
score VARCHAR(10) NOT NULL COMMENT ‘分数’
)

插入语句
insert into STUDENT2 VALUES(‘张三’,’语文’,’81’),
(‘张三’,’数学’,’75’),
(‘李四’,’语文’,’76’),
(‘李四’,’数学’,’90’),
(‘王五’,’语文’,’81’),
(‘王五’,’数学’,’100′)

（1）：select name from STUDENT2 GROUP BY name HAVING min(score) >80

 (2)： SELECT NAME FROM student2 WHERE score >
(SELECT AVG(score) FROM 
(SELECT * FROM student2)a GROUP BY a.sub)

(3):SELECT 
NAME,
SUM(CASE WHEN sub = ‘语文’ THEN score ELSE 0 END)AS ‘语文’,
SUM(CASE WHEN sub = ‘数学’ THEN score ELSE 0 END)AS ‘数学’
    FROM student2 GROUP BY NAME
    
第十题：
创表：
学生表：CREATE TABLE IF NOT EXISTS student3(
id TINYINT ,
name VARCHAR(5)   COMMENT ‘姓名’,
grade VARCHAR(2)  COMMENT ‘年级’,
sex VARCHAR(2)   COMMENT ‘性别’
)

#选课表
CREATE TABLE IF NOT EXISTS xuanke(
id VARCHAR(5) ,
lessionid VARCHAR(10) ,
score VARCHAR(5) 
)

#course课程表
CREATE TABLE IF NOT EXISTS course(
lessionid VARCHAR(10),
lessionname VARCHAR(10),
teachername VARCHAR(10)
)

赋值：
#student3表赋值
INSERT INTO `student3` VALUES
(101,’李军’,’2′,’男’),
(103,’陆君’,’2′,’女’)，
(105,’匡明’,’2′,’男’),
(107,’王丽’,’2′,’女’),
(108,’曾华’,’2′,’男’),
(109,’王芳’,’2′,’女’);

#选课表赋值
INSERT INTO `xuanke` 
VALUES
(‘101′,’001′,’90’),
(‘101′,’002′,’90’),
(‘101′,’003′,’90’),
(‘101′,’004′,’90’),
(‘103′,’001′,’90’),
(‘105′,’001′,’90’),
(‘107′,’001′,’90’),
(‘108′,’001′,’90’),
(‘109′,’001′,’90’)

#课程表赋值
INSERT INTO `course` 
VALUES
(‘001′,’茶艺’,’小叶’),
(‘002′,’计算机’,’小吴’),
(‘003′,’甲骨文’,’小李’),
(‘004′,’欧洲历史’,’小庄’)

第十题解答：
（1）：
SELECT DISTINCT c.teachername FROM course AS c 
LEFT JOIN xuanke b ON c.lessionid = b.lessionid
LEFT JOIN student3 a ON b.id = a.id 
AND a.name LIKE ‘王%’
ORDER BY c.teachername

(2):
SELECT DISTINCT lessionname FROM course,xuanke,student3 
WHERE course.lessionid  = xuanke.lessionid
AND xuanke.id = student3.id 
GROUP BY student3.id

(3):
SELECT all.name  FROM
(
SELECT 
    a.`id`,a.`name`,a.`grade`,a.`sex`,
    b.`lessionid`,b.`score`,
    c.`lessionname`,c.`teachername`d FROM course AS c 
INNER JOIN xuanke b ON c.lessionid = b.lessionid
INNER JOIN student3 a ON b.id = a.id 
)AS `all` 
GROUP BY all.id 
HAVING COUNT(all.lessionid) < 4

（4）：

SELECT NAME FROM student3 WHERE NAME NOT IN(
     SELECT a.name FROM student3 a
       INNER JOIN
         (SELECT * FROM xuanke)b
         ON a.id = b.id
       INNER JOIN
         (SELECT * FROM course)c
         ON b.lessionid = c.lessionid
       AND c.teachername  LIKE ‘刘%’
       HAVING COUNT(b.id) < 3
)
        
第十一题：

declare
    type tab_name is table of varchar2(80) index by binary_integer;
    t_name tab_name;
    coun number;
    str varchar(200);
begin
    select table_name bulk collect into t_name from user_tables;
    for i in t_name.first .. t_name.last loop
        str := ‘select count(*) from ‘|| t_name(i);
        execute immediate str into coun;
        dbms_output.put_line(t_name(i)||’…..’||coun);
    end loop;
end;
        
        
第十二题：

select 
    max(team) team,
    min(y) B,
    min(y)+count(*) E 
from 
(
select 
    a.team,
    a.y,
    rownum rn 
from nba a, nba b 
where a.y=b.y-1 and a.team=b.team
) t 
group by (y-rn) order by min(y)