Chapter 11 Textbook Assignment

Purpose: To practice using DDL commands

Task #1: Creating Database Files

To see a video demonstration, view: https://youtu.be/5KVJv-ysD7A

Scenario: Our boss would like you to create 3 database files for test purposes

1) Add a comment that says Task #1

2) Create a database called New_AP and comment out your code

3) Create a database called Test_AP and comment out your code

4) Create a database called testDB and comment out your code

5) Save the SQL files as chapter11-textbook-exercises.sql

Task #2: Deleting a Database File

To see a video demonstration, view: https://youtu.be/TTze_7Vec-E

Scenario: Our boss has decided 2 database files is plenty for testing and told you to delete the testDB database

1) Add a comment that says Task #2

2) Delete the testDB database file

3) Comment out your code and save the changes

Task #3: Adding tables to a Database and copying data into a table

To see a video demonstration, view: https://youtu.be/zIzh4xTMlAk

Scenario: Our boss has given you specs for a customer table they would like created in the TEST_AP database and they would also like a rep table created by copying an employee table from the Examples database into the TEST_AP database

1) Add a comment that says Task #3

2) Add the following table to the Test_AP database:

USE Test_AP;
CREATE TABLE customers(
    id INT NOT NULL,
    fname VARCHAR (20) NOT NULL,
    lname VARCHAR (20) NOT NULL,
    address CHAR (25) ,
    phone varchar(10),
    PRIMARY KEY (id)
);

3 Comment out the code

4) To make sure the customers table was created properly, execute the command below:

Use Test_AP;
exec sp_columns customers;

5) Comment out the code

6) Copy data from an existing table(employees) into a new table(Reps) using the commands shown below (the new table will be created and the records will be copied into it):

SELECT * INTO Test_AP.dbo.Reps FROM Examples.dbo.Employees;
SELECT * FROM Test_AP.dbo.Reps;

7) Comment out the code and save your changes.

Task #4: Dropping tables and copying data into a table

To see a video demonstration, view: https://youtu.be/HKTYH8pUs2s

Scenario: Our boss has changed their mind about the column names and data types in the customers table. They would like a new customer table created and the old one deleted. They would also like you to copy data from the Examples database, Customers table into your TEST_AP customers table.

1) Add a comment that says Task #4

2) Delete the customers table from Test_AP

3) Comment out the code

4) Recreate the customers table using the following code:

USE TEST_AP;
CREATE TABLE customers(
    ID int NOT NULL,
    LastName nvarchar(30) NULL,
    FirstName nvarchar(30) NULL,
    Addr nvarchar (60) NULL,
    City nvarchar(15) NULL,
    State nvarchar(15) NULL,
    Zip nvarchar(10) NULL,
    Phone nvarchar(24) NULL,
    PRIMARY KEY(ID)
);

5) Comment out the code.

6) Enter the commands below to copy the data from the Examples Customer table into the Test_AP customers table

USE Test_AP;

INSERT INTO customers
SELECT * FROM Examples.dbo.Customers;

7) Save the file and comment out the code.

Task #5: Altering tables

To see a video demonstration, view: https://youtu.be/wDfxaLnuXjI

Scenario: Our boss would like you to make some adjustments to the customer and rep tables in the Test_AP database

1) Add a comment that says task #5

2) Add a commission column to the Rep table with a default value of 0 and a data type of money (it cannot contain a null value)

USE Test_AP;
ALTER TABLE Reps
ADD Commission money DEFAULT(0) NOT NULL;

3) Comment out the code after you run it.

4) Add a custType and a companyName column to the customer table. Both should be varchar(30). HINT: You will need two ALTER TABLE commands similar to the one above. The semicolon should be placed after the last command.

5) Comment out the code after you run it.

Scenario: Our boss changed their mind and would like companyName removed from the customer table HINT: You need to use ALTER TABLE with DROP COLUMN

1) Comment out the code after you run it.

Scenario: Our boss would like the data type for custType changed to integer (right now it is variable character) HINT: You need to use ALTER TABLE with ALTER COLUMN

1) Comment out the code and save your changes

Task #6 Working with Indices

To see a video demonstration, view: https://youtu.be/1W07XdWyZBg

Scenario: Our boss would like you to create a variety of indices for the test tables to see how it affects database performance. They would like you to begin with a non-clustered index on VendorID in the AP database, Vendors table

1) Add a comment that says Task #6

2) Create the index shown below:

USE AP;
CREATE INDEX IX_VendorID ON Invoices (VendorID);

3) Comment out the code after you run it

Scenario: Our boss would like a non-clustered index on InvoiceDate In DESC order and InvoiceTotal (they are in the AP database, Invoices table).

1) Create the index shown below:

CREATE INDEX IX_Invoices ON Invoices (InvoiceDate DESC, InvoiceTotal);

2) Comment out the code after you run it

Scenario: Our boss would like an index based on LastName added to the Reps table and a similar index on the customer table that uses LastName and FirstName:

1) Create the index shown below:

USE Test_AP;
CREATE INDEX idx_lastname
ON Reps (LastName);

2) Comment out the code after you run it

3) Create a similar index on the Customers table based on LastName and FirstName. Call the index idx_Name

4) Comment out the code after you run it

Scenario: Our boss would like a filtered index for the InvoiceDate and InvoiceTotal in the AP database.

There is a feature in SQL server that will help you create statements for tables and indices – it is called Snippet

To use Snippet, position your mouse below any queries in the query editor window and right click, then select Snippet. Choose Index. Select Basic Index by double clicking – you will see an index template display in the window, similar to the example below, that you can customize.

CREATE INDEX IX_TableName_Col1
ON dbo.TableName
(column_1)

1) Change the snippet index defaults to the example below:

USE AP;
CREATE INDEX IX_InvoicesDateFilter
ON Invoices (InvoiceDate DESC, InvoiceTotal)
WHERE InvoiceDate > '2016-02-01';

2) Comment out the code after you run it and save your changes.

Task #7 Working with Constraints

To see a video demonstration, view: https://youtu.be/MGpxQxgwFPs

Scenario: Our boss would like 2 tables added to the New_AP database: Persons and Orders. The primary key for Persons should be ID and Age should have a constraint of 18 or older. The Orders table should have a Primary key of Order ID and a foreign key constraint on the PersonID column (it should link to the ID column in the Persons table) Since your boss has a history of changing their mind, you decide to create table constraints so you can give the constraints names (that will allow you to remove them or change them).

1) Add a comment for Task #7

2) Create the following tables with the required constraints as shown below:

USE New_AP;
CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    PRIMARY KEY (ID),
    CONSTRAINT CHK_Age CHECK(Age >=18)
);
CREATE TABLE Orders (
    OrderID int NOT NULL,
    OrderNumber int NOT NULL,
    PersonID int,
    PRIMARY KEY (OrderID),
    CONSTRAINT FK_PersonOrder FOREIGN KEY (PersonID) REFERENCES Persons(ID)
);

3) Comment out the code after you run it

Scenario: Our boss would also like us to add some constraints to our existing tables in the Test_AP database. To connect the tables, we will need to add the RepID to our customers table as a foreign key.

1) In order to do this, we need to define EmployeeID in the Reps table as a primary key first and then we can add it to the customers table as a new column and foreign key.

2) The code below will add a primary key constraint to the Reps table and give it a name

USE TEST_AP;
ALTER TABLE Reps
ADD CONSTRAINT PK_EmployeeID PRIMARY KEY(EmployeeID);

3) After running the code, comment it out and run the next set of commands to add the RepID to the customer table as a foreign key:

USE TEST_AP;
ALTER TABLE customers
ADD RepID int CONSTRAINT FK_Rep FOREIGN KEY(RepID) REFERENCES Reps(EmployeeID);

4) Comment out the code after you run it.

Scenario: Our boss would like a constraint added to the DeptNo column in the Reps table to only allow numbers 1 through 6 (those are the departments that have sales reps)

1) Add the constraint shown below to the table:

USE TEST_AP;
ALTER TABLE Reps
ADD CONSTRAINT chk_dept CHECK(DeptNo>=1 AND DeptNo<=6);

2) Comment out the code after you run it

Scenario: Our boss would like a constraint added to the State column in the customers table to only allow customers from Illinois. Unfortunately, the table has data in it that includes other states. SQL server won't let us add the constraint unless we create a different table with only Illinois customers in it.

1) Create the new table as shown below:

USE Test_AP;
SELECT * INTO IllinoisCustomers FROM customers
WHERE State='IL';

2) Comment out the code after you run it

3) Enter a constraint to limit the State to IL in the new IllinoisCustomers table and name the constraint chk_state

4) Comment out the code and save all your changes

5) Upload the file to the dropbox for the chapter 11 textbook assignment.