-- SQL: Day-1: Date: 19/05/2025

- What is data science?

- What is software?
- Layers of the applications?
- Key components of data science?
- Positions in data science?
- Real time examples

----------------------------------

> What is meant by Database?

- Database is a collection of the data that can be easily accessible.

- Means in the database User Data and Company Data will be

present.

> What is DBMS? Database Management System?

- DBMS is useful for the transfer of the data from the "Client To
Database" or "Database To Client".

- There are some DBMS or Databases

1) Oracle
2) MySQL
3) MySQL Server
4) SQL Lite
5) MongoDB
6) PostgreSQL
> What is a relational database? RDBMS?

- In RDBMS(Relational Database Management System) all tables are

connected to each other hence it is called "Relational Database".

Note: We are going to use "MySQL" Database.

> What are the advantages of the MYSQL?

1) Cross Platform:

- There are not any limitations of the platform for the use: Operating
System: Windows, Ubuntu, Linux, Android

2) Language Independent:

- We can use MYSQL with multiple languages.

3) MySQL is Open Source Software: (Free of Cost available in the

market for the use)

4) MySQL RDBMS

5) MySQL is faster compared to other databases.

----------------------------------

> What is meant by SQL?

- SQL is nothing but "Structure Query Language" it is a standard
programming language used to "manage" and "manipulate" relational
databases.

- We can communicate with different databases with the help SQL

language.

- By using the commands of the SQL we can "Create the Database",

"We can Create the Table", "We can insert the Data", "We can update
the Data", "We can Delete the Data".....number of operations we can
perform with help of the SQL.

> Types of the SQL?

- So SQL Commands are divided into different categories.

1) DDL Commands
2) DML Commands
3) TCL Commands
4) DCL Commands
5) DQL Commands

--------------------------------

1) DDL(Data Definition Language):

- By using DDL Commands we can define the "Structure" or "Schema"

of the Table.

> Important Commands:

A) CREATE:
- If we want to create a New "Database" or "Table" then that time we
can use "CREATE" Command

B) ALTER:

- If we want to "Change the existing Structure or Schema" of the table

then we will use "ALTER" Command

- For Ex: Add or Delete Column, Change the Datatype, Change the
name of the column or table

C) DROP:

- If we want to delete the "Whole Table" or "Specific Column" then that

time we will use "DROP" Command

D) TRUNCATE:

- If we want to delete "whole records" or "rows" from the table in "one

go" and we want table structure or schema to remain in the database
so in that condition we will use "TRUNCATE" Command

-- SQL: Day-2: Date: 20/05/2025

-- 2) DML(Data Manipulation Language) Commands:

-- We use DML Commands to perform the actions on the data present

inside the table.

-- Important Commands:
-- A) INSERT INTO:
-- If we have to insert or add data in the table then we will use
"INSERT INTO" command

-- B) UPDATE:
-- If we want to change or update or modify data present inside the
table then we will use "UPDATE" command

-- C) DELETE:
-- If we want to delete data or row from the existing table then we will
use "DELETE" Command

-- 3) DCL(Data Control Language) Commands:

-- By using these commands we can control the data access part

-- Means we have to "give" or "remove" access or permission of the
data then we will use "DCL Commands

-- Important Commands:

-- A) GRANT:
-- We use this command to provide the "permission" to access the
data(viewer, editor)

-- B) REVOKE:
-- If we want to remove or revoke given "permissions" to access the
data then we will use "REVOKE" Command

-- SQL: Day-3: Date: 21/05/2025

-- 4) TCL(Transactions Control Language) Commands:

-- These commands are used to manage transactions to ensure data
integrity

-- Important Commands:

-- 1) BEGIN/START TRANSACTION:
-- This command is used to start the transactions or transactions will
consider from this command

-- 2) COMMIT:

-- This command is used to save changes permanently during the

current transaction process

-- 3) ROLLBACK:

-- If we want to undo(revert) changes made during the current

transactions we will use "ROLLBACK" command

-- 4) SAVEPOINT:

-- Set a point within transactions to which you can rollback the

changes

-------------------------------------------
-- 5) DQL: Data Query Language

-- If we want to retrieve or fetch the data from the table then we will
use "DQL" Command

-- Important Command:
-- A) SELECT:
-- For retrieve or fetch the data from the table
-- We can fetch specific column data or whole table data

--------------------------------------------
-- What are the rules followed in the MySQL or SQL Commands?

-- 1) First we need to select database for run different commands

-- Command: USE Database_name;

-- For Ex: USE ds_batch_22;

-- 2) Syntax Rule:
-- SQL Commands must end with a semicolon(;)
-- This tells the MySQL Workbench that the query is complete and
ready to execute or run

-- 3) Case in-sensitivity:
-- SQL Commands are case-insensitive, so you can write down them
in the "Uppercase" or "Lowercase"
-- For good practice or readability purpose we can write down the
command keywords in the uppercase (USE, SELECT, UPDATE,
DELETE)

-- 4) Table and Column Name:

-- Table name and column name can be includes,

-- Letters: (a-z), (A-Z)

-- Numbers: (0-9)
-- Underscore: (__)
/*
Note:
1) Space between the table or column name will not be allowed
2) There is a limit for the table name and column name of the "64"
Characters
*/

-------------------------------------------------
-- Now we will create one new table,

-- Command for the Create New Table:

CREATE TABLE Table_Name(Column_Name1 Datatype(LIMIT),

Column_Name2 Datatype(LIMIT));

-- For Ex: CREATE TABLE DS_BATCH_22(Student_Name

VARCHAR(10), Student_City VARCHAR(10),
Student_Mobile_Number BIGINT);

-- SELECT * FROM Table_Name; -- Command for the fetch the data

from table

-- For Ex: SELECT * FROM ds_batch_22;

-- Now we will insert data in the table,

-- Command: INSERT INTO Table_Name VALUES('Value1', 'Value2',

'Value3');

-- For Ex: INSERT INTO DS_BATCH_22 VALUES ('BharatKale',

'Pune', 989898989);
-- SQL: Day-4: Date: 22/05/2025

-- Datatype:
-- Datatype are used to specify the what kind of data we can stored in
the columns of the table

-- Types:

-- 1) String Data Types:

-- If we want to store the text value in the column then we will use the
"String" datatype.

-- A) CHAR:
-- This is fixed length datatype
-- If we stored or enter shorter value than specified size or limit then
"MySQL" will pad the remaining value with the help spaces in the right
side

-- For Ex: First_Name CHAR(10)

-- First_Name='Bharat'

-- So here we added "6 Characters" in the name but as per the

standard of the "CHAR" datatype it will "4 Trailing Spaces" in the right
side

-- B) VARCHAR:
-- This is variable length datatype

-- For Ex: First_Name VARCHAR(10)

-- First_Name='Bharat'

-- So here only 6 spaces will be occupied and remaining spaces will

be free

-- C) TEXT:

-- We will use this datatype if the size of the data is too large
-- Maximum: 65535 characters we can add

--------------------------------------------------
-- 2) Numerical Data Types:
-- Numerical data types used to store "Numeric Data Or Integer Data".

-- A) INT:
-- If we want to store the "Integers" then we will use "INT" Datatype
-- We can store the values from: -2,147,483,648 To 2,147,483,647

-- B) BIGINT:
-- When we have to enter large numeric value then that time we will
use "BIGINT" datatype
-- Range: -9,223,372,036,854,775,808 TO 9,223,372,036,854,775,807

-- C) FLOAT:
-- If we want to store floating point numbers then we will use "FLOAT"
datatype
-- Up To 7 Decimal Digit

-- For Ex: 2.1145666

-- D) DOUBLE:
-- It is similar to FLOAT but double precision
-- Up to 15 Decimal Digit

-- For Ex: 2.143143143143123

-- E) DECIMAL(M,D):

-- Means if we want to define size of the numerical values with exact

decimal points then we will use this datatype

-- M: Total Number of Digit

-- D: Number of digits after the decimal points

-- For Ex: DECIMAL(5,2): 123.45

-----------------------------------------
-- 3) DATE and TIME Datatype:

-- A) DATE:
-- Format: 'YYYY-MM-DD'
-- Range: 1000-01-01 TO 9999-12-31
-- This data type we can use for the "Birth Date", "Events Date", or
"Joining Date"

-- B) TIME:
-- Format: 'HH:MM:SS'
-- For Ex: '08:50:00'

-- C) DATETIME:
-- Format: 'YYYY-MM-DD HH:MM:SS'
-- For Ex: '2025-05-22 08:50:00'

-- D) TIMESTAMP:
-- Format is similar to "DATETIME" datatype

-- Range: 1970-01-01 00:00:01 UTC 2038-01-19 03:14:07 UTC

-- E) YEAR:
-- Format: 'YYYY' or 'YY'
-- 2 Digit Year: 70(1970)
-- 4 Digit Year: 2025

-- Year Values in the range of 00-69 then it will become: 2000-2069

-- Year Values in the range of 70-99 then it will become: 1970-1999

----------------------------------------------
-- Now we will create one table for the date and time datatype

-- First select database

-- USE ds_batch_22;
-- For Ex:

CREATE TABLE BS_Batch_22(Student_Id INT, myDate DATE,

myTime TIME,
myDateTime DATETIME, myTimeStamp TIMESTAMP, myYear
YEAR);

-- SELECT * FROM BS_Batch_22; -- To check whole table data

-- DESCRIBE Table_Name; -- To check schema or structure of the

table

-- For Ex: DESCRIBE BS_Batch_22;

-- Now we will insert data in the table

-- A) DATE: Student_Id and myDate(I have to add data in to this two

column)

-- Way for the Insert Data:

INSERT INTO Table_Name VALUES('Value1', 'Value2', 'Value3'); --

This command for add values in the all columns

INSERT INTO Table_Name (Column_Name1, Column_Name2)

VALUES ('Value1', 'Value2'); -- This command for add values in the
specific columns

-- For Ex:1: INSERT INTO BS_Batch_22(Student_Id, myDate)

VALUES (1,2025-05-22);
-- We are getting Error Message: Error Code: 1292. Incorrect date
value: '1998' for column 'myDate' at row 1​ 0.000 sec
Becasue we not passed date value in the single coat

-- For Ex:2: INSERT INTO BS_Batch_22(Student_Id, myDate)

VALUES (1,'2025-05-22');

-- SQL: Day-6: Date: 27/05/2025

-- Important Commands:

-- 1) Create New Database:

-- Command: CREATE DATABASE Database_name;

-- 2) If we want to use database:

-- Command: USE Database_Name;
-- For Ex: USE ds_batch_22;

-- 3) Show the databases present under the MYSQL:

-- Command: SHOW DATABASES;

-- 4) Show tables present in the specific database:

-- Command: SHOW TABLES;

-- 5) If we want to run/execute query or command written by us:

-- Way: 1
-- A) We can select whole command and click on the run/fire button

-- Command: SHOW TABLES;

-- Way:2
-- B) Just go to the command and Press "Control + Enter"
SHOW TABLES;

-- 6) If we want select or fetch whole table data or all records from the
table,
-- Command: SELECT * FROM Table_Name;
-- For Ex: SELECT * FROM bs_batch_22;

-- 7) If we want specific column data in the output:

-- Command: SELECT Column_Name1, Column_Name2 FROM
Table_Name;
-- For Ex: SELECT Student_Id, myDate FROM bs_batch_22;

-- 8) If we want to create New Table,

-- Command: CREATE TABLE Table_Name(Column_Name1
Datatype(Limit), Column_Name2 Datatype(Limit));

-- For Ex: CREATE TABLE BS_Class(First_Name VARCHAR(20),

Mobile_Number BIGINT, CITY VARCHAR(20));

-- 9) If we want to insert or add data in the newly created table

-- Command:

-- 1st Way: Without mentioning column name:

-- Command: INSERT INTO Table_Name VALUES('Value1', 'Value2',

'Value3');

-- For Ex: INSERT INTO BS_Class VALUES('Bharat', 989989898,

'Barshi'); -- Data added Successfully
-- For Ex: INSERT INTO BS_Class VALUES('Bharat', 989989898);

-- We are getting an error message: Error Code: 1136. Column count

doesn't match value count at row 1​ 0.000 sec

-- 2nd Way: With mentioning column name:

-- Command: INSERT INTO Table_Name (Column_Name1,

Column_Name2, Column_Name3) VALUES('Value1', 'Value2',
'Value3');

-- For Ex: INSERT INTO BS_Class(First_Name, Mobile_Number)

VALUES('Sachin', 9896589879); -- Data added successfully

-- 10) If we want to check/see the table structure or schema(Column

names and datatype)

-- Command: DESCRIBE Table_Name;

-- For Ex: DESCRIBE BS_Class;

----------------------------------------------
-- Now we will see the Practical examples for the DATE and TIME
Datatype:

-- Now we will insert data in the table

-- A) DATE: Student_Id and myDate(I have to add data in to this two

column)

-- Way for the Insert Data:

INSERT INTO Table_Name VALUES('Value1', 'Value2', 'Value3'); --
This command for add values in the all columns
INSERT INTO Table_Name (Column_Name1, Column_Name2)
VALUES ('Value1', 'Value2'); -- This command for add values in the
specific columns

-- For Ex:1: INSERT INTO BS_Batch_22(Student_Id, myDate)

VALUES (1,2025-05-22);

-- We are getting Error Message: Error Code: 1292. Incorrect date

value: '1998' for column 'myDate' at row 1​ 0.000 sec

-- For Ex:2: INSERT INTO BS_Batch_22(Student_Id, myDate)

VALUES (1,'2025-05-22');

-- For Ex:3: INSERT INTO BS_BATCH_22(Student_Id, myDate)

VALUES (1, '2025>05>27'); -- This delimiter also support

-- For Ex:4: INSERT INTO BS_BATCH_22(Student_Id, myDate)

VALUES (1, '2025/05/27'); -- This delimiter also support

----------------------------------------------
-- B) TIME: Student_Id and myTime: HH:MM:SS

-- 12 HRS Format:
INSERT INTO BS_BATCH_22 (Student_Id, myTime) VALUES (2,
'08:12:00');

-- 24 HRS Format: I want to enter evening "8" values

-- INSERT INTO BS_BATCH_22 (Student_Id, myTime) VALUES (2,

'20:00:00');
-- What will happen if we have not provided "Second Value" ?

-- INSERT INTO BS_BATCH_22 (Student_Id, myTime) VALUES (2,

'08:16'); -- So here by default "0" value will be added for the "Second"

-- What would happen if we passed just a single value in the quote?

-- INSERT INTO BS_BATCH_22 (Student_Id, myTime) VALUES (2,

'08'); -- So here this value will be considered as a "Second" Value

-- INSERT INTO BS_BATCH_22 (Student_Id, myTime) VALUES(2,

'00:00:00'); -- 12AM

--------------------------------------------------
-- C) DATETIME: Student_id and myDateTime
-- Format: (YYYY-MM-DD HH:MM:SS)

INSERT INTO BS_BATCH_22 (Student_Id, myDateTime) VALUES (3,

'2025-05-27 08:22:00');

INSERT INTO BS_BATCH_22 (Student_Id, myDateTime) VALUES (3,

'2025>05>27 08:22:00'); -- This delimiter also support

INSERT INTO BS_BATCH_22 (Student_Id, myDateTime) VALUES (3,

'2025/05/27 08:22:00'); -- This delimiter also support

-- Suppose we have not passed time value,

INSERT INTO BS_BATCH_22 (Student_Id, myDateTime) VALUES (3,

'2025/05/27'); -- by default "0" value will be added for the time

-- Suppose we have not passed date value,

INSERT INTO BS_BATCH_22 (Student_Id, myDateTime) VALUES (3,
'08:22:00');

-- We are getting error message: Incorrect datetime value: '08:22:00'

for column 'myDateTime' at row 1​ 0.000 sec

-- SQL: Day-7: Date: 28/05/2025

-- D) TIMESTAMP: Student_Id and myTimeStamp

-- Format: YYYY-MM-DD HH:MM:SS

-- Range: 1970-01-01 00:00:01 UTC 2038-01-19 03:14:07 UTC

-- For Ex:1
INSERT INTO BS_BATCH_22 (Student_Id, myTimeStamp) VALUES
(4, '2025-05-28 07:47:00');

-- For Ex:2
INSERT INTO BS_BATCH_22 (Student_Id, myTimeStamp) VALUES
(4, '2038-05-28 07:47:00'); -- We are trying to insert date value more
than range defined

-- For Ex:3
INSERT INTO BS_BATCH_22 (Student_Id, myTimeStamp) VALUES
(4, '1969-05-28 07:47:00'); -- We are trying to insert date value less
than range defined

-- We are getting Error message for 2nd and 3rd example: ​ Error
Code: 1292. Incorrect datetime value: '1969-05-28 07:47:00' for
column 'myTimeStamp' at row 1​0.000 sec
-- Note: By using TIMESTAMP datatype we can insert automatic date
and time value in the column

-- We will create one new table,

CREATE TABLE Flipkart(Item_Id INT, Item_Name VARCHAR(20),

Quantity INT,
Last_Updated TIMESTAMP DEFAULT current_timestamp ON
UPDATE current_timestamp);

-- Now we will insert data in the table,

INSERT INTO Flipkart(Item_Id, Item_Name, Quantity)

VALUES(1,'Mobile',5);

-- Note: In the above example date and time value will be

automatically added because we have not passed date and time value
manually

INSERT INTO Flipkart(Item_Id, Item_Name, Quantity, Last_Updated)

VALUES(1,'Mobile',5, '2025-05-28 07:57:37');

-- Note: In the above example we have added last updated column

value manually

-- Suppose I have to update the Mobile Quantity from 5 To 4

-- Command:
UPDATE Table_Name
SET Column_Name='Value'
WHERE Column_Name='Value';
-- For Ex:
UPDATE Flipkart
SET Quantity=4
WHERE Item_Name='Mobile';

-- Note: By default in the MYSQL "SAFE UPDATE MODE" is "ON"

means we cannot directly update or delete data from the table and it
because of the safety of the data

-- How to check SAFE UPDATE MODE is ON or OFF?

-- Command: SELECT @@SESSION.sql_safe_updates;

-- How to change SAFE Update Mode?

-- Command: SET SQL_SAFE_UPDATES=0; -- Disable or OFF the

safe update mode means we can update or delete data from the table

-- Command: SET SQL_SAFE_UPDATES=1; -- Enable or ON the

safe update mode means we cannot update or delete data from the
table

-- We enable or ON the safe update mode and now we will try to

update data

-- For Ex:
UPDATE Flipkart
SET Quantity=5
WHERE Item_Name='Mobile';

-- We are getting Error Message: Error Code: 1175. You are using
safe update mode and you tried to update a table without a WHERE
that uses a KEY column. To disable safe mode, toggle the option in
Preferences -> SQL Editor and reconnect.​ 0.000 sec

----------------------------------------------------
-- E) YEAR: Student_Id and MyYear

-- Range: 1901 To 2155 and 0000

INSERT INTO BS_BATCH_22 (Student_Id, myYear) VALUES

(5,'2025'); -- 2025

INSERT INTO BS_BATCH_22 (Student_Id, myYear) VALUES (5,'25');

-- 2025

INSERT INTO BS_BATCH_22 (Student_Id, myYear) VALUES (5,'01');

-- 2001

INSERT INTO BS_BATCH_22 (Student_Id, myYear) VALUES (5,'70');

-- 1970

-- When we passed 2 digit Year values then there will be confusion

because of the unknown century
-- Because of that MYSQL set some rules
-- Year Values in the range of 00-69 then it will become: 2000-2069
-- Year Values in the range of 70-99 then it will become: 1970-1999

INSERT INTO BS_BATCH_22 (Student_Id, myYear) VALUES

(5,'2156');

-- Getting error message: Error Code: 1264. Out of range value for
column 'myYear' at row 1​ 0.016 sec
INSERT INTO BS_BATCH_22 (Student_Id, myYear) VALUES
(5,'0000');

-- SQL: Day-8: Date: 29/05/2025

-- DDL: Data Definition Language

-- By using DDL Commands we can define or modify the structure or

schema of the table
-- Major Use: CREATE DATABASE, Create Table, Add Column,
Delete Column, DROP Table, RENAME column, Table Name, Modify
Datatype

-- Important Commands:

-- 1) CREATE:

-- A) CREATE New Database:

-- Command: CREATE DATABASE Database_name;
-- For Ex: CREATE DATABASE DS_Practice;

USE DS_Practice; -- Select database

-- B) Create New Table:

-- Command: CREATE TABLE Table_Name(Column_Name1
Datatype(Limit), Column_Name2 Datatype(Limit));

-- For Ex: CREATE TABLE IPL_25(Player_Name VARCHAR(20),

Team_Name VARCHAR(20), Auction_Price BIGINT);

-- Now we will insert data in the table,

INSERT INTO IPL_25 VALUES
('Rohit Sharma', 'MI', 180000000),
('Virat Kohli', 'RCB', 210000000),
('MS Dhoni', 'CSK', 400000000),
('Shreyas Ayyer','PK', 2500000000);

SELECT * FROM IPL_25;

-- 2) ADD: To add New Column in the existing table,

-- Command: ALTER TABLE Table_Name ADD Column_Name

Datatype(Limit);

-- For Ex: ALTER TABLE IPL_25 ADD Team_Coach VARCHAR(20);

-- Now we will add data into newly added column,

UPDATE IPL_25
SET Team_Coach='Mahila Jaywardne'
WHERE Team_Name='MI';

-- 3) MODIFY:
-- If we want to change the datatype of the column we will use
"MODIFY" command

-- Command: ALTER TABLE Table_Name MODIFY Column_Name

Datatype(Limit);

-- For Ex: ALTER TABLE IPL_25 MODIFY Auction_Price

DECIMAL(15,2);
-- Command: DESCRIBE IPL_25; -- To check Schema or check
datatype

-- For Ex: ALTER TABLE IPL_25 MODIFY Team_Coach BIGINT; --

Here we are trying to change datatype from "VARCHAR" to "BIGINT"
-- So Getting Error: Error Code: 1366. Incorrect integer value: 'Mahila
Jaywardne' for column 'Team_Coach' at row 1​ 0.031 sec

-- 4) RENAME:
-- If we want to change or rename the name of the column and table
then that time we will use "RENAME" Command

-- A) RENAME Column_Name:

-- Command: ALTER TABLE Table_Name RENAME COLUMN

Old_Column_Name TO New_Column_Name;

-- For Ex: ALTER TABLE IPL_25 RENAME COLUMN Team_Name TO

IPL_Team_Name; -- Column Name Changed

-- B) RENAME Table_Name:

-- Command: RENAME TABLE Old_Table_Name TO

New_Table_Name;

-- For Ex: RENAME TABLE IPL_25 TO Tata_IPL_25;

-- Now if we tried SELECT Command with Old Table Name

SELECT * FROM IPL_25;
-- Getting Error Code: 1146. Table 'ds_practice.ipl_25' doesn't exist​
0.000 sec
-- Now if we tried SELECT Command with New Table Name
SELECT * FROM Tata_IPL_25; -- We are getting proper data

-- Format for the Answer:

-- Question: What is meant by SQL and what are the types of it?

-- Answer:
-- Definition/Concept:
-- Types/Use
-- Example

-- SQL: Day-9: Date: 30/05/2025

-- 5) DROP:

-- If we want to delete/drop specific columns or table then we will use

"DROP" Command

-- Note: We cannot retrieve or recover or rollback data deleted by

"DROP" command (Shift + Delete)

-- A) For delete/drop specific column:

-- Command: ALTER TABLE Table_Name DROP COLUMN

Column_Name;

-- For Ex: ALTER TABLE Tata_IPL_25 DROP COLUMN Team_Coach;

-- Column deleted successfully
-- B) For Delete/Drop Multiple Columns:

-- Command:
ALTER TABLE Table_Name
DROP COLUMN Column_Name1,
DROP COLUMN Column_Name2;

-- For Ex:
ALTER TABLE Tata_IPL_25
DROP COLUMN IPL_Team_Name,
DROP COLUMN Auction_Price;

-----------------------------
-- 7) TRUNCATE:

-- If we want to delete "Whole records" from the table in "One Go" then
we will use "TRUNCATE" Command
-- We cannot retrieve or recover or rollback data deleted by
"TRUNCATE" command
-- When we use "TRUNCATE" command for the delete whole data
then all data will be deleted but table "schema or structure" remains in
the database history

-- Command: TRUNCATE TABLE Table_Name;

-- For Ex: TRUNCATE TABLE Tata_IPL_25; -- All rows get deleted

-- Now if we tried below command,

SELECT * FROM Tata_IPL_25; -- Here we will get structure of the
table in the output

-- Now if we try to insert data in the table,

INSERT INTO Tata_IPL_25 VALUES ('Sachin'); -- data added
successfully

-----------------------------------------
-- 8) DROP Table:
-- If we want to delete or drop whole table with structure or schema of
the table then we will use "DROP Table" Command

-- Command: DROP TABLE Table_Name;

-- For Ex: DROP TABLE Tata_IPL_25; -- Whole Table Dropped

-- Now if we tried below command,

SELECT * FROM Tata_IPL_25;
-- We are getting Error Message: Error Code: 1146. Table
'ds_practice.tata_ipl_25' doesn't exist​0.000 sec

-- 9) DROP DATABASE:
-- If we want to delete or drop database then we will use "DROP
Database" Command

-- Command: DROP DATABASE Database_Name;

-- For Ex: DROP DATABASE ds_practice; -- Database get deleted

-- Interview Question:
-- What is the difference between "DELETE", "DROP" and
"TRUNCATE" ?

-- DELETE:
-- 1) This is DML(Data Manipulation Language) Command
-- 2) If we want to delete specific records or rows and also whole table
data then we will use "DELETE" Command
-- 3) We can recover or retrieve back or rollback data deleted by
"DELETE" Command with the help of TCL Command "ROLLBACK"

-- DROP:
-- 1) This is DDL(Data Definition Language) Command
-- 2) If we want to delete or drop "Specific Columns', "Whole Table",
and "Whole Database" then we will use "DROP" Command
-- 3) We cannot retrieve or recover data deleted by the "DROP"
Command

-- Note: Here Table Data and Table Schema or structure deleted

-- TRUNCATE:
-- 1) This is DDL(Data Definition Language) Command
-- 2) If we want to delete "Whole Table Records" in "one go" then we
will use "TRUNCATE" Command
-- 3) We cannot retrieve or recover data deleted by "TRUNCATE"
command

-- Note: Here table data get deleted but table structure or schema
remains in the history of the database

---------------------------------------------------
-- DML: Data Manipulation Language Commands
-- If we want to perform any actions on the data present in the table
then we will use "DML" Commands

-- SQL: Day-11: Date: 02/06/2025

-- DML: Data Manipulation Language Commands

-- If we want to perform any actions on the data present in the table
then we will use "DML" Commands

-- First we will create one table,

USE DS_Batch_22; -- Select database

CREATE TABLE IPL_25 (Player_Name VARCHAR(20), Team_Name

VARCHAR(20), Coach_Name VARCHAR(20), Auction_Price BIGINT);

-- Important DML Commands:

-- 1) INSERT INTO:
-- If we want to insert data into table then we will use "INSERT INTO"
command

-- Command:
INSERT INTO IPL_25 VALUES
('Rohit Sharma', 'MI', 'Mahela Jaywardne', 160000000),
('Suryakumar Yadav', 'MI', 'Mahela Jaywardne', 180000000),
('Virat Kohli', 'RCB', 'Dinesh Kartik', 150000000),
('Shryes Ayyer', 'PK', 'Ricky Ponting', 240000000);

-- 2) DISTINCT:
-- If we want only "Unique Records" in the output then we will use
"DISTINCT" Command
-- DISTINCT Command will remove the duplicate records from the
response or output

-- Command: Single Column

SELECT DISTINCT Column_Name FROM Table_Name;
-- For Ex:
SELECT DISTINCT Team_Name FROM IPL_25; -- Here we are
getting just unique records in the output

-- Command: Multiple Column

SELECT DISTINCT Column_Name1, Column_Name2 FROM
Table_Name;

-- For Ex:
SELECT DISTINCT Coach_Name, Team_Name FROM IPL_25;

---------------------------------
-- 3) WHERE Clause or Command:

-- The WHERE Clause or Command used in the SQL to "filter out data
or records" on the basis of "condition applied"
-- By using "WHERE Clause" we can "retrieve the specific data", "we
can update specific data", "we can delete specific data" as per the
condition applied

-- Command: A) WHERE Clause with SELECT Command

SELECT Column_Name1, Column_Name2

FROM Table_Name
WHERE Column_Name='Value';

-- For Ex: Suppose I want all the MI Players Name in the output
SELECT Player_Name, Team_Name
FROM IPL_25
WHERE Team_Name='MI'; -- We are getting proper result
SELECT * FROM IPL_25 WHERE Team_Name='MI'; -- We are
getting whole rows in the output as per the condition applied

-- For Ex: Suppose I want Shryes Ayyer Team name in the output

SELECT Team_Name FROM IPL_25 WHERE Player_Name='Shryes

Ayyer'; -- Getting "PK" in the output

SELECT * FROM IPL_25 WHERE Player_Name='Shryes Ayyer'; --

Getting whole row of the Shyres Ayyer in the output

-------------------------------------
-- B) WHERE Clause with UPDATE Command
-- UPDATE Also DML Command
-- If we want to "Modify" or "Update" or "Change" in the data present in
the table then we will use "UPDATE" command

-- Command
UPDATE Table_Name
SET Column_Name='Value'
WHERE Column_Name='Value';

-- For Ex: Suppose I have to change Auction Price of the Virat Kohli
From 15Cr to 21Cr

UPDATE IPL_25
SET Auction_Price=210000000
WHERE Player_Name='Virat Kohli'; -- Data updated successfully

-- How to check safe update mode ON or OFF?

-- Command: SELECT @@GLOBAL.sql_safe_updates,
@@SESSION.sql_safe_updates; -- Global and specific Session safe
update mode

-- How to enable or disable safe update mode?

-- Command: SET SQL_SAFE_UPDATES=0; -- Disable or OFF the

SAFE UPDATE Mode means we can update or delete data from the
table

-- Command: SET SQL_SAFE_UPDATES=1; -- Enable or ON the

SAFE UPDATE Mode means we cannot update or delete data from
the table

-- For Ex: Suppose there are two rows having same data(duplicate
data) and I have to just update data in the one row

UPDATE IPL_25
SET Auction_Price=170000000
WHERE Player_Name='Rohit Sharma'; -- Data updated in the both
the rows

-- So if we want to update data in the one row only then we need to

use "LIMIT" command

-- Command:
UPDATE Table_Name
SET Column_Name='Value'
WHERE Column_Name='Value'
LIMIT Row_Number;

-- For Ex:
UPDATE IPL_25
SET Auction_Price=16000000
WHERE Player_Name='Rohit Sharma'
LIMIT 1; -- Here only one row will be updated

-- SQL: Day-12: Date: 03/06/2025

-- C) WHERE Clause with DELETE :

-- DELETE also DML Command

-- If we want to delete specific rows from the table then we will use
"DELETE" Command
-- We can also delete whole table records in "one go"
-- We can retrieve or recover data deleted by "DELETE" command
with the help of "ROLLBACK" TCL Command

-- Command: DELETE FROM Table_Name WHERE

Column_Name='Value';

-- For Ex: DELETE FROM IPL_25 WHERE

Player_Name='Suryakumar Yadav'; -- Record deleted successfully

-- Scenario: Suppose there are two rows with same data and I have to
just delete one row

-- So here we need to use "LIMIT" command if we want to delete

single record

-- Command:
DELETE FROM Table_Name WHERE Column_Name='Value' LIMIT
RowNumber;
-- For Ex:
DELETE FROM IPL_25 WHERE Player_Name='Rohit Sharma' LIMIT
1;

-- Here only one record was deleted. Here internal sequence will be
followed for the delete records when duplicate records present

---------------------------------------------
-- D) WHERE Clause with "AND", "OR" Logical Operator:

-- Both are the Logical Operator

-- A) AND:
-- When both the conditions are separated by "AND" logical operator
"True" then only we will get data in the output

-- Command:
SELECT Column_Name1, Column_Name2 FROM Table_Name
WHERE
Column_Name1='Value'
AND
Column_Name2='Value';

-- For Ex:1
SELECT Team_Name FROM IPL_25
WHERE
Player_Name='Virat Kohli'
AND
Coach_Name='Ricky Ponting'; -- Getting blank response because
condition not satisfied
-- For Ex:2
SELECT Team_Name FROM IPL_25
WHERE
Player_Name='Sachin Tendulkar'
AND
Coach_Name='Zahir Khan'; -- Getting blank response because
condition not satisfied

-- For Ex:3
SELECT Team_Name FROM IPL_25
WHERE
Player_Name='Virat Kohli'
AND
Coach_Name='Dinesh Kartik'; -- Both the condition are true so we are
getting data in the output: RCB

---------------------------------
-- B) OR:
-- When one of the conditions separated by "OR" logical operator are
true then we will get data in the output

-- Command:
SELECT Column_Name1, Column_Name2 FROM Table_Name
WHERE
Column_Name1='Value'
OR
Column_Name2='Value';

-- For Ex:1
SELECT Team_Name FROM IPL_25
WHERE
Player_Name='Virat Kohli'
OR
Coach_Name='Ricky Ponting'; -- We are getting "RCB" and "PK" in
the output because both the conditions are true

-- For Ex:2
SELECT Team_Name FROM IPL_25
WHERE
Player_Name='Sachin Tendulkar'
OR
Coach_Name='Zahir Khan'; -- Getting blank response because both
the conditions are not satisfied

-- For Ex:3
SELECT Team_Name FROM IPL_25
WHERE
Player_Name='Virat Kohli'
OR
Coach_Name='Dinesh Kartik'; -- Both the condition are true so we are
getting data in the output: RCB

----------------------------------------------
-- E) WHERE Clause with Comparison Operator: (=,!=,>,>=,<,<=)

-- Command:
SELECT Column_Name1, Column_Name2 FROM Table_Name
WHERE Condition;

-- For Ex:1:!=(Not Equal To):

-- Suppose I want all the players in the output whose Team name
other than "MI"
SELECT * FROM IPL_25 WHERE Team_name!='MI'; -- We are
getting all the players details in the output except MI Team

-- For Ex:2:>(Greater Than):

-- Suppose I want all the players' details in the output whose auction
price is greater than 17 Cr.

SELECT * FROM IPL_25 WHERE Auction_Price>170000000; -- We

are getting two records in the output

-- For Ex:3:>=(Greater Than Equal To):

-- Suppose I want all the players' details in the output whose auction
price is greater than and equal to 17 Cr.

SELECT * FROM IPL_25 WHERE Auction_Price>=170000000; -- We

are getting three records in the output

-- For Ex:4:<(Less Than):

-- Suppose I want all the players' details in the output whose auction
price is less than 21 Cr.

SELECT * FROM IPL_25 WHERE Auction_Price<210000000; --

Getting one record in the output

-- For Ex:5:<=(Less Than Equal To):

-- Suppose I want all the players' details in the output whose auction
price is less than and equal to 21 Cr.

SELECT * FROM IPL_25 WHERE Auction_Price<=210000000; --

Getting two record in the output

----------------------------------------------------------
-- LIMIT Statement or Command:

-- LIMIT Command used in the MYSQL to restrict the number of rows

returned by the "SELECT", "UPDATE", "DELETE" command

-- By using LIMIT command we can control "how many records return

or retrieve " in the output

-- How many records we have update, how many records we have

delete and how many records we want in the output

-- SQL: Day-13: Date: 04/06/2025

-- LIMIT Statement or Command:

-- LIMIT Command used in the MYSQL to restrict the number of rows

returned by the "SELECT", "UPDATE", "DELETE" command

-- By using LIMIT command we can control "how many records return

or retrieve " in the output

-- How many records we have update, how many records we have

deleted and how many records we want in the output

-- Basic Syntax or Command:

SELECT Column_Name FROM Table_Name LIMIT number_of_rows;

-- 1) LIMIT with SELECT Command:

-- Suppose I want only first 2 rows in the output:

SELECT * FROM IPL_25 LIMIT 2; -- We are getting 2 rows in the
output

SELECT Player_Name, Team_Name FROM IPL_25 LIMIT 2; -- For

Specific Column

-- 2) LIMIT with ORDER BY Clause or Command:

-- ORDER BY: If we want to sort out data in the "Ascending" and

"Descending" order then we will use "ORDER BY" Command

-- Command:
SELECT Column_Name FROM Table_Name ORDER BY
Column_Name ASC/DESC;

-- For Ex:A) ORDER BY with Ascending Order(Without ASC Keyword)

SELECT Player_Name FROM IPL_25 ORDER BY Player_Name;

-- Note: For Ascending order we do not need to pass "ASC" keyword

with column name. By default data will be sort out in the ascending
order

-- For Ex:B) ORDER BY with Ascending Order(With ASC Keyword)

SELECT Player_Name FROM IPL_25 ORDER BY Player_Name

ASC;

-- For Ex:C) ORDER BY with Descending Order (DESC Keyword)

SELECT Player_Name FROM IPL_25 ORDER BY Player_Name

DESC;
SELECT * FROM IPL_25 ORDER BY Player_Name DESC;

SELECT * FROM IPL_25 ORDER BY Auction_Price DESC;

-- We can apply the ORDER BY to multiple column as well

SELECT * FROM Practice_1;

SELECT Student_Id, Student_Name FROM Practice_1 ORDER BY

Student_Id ASC, Student_Name DESC;

INSERT INTO Practice_1 (student_Id, Student_Name) VALUES (6,

'Omkar'), (6, 'Paresh');

-- Now we will use ORDER BY Command with LIMIT Statement

-- For Ex: Suppose I want top 2 marked students details in the output

SELECT Student_Id, Student_Name, Student_Marks FROM

Practice_1 ORDER BY Student_marks DESC LIMIT 3; -- Here we are
getting top 2 records in the output

-- Task: Suppose I want bottom 2 records in the output

-- Do practice for the Bottom records

========================================
-- 3) LIMIT with OFFSET Command:
-- If we want specific rows after the specific rows then we will use
"LIMIT with OFFSET" command

-- Command:
SELECT * FROM Table_Name LIMIT OFFSET_Value,
LIMIT_Number_Of_Rows;

-- For Ex: Suppose I want two rows data from the 2nd Row

SELECT * FROM Practice_1 LIMIT 1,2;

-- Explanation:
-- 1: This is offset value: Means start from 2nd row
-- 2: This is LIMIT Value: Means two records will be returned in the
output from the 2nd Row

SELECT * FROM Practice_1 LIMIT 2,2; -- Here we will get two

records from the 3rd Row

SELECT * FROM Practice_1 LIMIT 7,2; -- Here we are getting blank

response because no any rows present post the 7th Row

-- 4) LIMIT with UPDATE -- Do practice

-- 5) LIMIT with DELETE -- Do practice

------------------------------------------
-- IN Command:
-- This is also DML Command
-- By using IN Command we can apply the condition on the multiple
column and get the number of records in the output
-- We will get all the matching records in the output as per the
condition applied

-- Command:
SELECT Column_Name FROM Table_Name WHERE Column_Name
IN('Value1', 'Value2', 'Value3');
-- For Ex: Suppose I want all the records of the students whose city
"Mumbai", "Pune", "Solapur"

SELECT * FROM Practice_1 WHERE Student_City IN('Mumbai',

'Pune', 'Solapur');

-- SQL: Day-14: Date: 05/06/2025

-- UPDATE Command with OFFSET and LIMIT:

-- Command:
UPDATE Table_Name
SET Column_Name='Value'
WHERE COLUMN_Name IN
(SELECT Column_Name FROM(SELECT Column_Name FROM
Table_Name ORDER BY Column_Name LIMIT OFFSET_Value,
LIMIT_Value) AS Subquery);

-- Scenario: Suppose I have to increase Auction_Price of the 2nd and

3rd row player_Name by 100000

UPDATE IPL_25
SET Auction_Price=Auction_Price + 100000
WHERE Player_Name IN
(SELECT Player_Name FROM(SELECT Player_Name FROM IPL_25
LIMIT 1,2) AS Subquery);

-- Explanation:
-- Subquery/Inner Query: (SELECT Player_Name FROM IPL_25
LIMIT 1,2) : Output: We will get 2nd and 3rd rows in the output
-- SELECT Player_Name FROM : Here this command will fetch
records from the inner query: Rohit, Shryes
-- UPDATE IPL_25 SET Auction_Price=Auction_Price + 100000: Then
this command will update auction price for the "Rohit" and "Shryes"

--------------------------------------------------
-- NOT IN:

-- Suppose I want all the player name except Rohit sharma in the
output

-- Command: SELECT * FROM Table_Name WHERE Column_Name

NOT IN('Value');

-- For Ex: SELECT * FROM IPL_25 WHERE Player_Name NOT

IN('Rohit Sharma'); -- Here we are getting remaining all the records
except Rohit sharma

-----------------------------------------------
-- BETWEEN Command:
-- This is DML Command
-- The BETWEEN Command is used to filter out data within specific
range

-- Command:
SELECT Column_Name FROM Table_Name WHERE Column_Name
BETWEEN 'Value1' AND 'Value2';

-- For Ex: Suppose I want all the students name in the output whose
marks between range of 60-90
SELECT Student_Name, Student_Marks FROM Practice_2 WHERE
Student_Marks BETWEEN 60 AND 100;

------------------------------------------------
-- LIKE Command:

-- The LIKE Command or Operator used in the MYSQL with "WHERE"

Clause to search data as per the specific patterns
-- We will apply patterns with the help two wildcards
-- %: It represents as a "Zero", "One", "Multiple" characters
-- _: It will always represent as a "Single Character"

-- Basic command:
SELECT Column_Name FROM Table_Name WHERE Column_Name
LIKE 'Pattern';

-- 1) LIKE Command with (%):

-- A) Start with Specific Alphabets(A%):

-- Suppose I want all the employees details in the output whose

first_name start with "A" Alphabet

-- For Ex: SELECT * FROM bug_spotter_employees WHERE

Emp_Name LIKE 'A%';

-- B) Ends with Specific Alphabets:(%A)

-- Suppose I want all the employees details in the output whose

first_name ends with "A" Alphabet
-- For Ex: SELECT * FROM bug_spotter_employees WHERE
Emp_Name LIKE '%a';

-----------------------------------
-- 2) LIKE with Multiple Columns:

-- Command:
SELECT * FROM Table_Name
WHERE Column_Name1 LIKE 'Pattern'
AND
Column_Name2 LIKE 'Pattern';

-- For Ex: With AND

SELECT * FROM bug_spotter_employees
WHERE Emp_Name LIKE 'B%'
AND
Department LIKE 'H%';

-- For Ex: With OR

SELECT * FROM bug_spotter_employees

WHERE Emp_Name LIKE 'B%'
OR
Department LIKE 'H%';

-- SQL: Day-15: Date: 06/06/2025

-- 3) LIKE with (%) in the Middle:

-- Suppose I want all the employee records in the output whose

emp_name contains "a" character
-- For Ex:
SELECT * FROM bug_spotter_employees WHERE emp_name LIKE
'%a%'; -- WE are getting all the "a" character containing result in the
response

-- For Ex:
SELECT * FROM bug_spotter_employees WHERE emp_name LIKE
'%ai%'; -- We are getting blank response because don't have such
result in the table

-- 4) LIKE with Underscore Sign(_):

-- For Ex: SELECT * FROM bug_spotter_employees WHERE

emp_Name LIKE 'B_'; -- We are getting blank response

-- For Ex: SELECT * FROM bug_spotter_employees WHERE

emp_name LIKE 'aj__'; -- We are getting "Ajit" record in the output

-- For Ex: SELECT * FROM bug_spotter_employees WHERE

Emp_name LIKE '_j_t'; -- We are getting "Ajit" record in the output

-- 5) LIKE with combination of "%" and "_"

-- Suppose I want all the employee name in the output whose name
start with "A" and atleast have "3 character"

SELECT * FROM bug_spotter_employees WHERE emp_name LIKE

'A__%';

------------------------------------------
-- SQL CONSTRAINTS:
-- SQL Constraints used in the MySQL to apply specific rules or
restrictions on the columns

-- It will help to increase data 'accuracy' and 'consistency'

-- 1) NOT NULL:
-- 2) UNIQUE:
-- 3) PRIMARY KEY
-- 4) FOREIGN KEY
-- 5) CHECK
-- 6) DEFAULT

---------------------------
-- 1) NOT NULL:
-- When we applied "NOT NULL" Constraints on the column then we
need to add "mandatory value" in the column
-- Otherwise it will generate error message or we cannot proceed with
action

-- Command: CREATE TABLE Table_Name (Column_Name1

Datatype(LIMIT) CONSTRAINT, Column_Name2 Datatype(LIMIT)
CONSTRAINT));

-- For Ex:
CREATE TABLE BS_1(Student_Id INT NOT NULL, Student_Name
VARCHAR(20) NOT NULL, Student_Mobile_Number BIGINT);

-- Now we will insert data in the table,

For Ex:1

INSERT INTO BS_1 VALUES (NULL, 'Bharat', 98989898989);

-- We are getting Error Code: 1048. Column 'Student_Id' cannot be
null​ 0.000 sec because we are trying to insert "NULL" value in the
"NOT NULL" constraints applied column

For Ex:2

INSERT INTO BS_1 VALUES ('', 'Bharat', 98989898989);

-- We are getting Error Code: 1366. Incorrect integer value: '' for
column 'Student_Id' at row 1​ 0.016 sec

For Ex:3

INSERT INTO BS_1 VALUES ('Bharat', 98989898989);

-- We are getting Error Code: 1136. Column count doesn't match value
count at row 1​ 0.000 sec

For Ex:4

INSERT INTO BS_1 (Student_name, Student_Mobile_Number)

VALUES ('Bharat', 98989898989);

-- We are getting Error Code: 1364. Field 'Student_Id' doesn't have a

default value​ 0.000 sec

For Ex:5

INSERT INTO BS_1 (Student_name, Student_Mobile_Number)

VALUES (NULL, 'Bharat', 98989898989);
-- We are getting Error Code: 1136. Column count doesn't match value
count at row 1​ 0.000 sec

For Ex:6

INSERT INTO BS_1 (Student_Id, Student_name,

Student_Mobile_Number) VALUES (1, 'Bharat', 98989898989);
-- Data added Successfully

-------------------------------------------
-- 2) UNIQUE:
-- When we apply "UNIQUE" constraint to a column then we cannot
insert "Duplicate Values" in the column

-- Command: CREATE TABLE Table_Name (Column_Name1

Datatype(LIMIT) CONSTRAINT, Column_Name2 Datatype(LIMIT)
CONSTRAINT));

-- For Ex:
CREATE TABLE BS_2 (Student_Id INT UNIQUE, Student_Name
VARCHAR(20) NOT NULL, Mobile_Number BIGINT);

INSERT INTO BS_2 VALUES (1, 'Bharat', 9898989898); -- data added

successfully

INSERT INTO BS_2 VALUES (1, 'Sachin', 9888989898);

-- We are getting Error Code: 1062. Duplicate entry '1' for key
'bs_2.Student_Id'​ 0.000 sec because we are getting trying to insert
duplicate value in the student id column

-- SQL: Day-17: Date: 09/06/2025

-- 3) PRIMARY KEY:

-- PRIMARY KEY constraint it is combination of "UNIQUE" and "NOT

NULL" Constraint
-- Means when we applied "PRIMARY Key" Constraint on the column
then "we cannot keep that column value blank" and "we cannot add
duplicate value in the column"
-- We can apply the PRIMARY KEY constraint only for "one column" in
the table

-- Command: CREATE TABLE Table_Name (Column_Name1

Datatype(LIMIT) CONSTRAINT, Column_Name2 Datatype(LIMIT)
CONSTRAINT));

-- For Ex:
CREATE TABLE BS_3 (Student_Id INT PRIMARY KEY,
Student_Name VARCHAR(20) NOT NULL);

-- Now we will insert data in the table,

-- For Ex:1
INSERT INTO BS_3 VALUES(NULL,'Bharat'); -- here we are trying to
insert null data in the table

-- Getting Error Code: 1048. Column 'Student_Id' cannot be null​

0.047 sec

-- For Ex:2
INSERT INTO BS_3 VALUES(1,'Bharat'); -- Data added Successfully

SELECT * FROM BS_3;

-- For Ex:3
INSERT INTO BS_3 VALUES(1,'Sachin'); -- Here we are trying to
insert duplicate student id

-- Getting Error Code: 1062. Duplicate entry '1' for key

'bs_3.PRIMARY'​ 0.000 sec

-- 4) FOREIGN KEY:
-- Note: We will cover this topic in the SQL JOIN Topic

-- 5) CHECK:

-- When we want to add as per the condition applied then we will use
"CHECK" Constraint

-- Suppose we have to create one table for the students who have
greater than equal to 35 marks

-- Command:
CREATE TABLE BS_4(Student_Id INT PRIMARY KEY,
Student_Name VARCHAR(20) NOT NULL, Student_Marks INT,
CHECK(Student_Marks>=35));

-- Now we will insert data in the table,

-- For Ex:1
INSERT INTO BS_4 VALUES(1, 'Sachin', 34); -- We are trying to
insert marks less than condition

-- We are getting Error Code: 3819. Check constraint 'bs_4_chk_1' is

violated.​ 0.031 sec
-- For Ex:2
INSERT INTO BS_4 VALUES(1, 'Sachin', 35); -- Data Added
Successfully

-- For Ex:3
INSERT INTO BS_4 VALUES(2, 'Sachin', 36); -- Data Added
Successfully

-- Practical Task:
-- Suppose there are one Gym Opening Form and in that AGE field
present which only accept AGE Range between "18-60"

---------------------------------------------
-- 6) DEFAULT:

-- When we use "DEFAULT" constraint then it will provide the "default

value" for the column when "none" specified

-- We will create one table,

CREATE TABLE BS_5 (Student_Id INT PRIMARY KEY,

Student_Name VARCHAR(20) NOT NULL, COUNTRY CHAR(5)
DEFAULT 'INDIA');

-- Now we will insert data in the table,

-- For Ex:

INSERT INTO BS_5 VALUES (1, 'Sachin');

-- We are getting Error Code: 1136. Column count doesn't match value
count at row 1​ 0.015 sec

INSERT INTO BS_5 (Student_Id, Student_Name) VALUES (1,

'Sachin'); -- Here we have not passed "Country value" manually so
default valued added

INSERT INTO BS_5 (Student_Id, Student_Name,Country) VALUES

(2, 'Sachin', 'China'); -- We can add country value other than default
value manually

--------------------------------------------
-- 7) NULL:
-- Meaning: It means "NO Value" or "Unknown Value".
-- If any column we declared with "NULL" constraint then we can keep
that column value NULL(But it not mandatory)

-- We will create one table,

CREATE TABLE BS_6(Student_Id INT UNIQUE, Student_Name

VARCHAR(20) NOT NULL, Mobile_Number BIGINT NULL);

-- Now we will insert data in the table,

-- For Ex:1
INSERT INTO BS_6 VALUES (1, 'Sachin', NULL); -- Data Added
Successfully

-- For Ex:2
INSERT INTO BS_6 VALUES (2, 'Sachin', ''); -- Here we are trying to
pass 'Empty String" in the "BIGINT" Datatype applied column
-- We are getting Error Code: 1366. Incorrect integer value: '' for
column 'Mobile_Number' at row 1​ 0.016 sec

-- For Ex:3

INSERT INTO BS_6 (Student_Id, Student_Name) VALUES (3,

'Omkar');

SELECT * FROM BS_6;

-- Suppose we have update NULL Mobile Number value,

UPDATE BS_6
SET Mobile_Number=9898989898
WHERE Student_Id=3;

-- SQL: Day-18: Date: 10/06/2025

-- SQL Constraints:
-- 1) NOT NULL
-- 2) UNIQUE
-- 3) PRIMARY KEY
-- 4) CHECK
-- 5) DEFAULT
-- 6) NULL
-- 7) FOREIGN KEY

-----------------------------
-- AUTO_INCREMENT:
-- This command automatically generates "unique numbers for the
rows" in the table
-- It will always start with "1" and increments by "1" for each records

-- We will create one table,

USE DS_BATCH_22; -- Select Database

CREATE TABLE Student_Data (Sr_No INT AUTO_INCREMENT

PRIMARY KEY, Student_Name VARCHAR(20), Student_Marks INT);

-- Now we will insert data in the table

INSERT INTO Student_Data(Student_Name, Student_Marks)

VALUES ('Sachin', 65),('Neha', 70);

----------------------------------------------
-- Auto_Increment with Specific Number and From Specific Number:

-- Command: Set a custom increment(Step Value)

SET@@auto_increment_increment=2;

CREATE TABLE Student_Data1(Sr_No INT auto_increment

PRIMARY KEY, Student_Name VARCHAR(20) NOT NULL)
AUTO_INCREMENT=1000;

INSERT INTO Student_Data1(Student_Name) VALUES ('Omkar');

--------------------------------------------
-- Aggregate Functions:
-- Aggregate Functions or commands are used to perform
"calculations" on the multiple rows or records

-- We will create one new Table,

CREATE TABLE Student_Records(Student_Id INT, Student_Name

VARCHAR(20),
Student_City VARCHAR(20), Student_Marks INT);

-- We will insert data in the table,

INSERT INTO Student_Records VALUES

(1,'Ankita', 'Pune', 40),
(2,'Neha', 'Thane', 60),
(3,'Ajit', 'Wai', 70),
(4,'Sharad', 'Mumbai', 80),
(5,'Supriya', 'Chinchwad', 100),
(6,'Raj', 'Solapur', 50);

INSERT INTO Student_Records (Student_Id, Student_Name,

Student_Marks) VALUES (8, 'Eknath', 34);
INSERT INTO Student_Records (Student_Id, Student_Name,
Student_City) VALUES (9, 'Kirit', 'Thane');

-- 1) COUNT:
-- COUNT Aggregate Functions will return number of rows or records
present in the table or specific column

-- A) COUNT() For Whole Table:

-- Command: SELECT COUNT(*) FROM Table_Name;

-- For Ex: SELECT COUNT(*) FROM Student_Records; -- Output: 8
-- B) COUNT() with specific column:

SELECT COUNT(Student_Name) FROM Student_Records; -- Output:

8

-- C) COUNT() With Specific Column Where Null records present

SELECT COUNT(Student_City) FROM Student_Records; -- Output:6

-- Here Null records excluded from the result

SELECT * FROM Student_Records;

-- D) COUNT() With WHERE Clause:

-- Suppose I want total count of the Students from the Pune City

SELECT COUNT(Student_Name) FROM Student_Records WHERE

Student_City='Pune'; -- Output: 1

-- E) COUNT() With DISTINCT:

-- Suppose I want Unique Student_City count

SELECT COUNT(DISTINCT Student_City) FROM Student_Records;

-- Output: 6

-- SQL: Day-19: Date: 11/06/2025

-- 2) SUM:
-- When we use "SUM" aggregate function then we will get
"Summation" or "Addition" of the all records present in the column
-- Command: SELECT SUM(Column_Name) FROM Table_Name;

-- For Ex: SELECT SUM(emp_salary) FROM

Bug_Spotter_Employees; -- Total=188500

--------------------------------------
-- 3) AVG:
-- If we want "average value" from the column then we will use "AVG"
aggregate function

-- Command: SELECT AVG(Column_Name) FROM Table_Name;

-- For Ex: SELECT AVG(emp_salary) FROM
Bug_Spotter_Employees; -- Output: 26928.5714

-- AVG Value= Sum of values/Count of the values

-- AVG Value= 188500/7 =26928.5714

-----------------------------
-- 4) MIN:
-- When we use "MIN" aggregate function then we will get "Smallest"
or "Lowest" value from the column

-- Command: SELECT MIN(Column_Name) FROM Table_Name;

-- For Ex: SELECT MIN(emp_salary) FROM Bug_Spotter_Employees;
-- Output: 14500

--------------------------------
-- 5) MAX:
-- When we use "MAX" aggregate function then we will get "Highest"
or "largest" or "Maximum" value from the column
-- Command: SELECT MAX(Column_Name) FROM Table_Name;
-- For Ex: SELECT MAX(emp_salary) FROM
Bug_Spotter_Employees; -- Output: 35000

--------------------------------------------
-- Q.1: Suppose I want "Earliest" and "Latest" joining date in the output
of the employee

SELECT MIN(emp_joining_date), MAX(emp_joining_date) FROM

Bug_Spotter_Employees;

-- Output: 2020-11-25​2024-01-01

-- Q.2: Suppose I want the 2nd Highest Salary from the column?

-- 1st Way:
-- Command:
SELECT MAX(Column_Name) FROM Table_Name WHERE
Column_Name<(SELECT MAX(Column_Name) FROM Table_Name);

-- For Ex:
SELECT MAX(emp_salary) FROM Bug_Spotter_Employees WHERE
emp_Salary<(SELECT MAX(emp_salary) FROM
Bug_Spotter_Employees);
-- Output: 30000(2nd Highest Salary)

-- Explanation:

-- A) INNER Query: (SELECT MAX(emp_salary) FROM

Bug_Spotter_Employees)
-- Output: 35000 (Highest Salary)
-- B) Outer Query: SELECT MAX(emp_salary) FROM
Bug_Spotter_Employees WHERE emp_Salary<
-- Output: 30000(2nd Highest Salary)

---------------------------------
-- 2nd Way:
-- Command:
SELECT Column_Name FROM Table_Name ORDER BY
Column_Name DESC LIMIT 1,1;

-- For Ex:
SELECT DISTINCT emp_salary FROM Bug_Spotter_Employees
ORDER BY emp_salary DESC LIMIT 1,1; -- 2nd Highest Value: 30000

-- Note: Use "DISTINCT" command to get proper records

---------------------------------------------------
-- Q.2: Suppose I want the 2nd Lowest Value from the column?

-- 1st Way:
-- Command:
SELECT MIN(Column_Name) FROM Table_Name WHERE
Column_Name>(SELECT MIN(Column_Name) FROM Table_Name);

-- For Ex:
SELECT MIN(emp_salary) FROM Bug_Spotter_Employees
WHERE emp_salary>(SELECT MIN(emp_salary) FROM
Bug_Spotter_Employees);

-- Output: 24000(2nd Lowest Value)

---------------------------------------
-- Q.3: Suppose I want 3rd Highest or Nth Highest Salary?

-- Command:
SELECT DISTINCT Column_Name FROM Table_Name ORDER BY
Column_Name DESC LIMIT Offset_Value, Limit_Value;

-- For Ex:
SELECT DISTINCT emp_salary FROM Bug_Spotter_Employees
ORDER BY emp_salary DESC LIMIT 2,1;

-- Explanation:

-- A) SELECT DISTINCT emp_salary FROM Bug_Spotter_Employees

ORDER BY emp_salary DESC
-- Data will be sort out in the descending order (DISTINCT used so
always unique records in the output)
-- Output: 35000,30000,25000,24000,14500

-- B) LIMIT 2,1:

-- 2: This is offset value means it will skip first two rows and consider
data from the 3rd row: 25000
-- 1: This is limit value: Means we will get one record in the output (3rd
Highest Salary: 25000)

-- For Ex:
SELECT DISTINCT emp_salary FROM Bug_Spotter_Employees
ORDER BY emp_salary DESC LIMIT 2,2;

-- Output: We are getting 3rd and 4th Highest Value from the column

-- Task: - Suppose I want 3rd Lowest or Nth Lowest Salary?

-- SQL: Day-20: Date: 12/06/2025

-- Task: - Suppose I want 3rd Lowest or Nth Lowest Salary?

-- Command:
SELECT Column_Name FROM Table_Name ORDER BY
Column_Name ASC LIMIT Offset_Value,Limit_Value;

-- For Ex:
SELECT emp_salary FROM Bug_Spotter_Employees ORDER BY
emp_salary ASC LIMIT 2,1; -- Output: 24000

-- Here we are not getting proper result in the output because the
column present "NULL" records and that will be considered when
command executed
-- So here for the proper result we can exclude the "NULL" records
from the column,

-- Command:
SELECT Column_Name FROM Table_Name WHERE Column_Name
IS NOT NULL ORDER BY Column_Name ASC LIMIT Offset_Value,
Limit_Value;

-- For Ex:
SELECT * FROM Bug_Spotter_Employees WHERE emp_salary IS
NOT NULL ORDER BY emp_salary ASC LIMIT 2,1;
-- Output: 25000(3rd Lowest Value)

------------------------------------------------
-- TCL: Transaction Control Language

-- This command is used to control the "transactions" in the database

-- A transactions in the MYSQL is a "sequence of one or more SQL
Operations" will consider as a "Single Unit"

-- The main idea behind the "TCL Commands" is to ensure the

"ACID": "Atomicity", "Consistency", "Isolation", "Durability" Properties
of the database operations

-- ACID Properties and their examples:

-- 1) Atomicity:

-- Example:
-- Suppose you are transferring Money from "Account A" To "Account
B"
-- The transaction involves debiting money from "Account A" and
"crediting to "Account B"

-- Description:
-- Atomicity ensures that both the steps(debit and credit) are treated
as a "Single Unit"
-- If one step fail(crediting fail) then entire transaction will be rolled
back and no changes are made
-- So either both operations success or both fail

-- 2) Consistency:

-- Example:
-- Suppose you are adding a new record to a table with rule "no
duplicate value" will be added in the column

-- Description:
-- Consistency ensure that after the transactions, the tables still follow
rules applied
-- If any transactions violates/brakes any rules(Like adding duplicate
values, keeping column null) then entire transactions should be fail

-- 3) Isolation:

-- Example:
-- Suppose two Users are trying to buy same product from the
"Flipkart" at a same time

-- Description:
-- Isolation ensures that the two operations do not interface with each
other
-- Each transaction happened independently

-- 4) Durability:

-- Example:
-- Suppose you have added the payment details on the Flipkart and
clicked on the "Buy Product"
-- Your amount got debited from your account and suddenly system
crashed

-- Description:

-- So here Durability ensure that committed transactions(Debited

Amount) will remain permanent even system crash
-- As per the application norms that amount will be returned or some
time will be returned on the previous stage

------------------------------------------------------------
-- TCL Commands are helpful for,

-- 1) Commit your changes

-- 2) Rollback your changes if something goes wrong
-- 3) SAVE intermediate points in the transactions for the rollback

-- Important Commands:
-- 1) START TRANSACTION
-- 2) COMMIT
-- 3) ROLLBACK
-- 4) SAVEPOINT

-- SQL: Day-20: Date: 13/06/2025

-- 1) START TRANSACTION:

-- Means "transactions" or "operations" from this command will be

considered for the "rollback" or "commit" in the database

-- BEGIN: This command rarely used in the MySQL for the start
transaction(Same as a START TRANSACTION command)

-- Command:
START TRANSACTION;

--------------------------------
-- 2) COMMIT:

-- When we use "COMMIT" command then all the changes done by

using current transactions will be "permanently" saved in the
database
-- Changes done by using "INSERT INTO", "UPDATE", "DELETE"
commands will be permanently saved with the help of "COMMIT"
command

-- Command:
COMMIT;

-- We will see one practical example here,

-- For Ex: Suppose I have to increase Nikhil Salary by 5000

START TRANSACTION; -- means transactions from this command

will be consider

UPDATE Bug_Spotter_Employees
SET emp_salary=emp_salary+5000
WHERE emp_name='Nikhil';

COMMIT; -- Saved changes permanently

-- Explanation:

-- START TRANSACTION: Start the Transactions

-- UPDATE: Modify the records
-- COMMIT: Save all the changes permanently in the database

---------------------------------
-- How can we check whether AUTOCOMMIT mode is ON or OFF?

-- Command: SELECT @@Autocommit;

-- SET AUTOCOMMIT=1; -- For enabled the Autocommit Mode

-- SET AUTOCOMMIT=0; For disabled the Autocommit Mode

-- Note:
-- 1) When Autocommit mode ON/Enabled then whatever changes
done or action performed by us get "permanently saved in the
database"
-- 2) Because of the Autocommit Mode we cannot "Rollback" action
performed by us

-----------------------------------------
-- 2) ROLLBACK:

-- The ROLLBACK command used in the SQL to "undo" or "reverse"

changes done by us in the current transactions
-- It will revert the changes or state before the transactions begin

-- Command:
ROLLBACK;

----------------------------------------
-- Practical Example: Without Commit for the Rollback the data

START TRANSACTION; -- From this command transactions will be

consider

-- For Ex: Suppose I have to assign "IT Department" To "Amar"

UPDATE Bug_Spotter_Employees
SET department='IT'
WHERE emp_name='Amar'; -- Changed updated successfully

-- Suppose I want to go back to previous stage/state

ROLLBACK; -- Changes rolled back to previous stage

------------------------------------------
-- Practical Example: With Commit for the Rollback the data

START TRANSACTION;

-- For Ex: Suppose I have to assign "IT Department" To "Amar"

UPDATE Bug_Spotter_Employees
SET department='IT'
WHERE emp_name='Amar'; -- Changes updated successfully

-- Now I am committing the changes,

COMMIT;

-- Suppose I want to go back to previous stage/state

ROLLBACK;
-- Here we cannot return back to previous stage with this command
because changes saved permanently with the help "COMMIT"
command

----------------------------------
-- Practical Example: ROLLBACK with DELETE Command

START TRANSACTION; -- Consider transaction from here

DELETE FROM Bug_Spotter_Employees WHERE

emp_name='Amar'; -- Deleted row Successfully
ROLLBACK; -- Undo deleted data

-----------------------------------------
-- 4) SAVEPOINT:

-- The SAVEPOINT command allows you to set a point with a

transaction to which you can rollback later

-- This command is used to divide a transactions into smaller part so

you can revert changes to specific point

-- Command:
SAVEPOINT Savepoint_Name;

-- Practical Example:

START TRANSACTION;

-- Suppose I have to add one new employee record in the table,

INSERT INTO Bug_Spotter_Employees VALUES (1,'Ajit', 'Finance',

45000, '2025-01-01');

SAVEPOINT Added_New_Employee_Record; -- This is First

Savepoint for the rollback the data

-- Suppose I have to add "Salary" for the "Amar"

UPDATE Bug_Spotter_Employees
SET emp_salary=38000
WHERE emp_name='Amar';
SAVEPOINT Added_Amar_Salary; -- 2nd Savepoint for the rollback
the changes

-- Suppose you realised 2nd Update was mistake and I want to

rollback changes to "Added_New_Employee_Record"

-- Now we will use "ROLLBACK" command to revert changes to

specific Savepoint,

-- Command:
ROLLBACK TO Savepoint_Name;

-- For Ex:
ROLLBACK TO Added_New_Employee_Record;

-------------------------------------
-- Multiple Transactions:

-- START TRANSACTION;

SAVEPOINT Before_Changes; -- 1st Savepoint created before any

changes made

-- Suppose I have to insert few new employee records,

INSERT INTO Bug_Spotter_Employees VALUES (13,'Omkar', 'IT',

43000, '2024-01-01');
INSERT INTO Bug_Spotter_Employees VALUES (14,'Shehal',
'Finance', 41000, '2025-01-01');

SAVEPOINT Added_Two_New_Records; -- 2nd Savepoint

-- Suppose I have to update Salary of the Nikhil from 40000 To 50000

UPDATE Bug_Spotter_Employees
SET emp_salary=50000
WHERE emp_name='Nikhil';

SAVEPOINT Updated_Nikhil_Salary; -- 3rd Savepoint

-- Now suppose I have to delete "Ajit" Record

DELETE FROM Bug_Spotter_Employees WHERE emp_name='Ajit';

SAVEPOINT Deleted_Ajit_Record; -- 4th Savepoint

ROLLBACK TO Added_Two_New_Records;

-- SQL: Day-22: Date: 16/06/2025

-- Note: Suppose we have total 5 savepoints and we rolled back

changes up to 3rd Savepoint then "4th" and "5th" savepoint are not
useful for further any action
-- Suppose we don't want specific savepoint then we can release it

-- Command: RELEASE SAVEPOINT savepoint_name;

---------------------------------------------
-- Can we rollback columns or table deleted by the "DROP" Command
with the help of "ROLLBACK" Command
-- Answer: No. We cannot rollback columns or table deleted by using
"DROP" command with the help of "ROLLBACK"

-- Reason:
-- In the MYSQL DDL Commands like "DROP", "TRUNCATE",
"ALTER", "CREATE" are "Auto-Commited"
-- Means once we executed this command then changes will be
permanently applied to database and we cannot rollback it

-- For Ex: We will delete one column and try to rollback data

-- START TRANSACTION;

-- Suppose I have to drop/delete Student_Mobile_number column

ALTER TABLE Student_Data DROP COLUMN

Student_Mobile_Number;

-- ROLLBACK;

-- Deleted column will not rollback because ALTER Command is

"Auto-Commited"

-----------------------------------------
-- UNION and UNION ALL:

-- When we want to combine two or multiple columns of the two or

multiple tables then we will use "UNION" and "UNION ALL" command

-- A) UNION:
-- When we will use "UNION" command then "Duplicate Values" not
allowed
-- Means UNION Command only return "UNIQUE" records in the
output
-- Means two SELECT statement data will be combined with the help
of "UNION" command

-- Command:
SELECT Column_Name1, Column_Name2 FROM Table_Name1
UNION
SELECT Column_Name1, Column_Name2 FROM Table_Name2;

-- For Ex:1: For Single Column

SELECT Student_Name FROM Practice_1

UNION
SELECT Student_Name FROM Practice_2; -- Return unique records
in the output

-- For Ex:2: Different Datatype

SELECT Student_Name FROM Practice_1

UNION
SELECT ID FROM Practice_2; -- Here records get combined in the
column

-- Note:
-- In the Oracle Database we cannot combine records of different
datatype
-- We will get error message: ORA-01790: expression must have
same datatype as corresponding expression
-- For Ex:3: Multiple Columns

SELECT Student_Id, Student_Name FROM Practice_1

UNION
SELECT Id, Student_Name FROM Practice_2;

-- Here we are getting some duplicate student_id in the column but

reason is "Unique" Students name

-- For Ex:4: Column count not same in the SELECT statement

SELECT Student_Id, Student_Name FROM Practice_1

UNION
SELECT Student_Name FROM Practice_2;

-- Getting Error Code: 1222. The used SELECT statements have a

different number of columns​ 0.015 sec

------------------------------------------------
-- UNION ALL:

-- When we will use "UNION ALL" command for combine two columns
records then it will allows "Duplicate Records" as well
-- Means UNION ALL command will return "UNIQUE" as well
"Duplicate" Records

-- Command:
SELECT Column_Name1, Column_Name2 FROM Table_Name1
UNION ALL
SELECT Column_Name1, Column_Name2 FROM Table_Name2;

-- For Ex:1: For Single Column

SELECT Student_Name FROM Practice_1
UNION ALL
SELECT Student_Name FROM Practice_2; -- Return All the
records(Duplicate and Unique) in the output

-- For Ex:2: Different Datatype

SELECT Student_Name FROM Practice_1

UNION ALL
SELECT ID FROM Practice_2; -- Here records get combined in the
column

-----------------------------------------------
-- UNION with ORDER BY Command

-- ORDER BY we can use with UNION and UNION ALL query but it
will always appear after the final SELECT Statement
-- We cannot use "ORDER BY" inside the individual SELECT
statement

-- For Ex: Single Column

SELECT Student_Id FROM Practice_1
UNION
SELECT ID FROM Practice_2
ORDER BY Student_Id DESC;

-- For Ex: Multiple Column

SELECT Student_Id, Student_Name FROM Practice_1

UNION
SELECT Id, Student_Name FROM Practice_2
ORDER BY Student_Id ASC, Student_Name DESC;

-- SQL: Day-23: Date:18/06/2025

-- ALIAS Command:

-- In SQL ALIAS command used to provide "temporary name" for the

table, column and views present in the database
-- This temporary name will be visible only at the time of query
execution and it will not stored in the database

-- Why is the ALIAS command used in SQL?

-- 1) Simplify the table or column name in the query

-- 2) To make query more readable and understandable to use
-- 3) Mostly in the "SQL Joins", "Aggregate Functions", "GROUP BY
Functions" used this command

-- 1) For Column:
-- Command: SELECT Column_Name AS Alias_Column_Name
FROM Table_Name;
-- For Ex: SELECT department AS Emp_Department FROM
Bug_Spotter_Employees;

-- 2) For Table:
-- Command: SELECT * FROM Table_Name AS Alias_Table_Name;
-- For Ex: SELECT * FROM Bug_Spotter_Employees AS b;

------------------------------------------
-- Command for the Multiple Columns:
SELECT alias_table_name.column_name,
alias_table_name.column_name FROM Table_Name AS
alias_table_name;

SELECT b.emp_id, b.emp_name, [Link] FROM

Bug_Spotter_Employees AS b;

-- 3) ALIAS with Aggregate Functions:

SELECT MAX(emp_salary) AS Highest_Salary FROM

Bug_Spotter_Employees;

---------------------------------------------
-- SQL Operators:

-- SQL operators are special symbols or keywords used to perform

operations on the values or data in the column
-- These operations may be "arithmetic", "logical", "comparison" based
-- These operators are helped to "filter out the data", manipulating the
data", "apply the specific conditions"

-- Types of the Operators:

-- 1) Arithmetic Operators: +, -, *, /, %
-- 2) Logical Operators: AND, OR, NOT
-- 3) Comparison Operators: =, !=, >, >=, <, <=
-- 4) Identify Operators: IS, IS NOT
-- 5) Bitwise Operators
-- 6) SET Operators: UNION, INTERSECT

------------------------------------------
-- 1) Arithmetic Operators:
-- If we want to perform "mathematical operations" then we will use
"Arithmetic Operators"
-- Mathematical Operations are "Addition(+)", "Substraction(-)",
"Multiplication(*)", "Division(/)", "Modulus(%)"

-- Simple Examples:
-- SELECT 200 + 200; -- 400
-- SELECT 100 -50; -- 50
-- SELECT 1000 * 20; -- 20000
-- SELECT 1000/25; -- 40
-- SELECT 900%30; -- 0

-- Combined Examples:

-- 1) Addition:
SELECT (100 + 50) + (50+ 150) AS Total; -- 350

-- 2) Substraction:
SELECT (100-50) - (20-10) AS Difference; -- 40

-- 3) Multiplication:
SELECT 20 * 10 + 100 AS Result; -- 300

-- 4) Division:
SELECT (100/8) / (10/2) AS Quotient; -- 2.5

-- 5) Modulus:
SELECT (100%9) + (50%7) AS Remainder; -- 2

-- 6) Combined All Operators:

SELECT (10+20) -(5*2)/(10%3) AS Combined_Result; -- 20
-- Now we will create one table,

CREATE TABLE BS_Employees (emp_id INT, emp_name

VARCHAR(20), emp_salary DECIMAL(10,2), BONUS
DECIMAL(10,2));

-- Now we will insert data in the table,

INSERT INTO BS_Employees

VALUES
(1, 'Pravin', 50000,5000),
(2, 'Kiran', 60000, 6000),
(3, 'Omkar', 70000, 7000),
(4, 'Pooja', 70000, 5000);

SELECT * FROM BS_Employees;

--------------------------------
-- Now we will see the use of the Arithmetic Operators,

-- 1) Addition(+):

-- Suppose I have to calculate total salary(Salary + Bonus) for each

employee

SELECT emp_id , emp_name, emp_salary + bonus AS

Total_Monthly_Salary FROM BS_Employees;

---------------------------
-- 2) Substraction(-):
-- Suppose I want to calculate adjusted salary(Salary - Bonus) For
each Employees

SELECT emp_id , emp_name, emp_salary - bonus AS

Total_Monthly_Salary FROM BS_Employees;

--------------------------
-- 3) Multiplication:

-- Suppose I have to calculate Total yearly package (Salary + Bonus)

of the each employees

SELECT emp_Id, emp_Name, (emp_salary + bonus) * 12 AS

Total_Yearly_Package FROM BS_Employees;

------------------------
-- 4) Division:

-- Suppose consider provided salary in the table are yearly package of

the employee and we have to find out monthly salary for each
employee

SELECT emp_Id, emp_name, emp_salary/12 AS

Emp_Monthly_Salary FROM BS_Employees;

-------------------------
-- 5) Remainder/Modulus(%):

-- Suppose I have to calculate remainder when dividing the salary by

20000 for each employee
SELECT emp_name, (emp_salary % 20000) AS Salary_Modulus
FROM BS_Employees;

--------------------------
-- 6) Increase Salary By(%):

-- Suppose I have to increase salary by 10% of each employee

SELECT emp_Id, emp_name, emp_salary AS Current_Salary,

emp_salary * 1.10 AS Increased_Salary FROM BS_Employees;

-- SQL: Day-24: Date: 19/01/2025

-- SET Operators:
-- 1) UNION and UNION ALL

-- 2) INTERSECT:
-- When we use INTERSECT command then it will return only
"Common Records" from two or more tables or SELECT Statement

-- Command:
SELECT Column_Name FROM Table_Name1
INTERSECT
SELECT Column_Name FROM Table_Name2;

-- For Ex:

SELECT Student_Name FROM Practice_1

INTERSECT
SELECT Student_Name FROM Practice_2;
-- Here we are getting "Kartik" and "Ashwini" because both records
are common in the two tables

-----------------------------------------
-- How to take backups in the MYSQL? with the help of 'mysqldump'

-- Steps:
-- 1) Open Command Prompt(Open Administrator Window)

-- 2) Go to the path of the server or locate mysqldump utility path

-- We need to provide path of the bin folder of server

-- 3) How to check Username:

-- command: SELECT USER(); -- To get current username: Output:
root@localhost

-- 4) Run the mysqldump command for backup:

-- Command: mysqldump -u[username]

-p[database_name]>[backup_file.sql]

-- Explanation:

-- -u[username] : Username of the MySQL User: For Ex: root

-- -p: This will prompts for the password: So here we need to provide
the password when prompted
-- [database_name] : The name of the database you want to back up
data
-- [backup_file.sql] : The path where backup file should be saved

-- We need to run below command on the mysqldump location:

-- For Ex: mysqldump -u root -p ds_batch_22>
C:\DS_Batch_Backup\ds_batch_22.sql

-------------------------------------------------------
-- If you want to take backup of specific table from the database

-- Command: mysqldump -u username -p database_name

table_name> backup_file_path\backup_file.sql

-- For Ex: mysqldump -u root -p ds_batch_22 bs_batch_22 >

C:\DS_Batch_Table_Backups\bs_batch_22.sql

---------------------------------------------------
-- If we want to take backup of the multiple tables,

-- Command: mysqldump -u username -p database_name

table_name1 table_name2 > backup_file_path\backup_file.sql

-- For Ex: mysqldump -u root -p ds_batch_22 bs_batch_22 bs_class >

C:\DS_Batch_Table_Backups\bs_batch_22.sql

--------------------------------------------------
-- If you want to take backup of all databases

-- Command: mysqldump -u root -p --all-databases >

path_of_backup_folder\name_of_backup_file.sql

-- For Ex: mysqldump -u root -p --all-databases >

C:\DS_Batch_Table_Backups\bs_batch_all_database.sql

-- SQL: Day-25: 20/06/2025

-- SQL JOINS:

-- If we want to join or connect tables each other then we will use

"JOIN command"
-- These two or multiple tables will be joined on the basis of
"PRIMARY KEY" and "FOREIGN KEY".

-- Interview Question: What is the difference between "PRIMARY

KEY" and "FOREIGN KEY"?

-- PRIMARY KEY:

-- 1) "PRIMARY KEY" always declare in the "Parent Table" or we can

say "Primary Key" applied table will be "Parent Table" or We can say
"Left side Table"
-- 2) If we added "PRIMARY KEY" constraint on the column then "we
cannot keep that column value null" and "we cannot insert duplicate
value in the column"
-- 3) PRIMARY KEY constraint we can apply for only "one column" in
the table
-- 4) PRIMARY KEY used to "link" or "connect" or "join" two or multiple
tables

-- FOREIGN KEY:

-- 1) "FOREIGN KEY" always declare in the "Child Table" or we can

"Foregin Key" applied table will be "child table" or we can say "Right
Side Table"
-- 2) FOREIGN KEY allows "Duplicate Values" as well as "NULL
Values"
-- 3) We can apply the "FOREIGN KEY" constraint to multiple columns
-- 4) FOREIGN KEY used to "link" or "connect" or "join" two or multiple
tables

---------------------------------------------
-- Types of the JOIN:

-- 1) INNER JOIN
-- 2) LEFT JOIN
-- 3) RIGHT JOIN
-- 4) FULL JOIN
-- 5) CROSS JOIN

------------------------------------------
-- We will create two new tables 1) Parent Table 2) Child Table:

USE DS_Batch_22;

-- 1) Create New Parent Table with PRIMARY KEY: Employee

CREATE TABLE Employee (Emp_ID INT, Emp_First_Name

VARCHAR (50), Emp_Last_Name
VARCHAR (50), Age INT, EmailID VARCHAR (50), Mobile_NO
BIGINT, Address VARCHAR (50),
PRIMARY KEY (Emp_ID)); -- Table Created Successfully

CREATE TABLE Employee (Emp_ID INT, Emp_First_Name

VARCHAR (50), Emp_Last_Name
VARCHAR (50), Age INT, EmailID VARCHAR (50), Mobile_NO
BIGINT, Address VARCHAR (50),
PRIMARY KEY (Emp_ID, EMP_First_Name)); -- Table Created With
Composite Primary Key
CREATE TABLE Employees (Emp_ID INT PRIMARY KEY,
Emp_First_Name VARCHAR (50) PRIMARY KEY, Emp_Last_Name
VARCHAR (50), Age INT, EmailID VARCHAR (50), Mobile_NO
BIGINT, Address VARCHAR (50));

-- In the above example we are trying to apply "PRIMARY KEY"

constraint to multiple columns separately and because of that
getting below error
-- Error Code: 1068. Multiple primary key defined​ 0.000 sec

-- 2) Now we will insert data in the Parent Table:

INSERT INTO Employee VALUES (1, 'Nikhil', 'Jadhav', 23,

'nikhil@[Link]', 9863253524,'Pune');
INSERT INTO Employee VALUES (2, 'Nilesh', 'Mohite', 22,
'nilesh@[Link]', 9863253564,'Mumbai');
INSERT INTO Employee VALUES (3, 'Sachin', 'Sakpal', 25,
'sachin@[Link]', 9963253564,'Delhi');
INSERT INTO Employee VALUES (4, 'Nitin', 'Shinde', 29,
'nitin@[Link]', 9999253564,'Bengaluru');
INSERT INTO Employee VALUES (5, 'Kedar', 'Jadhav', 34,
'kedar@[Link]', 9977253564,'Kolkata');

-- In the below example trying to insert duplicate Emp_Id in the

Primary Key applied column:
INSERT INTO Employee VALUES (5, 'Kedar', 'Jadhav', 34,
'kedar@[Link]', 9977253564,'Kolkata');

-- Getting Error Code: 1062. Duplicate entry '5' for key

'[Link]'​0.016 sec
-- In the below example trying to insert null data in the primary key
applied column:
INSERT INTO Employee VALUES (NULL, 'Kedar', 'Jadhav', 34,
'kedar@[Link]', 9977253564,'Kolkata');

-- Getting Error Code: 1048. Column 'Emp_ID' cannot be null​

0.000 sec

SELECT * FROM Employee;

-- 3) Create New Project Table With Foreign Key(Child Table):

CREATE TABLE Project (Project_ID INT, Emp_ID INT, Client_ID INT,

Project_Name VARCHAR
(50), Project_Start_Date DATE, FOREIGN KEY (Emp_ID)
REFERENCES Employee(Emp_ID));

-- Here while creating the "Child Table" we need to provide reference

of the "Parent Table Primary Key applied Column"
-- For Ex: FOREIGN KEY (Emp_ID) REFERENCES
Employee(Emp_ID))

-- 4) Now we will insert data in the Child Table:

INSERT INTO Project VALUES (111, 1, 3, 'Project1', '2020-01-01');

INSERT INTO Project VALUES (222, 2, 1, 'Project2', '2020-02-02');
INSERT INTO Project VALUES (333, 3, 5, 'Project3', '2020-03-03');
INSERT INTO Project VALUES (444, 3, 2, 'Project4', '2020-04-04');
INSERT INTO Project VALUES (555, 5, 4, 'Project5', '2020-05-05');

-- SQL: Day-26: Date: 23/06/2025

-- Here in the below example we are trying to insert duplicate value in

the foreign key applied column

INSERT INTO Project VALUES (555, 5, 4, 'Project5', '2020-05-05'); --

Duplicate Emp_Id accepted

-- Here in the below example we are trying to insert "NULL" data in the
foreign key applied column

INSERT INTO Project VALUES (555, NULL, 4, 'Project5',

'2020-05-05'); -- NULL Data accepted

INSERT INTO Project VALUES (555, 6, 4, 'Project5', '2020-05-05'); --

We are trying to insert "Emp_Id" which not present in the "Parent
Table"

-- We are getting Error Code: 1452. Cannot add or update a child row:
a foreign key constraint fails (`ds_batch_22`.`project`, CONSTRAINT
`project_ibfk_1` FOREIGN KEY (`Emp_ID`) REFERENCES
`employee` (`Emp_ID`))​ 0.031 sec

SELECT * FROM Project;

-- Now we will see different types of the Join,

-- 1) INNER JOIN:
-- When we use "INNER JOIN" then we will get only "matching
records" or "Values" from both the tables in the output

-- Command:

SELECT Table1.Column1, Table1.Column2, Table2.Column1,

Table2.Column2
FROM Table_Name1 INNER JOIN Table_Name2
ON
Table_Name1.MatchingColumn_Name(PrimaryKeyAppliedColumn)=
Table_Name2.MatchingColumn_Name(ForeignKeyAppliedColumn);

-- For Ex:(Without ALIAS)

SELECT Employee.Emp_ID, Employee.Emp_First_Name,

Employee.Emp_Last_Name, Project.Project_ID,
Project.Project_Name
FROM Employee INNER JOIN Project
ON Employee.Emp_ID=Project.Emp_ID;

-- For Ex:(With ALIAS)

SELECT e.Emp_ID, e.Emp_First_Name, e.Emp_Last_Name,

p.Project_ID, p.Project_Name
FROM Employee AS e INNER JOIN Project AS p
ON e.Emp_ID=p.Emp_ID;

-- Output:
-- Note: In the above output emp_id=4 is missing because this emp_id
does not present in the both tables(Present only in the child table) so
excluded from the output and only matching records returns in the
output

----------------------------------------------
-- 2) LEFT JOIN:

-- When we use "LEFT JOIN" then returns all the rows from the "Left
Side Table(Employee)" and matching rows from the "Right Side
Table(Project)"
-- If matching records not present in the right side table then it return
"NULL" data for those columns

-- Command:

SELECT Table1.Column1, Table1.Column2, Table2.Column1,

Table2.Column2
FROM Table_Name1 LEFT JOIN Table_Name2
ON
Table_Name1.MatchingColumn_Name(PrimaryKeyAppliedColumn)=
Table_Name2.MatchingColumn_Name(ForeignKeyAppliedColumn);

-- For Ex:
SELECT Employee.Emp_ID, Employee.Emp_First_Name,
Employee.Emp_Last_Name, Project.Project_ID,
Project.Project_Name
FROM Employee LEFT JOIN Project
ON Employee.Emp_ID=Project.Emp_ID;

-- Note: Here for the Emp_Id=4 we are getting null data in the right
table columns because this emp_id not present in the right side table

------------------------------------------
-- 3) RIGHT JOIN:
-- When we will use "RIGHT JOIN" then it will return "all the records"
from the right side table
-- Also we will get "matching records" from the left side table in the
result
-- If matching records not present in the left side table then we will get
null records for it

-- Command:

SELECT Table1.Column1, Table1.Column2, Table2.Column1,

Table2.Column2
FROM Table_Name1 RIGHT JOIN Table_Name2
ON
Table_Name1.MatchingColumn_Name(PrimaryKeyAppliedColumn)=
Table_Name2.MatchingColumn_Name(ForeignKeyAppliedColumn);

-- For Ex:
SELECT Employee.Emp_ID, Employee.Emp_First_Name,
Employee.Emp_Last_Name, Project.Project_ID,
Project.Project_Name
FROM Employee RIGHT JOIN Project
ON Employee.Emp_ID=Project.Emp_ID;

--------------------------------------------
-- 4) FULL JOIN:

-- FULL JOIN directly not supported in the MYSQL(It supported in the

Oracle Database)
-- FULL JOIN will return all the records from both the tables even
matching or not
-- If there are not any matching records then "NULL" records will be
returned for it
-- We can say FULL JOIN it is combination of the "Left" and "Right"
join
-- We can achieve FULL JOIN in the "MYSQL" with the help of
"UNION" command where we can combine "Left join" and "Right Join"

-- For Ex:

SELECT Employee.Emp_ID, Employee.Emp_First_Name,

Employee.Emp_Last_Name, Project.Project_ID,
Project.Project_Name
FROM Employee LEFT JOIN Project
ON Employee.Emp_ID=Project.Emp_ID

UNION / UNION ALL

SELECT Employee.Emp_ID, Employee.Emp_First_Name,

Employee.Emp_Last_Name, Project.Project_ID,
Project.Project_Name
FROM Employee RIGHT JOIN Project
ON Employee.Emp_ID=Project.Emp_ID;

SELECT * FROM Project;

-- SQL: Day-27: Date: 24/06/2025

-- 5) CROSS JOIN:
-- A CROSS JOIN returns the cartesian data(Create pair for every row
from the tables) from both the tables
-- Command: SELECT * FROM Table_1 CROSS JOIN Table_2;

-- For Ex: SELECT * FROM Employee CROSS JOIN Project;

USE ds_batch_22;

-------------------------------------
-- GROUP BY Clause:

-- GROUP BY Clause used in the SQL to group rows that have the
same values in the one or more multiple columns

-- GROUP BY Clause mostly used with aggregate functions like

"COUNT()", "SUM()", "MIN()", "MAX()", "AVG()"

-- Important points of the GROUP BY:

-- 1) The GROUP BY Clause always comes after the "WHERE

Clause"
-- 2) The ORDER BY Clause always comes after the "GROUP BY"
Clause
-- 3) HAVING Clause always comes after the "GROUP BY" Clause
-- 4) We will get only "UNIQUE Records" in the output with "GROUP
BY" Clause

-- Now we will create one table,

CREATE TABLE Flipkart_Order_Data(Order_Id INT, Customer_Id INT,

Product_Name VARCHAR(20), Quantity INT);

-- Now we will insert data in the table,

INSERT INTO Flipkart_Order_Data
VALUES
(1, 101, 'Laptop', 2),
(2, 102, 'SmartPhone', 3),
(3, 101, 'Tablet', 2),
(4, 102, 'HeadPhone', 3),
(5, 103, 'Laptop', 5);

SELECT * FROM Flipkart_Order_Data;

-- Now we will start use of the "GROUP BY" Clause

-- Command:
SELECT Column_Name1, Column_Name2,
Aggregate_Function(Column_Name) FROM Table_Name
GROUP BY Column_Name1, Column_Name2;

-- First we will try with Single Column,

-- For Ex:1: Suppose I want "Total Quantity" of each Product Ordered

(GROUP BY with SUM Aggregate Function)

SELECT Product_Name, Quantity, SUM(Quantity) FROM

Flipkart_Order_Data GROUP BY Product_Name;
-- We are getting Error Code: 1055. Expression #2 of SELECT list is
not in GROUP BY clause and contains nonaggregated column
'ds_batch_22.Flipkart_Order_Data.Quantity' which is not functionally
dependent on columns in GROUP BY clause; this is incompatible with
sql_mode=only_full_group_by​ 0.000 sec
-- Because we have passed single column with GROUP BY and two
columns in the SELECT Statement

SELECT Product_Name, SUM(Quantity) FROM Flipkart_Order_Data

GROUP BY Product_Name;

-- Here data groped by on the basis "Product_name" column

-- For Ex:2: Suppose I want count of each order of each

product(GROUP BY with COUNT Aggregate Function)

SELECT Product_Name, COUNT(Product_Name) FROM

Flipkart_Order_Data GROUP BY Product_Name;

-- Task of the Day:

-- For Ex:3: Suppose I want "Average Quantity" of the each product

ordered

-- For Ex:4: Suppose I have to calculate "Minimum Quantity" of each

product Ordered

-- For Ex: 5: Suppose I have to calculate "Maximum Quantity" of each

product Ordered

-- SQL: Day-28: Date: 25/06/2025

-- Task of the Day:

-- For Ex:3: Suppose I want "Average Quantity" of the each product

ordered

SELECT Product_Name, AVG(Quantity) FROM Flipkart_Order_Data

GROUP BY Product_Name;

-- For Ex:4: Suppose I have to calculate "Minimum Quantity" of each

product Ordered

SELECT Product_Name, MIN(Quantity) FROM Flipkart_Order_Data

GROUP BY Product_Name;

-- For Ex: 5: Suppose I have to calculate "Maximum Quantity" of each

product Ordered

SELECT Product_Name, MAX(Quantity) FROM Flipkart_Order_Data

GROUP BY Product_Name;

-- For Ex:6: Suppose I want to 'sort out quantity column data" in the
descending order after the addition of "Quantity column" of each
product

-- 1st Way:
SELECT Product_Name, SUM(Quantity) AS Total_Quantity FROM
Flipkart_Order_Data GROUP BY Product_Name ORDER BY
Total_Quantity DESC;

-- 2nd Way:
SELECT Product_Name, SUM(Quantity) FROM Flipkart_Order_Data
GROUP BY Product_Name ORDER BY SUM(Quantity) DESC;

-- For Ex:7: GROUP BY With WHERE Clause:

-- Suppose I want "Customer_Id" in the output whose

product_name='Laptop' and Customer_Id>101

SELECT Customer_Id, Product_Name FROM Flipkart_Order_Data

WHERE Product_Name='laptop' AND Customer_Id>101
GROUP BY Customer_Id, Product_Name;

----------------------------------------
-- HAVING Clause:

-- The HAVING clause used in the SQL to filter out data after the
grouping with the help "GROUP BY" command
-- Having clause also mainly used with aggregate function "COUNT",
"MIN", "MAX", "AVG", "SUM"

-- Interview Question: What is the difference between "WHERE" and

"HAVING" Clause?

-- WHERE Clause is applied before the grouping or aggregation of the

data
-- We cannot use "Aggregate Functions" inside the WHERE Clause

-- HAVING Clause is applied after the grouping or aggregation of the

data
-- We can use "Aggregate Functions" inside the HAVING Clause

-- Command for the HAVING Clause:

-- SELECT Column_Name1, Aggregate_Function(Column_Name2)
FROM Table_Name GROUP BY Column_Name1 HAVING Condition;

-- Q. Suppose I want those "customer_Id" in the output whose

'Product quantity' greater than 1

SELECT * FROM Flipkart_Order_Data WHERE quantity>1;

SELECT Customer_Id, Product_Name FROM Flipkart_Order_Data

WHERE quantity>1; -- Normal Query

SELECT Product_Name, Customer_Id, Quantity FROM

Flipkart_Order_Data WHERE quantity>1 GROUP BY Product_Name,
Customer_Id, Quantity; -- With WHERE Clause and GROUP BY
Clause

SELECT Product_Name, Customer_Id, Quantity FROM

Flipkart_Order_Data GROUP BY Product_Name, Customer_Id,
Quantity HAVING Quantity>1; -- With GROUP BY and HAVING
Clause

-- Q. Suppose I want those "Customer_Id" in the output whose total

product quantity greater than 2

SELECT Customer_Id, SUM(Quantity) AS Total_Quantity FROM

Flipkart_Order_Data GROUP BY Customer_Id HAVING
Total_Quantity>2;

-- For Ex:
SELECT Customer_Id, SUM(Quantity) FROM Flipkart_Order_Data
WHERE SUM(Quantity)>2 GROUP BY Customer_Id; -- We cannot
use "Aggregate Functions" inside the WHERE Clause

SELECT Customer_Id, SUM(Quantity) FROM Flipkart_Order_Data

GROUP BY Customer_Id HAVING SUM(Quantity)>2; -- We can use
HAVING with Aggregate Function

-- Task of the Day:

-- Try HAVING Clause with all aggregate functions: SUM, COUNT,
AVG, MIN, MAX
-- Also try multiple examples for understand difference between
WHERE Clause and HAVING Clause with GROUP BY Clause

-----------------------------------------------
-- SQL: Day-29: Date: 26/06/2025

-- VIEWS:

-- A VIEW is "Virtual Table" in the SQL

-- It will not store the actual data separately means data will be stored
in the actual table

-- Why do we use VIEWS?

-- 1) Simplifies the queries:

-- Instead of writing same complex queries repeatedly we can direct
save it as a "views" and we can directly call like a "table"

-- 2) Security:
-- You can hide the certain columns and only show the data which
user required
-- Command:
CREATE VIEW View_Name AS
SELECT Column_Name1, Column_Name2 FROM Table_Name
WHERE Column_Name='Value';

-- For Ex:1: Suppose I have to create "VIEW" that only shows

employees from the finance department

CREATE VIEW Finance_Department_Employees AS

SELECT emp_id, emp_name, emp_salary
FROM Bug_Spotter_Employees
WHERE department='finance';

-- How to query/fetch data from the view?

-- Here we can use same command like "Table"

-- For ex: SELECT * FROM Finance_Department_Employees; -- Call

newly created view
-- For Ex: SELECT * FROM Bug_Spotter_Employees; -- Call main
table data

-- For Ex: Suppose I want employees from the IT Department with

Salary over 25000 and calculate their annual package

-- Regular Command:
SELECT Emp_Id, Emp_Name, Department, Emp_Salary, Emp_Salary
*12 AS Emp_Annual_Salary FROM Bug_Spotter_Employees
WHERE department='IT' AND emp_salary>25000;

-- Above query is complex and use of this repetitive so instead this

complex query we can create "View" for this
-- For Ex:
CREATE VIEW IT_Department_Employee_Salary AS
SELECT Emp_Id, Emp_Name, Department, Emp_Salary, Emp_Salary
*12 AS Emp_Annual_Salary FROM Bug_Spotter_Employees
WHERE department='IT' AND emp_salary>25000;

-- SELECT * FROM IT_Department_Employee_Salary; -- Call Newly

Created View

-- Suppose I have to update Bhumika Salary From 30000 to 35000

from the View:

UPDATE IT_Department_Employee_Salary
SET Emp_Salary= 35000
WHERE Emp_Id=5;

-- Note:
-- We can update, insert or delete data through "Views" but there are
some limitation
-- We cannot modify data the views if they used complex
queries(JOIN, GROUP BY)

-- For Ex: Suppose I have to delete specific rows or records from

the view

DELETE FROM IT_Department_Employee_Salary WHERE

Emp_Id=5; -- Row get deleted from the View as well main table

-- For Ex: Suppose I have to insert one employee details in the

Views
INSERT INTO IT_Department_Employee_Salary (Emp_Id,
Emp_Name, Department, Emp_Salary) VALUES (14, 'Sachin', 'IT',
45000);

-- We are getting Error Code: 1471. The target table

IT_Department_Employee_Salary of the INSERT is not insertable-into​
0.000 sec because we applied some condition while creating the
views

-- For Ex: Suppose I have to drop/delete specific views

-- Command: DROP VIEW IF EXISTS VIEW_NAME;

-- For Ex: DROP VIEW IF EXISTS IT_Department_Employee_Salary;
-- Here only views dropped not a data or column from the main table

-- How to check VIEWS present in the current database?

-- Command: SHOW FULL TABLES WHERE TABLE_TYPE='VIEW';

-- SQL: Day-30: Date: 27/06/2025

-- Interview Question: Suppose we have created a "VIEW" of the

"GROUP BY" or "JOIN" Query so can we perform "DELETE",
"INSERT" or "UPDATE" command on that views

-- Now we will create one View for the GROUP BY Clause

-- For Ex: Suppose I have to do the addition of the salaries

department wise

CREATE VIEW Dept_Employees_Salary_Additions AS

SELECT department, SUM(emp_salary) FROM
Bug_Spotter_Employees GROUP BY department;

-- Suppose I have to update name of the department(HR To

HR_Department):

UPDATE Dept_Employees_Salary_Additions
SET department='HR_Department'
WHERE department='HR';

-- Getting Error Code: 1288. The target table

Dept_Employees_Salary_Additions of the UPDATE is not updatable​
0.015 sec

-- Task of the Day:

-- 1) Practice VIEW Topic with all the possible examples(Positive and

Negative)
-- 2) Try with SQL JOIN as well

-- Interview Questions:

-- 1) What is a view and why is it used?

-- 2) How is a view created?
-- 3) Can we update data through view?
-- 4) What will happen if the base or main tables are modified?
-- 5) Can we use Joins in the Views?
-- 6) Can we use Views with Aggregates, GROUP BY and HAVING
Clauses?
-- 7) Can views be nested? Means can we create a view from another
view?
-- 8) What are the advantages and disadvantages of the views?
-- DCL: Data Control Language

-- In the DCL "GRANT" and "REVOKE" command are used for the
controlling the access of specific user for accessing data from the
database
-- These commands are mostly used by the "Data Administrators" to
manage the permission effectively and ensure users have correct
access to perform their task
-- Data security will be maintained with the help of "DCL Commands".

-- 1. GRANT Command:

-- The GRANT Command is used to provide the access(privileges) to

specific user
-- GRANT command help to provide full access of the database or any
specific access: SELECT(READ), INSERT(ADD), UPDATE, DELETE

-- Command:
-- GRANT privilege_Type ON database_name.table_name TO
'Username'@'host';

-- Explanation:

-- privilege_Type: Type of the Access(SELECT, INSERT, DELETE,

UPDATE, ALL Privilege)
-- database_name.table_name: The name of the database and table
-- Username: The User who required the access
-- host: specific host from which the user can access the
database(localhost)
-- 2. REVOKE Command:

-- The REVOKE Command is used to remove previously granted

access(privileges) from the specific user

-- Command:
REVOKE privilege_Type ON database_name.table_name FROM
'username'@'host';

---------------------------------------------
-- Practical Implementation of the DCL Commands

CREATE DATABASE DS22_DCL_Commands; -- Create a New

Database

USE DS22_DCL_Commands;

-- Create a New Table:

CREATE TABLE BS_22_Batch_Students(Student_Id INT

auto_increment PRIMARY KEY, Student_Name VARCHAR(20),
Student_Grade VARCHAR(5));

-- CREATE NEW User:

-- Command:
CREATE USER 'Username'@'localhost' identified by 'Password';

-- For Ex:
CREATE USER 'BS22'@'localhost' identified by 'BugSpotter123'; --
Created New User
-- Now we will provide the access(privileges) to newly created
User

-- For Ex:
GRANT SELECT,INSERT,UPDATE ON
DS22_DCL_Commands.BS_22_Batch_Students TO
'BS22'@'localhost'; -- Here we granted SELECT, INSERT and
UPDATE permission to User

-- How to check what kind of the access provided to User:

-- Command: SHOW GRANTS FOR 'Username'@'localhost';

-- For Ex: SHOW GRANTS FOR 'BS22'@'localhost';

-- Output:
/*
GRANT USAGE ON *.* TO `BS22`@`localhost`
GRANT SELECT, INSERT, UPDATE ON
`ds22_dcl_commands`.`bs_22_batch_students` TO
`BS22`@`localhost`
*/

-- Now we will login with New User:

-- Steps:

-- 1) Go to the home page of the MYSQL

-- 2) Then click on the "+" Icon beside the "MySQL Connection"
-- 3) Then provide the connection name: For Ex: BS_22_Connection
-- 4) Host Name: localhost
-- 5) Username: BS22
-- 6) Then click on the "Test Connection"
-- 7) Then provide the password
-- 8) Then click on the OK
-- Then new connection will be added and we can login with new
connection

-------------------------------------
-- Practical Use of the DCL Commands in the New User Connection

-- 1) GRANT Command:

-- Now we will try different commands for checking permission granted

to specific user working or not

USE DS22_DCL_Commands; -- SELECT Database

SELECT * FROM BS_22_batch_Students; -- New Users have read

permission because of the "SELECT" privileges

INSERT INTO BS_22_batch_Students (Student_Name,

Student_Grade) VALUES ('Nehal', 'B');

-- Data inserted successfully means "New User" have authority to add

the data in the table through "INSERT Privileges"

-- Practical Use of the DCL Commands in the New User Connection

-- 1) GRANT Command:

-- Now we will try different commands for checking permission granted

to specific user working or not

USE DS22_DCL_Commands; -- SELECT Database

SELECT * FROM BS_22_batch_Students; -- New Users have read
permission because of the "SELECT" privileges

INSERT INTO BS_22_batch_Students (Student_Name,

Student_Grade) VALUES ('Nehal', 'B');

-- Data inserted successfully means "New User" have authority to add

the data in the table through "INSERT Privileges"

-- Now we will check "UPDATE" privileges

-- Suppose I have to change "Nehal" grade from "B" to "A"

UPDATE BS_22_batch_Students
SET Student_Grade='A'
WHERE Student_Name='Nehal'; -- Data updated Successfully means
user have "UPDATE" privileges as well

-- Now we will try DELETE command:

-- Suppose I have to delete "Bharat" record from the table,

DELETE FROM BS_22_batch_Students WHERE Student_Id=1; --

Here we are getting below error message because we have not
provided "DELETE" privileges to this user

-- Error Code: 1142. DELETE command denied to user

'BS22'@'localhost' for table 'bs_22_batch_students'​ 0.000 sec

----------------------------------------------
-- 2) REVOKE Command: (For reverse the permission)
REVOKE INSERT, UPDATE ON
ds22_dcl_commands.bs_22_batch_students FROM
'BS22'@'localhost'; -- We removed "UPDATE" and "INSERT"
privileges of the User

SHOW GRANTS FOR 'BS22'@'localhost'; -- Check now new user if

any privileges or not

-- Output:
/*
GRANT USAGE ON *.* TO `BS22`@`localhost`
GRANT SELECT ON `ds22_dcl_commands`.`bs_22_batch_students`
TO `BS22`@`localhost`
*/

-- Now we will try INSERT, UPDATE commands with New User

INSERT INTO BS_22_batch_Students (Student_Name,

Student_Grade) VALUES ('Nehal', 'B');

-- We are getting an error: Error Code: 1142. INSERT command

denied to user 'BS22'@'localhost' for table 'bs_22_batch_students'​
0.016 sec

-- Update:
UPDATE BS_22_batch_Students
SET Student_Grade='A'
WHERE Student_Name='Nehal';
-- We are getting an error: Error Code: 1142. UPDATE command
denied to user 'BS22'@'localhost' for table 'bs_22_batch_students'​
0.000 sec

SELECT * FROM BS_22_batch_Students; -- New User have read

permission because of the "SELECT" privileges

DCL Interview Questions

1. What is DCL, and how does it differ from DDL and DML?
2. Why is DCL important in SQL? Can you give real-world examples of
its usage?
3. What are the main DCL commands?
4. What permissions can we grant using the `GRANT` command?
5. Can a user with granted privileges grant those privileges to another
user?
6. Can we grant access to a specific column in a table rather than the
whole table?
7. Is it possible to grant permissions to multiple users at once? How?
8. What happens to a user’s permissions if you revoke access after
granting it?
9. If multiple privileges are granted, can we revoke only one specific
privilege?
10. What happens if a privilege is revoked from a user who granted it
to others?
11. Can we assign a role to a user and use `GRANT` on roles instead
of individual users?
12. How do we check which privileges a user has been granted?
13. Can we revoke all privileges from a user at once? How?
14. What happens if we grant conflicting permissions (e.g., `GRANT
SELECT` and `REVOKE SELECT`)?
15. If a user is granted `SELECT` privilege on a database but revoked
from a specific table, can they still access the table?
16. How do we ensure a user has read-only access to specific tables
in a database?
17. How can we limit access to specific IP addresses for a user in
MySQL?
18. What happens if you try to revoke a privilege that wasn’t granted?
19. What’s the difference between `REVOKE` and deleting a user?
20. What are some best practices for using DCL to secure a
database?
21. How do we view the list of all users in MySQL?
22. Can `GRANT` and `REVOKE` be used to control access to stored
procedures or views?
23. Can we use `GRANT` to assign permissions to non-existing
users?
24. Does MySQL automatically revoke permissions when we drop a
table or database?
25. Can we combine `GRANT` and `REVOKE` in a single command?
26. How does MySQL handle permissions for temporary tables?
27. Are permissions automatically inherited when a new table is
created in a schema?
28. Can you provide examples of how permissions are handled in
group-based or role-based security?
29. How do we use `FLUSH PRIVILEGES`, and when is it necessary?
30. What are some security risks of using `WITH GRANT OPTION`?

-- SQL: Day-31: Date: 30/06/2025

-- SQL INDEX:
-- The INDEX used in the SQL to increase speed of the searching
data in the database
-- It will help to retrieve a vast amount of the data from the database
-- Automatic indexes will be created for the "PRIMARY KEY" or
"UNIQUE" Constraint applied column
-- Small tables do not require indexing

-- Real Time Example:

-- Imagine you have a book with 1000 pages but no index.

-- To find out specific topic you need to read each page
-- But if the book has an index you can directly go the specific page
number for the topic
-- Indexing helps to find out rows faster without scanning every row

-- Reason why indexing is required?

-- 1) Faster data retrieval

-- Indexes reduce the amount of the data the database has to scan

-- 2) Efficient Searching:
-- Searching records based on the specific column Values faster with
indexes

-- Command:
CREATE INDEX Index_Name ON Table_Name(Column_1,
Column_2);

-- First we will create one table with huge data,

USE ds_batch_22; -- Database selected

CREATE TABLE Bug_Spotter_Employees_Records(Emp_Id INT
AUTO_INCREMENT PRIMARY KEY, Emp_First_Name
VARCHAR(20),
Emp_Last_Name VARCHAR(20), AGE INT, Email_Id VARCHAR(20),
Mobile_Number BIGINT, Address VARCHAR(20));

-- Now we will insert data in the table,

INSERT INTO Bug_Spotter_Employees_Records

(Emp_First_Name,Emp_Last_Name,AGE,Email_Id,Mobile_Number,A
ddress)
VALUES
('Shrusti', 'Patil', 26, 'Shrusti@[Link]', 98989899998, 'Pune'),
('Bharat', 'Kale', 30, 'bharat@[Link]', 9846998558, 'Barshi'),
('Sachin', 'Tendulkar', 44, 'sachin@[Link]', 9898988965,
'Mumbai'),
('Omkar', 'Pukale', 31, 'omkar@[Link]', 9898986889, 'Satara');

-- Note: Add at least more than 300 Records and try with Index and
without index

-- Now we will fetch specific records from the table and also check
how much records scanned or searched for the return result

-- 1) Without Index:

EXPLAIN SELECT * FROM Bug_Spotter_Employees_Records

WHERE Emp_First_Name='Bharat';

-- Explanation:
-- Type: ALL(Full Table Scanned means MySQL has to go through all
the rows to find out matching records)
-- Key: Empty/Null: Means no index Used
-- Rows: 320 Rows(Means whole table rows scanned)
-- Filtered: 10.00%(This indicates the percentage of the rows that met
the condition using WHERE Clause)

Output:

-- 2) With INDEX:

-- Now we will create one index:

CREATE INDEX idx_emp_first_name ON

Bug_Spotter_Employees_Records(Emp_First_Name);

-- Now we will check with same example after the indexing applied

EXPLAIN SELECT * FROM Bug_Spotter_Employees_Records

WHERE Emp_First_Name='Bharat';

Output:

-- Explanation:

-- Type: ref(This means MySQL is using an index to find out the rows)
-- Key: idx_emp_first_name: Means here indexing used for the
searching data
-- Rows: 80 Rows(Means MYSQL now uses the index to quickly find
out the matching rows, reduce the scan size from 320 To 80 Rows)
-- Filtered: 100.00%(Since the index efficiently narrowed down the the
matching rows, all the rows that were accessed matched the condition
applied)

-- How to check the index created for the specific table?

-- Command: SHOW INDEX FROM Table_Name;

-- For Ex: SHOW INDEX FROM Bug_Spotter_Employees_Records;

Output:

-- Note: For Primary Key indexes automatically created

-- How to drop index created for the specific table,

-- Command: DROP INDEX index_name ON Table_Name;

-- For Ex: DROP INDEX idx_emp_first_name ON
Bug_Spotter_Employees_Records;

-- SQL: Day-32: Date: 01/07/2025

-- Windows Function:

-- Windows Function are very useful in the SQL, that will allow you to
perform calculations across a set of rows(Particular Window)
-- Over Clause is used with Windows Function to define that windows
-- Over Clause will help:

-- 1) Regular Aggregate Functions(For Ex: SUM, AVG...etc) which

returns a "single value" for the group of rows
-- But "Window Function" return a value for each row in the original
dataset while still allowing you to see individual rows details

-- 2) Mostly this functions used to perform tasks like "Ranking",

"Running Total(Cumulative Sum)", "Running Average(Moving
Average)"

-- Practical Use:

-- Command:
SELECT Column_Name1, Window_Function(Column_Name2)
OVER([PARTITION BY Column_Name1][ORDER BY
Column_Name3]) AS New_Column_Name FROM Table_Name;

-- For Ex:1: I have to use Windows Function to calculate the average

emp_salary within each department, Ordered by emp_name(Moving
Average)

SELECT Emp_Name, Department, Emp_Salary, AVG(Emp_Salary)

OVER (partition by department ORDER BY Emp_Name)
AS AVG_Salary FROM Bug_Spotter_Employees;

-- Explanation of the query:

-- partition by department: Divide the data by department so the avg

calculations is done independently for each department
-- ORDER BY Emp_Name: Orders each departments rows by
emp_name
Output:

-- Now we will try without Partition BY:

SELECT Emp_Name, Department, Emp_Salary, AVG(Emp_Salary)

OVER (ORDER BY Emp_Name)
AS AVG_Salary FROM Bug_Spotter_Employees;

Output:
-- For Ex:2: Suppose I want Cumulative SUM of the Salary
Column(Running Total): Without Partition BY

SELECT Emp_Name, Department, Emp_Salary, SUM(Emp_Salary)

OVER (ORDER BY Emp_Name)
AS Cumulative_Sum FROM Bug_Spotter_Employees;

Output:
-- For Ex:2: Suppose I want Cumulative SUM of the Salary
Column(Running Total): With Partition BY:

SELECT Emp_Name, Department, Emp_Salary, SUM(Emp_Salary)

OVER (partition by department ORDER BY Emp_Name)
AS Cumulative_Sum FROM Bug_Spotter_Employees;

Output:
-----------------------------------------
-- Some more windows function:

-- 1) ROW_NUMBER()
-- 2) RANK()
-- 3) DENSE_RANK()

-- 1) ROW_NUMBER():
-- The RON_NUMBER Function assign a unique sequential integer or
number to each row within or without partition
-- Unlike RANK() or DENSE_RANK() it does not allow duplicate
numbers for rows with identical values

-- For Ex:
SELECT EMP_ID, Emp_Name, Department, Emp_Salary,
ROW_NUMBER() OVER(partition by department ORDER BY
Emp_Salary DESC) AS Row_Number_In_Department
FROM Bug_Spotter_Employees;
Output:

-- Explanation:
-- partition by department: This ensures that row numbers reset for the
each department(HR, IT, Finance)
-- ORDER BY Emp_Salary DESC: Within each department rows
ordered by salary in the descending order
-- ROW_NUMBER(): Generates a unique row number for each row in
the specified partition

-- SQL: Day-33: Date: 02/07/2025

-- 2) RANK():
-- The RANK() function assigns a rank to each row, but it skips ranks
when there are ties(duplicate records) in the records

-- For Ex:

SELECT
EMP_ID, Emp_Name, Department, Emp_Salary,
RANK() OVER (PARTITION BY Department ORDER BY
EMP_SALARY DESC) AS Rank_In_Department
FROM Bug_Spotter_Employees;

-- Explanation:
-- PARTITION BY Department: Divide the data into groups based on
their department
-- ORDER BY EMP_SALARY DESC: Orders employees in the
descending order of salary within each department
-- Ties in Salary: In the Finance department "Sachin" and "Snehal"
have same salary so assign the same rank "1" and provided "3rd"
rank to "Nikhil" by skipping 2nd Rank

---------------------------------
-- 3) DENSE_RANK() Function:

-- The DENSE_RANK() function is similar to "RANK()" but it does ot

skips ranks where are the ties of the records

-- For Ex: We want to rank employees in the each department based

on their salary, but we don't want gaps in the ranks when there are ties
in the records

SELECT
EMP_ID, Emp_Name, Department, Emp_Salary,
DENSE_RANK() OVER (PARTITION BY Department ORDER BY
EMP_SALARY DESC) AS Dense_Rank_In_Department
FROM Bug_Spotter_Employees;

-- 4) LEAD() Function:
-- When we use "LEAD()" we will get "NEXT" Rows Values in the
output
-- If next value not present then we will get "NULL" value

-- For Ex:
SELECT
EMP_ID, Emp_Name, Department, Emp_Salary,
LEAD(Emp_Salary) OVER (PARTITION BY Department ORDER BY
EMP_SALARY DESC) AS NEXT_Salary
FROM Bug_Spotter_Employees;

-- 5) LAG() Function:
-- When we use "LAG()" we will get previous rows values in the output
-- If previous value not present then we will get "NULL" value

-- For Ex:
SELECT
EMP_ID, Emp_Name, Department, Emp_Salary,
LAG(Emp_Salary) OVER (PARTITION BY Department ORDER BY
EMP_SALARY DESC) AS NEXT_Salary
FROM Bug_Spotter_Employees;

---------------------------------
-- Interview Questions:

-- 1) ROW_Number():
-- Q. What is the difference between ROW_NUMBER() and RANK()?
-- Q. How does ROW_NUMBER() behave when there are ties in the
records?
-- Q. Write a query to assign a unique sequential number to each row
in the partition?
-- Q. How can you reset the ROW_NUMBER() count for each
department in an employee table?
-- 2) RANK():
-- Q. What is the difference between "RANK()" and
"DENSE_RANK()"?
-- Q. What happens if two rows have the same value in the ordering
column?
-- Q. Write a query to get top 3 highest salary employees in each
department using "RANK()"
-- Q. Can we use "RANK()" to find out the 2nd highest salary in the
table?

-- 3) DENSE_RANK():
-- Q. What is the difference between "RANK()" and
"DENSE_RANK()"?
-- Q. In what scenarios would you prefer DENSE_RANK() over
RANK()?
-- Q. Write a query to return the "DENSE_RANK()" of employees
ordered by salary within each department?
-- Q. If three employees have the highest salary then what rank will
they assign by using the "DENSE_RANK" Function?

-- 4) LEAD():
-- Q. What is the purpose of the LEAD()?
-- Q. How can we use LEAD() to compare a row's values with the next
rows' value?
-- Q. Write a query to display the current and next month's sales for
each product?
-- Q. How can we use "LEAD()" to calculate the difference between
the current and next row's salary?

-- 5) LAG():
-- What is the difference between "LEAD()" and "LAG()" function?