環(huán)境是mysql
練習(xí)數(shù)據(jù)見(jiàn)SQL：練習(xí)的前期準(zhǔn)備
sql 練習(xí)(一)
sql 練習(xí)(二)
sql 練習(xí)(三)
31菌仁、查詢所有“女”教師和“女”同學(xué)的name存崖、sex和birthday.
SELECT sname,ssex,sbirthday
FROM student
WHERE ssex = '女'
UNION
SELECT tname,tsex,tbirthday
FROM teacher
WHERE tsex = '女'
32、查詢成績(jī)比該課程平均成績(jī)低的同學(xué)的成績(jī)表搏熄。
SELECT a.sno,a.cno,a.degree
FROM score a
LEFT JOIN (
SELECT AVG(degree) degree,cno
FROM score
GROUP BY cno)b
ON a.cno=b.cno
WHERE a.degree< b.degree
SELECT Sno,Cno,degree
FROM Score a
WHERE degree<(SELECT AVG(degree) FROM score b WHERE a.Cno=b.Cno)
33棚唆、查詢所有任課教師的Tname和Depart.
SELECT tname,depart
FROM teacher
WHERE tno IN(SELECT tno FROM course )
SELECT tname,depart
FROM teacher a
WHERE EXISTS (SELECT * FROM course b WHERE a.tno=b.tno)
34、查詢所有未講課的教師的Tname和Depart.
SELECT tname,depart
FROM Teacher
WHERE tno NOT IN(SELECT tno FROM course)
35心例、查詢至少有2名男生的班號(hào)宵凌。
SELECT class
FROM Student
WHERE Ssex='男'
GROUP BY class
HAVING COUNT(SSex)>=2
36、查詢不姓王的同學(xué)信息
SELECT *
FROM student
WHERE sname NOT LIKE '王%'
37止后、查詢Student表中每個(gè)學(xué)生的姓名和年齡瞎惫。
SELECT Sname,(YEAR(NOW())-YEAR(Sbirthday)) age
FROM student
38溜腐、查詢Student表中最大和最小的Sbirthday日期值。
SELECT MAX(sbirthday),MIN(sbirthday)
FROM student
39瓜喇、以班號(hào)和年齡從大到小的順序查詢Student表中的全部記錄挺益。
SELECT *
FROM student
ORDER BY sbirthday ASC,class DESC
40、查詢“男”教師及其所上的課程乘寒。
SELECT a.tname,b.cname
FROM teacher a
JOIN course b
ON a.tno = b.tno
WHERE a.tsex ='男'
41望众、查詢最高分同學(xué)的Sno、Cno和Degree列伞辛。
SELECT *
FROM score
WHERE degree=(SELECT MAX(degree)FROM score)
42烂翰、查詢所有選修“計(jì)算機(jī)導(dǎo)論”課程的“男”同學(xué)的成績(jī)表。
SELECT sno,degree
FROM score
WHERE sno IN (SELECT sno FROM student WHERE ssex='男')
AND cno IN (SELECT cno FROM course WHERE cname='計(jì)算機(jī)導(dǎo)論')
43蚤氏、查詢和“李軍”同性別的所有同學(xué)的Sname.
SELECT sname
FROM student
WHERE ssex=(SELECT ssex FROM student WHERE sname='李軍')
44甘耿、查詢?nèi)掌?/p>
SELECT *
FROM teacher
WHERE tbirthday=STR_TO_DATE('1972-05-05 00:00:00','%Y-%m-%d')
