DEV Community

Jean
Jean

Posted on

Introduction to SQL : DDL, DML, Commands, Filtering and Case When

#beginners #react #database #learning

DDL AND DML

DDL (Data Definition Language)
This is how we define and shape our data and includes commands such as;

  • create
  • alter
  • drop
  • truncate
  • rename

DML (Data Manipulation Language)
This is used to modify our data using commands such as;

  • insert
  • update
  • delete

The difference between DDL and DML is that DDL focuses on the structure of the database and affects tables and columns while DML focuses on data in the database and affects records in the tables.

How to use commands in SQL (create, insert, update and delete)

Create
The create command can be used to create a new schema or creating a table within your schema.
The syntax used for creating a new schema is create schema nairobi_academy; where nairobi_academy is the name of the schema.
The syntax used for creating a table is create table students where students is the column name.
To run a querry, first ensure that the querry end in a semi colon then press CTRL ENTER on your keyboard or use the first orange arrow on the top left of your

Create image

Insert
The insert command is used to put information into the tables created and the syntax used is insert into students() where students is the name of the table.

INSERT INTO students (student_id, first_name, last_name, gender, date_of_birth, class, city)
VALUES
(1, 'User_First_1', 'User_Last_1', 'F', '2008-01-01', 'Level 1', 'City_A'),
(2, 'User_First_2', 'User_Last_2', 'M', '2007-01-01', 'Level 2', 'City_B'),
(3, 'User_First_3', 'User_Last_3', 'F', '2009-01-01', 'Level 1', 'City_C'),
(4, 'User_First_4', 'User_Last_4', 'M', '2008-01-01', 'Level 3', 'City_D');

Update
This is used to modify esisting records in a database table.
Syntax used for this command is update.

Update command

Delete
This is used to remove existing records from a table and the syntax used fot this command is *DELETE FROM table_name WHERE condition;

Delete command

Filtering using WHERE

Filtering command

When filtering using where, operators such as =, >, between, in and like can be used in that;

  • = - equal to, it can be used for text and numerics
  • > and < - greater than and less than respectively, usually used with numerics
  • Between - filters values within a specified range (inclusive)
  • In - it provides a more concise, readable, and often more efficient way to filter a column instead of using multiple OR conditions in a WHERE CLAUSE.
  • Like - uses wildcards to find partial matches

Transforming data using CASE WHEN

It transforms data into categorical groups making it easier for analysis and reporting.
The syntax used is;
select column_name1,
CASE
WHEN condition1 THEN 'result1'
WHEN condition2 THEN 'result2'
END AS new_column_name
FROM table_name;

Case when image

Challenges faced while working with SQL

Some of the challenges I faced while working with SQL during this first week includes:

  • Debugging errors - SQL error messages are sometimes unclear making it hard to identify what went wrong while performing a query.
  • Remembering how to use different commands and the syntax can be tricky at first.

Top comments (0)