# 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;
```


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://eleven-fifty-academy.gitbook.io/sql-101-basics/part-2-postgresql/2.12-solutions.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.