Introduction
Structured Query Language or SQL as is commonly referred to is the language used in interacting with relational databases. It allows users to create databases, store data, retrieve data, and modify existing records in a database. SQL commands can be put into categories based on their functionality or how they affect the database. These categories are:
DDL - Data Definition Language
DML - Data Manipulation Language
DQL - Data Query Language
TCL - Transaction Control Language
DCL - Data Control Language
In this article, we look at DDL and DML
DDL, DML and their differences
Data Definition Language (DDL) consists of SQL commands used to define and manage the structure of a database. These commands are responsible for creating, modifying, and deleting database tables and schemas. Common DDL commands include:
CREATE
ALTER
DROP
TRUNCATE
On the other hand, Data Manipulation Language (DML) is used to manage and manipulate the data stored within the database tables. Common DML commands include:
INSERT
UPDATE
DELETE
The main difference between DDL and DML is that DDL deals with the structure of the database, while DML deals with the data inside the database.
DDL and DML commands use cases in SQL
How to Use CREATE
The CREATE TABLE statement is used to create a new table in a database. The syntax of the command is
create table table_name(
column1 datatype constraint,
columnN datatype constraint
);
create table if not exists students(
student_id int primary key,
first_name varchar(50) not null,
last_name varchar(50) not null,
gender char(1),
date_of_birth date,
class varchar(10),
city varchar(50)
);
This command creates a table named Students with 7 columns - student_id, first_name, last_name, gender, date_of_birth, class and city. Student_id is marked as the primary key, meaning each student must have a unique ID.
How to Use ALTER
The ALTER TABLE statement is used to add, delete, or modify columns in an existing table. It is also used to add or drop various constraints on an existing table.
Common ALTER TABLE operations are:
Add column - Adds a new column to a table
Drop column - Deletes a column in a table
Rename column - Renames a column
Modify column - Changes the data type, size, or constraints of a column
Add constraint - Adds a new constraint
Rename table - Renames a table
For instance, the syntax for adding a column is
alter table students
add phone_number varchar(20);
or renaming a column
alter table subjects
rename column credits to credit_hours;
or deleting a column
alter table students
drop column phone_number;
How to Use INSERT
To add data into the table, the INSERT command is used.
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');
How to Use UPDATE
The UPDATE command is used when you want to modify existing data in a table.
update students
set city = 'Nairobi'
where student_id = 5;
This changes city of student ID 5 to Nairobi
How to Use DELETE
If a record is no longer needed, the DELETE command can be used to remove it from the table.
delete from exam_results
where result_id = 9;
This removes/deletes results id 9 from exam_results table
Filtering with WHERE
When working with databases, it is usually necessary to retrieve or modify only specific records. The WHERE clause allows users to filter data based on specified conditions.
For example, to find all subjects in the Sciences department
select * from subjects
where department = 'Sciences';
Several operators can be used with the WHERE clause. Some of these operators include:
- = Checks if values are equal
select * from students
where gender = 'F';
select * from exam_results
where marks = 70;
- < or > Compares numerical values (less than/greater than)
select * from exam_results
where marks > 70;
- BETWEEN Filters values within a range inclusive of the range values
select * from exam_results
where marks between 50 and 80;
- IN Checks if a value matches any value in the specified column
select * from students
where city in ('Nairobi','Mombasa','Kisumu');
- LIKE Searches for patterns in text
select * from subjects
where subject_name like '%studies%';
The % symbol is a wildcard that can represent any number of characters,i.e,
'A%' means starts with A
'%n' means ends with n
'%ar%' means contains “ar”
How to Use CASE WHEN
The CASE expression is used to define different results based on specified conditions in an SQL statement. It allows users to create conditional logic within queries.
select *,
case
when marks >= 80 then 'Distinction'
when marks >= 60 then 'Merit'
when marks >= 40 then 'Pass'
else 'Fail'
end as performance
from exam_results;
This returns the exam_results table with a performance column categorized based on marks
Conclusion
One interesting thing about SQL is that a few simple commands can completely control a database. It is critical to never forget the WHERE clause when using UPDATE or DELETE as this can change or remove every record in a table. Overall, learning DDL and DML provides a strong foundation for working with databases and helps in managing both the structure and the data effectively
Top comments (0)