SQL Help

Use the file to create the initial database and tables.

Require

Using Create

1. Create a table that implements the many-to-many relationship between titles and authors. Include a field to track the royalty share the author receives for a title (must be between 0 and 100%).

Using Alter

Modify the Titles table:

2. Add a field indicating whether there is a contract with an author for this book. This field should default to true.

3. Add a field for the amount of advance payment made to author(s); advance cannot be a negative number; an advance cannot be made if there is not a contract.

4. Add a field for Projected Sales; projected sales cannot be negative or zero.

5. Add a field for Notes.

Expand the Database

Add a table to identify the Sales of books to stores.

6. Add a text field to identify each store by four characters; this is required.

7. Add a field to store up to 20 characters to capture the Purchase Order number; this is required.

8. Add a field to capture the date of the sale, defaulting to today.

Add a table for Sales Details; this is a join table between Sales and Titles.

9. Add fields to serve as foreign keys as appropriate.

10. Make the combination of foreign key values the primary key.

11. Add a field to track the quantity ordered; this is required and must be greater than zero.

12. Add a field to track the quantity shipped; this is required and must be greater than zero.

13. Add a field to track the shipping date; date must be on or before today and after January 1, 1998.

create database DDLPractice use DDLPractice create table authors au id char (11) not null primary key, au_lname varchar (40) not null, au_fname varchar(20) not null, phone char(12) null, address varchar (40) null, city varchar(20) null, au_state char(2) null, zip char(5) null create table publishers pub_id char (4) not null primary key, pub_name varchar (40) null, address varchar (40) null, city varchar(20) null, pub_state char (2) null create table titles title_id char(6) not null primary key, title varchar (80) not null, title_type char (12) nul1, pub_id char (4) null references publishers (pub_id) price money null, pubdate smalldatetime null