1.12: Solutions

1.1 Create Database Solution

To create a new database called "SQL_Library" using SQL, enter the following code into a SQL query:

CREATE DATABASE SQL_Library
GO

1.2 Drop Database Solution

Delete the SQL_Library database if it exists.

USE master;  
GO  

IF EXISTS(SELECT * from sys.databases WHERE name='SQL_Library')  
BEGIN  
    DROP DATABASE SQL_Library;  
END

1.3 Create Table Solution

To create a Products table, enter the following code in a SQL query:

CREATE TABLE dbo.Products
   (ProductID int PRIMARY KEY NOT NULL, 
    ProductName varchar(25) NOT NULL,
    Price money NULL,
    ProductDescription text NULL);

Note: Unlike some query languages, T-SQL does not require a semi-colon at the end of a statement. Therefore, the semi-colon shown in the example above is optional. You may also notice that SQL keywords are not case-sensitive. For example, "create table" would produce the same result as "CREATE TABLE," as shown above.

1.4 Insert Solution

--Using standard syntax
INSERT dbo.Products (ProductID, ProductName, Price, ProductDescription)
    VALUES (1, 'Clamp', 12.48, 'Workbench clamp');

--Changing the order of the columns
INSERT dbo.Products (ProductName, ProductID, ProductDescription, Price)
    VALUES ('Clamp', 2, 'Workbench clamp', 12.48);

--Skipping the column list, but keeping the values in order 
INSERT dbo.Products
    VALUES (50, 'Flat Head Screwdriver', 3.25, 'Flat head');

--Adding multiple items in the same statement
INSERT Products
    VALUES (51, 'Screwdriver', 3.25, 'Flat head'),
    (52, 'Screwdriver', 3.17, 'Flat head');

--Dropping the ProductDescription column
INSERT Products (ProductID, ProductName, ProductDescription)
    VALUES (75, 'Tire Bar', 'Tool for changing tires');

Note: You may notice that the last two examples do not include "dbo." in front of "Products." Adding "dbo." in front of the table name is optional in some query languages, including T-SQL.

1.5 Update Solution

--Change the ProductName by targeting the ProductID
UPDATE Products  
    SET ProductName = 'The Flattest Flat Head'  
    WHERE ProductID = 50; 

--Change the price of a specific ProductID
UPDATE Products
    SET Price = '12.75'  
    WHERE ProductID = 1;

--Change anything with a specific price to a new price
UPDATE Products
    SET Price = '3.50'  
    WHERE Price = '3.25';

1.6 Read Solution

--Print all products using standard syntax
SELECT ProductID, ProductName, Price, ProductDescription
    FROM Products;

--Print all products using *
SELECT * FROM PRODUCTS

--Print ProductName and Price columns
SELECT ProductName, Price
    FROM PRODUCTS;

--Print all Products with a ProductID less than 60
SELECT * FROM PRODUCTS
    WHERE ProductID < 60;

--Print all Products with a ProductID between 50 and 55 using BETWEEN and AND
SELECT * FROM PRODUCTS
    WHERE ProductID BETWEEN 50 AND 55;

--Print all Products with a ProductDescription that starts with Tool using % and LIKE
SELECT * FROM PRODUCTS
    WHERE ProductDescription LIKE 'Tool%';

--Print all ProductNames and Prices including a 7% tax in CustomerPays column
SELECT ProductName, ((.07 * Price) + Price) AS CustomerPays
    FROM PRODUCTS;

1.7 Alter Solution

Add a column called "Manufacturer":

ALTER TABLE Products
  ADD Manufacturer VARCHAR(25) NULL;

Drop the "Manufacturer" column:

ALTER TABLE Products
  DROP COLUMN Manufacturer;

Add a column called "UPC" with data type VARCHAR(25):

ALTER TABLE Products
  ADD Manufacturer VARCHAR(25) NULL;

Change the "UPC" column to a TEXT data type:

ALTER TABLE Products
    ALTER COLUMN UPC TEXT;

Drop the "UPC" column:

ALTER TABLE Products
  DROP COLUMN UPC;

1.8 Customer Table Solution

Create a "Customers" table with the specified criteria:

CREATE TABLE Customers (
    CustomerID int PRIMARY KEY NOT NULL,
    LastName varchar(25) NOT NULL,
    FirstName varchar(25) NOT NULL,
    LastPurchase datetime NOT NULL
);

Seed table with specified data:

INSERT Customers (CustomerID, LastName, FirstName, LastPurchase)
    VALUES (1, 'Tinney', 'Zach', '2017-05-01 10:17:00'),
    (2, 'Wainscott', 'Chris', '2017-06-02 08:12:00'),
    (3, 'Williams', 'Jenn', '2017-06-05 09:23:00'),
    (4, 'O''Connor', 'Paul', '2017-07-12 11:48:00'),
    (5, 'Rahimzadeh', 'Auri', '2018-01-08 14:34:00'),
    (6, 'Fancher', 'Dave', '2017-03-06 20:56:00'),
    (7, 'Handshoe', 'James', '2017-04-24 19:09:00');

This is what your resulting table should look like:

Table with seed data in Visual Studio

1.9 Diagramming Notation Solution

Diagramming Notation Solution Table

1.10 Foreign Keys Solution

Purchase Table Solution

Copy and paste the code below into a new query. You'll need it for the next lesson.

CREATE TABLE dbo.Purchases (
    PurchaseID INT IDENTITY (1, 1) NOT NULL,
    ProductID INT NOT NULL,
    CustomerID INT NOT NULL,
    Quantity   INT NULL,
    CONSTRAINT [PK_dbo.Purchases] PRIMARY KEY CLUSTERED ([PurchaseID] ASC),
    CONSTRAINT [FK_dbo.Purchase_dbo.Customer_ CustomerID] FOREIGN KEY ([CustomerID])
        REFERENCES [dbo].[Customers] ([CustomerID]) ON DELETE CASCADE,
    CONSTRAINT [FK_dbo.Purchases_dbo.Products_ProductID] FOREIGN KEY ([ProductID])
        REFERENCES [dbo].[Products] ([ProductID]) ON DELETE CASCADE
);

GO
CREATE NONCLUSTERED INDEX [IX_ProductID]
    ON [dbo].[Purchase]([ProductID] ASC);

GO
CREATE NONCLUSTERED INDEX [IX_ProductID]
    ON [dbo].[Purchase]([ProductID] ASC);

1.11 Join Solution

This is what your query should look like:

SELECT Customers.CustomerID, Customers.FirstName, Purchases.PurchaseID
FROM Purchases
INNER JOIN Customers ON Purchases.CustomerID=Customers.CustomerID;