sql and databases

What is a database.

A database is an organized collection of data stored electronically.

What is SQL

SQL stands for structured query language. It is used for storing data, retrieving data, updating data and deleting data.

Schema in SQL

A schema is a logical container or namespace inside a database that groups related objects like tables, views, indexes, stored procedures, functions and sequences.
You can think of a schema as a folder inside a database helping you organize and manage objects.

Why use schemas.

Schemas help organize large datasets into sections, control access to different parts of the database and avoiding name conflicts,

Creating a schema.

CREATE SCHEMA sales_data;

this creates a schema named sales_data.

Setting search path.

set search_path to sales_data;

this sets the search path to the schema. To check what schema you are using show search_path;

Creating a table in that schema.

CREATE TABLE customers (
    customer_id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    city VARCHAR(100));

this creates a table named customers.

Types of Database management systems(dbms).

DBMS means Database management system. It is a software that allows us to create, manage and interact with databases.

Types of databases.

• Relational database - uses tables with rows and columns. examples: PostgreSQL, MySQL, Oracle.
• NoSQL DBMS - non tabular (key-value, Json, Graph) examples: MongoDB, Cassandra.
• In-Memory DBMS - stores data in RAM. example: Redis
• Cloud DBMS - cloud-hosted & managed by providers. examples: Amazon RDS, Azure PostreSQL

SQL datatypes

Data types define the type of data that can be stored in a column. They enforce data integrity and data accuracy.

Main data types categories.

1. Numeric Types

• Integer/int - whole numbers e.g. 10,50,-4
• Serial - Auto-incrementing ID e.g. 1,2,3
• Numeric - precision numbers (money) e.g. 12345.76
• Decimal - same as numeric - 146505.95

2. Character Types

• CHAR - fixed length string e.g. 'Kenya'
• VARCHAR - variable-length string e.g. 'John Kamau'
• TEXT - unlimited string length e.g. long comments, emails

3. Date & Time Types

• DATE - stored date only e.g. '2020-01-01'
• TIMESTAMP - stores date and time e.g.'2024-10-10 12:20:00'
• TIME - time of the day e.g. '13:30:00'

4. Boolean

• Boolean - True or False e.g. True or False

Creating tables in SQL

Basic syntax

A table consists of rows and columns

create table customers(
Customer_id serial primary key,
First_name varchar(50) not null,
Last_name varchar (50) not null,
Email varchar (100) unique not null,
Phone char(13) );

Constraints

• PRIMARY KEY - uniquely identifies each record
• NOT NULL - cannot be empty
• UNIQUE - all values must be different
• DEFAULT - provides a default value

• FOREIGN KEY - links to another table's primary key

  Best practices

• Use singular table names e.g. employees

• Define primary keys

• Use meaningful column names

• Prefer VARCHAR over CHAR unless fixed

• Use foreign keys for relational integrity

  Inserting Data into a database.

INSERT INTO customers (First_name, Last_name, Email, Phone) 
VALUES   
('John', 'Doe', '[e-mail]', '[phone]'), 
('Jane', 'Smith', '[e-mail]', '[phone]'), 
('Paul', 'Otieno', '[e-mail]', '[phone]'), 
('Mary', 'Okello', '[e-mail]', '[phone]');

Viewing and querying Tables.

Viewing databases

-- from postgreSQL

SELECT datname
FROM pg_database
WHERE datistemplate = false;

-- from MySQL

SHOW DATABASES;

Viewing all tables in PostgreSQL

SELECT table_name 
FROM schema_name.tables
WHERE table_schema = 'sales_data';

View data from a table.

SELECT *
FROM customers;

Table manipulation and common SQL key words.

Add a column to a table

ALTER TABLE customers  
ADD COLUMN city VARCHAR(100); 

this adds a new city column to the customers table.

Updating existing data

Updating a single row

UPDATE customers  
SET city = 'Nairobi'  
WHERE customer_id = 1; 

Updating multiple rows with a CASE statement.

UPDATE customers  
SET city = CASE customer_id  
WHEN 1 THEN 'Nairobi'  
WHEN 2 THEN 'Mombasa'  
WHEN 3 THEN 'Kisumu'  
WHEN 4 THEN 'Nakuru'  
ELSE city  -- Retain original city if no match 
END; 

Updating specific quantity

UPDATE orders  
SET quantity = CASE order_id  
WHEN 1 THEN 2  
WHEN 2 THEN 1  
WHEN 3 THEN 3  
WHEN 4 THEN 2  
WHEN 5 THEN 1  
ELSE quantity 
END; 

Updates orders with specific quantity.

Delete columns from SQL

Remove column from a table.

ALTER TABLE books  
DROP COLUMN published_date;

Delete rows from SQL

Delete a specific row

DELETE FROM orders  
WHERE order_id = 3; 

Drop a table

DROP TABLE books; 

Deletes the entire table and all its data. Use with caution!!

Rename column

ALTER TABLE customers RENAME COLUMN phone_number TO contact_number;

Renames a column.

MODIFY COLUMN TYPE - Change a Column’s Data Type

ALTER TABLE orders  
ALTER COLUMN quantity TYPE DECIMAL(5,2);

SET DEFAULT Value for Column

ALTER TABLE orders  
ALTER COLUMN quantity SET DEFAULT 1;

DROP DEFAULT Value

ALTER TABLE orders  
ALTER COLUMN quantity DROP DEFAULT;

Add a NOT NULL Constraint

ALTER TABLE customers  
ALTER COLUMN email SET NOT NULL;

Drop a NOT NULL Constraint

ALTER TABLE customers  
ALTER COLUMN email DROP NOT NULL;

Add a Foreign Key Constraint

ALTER TABLE orders 
ADD CONSTRAINT fk_customer 
FOREIGN KEY (customer_id) 
REFERENCES customers(customer_id);

Drop a Foreign Key Constraint

You must know the constraint name. You can find it using:

SELECT conname  
FROM pg_constraint  
WHERE conrelid = 'orders'::regclass;

Then drop it:

ALTER TABLE orders DROP CONSTRAINT fk_customer;

COMMON SQL KEYWORDS

1. SELECT - Retrieve Data

SELECT column1, column2 FROM table_name;
Examples:

SELECT customer_name, city FROM customers; 
SELECT title, author FROM books; 
SELECT customer_id, book_id FROM orders; 

2. WHERE - Filter Data

SELECT column FROM table WHERE condition;
Examples:

SELECT * FROM customers WHERE city = 'Nairobi'; 
SELECT title, price FROM books WHERE price > 2000; 
SELECT * FROM orders WHERE customer_id = 1; 

3. ORDER BY - Sort Data

SELECT columns FROM table ORDER BY column ASC|DESC;
Examples:

SELECT title, price FROM books ORDER BY price ASC; 
SELECT customer_name FROM customers ORDER BY customer_name ASC; 

SELECT * FROM orders ORDER BY order_date DESC; 

4. GROUP BY - Group and Summarize Data

SELECT column, AGG_FUNCTION(column) FROM table GROUP BY column;
Examples:

SELECT author, COUNT(*) FROM books GROUP BY author; 
SELECT customer_id, COUNT(*) FROM orders GROUP BY customer_id; 

SELECT city, COUNT(*) FROM customers GROUP BY city;

5. HAVING - Filter After Grouping

SELECT column, COUNT() FROM table GROUP BY column HAVING COUNT() > 1;
Examples:

SELECT author, COUNT(*) FROM books GROUP BY author HAVING COUNT(*) > 1; 

SELECT customer_id, COUNT(*)
FROM orders GROUP BY customer_id HAVING COUNT(*) 
> 1; 

6. LIMIT - Restrict Number of Results

SELECT * FROM table LIMIT number;
Examples:

SELECT * FROM books LIMIT 3;

SELECT title, price FROM books 
ORDER BY price DESC LIMIT 2; 

SELECT * FROM customers LIMIT 5;

Summary

• SELECT -Retrieve data
• WHERE - Filter records
• ORDER BY - Sort records
• GROUP BY - Group data
• HAVING - Filter grouped data
• LIMIT - Restrict number of results
• INSERT - Add data
• UPDATE - Modify existing data
• DELETE - Remove rows
• ALTER - Modify table structure

SQL Aggregate Functions

• COUNT() Counts the number of rows (or non-NULL values)

SELECT COUNT(*) AS kisumu_customers FROM customer 
WHERE city = 'Kisumu'; 

• SUM() Adds up numeric values in a column

SELECT SUM(price) AS total_book_price FROM books;

• AVG() Calculates the average (mean) of numeric values

SELECT AVG(price) AS avg_price_2023 FROM books 
WHERE published_date >= '2023-01-01';

• MAX() Finds the highest value in a column

SELECT MAX(order_date) AS latest_order FROM orders;

• MIN() Finds the lowest value in a column

SELECT MIN(quantity) AS min_quantity FROM orders;

SQL Operators

1. Arithmetic Operators - Used for mathematical calculations.

• Addition SELECT title, price, price + 200 AS new_price FROM books;
• Subtraction SELECT book_name, price, price - 150 AS discounted_price FROM books;
• Multiplication SELECT book_name, price, price * 2 AS double_price FROM books; / Division SELECT book_name, price, price / 2 AS half_price FROM books; % Modulo (Remainder) SELECT book_name, price, price % 3 AS remainder FROM books;

2. Comparison Operators - Used to compare values, often in WHERE.

• = Equals - Selects rows where a column matches an exact value.
  SELECT * FROM books WHERE author = 'David Kimani';

• != or <> Not equal to - Selects rows where a column value does not match the specified value.
  SELECT * FROM orders WHERE order_date <> '2023-04-01';

• Greater than - Selects rows where column value is greater than the given value.
  SELECT * FROM orders WHERE order_date > '2023-04-03';

• < Less than - Selects rows where column value is less than the given value.
  SELECT * FROM orders WHERE order_date < '2023-04-04';

• = Greater than or equal to - Selects rows where column value is greater than or equal to the given value.
  SELECT * FROM customer WHERE customer_id >= 2;

• <= Less than or equal to - Selects rows where column value is less than or equal to the given value.
  SELECT * FROM books WHERE price <= 2200;

• BETWEEN Between two values - Selects rows with column values between two values (inclusive).
  SELECT * FROM orders WHERE order_date BETWEEN '2023-04-01' AND '2023-04-03';

• LIKE Pattern matching - Use LIKE to search for patterns in text data.
  SELECT * FROM customer WHERE email LIKE '%gmail.com';

• IN Matches values in a list - Use IN to filter records by matching any value in a list.
  SELECT * FROM books WHERE author IN ('David Kimani', 'Grace Achieng');

3. Logical Operators - combine multiple conditions.

• AND - All conditions must be true
  SELECT * FROM customer
WHERE city = 'Kisumu' AND first_name = 'Paul';

• OR - At least one condition is true
  SELECT * FROM customer
WHERE city = 'Nairobi' OR city = 'Kisumu';

• NOT - Reverses the result of a condition
  SELECT * FROM orders
WHERE NOT order_date = '2023-04-01';

4. Bitwise Operators - Used for bit-level operations.

• Bitwise AND
• Bitwise OR
• Bitwise XOR

5. Set Operators - Used to combine result sets of two SELECT queries.

• UNION - Combines results, removes duplicates

SELECT first_name AS name FROM customer 
UNION 
SELECT author AS name FROM books;

• UNION ALL - Combines results, keeps duplicates

SELECT first_name AS name FROM customer 
UNION ALL 
SELECT author AS name FROM books; 

• INTERSECT - Returns common records

SELECT first_name AS name FROM customer 
INTERSECT 
SELECT author AS name FROM books; 

• EXCEPT - Returns records in first query, not in second

SELECT first_name AS name FROM customer 
EXCEPT 
SELECT author AS name FROM books;

6. Other Useful Operators

IS NULL - Checks for missing values

SELECT * FROM customer 
WHERE city IS NULL; 

IS NOT NULL - Checks for non-missing values

SELECT * FROM customer 
WHERE city IS NOT NULL; 

DISTINCT - Removes duplicates

SELECT DISTINCT city FROM customer; 

Aggregate Functions

1. COUNT() - Counting Rows

The COUNT() function counts the number of rows that match a condition.

SELECT COUNT(*) AS kisumu_customers FROM customer 
WHERE city = 'Kisumu'; 

2. SUM() - Total of Numbers

SUM() adds up the values in a numeric column.

SELECT SUM(price) AS total_book_price FROM books; 

3. AVG() - Average Value

AVG() calculates the average (mean) of values in a numeric column.

SELECT AVG(price) AS avg_price_2023 FROM books 
WHERE published_date >= '2023-01-01'; 

4. MAX() - Maximum Value

MAX() finds the largest value in a numeric or date column.

SELECT MAX(order_date) AS latest_order FROM orders; 

5. MIN() - Minimum Value

MIN() finds the smallest value in a numeric or date column.

SELECT MIN(quantity) AS min_quantity FROM orders; 

String, Date, and Mathematical Functions

SQL String Functions

1. CONCAT(): Combine Strings - Joins multiple strings into one.

SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM customers;

1. SUBSTRING(): Extract Part of a String - Extracts a portion of a string

SELECT SUBSTRING(email, POSITION('@' IN email) + 1) AS domain FROM customers;

1. LENGTH(): String Length - Returns the number of characters in a string.

SELECT email, LENGTH(email) AS email_length FROM customers; 

1. UPPER() and LOWER(): Change Case UPPER() – Converts text to uppercase.

SELECT UPPER(customer_name) AS upper_name FROM customers;

LOWER() – Converts text to lowercase.

SELECT LOWER(email) AS standardized_email FROM customers; 

1. TRIM(), LTRIM(), RTRIM(): Remove Spaces - Cleans unwanted spaces from strings.

SELECT TRIM(string); 
SELECT LTRIM(string); 
SELECT RTRIM(string); 

1. REPLACE(): Replace Text - Replaces all occurrences of a substring

SELECT REPLACE(country, 'Kenya', 'KE') AS short_country FROM customers;

SQL Date Functions

1. NOW(): Current Date and Time

SELECT NOW() AS current_datetime;

1. YEAR(), MONTH(), DAY(): Extract Date Parts

SELECT order_id, YEAR(order_date) AS order_year FROM orders; 
SELECT order_id, MONTH(order_date) AS order_month FROM orders; 
SELECT order_id, DAY(order_date) AS order_day FROM orders; 

1. DATEDIFF() / Subtracting Dates Note: DATEDIFF() is for MySQL. In PostgreSQL, use subtraction.

SELECT order_id, CURRENT_DATE - order_date AS days_since_order FROM orders;

SQL Mathematical Functions

1. ROUND(): Round Numbers

SELECT ROUND(price, 2) AS rounded_price FROM books;

1. CEIL() and FLOOR(): Round Up/Down

SELECT order_id, CEIL(total_weight / quantity) AS shipping_cost_per_item FROM 
orders; 

1. MOD(): Get Remainder

SELECT order_id, quantity, MOD(quantity, 2) AS remainder FROM orders;

1. POWER(): Raise to a Power

SELECT book_name, POWER(price, 3) AS cubed_price FROM books;

1. ABS(): Absolute Value

SELECT book_name, ABS(price - 500) AS price_difference FROM books; 

1: INNER JOIN
INNER JOIN returns only matching rows from both tables. If there is no match between the tables the row is excluded from the result. It is used when you only want records that exist in both tables or want to combine related data.

2: LEFT JOIN
LEFT JOIN returns all rows from the left table and matching rows from the right table. Also known as left outer join. If no match exists null values are returned for the right table columns.

3: RIGHT JOIN
RIGHT JOIN returns all rows from the right table and matching rows from the left table. If no match exists null values are returned for the left table columns.

4: FULL OUTER JOIN
FULL OUTER JOIN returns all rows from both tables. If no match exists nulls appear on the missing side. It is used for combining two datasets and finding mismatches between systems.

5: CROSS JOIN
CROSS JOIN returns the cartesian product - every row from the first table combined with every row from the second table. No matching is required.

WINDOW FUNCTIONS IN SQL.

Window functions perform calculations across a set of rows related to the current row without grouping the rows into a single output. They preserve the individual row details while providing additional contextual insights.

Key components.
Window functions are defined using the mandatory OVER() clause which specifies how the rows are partitioned and ordered for the calculation.

Common types of window functions.
1: RANKING - Row_number, rank, dense_rank - It assigns numbers or ranks to rows based on order.
Rank

Row number

2: AGGREGATE - sum, avg, min, max, count - calculates sum, averages or extremes across the window.
Total

Average

Count

Max

3: VALUE/OFFSET - lag, lead - Accesses data from rows before, after or at specific points in the window.

lag
Used to compare rows i.e. comparing current value to previous value

lead
looks forward to the next row or a specified number of rows ahead.

Subqueries

A subquery (or nested query) is a SQL query written inside another query. It allows you to
perform an operation that depends on the result of another query.

1. The SELECT clause - Subqueries in the SELECT clause add an extra computed column to the result set. Each subquery executes once for every row of the outer query.

SELECT  
name, 
(SELECT COUNT(*)  
FROM projects  
WHERE projects.employee_id = employees.employee_id) AS total_projects 
FROM employees; 

1. The FROM clause - A subquery in the FROM clause creates a temporary or derived table. The outer query can then select or filter data from it.

SELECT * 
FROM ( 
SELECT  
department_id,  
COUNT(*) AS total_employees 
FROM employees 
GROUP BY department_id 
) AS dept_summary; 

1. The WHERE or HAVING clause - Subqueries in the WHERE clause allow the outer query to filter results based on another query’s output.

SELECT  
department_name 
FROM departments 
WHERE department_id IN ( 
SELECT department_id 
FROM employees 
GROUP BY department_id 
HAVING COUNT(*) > 1 
); 

Correlated vs Non-Correlated Subqueries

1. Non-correlated subquery - A non-correlated subquery does not depend on the outer query. It can be executed on its own.
2. Correlated subquery - A correlated subquery depends on the outer query. It is evaluated once per row of the outer query.

Common Table Expressions (CTEs)

A CTE (Common Table Expression) is like creating a temporary result set (or virtual table)
that exists only during the execution of a single SQL query.

WITH high_salary AS ( 
SELECT name, salary 
FROM employees 
WHERE salary > 45000 
) 
SELECT * 
FROM high_salary; 

CTE with Joins

You can use CTEs to simplify joins

WITH emp_dept AS ( 
SELECT e.name, d.department_name, e.salary 
FROM employees e 
LEFT JOIN departments d ON e.department_id = d.department_id 
) 
SELECT * FROM emp_dept;

Stored Procedures

A stored procedure is a named block of SQL logic stored inside the database that you can
execute using: CALL procedure_name(parameters);

Procedures are for actions, such as:

• Insert data (add customer, add book, add order)
• Update data (update contact, update email)
• Delete data (delete customer safely)
• Validation and enforcement of business rules
• Multi-step operations (create order + check customer + log)

Procedure Syntax

CREATE OR REPLACE PROCEDURE procedure_name(param_name param_type, ...)
LANGUAGE plpgsql
AS $$
BEGIN -- SQL statements
END;
$$;

Explanation of each keyword

CREATE - creates the procedure
OR REPLACE - updates the procedure if it already exists (no need to drop first)
PROCEDURE procedure_name(...) - name + inputs (parameters)
LANGUAGE plpgsql - enables procedural features (IF, LOOP, variables)
AS $$ ... $$ - defines the body of the procedure
BEGIN ... END; - start/end of procedure logic
Call with: CALL procedure_name(...)

CREATE OR REPLACE PROCEDURE add_customer( 
p_first_name VARCHAR, 
p_last_name  VARCHAR, 
p_email      VARCHAR, 
p_contact    VARCHAR, 
p_city       VARCHAR) 
LANGUAGE plpgsql 
AS $$ 
BEGIN 
INSERT INTO customers(first_name, last_name, email, contact, city) 
VALUES (p_first_name, p_last_name, p_email, p_contact, p_city); 
END; 
$$;  

Explanation

• CREATE OR REPLACE PROCEDURE add_customer(...) - Creates a reusable procedure named add_customer.
• Parameters like p_first_name VARCHAR - Inputs we pass when calling the procedure.
• LANGUAGE plpgsql - Enables procedural execution.
• BEGIN - Starts execution block.
• INSERT INTO customers(...) - Specifies the table and columns being inserted.
• VALUES (...) - Inserts the parameter values.
• END;- Ends procedure.
• CALL add_customer(...) - Runs the procedure by inserting values into our table.

Delete Customer Safely

CREATE OR REPLACE PROCEDURE delete_customer_safely( 
p_customer_id INT 
) 
LANGUAGE plpgsql 
AS $$ 
BEGIN 
DELETE FROM orders 
WHERE customer_id = p_customer_id; 
DELETE FROM customers 
WHERE customer_id = p_customer_id; 
END; 
$$;