SQL Basics Cheat Sheet
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_teamSET Hostman_ESalary = 62000WHERE Hostman_EID = 1;
To edit entries as per defined criteria, we can utilize UPDATE with the WHERE clause:
UPDATE hostman_teamSET Hostman_ESalary = 75000WHERE Hostman_ESalary >= 65000;
Example 6: Delete Data
If a particular entry is no longer relevant, we can remove it:
DELETE FROM hostman_teamWHERE 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_HFirstNameON 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_staffADD 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_staffMODIFY COLUMN HStaffEmail TEXT;
To delete EmpEmail from HostmanEmployee, we employ the DROP clause:
ALTER TABLE Hostman_staffDROP 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.
04 December 2024 · 11 min to read