¡¶Êý¾Ý¿âÔ­ÀíÓëÓ¦Óá·ÊµÑéÌâÄ¿¼°´ð°¸

--[3_8] ijЩѧÉúÑ¡Ð޿γ̺óûÓвμӿ¼ÊÔ£¬ËùÒÔÓÐÑ¡¿Î¼Ç¼£¬µ«Ã»Óп¼ÊԳɼ¨¡£ --²éѯȱÉٳɼ¨µÄѧÉúµÄѧºÅºÍÏàÓ¦µÄ¿Î³ÌºÅ¡£

SELECT sno,cno FROM sc WHERE grade IS NULL;

ÔËÐнá¹û£º

sno cno --------- ----

--[3_9] ²éѯ¼ÆËã»úϵÄêÁäÔÚ20ËêÒÔϵÄѧÉúÐÕÃû¡£

SELECT sname FROM student

WHERE sdept='cs' AND sage<20;

ÔËÐнá¹û£º

sname -------- Áõ³¿

--[3_10] ²éѯѡÐÞÁË3ºÅ¿Î³ÌµÄѧÉúµÄѧºÅ¼°Æä³É¼¨£¬²éѯ½á¹û°´·ÖÊý½µÐòÅÅÁС£

SELECT sno,grade FROM sc WHERE cno='3' ORDER BY grade DESC;

ÔËÐнá¹û£º

sno grade

--------- --------------------------------------- 201313101 88.0 201313102 50.0

--[3_11] ²éѯȫÌåѧÉúÇé¿ö£¬²éѯ½á¹û°´ËùÔÚϵÉýÐòÅÅÁУ¬Í¬Ò»ÏµÖеÄѧÉú°´ÐÔ±ð½µÐòÅÅÁС£

SELECT * FROM student

ORDER BY sdept,sage DESC;

ÔËÐнá¹û£º

sno sname ssex sage sdept --------- -------- ---- ------ --------------------

201313101 ÀîÓ ÄÐ 20 CS 201313102 Áõ³¿ Å® 19 CS 201313104 ÕÅÁ¢ ÄÐ 19 IS 201313103 ÍõÃô Å® 18 MA

--ʹÓü¯º¯Êý

--[3_12] ²éѯѧÉú×ÜÈËÊý¡£

SELECT COUNT(*) ѧÉú×ÜÈËÊý FROM student;

ÔËÐнá¹û£º

6

ѧÉú×ÜÈËÊý ----------- 4

--[3_13] ²éѯѡÐÞÁ˿γ̵ÄѧÉúÈËÊý¡£

SELECT COUNT(DISTINCT sno) Ñ¡¿ÎÈËÊý --×¢:ÓÃDISTINCTÒÔ±ÜÃâÖظ´¼ÆËãѧÉúÈËÊý FROM sc;

ÔËÐнá¹û£º

Ñ¡¿ÎÈËÊý ----------- 2

--[3_14] ¼ÆËã1ºÅ¿Î³ÌµÄ×î¸ß¡¢×îµÍ¼°Æ½¾ù³É¼¨¡£

SELECT MAX(grade) '1ºÅ¿Î³Ì×î¸ß·Ö', MAX(grade) '1ºÅ¿Î³Ì×îµÍ·Ö', AVG(grade) '1ºÅ¿Î³Ìƽ¾ù³É¼¨' FROM sc WHERE cno='1'

ÔËÐнá¹û£º

1ºÅ¿Î³Ì×î¸ß·Ö 1ºÅ¿Î³Ì×îµÍ·Ö 1ºÅ¿Î³Ìƽ¾ù³É¼¨ --------------------------------------- --------------------------------------- --------------------------------------- 92.0 92.0 92.000000

--[3_15] ²éѯѧÉú201313101Ñ¡Ð޿γ̵Äƽ¾ù³É¼¨¡£

ÔËÐнá¹û£º

ѧÉúƽ¾ù³É¼¨

--------------------------------------- 71.666666

--[3_16] ²éѯѧÉú201313102Ñ¡Ð޿γ̵Ä×Üѧ·ÖÊý¡£

SELECT SUM(ccredit) ѧÉúѧ·Ö FROM sc,course

WHERE sc.cno=course.cno AND sno='201313102';

ÔËÐнá¹û£º

ѧÉúѧ·Ö ------------- 6

--ʹÓÃGROUP BY×Ó¾ä·Ö×é

--[3_17] Çó¸÷¸ö¿Î³ÌºÅ¼°ÏàÓ¦µÄÑ¡¿ÎÈËÊý¡£

SELECT cno ¿Î³ÌºÅ,COUNT(sno) Ñ¡¿ÎÈËÊý FROM sc GROUP BY cno;

ÔËÐнá¹û£º

¿Î³ÌºÅ Ñ¡¿ÎÈËÊý ---- ----------- 1 1

7

2 2 3 2

--ʹÓÃHAVING¶ÌÓïɸѡ×îÖÕ·Ö×é½á¹û

--[3_18] ²éѯѡÐÞÁË3ÃÅÒÔÉϿγ̵ÄѧÉúѧºÅ¡£

SELECT sno FROM sc GROUP BY sno

HAVING COUNT(*)>=3

ÔËÐнá¹û£º

sno --------- 201313101

--[3_19] ²éѯÓÐ2ÃÅÒÔÉϿγÌÊÇ90·ÖÒÔÉϵÄѧÉúµÄѧºÅ¼°(90·ÖÒÔÉϵÄ)¿Î³ÌÊýSELECT sno ѧºÅ,COUNT(*) '90·ÖÒÔÉϵĿγÌÊý' FROM sc WHERE grade>=90

GROUP BY sno HAVING COUNT(*)>=2

ÔËÐнá¹û£º

ѧºÅ 90·ÖÒÔÉϵĿγÌÊý --------- -----------

2. Á¬½Ó²éѯ --×ÔÈ»Á¬½Ó

--[3_20] ²éѯÿ¸öѧÉú¼°ÆäÑ¡Ð޿γ̵ÄÇé¿ö¡£

SELECT sname,cno,grade FROM student,sc

WHERE student.sno = sc.sno

ÔËÐнá¹û£º

sname cno grade

-------- ---- --------------------------------------- ÀîÓ 1 92.0 ÀîÓ 2 35.0 ÀîÓ 3 88.0 Áõ³¿ 2 90.0 Áõ³¿ 3 50.0

--×óÍâÁ¬½Ó

--[3_21] ²éѯÿ¸öѧÉú¼°ÆäÑ¡Ð޿γ̵ÄÇé¿ö(°üÀ¨Ã»ÓÐÑ¡Ð޿γ̵ÄѧÉú)¡£

SELECT student.sno,sname,cno,grade

FROM student LEFT JOIN sc ON student.sno = sc.sno

ÔËÐнá¹û£º

sno sname cno grade

--------- -------- ---- ---------------------------------------

8

201313101 ÀîÓ 1 92.0 201313101 ÀîÓ 2 35.0 201313101 ÀîÓ 3 88.0 201313102 Áõ³¿ 2 90.0 201313102 Áõ³¿ 3 50.0 201313103 ÍõÃô NULL NULL 201313104 ÕÅÁ¢ NULL NULL

--×ÔÉíÁ¬½Ó: Ò»¸ö±íÓëÆä×Ô¼º½øÐÐÁ¬½Ó£¬³ÆΪ±íµÄ×ÔÉíÁ¬½Ó --[3_22] ²éѯÿһÃſεļä½ÓÏÈÐ޿Σ¨¼´ÏÈÐ޿εÄÏÈÐ޿Σ©

SELECT FIRST.cno ¿ÎºÅ,SECOND.cpno ¼ä½ÓÏÈÐÞ¿Î FROM course FIRST,course SECOND WHERE FIRST.cpno = SECOND.cno

ÔËÐнá¹û£º

¿ÎºÅ ¼ä½ÓÏÈÐÞ¿Î ---- ----- 1 7 3 5 4 NULL 5 6 7 NULL

--¸´ºÏÌõ¼þÁ¬½Ó

--[3_23] ²éѯѡÐÞ2ºÅ¿Î³ÌÇҳɼ¨ÔÚ90·ÖÒÔÉϵÄËùÓÐѧÉúµÄѧºÅ¡¢ÐÕÃû¡£SELECT student.sno, sname FROM student,sc WHERE student.sno = sc.sno AND sc.cno='2' AND sc.grade >=90

ÔËÐнá¹û£º

sno sname --------- -------- 201313102 Áõ³¿

--¶à±íÁ¬½Ó

--[3_24] ²éѯÿ¸öѧÉúµÄѧºÅ¡¢ÐÕÃû¡¢Ñ¡Ð޵ĿγÌÃû¼°³É¼¨¡£

SELECT student.sno,sname,cname,grade FROM student,sc,course

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

ÔËÐнá¹û£º

sno sname cname grade

--------- -------- ---------------- --------------------------------------- 201313101 ÀîÓ Êý¾Ý¿â 92.0 201313101 ÀîÓ Êýѧ 35.0 201313101 ÀîÓ ÐÅϢϵͳ 88.0 201313102 Áõ³¿ Êýѧ 90.0 201313102 Áõ³¿ ÐÅϢϵͳ 50.0

3£® ǶÌײéѯ

9

--[3_25] ²éѯÓë¡°Áõ³¿¡±ÔÚÒ»¸öϵѧϰµÄѧÉú¡£

select sno,sname,sdept from student

where sdept in --µ±ÄÚ²éѯ½á¹û×î¶àÖ»ÓÐÒ»¸öֵʱ¿ÉÓÃ=´úÌæin (select sdept from student where sname='Áõ³¿')

ÔËÐнá¹û£º

sno sname sdept --------- -------- --------------------

201313101 ÀîÓ CS 201313102 Áõ³¿ CS

--[3_26] ²éѯËùÓÐÐÕÃûÏàͬµÄѧÉú

select * from student where sname in (select sname from student

group by sname having count(*)>1) order by sname,sno

ÔËÐнá¹û£º

sno sname ssex sage sdept --------- -------- ---- ------ --------------------

--[3_27] ²éѯѡÐÞÁ˿γÌÃûΪ¡°ÐÅϢϵͳ¡±µÄѧÉúѧºÅºÍÐÕÃû¡£

SELECT sno,sname --¢Û×îºóÔÚsudent¹ØϵÖÐÈ¡³öSnoºÍSname FROM student WHERE sno IN

(SELECT sno --¢ÚÈ»ºóÔÚscÖÐÕÒ³öÑ¡ÐÞÁË¡°ÐÅϢϵͳ¡±ËùÔڿκŵÄѧÉúѧºÅ FROM sc WHERE cno IN

(SELECT cno --¢ÙÊ×ÏÈÔÚcurse¹ØϵÖÐÕÒ³ö¡°ÐÅϢϵͳ¡±µÄ¿Î³ÌºÅ FROM course WHERE cname='ÐÅϢϵͳ'))

ÔËÐнá¹û£º

sno sname --------- -------- 201313101 ÀîÓ 201313102 Áõ³¿

--[3_28] ÕÒ³öÿ¸öѧÉú³¬¹ýËûÑ¡Ð޿γÌƽ¾ù³É¼¨µÄѧºÅ¡¢¿ÎºÅ¼°³É¼¨¡£

SELECT x.* FROM sc x

WHERE grade>=(SELECT AVG(grade)

10

ÁªÏµ¿Í·þ£º779662525#qq.com(#Ì滻Ϊ@) ËÕICP±¸20003344ºÅ-4