数据库应用与设计（崔巍版）例题代码(一）（截至例题3-16）

create database real_student
on
(name=student,
filename='c:\mssql\data\student.mdf',
size=10,
maxsize=50,
filegrowth=5)
log on
(name=student_log,
filename='d:\mssql\log\student.ldf',
size=5mb,
maxsize=25mb,
filegrowth=5mb)
-- 3-1
create table department --yuanxi
(serial_number tinyint primary key,
department_name char(30) unique not null,
principal char(10),
office_location char(40)
)
--3-2
create table student
(student_number char(8) primary key,
department as convert(tinyint,substring(student_number,3,2)) persisted not null
  foreign key references department(serial_number),
name char(10) not null,
gender char(2) check(gender='male' or gender='female'),
source_of_student char(6),
condition char(4) check (condition in('normal','repeat','suspension','quit'))
 )
 --3-3
 create table teacher
 (teacher_number char(6) primary key,
 department as convert(tinyint,substring(teacher_number,1,2)) persisted not null
   foreign key references department(serial_number),
name char(10) not null,
gender char(2) check(gender='male' or gender='female'),
title char(6) check(title in ('professor','associate professor','lecturer','assistant')),
major char(16)
)
--3-4
 create table course
 (course_number char(6) primary key,
 course_name char(24) not null,
 duty_teacher char(6) constraint duty_teacher
   foreign key references teacher(teacher_number),
study_hour tinyint not null,
course_character char(8) check(course_character in('public foudation','professional_foudation','professional_elective','optional_elective'))
) 
--3-5
create table course_selection
(student_number char(8) foreign key references student,
course_number char(6) foreign key references course,
exam_grade tinyint check(exam_grade between 0 and 100) default null
primary key (student_number,course_number)
)
--3-6
alter table student alter column gender char(2) not null
--3-7
alter table course drop duty_teacher
--3-8
alter table course
add duty_teacher char(6) null
constraint ref_teacher foreign key references teacher(teacher_number)
on delete set null
on update cascade
--3-9
insert into department(serial_number ,department_name,principal,office_location)
values(1,'information_management','li jia','first_teaching_building_three_floor')
--3-9change need to delete the constraint first alter table CompanyTransactions drop constraint df__CompanyTr__Creat__0cdae408;alter table CompanyTransactions drop column Created
alter table department drop UQ__departme__226ED15775BFA182
alter table department alter column department_name char(60)
alter table department alter column  principal char(20)
alter table department alter column department_name char(70)
select * from department
--3-10
insert into department
values(2,'economics_management','wu hongzhong','third_teaching_building_four_floor')
--3-11
insert into department
values(3,'computer','zhang qiuxia','second teaching_building_three_floor')
--3-12
insert into department
values(11,'communication_engineering','wang yuehong','fourth_teaching_building_one_floor')
--3-13
insert into student(student_number,name,gender,source_of_student,condition)
values('04014101','cao bo','male','hu bei','normal')
--3-13 change
alter table student alter column condition char(10)
alter table student alter column source_of_student char(15)
alter table student alter column name char(15)
select * from student
--3-16
insert into student values ('04014111','cheng ming','female','hu nan','normal')
insert into student values ('04014122','wu hongxia','female','zhe jiang','normal')
insert into student values ('04026206','zhang hongfei','male','bei jing','normal')
insert into student values('04026210','zhang xuemei','male','gui zhou','suspension')
insert into student values('04016108','lin baoguo','male','shang hai','normal')
insert into student values('04016116','wang li','male','bei jing','normal')
insert into student values('05113102','hao haitao','male','he nan','normal')
insert into student values('05113110','wangwei','male','bei jing','normal')
--debug If your database is created with a Case Sensitive collation then all object names will be Case Sensitive(MSDN).
select collation_name from sys.databases where name=db_name()
select * from student
--change char of table teacher 
alter table teacher alter column major char(60)
alter table teacher alter column name char (18)
alter table teacher alter column gender char (6)
alter table teacher alter column title char(25)
--insert table teacher 
insert into teacher values('010194','liu changyong','male','professor','information_management')
insert into teacher values('010126','gu bo','female','associate professor','computer')
insert into teacher values('011122','zhang jianping','male','lecturer','system_engineering')
insert into teacher values('020112','du ziyi','male','professor','economics')
insert into teacher values('020555','huang mei','female','lecturer','finance')
insert into teacher values('030609','li li','female','associate professor','software_engineering')
select * from teacher

点个赞，么么哒