SQL Cheat Sheet: Basic Syntax for Beginners

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.

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

– Altering data type

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

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

– Dropping a Table

Removing the “Employee” table from the database.

Drop table Employee

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.

Insert into Employee Value (‘Smith’,’Analyst’, 55000)

– Updating a record

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

Update Employee SET Salary = 35000 where Salary = 25000

– Deleting a record

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

Delete from Employee Where Salary < 10000

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.

Select * from Employee

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

– Selecting specific column

Select Designation from Table_name

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.

Select * from Employee Where Salary = 55000

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
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 Basics 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
(
student_id int primary key
student_name varchar(30),
student_age int
)

Adding primary key constraints using Alter

Alter table student1
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
Alter column student_id int 
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
(
 student_id int foreign key references
student(student_id),
 student_course varchar(100),
 professor varchar(100)
)

Adding foreign key constraints using Alter

Alter table student_course
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
(
student_id int unique,
student_name varchar(30),
student_age int check (student_age > 0)
)

Adding unique constraints using Alter

ALTER TABLE student
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
(
student_id int foreign key references student(student_id),
student_course varchar(100),
Professor Varchar(100) Default 'to be decided'
)

Adding default constraints using Alter

Alter table student_course
Add default 'to be decided' for professor

Check Constraint

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

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

Adding check constraints using Alter

Alter table student
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 *
FROM employees				
INNER JOIN departments					
ON 
employees.department_id = departments.department_id;

Left Join

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

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

Right Join  

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

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

Full Outer Join

SELECT *
FROM employees
FULL JOIN departments 
ON 
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
CROSS JOIN departments;

Self Join

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

SELECT *
FROM employees e1
INNER JOIN employees e2
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
UNION
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
UNION ALL
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
INTERSECT
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
EXCEPT
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)
FROM table_name
GROUP BY column1;

Count the number of employees in each department

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

Calculate the avg salary for each department

SELECT department, AVG(age) AS average_age
FROM employees
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
FROM employees
GROUP BY department
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...
FROM table1
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
FROM employees
WHERE salary = (SELECT max(salary) FROM employees);

Checking Multiple Values

Find employees who are not in the HR department.

SELECT employee_name
FROM employees
WHERE department_id IN
(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...
FROM table1 alias1
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
FROM employees e1
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
AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

Updating the existing view

ALTER VIEW view_name
AS
SELECT new_column1, new_column2, ...
FROM new_table_name
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
(
student_id int,
student_name varchar(100),
student_age varchar(100)
)

Syntax for creating a global temporary table:

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

Syntax for inserting a record in temporary tables:

Using the insert command

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

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
(
@Parameter1 DataType,
@Parameter2 DataType
)
RETURNS DataType
AS
BEGIN
-- Function logic here
RETURN ResultValue;
END;

For modifying the existing function

ALTER FUNCTION dbo.FunctionName
(
@NewParameter DataType
)
RETURNS DataType
AS
BEGIN
-- Updated function logic here
RETURN ResultValue;
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

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

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)
returns table
as
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
@parameter1 datatype1,
@parameter2 datatype2,
  ---
AS
BEGIN
-- SQL statements
END;

To execute procedure:- exec procedure_name @parameters

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

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

Executing the procedure

EXEC GetEmployeeByDepartment @dept_id = 1;

Deleting the procedure

Drop procedure GetEmployeeByDepartment

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
Select 100/0 as Result;
END TRY
BEGIN CATCH
PRINT ERROR_MESSAGE()
END CATCH;

Transaction

Commit

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

Begin transaction
insert into table_name values (,...)
Commit

Rollback

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

Begin transaction
Update table_name set value=100
Rollback

Savepoint

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

Begin transaction
Delete from table_name where=condition
Savepoint savepoint_name
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
ON table_name (column1, column2, ...);

Example:

CREATE INDEX idx_employee_salary
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
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
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
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
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
ON employees (salary);

Dropping the index (delete)

Removes an existing index from a table.

DROP INDEX index_name
ON table_name;

Pivot & Un-pivot

PIVOT rotates rows into columns by aggregating values.

Basic PIVOT syntax:

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

PIVOT Example:

Input table:

Pivot query:

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

Output:

UNPIVOT rotates columns into rows by expanding values.

Basic UNPIVOT syntax:

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

UNPIVOT Example:

Input table:

Unpivot query: 

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

Output:

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