SQL语句练习

create table student(
Sno char(7) primary key,
Sname char(10) not null,
Ssex char(2),
Sage tinyint,
Sdept char(20)
)

create table Course(
Cno char(10) not null,
Cname char(20) not null,
Ccredit tinyint,
Semester tinyint,
primary key(Cno)
)
create table SC (
Sno char(7) not null,
Cno char(10) not null,
Grade tinyint,
XKLB char(4),
primary key(Sno,Cno),
FOREIGN KEY (Sno)
REFERENCES Student (Sno),
FOREIGN KEY (Cno)
REFERENCES Course(Cno)
)
alter table SC
 ADD XKLB char(4) NULL
 
alter table SC
 alter COLUMN XKLB char(6)
 
alter table Course
 drop column Period

alter table 雇员
 add CONSTRAINT PK_EMP
 PRIMARY KEY (雇员编号)

alter table 工作表
 add CONSTRAINT PK_JOB
 PRIMARY KEY (工作编号)
 
alter table 雇员
 ADD CONSTRAINT UK_SID
  UNIQUE  (电话号码)
 

 

create table 工作(
工作编号 char(8) PRIMARY KEY,
最低工资 int,
最高工资 int,
CHECK (最低工资<=最高工资)
)
create table 雇员(
雇员编号 char(7) PRIMARY KEY,
雇员名 char(10),
工作编号 char(8) REFERENCES 工作(工作编号),
工资 int DEFAULT 1000 CHECK (工资>= 500),
电话号码 char(8) not null UNIQUE
)

select Sname,Sno FROM student

select Sname,Sno,Sdept FROM student

select * from student

select Sname,2010 - Sage From student

select Sname,'出生年份:',2010 - Sage From student

select Sname 姓名,'Year of Birth' 出生年份, 2010 - Sage 年份 , FROM Student

select Sno FROM SC

select Sname From student WHERE Sdept = '计算机系'

select Sname,Sage FROM student WHERE Sage < 20

select DISTINCT Sno FROM SC WHERE Grade < 60

select Sname,Sage, Sdept FROM student WHERE Sage BETWEEN 20 AND 23

select Sname,Sdept,Sage FROM student WHERE Sage NOT BETWEEN 20 AND 23

select Sname,Ssex FROM student WHERE Sdept IN('信息系','数学系','计算机系')

select Sname,Ssex FROM student WHERE Sdept NOT IN('信息系','数学系','计算机系')

select * FROM Student WHERE Sname LIKE '张%'

select * FROM student WHERE Sname LIKE '[张李刘]%'

select Sname,Sno FROM student WHERE Sname LIKE' _[小大]%'

select Sname FROM student WHERE Sname NOT LIKE'[王张]%'

select Sname FROM student WHERE Sname NOT LIKE'王%'
 AND Sname NOT LIKE'张%'
 
select Sname FROM student WHERE Sname LIKE '王_'

select Sname FROM student WHERE Sname LIKE '王__'

select SName FROM student WHERE RTRIM(Sname) LIKE '王__'

select Sname FROM student WHERE Sdept = '计算机系' AND Sage < 20

select Sname FROM student WHERE (Sdept = '计算机系' OR Sdept = '信息系') AND Sage >= 20

select Sname FROM student WHERE (Sdept = '计算机系' OR Sdept = '信息系') AND Sage >= 20

select Sname FROM student WHERE Sdept IN('计算机系','信息系') AND Sage <= 20

select *FROM student ORDER BY Sage

select * FROM student ORDER BY Sdept,Sage DESC

select COUNT(*) FROM Student

INSERT INTO student VALUES ('9521105','陈东','男',18,'信息系')

select Sname FROM Student

UPDATE student SET Sage = Sage + 1

update student Set Sage = 21 WHERE Sno = '9512101'

select * FROM student WHERE Sno = '9512101'

 

create VIEW IS_Student
 as
  select Sno,Sname,Sage
   from student
    where Sdept = '信息系'
    
    
    
create VIEW V_BirthYear
 (Sno,Sname,BirthYear)
as
 select Sno,Sname,2010-Sage
  FROM Student

 

select Sno,Sname,Sage FROM IS_Student
 WHERE Sage <= 20
 
select Sno,Sname,Sage FROM student
 WHERE Sdept = '信息系'
 AND Sage  <= 20
 
 
DROP VIEW IS_Student