Êý¾Ý¿â¿ÎºóÏ°Ìâ²Î¿¼´ð°¸½âÎö

ÍêÃÀWORD¸ñʽ

µÚ3Õ SQLÓïÑÔ»ù´¡¼°Êý¾Ý¶¨Ò幦ÄÜ

Ò»£®Ñ¡ÔñÌâ

1£®ÏÂÁйØÓÚSQLÓïÑÔÌض¨µÄÐðÊö£¬´íÎóµÄÊÇ B

A£®Ê¹ÓÃSQLÓïÑÔ·ÃÎÊÊý¾Ý¿â£¬Óû§Ö»ÐèÌá³ö×öʲô£¬¶øÎÞÐèÃèÊöÈçºÎʵÏÖ B£®SQLÓïÑԱȽϸ´ÔÓ£¬Òò´ËÔÚʹÓÃÉϱȽÏÄÑ

C£®SQLÓïÑÔ¿ÉÒÔÔÚÊý¾Ý¿â¹ÜÀíϵͳÌṩµÄÓ¦ÓóÌÐòÖÐÖ´ÐУ¬Ò²¿ÉÒÔÔÚDOS»·¾³ÏÂÖ´ÐÐ D£®Ê¹ÓÃSQLÓïÑÔ¿ÉÒÔÍê³ÉÈκÎÊý¾Ý¿â²Ù×÷ 2£®ÏÂÁÐËùÊö¹¦ÄÜÖУ¬²»ÊôÓÚSQLÓïÑÔ¹¦ÄܵÄÊÇ D

A£®Êý¾Ý¿âºÍ±íµÄ¶¨Ò幦ÄÜ B£®Êý¾Ý²éѯ¹¦ÄÜ C£®Êý¾ÝÔö¡¢É¾¡¢¸Ä¹¦ÄÜ D£®Ìṩ·½±ãµÄÓû§²Ù×÷½çÃ湦ÄÜ 3£®Éèijְ¹¤±íÖÐÓÐÓÃÓÚ´æ·ÅÄêÁ䣨ÕûÊý£©µÄÁУ¬ÏÂÁÐÀàÐÍÖÐ×îºÏÊÊÄêÁäÁеÄÊÇ C A£®int B£®smallint C£®tinyint D£®bit 4£®SQL ServerÊý¾Ý¿âÊÇÓÉÎļþ×é³ÉµÄ¡£ÏÂÁйØÓÚÊý¾Ý¿âËù°üº¬µÄÎļþµÄ˵·¨£¬ÕýÈ·µÄÊÇ D A£®Ò»¸öÊý¾Ý¿â¿É°üº¬¶à¸öÖ÷Êý¾ÝÎļþºÍ¶à¸öÈÕÖ¾Îļþ B£®Ò»¸öÊý¾Ý¿âÖ»ÄÜ°üº¬Ò»¸öÖ÷Êý¾ÝÎļþºÍÒ»¸öÈÕÖ¾Îļþ

C£®Ò»¸öÊý¾Ý¿â¿É°üº¬¶à¸ö´ÎÒªÊý¾ÝÎļþ£¬µ«Ö»ÄÜ°üº¬Ò»¸öÈÕÖ¾Îļþ D£®Ò»¸öÊý¾Ý¿â¿É°üº¬¶à¸ö´ÎÒªÊý¾ÝÎļþºÍ¶à¸öÈÕÖ¾Îļþ 5£®ÔÚSQL ServerÖд´½¨Óû§Êý¾Ý¿â£¬ÆäÖ÷Êý¾ÝÎļþµÄ´óС±ØÐë´óÓÚ B A£®masterÊý¾Ý¿âµÄ´óС B£®modelÊý¾Ý¿âµÄ´óС C£®msdbÊý¾Ý¿âµÄ´óС D£®3MB 6£®ÔÚSQL ServerϵͳÊý¾Ý¿âÖУ¬´æ·ÅÓû§Êý¾Ý¿â¹«¹²ÐÅÏ¢µÄÊÇ D A£®master B£®model C£®msdbÊý¾Ý¿âµÄ´óС D£®tempdb

7£®ÔÚSQL ServerÖд´½¨Óû§Êý¾Ý¿âʵ¼Ê¾ÍÊǶ¨ÒåÊý¾Ý¿âËù°üº¬µÄÎļþÒÔ¼°ÎļþµÄÊôÐÔ¡£ÏÂÁв»ÊôÓÚÊý¾Ý¿âÎļþÊôÐÔµÄÊÇ C A£®³õʼ´óС B£®ÎïÀíÎļþÃû C£®Îļþ½á¹¹ D£® ×î´ó´óС 8£®ÔÚSQL ServerÖд´½¨Óû§Êý¾Ý¿âʵ¼Ê¾ÍÊǶ¨ÒåÊý¾Ý¿âËù°üº¬µÄÎļþÒÔ¼°ÎļþµÄÊôÐÔ¡£ÏÂÁв»ÊôÓÚÊý¾Ý¿âÎļþÊôÐÔµÄÊÇ C A£®³õʼ´óС B£®ÎïÀíÎļþÃû C£®Îļþ½á¹¹ D£® ×î´ó´óС 9£®ÏÂÁÐÔ¼ÊøÖÐÓÃÓÚÏÞÖÆÁеÄÈ¡Öµ·¶Î§µÄÔ¼ÊøÊÇ B A£®PRIMARY KEY B£®CHECK C£®DEFAULT D£® UNIQUE 10£®ÏÂÁÐÔ¼ÊøÖÐÓÃÓÚÏÞÖÆÁеÄÈ¡Öµ²»ÖصÄÔ¼ÊøÊÇ D

רҵÕûÀí·ÖÏí

ÍêÃÀWORD¸ñʽ

A£®PRIMARY KEY C£®DEFAULT

B£®CHECK

D£® UNIQUE

11£®ÏÂÁÐÔ¼ÊøÖÐÓÃÓÚʵÏÖʵÌåÍêÕûÐÔµÄÔ¼ÊøÊÇ A A£®PRIMARY KEY B£®CHECK C£®DEFAULT D£® UNIQUE

12£®ÏÂÁйØÓÚDEFAULTÔ¼ÊøµÄ˵·¨£¬´íÎóµÄÊÇ D A£®Ò»¸öDEFAULTÔ¼ÊøÖ»ÄÜÔ¼Êø±íÖеÄÒ»¸öÁÐ B£®ÔÚÒ»¸ö±íÉÏ¿ÉÒÔ¶¨Òå¶à¸öDEFAULTÔ¼Êø C£®DEFAULTÖ»Äܶ¨ÒåÔÚÁм¶ÍêÕûÐÔÔ¼Êø´¦

D£®ÔÚÁм¶ÍêÕûÐÔÔ¼ÊøºÍ±í¼¶ÍêÕûÐÔÔ¼Êø´¦¶¼¿ÉÒÔ¶¨ÒåDEFAULTÔ¼Êø

רҵÕûÀí·ÖÏí

ÍêÃÀWORD¸ñʽ

µÚ 4 Õ Êý¾Ý²Ù×÷Óï¾ä

Ò»£® Ñ¡ÔñÌâ

1£® µ±¹ØϵRºÍS½øÐÐÁ¬½Ó²Ù×÷ʱ£¬Èç¹ûRÖеÄÔª×é²»Âú×ãÁ¬½ÓÌõ¼þ£¬ÔÚÁ¬½Ó½á¹ûÖÐÒ²»á½«ÕâЩ¼Ç

¼±£ÁôÏÂÀ´µÄ²Ù×÷ÊÇ A A£®×óÍâÁ¬½Ó B£®ÓÒÍâÁ¬½Ó C£®ÄÚÁ¬½Ó D£®×ÔÁ¬½Ó

2£® ÉèÔÚijSELECTÓï¾äµÄWHERE×Ó¾äÖУ¬ÐèÒª¶ÔGradeÁеĿÕÖµ½øÐд¦Àí¡£ÏÂÁйØÓÚ¿ÕÖµµÄ²Ù

×÷£¬´íÎóµÄÊÇ C

A£®Grade IS NOT NULL B£®Grade IS NULL C£®Grade = NULL D£®NOT (Grade IS NULL) 3£® ÏÂÁоۺϺ¯ÊýÖУ¬²»ºöÂÔ¿ÕÖµµÄÊÇ A£®SUM(ÁÐÃû) C£®AVG(ÁÐÃû)

D

B£®MAX(ÁÐÃû) D£®COUNT(*)

A

4£®SELECT ¡­ INTO ¡­FROMÓï¾äµÄ¹¦ÄÜÊÇ

A£® ½«²éѯ½á¹û²åÈëµ½Ò»¸öбíÖÐ B£® ½«²éѯ½á¹û²åÈëµ½Ò»¸öÒѽ¨ºÃµÄ±íÖÐ C£® ºÏ²¢²éѯµÄ½á¹û

D£® ÏòÒÑ´æÔڵıíÖÐÌí¼ÓÊý¾Ý

5£®ÏÂÁÐÀûÓÃSC±íµÄ²éѯÓï¾äÖУ¬´íÎóµÄÊÇ B

A£® SELECT Sno, COUNT(*) FROM SC GROUP BY Sno B£® SELECT Sno FROM SC GROUP BY Sno WHERE COUNT(*) > 3 C£® SELECT Sno FROM SC GROUP BY Sno HAVING COUNT(*) > 3 D£® SELECT Sno FROM SC GROUP BY Sno

6£®ÏÖÒªÀûÓÃStudent±í²éѯÄêÁä×îСµÄѧÉúÐÕÃûºÍÄêÁä¡£ÏÂÁÐʵÏִ˹¦ÄܵIJéѯÓï¾äÖУ¬ÕýÈ·µÄÊÇ D

A£® SELECT Sname, MIN(Sage) FROM Student B£® SELECT Sname, Sage FROM Student WHERE Sage = MIN(Sage) C£® SELECT TOP 1 Sname, Sage FROM Student

D£® SELECT TOP 1 Sname, Sage FROM Student ORDER BY Sage 7£®ÉèSC±íÖмǼ³É¼¨µÄÁÐΪ£ºGrade£¬ÀàÐÍΪint¡£ÈôÔÚ²éѯ³É¼¨Ê±£¬Ï£Íû½«³É¼¨°´¡®ÓÅ¡¯¡¢¡®Á¼¡¯¡¢¡®ÖС¯¡¢¡®¼°¸ñ¡¯ºÍ¡®²»¼°¸ñ¡¯ÐÎʽÏÔʾ£¬ÕýÈ·µÄCaseº¯ÊýÊÇ C

A. Case Grade

When 90~100 THEN 'ÓÅ' When 80~89 THEN 'Á¼' When 70~79 THEN 'ÖÐ' When 60~69 THEN '¼°¸ñ' Else '²»¼°¸ñ' End B. Case

רҵÕûÀí·ÖÏí

ÍêÃÀWORD¸ñʽ

When Grade between 90 and 100 THEN Grade = 'ÓÅ' When Grade between 80 and 89 THEN Grade = 'Á¼' When Grade between 70 and 79 THEN Grade = 'ÖÐ' When Grade between 60 and 69 THEN Grade = '¼°¸ñ' Else Grade = '²»¼°¸ñ' End C. Case

When Grade between 90 and 100 THEN 'ÓÅ' When Grade between 80 and 89 THEN 'Á¼' When Grade between 70 and 79 THEN 'ÖÐ' When Grade between 60 and 69 THEN '¼°¸ñ' Else '²»¼°¸ñ' End

D. Case Grade

When 90~100 THEN Grade = 'ÓÅ' When 80~89 THEN Grade = 'Á¼' When 70~79 THEN Grade = 'ÖÐ' When 60~69 THEN Grade = '¼°¸ñ' Else Grade = '²»¼°¸ñ'

End

8£®ÏÂÊöÓï¾äµÄ¹¦ÄÜÊǽ«Á½¸ö²éѯ½á¹ûºÏ²¢ÎªÒ»¸ö½á¹û¡£ÆäÖÐÕýÈ·µÄÊÇ B

A£®select sno,sname,sage from student where sdept = 'cs' Order by sage Union

select sno,sname,sage from student where sdept = 'is' Order by sage B£®select sno,sname,sage from student where sdept = 'cs' Union

select sno,sname,sage from student where sdept = 'is' Order by sage C£®select sno,sname,sage from student where sdept = 'cs' Union

select sno,sname from student where sdept = 'is' Order by sage D£®select sno,sname,sage from student where sdept = 'cs' Order by sage Union

select sno,sname,sage from student where sdept = 'is' 9£®ÏÂÁÐSQLÓï¾äÖУ¬ÓÃÓÚÐ޸ıíÊý¾ÝµÄÓï¾äÊÇ C

A£®ALTER B£®SELECT C£®UPDATE D£®INSERT 10£®ÉèÓÐTeachers±í£¬¸Ã±íµÄ¶¨ÒåÈçÏ£º CREATE TABLE Teachers(

רҵÕûÀí·ÖÏí

ÍêÃÀWORD¸ñʽ

Tno CHAR(8) PRIMARY KEY, Tname VARCHAR(10) NOT NULL,

Age TINYINT CHECK(Age BETWEEN 25 AND 65) )

ÏÂÁвåÈëÓï¾äÖУ¬²»ÄÜÕýÈ·Ö´ÐеÄÊÇ D

A£®INSERT INTO Teachers VALUES('T100','Õźè',NULL)

B£®INSERT INTO Teachers(Tno,Tname,Age) VALUES('T100','Õźè',30) C£®INSERT INTO Teachers(Tno,Tname) VALUES('T100','Õźè') D£®INSERT INTO TeachersVALUES('T100','Õźè')

11£®ÉèÊý¾Ý¿âÖÐÒÑÓбí4-1ÖÁ4-3ËùʾµÄStudent¡¢CourseºÍSC±í¡£ÏÖÒª²éѯѧÉúÑ¡µÄµÚ2ѧÆÚ¿ªÉè¿Î³ÌµÄÇé¿ö£¬Ö»ÐèÁгöѧºÅ¡¢ÐÕÃû¡¢ËùÔÚϵºÍËùÑ¡µÄ¿Î³ÌºÅ¡£¸Ã²éѯÉæ¼°µ½µÄ±íÊÇ D

A£®½öStudent±í B£®½öStudentºÍSC±í C£®½öStudentºÍCourse±í D£®Student¡¢SCºÍCourse±í 12£®É¾³ý¼ÆËã»úϵѧÉú£¨ÔÚstudent±íÖУ©µÄÐ޿μǼ£¨ÔÚSC±íÖУ©µÄÕýÈ·µÄÓï¾äÊDZíÊÇ B A£®DELETE FROM SC JOIN Student b ON S.Sno = b.Sno WHERE Sdept = '¼ÆËã»úϵ'

B£®DELETE FROM SC FROM SC JOIN Student b ON SC.Sno = b.Sno WHERE Sdept = '¼ÆËã»úϵ'

C£®DELETE FROM Student WHERE Sdept = '¼ÆËã»úϵ' D£®DELETE FROM SC WHERE Sdept = '¼ÆËã»úϵ'

¶þ£® Ìî¿ÕÌâ

1£® ÔÚÏà¹Ø×Ó²éѯÖУ¬×Ó²éѯµÄÖ´ÐдÎÊýÊÇÓÉ_________¾ö¶¨µÄ¡£Íâ²ã±íµÄÐÐÊý

2£® ¶Ô°üº¬»ùÓÚ¼¯ºÏ²âÊÔ×Ó²éѯµÄ²éѯÓï¾ä£¬ÊÇÏÈÖ´ÐÐ_________²ã²éѯ£¬ÔÚÖ´ÐÐ_________²ã²é

ѯ¡£ÄÚ£¬Íâ

3£® ¶Ô°üº¬Ïà¹Ø×Ó²éѯµÄ²éѯÓï¾ä£¬ÊÇÏÈÖ´ÐÐ_________²ã²éѯ£¬ÔÚÖ´ÐÐ_________²ã²éѯ¡£Í⣬

ÄÚ

4£® ¾ÛºÏº¯ÊýCOUNT(*)ÊÇ°´_________ͳ¼ÆÊý¾Ý¸öÊý¡£ÐÐ

5£® ÉèGradeÁÐÄ¿Ç°ÓÐÈý¸öÖµ£º90¡¢80ºÍNULL£¬ÔòAVG(Grade)µÄÖµÊÇ_________£¬MIN(Grade)

µÄÖµÊÇ_________¡£85£¬80

6£® ÉèÓÐѧÉú±í£¨Ñ§ºÅ£¬ÐÕÃû£¬ËùÔÚϵ£©ºÍÑ¡¿Î±í£¨Ñ§ºÅ£¬¿Î³ÌºÅ£¬³É¼¨£©£¬ÏÖÒª½¨Á¢Í³¼Æÿ¸öϵµÄ

Ñ¡¿ÎÈËÊý¡£Ç벹ȫÏÂÁÐÓï¾ä£º COUNT(DISTINCT Ñ¡¿Î±í.ѧºÅ) SELECT ËùÔÚϵ, _________ FROM Ñ¡¿Î±í JOIN ѧÉú±í ON Ñ¡¿Î±í.ѧºÅ = ѧÉú±í.ѧºÅ GROUP BY ËùÔÚϵ

7£® ÉèÓÐÑ¡¿Î±í£¨Ñ§ºÅ£¬¿Î³ÌºÅ£¬³É¼¨£©£¬ÏÖÒª²éѯ¿¼ÊԳɼ¨×î¸ßµÄÈý¸öѧÉúµÄѧºÅ¡¢¿Î³ÌºÅºÍ³É¼¨£¬

°üÀ¨²¢ÁÐÇé¿ö¡£Ç벹ȫÏÂÁÐÓï¾ä£º TOP 3 WITH TIES, ORDER BY ³É¼¨ DESC SELECT _________ ѧºÅ£¬¿Î³ÌºÅ£¬³É¼¨ FROM Ñ¡¿Î±í _________ 8£® UNION²Ù×÷ÓÃÓںϲ¢¶à¸ö²éѯÓï¾äµÄ½á¹û£¬Èç¹ûÔںϲ¢½á¹ûʱ²»Ï£ÍûÈ¥µôÖظ´µÄÊý¾Ý£¬ÔòÔÚÓÃ

UNION²Ù×÷ʱӦʹÓÃ_________¹Ø¼ü×Ö¡£ ALL

9£® ½øÐÐ×ÔÁ¬½Ó²Ù×÷µÄÁ½¸ö±íÔÚÎïÀíÉÏΪһÕÅ±í¡£Í¨¹ý_________·½·¨¿É½«ÎïÀíÉϵÄÒ»ÕűíÔÚÂß¼­

ÉϳÉΪÁ½ÕÅ±í¡£Æð±ðÃû

10£® FROM A LEFT JOIN B ON ¡­Óï¾ä±íʾÔÚÁ¬½Ó½á¹ûÖв»ÏÞÖÆ_________±íÊý¾Ý±ØÐëÂú×ãÁ¬½Ó

רҵÕûÀí·ÖÏí