数据库查询1

原创 2012年05月02日 23:54:24

CREATE TABLE Student

(Sno char(9) not null unique,

 Sname char(20) unique,

 Ssexchar(2),

 Sagesmallint check(Sage>16),

 Sdept char(20)

);

 

CREATE TABLE Course

(Cno char(4) primary key,

 Cname char(40) unique,

 Cpno char(4),

 Ccredit smallint,

);

 

CREATE TABLE Teacher

(

Tno char(9) primary key,

Tname char(20),

Department char(20),

Email char(25),

Salary smallint

);

 

CREATE TABLE  SC

(Sno char(9) not null,

 Cnochar(4) not null,

 Grade int

);

 

ALTER TABLE SC Alter column Grade float;

ALTER TABLE Student ADD Scome DATETIME;

ALTER TABLE Student drop column Scome;

ALTER TABLE Teacher drop column Email;

ALTER TABLE Student drop UQ__Student__7D78A4E7;

 

INSERT INTO Student VALUES ('200215121','李勇','',20,'CS');

INSERT INTO Student VALUES ('200215122','刘晨','',19,'CS');

INSERT INTO Student VALUES ('200215123','王敏','',18,'MA');

INSERT INTO Student VALUES ('200215125','张立','',19,'IS');

 

INSERT INTO Course VALUES (1,'数据库',5,4);

INSERT INTO Course(Cno,Cname,Ccredit)VALUES (2,'数学',2);

INSERT INTO Course VALUES (3,'信息系统',1,4);

INSERT INTO Course VALUES (4,'操作系统',6,3);

INSERT INTO Course VALUES (5,'数据结构',7,4);

INSERT INTO Course(Cno,Cname,Ccredit)VALUES(6,'数据处理',2);

INSERT INTO Course VALUES (7,'PASCAL语言',6,4);

 

INSERT INTO SC VALUES('200215121',1,92);

INSERT INTO SC VALUES('200215121',2,85);

INSERT INTO SC VALUES('200215121',3,88);

INSERT INTO SC VALUES('200215122',2,90);

INSERT INTO SC VALUES('200215122',3,80);

 

CREATE NONCLUSTERED INDEX Stusage ON student(SageDESC);

CREATE CLUSTERED INDEX CLU_Stusno ONstudent(Sno);

CREATE CLUSTERED INDEX CLU_Stusname ONstudent(Sname);

--无法对表 'student' 创建多个聚集索引。请在创建新聚集索引前删除现有的聚集索引'CLU_Stusno'--

CREATE UNIQUE INDEX ASC_CC ON Course(Ccredit);

CREATE UNIQUE INDEX UN_SC ON SC(Sno ASC,CnoDESC);

 

DROP INDEX Student.CLU_Stusno;

DROP INDEX Sc.UN_SC;

DROP TABLE Teacher;

 

--1、简单查询

-- (1)      查询全体学生的姓名、学号、所在系。

select Sname,Sno,Sdept from Student;

-- (2)      查询全体学生的详细记录。

select * from Student;

-- (3)      查询全体学生的姓名、出生年份和所有系,要求用小写字母表示所有系名,并使用列别名改变查询结果的列标题。

selectSname NAME,'Year of Birth:' BIRTH ,2012-Sage BIRTHDAY,LOWER(Sdept)DEPARTMENTfrom Student;

-- (4)      查询选修了课程的学生学号。

select Sno from SC;

-- (5)      查询所有年龄在20岁以下的学生姓名及其年龄。

select Sname,Sage from Student whereSage<20;

-- (6)      查询年龄不在20~23岁之间的学生姓名、系别和年龄。

select Sname,Sdept,Sage from Student WhereSage not between 20 and 23;

-- (7)      查询既不是信息系、数学系,也不是计算机科学系的学生的姓名和性别。

select Sname,Ssex from Student where Sdeptnot in ('CS','MA','IS');

-- (8)      查询学号为20080711的学生的详细情况。(具体的学号值根据表中数据确定)

select * from Student where Sno='20080711';

-- (9)      查询姓“刘”且全名为三个汉字的学生姓名。

select Sname from Student where Sname like'__';

-- (10)查询名字中第3个字为“阳”字的学生的姓名和学号。

select Sname,Sno from Student where Snamelike '__';

-- (11)查询所有不姓刘的学生姓名。

select Sname from Student where Sname notlike '%';

-- (12)查询以"DB_"开头,且倒数第3个字符为 i的课程的详细情况。

select * from Course where Cnamelike'DB\_%i__'escape'\';

-- (13)查所有有成绩的学生学号和课程号。

select Sno,Cno from SC where Grade is notnull;

-- (14)查询计算机系年龄在20岁以下的学生姓名。

select Sname from Student where Sdept='CS'and Sage<20;

-- (15)查询选修了3号课程的学生的学号及其成绩,查询结果按分数降序排列。

select Sno,Grade from SC where Cno='3'order by Grade desc;

-- (16)查询全体学生情况,查询结果按所在系的系号升序排列,同一系中的学生按年龄降序排列。

select * from Student order by Sdept,Sagedesc;

-- (17)查询年龄最大的前三个学生的姓名。

select top 3 Sname from Student order bySage desc;

-- (18)查询学生总人数。

select count(*) from Student;

-- (19)查询选修了课程的学生人数。

select count(distinct Sno) from SC;

-- (20)查询选修2号课程的学生平均成绩。

select avg(Grade) from SC where Cno='2';

-- (21)查询选修1号课程的学生最高分数。

select max(Grade) from SC where Cno='1';

-- (22)求各个课程号及相应的选课人数。

select Cno,count (Sno) from SC group byCno;

-- (23)查询选修了3门以上课程的学生学号。

select Sno from SC group by Sno havingcount(*)>3;

-- (24)查询有3门以上课程是90分以上的学生的学号及(90分以上的)课程数。

select Sno,count(*) from SC whereGrade>=90 group by Sno having count(*)>=3;

-- (25)查询学生20060711选修课程的总学分。

select sum(Ccredit) from SC,Course whereSno='20060711' and SC.Cno=Course.Cno;

-- (26)查询每个学生选修课程的总学分。

select sum(Ccredit) from SC,Course whereSC.Cno=Course.Cno group by Sno;

 

--2、连接查询

-- (1)查询每一门课的间接先修课(即先修课的先修课)

select first.Cno,second.Cpno from Coursefirst,course second where first.Cpno=second.Cno;

-- (2)查询每个学生及其选修课程的情况包括没有选修课程的学生(用外连接)

selectStudent.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade from Student left outer join SCon(Student.Sno=SC.Sno);

-- (3)查询选修2号课程且成绩在90分以上的所有学生的学号、姓名

select Student.Sno,Sname from Student,SCwhere Student.Sno=SC.Sno and SC.Cno='2' and SC.Grade>90;

-- (4)查询每个学生的学号、姓名、选修的课程名及成绩。

select Student.Sno,Sname,Cname,Grade fromStudent,SC,Course where Student.Sno=SC.Sno and SC.Cno=Course.Cno;

--3、嵌套查询

-- (5)查询与“刘晨”在同一个系学习的学生(分别用嵌套查询和连接查询)

select Sno,Sname,Sdept from Student whereSdept in (select Sdept from Student where Sname='刘晨');

-- (6)查询选修了课程名为“信息系统”的学生学号和姓名

select Sno,Sname from Student where Sno in(select Sno from SC where Cno in(select Cno from Course where Cname='信息系统'));

-- (7)查询其他系中比信息系任意一个(其中某一个)学生年龄小的学生姓名和年龄

select Sname,Sage from Student whereSage<ANY (select Sage from Student where Sdept='CS') and Sdept<>'CS';

-- (8)查询其他系中比信息系所有学生年龄都小的学生姓名及年龄。分别用ALL谓词和集函数

select Sname,Sage from Student whereSage<ALL(select Sage from Student where Sdept='CS') and Sdept<>'CS';

-- (9)查询所有选修了1号课程的学生姓名。(分别用嵌套查询和连接查询)

select Sname from Student where  exists (select * from SC whereSno=Student.Sno and Cno='1');

-- (10)查询没有选修1号课程的学生姓名。

select Sname from Student where not exists(select * from SC where Sno=Student.Sno and Cno='1');

-- (11)查询选修了全部课程的学生姓名。

select Sname from Student where not exists(select * from Course where not exists (select * from SC where Sno=Student.Snoand Cno=Course.Cno));

-- (12)查询至少选修了学生95002选修的全部课程的学生号码。

select distinct Sno from SC SCX where notexists (select * from SC SCY where SCY.Sno='95002' and not exists (select *from SC SCZ where SCZ.Sno=SCX.Sno and SCZ.Cno=SCY.Cno));

--4、集合查询

-- (13)查询选修了课程1或者选修了课程2的学生的信息。

SELECT Sno FROM SC WHERE Cno='1' UNION SELECT Sno FROM SC WHERE Cno='2';

-- (14)查询计算机科学系中年龄不大于19岁的学生的信息。

select * from student where Sdept='CS'intersect select * from student where Sage<=19;

-- (15)查询既选修了课程1又选修了课程2的学生的信息。

select * from SC where Cno='1' intersectselect Sno from SC where Cno='2';

-- (16)查询计算机科学系的学生与年龄不大于19岁的学生的差集。

select * from student where Sdept='CS'ecxept select * from student where Sage<=19;

--5、综合查询

--1、查询所有学生都选修的课程的课程号和课程名

select Cno,Cname from course where notexists(select * from student where not exists(select * from sc wherecno=course.cno and sno=student.sno));

--2、查询与课程名“数据库”先行课相同的课程号和课程名

select Cno,Cname from course where Cpno in(select Cpno from course where Cname like '数据库');

--3、查询成绩在85分以上的学生选课情况,要求显示学生姓名、课程名及成绩

select sname,cname,grade from student,course,scwhere student.sno=sc.sno and course.cno=sc.cno and grade>85;

--4、查询总成绩大于500、总学分大于30的学生学号、总成绩和总学分

select student.sno,sum(grade),sum(ccredit)from student,course,sc where student.sno=sc.sno and sc.cno=course.cno

group by student.sno having sum(grade)>500and sum(ccredit)>30;

--5、查询每门课程及其被选修的情况

select course.cno,course.cname,sc.gradefrom course full join sc on (sc.cno=course.cno);

--6、查询至少选修了“刘晨”所选全部课程的学生学号和姓名

select distinct sname,student.sno fromstudent,sc scx where not exists

(select * from sc scy wherestudent.sno=scx.sno and student.sname='刘晨'and not exists

(select * from sc scz where scz.sno=scx.snoand scz.cno=scy.cno));

--7、查询没有选修“数据库”这门课程的学生学号和姓名

select sname,student.sno fromcourse,student,sc where

student.sno=sc.sno and sc.cno=course.cnoand not exists

(select * from course where cname='数据库');

--8、查询只选修了“数据库”这门课程的学生学号、姓名和成绩

select student.sno,sname,grade fromstudent,sc where student.sno=sc.sno and not exists

(select * from sc scx,course wherecourse.cno=scx.cno and course.cname!='数据库');

 

相关文章推荐

数据库查询jdbc1

  • 2014年01月10日 10:31
  • 51KB
  • 下载

用struct1编写从数据库查询数据初始化

1、先写web层 model: Extension.java: /**  * Descript:   * @author:  * @version: 1.0  * @struts.form name=...

6-1数据库查询处理与优化

6-1数据库查询处理与优化tags:数据库关系数据库的查询处理 查询处理是关系数据库管理系统执行查询语句的过程,其任务是把用户提交给关系数据库管理系统的查询语句转换为高效的查询执行计划 相关名词解...

1为数据库查询的数据中加一排序号,2判断为查询出来的字段赋值

@Override public String getSqlByFileCodeForExport(String strTime, String strCompany) { S...

MySQL心得4--1--数据库查询1

1.  选择(Selection)作用于行! 选择是单目运算,其运算对象是一个表。该运算按给定的条件,从表中选出满足条件的行形成一个新表作为运算结果。 选择运算的记号为sF(R)。其中s是选择运算...

数据库查询语句

  • 2016年01月11日 20:19
  • 328KB
  • 下载

数据库查询内连接,外连接,各种查

一、外连接 1.概念:包括左向外联接、右向外联接或完整外部联接 2.左连接:left join 或 left outer join (1)左向外联接的结果集包括 LEFT OUTER 子...

数据库查询

  • 2016年01月14日 09:13
  • 55KB
  • 下载
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:数据库查询1
举报原因:
原因补充:

(最多只允许输入30个字)