SQL12章 练习

--1 查询俩表的数据,检查数据插入是否正确SELECT * FROM stulnfoSELECT * FROM dbo.stuMarks--2 查询男生名单SELECT stuName, stuNo, stuSex, stuAge, stuSeat, stuAddressFROM stulnfoWHERE stuSex='男'--3 查询笔试成绩优异的学生情况SELECT  examNo,writtenExamFROM stuMarksWHERE writtenExam BETWEEN 75 AND 100--5 统计笔试考试平均分和机试平均分SELECT AVG(writtenExam) AS 笔试平均分,AVG(labExam) AS 机试平均分FROM stuMarks--6 统计参加考试的学生总人数SELECT COUNT(stuNo)AS 考试总人数FROM stuMarks--7 没有通过考试的人SELECT stuName,writtenExam,labExamFROM stuMarks,stulnfoWHERE writtenExam<60 OR labExam<60--8 查询学生成绩,显示学号,笔试,机试及平均分SELECT examNo AS 学号,writtenExam AS 笔试成绩,labExam AS 机试成绩,(writtenExam+labExam)/2 AS平均分FROM stuMarks--9 排名SELECT (writtenExam+labExam)/2 AS 平均分FROM stuMarksORDER BY (writtenExam+labExam)/2 DESC--阶段四--1 95分以上的学生封顶100分UPDATE stuMarks SET writtenExam=100WHERE writtenExam>95--2 笔试成绩95分以下的学生都提5分UPDATE stuMarks SET writtenExam=writtenExam+5WHERE writtenExam<95