. . . .
£¨2£© ²éѯѧÉúµÄѧºÅ¡¢ÐÕÃû¡¢Ñ¡Ð޵ĿγÌÃûºÍ¿¼ÊԳɼ¨¡£ ´ð£ºCreate view v2 As
Select s.sno,sname,cname,grade
From student s join sc on s.sno = sc.sno Join course c on c.cno = sc.cno
£¨3£© ͳ¼ÆÃ¿¸öѧÉúµÄÑ¡¿ÎÃÅÊý£¬ÁгöѧÉúѧºÅºÍÑ¡¿ÎÃÅÊý¡£ ´ð£ºCreate view v3 As Select sno,count(*) as total From sc group by sno
£¨4£© ͳ¼ÆÃ¿¸öѧÉúµÄÐÞ¿Î×Üѧ·Ö£¬ÁгöѧÉúѧºÅºÍ×Üѧ·Ö£¨ËµÃ÷£º¿¼ÊԳɼ¨´óÓÚµÈÓÚ60²Å¿É»ñ
µÃ´ËÃſγ̵Äѧ·Ö£©¡£ ´ð£ºCreate view v4 As
Select sno,sum(credit) as total_credit From sno join course c on c.cno = sc.cno Where grade >= 60 Group by sno
2£® ÀûÓõÚ1Ì⽨Á¢µÄÊÓͼ£¬Ð´³öÍê³ÉÈçϲéѯµÄSQLÓï¾ä£¬²¢Ö´ÐÐÕâЩÓï¾ä£¬²é¿´Ö´Ðнá¹û¡£½«
²éѯÓï¾äºÍÖ´Ðнá¹û±£´æµ½Ò»¸öÎļþÖС£
£¨1£© ²éѯ¿¼ÊԳɼ¨´óÓÚµÈÓÚ90·ÖµÄѧÉúµÄÐÕÃû¡¢¿Î³ÌÃûºÍ³É¼¨¡£
. word×ÊÁϿɱ༠.
. . . .
´ð£ºSelect sname,cname,grade From v2 where grade >= 90
£¨2£© ²éѯѡ¿ÎÃÅÊý³¬¹ý3ÃŵÄѧÉúµÄѧºÅºÍÑ¡¿ÎÃÅÊý¡£ ´ð£ºSelect * from v3 where total >= 3
£¨3£© ²éѯ¼ÆËã»úϵѡ¿ÎÃÅÊý³¬¹ý3ÃŵÄѧÉúµÄÐÕÃûºÍÑ¡¿ÎÃÅÊý¡£ ´ð£ºSelect sname,total from v3 join student s on s.sno = v3.sno Where sdept = ¡®¼ÆËã»úϵ¡¯ and total >= 3
£¨4£© ²éѯÐÞ¿Î×Üѧ·Ö³¬¹ý10·ÖµÄѧÉúµÄѧºÅ¡¢ÐÕÃû¡¢ËùÔÚϵºÍÐÞ¿Î×Üѧ·Ö¡£ ´ð£ºSelect v4.sno,sname,sdept,total_credit From v4 join student s on s.sno = v4.sno Where total_credit >= 10
£¨5£© ²éѯÄêÁä´óÓÚµÈÓÚ20ËêµÄѧÉúÖУ¬ÐÞ¿Î×Üѧ·Ö³¬¹ý10·ÖµÄѧÉúµÄÐÕÃû¡¢ÄêÁä¡¢ËùÔÚϵ
ºÍÐÞ¿Î×Üѧ·Ö¡£
´ð£ºSelect sname,sage,sdept,total_credit From v4 join student s on s.sno = v4.sno Where sage >= 20 and total_credit >= 10
3£® Ð޸ĵÚ1Ì⣨4£©¶¨ÒåµÄÊÓͼ£¬Ê¹Æä²éѯÿ¸öѧÉúµÄѧºÅ¡¢×Üѧ·ÖÒÔ¼°×ܵÄÑ¡¿ÎÃÅÊý¡£
. word×ÊÁϿɱ༠.
. . . .
´ð£º Alter view v4 As
Select sno,sum(credit) as total_credit,count(*) as total_cno
From sc join course c on c.cno = sc.cno Group by sno
4£® д³öʵÏÖÏÂÁвÙ×÷µÄSQLÓï¾ä£¬Ö´ÐÐÕâЩÓï¾ä£¬²¢ÔÚSSMS¹¤¾ßÖй۲ìÓï¾äÖ´Ðнá¹û¡£ £¨1£© ÔÚStudent±íµÄSdeptÁÐÉϽ¨Á¢Ò»¸ö°´½µÐòÅÅÐòµÄ·Ç¾Û¼¯Ë÷Òý£¬Ë÷ÒýÃûΪ£ºIdx_Sdept¡£ ´ð£ºCreate index Idx_Sdept on student(Sdept DESC)
£¨2£© ÔÚStudent±íµÄSnameÁÐÉϽ¨Á¢Ò»¸öΨһµÄ·Ç¾Û¼¯Ë÷Òý£¬Ë÷ÒýÃûΪ£ºIdx_Sname¡£ ´ð£ºCreate unique index Idx_Sname on student(Sname)
£¨3£© ÔÚCourse±íÉÏΪCnameÁн¨Á¢Ò»¸ö·Ç¾Û¼¯Ë÷Òý£¬Ë÷ÒýÃûΪ£º Idx_Cname ´ð£ºCreate index Idx_Cname on Course(Cname)
£¨4£© ÔÚSC±íÉÏΪSnoºÍCno½¨Á¢Ò»¸ö×éºÏµÄ·Ç¾Û¼¯Ë÷Òý£¬Ë÷ÒýÃûΪ£ºIdx_SnoCno¡£ ´ð£ºCreate index Idx_SnoCno on SC(Sno,Cno)
£¨5£© ɾ³ýÔÚSnameÁÐÉϽ¨Á¢µÄIdx_SnameË÷Òý¡£ ´ð£ºdrop index Idx_Sname on Student
. word×ÊÁϿɱ༠.
. . . .
µÚ6Õ ¹ØÏµÊý¾ÝÀíÂÛ
Ò»£®Ñ¡ÔñÌâ
1£®¶Ô¹ØÏµÄ£Ê½½øÐй淶»¯µÄÖ÷ҪĿµÄÊÇ
B
A£®Ìá¸ßÊý¾Ý²Ù×÷ЧÂÊ B£®Î¬»¤Êý¾ÝµÄÒ»ÖÂÐÔ
C£®¼ÓÇ¿Êý¾ÝµÄ°²È«ÐÔ D£®ÎªÓû§Ìṩ¸ü¿ì½ÝµÄÊý¾Ý²Ù×÷
2£®¹ØÏµÄ£Ê½ÖеIJåÈëÒì³£ÊÇÖ¸
D
A£®²åÈëµÄÊý¾ÝÎ¥·´ÁËʵÌåÍêÕûÐÔÔ¼Êø B£®²åÈëµÄÊý¾ÝÎ¥·´ÁËÓû§¶¨ÒåµÄÍêÕûÐÔÔ¼Êø C£®²åÈëÁ˲»¸Ã²åÈëµÄÊý¾Ý D£®Ó¦¸Ã±»²åÈëµÄÊý¾Ý²»Äܱ»²åÈë
3£®Èç¹ûÓк¯ÊýÒÀÀµX¡úY£¬²¢ÇÒ¶ÔXµÄÈÎÒâÕæ×Ó¼¯X¡¯£¬¶¼ÓÐX¡¯
Y£¬Ôò³Æ C
A£®XÍêÈ«º¯ÊýÒÀÀµÓÚY B£®X²¿·Öº¯ÊýÒÀÀµÓÚY C£®YÍêÈ«º¯ÊýÒÀÀµÓÚX
D£®Y²¿·Öº¯ÊýÒÀÀµÓÚX
4£®Èç¹ûÓк¯ÊýÒÀÀµX¡úY£¬²¢ÇÒ¶ÔXµÄij¸öÕæ×Ó¼¯X¡¯£¬ÓÐX¡¯¡úY³ÉÁ¢£¬Ôò³Æ B
A£®YÍêÈ«º¯ÊýÒÀÀµÓÚX B£®Y²¿·Öº¯ÊýÒÀÀµÓÚX C£®XÍêÈ«º¯ÊýÒÀÀµÓÚY
D£®X²¿·Öº¯ÊýÒÀÀµÓÚY
5£®ÈôX¡úYºÍY¡úZÔÚ¹ØÏµÄ£Ê½RÉϳÉÁ¢£¬ÔòX¡úZÔÚRÉÏÒ²³ÉÁ¢¡£¸ÃÍÆÀí¹æÔò³ÆÎª A£®×Ô·´¹æÔò B£®Ôö¹ã¹æÔò C£®´«µÝ¹æÔò
D£®Î±´«µÝ¹æÔò
. word×ÊÁϿɱ༠.
C
. . . .
6£®Èô¹ØÏµÄ£Ê½RÖÐÊôÐÔA½ö³öÏÖÔÚº¯ÊýÒÀÀµµÄ×󲿣¬ÔòAΪ A
A£®LÀàÊôÐÔ C£®NÀàÊôÐÔ
B£®RÀàÊôÐÔ D£®LRÀàÊôÐÔ
7£®Èô¹ØÏµÄ£Ê½RÖÐÊôÐÔAÊÇNÀàÊôÐÔ£¬ÔòA D
A£®Ò»¶¨²»°üº¬ÔÚRÈκκòÑ¡ÂëÖÐ B£®¿ÉÄܰüº¬Ò²¿ÉÄܲ»°üº¬ÔÚRµÄºòÑ¡ÂëÖÐ C£®Ò»¶¨°üº¬ÔÚRµÄij¸öºòÑ¡ÂëÖÐ D£®Ò»¶¨°üº¬ÔÚRµÄÈκκòÑ¡ÂëÖÐ
8£®ÉèFÊÇij¹ØÏµÄ£Ê½µÄ¼«Ð¡º¯ÊýÒÀÀµ¼¯¡£ÏÂÁйØÓÚFµÄ˵·¨£¬´íÎóµÄÊÇ
A£®FÖÐÿ¸öº¯ÊýÒÀÀµµÄÓÒ²¿¶¼±ØÐëÊǵ¥¸öÊôÐÔ B£®FÖÐÿ¸öº¯ÊýÒÀÀµµÄ×󲿶¼±ØÐëÊǵ¥¸öÊôÐÔ C£®FÖв»ÄÜÓÐÈßÓàµÄº¯ÊýÒÀÀµ
D£®FÖÐÿ¸öº¯ÊýÒÀÀµµÄ×󲿲»ÄÜÓÐÈßÓàÊôÐÔ
9£®ÓйØÏµÄ£Ê½£ºÑ§Éú£¨Ñ§ºÅ£¬ÐÕÃû£¬ËùÔÚϵ£¬ÏµÖ÷ÈΣ©£¬ÉèÒ»¸öϵֻÓÐÒ»¸öϵÖ÷ÈΣ¬Ôò¸Ã¹ØÏµÄ£Ê½ÖÁÉÙÊôÓÚ B
A£®µÚÒ»·¶Ê½ C£®µÚÈý·¶Ê½
B£®µÚ¶þ·¶Ê½ D£®BC·¶Ê½
D
B
10£®ÉèÓйØÏµÄ£Ê½R(X, Y, Z)£¬ÆäF={Y¡úZ, Y¡úX, X¡úYZ}£¬Ôò¸Ã¹ØÏµÄ£Ê½ÖÁÉÙÊôÓÚ
A£®µÚÒ»·¶Ê½ C£®µÚÈý·¶Ê½
B£®µÚ¶þ·¶Ê½ D£®BC·¶Ê½
D
11£®ÏÂÁйØÓÚ¹ØÏµÄ£Ê½Ó뷶ʽµÄ˵·¨£¬´íÎóµÄÊÇ
. word×ÊÁϿɱ༠.