{"id":329,"date":"2014-05-31T15:59:12","date_gmt":"2014-05-31T15:59:12","guid":{"rendered":"http:\/\/opentextbc.ca\/dbdesign01\/?post_type=back-matter&#038;p=329"},"modified":"2014-10-22T04:03:33","modified_gmt":"2014-10-22T04:03:33","slug":"appendix-d-sql-lab-with-solution","status":"publish","type":"back-matter","link":"https:\/\/opentextbc.ca\/dbdesign01\/back-matter\/appendix-d-sql-lab-with-solution\/","title":{"raw":"Appendix C SQL Lab with Solution","rendered":"Appendix C SQL Lab with Solution"},"content":{"raw":"Download the following script:\u00a0 <a title=\"Orders and Data\" href=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2014\/06\/ordersanddata.txt\">OrdersAndData.sql<\/a>.\r\n<h1>Part 1 \u2013 DDL<\/h1>\r\n[caption id=\"attachment_1067\" align=\"aligncenter\" width=\"664\"]<a href=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2014\/10\/DD-OrdersandData.jpg\"><img src=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2014\/10\/DD-OrdersandData.jpg\" alt=\"Figure C.1. ERD \" class=\"wp-image-1067 size-full\" height=\"430\" width=\"664\" \/><\/a> Figure C.1. ERD for Orders and Data.[\/caption]\r\n<ol>\r\n\t<li>Use the script OrdersAndData.sql that creates the tables and adds the data for the Orders and Data ERD in Figure C.1.<\/li>\r\n\t<li>Create a database called Orders. Modify the script to integrate the PK and referential integrity. Show the CREATE TABLE\u00a0statements with the modifications including the constraints given in step 3.<\/li>\r\n\t<li>Add the following constraints:<\/li>\r\n<\/ol>\r\n<ul>\r\n\t<li>tblCustomers table:\u00a0 Country - default to Canada<\/li>\r\n\t<li>tblOrderDetails:\u00a0 Quantity -\u00a0\u00a0 &gt; 0<\/li>\r\n\t<li>tblShippers: CompanyName must be unique.<\/li>\r\n\t<li>tblOrders: ShippedDate must be greater than order date.<\/li>\r\n<\/ul>\r\n<div class=\"textbox\">CREATE DATABASE Orders\r\nGo\r\nUse Orders\r\nGo<\/div>\r\n<div class=\"textbox shaded\">Use Orders\r\nGo\r\nCREATE TABLE [dbo].[tblCustomers]\r\n[CustomerID]\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 nvarchar(5) NOT NULL,\r\n[CompanyName]\u00a0\u00a0\u00a0\u00a0\u00a0 nvarchar(40) NOT NULL,\r\n[ContactName]\u00a0\u00a0\u00a0\u00a0\u00a0 nvarchar(30) NULL,\r\n[ContactTitle]\u00a0\u00a0\u00a0\u00a0 nvarchar(30) NULL,\r\n[Address]\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 nvarchar(60) NULL,\r\n[City]\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 nvarchar(15) NULL,\r\n[Region]\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 nvarchar(15) NULL,\r\n[PostalCode]\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 nvarchar(10) NULL,\r\n[Country]\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 nvarchar(15) NULL\r\nConstraint \u00a0\u00a0 \u00a0df_country DEFAULT 'Canada',\r\n[Phone]\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 nvarchar(24) NULL,\r\n[Fax]\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 nvarchar(24) NULL,\r\nPrimary Key (CustomerID)\r\n);<\/div>\r\n<div class=\"textbox shaded\">CREATE TABLE [dbo].[tblSupplier] (\r\n[SupplierID]\u00a0\u00a0\u00a0\u00a0 int NOT NULL,\r\n[Name]\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 nvarchar(50) NULL,\r\n[Address]\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 nvarchar(50) NULL,\r\n[City]\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 nvarchar(50) NULL,\r\n[Province]\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 nvarchar(50) NULL,\r\nPrimary Key (SupplierID)\r\n);<\/div>\r\n<div class=\"textbox shaded\">CREATE TABLE [dbo].[tblShippers] (\r\n[ShipperID]\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 int NOT NULL,\r\n[CompanyName]\u00a0\u00a0\u00a0\u00a0 nvarchar(40) NOT NULL,\r\nPrimary Key (ShipperID),&lt;\r\nCONSTRAINT uc_CompanyName UNIQUE (CompanyName)\r\n);<\/div>\r\n<div class=\"textbox shaded\">CREATE TABLE [dbo].[tblProducts] (\r\n[ProductID]\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 int NOT NULL,\r\n[SupplierID]\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 int NULL,\r\n[CategoryID]\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 int NULL,\r\n[ProductName]\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 nvarchar(40) NOT NULL,\r\n[EnglishName]\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 nvarchar(40) NULL,\r\n[QuantityPerUnit]\u00a0\u00a0\u00a0\u00a0 nvarchar(20) NULL,\r\n[UnitPrice]\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 money NULL,\r\n[UnitsInStock]\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 smallint NULL,\r\n[UnitsOnOrder]\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 smallint NULL,\r\n[ReorderLevel]\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 smallint NULL,\r\n[Discontinued]\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 bit NOT NULL,\r\nPrimary Key (ProductID),\r\nForeign Key (SupplierID) References tblSupplier\r\n);<\/div>\r\n<div class=\"textbox shaded\">CREATE TABLE [dbo].[tblOrders] (\r\n[OrderID]\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 int NOT NULL,\r\n[CustomerID]\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 nvarchar(5) NOT NULL,\r\n[EmployeeID]\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 int NULL,\r\n[ShipName]\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 nvarchar(40) NULL,\r\n[ShipAddress]\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 nvarchar(60) NULL,\r\n[ShipCity]\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 nvarchar(15) NULL,\r\n[ShipRegion]\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 nvarchar(15) NULL,\r\n[ShipPostalCode]\u00a0\u00a0\u00a0\u00a0 nvarchar(10) NULL,\r\n[ShipCountry]\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 nvarchar(15) NULL,\r\n[ShipVia]\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 int NULL,\r\n[OrderDate]\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 smalldatetime NULL,\r\n[RequiredDate]\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 smalldatetime NULL,\r\n[ShippedDate]\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 smalldatetime NULL,\r\n[Freight]\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 money NULL\r\nPrimary Key (OrderID),\r\nForeign Key (CustomerID) References tblCustomers,\r\nForeign Key (ShipVia) References tblShippers,\r\nConstraint valid_ShipDate CHECK (ShippedDate &gt; OrderDate)\r\n);<\/div>\r\n<div class=\"textbox shaded\">CREATE TABLE [dbo].[tblOrderDetails] (\r\n[OrderID]\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 int NOT NULL,\r\n[ProductID]\u00a0\u00a0\u00a0\u00a0 int NOT NULL,\r\n[UnitPrice]\u00a0\u00a0\u00a0\u00a0 money NOT NULL,\r\n[Quantity]\u00a0\u00a0\u00a0\u00a0\u00a0 smallint NOT NULL,\r\n[Discount]\u00a0\u00a0\u00a0\u00a0\u00a0 real NOT NULL,\r\nPrimary Key (OrderID, ProductID),\r\nForeign Key (OrderID) References tblOrders,\r\nForeign Key (ProductID) References tblProducts,\r\nConstraint Valid_Qty Check (Quantity &gt; 0)\r\n);\r\nGo<\/div>\r\n<h1>Part 2 - Create the Following SQL Statements<\/h1>\r\n1.\u00a0\u00a0 \u00a0Show a list of customers and the orders they generated during 2014. Display customer ID, order ID, order date and date ordered.\r\n<div class=\"textbox shaded\">Use Orders\r\nGo\r\nSELECT CompanyName, OrderID, RequiredDate as 'order date', OrderDate as 'date ordered'\r\nFROM tblcustomers\u00a0 JOIN tblOrders on tblOrders.CustomerID = tblCustomers.CustomerID\r\nWHERE Year(OrderDate) = 2014<\/div>\r\n2.\u00a0\u00a0 \u00a0Using the ALTER TABLE\u00a0statement, add a new field (Active) in the tblcustomer.\u00a0 Default it to True.\r\n<div class=\"textbox shaded\">ALTER TABLE tblCustomers\r\nADD Active bit DEFAULT ('True')<\/div>\r\n3.\u00a0\u00a0 \u00a0Show all orders purchased before September 1, 2012. Display company name, date ordered and total amount of order (include freight).\r\n<div class=\"textbox shaded\">SELECT tblOrders.OrderID, OrderDate as 'Date Ordered', sum(unitprice*quantity*(1-discount))+ freight as 'Total Cost'\r\nFROM tblOrderDetails join tblOrders on tblOrders.orderID = tblOrderDetails.OrderID\r\nWHERE OrderDate &lt; 'September 1, 2012'\r\nGROUP BY tblOrders.OrderID, freight, OrderDate<\/div>\r\n4.\u00a0\u00a0 \u00a0Show all orders that have been shipped via Federal Shipping.\u00a0 Display OrderID, ShipName, ShipAddress and CustomerID.\r\n<div class=\"textbox shaded\">SELECT OrderID, ShipName, ShipAddress, CustomerID\r\nFROM tblOrders join tblShippers on tblOrders.ShipVia = tblShippers.ShipperID\r\nWHERE CompanyName= 'Federal Shipping'<\/div>\r\n5.\u00a0\u00a0 \u00a0Show all customers who have not made purchases in 2011.\r\n<div class=\"textbox shaded\">SELECT CompanyName\r\nFROM tblCustomers\r\nWHERE CustomerID not in\r\n(\u00a0 SELECT CustomerID\r\nFROM\u00a0 tblOrders\r\nWHERE Year(OrderDate) = 2011\r\n)<\/div>\r\n6.\u00a0\u00a0 \u00a0Show all products that have never been ordered.\r\n<div class=\"textbox shaded\">SELECT ProductID from tblProducts\r\nExcept\r\nSELECT ProductID from tblOrderDetails<\/div>\r\nOR\r\n<div class=\"textbox shaded\">SELECT Products.ProductID,Products.ProductName\r\nFROM Products LEFT JOIN [Order Details]\r\nON Products.ProductID = [Order Details].ProductID\r\nWHERE [Order Details].OrderID IS NULL<\/div>\r\n7.\u00a0\u00a0 \u00a0Show OrderIDs for customers who reside in London. Use a subquery.\u00a0Display CustomerID, CustomerName and OrderID.\r\n<div class=\"textbox shaded\">SELECT Customers.CompanyName,Customers.CustomerID,OrderID\r\nFROM Orders\r\nLEFT JOIN Customers ON Orders.CustomerID = Customers.CustomerID\r\nWHERE Customers.CompanyName IN\r\n(SELECT CompanyName\r\nFROM Customers\r\nWHERE City =\u00a0'London')<\/div>\r\n8.\u00a0\u00a0 \u00a0Show products supplied by Supplier A and Supplier B. Display product name and supplier name.\r\n<div class=\"textbox shaded\">SELECT ProductName, Name\r\nFROM tblProducts JOIN tblSupplier on tblProducts.SupplierID = tblSupplier.SupplierID\r\nWHERE Name Like 'Supplier A' or Name Like 'Supplier B'<\/div>\r\n9.\u00a0\u00a0 \u00a0Show all products that come in boxes. Display product name and QuantityPerUnit.\r\n<div class=\"textbox shaded\">SELECT EnglishName, ProductName,\u00a0 QuantityPerUnit\r\nFROM tblProducts\r\nWHERE QuantityPerUnit like '%box%'\r\nORDER BY EnglishName<\/div>\r\n<h1><span style=\"color: #000000\">Part 3 - Insert, Update, Delete, Indexes<\/span><\/h1>\r\n1.\u00a0\u00a0 \u00a0Create an Employee table. The primary key should be EmployeeID (autonumber). Add the following fields: LastName, FirstName, Address, City, Province, Postalcode, Phone, Salary. Show the CREATE TABLE\u00a0statement and the INSERT\u00a0statements for the five\u00a0employees. Join the employee table to the tblOrders. Show the script for creating the table, setting constraints and adding employees.\r\n<div class=\"textbox shaded\">Use Orders\r\nCREATE TABLE [dbo].[tblEmployee](\r\nEmployeeID Int IDENTITY NOT NULL ,\r\nFirstName varchar (20) NOT NULL,\r\nLastName varchar (20) NOT NULL,\r\nAddress varchar (50),\r\nCity varchar(20), Province varchar (50),\r\nPostalCode char(6),\r\nPhone char (10),\r\nSalary Money NOT NULL,\r\nPrimary Key (EmployeeID)<\/div>\r\n<div class=\"textbox shaded\">Go\r\nINSERT into tblEmployees\r\nValues ('Jim', 'Smith', '123 Fake', 'Terrace', 'BC', 'V8G5J6', '2506155989', '20.12'),\r\n('Jimmy', 'Smithy', '124 Fake', 'Terrace', 'BC', 'V8G5J7', '2506155984', '21.12'),\r\n('John', 'Smore', '13 Fake', 'Terrace', 'BC', 'V4G5J6', '2506115989', '19.12'),\r\n('Jay', 'Sith', '12 Fake', 'Terrace', 'BC', 'V8G4J6', '2506155939', '25.12'),\r\n('Jig', 'Mith', '23 Fake', 'Terrace', 'BC', 'V8G5J5', '2506455989', '18.12');\r\nGo<\/div>\r\n2.\u00a0\u00a0 \u00a0Add a field to tblOrders called TotalSales. Show DDL \u2013 ALTER TABLE\u00a0statement.\r\n<div class=\"textbox shaded\">ALTER TABLE tblOrders\r\nADD Foreign Key (EmployeeID) references tblEmployees (EmployeeID)<\/div>\r\n3.\u00a0\u00a0 \u00a0Using the UPDATE\u00a0statement, add the total sale for each order based on the order details table.\r\n<div class=\"textbox shaded\">UPDATE tblOrders\r\nSet TotalSales = (select sum(unitprice*quantity*(1-discount))\r\nFROM tblOrderDetails\r\nWHERE tblOrderDetails.OrderID= tblOrders.OrderID\r\nGROUP BY OrderID<\/div>","rendered":"<p>Download the following script:\u00a0 <a title=\"Orders and Data\" href=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2014\/06\/ordersanddata.txt\">OrdersAndData.sql<\/a>.<\/p>\n<h1>Part 1 \u2013 DDL<\/h1>\n<figure id=\"attachment_1067\" aria-describedby=\"caption-attachment-1067\" style=\"width: 664px\" class=\"wp-caption aligncenter\"><a href=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2014\/10\/DD-OrdersandData.jpg\"><img loading=\"lazy\" decoding=\"async\" src=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2014\/10\/DD-OrdersandData.jpg\" alt=\"Figure C.1. ERD\" class=\"wp-image-1067 size-full\" height=\"430\" width=\"664\" srcset=\"https:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2014\/10\/DD-OrdersandData.jpg 664w, https:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2014\/10\/DD-OrdersandData-300x194.jpg 300w, https:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2014\/10\/DD-OrdersandData-65x42.jpg 65w, https:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2014\/10\/DD-OrdersandData-225x145.jpg 225w, https:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2014\/10\/DD-OrdersandData-350x226.jpg 350w\" sizes=\"auto, (max-width: 664px) 100vw, 664px\" \/><\/a><figcaption id=\"caption-attachment-1067\" class=\"wp-caption-text\">Figure C.1. ERD for Orders and Data.<\/figcaption><\/figure>\n<ol>\n<li>Use the script OrdersAndData.sql that creates the tables and adds the data for the Orders and Data ERD in Figure C.1.<\/li>\n<li>Create a database called Orders. Modify the script to integrate the PK and referential integrity. Show the CREATE TABLE\u00a0statements with the modifications including the constraints given in step 3.<\/li>\n<li>Add the following constraints:<\/li>\n<\/ol>\n<ul>\n<li>tblCustomers table:\u00a0 Country &#8211; default to Canada<\/li>\n<li>tblOrderDetails:\u00a0 Quantity &#8211;\u00a0\u00a0 &gt; 0<\/li>\n<li>tblShippers: CompanyName must be unique.<\/li>\n<li>tblOrders: ShippedDate must be greater than order date.<\/li>\n<\/ul>\n<div class=\"textbox\">CREATE DATABASE Orders<br \/>\nGo<br \/>\nUse Orders<br \/>\nGo<\/div>\n<div class=\"textbox shaded\">Use Orders<br \/>\nGo<br \/>\nCREATE TABLE [dbo].[tblCustomers]<br \/>\n[CustomerID]\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 nvarchar(5) NOT NULL,<br \/>\n[CompanyName]\u00a0\u00a0\u00a0\u00a0\u00a0 nvarchar(40) NOT NULL,<br \/>\n[ContactName]\u00a0\u00a0\u00a0\u00a0\u00a0 nvarchar(30) NULL,<br \/>\n[ContactTitle]\u00a0\u00a0\u00a0\u00a0 nvarchar(30) NULL,<br \/>\n[Address]\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 nvarchar(60) NULL,<br \/>\n[City]\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 nvarchar(15) NULL,<br \/>\n[Region]\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 nvarchar(15) NULL,<br \/>\n[PostalCode]\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 nvarchar(10) NULL,<br \/>\n[Country]\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 nvarchar(15) NULL<br \/>\nConstraint \u00a0\u00a0 \u00a0df_country DEFAULT &#8216;Canada&#8217;,<br \/>\n[Phone]\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 nvarchar(24) NULL,<br \/>\n[Fax]\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 nvarchar(24) NULL,<br \/>\nPrimary Key (CustomerID)<br \/>\n);<\/div>\n<div class=\"textbox shaded\">CREATE TABLE [dbo].[tblSupplier] (<br \/>\n[SupplierID]\u00a0\u00a0\u00a0\u00a0 int NOT NULL,<br \/>\n[Name]\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 nvarchar(50) NULL,<br \/>\n[Address]\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 nvarchar(50) NULL,<br \/>\n[City]\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 nvarchar(50) NULL,<br \/>\n[Province]\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 nvarchar(50) NULL,<br \/>\nPrimary Key (SupplierID)<br \/>\n);<\/div>\n<div class=\"textbox shaded\">CREATE TABLE [dbo].[tblShippers] (<br \/>\n[ShipperID]\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 int NOT NULL,<br \/>\n[CompanyName]\u00a0\u00a0\u00a0\u00a0 nvarchar(40) NOT NULL,<br \/>\nPrimary Key (ShipperID),&lt;<br \/>\nCONSTRAINT uc_CompanyName UNIQUE (CompanyName)<br \/>\n);<\/div>\n<div class=\"textbox shaded\">CREATE TABLE [dbo].[tblProducts] (<br \/>\n[ProductID]\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 int NOT NULL,<br \/>\n[SupplierID]\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 int NULL,<br \/>\n[CategoryID]\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 int NULL,<br \/>\n[ProductName]\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 nvarchar(40) NOT NULL,<br \/>\n[EnglishName]\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 nvarchar(40) NULL,<br \/>\n[QuantityPerUnit]\u00a0\u00a0\u00a0\u00a0 nvarchar(20) NULL,<br \/>\n[UnitPrice]\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 money NULL,<br \/>\n[UnitsInStock]\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 smallint NULL,<br \/>\n[UnitsOnOrder]\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 smallint NULL,<br \/>\n[ReorderLevel]\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 smallint NULL,<br \/>\n[Discontinued]\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 bit NOT NULL,<br \/>\nPrimary Key (ProductID),<br \/>\nForeign Key (SupplierID) References tblSupplier<br \/>\n);<\/div>\n<div class=\"textbox shaded\">CREATE TABLE [dbo].[tblOrders] (<br \/>\n[OrderID]\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 int NOT NULL,<br \/>\n[CustomerID]\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 nvarchar(5) NOT NULL,<br \/>\n[EmployeeID]\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 int NULL,<br \/>\n[ShipName]\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 nvarchar(40) NULL,<br \/>\n[ShipAddress]\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 nvarchar(60) NULL,<br \/>\n[ShipCity]\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 nvarchar(15) NULL,<br \/>\n[ShipRegion]\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 nvarchar(15) NULL,<br \/>\n[ShipPostalCode]\u00a0\u00a0\u00a0\u00a0 nvarchar(10) NULL,<br \/>\n[ShipCountry]\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 nvarchar(15) NULL,<br \/>\n[ShipVia]\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 int NULL,<br \/>\n[OrderDate]\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 smalldatetime NULL,<br \/>\n[RequiredDate]\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 smalldatetime NULL,<br \/>\n[ShippedDate]\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 smalldatetime NULL,<br \/>\n[Freight]\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 money NULL<br \/>\nPrimary Key (OrderID),<br \/>\nForeign Key (CustomerID) References tblCustomers,<br \/>\nForeign Key (ShipVia) References tblShippers,<br \/>\nConstraint valid_ShipDate CHECK (ShippedDate &gt; OrderDate)<br \/>\n);<\/div>\n<div class=\"textbox shaded\">CREATE TABLE [dbo].[tblOrderDetails] (<br \/>\n[OrderID]\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 int NOT NULL,<br \/>\n[ProductID]\u00a0\u00a0\u00a0\u00a0 int NOT NULL,<br \/>\n[UnitPrice]\u00a0\u00a0\u00a0\u00a0 money NOT NULL,<br \/>\n[Quantity]\u00a0\u00a0\u00a0\u00a0\u00a0 smallint NOT NULL,<br \/>\n[Discount]\u00a0\u00a0\u00a0\u00a0\u00a0 real NOT NULL,<br \/>\nPrimary Key (OrderID, ProductID),<br \/>\nForeign Key (OrderID) References tblOrders,<br \/>\nForeign Key (ProductID) References tblProducts,<br \/>\nConstraint Valid_Qty Check (Quantity &gt; 0)<br \/>\n);<br \/>\nGo<\/div>\n<h1>Part 2 &#8211; Create the Following SQL Statements<\/h1>\n<p>1.\u00a0\u00a0 \u00a0Show a list of customers and the orders they generated during 2014. Display customer ID, order ID, order date and date ordered.<\/p>\n<div class=\"textbox shaded\">Use Orders<br \/>\nGo<br \/>\nSELECT CompanyName, OrderID, RequiredDate as &#8216;order date&#8217;, OrderDate as &#8216;date ordered&#8217;<br \/>\nFROM tblcustomers\u00a0 JOIN tblOrders on tblOrders.CustomerID = tblCustomers.CustomerID<br \/>\nWHERE Year(OrderDate) = 2014<\/div>\n<p>2.\u00a0\u00a0 \u00a0Using the ALTER TABLE\u00a0statement, add a new field (Active) in the tblcustomer.\u00a0 Default it to True.<\/p>\n<div class=\"textbox shaded\">ALTER TABLE tblCustomers<br \/>\nADD Active bit DEFAULT (&#8216;True&#8217;)<\/div>\n<p>3.\u00a0\u00a0 \u00a0Show all orders purchased before September 1, 2012. Display company name, date ordered and total amount of order (include freight).<\/p>\n<div class=\"textbox shaded\">SELECT tblOrders.OrderID, OrderDate as &#8216;Date Ordered&#8217;, sum(unitprice*quantity*(1-discount))+ freight as &#8216;Total Cost&#8217;<br \/>\nFROM tblOrderDetails join tblOrders on tblOrders.orderID = tblOrderDetails.OrderID<br \/>\nWHERE OrderDate &lt; &#8216;September 1, 2012&#8217;<br \/>\nGROUP BY tblOrders.OrderID, freight, OrderDate<\/div>\n<p>4.\u00a0\u00a0 \u00a0Show all orders that have been shipped via Federal Shipping.\u00a0 Display OrderID, ShipName, ShipAddress and CustomerID.<\/p>\n<div class=\"textbox shaded\">SELECT OrderID, ShipName, ShipAddress, CustomerID<br \/>\nFROM tblOrders join tblShippers on tblOrders.ShipVia = tblShippers.ShipperID<br \/>\nWHERE CompanyName= &#8216;Federal Shipping&#8217;<\/div>\n<p>5.\u00a0\u00a0 \u00a0Show all customers who have not made purchases in 2011.<\/p>\n<div class=\"textbox shaded\">SELECT CompanyName<br \/>\nFROM tblCustomers<br \/>\nWHERE CustomerID not in<br \/>\n(\u00a0 SELECT CustomerID<br \/>\nFROM\u00a0 tblOrders<br \/>\nWHERE Year(OrderDate) = 2011<br \/>\n)<\/div>\n<p>6.\u00a0\u00a0 \u00a0Show all products that have never been ordered.<\/p>\n<div class=\"textbox shaded\">SELECT ProductID from tblProducts<br \/>\nExcept<br \/>\nSELECT ProductID from tblOrderDetails<\/div>\n<p>OR<\/p>\n<div class=\"textbox shaded\">SELECT Products.ProductID,Products.ProductName<br \/>\nFROM Products LEFT JOIN [Order Details]<br \/>\nON Products.ProductID = [Order Details].ProductID<br \/>\nWHERE [Order Details].OrderID IS NULL<\/div>\n<p>7.\u00a0\u00a0 \u00a0Show OrderIDs for customers who reside in London. Use a subquery.\u00a0Display CustomerID, CustomerName and OrderID.<\/p>\n<div class=\"textbox shaded\">SELECT Customers.CompanyName,Customers.CustomerID,OrderID<br \/>\nFROM Orders<br \/>\nLEFT JOIN Customers ON Orders.CustomerID = Customers.CustomerID<br \/>\nWHERE Customers.CompanyName IN<br \/>\n(SELECT CompanyName<br \/>\nFROM Customers<br \/>\nWHERE City =\u00a0&#8216;London&#8217;)<\/div>\n<p>8.\u00a0\u00a0 \u00a0Show products supplied by Supplier A and Supplier B. Display product name and supplier name.<\/p>\n<div class=\"textbox shaded\">SELECT ProductName, Name<br \/>\nFROM tblProducts JOIN tblSupplier on tblProducts.SupplierID = tblSupplier.SupplierID<br \/>\nWHERE Name Like &#8216;Supplier A&#8217; or Name Like &#8216;Supplier B&#8217;<\/div>\n<p>9.\u00a0\u00a0 \u00a0Show all products that come in boxes. Display product name and QuantityPerUnit.<\/p>\n<div class=\"textbox shaded\">SELECT EnglishName, ProductName,\u00a0 QuantityPerUnit<br \/>\nFROM tblProducts<br \/>\nWHERE QuantityPerUnit like &#8216;%box%&#8217;<br \/>\nORDER BY EnglishName<\/div>\n<h1><span style=\"color: #000000\">Part 3 &#8211; Insert, Update, Delete, Indexes<\/span><\/h1>\n<p>1.\u00a0\u00a0 \u00a0Create an Employee table. The primary key should be EmployeeID (autonumber). Add the following fields: LastName, FirstName, Address, City, Province, Postalcode, Phone, Salary. Show the CREATE TABLE\u00a0statement and the INSERT\u00a0statements for the five\u00a0employees. Join the employee table to the tblOrders. Show the script for creating the table, setting constraints and adding employees.<\/p>\n<div class=\"textbox shaded\">Use Orders<br \/>\nCREATE TABLE [dbo].[tblEmployee](<br \/>\nEmployeeID Int IDENTITY NOT NULL ,<br \/>\nFirstName varchar (20) NOT NULL,<br \/>\nLastName varchar (20) NOT NULL,<br \/>\nAddress varchar (50),<br \/>\nCity varchar(20), Province varchar (50),<br \/>\nPostalCode char(6),<br \/>\nPhone char (10),<br \/>\nSalary Money NOT NULL,<br \/>\nPrimary Key (EmployeeID)<\/div>\n<div class=\"textbox shaded\">Go<br \/>\nINSERT into tblEmployees<br \/>\nValues (&#8216;Jim&#8217;, &#8216;Smith&#8217;, &#8216;123 Fake&#8217;, &#8216;Terrace&#8217;, &#8216;BC&#8217;, &#8216;V8G5J6&#8217;, &#8216;2506155989&#8217;, &#8216;20.12&#8217;),<br \/>\n(&#8216;Jimmy&#8217;, &#8216;Smithy&#8217;, &#8216;124 Fake&#8217;, &#8216;Terrace&#8217;, &#8216;BC&#8217;, &#8216;V8G5J7&#8217;, &#8216;2506155984&#8217;, &#8216;21.12&#8217;),<br \/>\n(&#8216;John&#8217;, &#8216;Smore&#8217;, &#8217;13 Fake&#8217;, &#8216;Terrace&#8217;, &#8216;BC&#8217;, &#8216;V4G5J6&#8217;, &#8216;2506115989&#8217;, &#8216;19.12&#8217;),<br \/>\n(&#8216;Jay&#8217;, &#8216;Sith&#8217;, &#8217;12 Fake&#8217;, &#8216;Terrace&#8217;, &#8216;BC&#8217;, &#8216;V8G4J6&#8217;, &#8216;2506155939&#8217;, &#8216;25.12&#8217;),<br \/>\n(&#8216;Jig&#8217;, &#8216;Mith&#8217;, &#8217;23 Fake&#8217;, &#8216;Terrace&#8217;, &#8216;BC&#8217;, &#8216;V8G5J5&#8217;, &#8216;2506455989&#8217;, &#8216;18.12&#8217;);<br \/>\nGo<\/div>\n<p>2.\u00a0\u00a0 \u00a0Add a field to tblOrders called TotalSales. Show DDL \u2013 ALTER TABLE\u00a0statement.<\/p>\n<div class=\"textbox shaded\">ALTER TABLE tblOrders<br \/>\nADD Foreign Key (EmployeeID) references tblEmployees (EmployeeID)<\/div>\n<p>3.\u00a0\u00a0 \u00a0Using the UPDATE\u00a0statement, add the total sale for each order based on the order details table.<\/p>\n<div class=\"textbox shaded\">UPDATE tblOrders<br \/>\nSet TotalSales = (select sum(unitprice*quantity*(1-discount))<br \/>\nFROM tblOrderDetails<br \/>\nWHERE tblOrderDetails.OrderID= tblOrders.OrderID<br \/>\nGROUP BY OrderID<\/div>\n","protected":false},"author":14,"menu_order":3,"template":"","meta":{"pb_show_title":"on","pb_short_title":"","pb_subtitle":"","pb_authors":[],"pb_section_license":""},"back-matter-type":[26],"contributor":[],"license":[],"class_list":["post-329","back-matter","type-back-matter","status-publish","hentry","back-matter-type-appendix"],"_links":{"self":[{"href":"https:\/\/opentextbc.ca\/dbdesign01\/wp-json\/pressbooks\/v2\/back-matter\/329","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/opentextbc.ca\/dbdesign01\/wp-json\/pressbooks\/v2\/back-matter"}],"about":[{"href":"https:\/\/opentextbc.ca\/dbdesign01\/wp-json\/wp\/v2\/types\/back-matter"}],"author":[{"embeddable":true,"href":"https:\/\/opentextbc.ca\/dbdesign01\/wp-json\/wp\/v2\/users\/14"}],"version-history":[{"count":21,"href":"https:\/\/opentextbc.ca\/dbdesign01\/wp-json\/pressbooks\/v2\/back-matter\/329\/revisions"}],"predecessor-version":[{"id":1081,"href":"https:\/\/opentextbc.ca\/dbdesign01\/wp-json\/pressbooks\/v2\/back-matter\/329\/revisions\/1081"}],"metadata":[{"href":"https:\/\/opentextbc.ca\/dbdesign01\/wp-json\/pressbooks\/v2\/back-matter\/329\/metadata\/"}],"wp:attachment":[{"href":"https:\/\/opentextbc.ca\/dbdesign01\/wp-json\/wp\/v2\/media?parent=329"}],"wp:term":[{"taxonomy":"back-matter-type","embeddable":true,"href":"https:\/\/opentextbc.ca\/dbdesign01\/wp-json\/pressbooks\/v2\/back-matter-type?post=329"},{"taxonomy":"contributor","embeddable":true,"href":"https:\/\/opentextbc.ca\/dbdesign01\/wp-json\/wp\/v2\/contributor?post=329"},{"taxonomy":"license","embeddable":true,"href":"https:\/\/opentextbc.ca\/dbdesign01\/wp-json\/wp\/v2\/license?post=329"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}