#1
SELECT count(t_name) FROM teacher WHERE t_name like '李%';
#2
SELECT DISTINCT sid,Sname,COUNT(expr) FROM student
-- 3
SELECT student.*,score FROM sc LEFT JOIN student on sc.Sid=student.Sid
WHERE cid=1 and score <(SELECT score FROM sc as p where p.sid=sc.sid and cid=2);
-- INSERT course VALUEs(7,'生物',7)
-- SELECT * FROM course
-- SELECT * FROM sc WHERE
-- (SELECT *,tid FROM teacher WHERE Tname ='abc' ) not in
--
--
-- (SELECT cid FROM sc as p WHERE p.sid=sc.Sid GROUP BY sid,cid )
-- 4SELECT * FROM student WHERE
SELECT * FROM student WHERE
(SELECT cid FROM course WHERE tid=
(SELECT tid FROM teacher WHERE tname='张三' )) not in (
SELECT cid FROM sc WHERE sid=student.sid );
#5
SELECT * FROM course
SELECT * FROM student WHERE sid in
( SELECT if(COUNT( cid)<5,Sid,-1) FROM sc GROUP BY sid );
# 6
SELECT * FROM sc GROUP BY
SELECT student.*,AVG(score) FROM student RIGHT JOIN sc on student.Sid=sc.Sid WHERE student.sid in
(SELECT if(sum( score <60)>=2,sid,-1) FROM sc GROUP BY sid ) GROUP BY sid;
7.ALTER
SELECT NULL 课程id,null 课程neme,null 最高分,null 最低分,null 平均分,null 及格,null 中等率,null 及格率 FROM sc
9
SELECT *
FROM sc WHERE ( SELECT sum(score>sc.score) FROM sc as p WHERE cid=sc.cid GROUP BY cid LIMIT 1
) <3 GROUP BY cid,sid
ORDER BY score DESC ;
sql 面试基础 必会
最新推荐文章于 2021-09-18 11:11:31 发布