SQL Cheat Sheet: Basic Syntax for Beginners

The SQL cheat sheet provides you with the most commonly used SQL statements for your reference. You can download the SQL cheat sheet as follows:

Table of content

• Download the PDF of the SQL Cheat Sheet
• Download the PDF for SQL Constraints, Joins, Set Operators Cheat Sheet
• Download the PDF of SQL Grouping, inbuilt, subquery, views, and temp table Cheat Sheet
• Download the PDF for SQL User Define Function Cheat Sheet
• Download the PDF for SQL Exception Handling Index, Pivot, Transactions Cheat Sheet

Reference SQL Cheat Sheet Guide for Interviews

You can download the PDF of this SQL cheat sheet

For a software Developer or a programmer, basic knowledge on SQL is a must. This helps in retrieving the information from the database and performing modifications and several operations on the data.

This SQL cheat sheet provides you with the necessary concepts required to start on with SQL. It is helpful for beginners willing to learn and work on SQL.

SQL BASICS 

Datatypes for SQL:

SQL Data Types generally define the type of data that can be stored in a column of a database table.

SQL Commands:

SQL commands allow users to interact with the data and manipulate it as needed.

DDL (Data Definition Language): Defines database schema.

– Creating Table

Creating a table named “Employee” with columns Emp_Name, Designation, and Salary.

<br>
Create table Employee (Emp_Name Varchar(25), Designation Char(25), Salary Int)<br>

– Altering data type

Reforming the data type of the “Designation” column in the “Employee” table to Char(30).

<br>
Alter table Employee<br>
Alter column Designation char(30)<br>

– Dropping a Table

Removing the “Employee” table from the database.

<br>
Drop table Employee<br>

DML (Data Manipulation Language): Manipulates data.

–Inserting a record

Adding a new record to the “Employee” table with values for Emp_Name, Designation, and Salary.

<br>
Insert into Employee Values (‘Smith’,’Analyst’, 55000)<br>

– Updating a record

Modifying the salary of an employee to 35,000 where the current salary is 25000.

<br>
Update Employee SET Salary = 35000 where Salary = 25000<br>

– Deleting a record

Removing records from the “Employee” table where the salary is less than 10000.

<br>
Delete from Employee Where Salary&amp;amp;lt;10000<br>

DCL (Data Control Language): Manages rights, permissions, and controls.

TCL (Transaction Control Language): Manages transaction operations.

DQL (Data Query Language): Performs queries.

Employee Table

Creating an “Employee” table featuring names, designations, and salaries for individuals Smith, Jason, and Andrea.

Basic Query:

Fetching All Columns

–Selecting all the columns

Let’s retrieve comprehensive information with the query “SELECT * from Employee,” displaying all columns in the table.

<br>
Select * from Employee<br>

This query is used to fetch all the columns present in the table.

– Selecting specific column

<br>
Select Designation from Employee<br>

This query will return only a particular column present in the table.

Basic Filtering Query:

 Utilizing the “Where” clause, obtain records for employees with a salary of 55000: “Select * from Employee Where Salary = 55000.”

WHERE – The Where clause is used to filter the table horizontally.

Get the employee records whose salary is 55000.

<br>
Select * from Employee Where Salary = 55000<br>

Advance Filtering Query:

With the help of a comparison operator, we can perform advanced filtering on numeric or string values.

Comparison Operator =, >, <, >=, <=, <>,LIKE, IN

Get the employee records whose salary is equal to 55000 or greater than 55000.

Select * from Employee Where Salary >= 55000

Get the employees records whose Designation contains ‘A’ in it.

Select * from Employee Where Designation LIKE '%A%'

Multiple Conditions:

With the help of a comparison operator, we can perform advanced filtering on numeric or string values.

Logical Operator - ALL, AND, ANY, BETWEEN, EXISTS, IN, LIKE, NOT, OR, SOME

Get the employee records whose Designation contains ‘A’ in it and whose salary is greater than 50000.

Select * from Employee Where Designation LIKE '%a% AND Salary >50000

Get the employee records whose Designation contains ‘A’ in it or the salary is greater than 50000.

Select * from Employee Where Designation LIKE '%a% OR Salary >50000

Sorting Records:

Sorting methods ensure a structured presentation of employee records based on their salaries for better analysis.

Sort the employee table in ascending order.

Arrange employee records in ascending order based on salary.

Select * from Employee Order by Salary<br>
Select * from Employee Order by Salary ASC

Note: By default, the order-by clause will sort in ascending order.

Sort the employee’s table in descending order.

Arrange employee records in descending order based on salary.

Select * from Employee Order by Salary DESC

Download the PDF of the SQL Cheat Sheet

CONSTRAINTS

Constraints act as gatekeepers, setting rules to shape and defend the information in a database.

(Primary Key, Foreign Key, Not Null, Unique, Default, Check)

Primary Key

A primary key is a unique identifier for a record in a database table.

Syntax for creating a primary key:

Create table student<br>
(<br>
student_id int primary key<br>
student_name varchar(30),<br>
student_age int<br>
)

Adding primary key constraints using Alter

Alter table student1<br>
Add constraint pk_student_id primary key (student_id)

if not null is not active on the column on which you are trying to make the primary key using the alter command, first you have to make that column not null.

Alter table student1<br>
Alter column student_id int<br>
not null

Foreign key

A foreign key is a column or a set of columns in a database table that refers to the primary key of another table, establishing a link between the two tables.

Create table student_course<br>
(<br>
student_id int foreign key references<br>
student(student_id),<br>
student_course varchar(100),<br>
professor varchar(100)<br>
)

Adding foreign key constraints using Alter

Alter table student_course<br>
Add constraint fk_student_Id foreign key references student(student_id)

Unique Key

A unique constraint ensures that all values in a specified column or combination of columns are distinct within a database table.

Create table student123<br>
(<br>
student_id int unique,<br>
student_name varchar(30),<br>
student_age int check (student_age &gt; 0)<br>
)

Adding unique constraints using Alter

ALTER TABLE student<br>
ADD CONSTRAINT uk_studentid UNIQUE(student_id) 

Not Null

The NOT NULL constraint means a column cannot have any empty or blank values.

Default

Use to give a default value for a column if the user is not giving any value.

Create table student_course<br>
(<br>
student_id int foreign key references student(student_id),<br>
student_course varchar(100),<br>
Professor Varchar(100) Default 'to be decided'<br>
)

Adding default constraints using Alter

Alter table student_course<br>
<span style="font-weight: 400;">Add constraint df_professor default ‘to be decided’ for professor</span>

Check Constraint

Use to give a default value for a column if the user is not giving any value.

Create table student<br>
(<br>
student_id int primary key,<br>
student_name varchar(30),<br>
student_age int check (student_age> 0)<br>
)

Adding check constraints using Alter

Alter table student<br>
Add constraint ck_age check(student_age> 0)

Joins

Joins in databases connect information from different tables based on specified conditions.

Sample Tables

Employees

Creating a table named “Employees” with columns  employee_id,employee_Name, department_id, and Salary.       

Department

Creating a table named “Department” with columns  department_id,department_Name.

Inner Join

Pairs up information from “Employees” and “Department” tables where the department ID matches, showing only the shared details.

SELECT *<br>
FROM employees<br>
INNER JOIN departments<br>
ON<br>
employees.department_id = departments.department_id;<br>

Left Join

Fetching all employee details and including matching department info makes sure no employee is left without their department.       

SELECT *<br>
FROM employees<br>
LEFT JOIN departments<br>
ON<br>
employees.department_id = departments.department_id;<br>

Right Join  

It shows all department information and includes matched employee details, ensuring no department is left without employee information.

SELECT *<br>
FROM employees<br>
RIGHT JOIN departments<br>
ON<br>
employees.department_id = departments.department_id;<br>

Full Outer Join

<br>
SELECT *<br>
FROM employees<br>
FULL JOIN departments<br>
ON<br>
employees.department_id = departments.department_id;

Combines all details from both the “Employees” and “Department” tables, giving a complete overview even if there are no matches.

Cross Join 

Mixes every employee with every department, creating all possible combinations without specific matching criteria. 

SELECT * FROM employees<br>
CROSS JOIN departments;

Self Join

Links “Employees” with itself, exploring relationships based on shared department IDs for more detailed analysis.

SELECT *<br>
FROM employees e1<br>
INNER JOIN employees e2<br>
ON e1.department_id = e2.department_id;

SET OPERATORS

Set operators assist in combining and comparing data from different queries, using tools like UNION and INTERSECT.

Employee_Details1

Creating a table named “Employee_Details1” with columns  Emp_id,Emp_Name, and Emp_Salary.

Employee_Details2

Creating a table named “Employee_Details2” with columns Emp_id,Emp_Name, and Emp_Salary.

Set operators are used to combine two result sets together.

Get all distinct rows between the Employee_Details1 and Employee_Details2 table.

UNION

Combines the result sets of two SELECT queries, removing duplicate rows.

Union Output:

SELECT * FROM Employee_Details1<br>
UNION<br>
SELECT * FROM Employee_Details2;

Merges distinct rows from two tables (Employee_Details1 and Employee_Details2), removing duplicates.

Get all rows with duplicates between the Employee_Details1 and Employee_Details2 table.

UNION ALL

Combines the result sets of two SELECT queries, including all rows, including duplicates.

SELECT * FROM Employee_Details1<br>
UNION ALL<br>
SELECT * FROM Employee_Details2;

Union All Output:

Combines all rows, including duplicates, from two tables (Employee_Details1 and Employee_Details2).

Get common rows between Employee_Details1 and Employee_Details2 table

INTERSECT

Returns common rows between the result sets of two SELECT queries, removing duplicates.

SELECT * FROM Employee_Details1<br>
INTERSECT<br>
SELECT * FROM Employee_Details2;

Intersect Output:

Retrieves common rows between two tables (Employee_Details1 and Employee_Details2), removing duplicates.

Get the rows from Employee_Details1 except the rows that are present in the Employee_Details2 table.

EXCEPT

Returns distinct rows from the result of the first SELECT query that are not present in the result of the second SELECT query.

SELECT * FROM Employee_Details1<br>
EXCEPT<br>
SELECT * FROM Employee_Details2

Except Output:

Fetches distinct rows from the first table (Employee_Details1) that are not present in the second table (Employee_Details2).

Download the PDF for SQL Constraints, Joins, Set Operators Cheat Sheet

Group By

GROUP BY is a SQL clause used to aggregate rows with identical values in specified columns, facilitating the application of aggregate functions like COUNT, SUM, AVG, etc., to grouped data.

Sample Table

Creating a table named “ Employees” with columns employeeid, employeename, employeeaddress ,phonenuber, email, age, salary, and department.

Basic syntax:- 

SELECT column1, AGGREGATE_FUNCTION(column2)<br>
FROM table_name<br>
GROUP BY column1;

Count the number of employees in each department

SELECT department, COUNT(employeeid) AS employee_count<br>
FROM employees<br>
GROUP BY department;

Calculate the avg salary for each department

SELECT department, AVG(age) AS average_age<br>
FROM employees<br>
GROUP BY department;

GROUP BY HAVING A CLAUSE

Find out the department in which avg department salary is greater than 50000.

SELECT department, AVG(salary) AS average_salary<br>
FROM employees<br>
GROUP BY department<br>
HAVING AVG(salary) > 50000;

Subquery

A subquery is a query nested inside another query that retrieves data and provides the result to the outer query for further processing.

Sample tables: employees and departments used in joins

Syntax:

SELECT column1, column2...<br>
FROM table1<br>
WHERE column1 operator (SELECT column1 FROM table2 WHERE condition);

Checking Single Value

Find the employee who is getting the highest salary among all employees.

SELECT employee_name<br>
FROM employees<br>
WHERE salary = (SELECT max(salary) FROM employees);

Checking Multiple Values

Find employees who are not in the HR department.

SELECT employee_name<br>
FROM employees<br>
WHERE department_id IN<br>
(SELECT department_id FROM departments where department_name != 'HR')

Correlated Subquery:

A subquery is a query nested inside another query that retrieves data and provides the result to the outer query for further processing.

Syntax:

SELECT column1, column2...<br>
FROM table1 alias1<br>
WHERE column1 operator (SELECT column1 FROM table2 alias2 WHERE alias1.columnX = alias2.columnX);

Find employees with salaries greater than the average salary in their department

SELECT employee_name, salary<br>
FROM employees e1<br>
WHERE salary >(SELECT AVG(salary) FROM employees e2 WHERE e1.department_id = e2.department_id);

VIEWS

Views are virtual tables in a database, defined by a query, that provide a simplified and abstracted layer for querying and presenting data from one or more underlying tables.

Syntax for creating views

CREATE VIEW view_name<br>
AS<br>
SELECT column1, column2, ...<br>
FROM table_name<br>
WHERE condition;

Updating the existing view

ALTER VIEW view_name<br>
AS<br>
SELECT new_column1, new_column2, ...<br>
FROM new_table_name<br>
WHERE new_condition;

Drop the view

DROP VIEW view_name;

TEMPORARY TABLES

Temp tables are used to store the data for a temporary period of time.

Type of temp table:

Local temporary table
Global temporary table

The syntax for creating a local temporary table:

create table #student<br>
(<br>
student_id int,<br>
student_name varchar(100),<br>
student_age varchar(100)<br>
)

Syntax for creating a global temporary table:

create table ##student<br>
(<br>
stundet_id int,<br>
student_name varchar(100),<br>
student_age varchar(100)<br>
)

Syntax for inserting a record in temporary tables:

Using the insert command

insert into #student values(1,'Sachin',12)<br>
insert into ##student values(1,'Sachin',12)

Download the PDF of SQL Grouping, inbuilt, subquery, views, and temp table Cheat Sheet

User Defined Function

Scalar Valued Function

Scalar UDFs return a single value, which can be used in SELECT statements, WHERE clauses, and more.

Syntax for creating a scalar-valued user-defined function:

CREATE FUNCTION dbo.FunctionName<br>
(<br>
@Parameter1 DataType,<br>
@Parameter2 DataType<br>
)<br>
RETURNS DataType<br>
AS<br>
BEGIN<br>
-- Function logic here<br>
RETURN ResultValue;<br>
END;

For modifying the existing function

ALTER FUNCTION dbo.FunctionName<br>
(<br>
@NewParameter DataType<br>
)<br>
RETURNS DataType<br>
AS<br>
BEGIN<br>
-- Updated function logic here<br>
RETURN ResultValue;<br>
END;

Syntax for executing the scalar user define function:

Select dbo.functioname(@parameters)

Table Valued Function

Table valued function is like a special tool that gives you a table of information when you ask for it in a query.

General syntax for creating the TVF

<br>
CREATE FUNCTION dbo.function_name<br>
(<br>
-- Input parameters (if any)<br>
@param1 datatype,<br>
@param2 datatype<br>
)<br>
RETURNS TABLE<br>
AS<br>
RETURN<br>
(<br>
-- SELECT statement to define the result set<br>
SELECT column1, column2, ...<br>
FROM your_tables_or_queries<br>
WHERE conditions (if any)<br>
);

Syntax for executing the function:-

select * from dbo.function_name(@parameters)

Table for table valued function:

Create a function to give the details of the employees with the help of employee id

create function fn_getbyemployeeid(@id int)<br>
returns table<br>
as<br>
return(select * from employees where employee_id=@id)

–Calling function:

select * from dbo.fn_getbyemployeeid(1)

STORED PROCEDURE

A stored procedure is a precompiled and reusable set of SQL statements and procedural logic stored in a database that can be invoked with parameters and executed on demand, providing a way to encapsulate and manage complex database operations.

Syntax

CREATE PROCEDURE procedure_name<br>
@parameter1 datatype1,<br>
@parameter2 datatype2,<br>
---<br>
AS<br>
BEGIN<br>
-- SQL statements<br>
END;

To execute procedure:- exec procedure_name @parameters

Procedure to give the details of employees with the help of department name.

CREATE PROCEDURE GetEmployeeByDepartment<br>
@dept_id INT<br>
AS<br>
BEGIN<br>
SELECT * FROM employees WHERE department_id = @dept_id;<br>
END;

Executing the procedure

EXEC GetEmployeeByDepartment @dept_id = 1;

Deleting the procedure

Drop procedure GetEmployeeByDepartment

Download the PDF for SQL User Define Function Cheat Sheet

Exception Handling

Exception handling in SQL Server is the process of dealing with errors or exceptions that occur during the execution of SQL code.

BEGIN TRY<br>
Select 100/0 as Result;<br>
END TRY<br>
BEGIN CATCH<br>
PRINT ERROR_MESSAGE()<br>
END CATCH;

Transaction

Commit

It is used to permanently save the changes in the database during a transaction.

Begin transaction<br>
insert into table_name values (,...)<br>
Commit

Rollback

It is used to undo the changes made during the transaction.

Begin transaction<br>
Update table_name set value=100<br>
Rollback

Savepoint

A save point is a point within a transaction that can be rolled back later.

Begin transaction<br>
Delete from table_name where=condition<br>
Savepoint savepoint_name<br>
Rollback

Indexes

An index in a database is a data structure that enhances the speed of data retrieval operations on a database table by providing a quick and efficient way to locate and access rows based on the values in one or more columns.

Syntax

CREATE INDEX index_name<br>
ON table_name (column1, column2, ...);

Example:

CREATE INDEX idx_employee_salary<br>
ON employees (salary);

Single column index

Enhances search performance by creating an index on a single column, such as salary in the “employees” table.

CREATE INDEX idx_employee_salary<br>
ON employees (salary);

Composite index (multiple columns)

Enhances search efficiency for combinations of columns, like department_id and salary in the “employees” table.

CREATE INDEX idx_employee_department<br>
ON employees (department_id, salary);

Unique index

Ensures uniqueness for a specific column, like employee_id in the “employees” table.

CREATE UNIQUE INDEX idx_employee_id<br>
ON employees (employee_id);

Clustered index

Organizing  the physical order of the table based on specified columns for quicker retrieval.

CREATE CLUSTERED INDEX idx_clustered_example<br>
ON table_name (column1, column2, ...);

Non-clustered index

Enhance search speed without altering the physical order of the table.

CREATE NONCLUSTERED INDEX idx_nonclustered_employees_salary<br>
ON employees (salary);

Dropping the index (delete)

Removes an existing index from a table.

DROP INDEX index_name<br>
ON table_name;

Pivot & Un-pivot

PIVOT rotates rows into columns by aggregating values.

Basic PIVOT syntax:

SELECT columns, pivoted_columns<br>
FROM table<br>
PIVOT<br>
(<br>
aggregate_func(value_column)<br>
FOR pivot_column IN (values)<br>
) AS pivot_table;

PIVOT Example:

Input table:

Pivot query:

SELECT Name, Apple, Orange<br>
FROM table<br>
PIVOT<br>
(<br>
SUM(Amount)<br>
FOR Product IN (Apple, Orange)<br>
) AS pivot_table;

Output:

UNPIVOT rotates columns into rows by expanding values.

Basic UNPIVOT syntax:

SELECT columns, unpivoted_column, value_column<br>
FROM table<br>
UNPIVOT<br>
(<br>
value_column FOR unpivoted_column IN (original_columns)<br>
) AS unpivot_table;

UNPIVOT Example:

Input table:

Unpivot query: 

SELECT Name, Product, Amount<br>
FROM table<br>
UNPIVOT<br>
(<br>
Amount FOR Product IN (Apple, Orange)<br>
)<br>
AS unpivot_table;

Output:

Download the PDF for SQL Exception Handling Index, Pivot, Transactions Cheat Sheet