欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

数据库练习(学生表)

程序员文章站 2023-01-22 08:07:20
创建表 1、创建学生表,分数表和课程表 create table student( sid int(11) primary key not null, sname char(25) not null, age int(11) not null, sex char(2) not null, depar ......

创建表

1、创建学生表,分数表和课程表

create  table student(

 sid     int(11) primary key not null,

 sname   char(25) not null,

 age     int(11) not null,

 sex     char(2) not null,

 department char(40) ,

 address  char(200) ,

 birthplace  varchar(256)

);

create  table sc(

  sid   int(11) not null,

  cid   int(11) not null,

  grade int(11)

);

create  table course(

  cid    int(11) not null primary key default 4,

  cname  char(40),

  teacher  char(40)

);

#以下是插入课程表的数据

delete from course ;

insert into course values('8108001','math','sandy');

insert into course values('8108002','english','sherry');

insert into course values('8108003','computer','sandy');

insert into course values('8108004','web','sandy');

insert into course values('8108005','java','sandy');

insert into course values('8108006','c languge','sherry');

insert into course values('8108007','python','xiaozhu');

insert into course values('8108008','testing','xiaozhu');

insert into course values('8108009','linux','sherry');

insert into course values('8108010','shell','sherry');

#以下是插入成绩级表的数据

delete from sc;

insert into sc values('3108001','8108010','90');

insert into sc values('3108001','8108003','67');

insert into sc values('3108002','8108003','54');

insert into sc values('3108002','8108010','84');

insert into sc values('3108003','8108003','78');

insert into sc values('3108004','8108004','89');

insert into sc values('3108005','8108006','56');

insert into sc values('3108006','8108005','60');

insert into sc values('3108007','8108004','79');

insert into sc values('3108008','8108008','89');

insert into sc values('3108009','8108002','46');

insert into sc values('3108010','8108003','87');

insert into sc values('3108011','8108001','85');

insert into sc values('3108011','8108002','81');

insert into sc values('3108012','8108001','97');

insert into sc values('3108012','8108002','55');

insert into sc values('3108013','8108002','86');

insert into sc values('3108013','8108001','71');

insert into sc values('3108014','8108002','69');

insert into sc values('3108014','8108001','78');

insert into sc values('3108015','8108002','67');

insert into sc values('3108016','8108001','85');

insert into sc values('3108016','8108003','85');

insert into sc values('3108016','8108002','85');

insert into sc values('3108016','8108004','85');

insert into sc values('3108016','8108005','85');

insert into sc values('3108016','8108006','80');

insert into sc values('3108016','8108007','79');

insert into sc values('3108016','8108009','36');

insert into sc values('3108016','8108010','78');

insert into sc values('3108016','8108008','88');

insert into sc values('3108016','8108021','83');

insert into sc values('3108015','8108001','85');

insert into sc values('3108015','8108003','85');

insert into sc values('3108015','8108004','85');

insert into sc values('3108015','8108005','85');

insert into sc values('3108015','8108006','80');

insert into sc values('3108015','8108007','79');

insert into sc values('3108015','8108009','36');

insert into sc values('3108015','8108010','78');

insert into sc values('3108015','8108008','88');

insert into sc values('3108015','8108021','83');

#以下是插入学生信息数据

delete from student;

insert into student values('3108001','wang min',21,'f','computer-tec','zhongshan

road','jiangsu');

insert into student values('3108002','jidu',20,'m','english','zhongshan road','fujian');

insert into student values('3108003','wangqing',19,'f','computer-tec','zhongshan

road','jiangsu');

insert into student values('3108004','liuxin',23,'f','chinese','zhongshan road','shanghai');

insert into student values('3108005','ligu',22,'f','computer-tec','zhongshan road','jiangsu');

insert into student values('3108006','songjia',19,'m','english','zhongshan road','jiangsu');

insert into student values('3108007','huamao',20,'f','chinese','zhongshan road','shanghai');

insert into student values('3108008','zhujiao',21,'f','english','zhongshan road','jiangsu');

insert into student values('3108009','*',23,'m','computer-tec','zhongshan road','jiangsu');

insert into student values('3108010','jilian',18,'f','chinese','zhongshan road','hunan');

insert into student values('3108011','linbiao',22,'m','computer-tec','zhongshan

road','jiangsu');

insert into student values('3108012','maoguai',21,'m','english','zhongshan road','fujian');

insert into student values('3108013','rongqi',23,'m','computer-tec','zhongshan

road','jiangsu');

insert into student values('3108014','sangzi',20,'f','chinese','zhongshan road','hunan');

insert into student values('3108015','surui',16,'f','computer-tec','zhongshan road','fujian');

insert into student values('3108016','liushaoqi',24,'m','english','zhongshan road','hunan');

commit;

 

 

问题列表

1.sandy老师所教的课程号、课程名称;

select c.cid,cname from student s,sc,course c where s.sid=sc.sid and sc.cid=c.cid and teacher='sandy';

2.年龄大于20岁的女学生的学号和姓名;

select s.sid,sname from student s where age>20 and sex=f;

3.在学生表中按性别排序,且男在前女在后显示记录。

select * from student order by sex desc;

4.“*”所选修的全部课程名称;

select cname from student s,sc t,course c where s.sid=t.sid and t.cid=c.cid and sname='*';

5.所有成绩都在80分以上的学生姓名及所在系;

select distinct sname,department from student s,sc t,course c where s.sid=t.sid and t.cid=c.cid and t.sid  not in (select sid from sc where grade<80 );

6.没有选修“english”课的学生的姓名;

select sname from student where   not sid in(select sid from sc where cid in(select cid from

course where cname = 'english' ))

7.与“jilian”同乡的男生姓名及所在系;

select sname,department from student where sex='m' and birthplace = (select birthplace from student where sname='jilian');

8.英语成绩比数学成绩好的学生;

select * from student s,(select t.sid,grade from sc t,course c,student s where c.cid=t.cid and s.sid=t.sid and cname = 'english') a,(select t1.sid,grade from sc t1,course c1,student s1 where c1.cid=t1.cid and s1.sid=t1.sid and cname = 'math') b where s.sid=a.sid and a.sid=b.sid and a.grade>b.grade;

9.选修同一门课程时,女生比所有男生成绩都好的学生名单;

select * from student s,sc t,student s1,sc t1 where s.sid=t.sid and s1.sid=t1.sid and s.sid=s1.sid and s.sex='f'and s1.sex='m' and

t.cid=t1.cid and t.grade>t1.grade;

10.至少选修两门及以上课程的学生姓名、性别;

select sname,sex from student s,sc t where s.sid=t.sid having count(t.cid)>=2

11.选修了sandy老师所讲课程的学生人数;

select count(sid) from student s where sid in (select distinct sid from sc where cid in (select cid from course where

teacher='sandy'));

12.本校学生中有学生姓名/性别重复的同学,请编写脚本查出本校所有学生的信息,显示学号,姓名,性别,总成绩,对于姓名/性别重复的学生信息只取总成绩最高的那一条记录。

select s.sid,sname,sex,sum(grade) from student s,sc t where s.sid=t.sid

group by  s.sid,sname;

13.“english”课程得最高分的学生姓名、性别、所在系;

select sname,sex,department from student where sid = (select sid from sc where grade =(select max(grade) from sc where cid= (select cid

from course where cname='english')));