Query Exercises: $15$ Points

$1.$ Write a TSQL script to create a database with the specifications listed under:

$$ Database name: Marketing

$$ Initial data file size:$10$GB

$$ Growth rate: $10\%$

$$ Initial log file size:$5$GB

$$ Growth rate: $10\%$

$$

$2.$ Write a TSQL script to create tables with the specifications listed under:

Table Name

Column Names

Customers

CustomerID $($PK$)($identity$),$ LName, FName, MName, StreetNo, StreetName, City, SSN$,$ DOB, Phone, ZipCode, Country

Orders

OrderID $($PK$)($identity$),$ ProductID, Transactiondate, OrderQuantity, UnitPrice, Paymenttype$$

$$

$3.$ Write a TSQL script to add a new column to the Orders table with the same specification as CustomerID column of Customers table.

$$

$4.$ Write a TSQL script to add CustomerID column as a foreign key in the Orders table referencing CurstomerID column from Customers table.

$$

$5.$ Write TSQL scripts to insert $10$ sample records into the customers table and only $7$ out of the $10$ customers placed orders.

$6.$ Write a TSQL script to select all orders information from orders table with full customer information who made the orders.

$$

$7.$ Write a TSQL statement that updates the last name of CustomerID $=7$ to $$Charles$.$

$$

$8.$ Create a parametrized stored procedure that takes CustomerID as a variable and returns customer$$s information along with any orders they made. For customers who did not place orders, order related attributes should show null values. $$

$$

$9.$ Take a full backups of marketing database and submit the backup script along with the backup $(.$bak$)$ file.