SQL 101: Introduction to Structured Query Language(SQL)

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.