sql子查询练习

sql子查询练习

#子查询临时表‘
CREATE TABLE shop(
goodid INT,
catid INTEGER,
NAME VARCHAR(10),
price INT

)
INSERT INTO shop VALUES(1,10,'vivo x90',3888),(2,10,'vivo x80',3288)
INSERT INTO shop VALUES(3,20,'iqoo neo5',1888),(5,20,'iqoo11pro',4288)
INSERT INTO shop VALUES(4,20,'iqoo z5',1288),(6,10,'vivo s12',2488)
INSERT INTO shop VALUES(8,30,'tws2',288),(7,30,'watch2',1288)
SELECT*FROM shop
#得到各个内别中最贵的商品
SELECT catid,MAX(price) FROM shop GROUP BY catid

SELECT goodid,shop.catid,NAME,price  FROM 
 (SELECT catid,MAX(price)AS maxprice FROM shop GROUP BY catid) temp,shop
 WHERE temp.catid=shop.catid AND temp.maxprice=shop.price;
#all操作符和any的使用

#显示价格比10号类别所有商品都贵的商品和商品编号
SELECT goodid,catid,NAME FROM shop 
WHERE price>ALL(SELECT price FROM shop WHERE catid=10)
#显示价格比10号类别所有商品其中一个都贵的商品和商品编号all->any
SELECT goodid,catid,NAME FROM shop 
WHERE price>ANY(SELECT price FROM shop WHERE catid=10) 
AND catid BETWEEN 20 AND 30

#多列子查询
SELECT *FROM emp
INSERT INTO emp VALUES(7699,'罗峰','保安部部长',7839,'1999-11-01','9999',NULL,30)
INSERT INTO emp VALUES(7700,'张卫健','保安部部长',7839,'2009-12-12','3000',NULL,30)
SELECT depton,job FROM emp WHERE ename='云韵'
#把上表当做子查询，并且用多列子查询
SELECT*FROM emp WHERE  (depton,job)=(SELECT depton,job FROM emp WHERE ename='云韵') AND ename!='云韵'
#
SELECT*FROM student
INSERT INTO student VALUES(11,'刘备',99,89,97)
INSERT INTO student VALUES(11,'看门狗',99,89,97)
UPDATE student SET NAME='抄袭哥',chinese=99,math=89,english=97 WHERE id=10

SELECT chinese,math,english FROM student WHERE NAME='刘备'
SELECT*FROM student WHERE (chinese,math,english)=
(SELECT chinese,math,english FROM student WHERE NAME='刘备') AND NAME!='刘备'

#查找每个部门雇员高于本部门平均工资的雇员】

#先得到每个部门部门号，和平均工资
SELECT depton,AVG(sal) AS avgsal FROM emp GROUP BY depton

SELECT ename,sal,temp.avgsal,emp.depton FROM emp,(SELECT depton,AVG(sal) AS avgsal FROM emp GROUP BY depton) temp
WHERE emp.depton=temp.depton AND emp.sal>temp.avgsal ORDER BY depton
#查找每个部门最高工资的雇员
SELECT ename,sal,temp.maxsal,emp.depton FROM emp,(SELECT depton,MAX(sal) AS maxsal FROM emp GROUP BY depton) temp
WHERE emp.depton=temp.depton AND emp.sal=temp.maxsal ORDER BY depton

#查询每个部门的部门名字，编号，地址和人员数量
SELECT *FROM dept

#先查每个部门的雇员人数
SELECT COUNT(*),depton FROM emp GROUP BY depton

SELECT deptnum,deptname,location,personnum FROM dept,(SELECT COUNT(*) AS personnum,depton FROM emp GROUP BY depton) temp
WHERE dept.deptnum=temp.depton