河北鸡毛大学
数据库原理实验二
实验名称 | 实验 2 简单查询、连接查询 | ||
实验目的 | 1.熟练掌握 SQL Server 查询分析器的使用方法,加深对标准 SQL 查询语句的理解。 2.熟练掌握简单表的数据查询、数据排序和数据连接查询的操作方法。 | ||
实验仪器 | PC | ||
实验内容 | 1. (1) SELECT Sno,Sname FROM STUDENT WHERE Sdept='MA';
(2) SELECT DISTINCT Sno FROM SC; (3) SELECT DISTINCT Sno,Grade FROM SC,COURSE WHERE Cname='数学'AND SC.Cno=COURSE.Cno ORDER BY Grade DESC,Sno ASC; (4) SELECT DISTINCT Sno,Grade*0.8 FROM SC,COURSE WHERE Cname='数学' AND Grade BETWEEN 80 AND 90 AND SC.Cno=COURSE.Cno; (5) SELECT DISTINCT * FROM STUDENT,SC WHERE Sdept in('CS','MA') AND Sname LIKE'刘%';
(6) SELECT Sno,Cno FROM SC WHERE Grade IS NULL; (7) SELECT STUDENT.*,SC.Cno,Grade FROM STUDENT,SC WHERE STUDENT.Sno=SC.Sno; (8) SELECT STUDENT.Sno,Sname,Cname,Grade FROM STUDENT,SC,COURSE WHERE STUDENT.Sno=SC.Sno AND SC.Cno=COURSE.Cno; (9) SELECT STUDENT.Sno,Sname,Grade FROM STUDENT,SC,COURSE WHERE STUDENT.Sno=SC.Sno AND COURSE.Cname='数学' AND SC.Cno=COURSE.Cno AND Grade>90; (10) SELECT FIRST.Cno,SECOND.Cpno FROM COURSE FIRST,COURSE SECOND WHERE FIRST.Cpno=SECOND.Cno; 2. (1) SELECT SNO FROM SPJ WHERE JNO='J1'; (2) SELECT SNO FROM SPJ WHERE JNO='J1' AND PNO='P1'; (3) SELECT PNO,SUM(QTY)AS SUM FROM SPJ GROUP BY PNO; | ||
实验结论 | distinct 可消除相同重复的行,若是没有distinct关键词,则默认为ALL desc指降序,asc指升序,通常默认为升序 将一个表进行自身链接,可取两个名字,如first和second。 | ||
成绩评定 | 教师签字 | 年 月 日 |