Êý¾Ý¿âÔ­Àí¼°Ó¦ÓÃϰÌâ²Î¿¼´ð°¸

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

ÁªÏµ¿Í·þ£º779662525#qq.com(#Ìæ»»Îª@)