·¢²¼Ê±¼ä : ÐÇÆÚÈý ÎÄÕÂÊý¾Ý¿âÔÀí¼°Ó¦Ó㨵ڶþ°æ£©ÈËÃñÓʵç³ö°æÉç³ö°æ - Ï°Ìâ²Î¿¼´ð°¸¸üÐÂÍê±Ï¿ªÊ¼ÔĶÁ
¡¤17¡¤
µÚ5Õ ÊÓͼºÍË÷Òý
Ò»£® Ñ¡ÔñÌâ
1£®ÏÂÁйØÓÚÊÓͼµÄ˵·¨£¬ÕýÈ·µÄÊÇ B
A£®ÊÓͼÓë»ù±¾±íÒ»Ñù£¬Ò²´æ´¢Êý¾Ý
B£®¶ÔÊÓͼµÄ²Ù×÷×îÖÕ¶¼×ª»»Îª¶Ô»ù±¾±íµÄ²Ù×÷ C£®ÊÓͼµÄÊý¾ÝÔ´Ö»ÄÜÊÇ»ù±¾±í
D£®ËùÓÐÊÓͼ¶¼¿ÉÒÔʵÏÖ¶ÔÊý¾ÝµÄÔö¡¢É¾¡¢¸Ä¡¢²é²Ù×÷ 2£®ÔÚÊÓͼµÄ¶¨ÒåÓï¾äÖУ¬Ö»ÄÜ°üº¬
A£®Êý¾Ý²éѯÓï¾ä C£®´´½¨±íµÄÓï¾ä
A
B£®Êý¾ÝÔö¡¢É¾¡¢¸ÄÓï¾ä D£®È«²¿¶¼¿ÉÒÔ
3£®ÊÓͼ¶ÔÓ¦Êý¾Ý¿âÈý¼¶Ä£Ê½ÖеÄ_________¡£
A. Íâģʽ B. ÄÚģʽ C. ģʽ
D. ÆäËû
4£®ÏÂÁйØÓÚͨ¹ýÊÓͼ¸üÐÂÊý¾ÝµÄ˵·¨£¬´íÎóµÄÊÇ A
A£®Èç¹ûÊÓͼµÄ¶¨ÒåÉæ¼°µ½¶àÕÅ±í£¬Ôò¶ÔÕâÖÖÊÓͼһ°ãÇé¿öÏÂÔÊÐí½øÐиüвÙ×÷
B£®Èç¹û¶¨ÒåÊÓͼµÄ²éѯÓï¾äÖк¬ÓÐGROUP BY×Ӿ䣬Ôò¶ÔÕâÖÖÊÓͼ²»ÔÊÐí½øÐиüвÙ×÷ C£®Èç¹û¶¨ÒåÊÓͼµÄ²éѯÓï¾äÖк¬ÓÐͳ¼Æº¯Êý£¬Ôò¶ÔÕâÖÖÊÓͼ²»ÔÊÐí½øÐиüвÙ×÷
D£®Èç¹ûÊÓͼÊý¾ÝÀ´×Ôµ¥¸ö»ù±¾±íµÄÐС¢ÁÐÑ¡Ôñ½á¹û£¬ÔòÒ»°ãÇé¿öÏÂÔÊÐí½øÐиüвÙ×÷ 5£®ÏÂÁйØÓÚÊÓͼµÄ˵·¨£¬ÕýÈ·µÄÊÇ B
A£®Í¨¹ýÊÓͼ¿ÉÒÔÌá¸ßÊý¾Ý²éѯЧÂÊ B£®ÊÓͼÌṩÁËÊý¾ÝµÄÂß¼¶ÀÁ¢ÐÔ C£®ÊÓͼֻÄܽ¨Á¢ÔÚ»ù±¾±íÉÏ
D£®¶¨ÒåÊÓͼµÄÓï¾ä¿ÉÒÔ°üº¬Êý¾Ý¸ü¸ÄÓï¾ä 6£®´´½¨ÊÓͼµÄÖ÷Òª×÷ÓÃÊÇ D
A£®Ìá¸ßÊý¾Ý²éѯЧÂÊ B£®Î¬»¤Êý¾ÝµÄÍêÕûÐÔÔ¼Êø C£®Î¬»¤Êý¾ÝµÄÒ»ÖÂÐÔ D£®ÌṩÓû§ÊӽǵÄÊý¾Ý
7£®½¨Á¢Ë÷Òý¿ÉÒÔ¼Ó¿ìÊý¾ÝµÄ²éѯЧÂÊ¡£ÔÚÊý¾Ý¿âµÄÈý¼¶Ä£Ê½½á¹¹ÖУ¬Ë÷ÒýÊôÓÚ A
A£®ÄÚģʽ B£®Ä£Ê½ C£®Íâģʽ D£®¸ÅÄîģʽ
8£®ÉèÓÐѧÉú±í£¨Ñ§ºÅ£¬ÐÕÃû£¬ËùÔÚϵ£©¡£ÏÂÁн¨Á¢Í³¼Æÿ¸öϵµÄѧÉúÈËÊýµÄÊÓͼÓï¾äÖУ¬ÕýÈ·µÄÊÇ D
A£®CREATE VIEW v1 AS
SELECT ËùÔÚϵ, COUNT(*) FROM ѧÉú±í GROUP BY ËùÔÚϵ B£®CREATE VIEW v1 AS
SELECT ËùÔÚϵ, SUM(*) FROM ѧÉú±í GROUP BY ËùÔÚϵ C£®CREATE VIEW v1(ϵÃû,ÈËÊý) AS
SELECT ËùÔÚϵ, SUM(*) FROM ѧÉú±í GROUP BY ËùÔÚϵ
17
¡¤18¡¤ D£®CREATE VIEW v1(ϵÃû,ÈËÊý) AS
SELECT ËùÔÚϵ, COUNT(*) FROM ѧÉú±í GROUP BY ËùÔÚϵ 9£®ÉèÓû§ÔÚijÊý¾Ý¿âÖо³£ÐèÒª½øÐÐÈçϲéѯ²Ù×÷£º A
SELECT * FROM T WHERE C1=¡¯A¡¯ ORDER BY C2
ÉèT±íÖÐÒÑÔÚC1ÁÐÉϽ¨Á¢ÁËÖ÷ÂëÔ¼Êø£¬ÇҸñíÖ»½¨ÓиÃÔ¼Êø¡£ÎªÌá¸ß¸Ã²éѯµÄÖ´ÐÐЧÂÊ£¬ÏÂÁз½·¨ÖпÉÐеÄÊÇ C
A£®ÔÚC1ÁÐÉϽ¨Á¢Ò»¸ö¾Û¼¯Ë÷Òý£¬ÔÚC2ÁÐÉϽ¨Á¢Ò»¸ö·Ç¾Û¼¯Ë÷Òý B£®ÔÚC1ºÍC2ÁÐÉÏ·Ö±ð½¨Á¢Ò»¸ö·Ç¾Û¼¯Ë÷Òý C£®ÔÚC2ÁÐÉϽ¨Á¢Ò»¸ö·Ç¾Û¼¯Ë÷Òý
D£®ÔÚC1ºÍC2ÁÐÉϽ¨Á¢Ò»¸ö×éºÏµÄ·Ç¾Û¼¯Ë÷Òý
10£®ÏÂÁйØÓÚË÷ÒýµÄ˵·¨£¬ÕýÈ·µÄÊÇ C
A£®Ö»Òª½¨Á¢ÁËË÷Òý¾Í¿ÉÒÔ¼Ó¿ìÊý¾ÝµÄ²éѯЧÂÊ
B£®µ±Ò»¸ö±íÉÏÐèÒª´´½¨¾Û¼¯ºÍ·Ç¾Û¼¯Ë÷Òýʱ£¬Ó¦¸ÃÏÈ´´½¨·Ç¾Û¼¯Ë÷Òý£¬È»ºóÔÙ´´½¨¾Û¼¯Ë÷Òý£¬ÕâÖÖ˳Ðò»áʹ´´½¨Ë÷ÒýµÄЧÂʱȽϸß
C£®ÔÚÒ»¸ö±íÉÏ¿ÉÒÔ½¨Á¢¶à¸öΨһµÄ·Ç¾Û¼¯Ë÷Òý
D£®Ë÷Òý»áÓ°ÏìÊý¾Ý²åÈëºÍ¸üÐÂÊý¾ÝµÄÖ´ÐÐЧÂÊ£¬µ«²»»áÓ°Ïìɾ³ýÊý¾ÝµÄÖ´ÐÐЧÂÊ 11£®ÏÂÁйØÓÚCREATE UNIQUE INDEX IDX1 ON T(C1,C2)Óï¾ä×÷ÓõÄ˵·¨£¬ÕýÈ·µÄÊÇ D
A£®ÔÚC1ºÍC2ÁÐÉÏ·Ö±ð½¨Á¢Ò»¸öΨһ¾Û¼¯Ë÷Òý B£®ÔÚC1ºÍC2ÁÐÉÏ·Ö±ð½¨Á¢Ò»¸öΨһ·Ç¾Û¼¯Ë÷Òý C£®ÔÚC1ºÍC2ÁеÄ×éºÏÉϽ¨Á¢Ò»¸öΨһ¾Û¼¯Ë÷Òý D£®ÔÚC1ºÍC2ÁеÄ×éºÏÉϽ¨Á¢Ò»¸öΨһ·Ç¾Û¼¯Ë÷Òý
¶þ£® Ìî¿ÕÌâ
1£®¶ÔÊÓͼµÄ²Ù×÷×îÖÕ¶¼×ª»»Îª¶Ô_____²Ù×÷¡£ »ù±¾±í
2£®ÊÓͼÊÇÐé±í£¬ÔÚÊý¾Ý¿âÖÐÖ»´æ´¢ÊÓͼµÄ_____£¬²»´æ´¢ÊÓͼµÄÊý¾Ý¡£ ¶¨Òå 3£®ÐÞ¸ÄÊÓͼ¶¨ÒåµÄÓï¾äÊÇ_____¡£ ALTER VIEW 4£®ÊÓͼ¶ÔÓ¦Êý¾Ý¿âÈý¼¶Ä£Ê½ÖеÄ_____ģʽ¡£ Íâ
5£®ÔÚÒ»¸ö±íÉÏ×î¶à¿ÉÒÔ½¨Á¢_____¸ö¾Û¼¯Ë÷Òý£¬¿ÉÒÔ½¨Á¢_____¸ö·Ç¾Û¼¯Ë÷Òý¡£ Ò» ,¶à
6£®µ±ÔÚT±íµÄC1ÁÐÉϽ¨Á¢¾Û¼¯Ë÷Òýºó£¬Êý¾Ý¿â¹ÜÀíϵͳ»á½«T±íÊý¾Ý°´ _____ ÁнøÐÐ _____¡£ C1 ÎïÀíÅÅÐò
7£®Ë÷Òý½¨Á¢µÄºÏÊÊ£¬¿ÉÒÔ¼Ó¿ìÊý¾Ý_____²Ù×÷µÄÖ´ÐÐЧÂÊ¡£ ²éѯ
8£®ÔÚemployees±íµÄphoneÁÐÉϽ¨Á¢Ò»¸ö·Ç¾Û¼¯Ë÷ÒýµÄSQLÓï¾äÊÇ_____
CREATE INDEX IDX1 ON employees(phone) »ò£ºCREATE NONCLUSTERED INDEX IDX1 ON employees(phone)
9£®ÉèÓÐstudent±í£¬½á¹¹ÎªStudent(Sno,Sname,Sdept)¡£ÏÖÒªÔڸñíÉϽ¨Á¢Ò»¸öͳ¼Æÿ¸öϵµÄѧÉúÈËÊýµÄÊÓͼ£¬ÊÓͼÃûΪV_dept£¬ÊÓͼ½á¹¹Îª£¨ÏµÃû,ÈËÊý£©¡£Ç벹ȫÏÂÁж¨Òå¸ÃÊÓͼµÄSQLÓï¾ä V_dept (ϵÃû,ÈËÊý) , GROUP BY Sdept CREATE VIEW _____ AS
SELECT Sdept, COUNT(*) _____ 10£®·Ç¾Û¼¯Ë÷ÒýµÄB-Ê÷ÖУ¬Ò¶¼¶½ÚµãÖÐÿ¸öË÷ÒýÐÐÓÉË÷Òý¼üÖµºÍ_____×é³É¡£ ÐÐָʾÆ÷
18
¡¤19¡¤
Èý£® ¼ò´ðÌâ
1. ÊÔ˵Ã÷ʹÓÃÊÓͼµÄºÃ´¦¡£
´ð£ºÀûÓÃÊÓͼ¿ÉÒÔ¼ò»¯¿Í»§¶ËµÄÊý¾Ý²éѯÓï¾ä£¬Ê¹Óû§ÄÜ´Ó¶à½Ç¶È¿´´ýͬһÊý¾Ý£¬¿ÉÒÔÌá¸ßÊý¾ÝµÄ°²È«ÐÔ£¬ÊÓͼ¶ÔÓ¦Êý¾Ý¿âÈý¼¶Ä£Ê½ÖеÄÍâģʽ£¬Òò´ËÌṩÁËÒ»¶¨³Ì¶ÈµÄÂß¼¶ÀÁ¢ÐÔ¡£
2. ÊÔ˵Ã÷ÄÄÀàÊÓͼ¿ÉʵÏÖ¸üÐÂÊý¾ÝµÄ²Ù×÷£¬ÄÄÀàÊÓͼ²»¿ÉʵÏÖ¸üÐÂÊý¾ÝµÄ²Ù×÷¡£
´ð£ºÒ»°ãÖ»Éæ¼°µ½Ò»ÕűíµÄÐÐÁÐ×Ó¼¯£¬ÇÒ²»º¬·Ö×顢ͳ¼Æ¼ÆËãµÈ²Ù×÷µÄÊÓͼÊǿɸüÐÂÊý¾ÝµÄ£¬ÆäËûÇé¿öÒ»°ã¶¼²»ÄÜʵÏÖÊý¾ÝµÄ¸üвÙ×÷¡£
3. ʹÓÃÊÓͼ¿ÉÒÔ¼Ó¿ìÊý¾ÝµÄ²éѯËٶȣ¬Õâ¾ä»°¶ÔÂð£¿ÎªÊ²Ã´£¿
´ð£º²»¶Ô£¬ÒòΪͨ¹ýÊÓͼ²éѯÊý¾Ýʱ£¬±ÈÖ±½ÓÕë¶Ô»ù±¾±í²éѯÊý¾Ý¶àÁËÒ»¸öת»»¹ý³Ì£¬¼´´ÓÍâģʽµ½Ä£Ê½µÄת»»¡£
4. Ë÷ÒýµÄ×÷ÓÃÊÇʲô£¿
´ð£ºË÷Òý¿ÉÒÔ¼Ó¿ìÊý¾ÝµÄ²éѯЧÂÊ¡£
5. Ë÷Òý·ÖΪÄļ¸ÖÖÀàÐÍ£¿·Ö±ðÊÇʲô£¿ËüÃǵÄÖ÷ÒªÇø±ðÊÇʲô
´ð£º·ÖΪ¾Û¼¯Ë÷ÒýºÍ·Ç¾Û¼¯Ë÷ÒýÁ½ÖÖ¡£¾Û¼¯Ë÷Òý»á¶ÔÊý¾Ý½øÐÐÎïÀíÅÅÐò£¬·Ç¾Û¼¯Ë÷Òý²»¶ÔÊý¾Ý½øÐÐÎïÀíÅÅÐò¡£
6. ¾Û¼¯Ë÷ÒýÒ»¶¨ÊÇΨһÐÔË÷Òý£¬¶ÔÂ𣿷´Ö®ÄØ£¿ ´ð£º²»¶Ô¡£·´Ö®Ò²²»¶Ô¡£
7. ÔÚ½¨Á¢¾Û¼¯Ë÷Òýʱ£¬Êý¾Ý¿â¹ÜÀíϵͳÊ×ÏÈÒª½«Êý¾Ý°´¾Û¼¯Ë÷ÒýÁнøÐÐÎïÀíÅÅÐò¡£¶ÔÂ𣿠´ð£º¶Ô¡£
8. ÔÚ½¨Á¢·Ç¾Û¼¯Ë÷Òýʱ£¬Êý¾Ý¿â¹ÜÀíϵͳ²¢²»¶ÔÊý¾Ý½øÐÐÎïÀíÅÅÐò¡£¶ÔÂ𣿠´ð£º¶Ô¡£
9. ²»¹Ü¶Ô±í½øÐÐʲôÀàÐ͵IJÙ×÷£¬ÔÚ±íÉϽ¨Á¢µÄË÷ÒýÔ½¶àÔ½ÄÜÌá¸ß²Ù×÷ЧÂÊ¡£¶ÔÂ𣿠10. ´ð£º²»¶Ô¡£
11. ÊʺϽ¨Á¢Ë÷ÒýµÄÁÐÊÇʲô£¿ ´ð£ºÊʺϽ¨Á¢Ë÷ÒýµÄÇé¿ö£º
? °üº¬´óÁ¿·ÇÖظ´ÖµµÄÁС£
? ÔÚWHERE×Ó¾äÖо³£ÓÃÓÚ½øÐÐBETWEEN AND¡¢>¡¢>=¡¢< ºÍ <=µÈ²Ù×÷µÄÁС£ ? ¾³£±»ÓÃ×÷Á¬½Ó²Ù×÷µÄÁС£
? ORDER BY»òGROUP BY×Ó¾äÖÐÉæ¼°µÄÁС£
ËÄ£®ÉÏ»úÁ·Ï°
1£® д³ö´´½¨Âú×ãÏÂÊöÒªÇóµÄÊÓͼµÄSQLÓï¾ä£¬²¢Ö´ÐÐÕâЩÓï¾ä¡£½«ËùдÓï¾ä±£´æµ½Ò»¸öÎļþÖС£ £¨1£© ²éѯѧÉúµÄѧºÅ¡¢ÐÕÃû¡¢ËùÔÚϵ¡¢¿Î³ÌºÅ¡¢¿Î³ÌÃû¡¢¿Î³Ìѧ·Ö¡£
19
¡¤20¡¤ ´ð£ºCreate view v1 As
Select s.sno,sname,sdept,c.cno,cname,credit From student s join sc on s.sno = sc.sno Join course c on c.cno = sc.cno
£¨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·ÖµÄѧÉúµÄÐÕÃû¡¢¿Î³ÌÃûºÍ³É¼¨¡£
´ð£º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
20