Database Job 17~26

--17. Check the average score of "95033" class students. Select avg(degree) fromScorewhereSnoinch(SelectSno fromStudentwhereClass=95033)--18, suppose to use the following command to establish a grade table: Now query all students Sno, CNO and rank column.Create TableGrade (Lowint, UPPint, rankChar(1))Insert  intoGradeValues( -, -,'A')Insert  intoGradeValues( the, the,'B')Insert  intoGradeValues( -, -,'C')Insert  intoGradeValues( -, the,'D')Insert  intoGradeValues(0, -,'E')Select * fromGradeSelect* fromscoreSelectSno,cno,rank fromScoreJoinGrade onDegreebetweenLow andUppOrder  byRank--the second method is select Sno,cno, (select RANK from grade where score.degree between Low and UPP) as degreee from score--19, the query elective "3-105" course performance is higher than the "109" student scores of all the students record. Select * fromScorewhereDegree>(SelectDegree fromScorewhereSno=109  andCno='3-105') andCno='3-105'--20, the inquiry score to choose to learn many courses of the students score is the record of non-highest scores. --This is the highest score of all the people who have chosen multiple courses.Select * fromScorewhereSnoinch(SelectSno fromScoreGroup  bySno having COUNT(*)>1) andDegree not inch(Select Max(degree) fromScorewhereSnoinch(SelectSno fromScoreGroup  bySno having COUNT(*)>1))--eliminate the highest score for each course selected for multiple coursesSelect * fromScore awhereDegree not inch(Select MAX(degree) fromScore BwhereB.cno=A.CNO) andSnoinch(SelectSno fromScoreGroup  bySno having COUNT(*)>1)--exclude student Score information in addition to the highest score for each courseSelect * fromScore awhereDegree not inch(Select MAX(degree) fromScore BwhereB.cno=a.cno)--21, the results of the inquiry is higher than the number of "109", the course number is "3-105" of all records. Select * fromScorewhereDegree>(SelectDegree fromScorewhereSno=109  andCno='3-105')--22. The Sno, sname and Sbirthday of all students who were born in the same year were queried and studied for 108. --Time-date function:--year (Time-date column): Take years--Month (time date column): Month taken--Day (Time-date column): Take daysSelectSno, Sname, Sbirthday fromStudentwhere  Year(Sbirthday)=(Select  Year(Sbirthday) fromStudentwhereSno=108)Select DATEADD( Day,5,'1999-08-31')--Add 5 days on the original basisSelect DATEADD(Week,5,'1999-08-31')--Add 5 weeks to the original base.Select DATEDIFF(WEEKDAY,'2015-04-23','2015-05-01')--days between two days (weekday), a few weeks (week)Print Datename(Weekday,'2015-4-23')--Back to the day of the week--Print Datename(Week,'2015-4-23')--return This is the number of weeks of the first yearPrint DatePart(Weekday,'2015-4-23')--returns the number of weeks that this is the first of this year returns int type datepartPrint getdate()--get current system timePrint IsDate('1999-111-213')--determine if a value is the correct datetime 1: Correct 0: ErrorPrintSysdatetime ()--get a more detailed time--23, inquires "Zhang Xu" the Teacher classroom student achievement. SelectSno,degree fromScorewhereCno=(SelectCno fromCoursewhereTno=(SelectTno fromTeacherwhereTname='Zhang Xu'))--Select Sno,degree from Course join score on COURSE.CNO=SCORE.CNO join teacher on Course.tno=teacher.tno where Tname= ' Zhang Asahi '--Select * fromTeacherSelect * fromCourseSelect * fromscoreSelect * fromStudent--24. The name of the teacher who has more than 5 students who have enrolled in a course. SelectTname fromTeacherwhereTNO=(SelectTno fromCoursewhereCno=(SelectCno fromscoreGroup  byCno having COUNT(*)>5))----Select Tno from course where Cno in--(select Score. Cno from Course--join score on Score.cno=course.cno--GROUP BY SCORE.CNO have COUNT (*) >5)--25. Check the records of all students in class 95033 and 95031. Select * fromStudentwhereClassinch(95033,95031)--26, the inquiry existence has 85 points above the course CNO.Select distinctCno fromScorewhereDegree> -

Database Job 17~26