Sqlsever Database Jobs 2

1, using Enterprise Manager to create a new database, named "Book Database", the main data file size is 2M, the secondary data file size is 1M, the log file size is 1M, the storage location is "D:\SQLServer", the other options are default values

。 (If the operation is unsuccessful, analyze the cause, and establish a "library database" according to the relevant requirements)

New query

2, in the "book database" three new tables, table names are: books, authors and publishers;

The Book table contains the fields: Book number, book name, author number, publishing house number, publication date, pricing;

The fields included in the author's table are: Author number, name, gender, age, telephone number, contact address;

The Publishing House table contains fields such as: Publishing house number, Publisher name, contact telephone, contact address, data type and length of each field (note the consistency of the data).

3. Create a PRIMARY KEY constraint for the above three tables, where the first field of each table is its primary key. The default values for the gender fields and age fields in the author table are set to ' male ' and 32, respectively.

"2, 3 topics can be finished together"

New Query create table book (book number int notnull PRIMARY KEY, book name varchar (255), author number int, publisher number int, publication date date, pricing money,) CREATE table author ( Author number int Notnull PRIMARY KEY, name varchar (255), Gender Vatchar (2) Default male, age int default 32, contact phone varchar (20), contact address varchar (255)) CREATE TABLE Publishing House (publisher number int Notnull PRIMARY KEY, publisher name varchar (255), Contact phone varchar (20), contact address varchar (255))

4, for the above three tables to establish an association, the book table in the author number is set as the foreign key of the author table, the publishing house number is set as the foreign key of the publishing house, and the establishment of three table diagram. and enter at least 4 records for each table (content customization, experience foreign key about

The meaning of the bundle).
New query

ALTER TABLE Book Add FOREIGN key (author number) REFERENCES author (author number) ADD FOREIGN key (publisher number) REFERENCES publishing house (publisher number) new query insert INTO book VALU ES (' 1001 ', ' dream of Red Mansions ', ' 2001 ', ' 3001 ', ' 2010/8/9 ', ' ' ') ' INSERT into book VALUES (' 1002 ', ' Journey to the ', ' 2002 ', ' 3002 ', ' 2014/1/9 ', ' ') ' in SERT into books values (' 1003 ', ' Outlaws of the Marsh ', ' 2003 ', ' 3003 ', ' 2000/2/2 ', ' to ') INSERT into books values (' 1004 ', ' Kingdoms ', ' 2004 ', ' 3004 ', '  2002/6/9 ', ' Max ') insert into author values (' 2001 ', ' Cao Xueqin ', ' Male ', ' 21 ', ' 1235556 ', ' Hebei Tangshan ') insert into author values (' 2002 ', ' Wu Chengen ', ' Male ', ' 58 ', ' 13866612564 ', ' Jiangsu Huaian ') insert into author values (' 2003 ', ' Shi Naian ', ' Male ', ' 81 ', ' 13566555146 ', ' Jiangsu Taizhou ') insert into author values (' 20 04 ', ' Luo Guan Zhong ', ' Men ', ' 51 ', ' 13155562563 ', ' Shanxi Taiyuan ') insert into press values (' 3001 ', ' Red House Press ', ' 6533121 ', ' Beijing ') insert into press values (' 3002 ', ' Gao Press ', ' 1122335 ', ' Gao ') INSERT into press values (' 3003 ', ' Liangshan press ', ' 2533121 ', ' Shandong ') insert into press values (' 3004 ', ' Xiaoyao press ', ' 5723121 ', ' Anhui Hefei ')

5, revoke the Book table, and use the command in the Query Analyzer to reconstruct the Book table. (Requires a command to define its primary key and foreign key constraints).

New query

DROP table book create table book (book number int notnull PRIMARY KEY, book name varchar (255), author number int, publishing house number int, date published, Price Money,add FORE IGN Key (author number) REFERENCES author (author number) ADD FOREIGN KEY (publishing house number) REFERENCES Publishing house (publishing house number))

6. Separate the "book database" from the server.

sp_detach_db Book database, True

7. Attach the detached library database to the server.

sp_attach_db ' book database ', ' D:\SQLSever\ library database Db.mdf ', ' D:\SQLSever\ library database Db.ldf '

OK has sqlsever can try to error and then change ~~~└ (^o^) ┘

Sqlsever Database Jobs 2