Author: Devin Graupmann

Composite Keys use a unique combination of multiple values to identify rows.

A Natural Primary Key is a column of values in your existing data that is guaranteed to be unique for every row.

Natural Key Pitfalls

Watch out for columns that appear to hold unique values simply because they contain only a few rows.

Only choose a natural key column if it is impossible to have duplicates, not merely improbable.

Create the primary key column in the column specification with an auto-assigned constraint name

CREATE TABLE customers (
    customer_id char(5) NOT NULL PRIMARY KEY,
    company     varchar(100),
    address     varchar(100),
    city        varchar(50),
    state       char(2),
    zip         char(5)
);

Add the primary key constraint when creating the table with a namable constraint

CREATE TABLE customers (
    customer_id char(5) NOT NULL,
    company     varchar(100),
    address     varchar(100),
    city        varchar(50),
    state       char(2),
    zip         char(5),
    CONSTRAINT PK_Customers_CustomerID PRIMARY KEY (customer_id)
);

Add the primary key after the table is created

CREATE TABLE customers (
    customer_id char(5) NOT NULL,
    company     varchar(100),
    address     varchar(100),
    city        varchar(50),
    state       char(2),
    zip         char(5)
);

ALTER TABLE customers
   ADD CONSTRAINT PK_Customers_CustomerID PRIMARY KEY (customer_id);

[Datatypes in MSSQL]

[Datatypes in PostgresSQL]

MSSQL Date and time data type precision

-- Store dates and times in SQL Server
CREATE TABLE TimeExperiment (
    a datetimeoffset, --stores timezone, stores time down to 100 nanoseconds
    b datetimeoffset(0) -- round times to the nearest second
);

INSERT INTO TimeExperiment
    VALUES
        (getdate(), getdate())
;

SELECT * FROM TimeExperiment;

MSSQL Date Formats

-- Dates and time values can be entered in a variety of ways
INSERT INTO TimeExperiment (a)
    VALUES
        ('2020-01-02'), -- Standard date format
        ('Jan 2, 2020'), -- Can also use text abbreviations
        ('2 January, 2020 14:25'), -- Combine date and time
        ('2 Jan, 2020 2:25 PM') -- Specify PM when entering afternoon times in 12 hour format
;

SELECT * FROM TimeExperiment;

PostgresSQL Date and time data type precision

-- Store dates and times in PostgreSQL
CREATE TABLE TimeExperiment (
    a timestamp with time zone, -- store to 100 nanoseconds
    b timestamp (0) with time zone -- store to seconds
);

INSERT INTO TimeExperiment
    VALUES
        (now(), now())
;

SELECT * FROM TimeExperiment;

PostgresSQL Date Formats

-- Dates and time values can be entered in a variety of ways
INSERT INTO TimeExperiment (a)
    VALUES
        ('2020-01-02'), -- Standard date format
        ('Jan 2, 2020'), -- Can also use text abbreviations
        ('2 January, 2020 14:25'), -- Combine date and time
        ('2 Jan, 2020 2:25 PM') -- Specify PM when entering afternoon times in 12 hour format
;

SELECT * FROM TimeExperiment;

[Datebase Foundations: Data Structures]

[What are the MSSQL database functions?]

[What are the PostgresSQL database functions?]

Remember the HAVING keyword only applies to groups. The WHERE keyword only applies to rows.

Queries can use alias on table names and column names. The AS keyword is optional.

SELECT p.ProductName AS "Product Name",
    c.CategoryDescription AS "Category Description"
FROM products.products AS p
    JOIN products.categories AS c
        ON p.CategoryID = c.CategoryId
;

Strings can be converted to a result set.

SELECT 'Adam' AS "Name";

Some common math functions in SQL

SELECT
    MAX(Price) AS "Maximum Price",
    MIN(Price) AS "Minimum Price",
    AVG(Price) AS "Average Price"
FROM products.products;

SQL columns can use math operators.

SELECT SKU,
    ProductName,
    CategoryID,
    Size,
    Price,
    '8.5%' AS TaxRate,
    Price * 0.085 AS SalesTax,
    Price + (Price * 0.085) AS TotalPrice,
    ROUND((Price * 1.085), 2) AS TotalPrice
FROM products.products;

Select queries can be nested.

SELECT ProductName, Size, Price
FROM products.products
WHERE Price =
    (SELECT MAX(Price)
    FROM products.products)
;

Make a query show unique rows.

SELECT DISTINCT
    categories.CategoryDescription
FROM products.products
    JOIN products.categories
        ON products.CategoryID = categories.CategoryID
;

Use aggregates to count the number of records.

SELECT
    categories.CategoryDescription,
    COUNT(products.SKU) as "Number of Skus"
FROM products.products
    JOIN products.categories
        ON products.CategoryID = categories.CategoryID
GROUP BY CategoryDescription
ORDER BY "Number of Skus" DESC
;

Aggregate functions operate on each group individually.

SELECT
    categories.CategoryDescription,
    COUNT(products.SKU) as "Number of Skus",
    MAX(products.Price) AS "Maximum Price",
    MIN(products.Price) AS "Minimum Price",
    AVG(products.Price) AS "Average Price"
FROM products.products
    JOIN products.categories
        ON products.CategoryID = categories.CategoryID
GROUP BY CategoryDescription
ORDER BY "Number of Skus" DESC
;

The HAVING keyword filters aggregate results.

SELECT categories.CategoryDescription,
    COUNT(products.SKU) AS "Number of SKUs",
    MAX(products.Price) AS "Maximum Price",
    MIN(products.Price) AS "Minimum Price",
    AVG(products.Price) AS "Average Price"
    FROM products.products
    JOIN products.categories
    ON products.CategoryID = categories.CategoryID
WHERE products.Price > 15
GROUP BY CategoryDescription
HAVING CategoryDescription = 'Olive Oils'
--HAVING NOT CategoryDescription = 'Bath and Beauty'
--HAVING COUNT(products.SKU) < 10
ORDER BY COUNT(products.SKU) DESC
;

Query to return all rows from a table.

SELECT * FROM products.products

Query to return some rows from a table

SELECT * FROM products.products
WHERE CategoryID = 1;

Query that joins with another table to include related columns

SELECT *
FROM products.products
    JOIN products.categories
       ON products.CategoryID = categories.CategoryID
WHERE SKU = 'ALB008';

Add three records.

INSERT INTO HumanResources.Employees VALUES
    (2,'Tim', 'Graupmann', 'IT', '20211028'),
    (3,'Linae', 'Graupmann', 'IT', '20211028'),
    (4,'Logan', 'Graupmann', 'IT', '20211028')
;

Promote an employee.

UPDATE HumanResources.Employees
SET Department = 'IT Admin'
WHERE EmployeeID = 1
;

Retire an employee.

DELETE FROM HumanResources.Employees
WHERE EmployeeID = 3
;

Insert a single record with the default columns.

INSERT INTO products.products VALUES
('FCP008','First Cold Press', 1,8,8.99)
;

Insert multiple records with the default columns.

INSERT INTO products.products VALUES
    ('BI008','Basil-Infused EVO', 2, 8, 10.99),
    ('GI016','Garlic-Infused EVO', 2, 16, 15.99)
;

Insert with named columns.

INSERT INTO products.products
    (SKU, ProductName, Price)
VALUES
    ('OGEC004', 'Olive Glow Eye Cream', 18.99)
;

Update fields for a specific row.

UPDATE products.products
SET CategoryID = 3,
    Size = 4
WHERE SKU = 'OGEC004';

Delete a specific row.

DELETE FROM products.products
WHERE SKU = 'OGEC004';

Create the database.

CREATE DATABASE TwoTrees;

Create the schema.

CREATE SCHEMA products;

Create the products table.

CREATE TABLE products.products (
    SKU CHAR(7) NOT NULL,
    ProductName CHAR(50) NOT NULL,
    CategoryID INT,
    Size INT,
    Price DECIMAL(5,2) NOT NULL);

Create the categories table.

CREATE TABLE products.categories (
    CategoryID INT PRIMARY KEY,
    CategoryDescription CHAR(50),
    ProductLine CHAR(25)
);