SQL语句之建表+查询

create table Student
(
    Sno char(10)primary key,
    Sname char(10) unique,
    Ssex char(2) check (Ssex in ('男','女')), 
    Sage smallint check(Sage between 18 and 20),
    Sdept char(20),
);
create table Course(
	Cno char(4) primary key, 
	Cname char(20) not null,  
	Cpno char(4),
	Ccredit smallint,
	foreign key (Cpno) references Course(Cno), 
);
create table SC(
	Sno char(10),  
	Cno char(4),
	Grade smallint,
	primary key(Sno,Cno), 
	foreign key(Sno) references Student(Sno),
	foreign key(Cno) references Course(Cno)
);
insert into dbo.Student(Sno, Sname, Ssex, Sage, Sdept)
values('20601','子瞻','女',18,'art'),
	  ('20602','子尧','女',18,'it'),
	  ('20604','子饶','女',18,'art'),
	  ('20605','同风','女',18,'pe'),
	  ('20606','怀远','女',18,'pe');
	  
INSERT INTO [Course]([Cno],[Cname],[Cpno],[Ccredit])
     VALUES('1000','c#','1002',100),
			('1001','asp.net','1000',100),
			('1002','c',null,100),
			('1003','HTML',null,100),
			('1004','python',null,100),
			('1005','django','1004',100)
 
 
INSERT INTO [SC]([Sno],[Cno],[Grade])
     VALUES('20601','1000','48'),
			('20602','1003','98'),
			('20601','1001','56'),
			('20601','1004','83'),
			('20601','1003','35'),
			('20602','1002','71'),
			('20603','1005','49'),
			('20605','1002','37')

select *
from student

select Sno,Sname
from Student
where Sage = '18'

select Sname,'year of birth:'birth,2022-Sage,lower(Sdept)
from Student


select all Sno
from SC

select Sname
from Student
where Sdept = 'PE'

select Sname ,Sage
from Student
where Sage < 20

select Sname 
from Student 
where Sno like '600001'

select Sname 
from Student
where Sname like 't%'

select Sno ,Grade 
from SC
where Cno = '1002'
order by Grade DESC