ËÄ´¨´óѧÊý¾Ý¿â¼¼Êõ¸´Ï°Ìâ Èý ²Ù×÷Ìâ ÏÂÔØ±¾ÎÄ

²¿ÃÅ£ºDEPARTMENT(DNAME, DNUMBER, MSGSSN, MSGSTARTDATE)£» ¹¤³ÌÏîÄ¿£ºPROJECT(PNAME, PNUMBER, PLOCATION, DNUM)£» ¹¤×÷ÈËÔ±Ó빤³ÌÏîÄ¿£ºWORKS_ON(SSN,PNO,HOURS)£»ÊôÐÔ˵Ã÷ÈçÏ£ºNAME£º¹ÍÔ±Ãû£»SSN£º¹ÍÔ±µÄÉí·ÝÖ¤ºÅ£»BDATE£º¹ÍÔ±ÉúÈÕ£»ADDRESS£º¹ÍÔ±µØÖ·£»SEX£º¹ÍÔ±ÐÔ±ð£»SALARY£º¹ÍÔ±¹¤×Ê£»SUPERSSN£º¹ÍÔ±Ö±½ÓÁìµ½µÄÉí·ÝÖ¤ºÅ£»DNO£º¹¤×÷ÈËÔ±ËùÊô²¿Ãźţ»DNAME£º²¿ÃÅÃû×Ö£»DNUMBER£º²¿Ãźţ»MSGSSN£º²¿ÃÅÁìµ¼Éí·ÝÖ¤ºÅ£»MSGSTARTDATE£º²¿ÃÅÁìµ¼¿ªÊ¼Áìµ¼¹¤×÷µÄÈÕÆÚ£»PNAME£ºÏîÄ¿Ãû³Æ£»PNUMBER£ºÏîÄ¿ºÅ£»PLOCATION£ºÏîÄ¿ËùÔڵأ»DNUM£ºÏîÄ¿ËùÊô²¿Ãźţ»PNO£ºÏîÄ¿ºÅ£»HOURS£º¹¤×÷СʱÊý¡£ÓÃSQL±í´ïÈçϲéѯ£º (1) ²Î¼ÓÏîÄ¿ÃûΪ¡°¹«Â·1¡±µÄ¹ÍÔ±Ãû¡£

(2) ¼ìË÷²Î¼Ó¹¤³ÌºÅΪ¡°P1¡±£¬ÇÒ³ê½ð×î¸ßµÄÖ°¹¤ºÅ¼°Æä¹¤×Ê¡£

30. ¹ØÏµÄ£Ê½ÃèÊöÈçÏ£º

¹¤×÷ÈËÔ±£ºEMPLOYEE(NAME, SSN, BDATE, ADDRESS, SEX, SALARY, SUPERSSN, DNO)£»

²¿ÃÅ£ºDEPARTMENT(DNAME, DNUMBER, MSGSSN, MSGSTARTDATE)£» ¹¤³ÌÏîÄ¿£ºPROJECT(PNAME, PNUMBER, PLOCATION, DNUM)£» ¹¤×÷ÈËÔ±Ó빤³ÌÏîÄ¿£ºWORKS_ON(SSN,PNO,HOURS)£»

ÊôÐÔ˵Ã÷ÈçÏ£ºNAME£º¹ÍÔ±Ãû£»SSN£º¹ÍÔ±µÄÉí·ÝÖ¤ºÅ£»BDATE£º¹ÍÔ±ÉúÈÕ£»ADDRESS£º¹ÍÔ±µØÖ·£»SEX£º¹ÍÔ±ÐÔ±ð£»SALARY£º¹ÍÔ±¹¤×Ê£»SUPERSSN£º¹ÍÔ±Ö±½ÓÁìµ½µÄÉí·ÝÖ¤ºÅ£»DNO£º¹¤×÷ÈËÔ±ËùÊô²¿Ãźţ»

DNAME£º²¿ÃÅÃû×Ö£»DNUMBER£º²¿Ãźţ»MSGSSN£º²¿ÃÅÁìµ¼Éí·ÝÖ¤ºÅ£»MSGSTARTDATE£º²¿ÃÅÁìµ¼¿ªÊ¼Áìµ¼¹¤×÷µÄÈÕÆÚ£»PNAME£ºÏîÄ¿Ãû³Æ£»PNUMBER£ºÏîÄ¿ºÅ£»PLOCATION£ºÏîÄ¿ËùÔڵأ»DNUM£ºÏîÄ¿ËùÊô²¿Ãźţ»PNO£ºÏîÄ¿ºÅ£»HOURS£º¹¤×÷СʱÊý¡£ÓùØÏµ´úÊýºÍSQL±í´ïÈçϲéѯ£º (1) ÁгöûÓвμÓÏîÄ¿P1µÄÔ±¹¤ÐÕÃû¡£

(2) ÁгöËùÓÐÓÉÕÅÈýÖ±½ÓÁìµ¼µÄ¹¤×÷ÈËÔ±µÄÃû×ÖºÍËùÔÚ²¿ÃÅÃû×Ö¡£

31. ÔÚ¹©Ó¦ÉÌ¡¢Áã¼þÊý¾Ý¿âÖÐÓÐÒÔÏÂÈý¸ö¹ØÏµÄ£Ê½£º¹©Ó¦ÉÌ£ºS(SNO,SNAME,CITY,STATUS)£»Áã¼þ£ºP(PNO,PNAME,WEIGHT,COLOR,CITY) ¹©Ó¦»õ£ºSP(SNO,PNO,QTY)£»¸÷ÊôÐԵĺ¬Òå¿ÉÓÉÊôÐÔÃûÌåÏÖ£¬²»ÔÙÖØ¸´£¬¹©»õ¹ØÏµSP±íʾij¹©Ó¦ÉÌSNO,¹©Ó¦ÁËPNOÁã¼þ£¬ÊýÁ¿ÎªQTY¡£ÓÃSQLÓï¾äʵÏÖÏÂÁвéѯ£º (1) Çó¹©Ó¦ºìÉ«Áã¼þµÄ¹©Ó¦ÉÌÃû×Ö¡£

(2) Çó±±¾©¹©Ó¦É̵ĺÅÂë¡¢Ãû×ÖºÍ×´¿ö(STATUS)¡£ (3) ÇóÁã¼þP2µÄ×ܹ©Ó¦Á¿¡£

(4) °ÑÁã¼þP2µÄÖØÁ¿Ôö¼Ó6£¬ÑÕÉ«¸ÄΪÀ¶É«¡£

32. ÓÐһѧУ½ÌѧÊý¾Ý¿â,°üÀ¨Ñ§Éú¡¢¿Î³Ì¡¢½Ìʦ¡¢Ñ§Éú³É¼¨4¸ö¹ØÏµ¡£Ñ§Éú¹ØÏµS(SNO,SN,AGE,SEX),ÓÐÊôÐÔ£ºÑ§ºÅ£¬ÐÕÃû£¬ÄêÁ䣬ÐԱ𣻿γ̹ØÏµC(CNO,CN,PCNO)°üÀ¨ÊôÐÔ£º¿Î³ÌºÅ£¬¿Î³ÌÃû£¬ÏÈÐ޿ογ̺ţ»½Ìʦ¹ØÏµT(ENO,EN,DEPT),°üÀ¨ÊôÐÔ£ºÖ°¹¤ºÅ£¬ÐÕÃû£¬Ïµ±ð£»Ñ§Éú³É¼¨¹ØÏµSC(SNO,CNO,ENO,G) °üÀ¨ÊôÐÔ£ºÑ§ÉúºÅ£¬¿Î³ÌºÅ£¬ÈÎ

¿Î½Ìʦְ¹¤ºÅºÍѧÉúѧϰ³É¼¨£»ÇëÓùØÏµ´úÊý±í´ïʽÍê³ÉÏÂÁвÙ×÷£º (1) ÇóÑ¡ÐÞËùÓпγ̲¢Çҳɼ¨ÎªAµÄѧÉúÐÕÃû¡£ (2) ÇóÑ¡ÐÞÁËÍõƽÀÏʦ½²ÊÚµÄËùÓпγ̵ÄѧÉúÐÕÃû¡£ (3) Çó²»Ñ¡ÐÞÐÅϢϵÀÏʦ¿ªÉèµÄËùÓпγ̵ÄѧÉúÐÕÃû¡£

33. ¹©Ó¦ÉÌ-Áã¼þ-¹¤³ÌÏîÄ¿Êý¾Ý¿âÓÉÒÔÏÂ4¸ö¹ØÏµÄ£Ê½¹¹³É£ºS(SNO , SNAME , STATUS , CITY)£»P(PNO , PNAME , COLOR , WEIGHT , CITY)£»J(JNO , JNAME , CITY)£»SPJ(SNO , PNO , JNO , QTY)£»¹©Ó¦ÉÌS , Áã¼þPºÍ¹¤³ÌÏîÄ¿J ·Ö±ðÓɹ©Ó¦É̺Å(SNO),Áã¼þºÅ(PNO)ºÍ¹¤³ÌÏîÄ¿ºÅ(JNO)Ψһ±êʶ¡£¹©»õSPJÊÇÖ¸ÓÉij¸ö¹©Ó¦ÉÌÏòij¸ö¹¤³ÌÏîÄ¿¹©Ó¦Ä³Ð©ÊýÁ¿µÄijÖÖÁã¼þ¡£ÇëÓÃSQLÓïÑÔÍê³ÉÈçϲÙ×÷£º (1) ÕÒ³ö¸ø±±¾©µÄ¹¤³ÌÏîÄ¿Ìṩ²»Í¬µÄÁã¼þºÅ¡£ (2) ½«Ã»Óй©»õµÄËùÓй¤³ÌÏîÄ¿´ÓJÖÐɾ³ý¡£ (3) ²éѯÌṩȫ²¿Áã¼þµÄ¹©Ó¦ÉÌÃû¡£

(4) ²éѯÕâÑùµÄ¹¤³ÌÏîÄ¿ºÅ£º¹©¸ø¸Ã¹¤³ÌÏîÄ¿µÄÁã¼þP1µÄƽ¾ù¹©Ó¦Á¿´óÓÚ¹©¸ø¹¤³ÌÏîÄ¿J1µÄÈκÎÒ»ÖÖÁã¼þµÄ×î´ó¹©Ó¦Á¿¡£

(5) ¶¨ÒåÒ»¸öÊÓͼ£¬ËüÓÉËùÓÐÕâÑùµÄ¹¤³ÌÏîÄ¿£¨¹¤³ÌÏîÄ¿ºÅÓëËùÔÚ³ÇÊÐÃû³Æ)×é³É£ºËüÃÇÓɹ©Ó¦ÉÌS1¹©»õÇÒʹÓÃÁã¼þP1¡£

34. ÒÑÖª¹ØÏµS£¬P£¬J£¬SPJµÄ¹ØÏµÄ£Ê½Îª£ºS£¨SNO£¬SN£¬CITY£©£»P£¨PNO£¬PN£¬COLOR£©£»J£¨JNO£¬JN£¬CITY£©£»SPJ£¨SNO£¬JNO£¬PNO£¬QTY£©£»ÆäÖУºSNOΪ¹©Ó¦É̺ţ¬SNΪ¹©Ó¦ÉÌÐÕÃû£¬CITYΪËùÔÚ³ÇÊУ»PNOΪ

Áã¼þºÅ£¬PNΪÁã¼þÃû³Æ£¬COLORΪÑÕÉ«£»JNOΪ¹¤³ÌÏîÄ¿ºÅ£¬JNΪ¹¤³ÌÏîÄ¿Ãû³Æ£»QTYΪÊýÁ¿¡£ ÊÔÓÃSQLÓïÑÔÍê³ÉÏÂÁвéѯ£º

(1) Çó³öʹÓÃÈ«²¿Áã¼þµÄ¹¤³ÌÏîÄ¿Ãû³Æ¡£

(2) °ÑÁã¼þºÅΪP2µÄÁã¼þÖØÁ¿Ôö¼Ó5£¬ÑÕÉ«¸ÄΪ»ÆÉ«¡£ (3) È¡³öÔÚͬһ³ÇÊеÄÿ¶Ô¹¤³ÌÏîÄ¿ºÅ¡£

35. ÏÖÓйØÏµÊý¾Ý¿âÈçÏ£ºÑ§Éú(ѧºÅ£¬ÐÕÃû£¬ÐÔ±ð£¬×¨Òµ£¬½±Ñ§½ð)£»¿Î³Ì(¿ÎºÅ£¬¿ÎÃû£¬Ñ§·Ö) £»Ñ§Ï°(ѧºÅ£¬¿ÎºÅ£¬·ÖÊý) ¡£ ÓÃSQLÓï¾äʵÏÖÏÂÁвéѯ£º

(1) ¼ìË÷ûÓÐÈκÎÒ»Ãſγ̳ɼ¨ÔÚ80·ÖÒÔϵÄѧÉúµÄÐÅÏ¢£¬°üÀ¨Ñ§ºÅ¡¢ÐÕÃûºÍרҵ¡£

(2) °ÑѧÉú¡°ÕÅÈý¡±µÄ¡°Êý¾Ý¿â¡±¿Î³Ì·ÖÊý¸ÄΪ86·Ö¡£

(3) ¼ìË÷ûÓлñµÃ½±Ñ§½ð¡¢Í¬Ê±ÖÁÉÙÓÐÒ»Ãſγ̳ɼ¨ÔÚ95·ÖÒÔÉϵÄѧÉúÐÅÏ¢£¬°üÀ¨Ñ§ºÅ¡¢ÐÕÃûºÍרҵ¡£

(4) Çó¸÷רҵѧÉú¡°Êý¾Ý¿â¡±¿Î³ÌµÄƽ¾ù³É¼¨¡£

36. ÒÑÖª¹ØÏµS£¬SC£¬CµÄ¹ØÏµÄ£Ê½Îª£ºS£¨S#£¬SN£¬SA£¬SD£©£»SC£¨S#£¬C#£¬G£©£»C£¨C#£¬CN£¬PC#£©£»ÆäÖУºS#ΪѧºÅ£¬SNΪѧÉúÐÕÃû£¬SAΪÄêÁ䣬SDΪϵÃû£¬C#Ϊ¿Î³ÌºÅ£¬GΪ³É¼¨£¬CNΪ¿Î³ÌÃû³Æ£¬PC#ΪÏÈÐпκš£

ÊÔÓÃSQLÓïÑÔÍê³ÉÏÂÁвéѯ£º