Practical SQL Concepts

Introduction

In today's world data driven world, data is becoming more valuable in that it can help organizations, persons, and companies in informed decision-making, enhanced customer experience and increased revenue & innovation among many other benefits. Using examples such as online stores, hospitals, digital entertainment spaces, digital learning websites and digital booking apps, SQL is widely used in making sure data is stored and turned into meaningful insights. SQL in this case is crucial tool in the data space and we'll be diving into it throughout this article.

What is SQL? SQL in full means Structured Query Language. It is a programming language used to manage, manipulate and retrieve data from data stores such as relational databases. It is mostly used by Data and Software professionals such as Software Engineers, Data engineers, Data Analysts, Data Scientists, Analytics Engineers, Database Administrators and Business Intelligence specialists.

What is a Relational Database This is a data storage tool used to host data in rows and columns in form of tables. A database organizes data in tables which are then organized stored inside a database as the main container. Examples of relational databases include PostgreSQL, MYSQL, MariaDB, SQLite, Oracle and Microsoft SQL Server.

Core Components of SQL

SQL is divided into several subcategories category wise. Please note that the core of these subdivisions is still SQL only that the components are ways to define, model and control data. Some of the components include:

1. Data Definition Language(DDL) - Used to define and modify database structures. Common commands include:

   • CREATE - Creates databases and tables.
   • ALTER - Modifies existing databases and tables.
   • DROP - Deletes databases, tables etc
   • TRUNCATE - Removes all data from a table.

2. Data Manipulation Language(DML) - Used to define and modify database structures. Common commands include:

   • SELECT - Retrieves data from one or more tables.
   • INSERT - Adds new records.
   • UPDATE - Modifies existing records.
   • DELETE - Removes records from a table.

3. Transaction Control Language(TCL) - Used in ensuring data integrity during transactions. Common commands include:

   • COMMIT - Permanently saves changes.
   • ROLLBACK - Undoes previous changes.

What is the difference between DDL and DML?

DDL changes the schema of the database and statements are auto-commited hence ussually permanent. DML is used to query and modify a database. Changes can be rolled-back if they are inside a transaction.

Practical Examples of using CREATE, INSERT, UPDATE and DELETE.

• Using CREATE to make a students table

--Create Students Table
CREATE TABLE students (
  student_id INT PRIMARY KEY,
  first_name VARCHAR(50) NOT NULL,
  lastname VARCHAR(50) NOT NULL,
  gender VARCHAR(1),
  date_of_birth DATE,
  class VARCHAR(10),
  city VARCHAR(50)  
);

• Using INSERT to feed data into the students table

-- Insert All 10 Exam Results into the Exam Results Table 
insert into 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');

• Using UPDATE to modify the students table

-- UPDATE marks from 49 to 59
update  students
set city = 'Nairobi'
where student_id =5;

• Using DELETE to remove data in the exam results table

-- DELETE Exam Result with id =9
delete from exam_results
where result_id =9;

Using the WHERE keyword

WHERE is used in filtering out specific data. eg writing a query to find a student whose student id number is 209 would be written as:

select student_name from students where student_id = 209;

WHERE is normally accompanied with other keywords such as

• LIKE - Used to return records starting or ending with specific letters

-- Get all Students whose First name starts with letter A or E
select * from students
where first_name like 'A%' or  first_name like 'E%';

• BETWEEN - Used to filter records that fall in certain ranges. Eg Dates, Prices, Marks etc

-- Get all Exam Results of marks between 50 and 80
select * from exam_results
where marks between 50 and 80;

• IN - Used to filter records that have multiple required values for a specific column. eg

-- Get all Students who live in Nairobi, Mombasa or Kisumu
select * from students
where city IN('Nairobi','Mombasa','Kisumu');

Using the CASE WHEN keyword

Case When is used as to add the IF-THEN-ELSE logic to queries. It evaluates a list of conditions and returns a result when the first condition is met. See the sql example below.

-- Use Case-When to classify marks in Categories
select * from exam_results;
select *,
case 
    when marks >=80 then 'Distinction'
    when marks >=60 then 'Merit'
    when marks >=40 then 'Pass'
    when marks <40 then 'Fail'
end as performance
from exam_results;

Conclussion

SQL is an interesting language to tinker aroud with, especially while working with relatable data. Learning to insert, modify, delete and query data has been a fullfiling experience and I will continue to learn complex SQL concepts as I enjoy my journey to becoming a skilled Data Engineer. If you would like to learn more SQL Advanced concepts, feel free to follow me as I will be dropping another article soon. Happy coding!