FROM SC
WHERE C# IN (SELECT C# FROM C
WHERE TEACHER=¡¯LIU¡¯))
»ò£º
SELECT SNAME (´æÔÚÁ¿´Ê·½Ê½) FROM S
WHERE SEX=¡¯F¡¯ AND EXISTS(SELECT* FROM SC
WHERE SC.S#=S.S# AND EXISTS(SELECT * FROM C
WHERE C.C#=SC.C# AND TEACHER=¡¯LIU¡¯))
¢É SELECT C# FROM C
WHERE NOT EXISTS (SELECT * FROM S,SC
WHERE S.S#=SC.S# AND SC.C#=C.C# AND SNAME=¡¯WANG)); ¢Ê SELECT DISTINCT X.S# FROM SC AS X£¬SC AS Y
WHERE X.S#=Y.S# AND X.C#!=Y.C#; ¢Ë SELECT C#.CNAME FROM C
WHERE NOT EXISTS (SELECT * FROM S
WHERE NOT EXISTS (SELECT * FROM SC
WHERE S#=S.S# AND C#=C.C#)); ¢Ì SELECT DISTINCT S#
FROM SC AS X
WHERE NOT EXISTIS
(SELECT *
FROM C
WHERE TEACHER=¡¯LIU¡¯ AND NOT EXISTS (SELECT * FROM SC AS Y
WHERE Y.S#=X.S# AND Y.C#=C.C#));
4-3 ÊÔÓÃSQL²éѯÓï¾ä±í´ïÏÂÁжÔ4.2ÌâÖнÌÎñ¹ÜÀíÊý¾Ý¿âµÄÈý¸ö»ù±¾±íS¡¢SC¡¢C²éѯ£º ¢Å ͳ¼ÆÓÐѧÉúÑ¡Ð޵ĿγÌÃÅÊý¡£
¢Æ ÇóÑ¡ÐÞ4ºÅ¿Î³ÌµÄѧÉúµÄƽ¾ùÄêÁä¡£
¢Ç ÇóLIUÀÏʦËùÊڿγ̵ÄÿÃſγ̵ÄѧÉúƽ¾ù³É¼¨¡£
µÚ 8 Ò³
¢È ͳ¼ÆÃ¿Ãſγ̵ÄѧÉúÑ¡ÐÞÈËÊý£¨³¬¹ý10È˵Ŀγ̲Åͳ¼Æ£©¡£ÒªÇóÊä³ö¿Î³ÌºÅºÍÑ¡ÐÞÈËÊý£¬²éѯ½á¹û°´ÈËÊý½µÐòÅÅÁУ¬ÈôÈËÊýÏàͬ£¬°´¿Î³ÌºÅÉýÐòÅÅÁС£ ¢É ¼ìË÷ѧºÅ±ÈWANGͬѧ´ó£¬¶øÄêÁä±ÈËûСµÄѧÉúÐÕÃû¡£ ¢Ê ¼ìË÷ÐÕÃûÒÔWANG´òÍ·µÄËùÓÐѧÉúµÄÐÕÃûºÍÄêÁä¡£ ¢Ë ÔÚSCÖмìË÷³É¼¨Îª¿ÕÖµµÄѧÉúѧºÅºÍ¿Î³ÌºÅ¡£ ¢Ì ÇóÄêÁä´óÓÚŮͬѧƽ¾ùÄêÁäµÄÄÐѧÉúÐÕÃûºÍÄêÁä¡£
¢Í ÇóÄêÁä´óÓÚËùÓÐŮͬѧÄêÁäµÄÄÐѧÉúÐÕÃûºÍÄêÁä¡£
½â£º
¢Å SELECT COUNT(DISTINCT C#) FROM SC;
¢Æ SELECT AVG(AGE) FROM S,SC
WHERE S.S#=SC.S# AND C#=¡¯4¡¯AND SEX=¡¯F¡¯; ¢Ç SELECT C.C#,AVG(GRADE) FROM SC,C
WHERE SC.C#=C.C# AND TEACHER=¡¯LIU¡¯; ¢È SELECT C#,COUNT(S#) FROM SC
GROUP BY C#
HAVING COUNT(*)>10
ORDER BY 2 DESC£¬1; ¢É SELECT SNAME FROM S
WHERE S#>ALL(SELECT S# FROM S
WHERE SNAME=¡¯WANG¡¯
AND AGE FROM S WHERE SNAME=¡¯WANG¡¯); ¢Ê SELECT SNAME,AGE FROM S WHERE SNAME LIKE ¡®WANG%¡¯ ¢Ë SELECT S#,C# FROM SC WHERE GRADE IS NULL; ¢Ì SELECT SNAME,AGE FROM S WHERE SEX=¡¯M¡¯AND AGE>(SELECT AVG(AGE) FROM S WHERE SEX=¡¯F¡¯); ¢Í SELECT SNAME,AGE FROM S WHERE SEX=¡¯M¡¯AND AGE>ALL(SELECT AGE FROM S µÚ 9 Ò³ WHERE SEX=¡¯F¡¯); 4-4 ÊÔÓÃSQL¸üÐÂÓï¾ä±í´ï¶Ô4.2¸ø³öµÄ½ÌÎñ¹ÜÀíÊý¾Ý¿âÖÐÈý¸ö»ù±¾±íS¡¢SC¡¢C½øÐÐÈçϸüвÙ×÷£º ¢Å Íù»ù±¾±íSÖвåÈëÒ»¸öѧÉúÔª×é(¡®200912143¡¯£¬¡®Õž§¡¯£¬21)¡£ ¢Æ ÔÚ»ù±¾±íSÖмìË÷ÿһÃſγ̳ɼ¨¶¼´óÓÚµÈÓÚ80·ÖµÄѧÉúѧºÅ¡¢ÐÕÃûºÍÐԱ𣬲¢°Ñ¼ìË÷µ½µÄÖµËÍÍùÁíÒ»¸öÒÑ´æÔڵĻù±¾±íSTUDENT(SNO,SNAME,SEX)¡£ ¢Ç ÔÚ»ù±¾±íSCÖÐɾ³ýÉÐÎ޳ɼ¨µÄÑ¡¿ÎÔª×é¡£ ¢È °ÑÕųÉÃñͬѧÔÚSCÖеÄÑ¡¿Î¼Ç¼ȫ²¿É¾È¥¡£ ¢É °ÑÑ¡Ð޸ߵÈÊýѧ¿Î³ÌÖв»¼°¸ñµÄ³É¼¨È«²¿¸ÄΪ¿ÕÖµ¡£ ¢Ê °ÑµÍÓÚ×ÜÆ½¾ù³É¼¨µÄŮͬѧ³É¼¨Ìá¸ß5%¡£ ¢Ë ÔÚ»ù±¾±íSCÖÐÐÞ¸Ä4ºÅ¿Î³ÌµÄ³É¼¨£¬Èô³É¼¨Ð¡ÓÚµÈÓÚ75·ÖʱÌá¸ß5%£¬Èô³É¼¨´óÓÚ75·ÖʱÌá¸ß4%£¨ÓÃÁ½¸öUPDATEÓï¾äʵÏÖ£©¡£ ½â£º ¢ÅINSERT INTO S(S#,SNAME,AGE) VALUES(¡®200912143¡¯,¡¯Õž§¡¯,21); INSERT INTO STUDENT¢Æ(SNO,SNAME,SEX) SELECT S#,SNAME,SEX FROM S WHERE S# IN (SELECT S# FROM SC WHERE 80<=ALL(SELECT GRADE FROM SC GROUP BY S#)); DELETE FROM SC¢Ç WHERE GRADE IS NULL; DELE¢ÈTE FROM SC WHERE S# IN(SELECT S# FROM S WHERE SNAME=¡¯ÕÅÃñ¡¯) ¢ÉUPDATE SC SET GRADE=NULL WHERE GRADE<60 AND C# IN(SELECT C# FROM C WHERE CNAME=¡¯¸ßµÈÊýѧ¡¯); ¢ÊUPDATE SC SET GRADE=GRADE*1.05 WHERE S# IN(SELECT S# FROM S WHERE SEX=¡¯F¡¯) AND GRADE<(SELECT AVG(GRADE) FROM SC); ¢Ë ÓÃÁ½¸öUPDATEÓï¾äʵÏÖ£º UPDATE SC