SQL 101: Introduction to Structured Query Language(SQL)

Daniel Chege — Wed, 02 Oct 2024 09:55:10 +0000

What is SQL?

SQL, or Structured Query Language, is a language designed to allow both technical and non-technical users query, manipulate, and transform data from a relational database. And due to its simplicity, SQL databases provide safe and scalable storage for millions of websites and mobile applications.
There are many popular SQL databases including SQLite, MySQL, Postgres, Oracle and Microsoft SQL Server, they all support the common SQL language standard.

What is relational database?

A relational database is a type of database that stores and provides access to data points that are related to one another.

For example, if a school has a database, you might find a table containing data of all students. This table might need to store the student name, administration number, age, and his/her level(grade).

Key components of SQL

SQL consists of various commands, categorized into different groups based on their purpose. Here’s a breakdown of the primary categories:

Data Defination Language(DDL).

DDL commands are used to define the structure of the database, including creating, altering, and deleting tables.

CREATE: Used to create a new table or database.

-- Create a database
CREATE DATABASE schoolDB;

--Create table
CREATE TABLE students (
    Administration_number INT PRIMARY KEY,
    Student_name VARCHAR(50),
    Age INT,
    Grade INT
);

ALTER: Modifies an existing table, like adding or removing a column.

ALTER TABLE students ADD age INT;

DROP: Deletes a table or database.

DROP TABLE students;

TRUNCATE: Removes all records from a table, including all spaces allocated for the records are removed.

TRUNCATE TABLE students;

Data Manipulation Language (DML)

DML is used to manage data within tables, focusing on data retrieval, insertion, updates, and deletion.

INSERT: Used to insert data into a table.

INSERT INTO students(Administration_number, Student_name, age, grade)
VALUES (
12, Peter, 11, 2);

UPDATE: Used to update existing data within a table

UPDATE students
SET age = 13
WHERE administration_number = 1124;

DELETE: Removes rows of data from a table.

DELETE FROM students WHERE administration_number = 1124;

LOCK: Table control concurrency.
CALL: Call a PL/SQL

Data Control Language(DCL)

DCL mainly deal with the rights, permissions, and other controls of the database system.

GRANT: Gives users access privileges to the database.

GRANT SELECT, UPDATE ON students TO SOME_USER, ANOTHER_USER;

REVOKE: Withdraws the user’s access privileges given by using the GRANT commands.

REVOKE SELECT, UPDATE ON students FROM USER1, USER2;

Transaction Control Language(TCL)

TCL manages transactions within a database, ensuring data consistency.

OPEN: Opens a transaction.
COMMIT: Commits a transaction.

COMMIT;

ROLLBACK: Rollbacks a transaction in case of any error occurs.

ROLLBACK;

Data Query Language(DQL)

DQL are used for performing queries on the data within schema.

SELECT: Retrieves data from one or more tables.

SELECT * FROM students;

SQL Syntax and Structure

SQL is designed easy to read and write. Each query follows a structured flow:

SELECT Clause – Specifies the columns to retrieve.

SELECT student_name, age
FROM students;

-_ FROM_ Clause: Specifies the table from which data is retrieved.

FROM students;

WHERE Clause: Applies conditions to filter the data.

WHERE age < 15;

ORDER BY Clause: Sorts the results by one or more columns.

ORDER BY administration_number ASC/DESC

GROUP BY Clause: Groups rows with the same values into summary rows.

SELECT
student_name,
COUNT(*)
FROM students
GROUP BY age;

SQL JOINS

SQL joins combine rows from two or more tables based on a related column between them. The most common types of joins are:

INNER JOIN: Returns rows that have matching values in both tables.

SELECT column, another_table_column
FROM mytable
INNER JOIN another_table 
    ON mytable.id = another_table.id

LEFT JOIN: Returns all rows from the left table and the matched rows from the right table. If there's no match, NULL values are returned.
RIGHT JOIN: Similar to the LEFT JOIN but returns all rows from the right table.
FULL OUTER JOIN: Returns rows when there is a match in one of the tables.

SELECT column, another_column, …
FROM mytable
LEFT/RIGHT/FULL JOIN another_table 
    ON mytable.id = another_table.matching_id

Common SQL Functions

Aggregate Functions: These include SUM, COUNT, AVG, MIN, and MAX, which are used to perform calculations on sets of rows.

SELECT AGG_FUNC(column_or_expression) AS aggregate_description, …
FROM mytable;

String Functions: These functions manipulate string values, such as UPPER(), LOWER(), CONCAT(), etc.

SELECT STR_FUNC(column)
FROM mytable;

Date Functions: SQL provides various functions to manipulate and format dates, like NOW(), DATEADD(), and DATE_TRUNC(). SELECT.

Conclusion

SQL is essential for managing and analyzing data, offering powerful tools for efficient database operations.

DEV Community: Daniel Chege