1、order by ASC|DESC;实验方法:将查询需求用T-SQL语言表示;在SQL Server Query Analyzer的输入区中输入T-SQL查询语句;设置 Query Analyzer的结果区为Standard Execute(标准执行)或Execute to Grid(网格执行)方式;发布执行命令,并在结果区中查看查询结果;如果结果不正确,要进行修改,直到正确为止。实验内容:1. 分别用带DISTINCT和不带DISTINCT关键字的SELELCT在student中进行查询.带distinct:Select class_id from student不带distinct:sel
2、ect distinct class_id 2. 将teacher表中各教师的姓名、教工号及工资按95发放的信息,并将工资按95发放后的列名改为预发工资select teacher_id,teacher_name,salary*0.95 as 预发工资from teacher3. 查询course表中所有学分大于2并且成绩不及格的学生的信息.select distinct student.* from student,course,sc where student.student_id=sc.student_id and sc.course_id=course.course_id and co
3、urse.credit2 and sc.grade80In: from student where student_id in( select student_id from sc where course_id= and grade80)exists: where exists( and course_id=10. 查询所有上计算机基础课程的学生的学号、选修课程号以及分数(分别用连接,in和exists实现)select sc.* where course.course_name=计算机基础 and course.course_id=sc.course_idselect student_id
4、,course_id,grade from sc where course_id select course_id from course where course_name=) select * where course_id=sc.course_id and course_name=11. 查询选修了课程名为“数据库基础”的学生学号和姓名(分别用连接,in和exists实现)select student.student_id,student.student_name from sc,student,course 数据库开发技术 and sc.student_id=student.stude
5、nt_id select student_id,student_name from student where student_id in( where course_id=( ) ) and sc.course_id=( )12. 查询所有计算机系学生的学号、选修课程号以及分数(分别用连接,in和exists实现) from sc,department,student where department.department_name=计算机科学 and department.department_id=student.department_id and sc.student_id=stude
6、nt.student_idselect* where department_id=( select department_id from department where department_name=) where exists ( select * from student where student.student_id =sc.student_id and student.department_id=(计算机科13查询每个dep_04系学生的总成绩、平均成绩, 仅显示平均成绩及格的学生的记录。select student.student_name ,sum(grade) as 总成绩
7、,avg(grade) as 平均成绩 from sc, student,department and student.department_id=department.Department_id and department.department_id=dep_04 group by student.student_id,student_name having avg(grade)14查询“数据库开发技术”的平均成绩select avg(grade) as 数据库开发平均成绩 where course_id in(15按职称查询教师的平均工资,并按总工资降序排列select professi
8、on,avg(salary) as 平均工资,sum(salary) as 总工资 group by profession order by sum(salary) desc附录1:教务管理数据库jwgl结构student表结构列名称数据类型长度允许空值说明Student_idChar8否学生学号Student_nameVarchar学生姓名SexBit1性别AgeInt4年龄Class_id 6班级号Department_id系编号Addressvarchar50家庭住址Course表字段名称允许空Course_id10课程号Course_name20课程名称Book_id15书标识SC表学
9、生号gradeTinyint成绩credit学分Teacher表Teacher_id9教师编号Teacher_namenvarchar教师姓名所在系号ProfessionNvarchar16职称phone是电话adressNchar40住址SalaryNumeric7.2工资BirthSmalldatetime出生日期Postalcode6,0邮编Book表13书号Book_name30书名Publish_company出版社Author作者Price5.2价格Class表Class_idMonitor班主任姓名Class_course表charDepartment表Department_name系名称
copyright@ 2008-2022 冰豆网网站版权所有
经营许可证编号:鄂ICP备2022015515号-1