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. …

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', '[email protected]', '+254712345678'), 

('Jane', 'Smith', '[email protected]', '+254798765432'), 

('Paul', 'Otieno', '[email protected]', '+254701234567'), 

('Mary', 'Okello', '[email protected]', '+254711223344');

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

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

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

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

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

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

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

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;

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

SELECT TRIM(string); 

SELECT LTRIM(string); 

SELECT RTRIM(string);

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

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

SQL Date Functions

NOW(): Current Date and Time

SELECT NOW() AS current_datetime;

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;

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

ROUND(): Round Numbers

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

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

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

orders;

MOD(): Get Remainder

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

POWER(): Raise to a Power

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

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.

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;

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;

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

Non-correlated subquery - A non-correlated subquery does not depend on the outer query. It can be executed on its own.

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; 

$$;

tsecurity.de LIVE

Echtzeit-Radar & Feeds

👥 Community & Social

sql and databases