数据库的一些题型2

第一

user表

 

 

 user_ext表

 

 

#1.查询出来 user 表中 score 大于 80 的所有数据
 select * from user where score > 80

#2.查询表 user 中字段 gender 为 '男' 的所有内容
select * from user where gender = '男'

#3.查询表 user 中字段 students 开头为'小'字的内容
select * from user where students like '小%'

#4.查询表 user 中字段 students 开头不是为'小'字的内容
select * from user where students not like '小%'

#5.查询表 user 中字段 students 包含'聪'字的所有内容
select * from user where students like '%聪%'

#6.查询表 user 中字段 score 为98,60,92的所有内容
#1
select * from user where score = '98' or score = '60' or score = '92'
#2
select * from user where score in(98,60,92)

#7.查询表 user 中字段 score 大于95 或者 gender 为女性的所有内容
select * from user where score > '95' or gender = '女'

#8.合并查询表 user 和表 user_ext 中 id 相同的所有数据
select * from user u
inner join user_ext ue
on u.id = ue.id

#9.获取表 user 中字段 score 大于 60 的内容数量
select count(*) from user where score > 60

#10.获取表 user 中字段 score 的平均值
select avg(score) from user

#11.获取表 user 中字段 score 的总分数
select sum(score) from user

#12.获取表 user 中字段 score 的最大值
select max(score) from user

#13.获取表 user 中字段 score 的最小值
select min(score) from user

#14.获取表 user_ext 中所有不同的字段 age 并设置字段别名为'年龄'
select id,students,age '年龄',height,weight
from user_ext

#15.获取表 user_ext 中的所有数据并且按照字段 weight 进行倒序排序
select * from user_ext
order by weight desc

#16.通过左连接 获取表 user(别名t1) 和表 user_ext(别名t2) 中字段 id 相同的数据，
#其中字段 age 大于9，并仅返回 id、students、age、weight 这几个字段的数据
select t1.id,t1.students,t2.age,t2.weight
from user t1
left join user_ext t2
on t1.id = t2.id
where age > 9

#17.在 user 表 所有字段 中添加记录
insert into user values(7,'小敏',99,'女')

#18.把 user 表 中字段 students 为'小明' 所在字段 score 更改为30分
update user set score = 30 where students = '小明'

#19.把 user 表 students 字段为'小明'的记录删除
delete from user where students = '小明'

#20.删除user_ext表
drop table user_ext

 

第二 

CREATE TABLE t_Shop(
 id int NOT NULL,
 商品名称 varchar (50) NOT NULL,
 商品编号 varchar(50) NOT NULL,
 销售数量 int NOT NULL,
 商品单价 int NOT NULL,
 销售地点 varchar(50) NOT NULL,
 销售日期 varchar(50) NOT NULL,
 销售人 varchar(50) NOT NULL
)

添加数据

insert into t_Shop values(1,'可口可乐','100101',7,36,'新职宿舍四号楼','2019-4-5','张三');
insert into t_Shop values(2,'老坛酸菜牛肉面','110101',2,24,'新职宿舍一号楼','2019-4-5','张三');
insert into t_Shop values(3,'哇哈哈AD钙奶','100201',3,18,'新职宿舍五号楼','2019-4-5','张三');
insert into t_Shop values(4,'乐视薯片','140101',5,12,'新职宿舍四号楼','2019-4-5','张三');
insert into t_Shop values(5,'青岛啤酒','100301',3,30,'新职宿舍一号楼','2019-4-5','张三');
insert into t_Shop values(6,'傻子瓜子','120101',4,25,'新职宿舍五号楼','2019-4-5','张三');
insert into t_Shop values(7,'猫屎咖啡','130101',7,80,'新职宿舍一号楼','2019-4-5','张三');

insert into t_Shop values(8,'可口可乐','100101',7,36,'新职宿舍四号楼','2019-4-5','李四');
insert into t_Shop values(9,'老坛酸菜牛肉面','110101',2,24,'新职宿舍一号楼','2019-4-5','李四');
insert into t_Shop values(10,'哇哈哈AD钙奶','100201',3,18,'新职宿舍五号楼','2019-4-5','李四');
insert into t_Shop values(11,'乐视薯片','140101',7,12,'新职宿舍四号楼','2019-4-5','李四');
insert into t_Shop values(12,'青岛啤酒','100301',2,30,'新职宿舍一号楼','2019-4-5','李四');
insert into t_Shop values(13,'傻子瓜子','120101',3,25,'新职宿舍五号楼','2019-4-5','李四');
insert into t_Shop values(14,'猫屎咖啡','130101',5,80,'新职宿舍一号楼','2019-4-5','李四');

insert into t_Shop values(15,'可口可乐','100101',8,36,'新职宿舍四号楼','2019-4-5','王五');
insert into t_Shop values(16,'老坛酸菜牛肉面','110101',12,24,'新职宿舍一号楼','2019-4-5','王五');
insert into t_Shop values(17,'哇哈哈AD钙奶','100201',5,18,'新职宿舍五号楼','2019-4-5','王五');
insert into t_Shop values(18,'乐视薯片','140101',6,12,'新职宿舍四号楼','2019-4-5','王五');
insert into t_Shop values(19,'青岛啤酒','100301',3,30,'新职宿舍一号楼','2019-4-5','王五');
insert into t_Shop values(20,'傻子瓜子','120101',3,25,'新职宿舍五号楼','2019-4-5','王五');
insert into t_Shop values(21,'猫屎咖啡','130101',4,80,'新职宿舍一号楼','2019-4-5','王五');

insert into t_Shop values(22,'可口可乐','100101',5,36,'新职宿舍四号楼','2019-4-5','赵四');
insert into t_Shop values(23,'老坛酸菜牛肉面','110101',2,24,'新职宿舍一号楼','2019-4-5','赵四');
insert into t_Shop values(24,'哇哈哈AD钙奶','100201',8,18,'新职宿舍五号楼','2019-4-5','赵四');
insert into t_Shop values(25,'乐视薯片','140101',4,12,'新职宿舍四号楼','2019-4-5','赵四');
insert into t_Shop values(26,'青岛啤酒','100301',3,30,'新职宿舍一号楼','2019-4-5','赵四');
insert into t_Shop values(27,'傻子瓜子','120101',3,25,'新职宿舍五号楼','2019-4-5','赵四');
insert into t_Shop values(28,'猫屎咖啡','130101',5,80,'新职宿舍一号楼','2019-4-5','赵四');

insert into t_Shop values(29,'可口可乐','100101',6,36,'新职宿舍一号楼','2019-4-6','王五');
insert into t_Shop values(30,'可口可乐','100101',6,36,'新职宿舍二号楼','2019-4-6','王五');
insert into t_Shop values(31,'可口可乐','100101',6,36,'新职宿舍三号楼','2019-4-6','王五');
insert into t_Shop values(32,'可口可乐','100101',6,36,'新职宿舍四号楼','2019-4-6','王五');

insert into t_Shop values(33,'可口可乐','100101',7,36,'新职宿舍一号楼','2019-4-6','李四');
insert into t_Shop values(34,'可口可乐','100101',6,36,'新职宿舍二号楼','2019-4-6','李四');
insert into t_Shop values(35,'可口可乐','100101',7,36,'新职宿舍三号楼','2019-4-6','李四');
insert into t_Shop values(36,'可口可乐','100101',6,36,'新职宿舍四号楼','2019-4-6','李四');

insert into t_Shop values(37,'可口可乐','100101',16,36,'新职宿舍一号楼','2019-4-6','张三');
insert into t_Shop values(38,'可口可乐','100101',6,36,'新职宿舍二号楼','2019-4-6','张三');
insert into t_Shop values(39,'可口可乐','100101',8,36,'新职宿舍三号楼','2019-4-6','张三');
insert into t_Shop values(40,'可口可乐','100101',6,36,'新职宿舍四号楼','2019-4-6','张三');

#1、写出热销商品排名表 销售排行榜 
select
 商品编号,
  max(商品名称),
  sum(销售数量) 销售总数量
from t_shop sp
group by '商品编号'
order by 销售总数量 desc;

#2、统计销售总价超过3000元的商品名称和销售总价，并按照销售总价降序排序
select
 '商品名称',
 sum(商品单价 * 销售数量) 销售总价
from t_Shop sp
group by '商品名称'
order by 销售总价 desc

#3、统计各个宿舍楼对可口可乐的喜爱程度，即统计每个宿舍购买可口可乐的数量
select
销售地点,
sum(sp.销售数量) 数量
from t_Shop sp
where  sp.商品名称 = '可口可乐'
group by sp.销售地点;

 

第三

-- 创建学生表
create table t_Student(
 S   varchar(20), -- 学生编号
 SN   varchar(20), -- 学生姓名
 SA   int,   -- 学生年龄
 SD   varchar(100) -- 所属单位
)
-- 创建选修课程表
create table t_Course(
 C  int,   -- 选修课程编号
 CN  varchar(50), -- 选修课程名字
 CT  varchar(20)  -- 任课老师
 
)
-- 创建成绩表
create table t_Score(
 S varchar(20),  -- 学生编号
 C int,    -- 选修课程编号
 G  int    -- 成绩
)

-- 1、使用子查询出选修课程名称为数学的学员学号和姓名
select * from student stu
where stu.S in(
  select sc.s from score sc
  where sc.c = (
   select c.c from course c
   where c.cn= '数学'
)
)
 
-- 2、查询选修课程超过5门的学员学号和所属单位 统计操作 distinct 去重复
select S,SD from  t_Student
where S in(
select S from t_Score
group by S
having count(distinct C)>5
);

-- 3、查询两门以上(含两门)课程不及格的学生姓名及其平均成绩
select s.SN, avg(sc.G) from t_student s,t_score sc
where
     s.S = sc.S and sc.G < 60
group by sc.s
having count(*)>1;

-- 4、查询出英语成绩比语文成绩高的学生学号,以及英语和语文成绩
select stu.S,yy.G as 英语,yw.G as 语文 from
    t_Student stu,
        (select * from t_Score
            where C = (select C from t_Course where CN='语文'))as yw,
        (select * from t_Score
            where C = (select C from t_Course where CN='英语'))as yy
        where stu.S = yw.S#
        and yw.S=yy.S
        and yy.G>yw.G

-- 5、查询同时选修了历史和地理的所有学生姓名
select sc1.S from t_score sc1
inner join t_course c
on sc1.C = c.C
where c.CN = '历史'
intersect #交集
select sc.S from t_score sc
inner join t_course c
on sc.C = c.C
where c.CN = '地理';

 

第四

emp 员工表
 empno 员工号
 ename 员工姓名 
 job 工作
 mgr 上级编号
 hiredate 受雇日期
 sal 薪金
 comm 佣金
 deptno 部门编号

dept 部门表
 deptno 部门编号
 dname 部门名称
 loc 地点
 
工资 = 薪金 + 佣金

 

#1、列出至少有一个员工的所有部门。
select dname
from dept
where deptno in(
   select deptno from emp
);

#2、列出薪金比“SMITH”多的所有员工。（大于最大薪水SMITH员工）
select *
from emp
where sal > (
  select sal from emp
  where ename = 'SMITH'
); 

#3、列出所有员工的姓名及其直接上级的姓名。
select a.ename,
(
   select ename
   from emp b
   where b.empno=a.mgr
)
as boss_name
from emp a;  

#4、列出受雇日期早于其直接上级的所有员工。
select a.ename
from emp a
where a.hiredate<(
  select hiredate
  from emp b
  where b.empno=a.mgr
);  

#5、列出部门名称和这些部门的员工信息，包括那些没有员工的部门。
select a.dname,b.empno,b.ename,b.job,b.mgr,b.hiredate,b.sal,b.deptno 
from dept a
left join emp b
on a.deptno=b.deptno;

#6、列出所有job为“CLERK”（办事员）的姓名及其部门名称。
select a.ename,b.dname
from emp a
join dept b
on a.deptno=b.deptno
and a.job='CLERK'; 

#7、列出最低薪金大于1500的各种工作。
select distinct job as HighSalJob
from emp
group by job
having min(sal)>1500;  

#8、列出在部门“SALES”（销售部）工作的员工的姓名，假定不知道销售部的部门编号。
select ename
from emp
where deptno=(
select deptno
from dept
where dname='SALES'
);

#9、列出薪金高于公司平均薪金的所有员工。
select ename
from emp
where sal>(
select avg(sal) from emp
);  

#10、列出与“SCOTT”从事相同工作的所有员工。
select ename
from emp
where job=(
select job
from emp
where ename='SCOTT'
); 

#11、列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金。
select a.ename,a.sal
from emp a
where a.sal in (
select b.sal 
from emp b
where b.deptno=30
)
and a.deptno$amp;<amp;$gt;30;

#12、列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金。
select ename,sal
from emp
where sal>(
select max(sal)
from emp
where deptno=30
);  

#13、列出在每个部门工作的员工数量、平均工资和平均服务期限。
select (
select b.dname
from dept b
where a.deptno=b.deptno)
as deptname ,count(deptno)
as deptcount,avg(sal)
as deptavgsal 
from emp a
group by deptno;  

#14、列出所有员工的姓名、部门名称和工资。
select a.ename,(
select b.dname
from dept b
where b.deptno=a.deptno)
as deptname,sal
from emp a;

#15、列出从事同一种工作但属于不同部门的员工的一种组合。

select distinct a.ename,a.job,a.deptno

from emp a,enp b
where (

a.job=b.job

)

and (

a.deptno!=deptno

)

order by a.job;
#16、列出所有部门的详细信息和部门人数。
select d.deptno,d.dname,d.loc,(select count(deptno)
from emp e
where e.deptno = d.deptno
group by e.deptno) as
deptcount from dept d

#17、列出各种工作的最低工资。
select job,min(sal) from emp group by job

#18、列出各个部门的MANAGER（经理）的最低薪金（job为MANAGER）。
select deptno,min(sal)
from emp
where job='MANAGER'
group by deptno

#19、列出所有员工的年工资，按年薪从低到高排序
select ename,sal * 12 nianxin
from emp
order by nianxin asc