sql server 经典练习题分享一

1、建表

create table students(
  stdnumb varchar(3) not null,
  sname varchar(4) not null,
  ssex varchar(2) not null,
  sbirthday datetime,
  class varchar(5)
  )


create table courses(
  cnumb varchar(5) not null,
  cname varchar(10) not null,
  tnumb varchar(10) not null  
 )


create table scores(  
  stdnumb varchar(3) not null,
  cnumb varchar(5) not null,
  degree numeric(10,1) not null)
-- NUMERIC(p,s)   精确数值，精度 p，小数点后位数 s。（与 DECIMAL 相同）
-- DECIMAL(p,s)   精确数值，精度 p，小数点后位数 s。例如：decimal(5,2) 是一个小数点前有 3 位数，小数点后有 2 位数的数字。


create table teachers(
  tnumb varchar(3) not null,
  tname varchar(4) not null,
  tsex varchar(2) not null,
  tbirthday datetime not null,
  prof varchar(6),
  depart varchar(10) not null
  )

2、插入数据

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


insert into courses(cnumb,cname,tnumb) values ('3-105',