> For the complete documentation index, see [llms.txt](https://eleven-fifty-academy.gitbook.io/sql-101-basics/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://eleven-fifty-academy.gitbook.io/sql-101-basics/part-2-postgresql/2.12-solutions.md).

# 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:

```sql
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](/files/-LAU8ogu5ptstQNQaoJf)

## 2.4 Insert Solution

```sql
--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

```sql
--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

```sql
--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":

```sql
ALTER TABLE Products
  ADD Manufacturer VARCHAR(25) NULL;
```

Drop the "Manufacturer" column:

```sql
ALTER TABLE Products
  DROP COLUMN Manufacturer;
```

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

```sql
ALTER TABLE Products
  ADD Manufacturer VARCHAR(25) NULL;
```

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

```sql
ALTER TABLE Products
    ALTER COLUMN UPC TYPE TEXT;
```

Drop the "UPC" column:

```sql
ALTER TABLE Products
  DROP COLUMN UPC;
```

## 2.8 Customer Table Solution

Create a "Customers" table with the specified criteria:

```sql
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:

```sql
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:&#x20;

![Table with seed data in pgAdmin](/files/-LAU8ozJsa1QROpzJsV9)

## 2.9 Diagramming Notation Solution

![Diagramming Notation Solution Table](/files/-LAU8p0WVWfVrQMsKC8r)

## 2.10 Foreign Keys Solution

![Purchase Table Solution](/files/-LAU8p2RrIm4seyfwmrQ)

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

```sql
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:

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