What is SQL (Structured Query Language)
SQL is the standard programming language used to manage and query data in relational database management systems (RDBMS).
SQL enables users to: retrieve data, insert new data, update existing data, delete data, create database structures, modify database structures, control access to data and manage transactions.
Users utilize SQL through commands.
SQL commands
SQL commands are grouped into 5 categories
DDL (Data Definition Language):
Used to manage database structure.
Key commands include:
CREATE used to create tables/databases,
ALTER used to modify tables,
DROP used to delete tables,
TRUNCATE used to remove all data but keeps structure.
DML (Data Manipulation Language):
Used to manage data within tables.
Key commands include:
INSERT used to add data into rows,
UPDATE used to make edits to existing data,
DELETE used to remove data from the database.
DQL (Data Query Language):
Used to retrieve data.
The primary command is SELECT, often used with clauses like
WHERE used to apply filters,
GROUP BY used to aggregate data,
ORDER BY used to sort data,
JOIN used to combines tables.
DCL (Data Control Language):
Used to control access permissions.
Key commands include:
GRANT used to give users data access permissions,
REVOKE used to remove data access permissions.
TCL (Transaction Control Language):
Used to manage transactions.
Key commands include:
COMMIT used to saves changes made to the data,
ROLLBACK used to undo changes made to the data,
SAVEPOINT used to mark a point for rollback.
SQL data types
When using SQL, each column can only hold one type of data thus it is important to define the columns' data type.
Data types define the kind of data that can be stored in each column of a table.
Data types are declared when creating a table, where each columns data type has to be set.
They include
Numeric data types
| Data Type | Description |
|---|---|
| SMALLINT | Stores small whole numbers |
| INTEGER / INT | Stores standard whole numbers |
| BIGINT | Stores very large whole numbers |
| SERIAL | Auto-incrementing integer |
| BIGSERIAL | Auto-incrementing large integer |
Character or string data types
| Data Type | Description |
|---|---|
| CHAR(n) | Fixed-length text |
| VARCHAR(n) | Variable-length text with a maximum limit |
| TEXT | Variable-length text with no strict short limit |
Date and time data types
| Data Type | Description |
|---|---|
| DATE | Stores date only |
| TIME | Stores time only |
| TIMESTAMP | Stores date and time |
| TIMESTAMPTZ | Stores date and time with time zone |
Boolean data type
Used to store True or False values.
Importance of SQL data types:
Using the correct data type enables one to:
- Ensure correct data entry
- Prevent invalid values
- Improve storage efficiency
- Improve query performance
- Support accurate calculations
- Maintain consistency across the database
SQL constraints
A SQL constraints are rules applied to database columns or tables to enforce data integrity, accuracy, and reliability by limiting the type of data that can be inserted, updated, or deleted.
SQL constraints are set for the column during table creation along with the data type.
Common SQL constraints include:
| Constraint | Description |
|---|---|
| NOT NULL | Ensures a column cannot have a NULL (empty) value. |
| UNIQUE | Ensures all values in a column are distinct, preventing duplicates. |
| PRIMARY KEY | Uniquely identifies each row in a table. |
| FOREIGN KEY | Establishes a link between data in two tables by referencing a primary key in another table, ensuring referential integrity. |
| CHECK | Validates that values in a column satisfy a specific condition. |
| DEFAULT | Sets a default value for a column if no value is specified during insertion. |
Bringing it all together:
In order to illustrate how SQL commands, data types and constraints merge together to work with data we will use SQL to create a database for a school and perform some SQL queries.
Greenwood Academy
SECTION A - Building the Database (DDL)
The project starts with creating the database schema named greenwood-academy that will contain all our tables and setting the search path to our schema to ensure all our commands are executed in it.
Creating the schema
create schema greenwood_academy;
set search_path to greenwood_academy;
Creating the tables
Now we create our students, subjects and exam results tables while setting the data types and the needed constraints for each column.
Creating students table:
create table greenwood_academy.students(
student_id INT primary key,
first_name VARCHAR(50) not null,
last_name VARCHAR(50) not null,
gender VARCHAR(1),
date_of_birth DATE,
class VARCHAR(10),
city VARCHAR(50)
);
Creating subjects table:
create table greenwood_academy.subjects(
subject_id INT primary key,
subject_name VARCHAR(100) not null unique,
department VARCHAR(50),
teacher_name VARCHAR(100),
credits INT
);
Creating exam results table:
create table greenwood_academy.exam_results(
result_id INT primary key,
student_id INT not null references greenwood_academy.students(student_id),
subject_id INT not null references greenwood_academy.subjects (subject_id),
marks INT not null,
exam_date DATE,
grade VARCHAR(2)
);
Modifying the table structure
We can use some DDL commands to alter the tables after creation, such as renaming the credits column in the subjects table to credit_hours.
alter table greenwood_academy.students
add column phone_number VARCHAR(20);
SECTION B - Filling the Database (DML)
Now that we have our tables with the columns set, we need to insert data into the tables.
Inserting data into the tables
Inserting data into students table:
insert into greenwood_academy.students
(student_id, first_name, last_name, gender, date_of_birth, class, city)
values
('1', 'Amina', 'Wanjiku', 'F', '2008-03-12', 'Form 3', 'Nairobi'),
('2', 'Brian', 'Ochieng', 'M', '2007-07-25', 'Form 4', 'Mombasa'),
('3', 'Cynthia', 'Mutua', 'F', '2008-11-05', 'Form 3', 'Kisumu'),
('4', 'David', 'Kamau', 'M', '2007-02-18', 'Form 4', 'Nairobi'),
('5', 'Esther', 'Akinyi', 'F', '2009-06-30','Form 2', 'Nakuru'),
('6', 'Felix', 'Otieno', 'M', '2009-09-14', 'Form 2', 'Eldoret'),
('7', 'Grace', 'Mwangi', 'F', '2008-01-22', 'Form 3', 'Nairobi'),
('8', 'Hassan', 'Abdi', 'M', '2007-04-09', 'Form 4', 'Mombasa'),
('9', 'Ivy', 'Chebet', 'F', '2009-12-01', 'Form 2', 'Nakuru'),
('10', 'James', 'Kariuki', 'M', '2008-08-17', 'Form 3', 'Nairobi')
;
Inserting data into the subjects table:
insert into greenwood_academy.subjects
(subject_id, subject_name, department, teacher_name, credit_hours)
values
('1', 'Mathematics', 'Sciences', 'Mr. Njoroge', '4'),
('2', 'English', 'Languages', 'Ms. Adhiambo', '3'),
('3', 'Biology', 'Sciences', 'Ms. Otieno', '4'),
('4', 'History', 'Humanities','Mr. Waweru', '3'),
('5', 'Kiswahili', 'Languages', 'Ms. Nduta', '3'),
('6', 'Physics', 'Sciences', 'Mr. Kamande', '4'),
('7', 'Geography', 'Humanities', 'Ms. Chebet', '3'),
('8', 'Chemistry', 'Sciences', 'Ms. Muthoni', '4'),
('9','Computer Studies', 'Sciences', 'Mr. Oduya', '3'),
('10', 'Business Studies', 'Humanities', 'Ms. Wangari', '3')
;
Inserting data into the exam results table:
insert into greenwood_academy.exam_results
(result_id, student_id, subject_id, marks, exam_date, grade)
values
('1','1','1','78','2024-03-15','B'),
('2','1','2','85','2024-03-16','A'),
('3','2','1','92','2024-03-15','A'),
('4','2','3','55','2024-03-17','C'),
('5','3','2','49','2024-03-16','D'),
('6','3','4','71','2024-03-18','B'),
('7','4','1','88','2024-03-15','A'),
('8','4','6','63','2024-03-19','C'),
('9','5','5','39','2024-03-20','F'),
('10','6','9','95','2024-03-21','A')
;
Modifying table contents
We can use SQL commands to modify data in our tables, such as updating the location of student Esther Akinyi, whose student ID is 5, from Nakuru to Nairobi.
update greenwood_academy.students
set city = 'Nairobi'
where student_id = 5;
SECTION C - Querying the Data
Now that we have all the data we need, we can proceed to utilize SQL to query and pull insights from the data through filters.
Such as:
Using a query to find all the students who are in form 4.
select s.first_name, s.last_name, s."class"
from greenwood_academy.students s
where class = 'Form 4';
Using a query to find all the exam results where marks are greater than or equal to 70.
select *
from greenwood_academy.exam_results er
where er.marks >= 70
order by marks asc;
SECTION D - Range, Membership & Search Operators
We can further use SQL operators to filter data based on certain conditions.
Such as:
Using a query to find all exam results where marks are between 50 and 80 (inclusive).
select er.result_id, er.marks, er.grade
from greenwood_academy.exam_results er
where er.marks between 50 and 80;
Using a query to find all students who live in Nairobi, Mombasa, or Kisumu
select s.first_name, s.last_name, s.city
from greenwood_academy.students s
where city in ('Nairobi', 'Mombasa', 'Kisumu')
order by s.student_id ;
SECTION E - COUNT
We can use SQL to count based on certain criteria.
Such as:
Using a query to find out how many students are currently in Form 3.
select count (*) as form_three_students_count
from greenwood_academy.students s
where class = 'Form 3';
SECTION F - CASE WHEN
We can futher use SQL to create labels for data based on certain conditions.
Such as:
Using a query to label each exam result with a grade description:
select subject_id, marks, grade ,
case
when marks < 40 then 'Fail'
when marks between 40 and 60 then 'Pass'
when marks between 61 and 79 then 'Merit'
else 'Distinction'
end
as performance
from greenwood_academy.exam_results er
order by marks asc;
Conclusion
Understanding how SQL works is important and working with the basics provides key skills in anyone who wants to work with data. The Greenwood academy project provides a simple example on the basics on SQL and explores the SQL commands, data types and constraints illustrating how they are used to create a relational database.
I have provided a repository for the Greenwood Academy project on my GitHub page here, where further SQL Queries have been performed with a readme file that outlines all the queries in the project.
Top comments (0)