Complete SQL With Notes

1.

2.

3.

4.

5.

6.
Introduction to SQL-What Is SQL & Database

1.

8.
Data Types, Primary-Foreign Keys & Constraints
a. Install postgresql and pgadmin4
Create Table In SQL & Create Database

INSERT UPDATE, DELETE & ALTER Table

SELECT Statement & WHERE Clause with Example

How To Import Excel File (CSV) to SQL

Functions in SQL & String Function

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

SQL By Rishabh Mishra
2

WHAT IS SQL & DATABASE-

INTRODUCTION

SQL Tutorial In Hindi-1

SQL By Rishabh Mishra
3

Introduction to SQL

What is SQL

It's applications
SQL v/s NoSQL

Types of SQL Commands

What is Database

Excel v/s Database in SQL

4
SQL By Rishabh Mishra

What is SQL?

SQL (Structured Query

Language) is a programming
language used to interact
with database

SQL
SQL By Rishabh Mishra
5

SQL Application

T
目
CREATE
READ
UPDATE
DELETE

CRUD
CRUD is an acronym for CREATE,
READ(SELECT), UPDATE, and DELETE
statements in SQL
SQL By Rishabh Mishra
6

SQL v/s NoSQL

Relational Database

SQL database

Data stored in tables

These databases have fixed or static or

predefined schema

Low performance with huge volumes of data

Eg: PostgreSQL, MySQL, MS SQL Server

Non-Relational Database
NoSQL database

Data stored are either key-value pairs, document-based, graph

databases or wide- column stores

They have dynamic schema

Easily work with huge volumes of data

Eg: MongoDB, Cassandra, Hbase

SQL By Rishabh Mishra

7

SQL Commands

There are mainly 3 types of SQL

commands:
•

DDL (Data Definition Language):

create, alter, and drop
DML (Data Manipulation
Language): select, insert, update
and delete
DCL (Data Control Language):
grant and revoke permission to
users

SQL By Rishabh Mishra

8

What is Database?
 Database is a system that
allow users to store and
organise data

SQL By Rishabh Mishra

9

Excel v/s Database

Excel
Easy to use- untrained person can work

Data stored less data

Good for one time analysis, quick charts

No data integrity due to manual operation

Low search/filter capabilities

Database

Trained person can work

Stores large amount of data

Can automate tasks

High data integrity

High search/filter capabilities

SQL By Rishabh Mishra
10

SQL Databases

Microsoft

teradata.
IBM
SQL Server

SQL
DB2

Amazon RDS ORACLE

DATABASE

snowflake
SAP S/4 HANA
SQL

MySQL

PostgreSQL

SQL By Rishabh Mishra

11

DATA TYPES, PRIMARY &

FOREIGN KEYS,
CONSTRAINTS
SQL Tutorial In Hindi-2

12
SQL By Rishabh Mishra

Database

Tables

Data

SQL Structure

Columns

Example
mysql SELECT FROM employee;

emp_id❘emp_name | emp_dept
|emp_age | emp_sex

Rows
E00001 JHONNY
BACKEND DEVELOPER

(Rows & Columns)

E00002❘ DARSHI
NULL
26 | male 27 male

E00003❘ JASMINE JASMINE

NULL
37 female
E00004❘ LILLY
NULL
47 | female
E00005❘ RONALD | UI DEVELOPER
26 | male
+-

RDBMS
5 rows in set (0.01 sec)

SQL By Rishabh Mishra

13
Database Diagram
orders

id
int
order_items
user_id
int
order_id
int
users
status
varchar
product_id
int
id
int
created_at
varchar
quantity
int
full_name
varchar

email
varchar

gender
varchar

merchants
date_of_birth
varchar

id
int

merchant_name varchar
country_code

created_at
int

varchar

products
admin_id
int

id
int
country_code
int

merchant_id
int
created_at
varchar

name
varchar

price
int
countries

code

name
int

varchar
status
varchar

continent_name varchar
created_at
varchar

SQL By Rishabh Mishra

Example

14

Creating Database &

Tables
•
•

Data types
Primary & Foreign keys
Constraints
SQL Commands
•

CREATE

• INSERT
•

UPDATE

BACKUP

• DELETE

ALTER

• DROP, TRUNCATE
SQL By Rishabh Mishra
15
Data Types
•

Data type of a column defines what value

the column can store in table

• Defined while creating tables in

database

Data types mainly classified into three

categories + most used

oString: char, varchar, etc

oNumeric: int, float, bool, etc
oDate and time: date, datetime, etc

SQL By Rishabh Mishra

16
Data Types
Commonly Used data types in SQL:
int: used for the integer value
• float: used to specify a decimal point number

bool: used to specify Boolean values true and false

char: fixed length string that can contain

numbers, letters, and special characters

varchar: variable length string that can contain

numbers, letters, and special characters
date: date format YYYY-MM-DD
datetime: date & time combination, format is
YYYY-MM-DD hh:mm:ss

SQL By Rishabh Mishra

17

Primary and Foreign Keys:

Primary key (PK):

• A Primary key is a unique column we set in a
table to easily identify and locate data in queries

• A table can have only one primary key, which

should be unique and NOT NULL

Foreign keys (FK):

• A Foreign key is a column used to link two or more
tables together
•

A table can have any number of foreign keys, can

contain duplicate and NULL values
SQL By Rishabh Mishra
18

Constraints

Constraints are used to specify rules for data in

a table
This ensures the accuracy and reliability of the
data in the table
• Constraints can be specified when the table is
created with the CREATE TABLE statement, or
•

after the table is created with the ALTER TABLE statement

Syntax
CREATE TABLE table_name (
column1 datatype constraint,
column2 datatype constraint, column3 datatype constraint,
);
....

SQL By Rishabh Mishra

19

Constraints
Commonly used constraints in SQL:
•

NOT NULL - Ensures that a column cannot have a NULL value

UNIQUE - Ensures that all values in a column are different

PRIMARY KEY - A combination of a NOT NULL and

UNIQUE

• FOREIGN KEY - Prevents actions that would destroy

links between tables (used to link multiple tables
together)
CHECK - Ensures that the values in a column
satisfies a specific condition
• DEFAULT - Sets a default value for a column if no
value is specified
• CREATE INDEX - Used to create and retrieve data from the
database very quickly

SQL By Rishabh Mishra

20

Install PostgreSQL
(Click Here)

SQL By Rishabh Mishra

21

Creating Database &

Tables
SQL Tutorial In Hindi-3

SQL By Rishabh Mishra

22

Create Table
The CREATE TABLE statement is used to create a new table in a database

Syntax
CREATE TABLE table_name

column_name1 datatype constraint, column_name2 datatype constraint,

column_name3 datatype constraint,
);

Example

(
CREATE TABLE customer

CustID int8 PRIMARY KEY,

CustName varchar(50) NOT NULL,
Age int NOT NULL,
City char(50),
Salary numeric
);
SQL By Rishabh Mishra
23

Insert, Update, Delete

 Values in Table
+

Alter, Drop & Truncate

Table

SQL Tutorial In Hindi-4

SQL By Rishabh Mishra

24

Insert Values In Table

The INSERT INTO statement is used to insert new records in
a table
•

Syntax
INSERT INTO TABLE NAME

(column1, column2, column3,...columnN)

VALUES

(value1, value2, value3,...valueN);

Example
INSERT INTO customer

(CustID, CustName, Age, City, Salary) VALUES

(1, 'Sam', 26, 'Delhi', 9000),

(2, 'Ram', 19, 'Bangalore', 11000), (3, 'Pam', 31, 'Mumbai',
6000),
(4, 'Jam', 42, 'Pune', 10000);
SQL By Rishabh Mishra
25