SQL Notes

Table of Contents

1. Introduction to SQL -What Is SQL & Database

2. Data Types, Primary -Foreign Keys & Constraints a. Install postgresql and pgadmin4

3. Create Table In SQL & Create Database

4. INSERT UPDATE, DELETE & ALTER Table

5. SELECT Statement & WHERE Clause with Example

6. How To Import Excel File (CSV) to SQL

7. Functions in SQL & String Function

8. Aggregate Functions – Types & Syntax

9. Group By and Having Clause

10. Time Stamp and Extract Function, Date Time Function

11. SQL JOINS – Types & Syntax

12. SELF JOIN, UNION & UNION ALL

13. Subquery

14. Window Function – Types & Syntax

15. Case Statement/Expression with examples

16. CTE - Common Table Expression with examples

1. Introduction to SQL - What Is SQL & Database

SQL (Structured Query Language):

● SQL is a standard programming language specifically designed for managing and

manipulating databases.
● It allows users to query, insert, update, delete, and manage data stored in relational
databases.

Database:

● A database is an organized collection of data. It consists of tables, which hold

records (rows) and fields (columns).

Example:

Output:
The query will return all rows and columns from the employees table.

1. Introduction to SQL Syntax with Examples and Output of Code

Introduction: SQL (Structured Query Language) is a standard programming language used to

interact with databases. It is used for querying, updating, and managing data.

Syntax:
Example:

2. What Is SQL & Database Syntax with Examples and Output of Code

Introduction: SQL is a language used to manage relational databases. A database is an organized

collection of data stored electronically.

Creating a Database:

Syntax:

Example:
Output:

● The command creates a new database named “company_db”.

3. Data Types Syntax with Examples and Output of Code

Introduction: SQL supports various data types for defining the nature of data to be stored in a
table's columns.

Syntax:

Example:
4. Primary & Foreign Keys Syntax with Examples and Output of Code

Introduction: Primary keys uniquely identify each record in a table, while foreign keys establish
relationships between tables.

Syntax:

5. Constraints Syntax with Examples and Output of Code

Introduction: Constraints enforce rules at the column level, ensuring data integrity.

Syntax:
Output:

● The employees table is created with constraints to ensure data integrity.

6. Install PostgreSQL and pgAdmin4

Introduction: PostgreSQL is an open-source relational database management system, and

pgAdmin4 is a web-based interface to interact with PostgreSQL databases.

Steps:

1. Install PostgreSQL:
○ On Ubuntu: sudo apt-get install postgresql postgresql-contrib
○ On Windows: Download the installer from the PostgreSQL website.
2. Install pgAdmin4:
○ On Ubuntu: sudo apt-get install pgadmin4
○ On Windows: pgAdmin is included in the PostgreSQL installer.

7. Create Database Syntax with Examples and Output of Code

Syntax:
Example:

Output:

● A database named company_db is created.

8. Create Table In SQL Syntax with Examples and Output of Code

Syntax:

Example:

Output:

● A table named employees is created.

9. INSERT Syntax with Examples and Output of Code

Syntax:

Example:

Output:

● A new record is inserted into the employees table.

10. UPDATE Syntax with Examples and Output of Code

Syntax:

Example:

Output:
 ● The department ID for the employee with employee_id 1 is updated to 2.

11. DELETE Syntax with Examples and Output of Code

Syntax:

Example:

Output:

● The record for the employee with employee_id 1 is deleted.

12. ALTER Table Syntax with Examples and Output of Code

Syntax:

Example:
Output:

● A new column email is added to the employees table.

13. SELECT Statement & Syntax with Examples and Output of Code

Syntax:

Example:

14. WHERE Clause Syntax with Examples and Output of Code

Syntax:
Example:

15. How To Import Excel File (CSV) to SQL Syntax with Examples and Output of Code

Syntax:

Example:
Output:

● Data from the CSV file is imported into the employees table.

16. Functions in SQL Syntax with Examples and Output of Code

Syntax:

Example:

17. String Function Syntax with Examples and Output of Code

Common String Functions:

● UPPER(): Converts a string to uppercase.

● LOWER(): Converts a string to lowercase.
● CONCAT(): Concatenates two or more strings.
Syntax:

Example:

18. Aggregate Functions – Types & Syntax with Examples and Output of Code

Common Aggregate Functions:

● COUNT(): Counts the number of rows.

● SUM(): Sums up values.
● AVG(): Calculates the average.
● MAX(): Finds the maximum value.
● MIN(): Finds the minimum value.

Syntax:

Example:
19. Group By Syntax with Examples and Output of Code

Syntax:

Example:
20. Having Clause Syntax with Examples and Output of Code

Syntax:

Example:

21. Time Stamp Syntax with Examples and Output of Code

Introduction: A timestamp is a data type that stores both date and time information.

Syntax:
Example:

Output:

● A record is inserted with the current date and time in the event_time column.

22. Extract Function with Syntax with Examples and Output of Code

Introduction: The EXTRACT function is used to retrieve a part of a date or time value.

Syntax:

Example:
23. Date Time Function Syntax with Examples and Output of Code

Introduction: Date and time functions in SQL are used to manipulate date and time values.

Common Functions:

● NOW(): Returns the current date and time.

● DATE_PART(): Extracts a specific part of the date/time.

Example:

24. SQL JOINS – Types & Syntax with Examples and Output of Code

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

Types:

● INNER JOIN: Returns records with matching values in both tables.

 ● LEFT JOIN: Returns all records from the left table, and the matched records from the right
table.

Syntax:

Example:

25. SELF JOIN Syntax with Examples and Output of Code

Introduction: A self-join is a regular join but the table is joined with itself.

Syntax:
Example:

26. UNION Syntax with Examples and Output of Code

Introduction: The UNION operator is used to combine the result-set of two or more SELECT
statements.

Syntax:

Example:

Output:

● A combined list of employee and contractor names.

27. UNION ALL Syntax with Examples and Output of Code

Introduction: The UNION ALL operator is similar to UNION, but it includes all duplicates.

Syntax:

Example:

Output:

● A combined list of employee and contractor names, including duplicates.

28. Subquery Syntax with Examples and Output of Code

Introduction: A subquery is a query nested inside another query.

Syntax:
Example:

Output:

● List of employees with a salary above the average.

29. Window Function – Types Syntax with Examples and Output of Code

Introduction: Window functions perform a calculation across a set of table rows related to the
current row.

Syntax:

Example:
30. Case Statement/Expression Syntax with Examples and Output of Code

Introduction: The CASE statement is used to return values based on different conditions.

Syntax:

Example:

31. CTE - Common Table Expression with Examples and Output of Code

Introduction: A CTE (Common Table Expression) is a temporary result set that can be referenced
within a SELECT, INSERT, UPDATE, or DELETE statement.
Syntax:

Example:

These detailed notes will serve as a comprehensive guide for learning SQL, covering syntax,
examples, and expected outputs for each SQL operation.
 Revise SQL Course

BASIC to ADVANCE

WHAT IS SQL &

DATABASE-
INTRODUCTION
DATA TYPES, PRIMARY
& FOREIGN KEYS,
CONSTRAINTS
Creating Database &
Tables
SELECT & WHERE
CLAUSE
IMPORT CSV FILE

STRING FUNCTION
 AGGREGATE
FUNCTION

GROUP BY & HAVING

CLAUSE
TIMESTAMPS &
EXTRACT
JOINS
SELF JOIN
SUB QUERY
SUB QUERY
WINDOWS FUNCTION
CASE EXPRESSION
COMMON TABLE
EXPRESSION
SQL Interview Questions And Answer