在數據庫建立四個表：分別為

student(sid,sname,sage,ssex)

teacher(tid,tname)

course(cid,cname,tid)

sc(sid,cid,score)

-- 1、查詢“001”課程比"002"課程成績高的所有學生的學號。

select a.sid FROM

(select * from sc where cid="001") as a,

(select * from sc where cid = "002")as b

where a.sid = b.sid and a.score>b.score

-- 2、查詢平均成績大于60分的同學的學號和平均成績

select sid,avg(score)

FROM sc

GROUP BY sid

HAVING avg(score)>=70;

-- 3、查詢所有的同學的學號、姓名、選課數、總成績

select student.sid, sname,COUNT(*),sum(score)

from sc,student

where sc.sid = student.sid

GROUP BY sid;

-- 4、查詢姓“李”的老師的個數

select COUNT(*)

from teacher

where tname LIKE '大%';

-- 5、查詢沒學過“葉平“老師的課程的同學的學號、姓名

SELECT sid,sname

from student

where sid NOT in(select sid

from sc,course,teacher

where sc.cid = course.cid and teacher.tid = course.tid and teacher.tname = "葉良辰");

--? 6、查詢所有? ? 課程有掛科的同學的學號、姓名

SELECT sid ,sname

from student

where

sid in (select sid from sc) AND

sid not in(select sid from sc where score<60 GROUP BY sid);

--? 7、查詢至少一門課與學號為“2”的同學所學相同的學生的學號和姓名

SELECT DISTINCT(sc.sid)

from sc,student

where student.sid = sc.sid and cid in(select cid from sc where sc.sid='2') and student.sid<>'2';

-- 8、統計列印各科成績，各分數段人數：課程ID、課程名稱，100-85,85-70,70-60,<60

select sc.cid as '課程ID',cname as '課程名稱',

SUM(case WHEN score between 85 and 100 then 1 else 0 end) as '85-100',

SUM(case WHEN score between 70 and 84 then 1 else 0 end) as '70-84',

SUM(case WHEN score between 69 and 60 then 1 else 0 end) as '60-69',

SUM(case WHEN score between 0 and 100 then 59 else 0 end) as '0-59'

from course,sc

where sc.cid=course.cid

GROUP BY sc.cid;

--? 9、查詢每門課程的課程名和選修的學生數

select cname,count(*)

from sc,course

where course.cid=sc.cid

group by sc.cid;

-- 10、查詢出只選修了一門課程的全部同學的學號、姓名

select sc.sid,sname

from sc,student

where student.sid=sc.sid

GROUP BY (sc.sid)

HAVING COUNT(*)=1;

-- 11、查詢男生、女生的人數

(select "男生" AS "性別",count(*) from student where ssex="男")

UNION

(select "女生" as "性別", count(*) from student where ssex="女");

--? 12、查詢姓“李”的師生名單

(select sname as '名單' from student where sname like '李%')

union

(select tname as '名單' from teacher where tname like "李%");

在數據庫建立三個表：

學生表：student(sno,sname,sage,ssex,sdept)==(學號，姓名，年齡，性別，系別)

課程表：course(cno,cname,credit)==(課程號，課程名，學分)

選課表：sc(sno,cno,grade)===(學號，課程號，成績)

-- 寫出選修了數據結構的同學的學號和姓名

select *

from student

where sno in(select sno

from sc,course

where sc.cno=course.cno and cname='數據結構'

);

-- 1.統計每門課的選課人數，包括沒有人選的課程，列出課程號及選課情況，其中選課情況為，如果此門課的選課人數超過100

-- 人，則顯示人多，40-100一般 1-40人好，無人選

select? course.cno ,

case

when (count(*)>=40 and count(*)<=100) then? '較多'

when (count(*)>1 and count(*)<40) then '較少'

else '無人選' end as '選課情況'

from course left join sc

on course.cno=sc.cno

GROUP BY cno;

--? 2.查詢計算機有哪些學生沒有選課，列出姓名和學號(用外連接)

select sname

from student left join? sc on

sc.sno=student.sno

where sdept="計科" and sc.sno is null;

2>--?成績小于60的學生姓名,課程,成績

select sname,cname,grade

from student,sc,course

where student.sno=sc.sno and sc.cno=course.cno

and grade<60;

-- 3. 統計每個學生的選課人數和考試總成績，并按照選課門數升序排列

select sno,count(*)'選課門數' ,sum(grade)'總成績'

from sc

GROUP BY sno

ORDER BY count(*) DESC;

總結

以上是生活随笔為你收集整理的mysql查询语句习题._MySql数据库基本select查询语句练习题，初学者易懂。的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。