SQL CHEATSHEET

Introduction: What is SQL?

To understand SQL, we must first understand databases and database management systems (DBMS).

Data is essentially a collection of facts about a specific object. A database is a collection of small units of data that are organised in a logical manner. A Relational Database Management System (RDBMS) is a set of tools that allows users to manage, organise, and visualise the contents of a database while adhering to some common criteria that allow for fast response between the database and the user side.

After learning about data, databases, and DBMS/RDBMS, we can finally learn about SQL. SQL, or Structured Query Language, is the language that we (the users) use to communicate with databases and extract the information we need. It is used to store, manipulate, and retrieve data from a database.

SQL Features

SQL allows us to interact with databases and extract/manipulate data from them. We can use SQL to create our own databases and then populate them with data in the form of tables.

DDL (Data Definition Language) COMMANDS:

1.CREATE to create a new table or database.

2.ALTER for alteration.

3.Truncate to delete data from the table.

4.DROP to drop a table.

5. RENAME to rename a table.

DML (Data Manipulation Language) COMMANDS:

1. INSERT : It is used to insert data into a table.

2. UPDATE: It is used to update existing data within a table.

3. DELETE : It is used to delete records from a database table.

4. LOCK: Table control concurrency.

5. CALL: Call a PL/SQL or JAVA subprogram.

6. EXPLAIN PLAN: It describes the access path to data.

DQL (Data Query Language) COMMANDS:

1. SELECT STATEMENT: 

1. DISTINCT: Returns the identical rows.

1. WHERE: Used for giving constraints.

AND OR NOT: Logical Operators.

The WHERE clause can be combined with AND, OR, and NOT operators.

The AND and OR operators are used to filter records based on more than one condition:

1. The AND operator displays a record if all the conditions separated by AND are TRUE.

1. The OR operator displays a record if any of the conditions separated by OR is TRUE.

1. The NOT operator displays a record if the condition(s) is NOT TRUE.

1. BETWEEN:

1. NOT BETWEEN:

1. IN: It allows you to assign multiple values to the WHERE Clause. An alternative to multiple OR operators.

1. Can also be used with NOT Operators.

ARITHMETIC OPERATORS: +, -, *, /, %

    For example, if we want to return new salary of employees from employee table

by adding 10% of the previous salary:

STRING OPERATIONS:

WILDCARDS: Used for pattern matching along with LIKE operators.

1. % (for zero or more characters):

1. _ (for single character):

ALIASES: Gives temporary names to columns and tables. Used with AS operator.

1. Column: 

1. Table: 

ORDER BY: Used to sort columns.

1. Ascending (default):

1. Descending:

SET OPERATIONS:

1. UNION: Used to combine the result from two SELECT statements without duplicates.

1. UNION ALL: Used to combine the result from two SELECT statements with duplicates.

1. INTERSECTION: Returns only common rows returned by the two SELECT statements

1. EXCEPT: Returns only those rows that are not returned by the second SELECT statement.

AGGREGATE FUNCTIONS: MIN, MAX, SUM, AVG, COUNT

a. COUNT function is used to Count the number of rows in a database table. It can work on both numeric and non-numeric data types.

b. SUM function: Sum function is used to calculate the sum of all selected columns. It works on numeric fields only.

c. AVG function: The AVG function is used to calculate the average value of the numeric type. AVG function returns the average of all non-Null values.

d. MAX function: MAX function is used to find the maximum value of a certain column. This function determines the largest value of all selected values of a column.

e. MIN function: MIN function is used to find the minimum value of a certain column. This function determines the smallest value of all selected values of a column. 

GROUP BY: Used to arrange distinct data into groups. Often used with aggregate functions. Can be followed by ORDER BY to sort the groups formed.

For example, we want to find the average salary of employees from each department:

HAVING: Used with GROUP BY to check for conditions.

For example, we want only those departments from the previous example where salary is greater the 40000:

NESTED SUBQUERY:

A Subquery or Inner query or a Nested query is a query within another SQL query and embedded within the WHERE clause.

A subquery is used to return data that will be used in the main query as a condition to further restrict the data to be retrieved.

Subqueries can be used with the SELECT, INSERT, UPDATE, and DELETE statements along with the operators like =, <, >, >=, <=, IN, SOME, BETWEEN, etc.

JOINS: Combines rows from two or more tables, based on a related column between them.

(INNER) JOIN: Returns records that have matching values in both tables

LEFT (OUTER) JOIN: Returns all reco36rds from the left table, and the matched records from the right table

RIGHT (OUTER) JOIN: Returns all records from the right table, and the matched records from the left table

FULL (OUTER) JOIN: Returns all records when there is a match in either left or right table

This blog gives you a rough pathway of core questions ans topics to follow. Ofcourse, you can and should explore more on your journey. There will be new blogs coming on SQL interview preparation soon, so stay tuned. Also, if you have any particular topic that you want covered, please write to us. If you wanna connect with me, here is my linkedin profile — Sneha Biswas. If you have any questions, feel free to contact me.