Database computer experiment 4 (data integrity and stored procedure)

This database mainly takes data integrity and stored procedures as an example , The experimental textbook is Li Chunbao's 《 Database principle and technology 》, The content of this experiment is
1） complete P245 The first 8 topic 、 The first 9 topic .
2） complete P395 Computer experiment questions 5 Various topics 、 Computer experiment questions 6 Of （1） and （2） Little topic .
3） complete P396 Computer experiment questions 7 Various topics .

In the last experiment, the index and view were too simple, so I stopped blogging , If necessary, you can comment and leave a message .
Of course, for data integrity , It's more important , But in general engineering design, I don't mind setting the foreign key of data （ Ali company specially emphasized ）, The reason is also obvious , Foreign key constraints are cumbersome for later modifications , So you don't have to or don't mind using .

The experiment will be described below ：

1. Add a non empty constraint to the student number ( When you start building tables, don't forget ）

create database test
use test
create  table table10(
     Student number  int,
     full name  nvarchar(10),
     major  nvarchar(20),
     fraction  int
)

-- 245-1
alter  table table10
    alter column  Student number  int not null

2. Set the student ID as the primary key

-- 245-2
alter table table10
add primary key ( Student number )

3. Set... On the score column 1-100 Value range of

-- 245-3
alter table  table10
add check( fraction >0 and  fraction <101)

4. Set the default value on the specialty column “ Computer science and technology ”

-- 245-4
alter table table10
add default ' Computer science and technology ' for  major 

5. Give it “ Borrower's student number ” Column references table10 Learn about the foreign key relationship of the number column ALTER TABLE sentence

-- 245-question9
create table table11(
     Book number  int,
     Title  nvarchar(30),
     Borrower's student number  int
)

alter table table11
add foreign key ( Borrower's student number ) references table10 ( Student number )

Experiment 5 after the book - 7、 ... and

6. take student The gender column in the table is set to take only “ male ” or “ Woman ” value

-- test5 -1
use Library2128
alter table student2128
add constraint   Gender   check ( Gender  in (' male ',' Woman '))

7. take student The default value in the table is male

--test5-2
alter  table student2128
add default ' male ' for  Gender 

8. modify student surface , Take their shift number as depart The foreign key of the flight number

--test5-3
alter table student2128
add foreign key ( Class number ) references depart2128( Class number )

9. take borrow The student number and book number in the table are defined as the primary key

--test5-4,  Notice how to delete the primary key here , The keys here are written according to their own 
alter table borrow2128
drop  CONSTRAINT PK__borrow21__7B9FD30EF71AFC24

alter table borrow2128
add primary key( Student number , Book number )

10. Delete the previously created constraint

-- test5-5
alter table student2128
drop constraint  Gender 

alter  table student2128
drop constraint  FK__student2128__ Class number __49C3F6B7

11. Create a transaction to depart Table insert 2 Record and roll back the transaction , Finally, check the table

-- Test 6-1
begin transaction
    insert into depart2128 values ('2001',' Department of mathematics ')
    insert into depart2128 values ('2003',' physics department ')
    --insert into depart2128 values ('2004',' Department of Chemistry ')
rollback
select  * from depart2128

12. Create a transaction to depart Table insert 3 A record , Return to the rollback point by setting the rollback point , Finally, check the situation .

-- Test 6-2
begin transaction
    insert into depart2128 values ('2001',' Department of mathematics ')
    insert into depart2128 values ('2003',' physics department ')
    save transaction Mysavp
    insert into depart2128 values ('2004',' Department of Chemistry ')
rollback transaction  Mysavp
select  * from depart2128

13. stay Library Create a scalar valued function in the database Sum(n), seek 1+2+…+n The sum of the , And test with relevant data

-- Test 7-1
use Library2128
create function  getSum(@n int)
returns  int
as
begin
    return @n*(@n+1)/2
end

-- Test data , test n=100,getSum(100)
print dbo.getSum(100)

14. stay Library Create an inline table valued function in the database nbook, Returns the student number of the specified Department 、 full name 、 class 、 The name and date of the book borrowed

-- Test7-2
--drop function nbook
create function  nbook(@ch nvarchar(10))
returns table
as
  return
    (select s. Student number ,s. Class number ,bo. Book name ,br. Borrowing date 
    from depart2128 d,student2128 s,borrow2128 br,book2128 bo
    where d. Department name =@ch and d. Class number =s. Class number  and s. Student number =br. Student number  and bo. Book number =br. Book number )

-- Test data 
select * from dbo.nbook(' Department of electronic engineering ')

15. stay Library Create a multi statement table valued function in the database pbook, Return the Department name and all the students in the Department The average price of books borrowed by students , And test with relevant data

-- Test7-3
create  function pbook(@ch nvarchar(10))
returns @st table
    (
         Department name  nvarchar(10),
         The average price  float
    )
as
begin
    insert @st
    select d. Department name ,avg(bo. pricing *1.0)
    from student2128 s,depart2128 d,borrow2128 br,book2128 bo
    where d. Department name =@ch and s. Class number =d. Class number  and s. Student number =br. Student number  and br. Student number =bo. Book number 
    group by  d. Department name 
return
end
-- Test data 
select * from  dbo.pbook(' Department of Computer Science ')

16. Design a stored procedure , Check the number of each book variety , And test with relevant data

-- Test7-4
create procedure  getBookNum(@ch nvarchar(10))
as
select  Book name ,count(*) as ' number '
from book2128
where  Book name =@ch
group by  Book name 

-- Test data 
exec getBookNum 'C Programming '

17. Design a stored procedure , Use the fuzzy query method to find the students who borrow the specified book title , Output student number 、 full name 、 Class number and title , And test with relevant data

create  procedure  checkStud(@ch nvarchar(10))
as
select s. Student number ,s. full name ,s. Class number ,bo. Book name 
from student2128 s,book2128 bo,borrow2128 br
where s. Student number =br. Student number  and br. Book number =bo. Book number  and bo. Book name =@ch

-- Test data 
exec checkStud 'C Programming '