2023年数据库应用技术案例.doc

1、 selectUSE studentGOSELECT stud_id, name, birthday, gender, mark FROM stud_info WHERE name LIKE N郑_USE studentGOSELECT teacher_id, name, tech_title, salaryFROM teacher_infoWHERE tech_title IN (N助教, N讲师, N副专家)USE studentGOSELECT AVG (grade) FROM stud_grade WHERE course_id=USE studentGOSELECT stud_id 学号, name 姓名, year(getdate()-year(birthday) 年龄, birthday 出生日期FROM stud_infoWHERE gender = N男ORDER BY birthday ASCSE studentGOSELECT substring(stud_id,5,2) 专业编号, avg(mark) 平均入学成绩FROM stud_infoWHERE substring(stud_id,3,2) =01GROUP BY substring(stud_id,5,2)USE studentGOSELECT tech_title, avg(age)FROM teacher_infoGROUP BY tech_titleHAVING tech_title = N讲师USE studentGOSELECT tech_title, salaryFROM teacher_infoWHERE tech_title = N讲师ORDER BY tech_titleCOMPUTE sum(salary)/* 查询每个学生的学号、姓名、邮政编码等基本信息及其所选课程的成绩 */USE studentGOSELECT stud_info.stud_id, stud_grade.name, stud_info.zipcode, stud_grade.gradeFROM stud_info, stud_gradeWHERE stud_info.stud_id = stud_grade.stud_id/* 在FROM子句中定义内连接查询每门课程名称及其该门课的任课老师的姓名、编号 */USE studentGOSELECT teacher_info.teacher_id, teacher_info.name, lesson_info.course_nameFROM lesson_info INNER JOIN teacher_info ON (lesson_info.course_id = teacher_info.course_id)/* 在stud_info与stud_grade中按学号stud_id进行等值连接，以查询所有参与考试的学生基本信息和成绩分数。 */USE studentGOSELECT *FROM stud_info INNER JOIN stud_grade ON stud_info.stud_id=stud_grade.stud_idORDER BY stud_info.stud_id/* stud_info和stud_grade采用自然连接以限制结果集的冗余列数据 */USE studentGOSELECT stud_grade.*,stud_info.telcode,stud_info.markFROM stud_grade INNER JOIN stud_info ON stud_grade.stud_id=stud_info.stud_idORDER BY stud_grade.stud_idUSE studentGOINSERT INTO stud_info -为了说明方便，先在学生信息表中插入一条新记录VALUES (,N王一明,03/03/1986,N男,N甘肃省兰州市, ,590000,573)SELECT stud_info.stud_id,stud_info.name,stud_grade.course_idFROM stud_info LEFT OUTER JOIN stud_grade ON stud_info.stud_id=stud_grade.stud_idORDER BY stud_info.stud_id,stud_info.name,stud_grade.course_id/* 学生信息表stud_info右外连接学生成绩表stud_grade */USE studentGOSELECT stud_info.stud_id,stud_info.name,stud_grade.course_idFROM stud_grade RIGHT OUTER JOIN stud_info ON stud_info.stud_id=stud_grade.stud_idORDER BY stud_info.stud_id,stud_info.name,stud_grade.course_id/* 教师信息表teacher_info全外连接课程信息表lesson_info */USE studentGOSELECT lesson_info.course_name, teacher_info.name, teacher_info.teacher_idFROM lesson_info FULL OUTER JOIN teacher_info ON lesson_info.course_id = teacher_info.course_idORDER BY lesson_info.course_name, teacher_info.name, teacher_info.teacher_id/* 查询学生成绩表stud_grade中与学号为“”的学生所学的课程相同的学生的学号、姓名、课程号、成绩 */USE studentGOSELECT a.stud_id, a.name, a.course_id, a.gradeFROM stud_grade a, stud_grade bWHERE a.course_id=b.course_id AND a.stud_id AND b.stud_id=/* 查询与学号为“”的学生同在计算机应用技术专业(学号stud_id中第5位和第6位为“专业编号”)学习的所有学生的学号、姓名、性别及电话号码 */USE studentGOSELECT stud_id, name, gender, telcodeFROM stud_infoWHERE substring(stud_id,5,2) = (SELECT substring(stud_id,5,2) FROM stud_info WHERE stud_id =)/* 在学生成绩表中查询课程类型为“考试”的学生学号、姓名、成绩 */USE studentGOSELECT stud_id,name,gradeFROM stud_gradeWHERE course_id IN (SELECT course_id FROM lesson_info WHERE course_type =N考试)/* 查询课程号为“”的多媒体技术这门课的成绩在80至89分的学生的学号、姓名 */USE studentGOSELECT stud_id,nameFROM stud_infoWHERE EXISTS (SELECT * FROM stud_grade WHERE stud_grade.stud_id = stud_info.stud_id AND (grade BETWEEN 80 AND 89) AND course_id=)/* 查询所学专业同为“计算机控制技术”或年龄为21岁的所有学生的姓名 */USE studentGOSELECT stud_id,name FROM stud_info WHERE substring(stud_id,5,2) =03UNIONSELECT stud_id,name FROM stud_info WHERE DATEDIFF(year, birthday, getdate()=212、 STUDENTcreate database studentgoUSE studentGOCREATE TABLE teacher_info (teacher_id CHAR(6) NOT NULL, name NVARCHAR(4) NOT NULL, gender NCHAR(1), age INT, tech_title NVARCHAR(5), telephone VARCHAR(12), salary DECIMAL(7,2), course_id CHAR(10) );USE studentGOCREATE TABLE teach_schedule(course_id CHAR(10) NOT NULL, course_time DATETIME, course_week CHAR(2), room_id CHAR(6), deptcode CHAR(2), teacher_id CHAR(6)USE studentGOCREATE TABLE stud_info ( stud_id CHAR(10) NOT NULL, name NVARCHAR(4) NOT NULL, birthday DATETIME, gender NCHAR(1), address NVARCHAR(20), telcode CHAR(12), zipcode CHAR(6), mark DECIMAL(3,0) )USE studentGOCREATE TABLE stud_grade(stud_id CHAR(10) NOT NULL, name NVARCHAR(4) NOT NULL, course_id CHAR(10), grade DECIMAL(4,1)USE studentGOCREATE TABLE staffroom_info ( jysh_id CHAR(4) not null, jysh_name NVARCHAR(10), jysh_type NCHAR(2), jysh_leader NVARCHAR(4) )USE studentGOCREATE TABLE specialty_code ( speccode CHAR(6), specname NVARCHAR(10) )USE studentGOCREATE TABLE lesson_info(course_id CHAR(10) NOT NULL, course_name NVARCHAR(12) NOT NULL, course_type NCHAR(2) NOT NULL, course_time INT NOT NULL, course_mark DECIMAL(3,1)USE studentGOCREATE TABLE dept_code ( deptcode CHAR(2), deptname NVARCHAR(10)USE studentGOCREATE TABLE classroom_info(room_id CHAR(6) NOT NULL, room_name NVARCHAR(8), room_type NVARCHAR(5), room_device NVARCHAR(10), room_size DECIMAL(3,0)USE studentGOINSERT INTO teacher_info VALUES(010101,N刘娜,N女,34,N讲师,1418,);INSERT INTO teacher_info VALUES(010106,N王吉林,N男,32,N讲师,1418,);INSERT INTO teacher_info VALUES(010102,N邵云鹏,N男,45,N专家,1458,);INSERT INTO teacher_info VALUES(010104,N赵一欧,N女,26,N助教,1380,);INSERT INTO teacher_info VALUES(010105,N王小悦,N女,35,N讲师,1448,);INSERT INTO teacher_info VALUES(010103,N孙乐多,N男,27,N助教,1380,);USE studentGOINSERT INTO teach_schedule VALUES(,08-30-2023,15,120703,01,010104);INSERT INTO teach_schedule VALUES(,08-30-2023,15,120704,01,010101);INSERT INTO teach_schedule VALUES(,08-30-2023,13,120705,01,010106);INSERT INTO teach_schedule VALUES(,08-30-2023,10,120706,01,010105);INSERT INTO teach_schedule VALUES(,08-30-2023,19,120707,01,010102);INSERT INTO teach_schedule VALUES(,08-30-2023,14,120708,01,010103);USE studentGOINSERT INTO STUD_INFO (STUD_ID,NAME,BIRTHDAY,GENDER, ADDRESS,TELCODE,ZIPCODE,MARK) VALUES (,N张源,12-05-1986,N男,N北京市海淀区,100080,560);INSERT INTO STUD_INFO (STUD_ID,NAME,BIRTHDAY,GENDER, ADDRESS,TELCODE,ZIPCODE,MARK)VALUES (,N赵明,08-06-1986,N男,N上海市浦东区,202300,560);INSERT INTO STUD_INFO (STUD_ID ,NAME ,BIRTHDAY ,GENDER , ADDRESS ,TELCODE ,ZIPCODE ,MARK) VALUES (,N王刚,01-02-1986,N男,N天津市南开区,300000,560);INSERT INTO STUD_INFO (STUD_ID ,NAME ,BIRTHDAY ,GENDER , ADDRESS ,TELCODE ,ZIPCODE ,MARK) VALUES (,N陈红,10-25-1986,N女,N武汉市汉口区,430000,560);INSERT INTO STUD_INFO (STUD_ID ,NAME ,BIRTHDAY ,GENDER , ADDRESS ,TELCODE ,ZIPCODE ,MARK) VALUES (,N孙强,06-07-1986,N男,N重庆市沙坪坝,400000,560);INSERT INTO STUD_INFO (STUD_ID ,NAME ,BIRTHDAY ,GENDER , ADDRESS ,TELCODE ,ZIPCODE ,MARK)VALUES (,N李伟,09-01-1986,N男,N北京市大兴县,102600,560);INSERT INTO STUD_INFO (STUD_ID ,NAME ,BIRTHDAY ,GENDER , ADDRESS ,TELCODE ,ZIPCODE ,MARK) VALUES (,N钱昆,12-06-1986,N男,N广州市海珠区,510000,560);INSERT INTO STUD_INFO (STUD_ID ,NAME ,BIRTHDAY ,GENDER , ADDRESS ,TELCODE ,ZIPCODE ,MARK) VALUES (,N郑芳,08-09-1986,N女,N江苏省南京市,210000,560);INSERT INTO STUD_INFO (STUD_ID ,NAME ,BIRTHDAY ,GENDER , ADDRESS ,TELCODE ,ZIPCODE ,MARK) VALUES (,N袁飞,03-11-1986,N男,N湖南省长沙县,410000,560);INSERT INTO STUD_INFO (STUD_ID ,NAME ,BIRTHDAY ,GENDER , ADDRESS ,TELCODE ,ZIPCODE ,MARK) VALUES (,N孔荣,05-31-1986,N男,N云南省昆明市,650000,600);USE studentGOINSERT INTO stud_grade VALUES(,N张源,90);INSERT INTO stud_grade VALUES(,N赵明,89);INSERT INTO stud_grade VALUES(,N王刚,87);INSERT INTO stud_grade VALUES(,N陈红,91);INSERT INTO stud_grade VALUES(,N孙强,83);INSERT INTO stud_grade VALUES(,N李伟,86);INSERT INTO stud_grade VALUES(,N钱昆,78);INSERT INTO stud_grade VALUES(,N郑芳,95);INSERT INTO stud_grade VALUES(,N袁飞,95);INSERT INTO stud_grade VALUES(,N孔荣,83);INSERT INTO stud_grade VALUES(,N张军,84);USE studentGOINSERT INTO staffroom_info VALUES (0101,N计算机应用,N专业,N王二毛);INSERT INTO staffroom_info VALUES (0102,N计算机网络,N专业,N李四冲);INSERT INTO staffroom_info VALUES (0103,N计算机软件,N专业,N赵一生);INSERT INTO staffroom_info VALUES (0104,N计算机管理,N专业,N汪三洋);USE studentGOINSERT INTO specialty_code VALUES (040101,N计算机应用技术);INSERT INTO specialty_code VALUES (040102,N计算机网络技术);INSERT INTO specialty_code VALUES (040103,N计算机控制技术);INSERT INTO specialty_code VALUES (040104,N多媒体技术);INSERT INTO specialty_code VALUES (040105,N计算机软件技术);INSERT INTO specialty_code VALUES (040106,N计算机通信技术);INSERT INTO specialty_code VALUES (040107,N计算机管理技术);USE studentGOINSERT INTO lesson_info VALUES(,N计算机导论,N考察,30,1.5);INSERT INTO lesson_info VALUES(,NJava程序设计,N考试,60,3.5);INSERT INTO lesson_info VALUES(,N微型计算机原理,N考试,60,3.5);INSERT INTO lesson_info VALUES(,NIT市场营销,N考察,30,1.5);INSERT INTO lesson_info VALUES(,N网络互联设备与配置,N考察,60,2.0);INSERT INTO lesson_info VALUES(,N多媒体技术,N考察,60,3.0);USE studentGOINSERT INTO dept_code VALUES (01,N计算机工程系);INSERT INTO dept_code VALUES (02,N管理工程系);INSERT INTO dept_code VALUES (03,N机电工程系);INSERT INTO dept_code VALUES (04,N食品工程系);INSERT INTO dept_code VALUES (05,N轻化工程系);INSERT INTO dept_code VALUES (06,N通信工程系);INSERT INTO dept_code VALUES (07,N外语工程系);USE studentGOINSERT INTO classroom_info VALUES(120703,N微机组装与维护,N实训,N微机、投影仪,40);INSERT INTO classroom_info VALUES(120704,N计算机网络,N实验,N互换机、路由器等,40);INSERT INTO classroom_info VALUES(120705,N数据库,N计算机机房,N微机、投影仪,60);INSERT INTO classroom_info VALUES(120706,N软件设计,N计算机机房,N微机、投影仪,60);INSERT INTO classroom_info VALUES(120707,N多媒体,N计算机机房,N微机、投影仪,60);INSERT INTO classroom_info VALUES(120708,N,N普通,N白板、投影仪,120);3、 第五章教学案例/5.1* 查询所有课程的具体信息。T-SQL语句：*/USE studentGOSELECT * FROM lesson_info/5.2* 在学生基本信息表中查询所有女生的学号、姓名、出生日期的语句：*/USE studentGOSELECT stud_id 学号, name AS 姓名, 出生日期=birthdayFROM stud_infoWHERE gender=N女/5.3* 查询学生的学号、姓名、考试成绩的语句：*/USE studentGOSELECT stud_info.stud_id, stud_info.name, stud_grade.gradeFROM stud_info, stud_gradeWHERE stud_info.stud_id=stud_grade.stud_id/5.4* 将学生的学号、姓名、性别的查询结果作为新建临时表的语句：*/USE studentGOSELECT stud_id, name, genderINTO new_stud_infoFROM stud_infoWHERE gender =N男/*5.5 查询性别为“女”的学生的姓名、电话、地址和邮编的语句：*/USE studentGOSELECT name, address, telcode, zipcodeFROM stud_infoWHERE gender=N女/*5.6 列出姓“郑”、姓名为两个汉字的学生学号、姓名，性别，入学成绩的语句：*/USE studentGOSELECT stud_id, name, birthday, gender, mark FROM stud_info WHERE name LIKE N郑_/* 5.7查询教师职称为“助教”，或为“讲师”，或为“副专家”的教师编号、姓名、职称及工资的语句：*/USE studentGOSELECT teacher_id, name, tech_title, salaryFROM teacher_infoWHERE tech_title IN (N助教, N讲师, N副专家)/*5.8 求“Java程序设计”课程平均成绩的语句：*/USE studentGOSELECT AVG (grade) FROM stud_grade WHERE course_id=/*5.9 查询所有男生学号、姓名和年龄，并按出生日期进行排列(升序)的语句：*/USE studentGOSELECT stud_id 学号, name 姓名, year(getdate()-year(birthday) 年龄, birthday 出生日期FROM stud_infoWHERE gender = N男ORDER BY birthday ASC/* 5.10记录计算机工程系各个专业的学生的平均入学成绩的语句：*/USE studentGOSELECT substring(stud_id,5,2) 专业编号, avg(mark) 平均入学成绩FROM stud_infoWHERE substring(stud_id,3,2) =01GROUP BY substring(stud_id,5,2)/* 5.11在教师信息表中，按职称分组记录“讲师”的平均年龄的语句：*/USE studentGOSELECT tech_title, avg(age)FROM teacher_infoGROUP BY tech_titleHAVING tech_title = N讲师/*5.12 对teacher_info中职称为“讲师”的工资，生成汇总行和明细行的语句：*/USE studentGOSELECT tech_title, salaryFROM teacher_infoWHERE tech_title = N讲师ORDER BY tech_titleCOMPUTE sum(salary)/* 5.13查询每个学生的学号、姓名、邮政编码等基本信息及其所选课程的成绩 */USE studentGOSELECT stud_info.stud_id, stud_grade.name, stud_info.zipcode, stud_grade.gradeFROM stud_info, stud_gradeWHERE stud_info.stud_id = stud_grade.stud_id/*5.14 在FROM子句中定义内连接查询每门课程名称及其该门课的任课老师的姓名、编号 */USE studentGOSELECT teacher_info.teacher_id, teacher_info.name, lesson_info.course_nameFROM lesson_info INNER JOIN teacher_info ON (lesson_info.course_id = teacher_info.course_id)/* 5.15 在stud_info与stud_grade中按学号stud_id进行等值连接，以查询所有参与考试的学生基本信息和成绩分数。 */USE studentGOSELECT *FROM stud_info INNER JOIN stud_grade ON stud_info.stud_id=stud_grade.stud_idORDER BY stud_info.stud_id/*5.16 stud_info和stud_grade采用自然连接以限制结果集的冗余列数据 */USE studentGOSELECT stud_grade.*,stud_info.telcode,stud_info.markFROM stud_grade INNER JOIN stud_info ON stud_grade.stud_id=stud_info.stud_idORDER BY stud_grade.stud_id/* 5.17学生成绩表stud_grade左外连接学生信息表stud_info */USE studentGOINSERT INTO stud_info -为了说明方便，先在学生信息表中插入一条新记录VALUES (,N王一明,03/03/1986,N男,N甘肃省兰州市, ,590000,573)SELECT stud_info.stud_id,stud_info.name,stud_grade.course_idFROM stud_info LEFT OUTER JOIN stud_grade ON stud_info.stud_id=stud_grade.stud_idORDER BY stud_info.stud_id,stud_info.name,stud_grade.course_id/* 5.18学生信息表stud_info右外连接学生成绩表stud_grade */USE studentGOSELECT stud_info.stud_id,stud_info.name,stud_grade.course_idFROM stud_grade RIGHT OUTER JOIN stud_info ON stud_info.stud_id=stud_grade.stud_idORDER BY stud_info.stud_id,stud_info.name,stud_grade.course_id/* 5.19教师信息表teacher_info全外连接课程信息表lesson_info */USE studentGOSELECT lesson_info.course_name, teacher_info.name, teacher_info.teacher_idFROM lesson_info FULL OUTER JOIN teacher_info ON lesson_info.course_id = teacher_info.course_idORDER BY lesson_info.course_name, teacher_info.name, teacher_info.teacher_id/* 5.20查询学生成绩表stud_grade中与学号为“”的学生所学的课程相同的学生的学号、姓名、课程号、成绩 */USE studentGOSELECT a.stud_id, a.name, a.course_id, a.gradeFROM stud_grade a, stud_grade bWHERE a.course_id=b.course_id AND a.stud_id AND b.stud_id=/* 5.21查询与学号为“”的学生同在计算机应用技术专业(学号stud_id中第5位和第6位为“专业编号”)学习的所有学生的学号、姓名、性别及电话号码 */USE studentGOSELECT stud_id, name,