学生选课系统数据库

实验二


create table student(
sno char(8),
sname char(8),
ssex char(2),
sdept char(20),
sage int)


create table course(
cno char(3),
cname char(20),
credit int,
cpno char(3)
)

create table sc(
sno char(8),
cno char(3),
grade int
)

alter table student
alter column sno char(8) not null

alter table student
add constraint student_sno
unique (sno)

alter table student
add column sbirth datetime

alter table student
add column adress char(20)


alter table student
drop column adress

alter table course
alter column cno char(8) not null


alter table course
add constraint course_cno
unique (cno)

create table workers(
cno char(8),
cname char(18),
ctelephonenumber char(11) )

drop table workers

create index sname
on student(desc)


create index grade
on sc(asc)


drop index sname
on student

drop index grade
on sc



实验三

1.
select sno,sname
from student



2.
select *
from student


3.
select distinct sno
from sc


4.
select sno
from sc
where grade<60


5.
select ssex,sage,sdept
from student
where sdept not in('is','cs')


6.
select sno,grade
from sc
where cno='004'
order by grade

7.
select cno,count(sno) as '选修人数'
from sc
group by cno


8.
select sname,sage,sdept
from student
where sdept='cs'

9.
select sno,sname,sdept,sage
from student
where sage>=18 and sage<=20


10.
select *
from student
where sname like '刘%'

11.
select sno
from sc
where cno='001'and sno in
(
select sno
from sc
where cno='002'
)


12.
select sname,2008-sage as '出生年月'
from student


13.
select sno,cno
from sc
where grade is NULL


14.
select sno,sum(grade)
from sc
group by sno
having sum(grade)>200


15.
select cno,count(sno)
from sc
where grade<60
group by cno



16.
select sno,count(cno)
from sc
where grade<60
group by sno
having count(cno)>3




17.
select *
from student
where sage>=10 and sage<=19


18.
select *
from student
order by sdept asc,sage desc


19.
select avg(grade) as '平均成绩'
from sc
where cno='001'

20.
select max(grade) as '最高分'
from sc
where cno='003'


21.
select sno,sum(grade) as '总成绩'
from sc
group by sno



实验四
1.
select student.*,cno,grade
from student,sc
where student.sno=sc.sno


2.
select https://www.360docs.net/doc/f013741669.html,o,second.cpno
from course frist,course second
where frist.cpno=https://www.360docs.net/doc/f013741669.html,o


3.
select student.*,sc.*
from student right join sc
on student.sno=sc.sno


4.
select distinct sname,sdept
from student,sc
where student.sno=sc.sno and sc.sno in(

select sc.sno
from sc
where grade<60
)


5.
select sname
from student
where sno in(
select sno
from sc
where sno not in
(
select sno
from sc
where grade is null
)
group by sno
having min(grade)>90
)
6.
select sname,sno
from student
where sno in(
select sno
from sc
where cno='002' and sno in(
select sno
from sc
where cno='003'
)
)

select distinct sname,student.sno
from sc,student
where student.sno=sc.sno and student.sno in(
select sno
from sc
where cno='002' and sno in(
select sno
from sc
where cno='003'
)
)


7.
select sno,sname
from student
where sage in
(
select sage
from student
where sname ='刘晨'
)

8.
select sname,sage
from student
where sno in(
select sno
from sc
where cno in(
select cno
from course
where cname='数据库')
)

9.
select sname
from student
where sdept!='is' and sageselect sage
from student
where sdept='is'
)

select sname
from student
where sdept!='is' and sage<(
select max(sage)
from student
where sdept='is'
)

10.
select sname from student where sdept!='is' and sage<(select min(sage) from student where sdept='is')

11. select sname from student where not exists ( select * from course where not exists( select * from sc where sno=student.sno and cno=https://www.360docs.net/doc/f013741669.html,o))

12.
select sname
from student
where sdept='cs' and ssex='男'
13.
select sname
from student
where sno in
(
select sno
from sc
where cno='001') and
sno not in
(
select sno
from sc
where cno='002'
)

14.
select distinct cno
from course
where https://www.360docs.net/doc/f013741669.html,o not in(
select https://www.360docs.net/doc/f013741669.html,o
from student,sc
where student.sno=sc.sno and sname='李丽'
)

15.
select avg(sage) as 平均年龄
from student,sc
where student.sno=sc.sno and https://www.360docs.net/doc/f013741669.html,o='003'

16.
select cno,avg(grade)
from sc
group by cno

17.
select cno as 课程号,count(sno) as 人数
from sc
group by cno
having count(sno)>3
order by count(sno) desc,cno asc

18.
select sname
from student
where sno>(
select sno
from student
where sname='刘晨'
) and sage<(
select sage
from student
where sname='刘晨'
)


19.
select sname,sage
from student
where ssex='男' and sage>(
select avg(sage)
from student
where ssex='女'
)

20.
select sname,sage
from student
where sage> (
select max(sage)
from student
where ssex='女'
)



21.
select sno
from sc sc1
where not exists(
select *
from sc sc2
where sc2.sno='08002' and not exists
(
select *
from sc sc3
where sc3.sno=sc1.sno and https://www.360docs.net/doc/f013741669.html,o=https://www.360docs.net/doc/f013741669.html,o
)
)

22.
select cno
from sc
where sno=08001 and cno in
(
select cno
from sc
where sno=08002
)





实验五

1.
insert int

o student1(sno,sname,sage)
values('95030','李莉',18)

2.
insert into sc(sno,cno)
values('95030','1')

3.
update student1
set sage=20
where sdept='cs'


4.
update sc
set grade=0
where 'ma'=(
select sdept
from student1
where student1.sno=sc.sno
)

5.
update sc
set grade=grade+5
where
grade<(
select avg(grade)
from sc
)
and
sno in(
select distinct sno
from student1
where ssex='女')

6.
update sc
set grade=(1+0.05)*grade
where cno='2' and grade<75



8
delete
from student1
where sno='95030'

9.
delete
from sc
where grade is NULL

10
delete
from sc
where sno in
(
select sno
from student
where sdept='ma'
)

12.
delete
from sc
where grade<60 or grade is NULL

13.
update student1
set sage=sage+1

不懂得数据的追加和向另一个基本表中存入信息



详细结果见上传资料中的截图


一、
打开SQL开发环境，进入SQL语句，然后编写下列查询语句
1、应用INSERT,UPDATE,DELETE语句进行更新操作：
１）插入如下学生记录（学号：95030，姓名：李莉，年龄：18）
２）插入如下选课记录（95030，1）
３）计算机系学生年龄改成20
４）数学系所有学生成绩改成0
５）把低于总平均成绩的女同学成绩提高5分
６）修改2号课程的成绩，若成绩小于75分提高5％，成绩大于75时提高4％（两个语句实现，注意顺序）
7）删除95030学生信息
8）删除SC表中无成绩的记录
9）删除张娜的选课记录
１0）删除数学系所有学生选课记录
１1）删除不及格的学生选课记录
12）查询每一门课程成绩都大于等于80分的学生学号、姓名和性别，把值送往另一个已经存在的基本表STU(SNO,SNAME,SSEX)中
13） 把所有学生学号和课程号连接追加到新表中
１4） 所有学生年龄增1
15） 统计3门以上课程不及格的学生把相应的学生姓名、系别追加到另外一个表中
2、熟练掌握INSERT,UPDATE,DELETE语句并能综合应用。




insert into student(sno,sname,sage)
values ('95030','李丽','18');
insert into sc(sno,cno)
values('95030','1');
select *
from student
update student
set sage=20
where sdept='cs'

update sc
set grade=0
where 'ma'=(select sdept
from student
where student.sno=sc.sno)
select *
from sc

update sc
set grade=grade+5
where sno in (select sno
from student
where ssex='女') and grade < (
select AVG(grade)
from sc
)
update sc
set grade =(case when grade<75 then grade *(1+0.05)
when grade >75 then grade *(1+0.04)
else grade
end)
where cno =2
8
delete
from student
where sno='95030'
9delete from sc
where grade is null
10.
delete from sc
where sno=(select sno from student where sname='张娜')

13 STU(SNO,SNAME,SSEX)
CRATE TABLE STU
(SNO CHAR(10) SNAME CHAR(5) SSEX CHAR(2))
INSERT INTO STU(SNO,SNAME,SSEX )
SELE

CT SNO,SNAME,SSEX FROM STUDENT
WHERE SNO IN (SELECT SNO FROM SC WHERE SNO NOT IN (SELECT SNO FROM SC WHERE GRADE IS NULL)
GROUP BY SNO
HAVING MIN(GRADE)>=80)