SQL12章 练习

--1 查询俩表的数据,检查数据插入是否正确

SELECT * FROM stulnfo
SELECT * FROM dbo.stuMarks

--2 查询男生名单
SELECT stuName, stuNo, stuSex, stuAge, stuSeat, stuAddress
FROM stulnfo
WHERE stuSex='男'

--3 查询笔试成绩优异的学生情况
SELECT  examNo,writtenExam
FROM stuMarks
WHERE 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,labExam
FROM stuMarks,stulnfo
WHERE 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 stuMarks
ORDER BY (writtenExam+labExam)/2 DESC

--阶段四
--1 95分以上的学生封顶100分
UPDATE stuMarks SET writtenExam=100
WHERE writtenExam>95

--2 笔试成绩95分以下的学生都提5分
UPDATE stuMarks SET writtenExam=writtenExam+5
WHERE writtenExam<95