Master SQL : Comprehensive Cheat Sheet for Beginners

What is SQL?

Relational databases are managed and manipulated using a common language called SQL (Structured Query Language). In addition to managing database structures like tables and indexes, it enables you to carry out a number of tasks like obtaining, inserting, updating, and removing data.

Key SQL Concepts

Database

An electronically stored, well-organized collection of data is called a database. Relational databases, which store data in tables, are accessed via SQL.

Table

A table is a grouping of rows and columns used to hold data. Each row is a record of data, and each column denotes a particular property (such as Name or Age).

Example Table: Employees

ID	Name	Department	Age	Salary
1	Alice	HR	30	60000
2	Bob	IT	25	75000
3	Charlie	IT	35	80000

Primary Key

Every record in a table is uniquely identified by its primary key. Because each ID is distinct, the ID column in the Employees table above, for instance, can function as a primary key.

Foreign Key

A foreign key is a column or group of columns in one table that creates a relationship between the two tables by linking to the main key in another table.

SQL Commands

Data Definition Language (DDL)

DDL commands are used to define and modify database structure.

Command	Description	Example
CREATE DATABASE	Creates a new database.	CREATE DATABASE SchoolDB;
CREATE TABLE	Creates a new table.	CREATE TABLE Students (ID INT, Name VARCHAR(50), Age INT);
ALTER TABLE	Modifies an existing table.	ALTER TABLE Students ADD COLUMN Grade INT;
DROP TABLE	Deletes a table and its data.	DROP TABLE Students;

Data Manipulation Language (DML)

DML commands are used to manipulate the data in the tables.

Command	Description	Example
INSERT INTO	Adds new rows of data into a table.	INSERT INTO Students (ID, Name, Age) VALUES (1, 'Alice', 20);
SELECT	Retrieves data from one or more tables.	SELECT Name, Age FROM Students;
UPDATE	Updates existing data in a table.	UPDATE Students SET Age = 21 WHERE ID = 1;
DELETE	Deletes rows from a table.	DELETE FROM Students WHERE Age > 25;

Data Query Language (DQL)

DQL includes only the SELECT command, which is used to query data.

Command	Description	Example
SELECT	Retrieves specific data from a table.	SELECT Name, Age FROM Students;
SELECT DISTINCT	Retrieves unique values.	SELECT DISTINCT Department FROM Employees;

Data Control Language (DCL)

DCL commands are used to control access to the database.

Command	Description	Example
GRANT	Grants permissions to a user.	GRANT SELECT ON Students TO User1;
REVOKE	Removes permissions from a user.	REVOKE SELECT ON Students FROM User1;

Transaction Control Language (TCL)

TCL commands manage database transactions.

Command	Description
COMMIT	Saves all the changes made in a transaction.
ROLLBACK	Undoes the changes made in a transaction.

SQL Data Types

SQL Data Types are used to define the type of data that can be stored in a column. Below is a table that summarizes common SQL data types:

Data Type	Description	Example
INT	Stores integer values (whole numbers).	INT - 123
VARCHAR(n)	Stores variable-length strings (up to n characters).	VARCHAR(50) - 'John Doe'
CHAR(n)	Stores fixed-length strings (exactly n characters).	CHAR(5) - 'ABCDE'
TEXT	Stores long text strings.	TEXT - 'This is a long text string.'
DATE	Stores date values in the format YYYY-MM-DD.	DATE - '2024-12-05'
DATETIME	Stores date and time values in the format YYYY-MM-DD HH:MM:SS.	DATETIME - '2024-12-05 12:30:00'
BOOLEAN	Stores TRUE or FALSE values.	BOOLEAN - TRUE
DECIMAL(p, s)	Stores fixed-point numbers, p represents precision, s represents scale.	DECIMAL(5,2) - 123.45
FLOAT	Stores floating-point numbers with approximate precision.	FLOAT - 123.456789
BLOB	Stores binary large objects such as images, files, etc.	BLOB - <binary data>
NUMERIC	Stores exact numeric data, similar to DECIMAL.	NUMERIC(10,2) - 12345.67
TIME	Stores time values in the format HH:MM:SS.	TIME - '12:30:00'

SQL Operators

SQL operators are used to perform operations on data, such as comparisons, logical conditions, and mathematical calculations. Below is a table summarizing common SQL operators:

Operator	Description	Example
=	Used to check equality between two expressions.	SELECT * FROM Employees WHERE Age = 30;
!= or <>	Used to check inequality between two expressions.	SELECT * FROM Employees WHERE Age != 30;
>	Greater than operator.	SELECT * FROM Employees WHERE Salary > 50000;
<	Less than operator.	SELECT * FROM Employees WHERE Salary < 50000;
>=	Greater than or equal to operator.	SELECT * FROM Employees WHERE Salary >= 50000;
<=	Less than or equal to operator.	SELECT * FROM Employees WHERE Salary <= 50000;
BETWEEN	Used to filter results within a specific range of values (inclusive).	SELECT * FROM Employees WHERE Salary BETWEEN 40000 AND 60000;
IN	Used to check if a value matches any value in a list.	SELECT * FROM Employees WHERE Department IN ('HR', 'IT');
LIKE	Used for pattern matching with wildcard characters (% for multiple characters and _ for a single character).	SELECT * FROM Employees WHERE Name LIKE 'J%';
IS NULL	Used to check if a value is NULL.	SELECT * FROM Employees WHERE Address IS NULL;
IS NOT NULL	Used to check if a value is NOT NULL.	SELECT * FROM Employees WHERE Address IS NOT NULL;
AND	Logical operator used to combine multiple conditions.	SELECT * FROM Employees WHERE Age > 25 AND Salary > 50000;
OR	Logical operator used to combine multiple conditions.	SELECT * FROM Employees WHERE Age > 25 OR Salary > 50000;
NOT	Logical operator used to negate a condition.	SELECT * FROM Employees WHERE NOT Age = 30;

SQL Functions

SQL functions are used to perform operations on data, such as manipulating strings, performing calculations, or working with dates. Below is a table summarizing common SQL functions:

Function	Description	Example
CONCAT()	Concatenates two or more strings.	SELECT CONCAT(FirstName, ' ', LastName) AS FullName FROM Employees;
UPPER()	Converts a string to uppercase.	SELECT UPPER(FirstName) AS UpperCaseName FROM Employees;
LOWER()	Converts a string to lowercase.	SELECT LOWER(FirstName) AS LowerCaseName FROM Employees;
LEN()	Returns the length of a string.	SELECT LEN(FirstName) AS NameLength FROM Employees;
GETDATE()	Returns the current system date and time.	SELECT GETDATE();
DATEADD()	Adds a specified time interval to a date.	SELECT DATEADD(MONTH, 1, GETDATE()) AS NextMonth;
DATEDIFF()	Returns the difference between two dates.	SELECT DATEDIFF(DAY, '2024-01-01', GETDATE()) AS DaysDiff;
ROUND()	Rounds a numeric value to a specified number of decimal places.	SELECT ROUND(Salary, 2) AS RoundedSalary FROM Employees;
COUNT()	Returns the number of rows that match a specified condition.	SELECT COUNT(*) AS TotalEmployees FROM Employees;
SUM()	Returns the sum of a numeric column.	SELECT SUM(Salary) AS TotalSalary FROM Employees;
AVG()	Returns the average value of a numeric column.	SELECT AVG(Salary) AS AverageSalary FROM Employees;
MAX()	Returns the maximum value of a numeric or date column.	SELECT MAX(Salary) AS HighestSalary FROM Employees;
MIN()	Returns the minimum value of a numeric or date column.	SELECT MIN(Salary) AS LowestSalary FROM Employees;
ABS()	Returns the absolute value of a number.	SELECT ABS(Salary - 60000) AS SalaryDifference FROM Employees;
COALESCE()	Returns the first non-NULL value in a list of expressions.	SELECT COALESCE(Salary, 0) AS SalaryOrDefault FROM Employees;

SQL Clauses

Clause	Description	Example
WHERE	Used to filter records based on specified conditions.	SELECT * FROM Employees WHERE Age > 30;
GROUP BY	Used to group rows that have the same values in specified columns into summary rows.	SELECT Department, COUNT(*) FROM Employees GROUP BY Department;
HAVING	Used to filter groups based on a condition (similar to WHERE, but for groups).	SELECT Department, COUNT(*) FROM Employees GROUP BY Department HAVING COUNT(*) > 1;
ORDER BY	Used to sort the result set in either ascending or descending order.	SELECT * FROM Employees ORDER BY Salary DESC;

Common SQL Queries

1. Create a Database in SQL

To create a new database, the CREATE DATABASE command is used.

CREATE DATABASE CompanyDB;

This creates a new database named CompanyDB.

2. Create Table in SQL

The CREATE TABLE command is used to create a table. We need to specify the data type for each column.

CREATE TABLE Employees (

    EmployeeID INT PRIMARY KEY,

    Name VARCHAR(50),

    Department VARCHAR(50),

    Salary DECIMAL(10, 2)

);

3. Insert Data in SQL

The INSERT INTO statement is used to add data to a table. One or more rows can be worked on at once.

INSERT INTO Employees (EmployeeID, Name, Department, Salary)

VALUES (1, 'Alice', 'HR', 60000);

4. Reading/Querying Data in SQL

To retrieve data from a table, the SELECT statement is used.

SELECT * FROM Employees;

You can retrieve specific columns by specifying the column names:

SELECT Name, Department FROM Employees;

5. Filtering Data in SQL

Use the WHERE clause to filter results based on certain conditions.

SELECT * FROM Employees

WHERE Salary > 70000;

This will give all the columns for the employees whose salary is greater than 70000.

6. Updating/Manipulating Data in SQL

Use the UPDATE statement to modify existing records.

UPDATE Employees

SET Salary = 70000

WHERE EmployeeID = 1;

This will update the salary of the employee with EmployeeID = 1 to 70000.

7. Deleting Data in SQL

Use the DELETE statement to remove data from a table.

DELETE FROM Employees

WHERE EmployeeID = 1;

This will delete the employee record with EmployeeID = 1.

8. SQL Operators

SQL operators are used to perform operations on data.

Comparison Operators:

• =: Equal to

• >: Greater than

• <: Less than

• >=: Greater than or equal to

• <=: Less than or equal to

• <>: Not equal to

  
  

Example:

SELECT * FROM Employees

WHERE Salary >= 70000;

Logical Operators:

• AND: If every condition is met, returns true.

• OR: If at least one condition is true, OR returns true.

• NOT: Reverses the condition's outcome; if the condition is false, returns true.

Example:

SELECT * FROM Employees

WHERE Salary > 50000 AND Department = 'HR';

This will return all the details of employees of ‘HR’ department and having salary greater than 50000.

9. Constraints in SQL

Constraints ensure data integrity. Some common constraints are:

• PRIMARY KEY: Uniquely identifies each record.

• FOREIGN KEY: Creates a relationship between two tables.

• NOT NULL: Ensures a column cannot have a NULL value.

• UNIQUE: Ensures all values in a column are unique.

  
  

Example:

CREATE TABLE Departments (

    DepartmentID INT PRIMARY KEY,

    DepartmentName VARCHAR(50) NOT NULL

);

This above query illustrates primary key and not null constraints.

10. Joins in SQL

Joins are used to combine rows from two or more tables based on a related column.

Types of Joins:

• INNER JOIN: Returns only rows with matching values in both tables.

• LEFT JOIN: Returns all rows from the left table, and matching rows from the right table.

• RIGHT JOIN: Returns all rows from the right table, and matching rows from the left table.

• FULL JOIN: Returns rows when there is a match in either the left or right table.

  
  

Example:

SELECT Employees.Name, Departments.DepartmentName

FROM Employees

INNER JOIN Departments

ON Employees.DepartmentID = Departments.DepartmentID;

The above example illustrates an inner join between two tables Employees & Departments based on a common column (DepartmentID).

11. SQL Functions

Data operations are carried out and results are returned using SQL functions. Based on their functionality, these functions are divided into many types, including date functions, string functions, numeric functions, and aggregate functions. The most often utilized SQL functions are listed below:

String Functions

String functions are used to perform operations on text data. These operations can include manipulating, extracting, or modifying string values.

• CONCAT(): Concatenates two or more strings into a single string.

Syntax:

CONCAT(string1, string2, ...)

Example:

SELECT CONCAT(Name, ' - ', Department) AS EmployeeDetails FROM Employees;

This will combine the Name and Department columns into one string with a separator " - ".

• UPPER(): Converts a string to uppercase.

Syntax:

UPPER(string)

Example:

SELECT UPPER(Name) AS UppercaseName FROM Employees;

This will display all the values of Name column in upper case.

• LOWER(): Converts a string to lowercase.

Syntax:

LOWER(string)

Example:

SELECT LOWER(Department) AS LowercaseDepartment FROM Employees;

This will display all the values of Department column in lower case.

• LEN(): Returns the number of characters in a string.

Syntax:

LEN(string)

Example:

SELECT LEN(Name) AS NameLength FROM Employees;

This will display length of all the values of Name column.

Date Functions

In SQL, date and time values can be changed using date functions. They assist with managing dates in queries, doing computations, and extracting particular portions of a date.

• GETDATE(): Returns the current system date and time.

  
  

• DATEADD(): Adds a specified time interval to a date.

Syntax:

DATEADD(datepart, number, date)

Example:

SELECT DATEADD(MONTH, 2, GETDATE()) AS NewDate;

This will show a date that is two months ahead of the present date.

• DATEDIFF(): Returns the difference between two dates.

Syntax:

DATEDIFF(datepart, startdate, enddate)

Example:

SELECT DATEDIFF(DAY, '2024-01-01', GETDATE()) AS DaysDifference;

This will give the difference in today’s date and, '2024-01-01' in number of days.

Numeric Functions

Numeric functions are used to perform mathematical operations on numeric data, such as rounding, finding maximum or minimum values, etc.

• ROUND(): Rounds a number to a specified decimal place.

  
  

Syntax:

ROUND(number, decimals)

Example:

SELECT ROUND(Salary, 2) AS RoundedSalary FROM Employees;

This will return values of column salary rounded to 2 decimal places.

• CEILING(): Returns the smallest integer greater than or equal to a given number.

Syntax:

CEILING(number)

Example:

SELECT CEILING(Salary / 1000) AS RoundedSalary FROM Employees;

• FLOOR(): Returns the largest integer less than or equal to a given number.

Syntax:

FLOOR(number)

Example:

SELECT FLOOR(Salary / 1000) AS RoundedSalary FROM Employees;

• ABS(): Returns the absolute value of a number (removes the sign).

Syntax:

ABS(number)

Example:

SELECT ABS(Salary - 65000) AS SalaryDifference FROM Employees;

Aggregate Functions

Calculations on a collection of rows are carried out using aggregate functions, which yield a single result. The GROUP BY clause is usually used in conjunction with these.

• COUNT(): Returns the number of rows that match a specified condition.

Syntax:

COUNT(column_name)

Example:

SELECT COUNT(*) AS TotalEmployees FROM Employees;

• SUM(): Returns the sum of the values in a column.

Syntax:

SUM(column_name)

Example:

SELECT SUM(Salary) AS TotalSalary FROM Employees;

• AVG(): Returns the average value of a numeric column.

Syntax:

AVG(column_name)

Example:

SELECT AVG(Salary) AS AverageSalary FROM Employees;

• MAX(): Returns the maximum value of a column.

Syntax:

MAX(column_name)

Example:

SELECT MAX(Salary) AS HighestSalary FROM Employees;

• MIN(): Returns the minimum value of a column.

Syntax:

MIN(column_name)

Example:

SELECT MIN(Salary) AS LowestSalary FROM Employees;

Other SQL Functions

• NULLIF(): Returns NULL if two expressions are equal; otherwise, it returns the first expression.

Syntax:

NULLIF(expression1, expression2)

Example:

SELECT NULLIF(Salary, 60000) AS SalaryComparison FROM Employees;

• COALESCE(): Returns the first non-null expression in a list.

Syntax:

COALESCE(expression1, expression2, ...)

Example:

SELECT COALESCE(Salary, 0) AS SalaryOrDefault FROM Employees;

12. Subqueries in SQL

A subquery is a query nested inside another query. Subqueries can be used in SELECT, INSERT, UPDATE, or DELETE statements.

Example:

SELECT Name FROM Employees

WHERE Salary > (SELECT AVG(Salary) FROM Employees);

13. Views in SQL

A view is a virtual table based on the result of an SQL query. It simplifies complex queries and improves data security.

Example:

CREATE VIEW EmployeeDetails AS

SELECT Name, Department, Salary FROM Employees;

You can now query the view like a table:

SELECT * FROM EmployeeDetails;

14. Indexes in SQL

Indexes improve the speed of data retrieval operations on a table. You can create an index on one or more columns.

Example:

CREATE INDEX idx_salary ON Employees(Salary);

15. Transactions in SQL

A transaction is a sequence of operations performed as a single unit. If any operation fails, all changes can be rolled back to maintain data integrity.

• BEGIN TRANSACTION: Starts a transaction.

• COMMIT: Saves the changes made during the transaction.

• ROLLBACK: Reverts the changes made during the transaction.

Conclusion

In conclusion, all of the fundamental commands and ideas that every novice should understand have been addressed in this SQL cheat sheet. Having these SQL fundamentals at your fingertips will make navigating databases easier, regardless of your level of experience. Gaining proficiency with these commands will enable you to efficiently write queries, filter data, and work with tables.

Call to Action (CTA)

Are you prepared to advance your database skills and realize SQL's full potential? Take the Data Analytics Certification Course at IOTA Academy to begin learning SQL. Learn from professionals in the field, get practical experience with real-world data, and improve your capacity for effective data management, analysis, and querying. Enroll now to hone your abilities and master SQL!