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

资源描述

1、 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='' 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 stud_info.stud_id, stud_grade.name, stud_info.zipcode, stud_grade.grade FROM stud_info, stud_grade WHERE stud_info.stud_id = stud_grade.stud_id /* 在FROM子句中定义内连接查询每门课程名称及其该门课的任课老师的姓名、编号 */ USE student GO SELECT teacher_info.teacher_id, teacher_info.name, lesson_info.course_name FROM lesson_info INNER JOIN teacher_info ON (lesson_info.course_id = teacher_info.course_id) /* 在stud_info与stud_grade中按学号stud_id进行等值连接，以查询所有参与考试的学生基本信息和成绩分数。 */ USE student GO SELECT * FROM stud_info INNER JOIN stud_grade ON stud_info.stud_id=stud_grade.stud_id ORDER BY stud_info.stud_id /* stud_info和stud_grade采用自然连接以限制结果集的冗余列数据 */ USE student GO SELECT stud_grade.*,stud_info.telcode,stud_info.mark FROM stud_grade INNER JOIN stud_info ON stud_grade.stud_id=stud_info.stud_id ORDER BY stud_grade.stud_id USE student GO INSERT INTO stud_info --为了说明方便，先在学生信息表中插入一条新记录 VALUES ('',N'王一明','03/03/1986',N'男',N'甘肃省兰州市', '','590000',573) SELECT stud_info.stud_id,stud_info.name,stud_grade.course_id FROM stud_info LEFT OUTER JOIN stud_grade ON stud_info.stud_id=stud_grade.stud_id ORDER BY stud_info.stud_id,stud_info.name,stud_grade.course_id /* 学生信息表stud_info右外连接学生成绩表stud_grade */ USE student GO SELECT stud_info.stud_id,stud_info.name,stud_grade.course_id FROM stud_grade RIGHT OUTER JOIN stud_info ON stud_info.stud_id=stud_grade.stud_id ORDER BY stud_info.stud_id,stud_info.name,stud_grade.course_id /* 教师信息表teacher_info全外连接课程信息表lesson_info */ USE student GO SELECT lesson_info.course_name, teacher_info.name, teacher_info.teacher_id FROM lesson_info FULL OUTER JOIN teacher_info ON lesson_info.course_id = teacher_info.course_id ORDER BY lesson_info.course_name, teacher_info.name, teacher_info.teacher_id /* 查询学生成绩表stud_grade中与学号为“”的学生所学的课程相同的学生的学号、姓名、课程号、成绩 */ USE student GO SELECT a.stud_id, a.name, a.course_id, a.grade FROM stud_grade a, stud_grade b WHERE a.course_id=b.course_id AND a.stud_id<>'' AND b.stud_id='' /* 查询与学号为“”的学生同在计算机应用技术专业 (学号stud_id中第5位和第6位为“专业编号”)学习的所有学生的学号、姓名、性别及电话号码 */ USE student GO SELECT stud_id, name, gender, telcode FROM stud_info WHERE substring(stud_id,5,2) = (SELECT substring(stud_id,5,2) FROM stud_info WHERE stud_id ='') /* 在学生成绩表中查询课程类型为“考试”的学生学号、姓名、成绩 */ USE student GO SELECT stud_id,name,grade FROM stud_grade WHERE course_id IN (SELECT course_id FROM lesson_info WHERE course_type =N'考试') /* 查询课程号为“”的多媒体技术这门课的成绩在80至89分的学生的学号、姓名 */ USE student GO SELECT stud_id,name FROM stud_info WHERE 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 student GO SELECT stud_id,name FROM stud_info WHERE substring(stud_id,5,2) ='03' UNION SELECT stud_id,name FROM stud_info WHERE DATEDIFF(year, birthday, getdate())=21 2、 STUDENT create database student go USE student GO CREATE 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 student GO CREATE 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 student GO CREATE 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 student GO CREATE TABLE stud_grade (stud_id CHAR(10) NOT NULL, name NVARCHAR(4) NOT NULL, course_id CHAR(10), grade DECIMAL(4,1) ) USE student GO CREATE TABLE staffroom_info ( jysh_id CHAR(4) not null, jysh_name NVARCHAR(10), jysh_type NCHAR(2), jysh_leader NVARCHAR(4) ) USE student GO CREATE TABLE specialty_code ( speccode CHAR(6), specname NVARCHAR(10) ) USE student GO CREATE 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 student GO CREATE TABLE dept_code ( deptcode CHAR(2), deptname NVARCHAR(10) ) USE student GO CREATE 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 student GO INSERT 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 student GO INSERT 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 student GO INSERT 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 student GO INSERT 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 student GO INSERT 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 student GO INSERT 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 student GO INSERT INTO lesson_info VALUES('',N'计算机导论',N'考察',30,1.5); INSERT INTO lesson_info VALUES('',N'Java程序设计',N'考试',60,3.5); INSERT INTO lesson_info VALUES('',N'微型计算机原理',N'考试',60,3.5); INSERT INTO lesson_info VALUES('',N'IT市场营销',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 student GO INSERT 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 student GO INSERT 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 student GO SELECT * FROM lesson_info /5.2* 在学生基本信息表中查询所有女生的学号、姓名、出生日期的语句：*/ USE student GO SELECT stud_id 学号, name AS 姓名, 出生日期=birthday FROM stud_info WHERE gender=N'女' /5.3* 查询学生的学号、姓名、考试成绩的语句：*/ USE student GO SELECT stud_info.stud_id, stud_info.name, stud_grade.grade FROM stud_info, stud_grade WHERE stud_info.stud_id=stud_grade.stud_id /5.4* 将学生的学号、姓名、性别的查询结果作为新建临时表的语句：*/ USE student GO SELECT stud_id, name, gender INTO new_stud_info FROM stud_info WHERE gender =N'男' /*5.5 查询性别为“女”的学生的姓名、电话、地址和邮编的语句：*/ USE student GO SELECT name, address, telcode, zipcode FROM stud_info WHERE gender=N'女' /*5.6 列出姓“郑”、姓名为两个汉字的学生学号、姓名，性别，入学成绩的语句：*/ USE student GO SELECT stud_id, name, birthday, gender, mark FROM stud_info WHERE name LIKE N'郑_' /* 5.7查询教师职称为“助教”，或为“讲师”，或为“副专家”的教师编号、姓名、职称及工资的语句：*/ USE student GO SELECT teacher_id, name, tech_title, salary FROM teacher_info WHERE tech_title IN (N'助教', N'讲师', N'副专家') /*5.8 求“Java程序设计”课程平均成绩的语句：*/ USE student GO SELECT AVG (grade) FROM stud_grade WHERE course_id='' /*5.9 查询所有男生学号、姓名和年龄，并按出生日期进行排列(升序)的语句：*/ USE student GO SELECT stud_id 学号, name 姓名, year(getdate())-year(birthday) 年龄, birthday 出生日期 FROM stud_info WHERE gender = N'男' ORDER BY birthday ASC /* 5.10记录计算机工程系各个专业的学生的平均入学成绩的语句：*/ USE 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) /* 5.11在教师信息表中，按职称分组记录“讲师”的平均年龄的语句：*/ USE student GO SELECT tech_title, avg(age) FROM teacher_info GROUP BY tech_title HAVING tech_title = N'讲师' /*5.12 对teacher_info中职称为“讲师”的工资，生成汇总行和明细行的语句：*/ USE student GO SELECT tech_title, salary FROM teacher_info WHERE tech_title = N'讲师' ORDER BY tech_title COMPUTE sum(salary) /* 5.13查询每个学生的学号、姓名、邮政编码等基本信息及其所选课程的成绩 */ USE student GO SELECT stud_info.stud_id, stud_grade.name, stud_info.zipcode, stud_grade.grade FROM stud_info, stud_grade WHERE stud_info.stud_id = stud_grade.stud_id /*5.14 在FROM子句中定义内连接查询每门课程名称及其该门课的任课老师的姓名、编号 */ USE student GO SELECT teacher_info.teacher_id, teacher_info.name, lesson_info.course_name FROM lesson_info INNER JOIN teacher_info ON (lesson_info.course_id = teacher_info.course_id) /* 5.15 在stud_info与stud_grade中按学号stud_id进行等值连接，以查询所有参与考试的学生基本信息和成绩分数。 */ USE student GO SELECT * FROM stud_info INNER JOIN stud_grade ON stud_info.stud_id=stud_grade.stud_id ORDER BY stud_info.stud_id /*5.16 stud_info和stud_grade采用自然连接以限制结果集的冗余列数据 */ USE student GO SELECT stud_grade.*,stud_info.telcode,stud_info.mark FROM stud_grade INNER JOIN stud_info ON stud_grade.stud_id=stud_info.stud_id ORDER BY stud_grade.stud_id /* 5.17学生成绩表stud_grade左外连接学生信息表stud_info */ USE student GO INSERT INTO stud_info --为了说明方便，先在学生信息表中插入一条新记录 VALUES ('',N'王一明','03/03/1986',N'男',N'甘肃省兰州市', '','590000',573) SELECT stud_info.stud_id,stud_info.name,stud_grade.course_id FROM stud_info LEFT OUTER JOIN stud_grade ON stud_info.stud_id=stud_grade.stud_id ORDER BY stud_info.stud_id,stud_info.name,stud_grade.course_id /* 5.18学生信息表stud_info右外连接学生成绩表stud_grade */ USE student GO SELECT stud_info.stud_id,stud_info.name,stud_grade.course_id FROM stud_grade RIGHT OUTER JOIN stud_info ON stud_info.stud_id=stud_grade.stud_id ORDER BY stud_info.stud_id,stud_info.name,stud_grade.course_id /* 5.19教师信息表teacher_info全外连接课程信息表lesson_info */ USE student GO SELECT lesson_info.course_name, teacher_info.name, teacher_info.teacher_id FROM lesson_info FULL OUTER JOIN teacher_info ON lesson_info.course_id = teacher_info.course_id ORDER BY lesson_info.course_name, teacher_info.name, teacher_info.teacher_id /* 5.20查询学生成绩表stud_grade中与学号为“”的学生所学的课程相同的学生的学号、姓名、课程号、成绩 */ USE student GO SELECT a.stud_id, a.name, a.course_id, a.grade FROM stud_grade a, stud_grade b WHERE a.course_id=b.course_id AND a.stud_id<>'' AND b.stud_id='' /* 5.21查询与学号为“”的学生同在计算机应用技术专业 (学号stud_id中第5位和第6位为“专业编号”)学习的所有学生的学号、姓名、性别及电话号码 */ USE student GO SELECT stud_id, name,

展开阅读全文