2.12: Solutions

2.3 Create Table Solution

To create a table for Products with the specifications outlined in the challenge, enter the following code in a SQL query:

CREATE TABLE Products (
    ProductID int PRIMARY KEY NOT NULL,
    ProductName varchar(25) NOT NULL,
    Price money NULL, -- you can add notes to your queries
    ProductDescription text NULL
);

This is what the resulting view will look like in pgAdmin after you've run the query:

Query View in pgAdmin

2.4 Insert Solution

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

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

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

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

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

2.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';

2.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;

2.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 TYPE TEXT;

Drop the "UPC" column:

ALTER TABLE Products
  DROP COLUMN UPC;

2.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 timestamp NOT NULL
);

Seed table with specified data:

INSERT INTO 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 pgAdmin

2.9 Diagramming Notation Solution

Diagramming Notation Solution Table

2.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 public.Purchases (
    PurchaseID integer NOT NULL,
    ProductID integer NOT NULL,
    CustomerID integer NOT NULL,
    Quantity integer,
    CONSTRAINT Purchases_pkey PRIMARY KEY (PurchaseID),
    CONSTRAINT Purchases_CustomerID_fkey FOREIGN KEY (CustomerID) 
        REFERENCES public.Customers (CustomerID) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE CASCADE,
    CONSTRAINT Purchases_ProductID_fkey FOREIGN KEY (ProductID)
        REFERENCES public.Products (ProductID) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE CASCADE
)
WITH (OIDS = FALSE)
TABLESPACE pg_default;

2.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;