DEV Community

Cover image for Getting started with SQL is 'HARD' until you follow these 10 steps.
Lameck Odhiambo
Lameck Odhiambo

Posted on

Getting started with SQL is 'HARD' until you follow these 10 steps.

Introduction

SQL (Structured Query Language) is the standard programming language used to manage, query, and manipulate data within relational database management systems (RDBMS). It allows you to talk directly to databases to perform tasks like retrieving data, adding new records, or building database structures.

A database is an organized collection of digital data stored in a computer system so it can be easily accessed, managed, modified, and updated. It relies on software called a database management system (DBMS) to control access and secure the information.

  • SQL commands are divided into five main categories based on their functionality: DDL, DML, DQL, DCL, and TCL. These groupings allow database professionals to easily manage database structures, manipulate or retrieve data, control security permissions, and oversee multi-step transactions.

1. DDL(Data Defination Language)

DDL commands define and modify the database structure or schema. They operate on the design of the layout (like tables, indexes, and views) rather than the actual rows of data. DDL actions automatically save to the database and cannot be undone. e.g

  • CREATE: Builds new database objects
  • ALTER: Modifies the structure of an existing object
  • DROP: Deletes tables or entire databases permanently
  • TRUNCATE: Removes all records from a table while keeping its structure intact

2. DML (Data Manipulation Language)

DML commands modify and manipulate the data stored within existing tables. Unlike DDL, these changes do not automatically save immediately and can be rolled back if an error occurs.

  • INSERT: Adds new rows of data to a table.
  • UPDATE: Modifies existing rows of data
  • DELETE: Removes specific rows based on a given condition

3. DQL (Data Query Language)

DQL commands are explicitly focused on retrieving and reading data from the database. It is the most common command category used by data analysts.

  • SELECT: Fetches records from one or more database tables

4. DCL (Data Control Language)

DCL commands handle security, authentication, and access control within the database environment. Database administrators use DCL to set up system permissions for specific user accounts

  • GRANT: Gives a user account specific privileges to read or modify data
  • REVOKE: Withdraws previously granted access permissions from a user account

5. TCL (Transaction Control Language)

TCL commands manage operations grouped as transactions to ensure overall data integrity and consistency. They work directly alongside DML statements to finalize or discard multi-step changes.

  • COMMIT: Permanently saves all active transactional changes to the database
  • ROLLBACK: Reverts uncommitted operations, restoring the database to its previous state.

  • SAVEPOINT: Creates an intermediate checkpoint within a transaction to roll back to if needed.

  • In this particular case, we are going to create a database for a school, Greenwood Academy using PostgreSQL.

Ok lets get hands-on by going through the following steps. Come along!

Step 1: Create Schema

  • A database schema is the structural blueprint or framework that defines how data is organized, stored, and related within a database.
  • In PostgreSQL, the search_path configuration variable determines the order in which schemas are searched to find database objects (like tables, views, or functions) when a query uses an unqualified name. It behaves similarly to the PATH environment variable in an operating system. 
-- SECTION A - Building the Database (DDL)

-- Create a schema called greenwood_academy 
create schema greenwood_academy;

set search_path to greenwood_academy;
show search_path;
Enter fullscreen mode Exit fullscreen mode

Step 2: Creating tables

  • A database table is a structured collection of related data organized into a grid format. It serves as the core building block of any relational database management system (RDBMS), allowing you to store, organize, and query information efficiently. 
-- Create the students table with the following columns:
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));

-- Create the subjects table with the following columns:
create table greenwood_academy.subjects(
subject_id  INT PRIMARY key,
subject_name VARCHAR(100) unique not null,
department  VARCHAR(50),
teacher_name    VARCHAR(100),
credits INT
);

-- Create the exam_results table:
create table greenwood_academy.exam_results(
result_id   INT PRIMARY key,
student_id  INT NOT null,
subject_id  INT NOT null,
marks   INT NOT null,
exam_date   DATE,
grade   VARCHAR(2)
);
Enter fullscreen mode Exit fullscreen mode

Step 3: Alter

  • Altering a database structure uses the ALTER command to change table designs. You can add columns, drop columns, and modify data types. 
-- After creating the students table, the school realises they forgot to include a phone number column. Use ALTER TABLE to add a column called phone_number with data type VARCHAR(20).
alter table greenwood_academy.students 
add column phone_number varchar(20);

-- The column credits in the subjects table needs to be renamed to credit_hours. Write the SQL to rename it.
alter table greenwood_academy.subjects
rename column credits to credit_hours;

-- The school decides they no longer need the phone_number column you added in Q5. Write the SQL to remove it completely from the students table.
alter table greenwood_academy.students 
drop column phone_number;
Enter fullscreen mode Exit fullscreen mode

Step 4: Inserting data in the database

  • To insert data into a relational database, you use the standard SQL INSERT INTO statement, which adds new rows or records to a specified table. 

-- Inserting all 10 students into the 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 all 10 subjects 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 all 10 exam results 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');
Enter fullscreen mode Exit fullscreen mode

Step 5: Querying

  • Querying a database is the process of requesting specific data or performing actions on records stored within a database system. Users and applications write queries using a structured language—most commonly SQL (Structured Query Language)—to filter, calculate, combine, or modify information into a human-readable format. 
-- After inserting the data, run a SELECT query to confirm all 10 rows exist in each of the three tables.
select * from greenwood_academy.students;

select * from greenwood_academy.subjects;

select * from greenwood_academy.exam_results;
Enter fullscreen mode Exit fullscreen mode

Step 6: Updating

  • Update is a SQL operation that modifies existing records in a database table. When you update data, you can change the values stored in one or more columns 
-- Esther Akinyi has moved from Nakuru to Nairobi. Write an UPDATE statement to change her city. (Her student_id is 5)
update greenwood_academy.students 
set city ='Nairobi'
where student_id =5;

-- The marks for result_id 5 were entered incorrectly - the correct marks are 59, not 49. Write an UPDATE to fix this.
update greenwood_academy.exam_results
set marks =59
where result_id =5;

-- The exam result with result_id 9 has been cancelled by the school. Write a DELETE statement to remove it from the exam_results table.
delete from greenwood_academy.exam_results
where result_id =9;
Enter fullscreen mode Exit fullscreen mode

Step 7: Filtering

  • Filtering in a database is the process of selecting a specific subset of records from a larger dataset based on defined conditions or rules. Instead of loading millions of unnecessary rows, filtering isolates only the data relevant to your needs, which optimizes query performance and speeds up analysis. 
-- Writing a query to find all students who are in Form 4.

select first_name,last_name from greenwood_academy.students
where class = 'Form 4';

-- Writing a query to find all subjects in the Sciences department.
select subject_name from greenwood_academy.subjects
where department = 'Sciences';

-- Writing a query to find all exam results where the marks are greater than or equal to 70.
select subject_id,marks from greenwood_academy.exam_results
where marks >= 70;

-- Writing a query to find all female students only. (Hint: gender = 'F')
select first_name,last_name from greenwood_academy.students
where gender = 'F';

-- Writing a query to find all students who are in Form 3 AND from Nairobi.
select first_name,last_name,class,city from greenwood_academy.students
where (class = 'Form 3') and (city = 'Nairobi');


-- Writing a query to find all students who are in Form 2 OR Form 4.
select first_name,last_name,class from greenwood_academy.students
where (class = 'Form 2') or (class = 'Form 4');
Enter fullscreen mode Exit fullscreen mode

Step 8: Memebership

  • Membership operators are used to check if a specific value exists within a collection of data, such as a static literal list, a subquery result, a JSON object, or a native arra 
-- Write a query to find all exam results where marks are between 50 and 80 (inclusive).
select * from greenwood_academy.exam_results
where marks between 50 and 80;

-- Write a query to find all exams that took place between 15th March 2024 and 18th March 2024.
select * from greenwood_academy.exam_results
where exam_date between '2024-03-15' and '2024-03-18';

-- Write a query to find all students who live in Nairobi, Mombasa, or Kisumu - use IN.
select first_name,last_name,city from greenwood_academy.students
where city in ('Nairobi','Mombasa','Kisumu');

-- Write a query to find all students who are NOT in Form 2 or Form 3 - use NOT IN.
SELECT first_name, last_name, city, class
FROM greenwood_academy.students
WHERE class NOT IN ('Form 2', 'Form 3');

-- Write a query to find all students whose first name starts with the letter 'A' or 'E' - use LIKE.
select first_name from greenwood_academy.students
where first_name like 'A%' 
     or first_name like 'E%';


-- Write a query to find all subjects whose subject name contains the word 'Studies'.
select * from greenwood_academy.subjects
where subject_name ilike '%Studies%';
Enter fullscreen mode Exit fullscreen mode

Step 9: Count

  • The COUNT() function in PostgreSQL is an aggregate function used to return the total number of rows or non-null values that match your query parameters. 
-- Finding number of students currently in Form 3? 
select count(student_id) as number_of_students_form3 from greenwood_academy.students
where class = 'Form 3';

-- Number of exam results that have a mark of 70 or above? Write the query.
select count(result_id) as marks_above_70 from greenwood_academy.exam_results
where marks >=70;
Enter fullscreen mode Exit fullscreen mode

Step 10: CASE - WHEN Statements

  • In PostgreSQL, the CASE expression acts as an IF-THEN-ELSE conditional statement that allows you to execute logic directly within your SQL queries. It evaluates conditions sequentially from top to bottom and returns a value as soon as the first condition evaluates to true. 
-- Writing a query using CASE WHEN to label each exam result with a grade description:

select *,
case 
    when marks >= 80 then 'Distinction'
    when marks >= 60 then 'Merit'
    when marks >= 40 then 'Pass'
    else 'Fail'
    end as performance
from greenwood_academy.exam_results;

select * from greenwood_academy.exam_results;

-- Writing a query using CASE WHEN to label each student 
select first_name,last_name,class,
case 
     when class = 'Form 3' or class ='Form 4' then 'Senior'
     when class = 'Form 2' or class ='Form 1' then 'Junior'
end as student_level 
from greenwood_academy.students;
Enter fullscreen mode Exit fullscreen mode

Conclusion

Structured Query Language (SQL) is the foundational cornerstone of modern data management and the universal standard for interacting with relational databases. Despite the rapid emergence of non-relational database technologies, SQL remains an irreplaceable skill due to its high efficiency, declarative simplicity, and absolute dominance in corporate data ecosystems.

Top comments (0)