SQL Basics Cheat Sheet

Technical writer

SQL

04.12.2024

Reading time: 11 min

SQL is a globally operated Query Language that interacts with the databases. It assists us in finding, editing, and handling data effectively. A cheat sheet makes learning easier by giving a quick way to memorize important commands. In this tutorial, we'll go over primary SQL commands, clauses, joins, transactions, and much more to assist you in administering databases instantly and easily. To demonstrate these concepts, we will implement each command in MySQL.

SQL Data Types

A data type determines the kind of values that can be preserved in a column, outlined below with their explanations:

INT: It keeps integers.
CHAR(n): It saves a static-size string consisting of n characters.
VARCHAR(n): It keeps a variable-length string, comprising a max of n characters.
TEXT: It enables the storage of extensive text or strings.
DATE: It lets us store dates.
DATETIME: It saves dates & times.
FLOAT: It stores floating-point digits.
BOOLEAN: Postgres and MySQL offer BOOLEAN for storing true or false entries. In contrast, SQL Server utilizes BIT for this purpose.

Basic SQL Commands

Commands let us create tables, delete, and insert or edit records. For example:

CREATE: Generates new databases and other objects.
SHOW: Displays a list of all accessible databases and other objects. Postgres doesn’t offer SHOW; however, the equivalent functionality can be obtained in psql by utilizing meta-commands like \l, \dt, \dn, etc.
USE: Switches the database. Postgres uses \c meta-command, instead.
INSERT: Appends new entries into a designated table.
SELECT: Displays information from the stated table(s).
UPDATE: Ugrades existing entries in a table.
DELETE: Removes desired or all rows.
DROP: Permanently drops a database or other objects.

Example 1: Create Database

Let’s generate a database called hostman_info:

CREATE DATABASE hostman_info;

Now execute SHOW to justify the database creation:

SHOW DATABASES;

Now utilize hostman_info by employing the USE command:

USE hostman_info;

The screenshot below demonstrates that we have successfully established a connection with the hostman_info:

Example 2: Create Table

The below-stated query demonstrates the table creation with various data types:

CREATE TABLE hostman_team (
    Hostman_EID INT AUTO_INCREMENT PRIMARY KEY,
    Hostman_FName VARCHAR(30),
    Hostman_LName VARCHAR(30),
    Hostman_DOB DATE,
    Hostman_ESalary FLOAT,
    Hostman_EStatus BOOLEAN
);

It constructs a new hostman_team table with the requested columns, which can be confirmed with this command:

SHOW TABLES;

Example 3: Insert Rows

Once a table is formed, we can append new entries to the hostman_team table:

INSERT INTO hostman_team (Hostman_FName, Hostman_LName, Hostman_DOB, Hostman_ESalary, Hostman_EStatus)
VALUES ('Anees', 'Asghar', '1995-01-01', 60000, TRUE);

Similarly, users can insert as many records as necessary with a single INSERT statement. In this scenario, each entry to be appended must be separated by a comma.

INSERT INTO hostman_team (Hostman_FName, Hostman_LName, Hostman_DOB, Hostman_ESalary, Hostman_EStatus) 
VALUES ('Joe', 'Root', '1990-01-15', 65000, TRUE),
    ('Steve', 'Smith', '1980-03-12', 70000, FALSE);

Example 4: Fetch Records

Next, execute SELECT to display data from hostman_team:

SELECT * FROM hostman_team;

Similarly, we can extract only the preferred columns by defining their names:

SELECT Hostman_FName, Hostman_LName, Hostman_ESalary 
FROM hostman_team;

Example 5: Update Table

SQL gives another helpful statement called UPDATE that assists us in editing existing records:

UPDATE hostman_team
SET Hostman_ESalary = 62000
WHERE Hostman_EID = 1;

To edit entries as per defined criteria, we can utilize UPDATE with the WHERE clause:

UPDATE hostman_team
SET Hostman_ESalary = 75000
WHERE Hostman_ESalary >= 65000;

Example 6: Delete Data

If a particular entry is no longer relevant, we can remove it:

DELETE FROM hostman_team
WHERE Hostman_EID = 3;

To clear all entries of hostman_team, utilize the subsequent query:

DELETE FROM hostman_team;

SQL SELECT Queries

SQL presents various SELECT queries that let us collect data in different ways, including filtering, arranging, and limiting results according to our requirements:

DISTINCT: It fetches distinct values while deleting duplicates.
WHERE: Obtain the entries according to predetermined criteria.
ORDER BY: It gives a certain order to the resultant table.
LIMIT: It applies restrictions to the entries to be fetched.

This would extract distinct firstNames from Hostman_team:

SELECT DISTINCT Hostman_FName 
FROM hostman_team;

Similarly, the subsequent query extracts entries from Hostman_team with EmpID 2 or above and then sorts them in descending sequence to exhibit only the topmost entry:

SELECT * FROM hostman_team 
WHERE Hostman_EID >= 2
ORDER BY Hostman_EID DESC 
LIMIT 1;

SQL Joins

SQL comes up with distinct kinds of JOIN that let us merge rows from several tables using related columns.

Let’s create a couple of tables titled Hostman_depts and Hostman_staff with the following structure:

CREATE TABLE Hostman_depts (
    HDptID INT AUTO_INCREMENT PRIMARY KEY,
    HDptName VARCHAR(255),
    HDptLocation VARCHAR(255)
);
CREATE TABLE Hostman_staff (
    HStaffID INT AUTO_INCREMENT PRIMARY KEY,
    HFirstName VARCHAR(255),
    HLastName VARCHAR(255),
    HEmail VARCHAR(255),
    HPhoneNumber VARCHAR(20),
    HHireDate DATE,
    HDptID INT,
    FOREIGN KEY (HDptID) REFERENCES Hostman_depts(HDptID)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

The above query creates the hostman_staff table with a foreign key HDptID linking to the hostman_depts table. After creating the table, we insert some records in these tables, which are populated with the following query:

SELECT * FROM Hostman_depts;
SELECT * FROM Hostman_staff;

INNER JOIN

It fetches rows that have related records in both target tables:

SELECT 
    HStaffID, HFirstName, HLastName, HEmail, HDptName, HDptLocation
FROM 
    Hostman_staff
INNER JOIN 
    Hostman_depts
ON 
    Hostman_staff.HDptID = Hostman_depts.HDptID;

We combine records where the HDptID in the Hostman_staff table corresponds to the HDptID in the Hostman_depts table:

LEFT JOIN

It fetches all data from the left table with associated entries from the right table. If unmatched, NULLs fill the right table's columns.

The below query displays all staff members with their respective departments and addresses:

SELECT 
    HStaffID, HFirstName, HLastName, HEmail, HDptName, HDptLocation
FROM 
    Hostman_staff
LEFT JOIN 
    Hostman_depts
ON 
    Hostman_staff.HDptID = Hostman_depts.HDptID;

Every record of Hostman_staff is returned, even if there is no related match in the Hostman_depts table:

RIGHT JOIN

It exhibits all details from the right table and associated entries from the left table. If unmatched, NULL will be displayed for the left table:

SELECT 
    HStaffID, HFirstName, HLastName, HEmail, HDptName, HDptLocation
FROM 
    Hostman_staff
RIGHT JOIN 
    Hostman_depts
ON 
    Hostman_staff.HDptID = Hostman_depts.HDptID;

It displays all departments and enlisted staff members, with NULL entries when no staff is linked with a department:

FULL JOIN

It depicts all rows from both tables, with associated records where available. The resultant table possesses NULL values for unavailable records:

SELECT 
    HStaffID, HFirstName, HLastName, HEmail, HDptName, HDptLocation
FROM 
    Hostman_staff
FULL JOIN 
    Hostman_depts
ON 
    Hostman_staff.HDptID = Hostman_depts.HDptID;

It exhibits all staff members with departments, even if no staff members are allocated to each department.

Note: Some SQL versions may not directly support FULL OUTER JOIN. In this scenario, we can integrate LEFT and RIGHT JOIN with UNION to accomplish a similar functionality:

SELECT 
    HStaffID, HFirstName, HLastName, HEmail, HDptName, HDptLocation
FROM
    Hostman_staff
LEFT JOIN
    Hostman_depts
ON
    Hostman_staff.HDptID = Hostman_depts.HDptID
UNION
SELECT
    HStaffID, HFirstName, HLastName, HEmail, HDptName, HDptLocation
FROM
    Hostman_staff
RIGHT JOIN 
    Hostman_depts
ON
    Hostman_staff.HDptID = Hostman_depts.HDptID;

Aggregate Functions

SQL offers distinct aggregate functions that execute computations on numerous rows and yield a single outcome:

COUNT: Computes the total records.
SUM: Finds the aggregate of the targeted column.
AVG: Calculates column average.
MIN: Extracts the column's minimal value.
MAX: Locates the column's most elevated value.

Let us invoke the aggregate methods to demonstrate their working in practice:

SELECT 
    COUNT(*) AS TotalStaff,
    MIN(HHireDate) AS EarliestHireDate, 
    MAX(HHireDate) AS LatestHireDate
FROM Hostman_staff;

The outcome demonstrates TotalStaff, EarliestHireDate, and LatestHireDate:

Grouping and Filtering in SQL

SQL contains several clauses for grouping and filtering the table’s details, as illustrated below.

GROUP BY

It combines rows with identical entries in targeted columns into a single summary row:

SELECT HDptID, COUNT(*) AS TotalStaff
FROM Hostman_staff
GROUP BY HDptID;

The staff members are grouped by HDptID and show the total staff in each department:

HAVING

It sorts groups as per predefined aggregate criteria. It groups data after the aggregation, unlike WHERE, which filters rows before aggregation:

SELECT HDptID, COUNT(*) AS TotalStaff
FROM Hostman_staff
GROUP BY HDptID
HAVING COUNT(*) >3;

It assembles staff by HDptID, computes staff members in each department, and demonstrates only departments exceeding 3 staff members:

Aliases and Subqueries

SQL aliases shorten table and column names, while subqueries assist us in returning data by embedding one query within another.

Aliases

They are temporary names allotted to tables or columns to make queries user-friendly:

SELECT 
    HFirstName AS FN, 
    HLastName AS LN, 
    HDptID AS DID
FROM Hostman_staff AS HS;

Subqueries

SQL subqueries are referred to as the queries embedded inside another query and execute actions as per the outcomes of the outer query:

SELECT HFirstName, HLastName, HDptID
FROM Hostman_staff
WHERE HDptID = (
    SELECT HDptID
    FROM Hostman_staff
    GROUP BY HDptID
    ORDER BY COUNT(*) DESC
    LIMIT 1
);

It fetches staff members who are registered in the department with the highest number of staff:

Indexes

Indexes boost the data fetching rate but consume more memory and demand extra supervision. Let’s create an index titled idx_HFirstName on the HFirstName column of Hostman_staff:

CREATE INDEX idx_HFirstName
ON Hostman_staff (HFirstName);

To abolish an index, employ this query:

DROP INDEX 
ON Hostman_staff;

Constraints in SQL

They impose limitations on table content to sustain precision and stability:

PRIMARY KEY: Uniquely recognizes every row.
FOREIGN KEY: Sustains referential integrity among tables.
NOT NULL: Restrict NULL entries.
UNIQUE: Accept distinct entries.
CHECK: It applies a restriction check on the data.

Let’s constructs a Hostman_orders table with the columns Hostman_OID, Hostman_ODate, Hostman_EID, and more:

CREATE TABLE Hostman_orders (
    Hostman_OID INT AUTO_INCREMENT PRIMARY KEY,
    Hostman_ODate DATE NOT NULL,
    Hostman_EID INT NOT NULL,
    Hostman_OrderAmount DECIMAL(10, 2) CHECK (Hostman_OrderAmount > 0),
    Hostman_Status VARCHAR(50) DEFAULT 'Pending',
    HDptID INT,
    FOREIGN KEY (HDptID) REFERENCES Hostman_depts(HDptID),
    FOREIGN KEY (Hostman_EID) REFERENCES Hostman_staff(HStaffID),
    CHECK (Hostman_ODate >= '2020-01-01')
);

The Hostman_OID is set as the primary key, ensuring unique identification for each order. Hostman_ODate must keep a date on or after January 1, 2020. Hostman_EID must reference a valid HStaffID from the Hostman_staff table via a foreign key constraint. The HDptID references a valid HDptID from the Hostman_depts table through a foreign key constraint. Additionally, the Hostman_OrderAmount has a check constraint to ensure it holds a value greater than 0, and the Hostman_Status has a default value of Pending:

Data Modifying Statements

ALTER TABLE enables modifications to the table’s structure. These changes can involve adding, editing, or deleting columns:

ALTER TABLE Hostman_staff
ADD HStaffEmail VARCHAR(100);

It appends a column titled HStaffEmail in the Hostman_staff table:

To edit the HStaffEmail column, we employ the MODIFY clause:

ALTER TABLE Hostman_staff
MODIFY COLUMN HStaffEmail TEXT;

To delete EmpEmail from HostmanEmployee, we employ the DROP clause:

ALTER TABLE Hostman_staff
DROP COLUMN HStaffEmail;

SQL Transactions

SQL transactions make sures that multiple functions are carried out as one cohesive action to keep data precise and consistent:

COMMIT: Finalizes and keeps any modifications made during the recent transaction.
ROLLBACK: Cancels any modifications applied throughout the ongoing transaction, reversing all alterations.
SAVEPOINT: Designates a precise point within a transaction to which it return if needed.
ROLLBACK TO: Undoes modifications to the preferred savepoint if a problem emerges.

Conclusion

In this cheat sheet, we've gone over core SQL concepts for successfully supervising data in databases. Grasping fundamental SQL principles is vital for successfully manipulating and engaging with databases. We've also illustrated advanced concepts like transaction control, joins, aggregate functions, and SQL constraints that may assist you manage data more accurately.

Hostman provides a SQL database hosting for your needs.

SQL