Reference no: EM133268676

Assignment Lab 1:

Open a new Query in SSMS and issue the following statements. Pay attention to the inline comments.

-- Just making sure that the database doesn't currently exist
-- If the databases do not currently exist, the following lines should post an error
-- Run the drop commands first before you do anything else.

Assignment Lab 2:

Exercise 1: Create a SQL Server Agent Job

Task 2: Create a Backup Job

Add a Transact-SQL step that runs in the HumanResources database and executes the following command. The output from the command should be saved as a text file in the D:\ folder:

BACKUP DATABASE HumanResources TO DISK = 'D:\HumanResources.bak';

Take a screenshot after the above command runs.

Transact-SQL:

Time to add more lines to our RetailSales database.

Lastly, add a line, with customer number 888 for yourself. Use your first and last name but ensure that you make afake address.

Design a T-SQL statement that displays only the lines you have entered for this assignment.

Exercise 1: Configuring Database Mail

Task 3: Test Database Mail

1. Send a test email message from the Database Mail service
2. Verify that the test email message is successfully delivered to the C:\inetpub\mailroot\Drop folder.

Take a screenshot of the Drop folder showing the email successfully delivered.

T-SQL Statement:

More Project Work! We will be adding data into the Products and Transactions tables.

Add the following transactions into the transaction table. (No syntax for you this time...)

Before you put in the transactions though, we are going to modify the Transactions table to remove a column.

ALTER TABLE Ottawa.Transactions DROP COLUMN Description;
GO

This change was made after some discussion about usefulness of the description column, and it is less typing for you to put the information into the transaction table. Have fun!

Once all the above data has been put into the Products and Transactions table, create a query that only lists the information that you have put in above (as in, don't include the table information that you put in as part of Assignment 5).

PART 4

Introduction
This project will test your knowledge on the labs that you have completed in this course. You will work with the database you created in Part 1, making modifications as necessary. Ensure that you first completed part 1, 2 and 3 in their entirety before you begin this final project. In addition, before you begin, please ensure that you verify the information that is your RetailSales database, and re-run SQL commands as necessary.

Query

• Run a query that display the contents of all tables (do four separate queries)
o Take a screenshot
• Run a query that displays only the first 5 lines of the Customers table
o Take a screenshot
• Run a query that displays the last line in the Suppliers table
o Take a screenshot
• Run a query that displays the names of the four tables that you created in the RetailSales database
o Take a screenshot

Backup

• Perform a Full backup of the RetailSales database
o Take a screenshot that displays the size of the RetailSales database backup
o Note: You can either run an SQL query on the size of the backup or take a screenshot of File Explorer showing the file size.

Backup (Revisited)

• Perform a transaction log backup of the RetailSales database
o Take a screenshot showing the size of the transaction log backup

Query

• Run a query displaying the last line entered of each table
o Take a screenshot for each query

Security

• Create a dynamic display mask that hides the Customer Phone Numbers in the Customers table
• Encrypt the Cost field in the Products table
• Create a dynamic display mask that hides the address information in the Suppliers table

Query

• Run a query that shows the information in the Customers table has been masked
o Take a screenshot
o Note: Do not show me the unmasked data
• Run a query showing that the cost field in the Products table has been encrypted
o Take a screenshot
• Run a query showing that the Suppliers address information is masked
o Take a screenshot
o Note: Do not show me the unmasked data

Attachment:- Configuring Database Mail.rar