plsql练习之sql笔试经典50题

SQL笔试题：经典50题
创建表

create table student(sno varchar2(4) primary key,sname varchar2(4),birthday date,sex varchar2(2));
create table scores(sno varchar2(4),cno varchar2(4),result float,primary key(sno,cno));
create table course(cno varchar2(4),cname varchar2(4),tno varchar2(4),primary key(cno,cname);
create table teacher(tno varchar2(4) primary key,tname varchar2(10));

添加数据

insert into student values('0001' , '猴子' , to_date('1989-01-01','yyyy-mm-dd'), '男');
insert into student values('0002' , '猴子' , to_date('1990-12-21','yyyy-mm-dd'), '女');
insert into student values('0003' , '马云' , to_date('1991-12-21','yyyy-mm-dd'), '男');
insert into student values('0004' , '马云' , to_date('1990-05-20','yyyy-mm-dd'), '男');

insert into scores values('0001' , '0001' , 80);
insert into scores values('0001' , '0002' , 90);
insert into scores values('0001' , '0003' , 99);
insert into scores values('0002' , '0002' , 60);
insert into scores values('0002' , '0003' , 80);
insert into scores values('0003' , '0001' , 80);
insert into scores values('0003' , '0002' , 80);
insert into scores values('0003' , '0003' , 80);

insert into course values('0001' , '语文' , '0002');
insert into course values('0002' , '数学' , '0001');
insert into course values('0003' , '英语' , '0003');

insert into teacher values('0001' , '孟扎扎');
insert into teacher values('0002' , '马化腾');
insert into teacher values('0003' , 'null');
insert into teacher values('0004' , ' ');

为了方便学习，我将50道面试题进行了分类
一.简单查询
1.查询姓“猴”的学生名单

select * from student where sname like '猴%';

2.查询姓名中最后一个字是“猴”的学生名单

select * from student where sname like '%猴';

3.查询姓名中带“猴”的学生名单

select * from student where sname like '%猴%';

4.查询姓“孟”老师的个数

select count(tname) from teacher where tname like '孟%'