2023年新版南邮数据库实验报告

《2023年新版南邮数据库实验报告》由会员上传分享，免费在线阅读，更多相关内容在教育资源-天天文库。

3U
/ᦪ2023/2023ᢣᦟᡂ

1ஹᦪᳮ[-1ᑁ":ᦪ⊤Ḅ&'()ᳮூ௃ஹ,Ḅ-.ᦪ/⊤Ḅ&'ஹᑤ◀ஹ23456஺8-9⚪,;◀<ᱯ>?@;AT-SQLBC;DEᡠᨵHI᪗8K⚪,ᶇNᙠPQR☢T;UVWP஺ூX௃ஹ⚪,/ᐸZ[-1ஹ\&B‘test'Ḅᦪ]So1utionCREATEDATABASEtest2ஹᙠ“test”ᦪ_3&`ab⊤“ab”;dᐭᑡ-name(char,10g);ID(char,7g),manager(char,10g)ᔜᑡᙳklBmn஺So1ution-usetestCREATETABLEab(IDCHAR(7)NOTNULL,nameCHAR(10)NOTNULL,managerCHAR(10)NOTNULL)vw-—yz.
ᡂᡂே஺3ஹᙠ“test”ᦪ_3&`ᕒ}⊤;~B“ᕒ}”஺ᙠ⊤_dᐭAᔜᑡ-name(char,10g);personlD(char,7g),Sex(char,7g);birthday(datetime),deptlD(char,7g)ᑡᙳklBmn஺So1ution-

2CREATETABLEᕒ}(nameCHAR(10)NOTNULL,personlDCHAR(7)NOTNULL,sexCHAR(7)NOTNULL,birthdaydatetimeNOTNULL,deptIDCHAR(7)NOTNULL)vw-yz,_______________
ᡂᡂ஺4ஹᦋ⊤Ḅ56.-1)E‘ab’⊤_ḄᑡIDBC]2)E'ᕒ}'⊤_personlDBC;DEdeptID'B᜜;ᐵὶᑮ'ab'⊤TḄ'ID'ᑡ;3)ᙠ‘ab’⊤_;ᑡquantity(char,5)]4)ᑤ◀'ᕒ}'⊤_Ḅᑡ'sex']5)ᦋ'ᕒ}'⊤_ᑡnameB(varchar,8)So1ution:ALTERTABLEabADDCONSTRAINTC1PRIMARYKEY(ID)ALTERTABLEᕒ}ADDCONSTRAINTC2PRIMARYKEY(personID)ALTERTABLEADDCONSTRAINTC3FOREIGNKEY(deptID)REFERENCESab(ID)ALTERTABLEabADDquantityCHAR(5)ALTERTABLEᕒ}DROPCOLUMNSexALTERTABLEᕒ}ALTERCOLUMNnameVARCHAR(8)vw:

3Ljyz
ᡂᡂ஺5ஹ1)ᙠᦪtest_3&⊤scores,dᐭAᑡ-ID(char,8g)C;CHnumeric(3,1),ITHnumeric(3,l)ᦪnumeric(3,1),Ạnumeric(3l);ᙳᡂ஺bklBm;DᙳᡂBbḄzᙳᑖ]2)B⊤scores_Ḅ⚗ᡂdefault:¡ᐸ¢£nB0]3)B⊤scores_Ḅ⚗ᡂcheck:¤¥⚗ᡂᙠ0ᑮ100¦§஺Solution-CREATETABLEscores(IDCHAR(8)PRIMARYKEYNOTNULL,CHNUMERIC(3,1)NOTNULL,ITHNUMERIC(3,1)NOTNULL,ᦪNUMERIC(3,1)NOTNULL,ẠNUMER}C(3,1)NOTNULL,ᙳᡂAS(CH+}THᓝᦪ+Ạ)/4)ALTERTABLEScoresADDCONSTRAINTcHDEFAULT'O'FORcHALTERTABLEsc0resADDCONSTRAINTITHDEFAULT'O'FORITHALTERTABLESCoresADDCONSTRAINTᦪDEFAULT'01F0RᦪALTERTABLESCoresADDCONSTRAINTẠDEFAULT'0'F஺RẠALTERTABLESCOresADDCONSTRAINTCK1CHECK(CH>=0ANDCH<=100)]ALTERTABLEscoresADDCONSTRAINTCK2CHECK(ITH>=0ANDITH<=100);

4ALTERTABLEScoresADDCONSTRAINTCK3CHECK(ᦪ=0ANDᦪ
=100);ALTERTABLEscoresADDCONSTRAINTCK4CHECK(Ạ°=0ANDẠ
=100)]vw-yz
ᡂᡂ஺6ஹᑤ◀department⊤;Dᑤ◀ᦪtest஺Solution:DROPTableab;DROPDATABASEtest;ூ௃᦮Ḅṹ:CREATEDATABASEtestusetestCREATETABLEab(IDCHAR(7)NOTNULL,nameCHAR(10)NOTNULL,managerCHAR(10)NOTNULL)CREATETABLEᕒ}(nameCHAR(10)NOTNULL,personlDCHAR(7)NOTNULL,sexCHAR(7)NOTNULL,birthdaydatetimeN0TNULL,deptIDCHAR(7)NOTNULL)ALTERTABLEabADDC0NSTRAlNTClPRIMARYKEY(ID)ALTERTABLEᕒ}ADDCONSTRAINTC2PRIMARYKEY(person}D)ALTERTABLEᕒ}ADDCONSTRAINTC3FOREIGNKEY(deptID)REFERENCESab(ID)ALTERTABLEabADDquantityCHAR(5)ALTERTABLEᕒ}DROPCOLUMNsexALTERTABLEᕒ}ALTERCOLUMNnameVARCHAR(8)CREATETABLESCores(IDCHAR(8)PRIMARYKEYNOTNULL,CHNUMERIC(3,1)NOTNULL,}THNUMERIC(3,1)NOTNULL,

5ᦪNUMERIC஺;1NOTNULL,ẠNUMERIC3,1NOTNULL,ᙳᡂASCH+1TH+ᦪ+Ạ/4ALTERTABLESCoresADDCONSTRAINTCHDEFAULT'0ZFORCHALTERTABLEscoresADDCONSTRAINTITHDEFAULT'o'FOR:ETHALTERTABLEscoresADDCONSTRAINTᦪDEFAULT'0'FORᦪALTERTABLEscoresADDCONSTRAINTẠDEFAULT'0'FORẠALTERTABLESCoreSADDCONSTRAlNTCK1CHECKCH>=0ANDCH<=100;ALTERTABLEscoresADDCONSTRAINTCK2CHECKITH>=0ANDITH<=100]ALTERTABLEscOresADDCONSTRAINTCK3CHECKᦪ>=0ANDᦪ<=100]ALTERTABLEScoresADDCONSTRAINTCK4CHECKẠ>=0ANDẠ<=100]DROPTabieab]DROPDATABASEtest;ஹᦪᳮூ௃ஹᑁ"-PQᢈÁḄÂᵨூ௃ஹ,Ḅ-1ஹ஺ÄÅSELECTHIḄ9HÆ2ஹ஺ÄÅÇÈPQḄ⊤É3ஹ஺ÄÅᦪÊËḄUÆ4ஹ஺ÄÅSELECTHIḄGROUPBYÌIḄ6ᵨÍ¡ᵨUÆ5ஹÄÅSELECTHIḄORDERBYÌIḄ6ᵨÍ¡ᵨUÆூX௃ஹᑁ"1ஹ\&ᕒ})ᳮᦪcompy/ÎÂḄ`⊤:

6Employees-ᕒ}Ïz⊤Departments-abÏz⊤Salary-ᕒ}ÐÑÏz⊤Purchase-ᕒ}ÒᱥÏz⊤ᔜ⊤_ÔឋḄ@Ö×᪷⊤_ḄÙÚA@;ᙳAÛBC;Ü᝞×@ÖDepartments⊤v᪀᝞-ᑡᦪßàáâ¤ᔲᐕåmnæçabÛChar3ᔲabÛCabèChar20ᔲabèᜓ8text16¤ᜓ8ᔣ⊤_ëᐭᦪAஹᔣDepartments⊤_ᐭ⊤2ᡠìḄÙÚÛabèᜓ81íîanuII2ïðñòanul13óôanull4õÒanull5├÷anul1So1ution:CREATEDATABASECompyusecompyCREATETABLEEmployees(ÛCHAR(10)NOTNULLPRIMARYKEY,VARCHAR(IO)NOTNULL,ᙢᙬCHAR(10)NOTNULL,úÛCHAR(6),ᵯüCHAR(II)NOTNULL,

7abcHAR(3)NOTNULL,ýóþDATENOTNULL,ឋÿCHAR(2)NOTNULL);CREATETABLEDepartments(CHAR(3)NOTNULLPRIMARYKEY,CHAR(20)NOTNULL,ᜓtext);CREATETABLESa1ary(CHAR(10)NOTNULLPRIMARYkey,ᦈᐭintdefault0,intdefault0);CREATETABLEPurchase(ᕒCHAR(io)NOTNULL,ᖪ!CHAR(4)NOTNULL,"#ᦪ%CHAR(4)NOTNULL,PRIMARYKEY(ᕒ)ᖪ!))*INSERTINTODepartmentsVALUES-11)+,')NULL)*INSERTINTODepartmentsVALUES('2./01',NULL);INSERTINTODepartmentsVALUES()3I)34',NULL):INSERTINTODepartmentsVALUES('4'5"')NULL);INSERTINTODepartmentsVALUES('5;,'├7,)NULL)*BஹᔣEmployees⊤;<ᐭ⊤1ᡠ>Ḅ@ABᙢᙬEᵯG3HIឋKU00000L᧽NOP23003333521956-1112756-1010008Q᪛ᔠT2300313111966-11

827359-1002023U☟ᓅX23002313235911972-116-102YZJ[\230026369311950-002385641-110220L]^^`ia123436351962-015653461-1102208bḝᓭX12345345851955-169651-1108e〉᧎h123445689231969-1—199156111110ᡂᔡklPZ123456345815196410645-1-121067⃫ᯠᡂo12345345431967-1-1865651SolutionINSERTINTOEmployeesVALUES('000001','L᧽N')'OP','230027','3333556','2')'1956-1-1*,'19INSERTINTOEmp1oyeesVALUES()010008','Q᪛I'ᔠT')'230027','3131359,,'1',,1966-1-1'1')INSERTINTOEmployeesVALUES('002023'3U☟I'ᓅX')'230026'/3132359','1','1972-1-1','1')INSERTINTOEmp1oyeesVALUES('020238','Yp')'[\')'230026','3693564'1','1950-1-I'0')INSERTINTOEmpl0yeesVALUES('1022011'L]^I'qa1'123456','3635364:5','1962—1-1',fO')INSERTINTOEmployeesVALUES('102208,.)bḝl'ᓭXI'123456','3458965','5','1955-1-1','1')

9INSERTINTOEmployeesVALUES('108991〉'᧎I'123456','4568921','3','1969-1-1','1')INSERTINTOEmployeesVALUES('1110061'ᡂᔡ'JL'123456','3458145','5','1964-1-1','1')INSERTINTOEmployeesVALUES('210678('⃫ᯠ'Jᡂ,'123456','34545653'3\z1967-1-1','1')*_3_____________________<1!)(1!)<1!)<1!)<1!)CஹᔣSalary⊤&'ᐭ⊤3ᡠ*Ḅ,-ᦈᐭ0000012100123010008158288002023256918502023819877910220120661081022082980210108991325928111100628601982106782347180SolutionINSERTINTOSalaryVALUEs('000001','2100','123')INSERTINTOSalaryVALUES(z010008','1582','88')

10INSERTINT0SalaryVALUES('002023(,,25693'185')INSERTINTOSalaryVALUES('020238','1987',‘79')INSERTINT0Sa1aryVALUES(z102201','2066','108')INSERTINTOSalaryVALuES('102208','2980','210')INSERTINTOSalaryVALUES('108991','3259'28J)INSERTINTOSalaryVALUES('111006','2860','198')INSERTINTOSalaryVALUES('210678',,2347','180')rstrߟvw________________________________<1!)<1!)<1!)<1!)<1!)<1!)<1!)<1!)(1!)DஹᔣPurchase⊤;<ᐭ⊤4ᡠ>Ḅ@Aᕒ/01ᖪ345ᦪ700()001013000001031002023015020238036108991111000001113020238015

11202378021020238112SolutionINSERTINTOPurchaseVALUES('000001','01','3');INSERTINTOPurchaseVALUES('000001','03','1');INSERTINTOPurchaseVALUES/002023','01','50*INSERTINTOPurchaseVALUES('020238'03','6');INSERTINTOPurchaseVALUES('108991','11\*1')*INSERTINTOPurchaseVALUES('000001\'11','3');INSERTINTOPurchaseVALUESy020238I'01','5')*INSERTINTOPurchaseVALUES('202378021');INSERTINTOPurchaseVALUES('020238','11','2')*2ஹzᩎ|}(1)|}1956,1962,1964~3Ḅᕒ@A*(2)|}ឋ1Ḅᕒ@A*(3)|};ᒹ‘b'Ḅᡠᨵᕒw*(4)|}B‘Y‘ᡈὅ‘e’)]ḄᕒḄ@A(r“B”ᑡḄᦪvarchar,)஺Solution:SELECT*FROMEmployeesWHERE3HILIKE'%1956%'or3HILIKE'%1962%'0r3HILIKE'%1964%,;SELECT*FROMEmp1oyeesWHEREឋ='1'rSELECT*FROMEmployeesWHEREBLIKE'b)*SELECT*FROMEmployeesWHEREBn஺tLIKE'^%'

12ANDBnotLIKE)e)ANDBLIKE'__,;strᑖ019:ᙢᙬE0ᵯGHI1JKLMឋO1000001;᧽=B23002733335562195601-0112102201;>?CD123456363536451962010103111006ᡂᔡ123456345814551964-01011019:ᙢᙬE0ᵯGHI1JKLMឋO1000001;᧽=B23002733335561956010112002001P☟ᓅS230026313235911972010113010008T᪛ᔠW230027313135911966010114102208XḝᓭS123456345896551955010115108991〉᧎123456456892131969010116111006ᡂᔡ123456345814551964010117210678⃫ᯠᡂ12345634545653196701011aᙢᙬᵯឋ1[02208jḝᓭ"123456345896551955-01-011ᙢᙬᵯឋ1000001j#᧽%&'230027333355621956010112102201#()*+123456363536451962-01-0103ஹ¡¢|}Ḅ£ᵨপ|}¦ᕒḄ§¨©ªᐸ¬§¨(2)|}ᦈᐭ®¯102201ḄᕒᦈᐭḄ°wS஺1ution:SELECTEmployees.*,Salary.ᦈᐭFROMEmp1oyeesINNERJOINSalaryONEmp1oyees.=Salary.SELECTa.*,b.ᦈᐭFROMEmployeesASaJOINsalaryASbONa.=b.WHEREᦈᐭ²(SELECTᦈᐭFROMSa1aryWHERE=)102201');

13:□J019:ᙢᙬE0ᵯGHI1JKLMឋOᦈᐭ000001^;᧽=B23002733335562195601-01121002002001P☟ᓅS23002631323591197201-01125693010008T᪛ᔠW2300273131359119660101115824020018_`ab2300263693564119500101019875102201;>?CD1234563635364519620101020666102208XḝᓭS12345634589655195501-01129807108991〉᧎1234564568921319690101132598111006ᡂᔡ1234563458145519640101128609210678cᯠᡂ123456345456531967010112347019:ᙢᙬE0ᵯGHI1JKLMឋOᦈᐭ1000)01;;᧽=B2300273333556219560101121002002001e☟ᓅS2300263132359119720101125693102208XḝᓭS1234563458965519550101129804108991〉᧎1234564568921319690101132595111006ᡂᔡ1234563458145519640101128606210678⃫ᯠᡂ12345634545653196701-01123474.ᦪfgh
1ijkHᕒ/ḄlᙳnᦈᐭফijkHpᕒḄhqᦪSolutionrSELECTAVGb.ᦈᐭrb.sJtASjkHᕒ/ḄlᙳnᦈᐭFROMEmployeesASaINNERJOINSalaryASbONa.01=b.01WHERE='l';SELECTCOUNTv01tASjkHpᕒḄhqᦪFROMEmployeesWHERE=)J*□_'jjkHᕒ/Ḅlᙳnᦈᐭ1h928jkHpᕒḄhqᦪI1IM5.GROUPBY,ORDERBYwxḄyᵨ

14(1)³ᔜḄᕒᦪফ¶ᔜᕒḄ§¨ᢥ¬ᵫ¹ᑮ®᣸ᑡSolutionSELECTb.)COUNT(a.)AS.ᦪFROMEmployeesASaJOINDepartmentsASbONa.=b.groupby*SELECTa.*,b.ᦈᐭFROMEmp1oyeesASaINNERJOINSalaryASbON2.=1?.orderbyᦈᐭ*str□{—HI:}HIqᦪ1rẆ.....¼3273213KH24H3∷19:ᙢᙬE0ᵯGHI1JKMឋK“ᦈᐭ1J010008!T᪛ᔠW2300273131359119664)1-01115822020018_`ab23002636935641195001-01019873102201;>?CD123456363536451962-01-01020664000001;᧽=B230027333355621956^)1-01121005210678ₐᯠᡂ123456345456531967-01-01123476002001P☟ᓅS230026313235911972-01-01125696ஹ½⌱¿⚪¼|ÁÂᡠᨵ+,ḄᕒḄᦈᐭo®ḄᕒḄB;Solution:SELECTBFROMEmp1oyeesWHEREIN(SELECTFROMSalary

15WHEREᦈᐭுAl1(SELECTᦈᐭFROMSalaryWHERE01/N(SELECT01FROMEmpl0yeesWHEREHI1='l')))Iߟᒓ9:1.....j23ᡂᔡூ௃᦮Ḅṹ:CREATEDATABASEcompyusecompyCREATETABLEEtnpIOyees(CHAR(IO)NOTNULLPRIMARYKEY,BVARCHAR(IO)NOTNULL,ᙢᙬCHAR(10)NOTNULL,ECHAR(6),ᵯGCHAR(11)NOTNULL,CHAR(3)NOTNULL,3HIDATENOTNULL,ឋcHAR(2)NOTNULL)*CREATETABLEDepartments(CHAR(3)NOTNULLPRIMARYKEY,CHAR(20)NOTNULL,ᜓtext);CREATETABLESalary(CHAR(10)NOTNULLPRIMARYkey,ᦈᐭintdefault0,intdefault0);CREATETABLEPurchase(ᕒCHAR(IO)NOTNULL,ᖪ!CHAR(4)NOTNULL,"#ᦪ%CHAR(4)NOTNULL,PRIMARYKEY(ᕒ)ᖪ!))*INSERTINTODepartmentsVALUES(11z)'+,INULL);

16INSERTINTODePartmentsVALUES(f2'./01,)NULL)*INSERTINTODepartmentsVALUES('3',)34))NULL);INSERTINTODepartmentsVALUES('4','5"')NULL);INSERTINTODePartmentsVALUES('5├7')NULL);INSERTINTOEmpIoyeeSVALUES('000001','L᧽N')'OP')'230027','3333556','2','1956—1-1'1')INSERTINTOEmployeesVALUES('010008','WW','ᔠTl'230027','3131359','111966-1—JJ1')INSERTINTOEmployeesVALUES('002023*.'U☟')'ᓅX')'230026','3132359','1','1972-1-1'1')INSERTINTOEmp1oyeesVALUES(y020238)JYpI)[\I'230026','3693564','1'/1950-1-1','O')INSERTINTOEmployeesVALUES('102201L]^')‘qa')'123456','3635364','5','1962-1-1','O')INSERTINTOEmployeesVALUES-102208','bḝ')'ᓭX')'1234563'3458965','5',(1955-1-1','1')INSERTINTOEmpIOyeesVALUES('108991','e〉))'᧎h')'123456','4568921','3','1969-1-1','1')INSERTINTOEmployeesVALUES('111006','ᡂᔡklI'PZ')'123456','3458145',(5','1964-1-1',,1')INSERTINTOEmployeesVALUES('210678,)'⃫ᯠ')'ᡂo')'123456','3454565,)'3','1967-1-1'1');INSERTINTOSa1aryVALUES('000001f,'2100','123')INSERTINTOSalaryVALUES('010008','1582','88,)INSERTINTOSalaryVALUES002023','2569','185')INSERTINTOSalaryVALUES('020238,/1987',;79')INSERTINTOSalaryVALUES('102201',,2066(,'108/)INSERTINTOSalaryVALUES('102208','2980z,'210')INSERTINTOSalaryVALUES('108991','3259','281')INSERTINTOSalaryVALUES('111006','2860z,,198,)INSERTINTOSa1aryVALUESC210678','2347','180');INSERTINTOPurchaseVALUES('000001','01','3')*INSERTINTOPurchaseVALUES('000001','03','1');INSERTINTOPurchaseVALUES('002023'01'5()*INSERTINTOPurchaseVALUES('020238','03','6')*INSERTINTOPUrchaseVALUES('108991','11;'11);INSERTINTOPurchaseVALUES('000001','11','3')*INSERTINTOPurchaseVALUES(;020238','01','5');INSERTINTOPurchaseVALUES('202378'O2''l/);zINSERTINTOPurehaseVALUES('020238-11','2')*SELECT*FROMEmployeesWHERE3HILIKE'%1956%(or3HILIKE"1962Éor3HILIKE'%1964%,;SELECT*FROMEmployeesWHEREឋ

17SELECT*FROMEmp1oyeesWHEREBLIKE)b'*SELECT*FROMEmployeesWHEREBnotLIKE'Y)ANDBnotLÊ1e)ANDBLIKE)_;SELECTEmp10yees*)Salary.ᦈᐭFROMEmployeesINNERJOINSa1aryONEmployees.=Salary.SELECTa.3b.ᦈᐭFROMEmployeesASaJOINsalaryASbONa.Ìb.WHEREᦈᐭ²½SELECTᦈᐭFROMSalaryWHERE='1022011;SELECTAvGb.ᦈᐭ-b.¼AS+,ᕒḄÍᙳÏᦈᐭFROMEmployeesASaINNERJOINSalaryASbONa.=b.WHERE;SELECTCOUNT½¼AS+,ᕒḄÐ.ᦪFROMEmp1oyeesWHERE='11;SELECTb.)COUNT½a.¼AS.ᦪFROMEmployeesASaJOINDepartmentsASbONa.=b.groupby*SELECTᦈᐭFROMEmployeesASaINNERJOlNSalaryASbONa.=b.orderbyᦈᐭ*SELECTBFROMEmp1oyeesWHEREIN½SELECTFROMSalaryWHEREᦈᐭுAllSELECTᦈᐭFROMSalaryWHEREINSELECTFROMEmployeesWHERE=1]/

18]ஹᦪÒÓᳮÕ]Ö×ØூÚ௃ஹ×ØᑁÝrᦪÒḄ⊤;ᦪḄÞß஺ூÌ௃ஹ×ØàḄr1ஹáâSQLãäåæ⊤;ḄᦪḄçᐭஹèéêᑤ◀*2ஹáíᐹᨵï|}ãäḄᦪèéÞß*2ஹᳮð⊤;ᑡḄñឋò⊤;ᦪḄóô஺ூ]×ØõᜓöᵨOÖ×Ø÷øḄᕒùᳮᦪÒ
compyªúûḄüL⊤½ᐹýþ×Ø2rEmployees:ᕒw⊤Departments:
⊤Salaryᕒ⊤Purchaseᕒᱥ⊤ூ௃ஹ⚪ᐸ1ஹᔣEmployees⊤ᐭᑡ,ᐸ!"“$%&'”)“*+,-”.!/0⏨Ḅ34567ᵨgetdateyear9:,-;ᦪ=ᐹ?7ᵨ@ὃὶCDETransactS

19QL@ὃG஺SoIutionusecompyALTERTABLEEmp1oyeesADDASdatediffyear,*+,-=getdate;2ஹᦪIJK1LMN"102201ḄOᕒᙢᙬᦋ"“ST”U2LMN"102208ḄOᕒᦈᐭWX10%;3Jᦋsalary⊤=Ꮇ᝞ᦈᐭ[\*Ḅ.!]^2023,ᑣLᦈᐭ0`1000;So1utionSELECT*FROMEmployeesUPDATEEmployeesSETᙢᙬ='ST=WHEREMN==102201,SELECT*FROMEmployeesUPDATESalarySETᦈᐭ=ᦈᐭ*0.9WHEREMN='102208'SELECT*FROMSalaryUPDATESalarySETᦈᐭ=ᦈᐭ+1000WHEREabs/ᦈᐭ-\*G
2023;fg:

20A019:ᙢᙬE0ᵯGHI1JKLMឋO110OJ001;᧽=B23002733335562195601011572002001PᓅS23002631323591197201-011413010008T᪛ᔠW23002731313591196601011474020018_`ab230026369356411950-01010635102201;>?ª«12345636353645196201010516102208XḝᓭS12345634589655195501011587108991〉᧎12345645689213196901-011448111006ᡂᔡ12345634581455196401-011499210678⃫ᯠᡂ123456345456531967-01-01146¬19:ᙢᙬE0ᵯGHI1JKLMឋO1000001;᧽=B23002733335562195601-011572002001P☟ᓅS230026313235911972-01d1413010008T᪛ᔠW23002731313591196601-011474020018_`ab23002636935641195001010635102201;>?®⁰n12345636353645196201010516102208XḝᓭS12345634589655195501011587108991〉᧎12345645689213196901011448111006ᡂᔡ…12345634581455196401011499210678⃫ᯠᡂ12345634545653196701011463ஹ7ᵨSELECT-INTOijkjlᐭ:Lm:
ḄMN,no=Oᕒᦪ=lᐭᑮK⊤DepartmentStatic஺SolutionSELECT*FROMSalarySELECTDePartments.
MN=Departments.
no=COUNTEmp1oyees.MNGAS
rᦪINToDePartmentStaticFROMDepartmentsINNERJOINEmployeesonDepartments,
MN=Employees.
NGROUPBY
no=
MNSELECT*FROMDapartmentStatic

21L-J01ᦈᐭsJ1iOOOOO1J310012320020012569185301000825828840200182987795102201306610861022082682210710881325928181110062860198HI01HI:}HIqᦪ1I1ijkH322qÅÆÇH1fg4ஹstuvḄᦪIJK(1)Lᙢᙬ"ᔠxḄOᕒᦈᐭyz5%(2)Lᦈᐭ]^{ᙳᦈᐭḄOᕒḄᦈᐭyz500ᐗ(3)L~ḄOᕒᦈᐭWX100So1ution:UPDATESalarySETᦈᐭ=ᦈᐭ*1.05WHEREMNIN(SELECTMNFROMEmpl0yeesWHEREᙢᙬ==ᔠx=)SELECTEmp1oyees.MN=Employees.tt^,Employees.ᙢᙬ=Salary.ᦈᐭFROMEmployeesINNERJOINSalaryONEmployees.MN=Salary.MNUPDATESalarySETᦈᐭ=ᦈᐭ+500WHEREᦈᐭீ(SELECTAVG(ᦈᐭ)FROMSaLary)SELECT*FROMSalaryUPDATESa1arySETᦈᐭ=ᦈᐭ-100WHEREMNIN(SELECTMNFROMEmp1oyeesWHERE
N=(SELECT
MNFROMDepartmentsWHERE
no==~'))U

22fg:iIq019:ᙢᙬᦈᐭ1jOOOOO11ÈÉ᧽=B31002.002001P☟ᓅS25693010008T᪛ÊᨴÌ27114020018_`ab29875102201ÈÉ>?ª«30666102208XᖆᓭS■2G827108991〉᧎32598111006ᡂᔡ2860:ᡡ1ᦈᐭsJ11OOOOO1I310012320020013OG91855ஹᑤ◀ᑤ◀DᓽartmentStaticᡠᨵᦪISolution:SELECTEmployees.MN=Employees.n,Employees.ᙬ=Salary.ᦈᐭFROMEmployeesINNERJOINSalary0NEmployees.MN=Salary.MNTRUNCATETABLEDepartmentStatic;fg3ÐÑÒT019:ᙢᙬᦈᐭ1jOOOOOI1;᧽=B31002002001P☟ᓅS29693010008T᪛ᔠW31114020018_`ab28875102201ÈÉ>?ª«30666102208XḝᓭS31827108991〉᧎32598111006ᡂᔡ28609210678cᯠᡂ28476ஹ/⌱G
1Lᦈᐭᜧ^6000Ḅ
ḄOᕒᦈᐭWX5%/2ᑤ◀ᦈᐭᜧ^{ᙳᦈᐭḄOᕒḄ=Employees,SalaryᐵḄᦪISolutionSELECT*FROMDepartmentstaticUPDATESalarySETᦈᐭ=ᦈᐭ*0.95

23WHEREMNIN(SELECTMNFROMEmployeesWHERE
N1N(SELECTb.
NFROMSa1aryASaINNERJOINEmployeesASbONa.MN=b.MNGROUPBY
NHAVINGSUM(ᦈᐭ)ு6000))SELECTEmployees.MN=Employees,n=Employces.
N=Salary.ᦈᐭ=Sa1ary.\*FROMEmployeesNNERJOINSalaryONEmployees.MNSalary.MNDELETEFROMEmployeesWHEREMNIN(SELECTMNFROMSa1aryWHEREᦈᐭ(SELECTAVG(ᦈᐭ)FROMSa1ary))DELETEFROMSa1aryWHEREMNIN(SELECTMNFROMSalaryWHEREᦈᐭ(SELECTAVG(ᦈᐭ)FROMSalary))SELECT*FROMEmployeesSELECT*FROMSalaryUfg

24fg
MN
no
rᦪMNn
Nᦈᐭ\*1[SdiidiF¡᧽£23100123'062001"2¤¥128201853010008¦᪛12955884020018¨512742795102201¡©ª529121086102208«ḝ530222107108^91〉330962818111006ᡂᔡ…52717198ÓiAiZ.Õ6ÖᵫRØᵫÙÚÛᔢᵞBᔢ91H⏙ூ௃>᦮Ḅṹ:usecompyALTERTABLEEmp1oyeesADDASdatediffyear,*+,-=getdateSELECT*FROMEmployeesUPDATEEmployeesSETᙢᙬ='ST=WHEREMN='102201,SELECT*FROMEmp1OyeesUPDATESalarySETᦈᐭ=ᦈᐭ*0.9WHEREMN='102208'SELECT*FROMSalaryUPDATESalarySETᦈᐭ=ᦈᐭ+1000WHEREabs/ᦈᐭ-\*G
2023SELECT*FROMSalarySELECTDepartments.
MN=DePartments.
no=COUNTEmployees.MNGAS
rᦪINTODepartmentStaticFROMDepartmentsINNERJOINEmployeesonDepartments.
MN=£111Ployees.
NGROUPBY
no=
MNSELECT*FROMDepartmentStaticUPDATESalarySETᦈᐭ=ᦈᐭ*1.05WHEREMNINSELECTMNFROMEmployeesWHEREᙢᙬ='ᔠx,GSELECTEmp1oyees.MN=EmpIoyees.n=EmpIoyees.ᙢᙬ=Salary.ᦈᐭFROMEmployeesINNERJOINSalaryONEmployees.MN=Salary.MNUPDATEsalarySETᦈᐭ=ᦈᐭ+500WHEREᦈᐭ³SELECTAVG/ᦈᐭGFROMSalary

25SELECT*FROMSa1aryUPDATESalarySETᦈᐭ=ᦈᐭ-100WHEREMNIN(SELECTMNFROMEmpl0yeesWHERE
N=(SELECT
MNFROMDepartmentsWHERE
no='~'))SELECTEmployees.MN=Emp1oyees.n=Emp1oyees.ᙢ´ᓃSalary.ᦈᐭFROMEmployeesINNERJOINSa1aryONEmployees.MN=Salary.MNTRUNCATETABLEDepartmentStaticSELECT*FROMDepartmentStaticUPDATESa1arySETᦈᐭᦈᐭ*0.95WHEREMNIN(SELECTMNFROMEmpl0yeesWHERE
NIN(SELECTb.
NFROMSalaryASaINNERJOINEmployeesASbONa.MN=b.MNGROUPBY
NHAVINGSUM(ᦈᐭ)>6000))SELECTEmployees.MN=Employees.n=Empl஺yees.
N=Salary.ᦈᐭ=Salary.\*FROMEmployeesINNERJOINSalaryONEmployees.MN=Salary.MNDELETEFROMEmployeesWHEREMNIN(SELECTMNFROMSalaryWHEREᦈᐭு(SELECTAVG(ᦈᐭ)FROMSalary))DELETEFROMSalaryWHEREMNIN(SELECTMNFROMSalaryWHEREᦈᐭு(SELECTAVG(ᦈᐭ)FROMSalary))SELECT*FROMEmployeesSELECT*FRoMSalary

26ஹᦪIº»ᳮ½¾ூ௃ஹᑁÀÁÂÃÄூ௃ஹḄ1ஹÃÄÁÂḄÅÆ)7ᵨU2ஹᳮÁÂḄÇÈ[Éᵨ஺ூ©௃ஹÊᜓÌÅᕒÍᳮᦪIº(company)ÎḄ¡⊤Employeesᕒ⊤Departments
⊤Salary:ᕒ⊤purchase⊤:Ï⊤ூr⚪ᐸ:

271ஹÅÆÁÂ1GÅÆÁÂviewl,uvᡠᨵ~ḄÐḄMNஹn[Ñᦈᐭ,ÒÓ%5⚗=Õᢥ᯿ᦈᐭ᣸Ù஺Solution:usecompyCREATEVIEWViewl/MN=n=ᦈᐭGASSELECTEmployees.MN=n=ᦈᐭFROMEmployeesINNERJOINSalaryONSalary.MN=Employees.MNWHERE
N=SELECT
MNFROMDepartmentsWHERE
no==~.GGUSELECTTOP5MN=n=ᦈᐭFROMviewlORDERBYᦈᐭU/2
ÅÆÁÂview"uvᡠᨵÐḄÐMNஹn[ÚÛUSolutionCREATEVIEWview2/MN=n=ÚÛGASSELECTEmp1oyees.MN=n=/ᦈᐭ\*GASÚÛFROMEmployeesINNERJOINSalaryONEmployees.MN=Salary.MNGSELECTMN=n=ÚÛFROMview23
ᙠÅÆÁÂviewlÝÅÆÁÂview3=Þß":ᡠᨵ~Ḅ=ᦈᐭᜧ^2023ḄÐMNஹn[ÑᦈᐭUSolutionCREATEVIEWview3MN=n,ᦈᐭGASSELECTMN=n=ᦈᐭFROMview1

28WHEREᦈᐭ2023SELECT*fromview3/4ÅÆàáÏᖪã1ḄᕒḄḄÁÂview4,ÕᙠÁÂview4Ýuvᦈᐭᜧ^3000ḄᕒUSoIutionCREATEVIEWview4ASSELECT*FROMEmp1oyeesNNERJOINPurchaseonEmployees.MN=Purchase.ᕒMNWHEREᖪãSELECT*FROMview4WHEREMNINSELECTMNFROMSa1aryWHEREᦈᐭ3000/5ÅÆᐵ^]^40ḄᡠᨵᕒḄMNஹnஹᵯåஹᙢᙬஹḄÁÂview5USo1ution:CREATEVIEWview5/MN=n,ᵯå=ᙢᙬ=GASSELECTMN=n=ᵯå=ᙢᙬ=FROMEmp1oyeesWHEREV40SELECT*FROMvieW5/6ÅÆᐵ^ÁÂឋè"'1'ḄᕒḄMNஹnஹᙢᙬஹᵯåஹ
Nஹ*+,-ஹឋèḄÁÂview6஺So1ution:CREATEVIEWview6/MN=n=ᙢᙬ=ᵯå=
N=*+é-=ឋèGASSELECTMN=n=ᙢᙬ=ᵯå=
N=*+,-=ឋèFROMEmp1oyees

29WHEREឋè)SELECT*FROMview62ஹuvÁÂ(1)ᙠÁÂview1Ýuvᦈᐭᜧ^3000Ḅ~ᕒḄUফëìÁÂview2[⊤uvÚÛᨬᜧḄÐᡠᙠ
Ḅ
N஺Solution:SELECT*FROMviewlWHEREᦈᐭ3000SELECT
NFROMEmp10yeesWHEREMNin(SELECTview2.MNFROMview2WHEREÚÛ=(SELECTMAX(ÚÛ)FROMview2))3ஹJKÁÂ(1)LÁÂview5ᕒMN"'002023'Ḅᵯåᦋ"‘8263137';(2)ᙠÁÂview6Ýî:KḄᕒïð010076,ñòó=☘õ,3865730,2,1985-11-11,1Solution:UPDATEview5SETᵯå='8263137'WHEREMN='002023'SELECT*FROMviewsINSERTINTOview6valuesö010076'='ñòól'☘õ'J3865730I'2','1985-11-11','1')SELECT*FROMview6ூ௃÷᦮ḄùṹusecompyCREATEVIEWViewl(MN=n=ᦈᐭ)AS(SELECTEmployees.MN/n=ᦈᐭ

30FROMEmployeesINNERJOINSalaryONSalary.MN-Employees.MNWHERE
N=/SELECT
MNFROMDepartmentsWHERE
no==~=GGUSELECTTOP5MN=n=ᦈᐭFROMviewl0RDERBYᦈᐭCREATEVIEWview2MN=n=ÚÛGASSELECTEmployees.MN=n=/ᦈᐭ\*GASÚÛFROMEmployeesINNERJOINSalaryONEmp1oyees.MN=Salary.MNGSELECTMN=n=ÚÛFROMView2CREATEVIEWview3/MN=n=ᦈᐭGASSELECTMN=n=ᦈᐭFROMview1WHEREᦈᐭ2023SELECT*fromview3CREATEVIEWview4ASSELECT*FROMEmployeesINNERJOINPurchaseonEmployees.MN=Purchase.ᕒMNWHEREᖪã='01'SELECT*FROMview4WHEREMNINSELECTMNFROMSa1aryWHEREᦈᐭ3000CREATEVIEWview5/MN=n=ᵯå=ᙢᙬ=GASSELECTMN=n=ᵯå=ᙢᙬ=FROMEmployeesWHERE³40SELECT*FROMview5cREATEVIEWview6MN=n=ᙢᙬ=ᵯå=
N=*+,-=ឋèGASSELECTMN=n=ᙢᙬ=ᵯå=
N=*+,-=ឋèFROMEmployeesWHEREឋèjJSELECT*FROMview6SELECT*FROMviewlWHEREᦈᐭ3000SELECT
NFROMEmployeesWHEREMNinSELECTview2.MNFROMview2WHEREÚÛ=SELECTMAXÚÛGFROMview2UPDATEview5SETᵯå=’8263137,WHEREMN==002023’SELECT*FROMView5INSERTINToView6values'010076I'ñòó'3☘õ'=,38657302

311985-11-11/=üGSELECT*FROMview6ýஹfþÿ
ᙠᜩᡃᑮ⊤ᐭᦪᐭ஺ᨬ⌕Ḅ!ᡃᨵ#
ᵨᵯᾯ'()*+,-./Ḅ0᎛2!345678ᝍᡈ!;<.=Ḅ>?@A⌕BᦪC⊤ḄDஹᑤ◀ஹH>IJK஺LMᢈOḄPᵨ,ᓽLMSELECT@SḄTU@VஹW.Ḅ⊤XஹᦪY2ḄZVஹSELECT@SḄGROUPBY[SḄKᵨ\]ᵨZV\SELECT@SḄORDERBY[SḄKᵨ\]ᵨZV஺
^ᦪCḄ⊤ᦪḄJKᓽBSQL@S)*⊤ḄᦪḄᐭஹH>\ᑤ◀;
ᐹᨵ[@SḄᦪH>JKaᳮc⊤ᑡḄeឋ^⊤ᦪḄgh஺i0,ᡃj)*klmᐵJKḄoB⌕oBklḄDp]ᵨ;ᳮcklḄqr\Kᵨ஺ᙠSQLsever2023s᛻uvw+xyzmᐵᦪCḄJK஺ᦪCḄ+{B]ᡃ^ᦪCḄᨵ>Ḅ)|y}~ᑮ;;,ᐸ+ᦪCᡃ34*!Ḅ@A;ᜧi᪵}<ᐸ5Ḅ᪵PᨵḄᦔ஺ᡃᦪCὁᡃᨵᵨḄy¡ᡃᙠy}BᡈHḄᑁ£¤ᨵ>ḄZV\¥¦¤H§ᨵᦔ\¨©Ḅªᓄᔾ>Ḅ஺®¯ᙠ°}SQLServer¤ᡃH±²஺᪥´µ᪵q¶]ᵨḄ·w¸ᡃ^ᦪCᨵH8Ḅc஺

32