Search
Relational Database and SQL

Note: SQL in the MySQL / MariaDB dialect

Relational Database

  • Database:
    • A set of two-dimensional tables
    • Each table has
      • Columns, known as fields
      • Rows, known as records
  • Relational:
    • Field values may refer to other records, in the same table or in another table
    • Therefore, tables (entities in the tables) have relations to one another

RDBMSF

  • Relational Database Management Systems:
    • Commercial, e.g. SQL Server, MS Access, Oracle, Sybase, Informix,…
    • Open source: MySQL/MariaDB, PostgreSQL
    • Server vs. file-based (MS Access, Filemaker, SQLite)
  • Most support standard SQL (Structured Query Language)
    • Dialects of the same language

Relational Database

Table values and “relational”

  • Example tables\, keys\, and relations: Products and Categories

Types of relations:

  1. Many-to-one, or One-to-many

  2. One-to-one

  3. Many-to-many

SQL

  • SQL: Structured Query Language
    • Standard language to work with databases
    • Supported by most relational database management systems (RDBMS) such as MySQL
    • DDL: Data Definition Language
      • Statements to create\, drop database
      • Statements to create\, alter\, drop tables
    • DML: Data Manipulation Language
      • Statements to _select_ \, _insert_ \, _update_ \, and _delete_ records in database tables

DDL: Data Definition Language

To create/define the products table:

create table products (
    productID int not null auto_increment primary key, 
    categoryID int, productCode varchar(10) not null, 
    productName varchar(255) not null, 
    listPrice decimal(10,2) not null
);

Common MySQL data types

  • String/text types: CHAR, VARCHAR
  • Numerical types: INT, DECIMAL
  • Date and time: DATE, TIME, DATETIME

DML: INSERT

To insert a new record into an existing table:

insert into products (categoryID, productCode, productName, listPrice)
            values   (1, 'gtr', 'Fender Guitar', 200.00);

insert into products (categoryID, productCode, productName, listPrice)
            values   (3, 'drm', 'Tama Drum', 259.99);

DML: SELECT

To retrieve all rows and columns from a table:

select * from products;

To retrieve rows (records) with a condition, use WHERE:

select * from products where categoryID = 2;

To retrieve selected columns and rows, in a specified order:

select productName, listPrice
from products
where listPrice < 500
order by listPrice ASC;

More on SQL Retrieval and Filtering

Sort by Price 

select productID, productName, listPrice
from products
order by listPrice;

Limit the price range 

select productID, productName, listPrice
from products
where listPrice < 450
order by listPrice;

Change column names (alias) with AS 

select  productID, 
        productName as name, 
        listPrice as price
from products
where listPrice < 450
order by listPrice;

The same without AS 

select  productID, 
        productName name, 
        listPrice price
from products
where listPrice < 450
order by listPrice;

Limit the number of rows to retrieve

To retrieve the first 3 rows:

select productID, productName
from products
limit 3;

Limit with offset 0 (first row)

To retrieve the SAME first 3 rows:

select productID, productName
from products
limit 0, 3;

Limit with offset 1 (second row)

to retrieve 3 rows starting at the second row:

select productID, productName
from products
limit 1, 3;

WHERE filtering

Use the WHERE clause with

  • Conditions
  • Comparison operators, e.g. =, >, <, <=, >=, <>

A compound condition 

select productName, listPrice, discountPercent, dateAdded
from products
where    dateAdded > '2010-07-01' 
     OR  listPrice < 500 
     AND discountPercent > 25;

Compound condition with parentheses 

select productName, listPrice, discountPercent, dateAdded
from products
where 
    (dateAdded > '2010-07-01' OR listPrice < 500) 
    AND discountPercent > 25;

Retrieve all rows 

select orderID, orderDate, shipDate
from orders;

Retrieve rows with NULL shipDate (not shipped yet) 

select orderID, orderDate, shipDate
from orders
where shipDate is null;

Retrieve shipped orders (not null) 

select orderID, orderDate, shipDate
from orders
where shipDate is not null;

Sorting with ORDER BY

Sort by:

  • One or multiple columns
  • In ASC or DESC orders

Sort by productName 

select productName, listPrice, discountPercent
from products
where listPrice < 500
order by productName

Sort by listPrice, in the DESC order 

select productName, listPrice, discountPercent
from products
where listPrice < 500
order by listPrice DESC

Sort by two columns 

select productName, listPrice, discountPercent
from products
where categoryID = 1
order by discountPercent, listPrice DESC

SQL JOIN

A powerful tool to get data from multiple tables:

  • Join the tables based on their relations
  • Pull data from the tables

Joining customers and orders tables 

select firstName, lastName, orderDate
from    customers
        join orders
             on customers.customerID = orders.customerID
order by orderDate;

Joining tables with aliases 

select firstName, lastName, orderDate
from    customers c
        join orders o
             on c.customerID = o.customerID
order by orderDate;

Joining four tables with aliases 

SELECT firstName, lastName, o.orderID, 
       productName, itemPrice, quantity
FROM customers c
   JOIN orders o
      ON c.customerID = o.customerID
   JOIN orderItems oi
      ON o.orderID = oi.orderID
   JOIN products p
      ON oi.productID = p.productID
ORDER BY o.orderID;

Aggregation and Group By

Aggregate functions in SQL:

  • AVG(), SUM(), COUNT()
  • MIN(), MAX(), ...

Count all products 

select count(*) as productCount
from products

Count all orders and shipped ones 

select count(*) as totalCount, 
       count(shipDate) as shippedCount
from orders

Note count(shipDate) only counts Non-Null ship dates.

Find lowest (min), highest (max) and average prices 

select min(listPrice) as lowestPrice, 
       max(listPrice) as highestPrice,
       avg(listPrice) as averagePrice
from products;

Get the total value of discounted orders 

select sum(itemPrice * quantity - discountAmount) as ordersTotal
from orderItems

Aggregate within groups (GROUP BY)

Average list price by category:

select categoryID, 
       count(*) as productCount, 
       avg(listPrice) as averageListPrice
from products
group by categoryID
order by productCount

Joining and Filtering based on aggregates (HAVING) 

select categoryName, 
       count(*) as productCount, 
       avg(listPrice) as averageListPrice
from products p join categories c
        on p.categoryID = c.categoryID
group by categoryName
having averageListPrice > 400;

Filter rows BEFORE grouping (WHERE) 

select categoryName, 
       count(*) as productCount, 
       avg(listPrice) as averageListPrice
from products p join categories c
        on p.categoryID = c.categoryID
where listPrice > 400
group by categoryName

Subquery

Retrieve product with a higher than average price:

select productName, listPrice
from products
where listPrice > (SELECT AVG(listPrice) FROM products)
order by listPrice DESC;

Review of SQL Insert, Update, and Delete

Insert

Insert one row using a column list:

INSERT INTO products
    (categoryID, productCode, productName, description, listPrice, dateAdded)
VALUES
    (1, 'tele', 'Fender Telecaster', 'NA', '949.99', NOW())

Insert one row without column list:

INSERT INTO products
VALUES (DEFAULT, 1, 'tele', 'Fender Telecaster', 'NA', 
        '949.99', DEFAULT, NOW())

Insert multiple rows:

INSERT INTO categories 
       (categoryID, categoryName) 
VALUES (4, 'Keyboards'),
       (5, 'Brass'),
       (6, 'Woodwind')

Update

Update one column:

UPDATE products
SET discountPercent = '10.00'
WHERE productName = 'Fender Telecaster'

Update multiple columns:

UPDATE products
SET discountPercent = '25.00', 
    description = 'This guitar has great tone and smooth playability.'
WHERE productName = 'Fender Telecaster'

Delete

Delete one row:

DELETE FROM products
WHERE productID = 6

Delete multiple rows:

DELETE FROM products
WHERE categoryID = 3

CAUTION

if you do this:

delete from products

All your data in products will be gone.

References