1zuoye

SQL 基礎文法練習

一,基本表的定义与删除.

 

题1:

用SQL语句创建如下三张表：学生（Student）,课程表（Course）,和学生选课表（SC）,这三张表的结构如表1-1到表1-3所示。

 

表1-1 Student表结构

列名    说明    数据类型    约束

Sno    学号    字符串，长度为7    主码

Sname    姓名    字符串，长度为10    非空

Ssex    性别    字符串，长度为2    取‘男’或‘女’

Sage    年龄    整数    取值15~45

Sdept    所在系    字符串，长度为20    默认为‘计算机系’

 

Use hello;

create table Student(

    Sno varchar(7)  NOT NULL primary key,

    Sname varchar(10) NOT NULL,

    Ssex varchar(2) check(Ssex='男' or Ssex='女'),

    Sage int check(Sage>15 and Sage<45),

    Sdept varchar(20) default '计算机系'

);

--------------------------------------------------------------------------------

表1-2Course表结构

列名    说明    数据类型    约束

Cno    课程号    字符串，长度为10    主码

Cname    课程名    字符串，长度为20    非空

Ccredit    学分    整数    取值大于0

Semster    学期    整数    取值大于0

Period    学时    整数    取值大于0

 

USE hello;

create TABLE Course(

       Cno varchar(10) NOT NULL primary key,

       Cname varchar(20) NOT NULL,

       Ccredit int CHECK(Ccredit>0),

       Semster int CHECK(Semster>0),

       Period int CHECK(Period>0)

);

--------------------------------------------------------------------------------

 

表1-3 SC表结构

列名    说明    数据类型    约束

Sno    学号    字符串，长度为7    主码，引用Student的外码

Cno    课程名    字符串，长度为10    主码，引用Course

Grade    成绩    整数    取值0~100

create table SC(

    Sno varchar(7)  NOT NULL,

    Cno varchar(10) NOT NULL,

    Grade int CHECk(Grade>0 and Grade<100),

    primary key(Sno,Cno),

    foreign key(Sno)  REFERENCES Student(Sno),

    foreign key(Cno)  REFERENCES Course(Cno)

);

--------------------------------------------------------------------------------

 

 

二，修改表结构

题2：

为SC表添加“选课类别”列，此列的定义为XKLB char(4).

alter table SC add column XKLB char(4);

题3：

将新添加的XKLB的类型改为char(6)。

alter table SC alter column XKLB char(6）;

题4：

删除Course表的Period列。

alter table course drop column period;

三，数据查询功能

 

表3-1 Student表数据

Sno    Sname    Ssex    Sage    Sdept

9512101    李勇    男    19    计算机系

9512102    刘晨    男    20    计算机系

9512103    王敏    女    20    计算机系

9521101    张立    男    22    信息系

9521102    吴宾    女    21    信息系

9521103    张海    男    20    信息系

9531101    钱小平    女    18    数学系

9531102    王大力    男    19    数学系

 

-------------------------------------------------------------------------------------------------------

 

表3-2 Course表数据

Cno    Cname    Ccredit    Cemester

C01    计算机文化学    3    1

C02    VB    2    3

C03    计算机网络    4    7

C04    数据库基础    6    6

C05    高等数学    8    2

C06    数据结构    5    4

 

-------------------------------------------------------------------------------------------------------

 

表 3-3 SC表数据

Sno    Cno    Grade    XKLB

9512101    c01    90    必修

9512101    c02    86    选修

9512101    c06    <NULL>    必修

9512102    c02    78    选修

9512102    c04    66    必修

9521102    c01    82    选修

9521102    c02    75    选修

9521102    c04    92    必修

9521102    c05    50    必修

9521103    c02    68    选修

9521103    c06    <NULL>    必修

9531101    c01    80    选修

9531101    c05    95    必修

9531102    c05    85    必修

 

-------------------------------------------------------------------------------------------------------

 

题5：

用sql语句填写以上（表3-1 Student表数据、表3-2 Course表数据、表 3-3 SC表数据）数据。

Student表：

insert into student values('9512101','李勇','男',19,'计算机系');

insert into student values('9512102','刘晨','男',19,'计算机系');

insert into student values('9512103','王敏','女',19,'计算机系');

insert into student values('9521101','张立','男',19,'信息系');

insert into student values('9521102','吴宾','女',19,'信息系');

insert into student values('9521103','张海','男',19,'信息系');

insert into student values('9531101','钱小平','女',19,'数学系');

insert into student values('9531102','王大力','男',19,'数学系');

Course表：

insert into course values('C01','计算机文化学',3,1);

insert into course values('C02','VB',2,3);

insert into course values('C03',' 计算机网络',4,7);

insert into course values('C04','数据库基础',6,6);

insert into course values('C05','高等数学',8,2);

insert into course values('C06','数据结构',5,4);

SC表：

insert into sc values('9512101','C01',90,'必修');

insert into sc values('9512101','C02',86,'选修');

insert into sc values('9512101','C06',NULL,'必修');

insert into sc values('9512102','C02',78,'选修');

insert into sc values('9512101','C04',66,'必修');

insert into sc values('9521102','C01',82,'选修');

insert into sc values('9521102','C02',75,'选修');

insert into sc values('9521102','C04',92,'必修');

insert into sc values('9521102','C05',50,'必修');

insert into sc values('9521103','C02',68,'选修');

insert into sc values('9521103','C06',NULL,'必修');

insert into sc values('9531101','C01',80,'选修');

insert into sc values('9531101','C05',95,'必修');

insert into sc values('9531102','C05',85,'必修');

题6：

查询全体学生的学号与姓名。

Select Sno,Sname from student;

题7：

查询全体学生的姓名，学号和所在系。

Select Sno,Sname,Sdept from student;

题8：

查询全体学生的记录。

Select * from student；

题9：

查询全体学生的姓名及其出生年份。

select sname,2021-Sage as year from student;

题10：

查询全体学生的姓名和出生年份，并在出生年份列前加入一个列，此列的每行数据均为“Year of Birth”常量值。

select sname as Name , 'Year of Birth' as Birth ,2021-Sage as Year from student;

题11：

在选课表（SC）中查询有哪些学生选修了课程，并列出学生的学号。

 

题12：

查询计算机系全体学生的姓名。

Select sname from student;

题13：

查询所有年龄在20岁以下的学生的姓名及年龄。

Select Sname,age from student where age<20;

题14：

查询考试成绩不及格的学生的学号。

Select sno from sc where grade<60;

题27：

将学生按年龄升序排序。

Select * from student order by Sage ;

 

题28：

查询选修了课程“c02”的学生的学号及其成绩，查询结果按成绩降序排列。

Select sno ,grade from sc where Cno = 'C02' order by grade desc;

题29：

查询全体学生的信息，查询结果按所在系的系名升序排列，同一系的学生按年龄降序排列。

Select * from student order by Sdept,Sage desc;

题55：

将新生纪录（9521105，陈冬，男，信息系，18岁）插入到Student表中。

insert into student values('9521105','陈冬','男',18,'信息系');

题56：

在SC表中插入一新记录（9521105，c01），成绩暂缺。

insert into SC values('9521105','c01',null,null);

题57：

将所有学生的年龄加1。

Update Student Set Sage=Sage+1;

题58：

将“9512101”学生的年龄改为21岁。

update student set Sage =21 where Sno='9512101';

题59：

将计算机系学生的成绩加5分。

Update SC set Grdae = Grade+5

where Sno in (select Sno from student where Sdept='计算机系');

题60删除所有学生的选课记录。

Delete from SC；

题61：

删除所有不及格学生的选课记录。

DELETE FROM SC WHERE Grade < 60；

题62：

删除计算机系不及格学生的选课记录。

DELETE FROM SC

     WHERE Grade < 60 AND Sno IN (

         SELECT Sno FROM Student WHERE Sdept = '计算机系' )