Êý¾Ý¿â¿ÎºóϰÌâ²Î¿¼´ð°¸ ÏÂÔØ±¾ÎÄ

. . . .

£¨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×ÊÁϿɱ༭ .