作业笔记

create database zzf;  
use zzf;  

create table Student(
Sno char(3) not null  Primary key , 
Sname char(8) not null, 
Ssex char(2)not null,
Sbirthday datetime, 
Class char(5) 

);

insert into Student () values(108,'曾华','男','1977-09-01','95033'),
(105,'匡明','男','1975-10-02','95031'),
(107,'王丽','女','1976-01-23','95033'),
(101,'李军','男','1976-02-20','95033'),
(109,'王芳','女','1975-02-10','95031'),
(103,'陆君','男','1974-06-03','95031');

select Sname , Ssex , Class from student;
select * from Student;
select * from Student where sex='女' or Class='95031'; 
select * from Student where Class='95031'; 
select * from Student order by Class desc;

create table Teacher(
Tno char(3)not null primary key, 
Tname char(4)not null, 
Tsex char(2)not null,
Tbirthday datetime,
Prof char(6),
Depart varchar(10)not null 
);

insert into Teacher () values(804,'李诚','男','1958-12-02','副教授','计算机系'),
(856,'张旭','男','1969-03-12','讲师','电子工程系'),
(825,'王萍','女','1972-05-05','助教','计算机系'), 
(831,'刘冰','女','1977-08-14','助教','电子工程系');

select * from Teacher;

select distinct name from Teacher ;

create table Course (
    Cno char(5) not null Primary key , 
    Cname varchar(10) not null, 
    Tno char(3) not null references Teacher(Tno)  
);

insert into Course () values('3-105','计算机导论',825),
('3-245','操作系统',804),
('6-166','数字电路',856),
('9-888','高等数学',831);

select * from Course;
create table Score (
Sno char(3) not null references Student(Sno),
Cno char(5) not null references Course(Cno),
Degree Decimal(4,1), 
primary key(Sno,Cno) 
);

insert into Score () values(103,'3-245',86),
(105,'3-245',75),
(109,'3-245',68),
(103,'3-105',92),
(105,'3-105',88),
(109,'3-105',76),
(101,'3-105',64),
(107,'3-105',91),
(108,'3-105',78),
(101,'6-166',85),
(107,'6-166',79),
(108,'6-166',81); 

select max(Sno ) from Score;
select max(Cno ) from Score;
select Sname , Cno , Degree from student;
select * from Score where Degree between 60 and 80;
select * from Score where Degree >60 and Degree<80;
select * from Score where Degree in (85,86,88);
select Cno , Sno , Degree from Score where Cno='3-105' and  Degree > '3-245';   
select * from Score where Degree = 85 or Degree = 86 or Degree=88;
select * from Score order by Cno asc;
select * from Score order by Degree desc;