数据库应用技术 ---案例.doc

select
USE student
GO
SELECT stud_id, name, birthday, gender, mark
FROM stud_info
WHERE name LIKE N'郑_'
USE student
GO
SELECT teacher_id, name, tech_title, salary
FROM teacher_info
WHERE tech_title IN (N'助教', N'讲师', N'副教授')
USE student
GO
SELECT AVG (grade) FROM stud_grade WHERE course_id='0401010102'
USE student
GO
SELECT stud_id 学号, name 姓名, year(getdate())-year(birthday) 年龄, birthday 出生日期
FROM stud_info
WHERE gender = N'男'
ORDER BY birthday ASC
SE student
GO
SELECT substring(stud_id,5,2) 专业编号, avg(mark) 平均入学成绩
FROM stud_info
WHERE substring(stud_id,3,2) ='01'
GROUP BY substring(stud_id,5,2)
USE student
GO
SELECT tech_title, avg(age)
FROM teacher_info
GROUP BY tech_title
HAVING tech_title = N'讲师'
USE student
GO
SELECT tech_title, salary
FROM teacher_info
WHERE tech_title = N'讲师'
ORDER BY tech_title
COMPUTE sum(salary)
/* 查询每个学生的学号、姓名、邮政编码等基本信息及其所选课程的成绩*/
USE student
GO
SELECT , , ,
FROM stud_info, stud_grade
WHERE =
/* 在FROM子句中定义内连接查询每门课程名称及其该门课的任课老师的姓名、编号*/
USE student
GO
SELECT , ,
FROM lesson_info INNER JOIN teacher_info
ON ( = )
/* 在stud_info与stud_grade中按学号stud_id进行等值连接,
以查询所有参加考试的学生基本信息和成绩分数。*/
USE student
GO
SELECT *
FROM stud_info INNER JOIN stud_grade
ON =
ORDER BY
/* stud_info和stud_grade采用自然连接以限制结果集的冗余列数据*/
USE student
GO
SELECT stud_grade.*,,
FROM stud_grade INNER JOIN stud_info
ON =
ORDER BY
USE student
GO
INSERT INTO stud_info --为了说明方便,先在学生信息表中插入一条新记录
VALUES ('0401010704',N'王一明','03/03/1986',N'男',N'甘肃省兰州市',
'0621-2221837','590000',573)
SELECT ,,
FROM stud_info LEFT OUTER JOIN stud_gra