T-SQL Integrated applications (heavy)

Questions:1. Statistics on the lack of examination in this exam. 2To extract the student's score information and save the results, including the student's name, student number, written test results, machine test results, whether passed, compare the average test scores and the average test, the lower to carry out the cycle, but the highest score after the score can not exceed 97 points. After the score, statistics on the students ' achievements and the adoption of the situation. 3statistics on the rate of passing of trainees after the mention of the points. Analysis: Using sub-query statistics: The number of people:SELECT Count(*) fromstuinfo Actual number:SELECT Count(*) fromStumarks extracts the student's score information and saves the results, including the student's name, student number, written test results, machine test results, whether the results obtained by the extracted information contains two tables of data, so consider two table connections, using the left connection ( Left Join);SELECTStuname ... fromStuinfo Left Joinstumarks ... onStuinfo.stuno=Stumarks.stuno requires a new column "Whether or not to pass (Ispass)", can be used case ...END, if the written test and the machine test are>60 minutes, then pass. In order to facilitate the subsequent pass rate statistics, pass is 1, failed to 0SELECT... ispass= Case                   whenWrittenexam>= -  andLabexam>= -   Then  1                  ELSE  0                END from..... Request to save the results of the Extract (query), you can use the Select ... intonewtable statement, generate a new table and save the data; Before you generate a new table, you need to detect if the newtable table already existsIF EXISTS(SELECT *  fromsysobjectswhereName='newtable')       DROP TABLEnewtableSELECT... intoNewTable .... Compare the average score of the written test and the average of the machine test, the lower one carries out the cyclic extraction, but the highest score after the score cannot exceed 97 points: Define 2 variables: Store the written test and the average of the machine test, and then use the AVG () function to get the data from the table and assign the value Using the IF statement to determine whether the written test or the machine test is low, the decision on the written test or machine test points; Use the while loop to add points to each student, except for the missing test, when the highest score is more than 97 ticks out of the loop, because each student's written test or machine test points, some students may have just passed the score,   So you need to update the Ispass (whether or not) column. UPDATEnewtableSETIspass= Case                whenWrittenexam>= -  andLabexam>= -  Then 1               ELSE  0              ENDafter the score, the statistics of the students ' achievements and the status of adoption:1Use an alias to implement the name of the text segment, which is the select name=Stuname, School Number=Stuno ...2If a student's score is null (empty), then replace with "missing", otherwise shown as is;3the 1 in the Ispass column is replaced by Yes, 0 is replaced by no;SELECTName=Stuname, School Number=Stuno, written results= Case               whenWrittenexam is NULL  Then 'Lack of test'              ELSE  Convert(varchar(5), Writtenexam)END, machine test results= Case              whenLabexam is NULL  Then 'Lack of test'             ELSE  Convert(varchar(5), Labexam)END, whether through= Case              whenIspass=1  Then 'is a'             ELSE  'No'           END  fromnewtable Statistics on the passing rate of the trainees after the mention of:1) by number: Because by means of 1, not by 0, so the accumulation of ispass column and that is through the number of people;2) Pass rate: the same as the average of the Ispass column*100 is the pass rate;/*--The original data of this exam--*/--SELECT * from Stuinfo--SELECT * from Stumarks /*The ----------------------of the lack of--------------statistical examination*/SELECTShould be to the number=(SELECT Count(*) fromStuinfo),--expected number of aliases for the subquery expressionActual to Number=(SELECT Count(*) fromStumarks), number of missing persons=((SELECT Count(*) fromStuinfo)-(SELECT Count(*) fromstumarks))/*----The statistical examination pass and store the results in the new table newtable---*/IF EXISTS(SELECT *  fromsysobjectsWHEREName='newtable')    DROP TABLEnewtableSELECTStuname,stuinfo.stuno,writtenexam, Labexam, Ispass= Case            whenWrittenexam>= -  andLabexam>= -  Then 1           ELSE 0       END     intoNewTable fromStuinfo Left JOINStumarks onStuinfo.stuno=Stumarks.stuno--SELECT * FROM newtable--view statistical results, can be used for debugging /*-Add points as appropriate: compare the written test and the average of the test, and decide which door to add---*/DECLARE @avgWrittenNumeric4,1)DECLARE @avgLabNumeric4,1)SELECT @avgWritten=AVG(Writtenexam) fromnewtableWHEREWrittenexam is  not NULLSELECT @avgLab=AVG(Labexam) fromnewtableWHERELabexam is  not NULLIF @avgWritten<@avgLab   while(1=1)--cycle to the written test plus points, the maximum score can not exceed 97 points    BEGIN       UPDATENewTableSETWrittenexam=Writtenexam+1      IF(SELECT MAX(Writtenexam) fromnewtable)>= the          Break    ENDELSE... Slightly...--cycle to the machine test plus points, the maximum score can not exceed 97 points--because of the mention, you need to update the data for the ispass (whether or not) columnUPDATEnewtableSETIspass= Case         whenWrittenexam>= -  andLabexam>= -  Then 1        ELSE  0     END--SELECT * FROM newtable--can be used for debugging /*--------------Show the final pass of the exam----------------*/SELECTName=Stuname, School Number=Stuno, written results= Case             whenWrittenexam is NULL  Then 'Lack of test'             ELSE  Convert(varchar(5), Writtenexam)END, machine test results= Case              whenLabexam is NULL  Then 'Lack of test'             ELSE  Convert(varchar(5), Labexam)END, whether through= Case              whenIspass=1  Then 'is a'             ELSE  'No'           END  fromnewtable/*-- show pass rate and pass number- -*/SELECTTotal number=Count(*), by number=SUM(ispass), pass rate=(Convert(varchar(5),AVG(Ispass* -))+'%') fromNewTable

T-SQL Integrated applications (heavy)