DATABASE MANAGEMENT SYSTEM

DATABASE- Collection of data is called a database.

DBMS - Database Management System is a software application which is used to manage
our data.

1- RELATIONAL DATABASE

Relational database stores the structured data in tables with the help of rows and columns.
They use SQL for structured querying and are good for complex query and structured data
integrity.

Examples - MySql, oracle, MariaDB

2- NO- RELATION DATABASE

Also known as NoSql database. They store data in a variety of formats such as documents,
key-value pairs, graphs and columns. They are good for unstructured and semi structured
data. Such as images, videos, and business documents.

Example - MongoDB

WHAT IS SQL

Sql is a structured query language, which is used for accessing and manipulating
databases. It also helps us in data retrieval,access control as well as transaction
management. It helps us to perform CRUD operations that is create, read,update and
delete in our database

WHAT IS MYSQL

MySQL is a specific relational database management system or software that uses sql as a
querying language.

HISTORY OF SQL

●​ Originated in the 1970

●​ From IBM
●​ It started as SEQUEL (structured english query lang) later renamed as SQL due to
trademark.

HOW SQL HELPS US

1.​ Retrieving Data- Extracting precious information from the database through queries .
2.​ Manipulating Data - Adding, Modifying, or Removing records within a database.
3.​ Defining Data - Create and adjust the structure of the database including
tables,views and index.
4.​ Controlling Data - Managing database by gathering or revoking permission.
DOES SQL SUPPORTS PROGRAMMING LANGUAGE FEATURE

It is true that SQL is a language, but it does not support programming as it is not a
programming language, it is a command language. We do not have conditional statements
in SQL like for loops or if..else, we only have commands which we can use to query,
update, delete, etc. data in the database

TYPES OF COMMANDS

1- DATA QUERY LANGUAGE (DQL)

DQL is used to retrieve or fetch data from a database.

Commands - SELECT

2- DATA MANIPULATION COMMAND

DML is used to manipulate data stored in databases.

Commands - INSERT, UPDATE, DELETE.

3- DATA DEFINITION LANGUAGE COMMAND

DDL is used to define structure and schema of the database.

Command - CREATE, ALTEP, TRUNCATE, RENAME

4- DATA CONTROL LANGUAGE

DLC deals with the control and security of data within database
Commands - GRANT, REVERT

5- TRANSACTION CONTROL LANGUAGE

TCL is used to manage transaction within a database

Commands - COMMIT, ROLLBACK, SAVEPOINT

WHAT IS SCHEMA

It is a logical representation of a database, it is a logic container that keeps database

objects such as tables, triggers, views, index constraints.
 DATABASE COMMANDS
S.NO COMMANDS FOR COMMAND

01 To Create Database Command: CREATE DATABASE databaseName;

Also to avoid errors we can use:

Command: CREATE DATABASE IF NOT EXISTS databaseName;

02 To Use a Database Command: USE databaseName;

03 To show all the Database Command: SHOW DATABASES;

04 To Delete a Database Command: DROP DATABASE databaseName;

Also to avoid errors we can use:

Command : DROP DATABASE IF EXISTS databaseName;

05 To Create a Table Command: CREATE TABLE TableName (

Column1 DataType1 Constraint1,
Column2 DataType2 Constraint2,
Column3 DataType3 Constraint3,

-- additional columns if needed )

Example - CREATE TABLE employee (

empId INT PRIMARY KEY,
name VARCHAR(50),
salary INT
);

06 To Insert values into table COMMAND-INSERT INTO tableName (Column1, Column2... ColumnN)
VALUES (value1,value2.......value n)
INSERT- DML Command
EXAMPLE- INSERT INTO employee (empId,name,salary)
VALUES
(1, ”Raj” ,1200),
(2, ”Rahul” ,1100),
(3, ”Riti” ,1100)

NOTE- We can directly insert the values in the table without specifying
the attributes, but in this way data is stored in a sequential manner.

EXAMPLE- INSERT INTO employee

VALUES (1, ”Raj” ,1200),
(2, ”Rahul” ,1100),
(3, ”Riti” ,1100);

07 To See the tables of a Database COMMAND- SHOW TABLES;

NOTE - It helps us to see all the tables in a given database.

08 To see all the values in table To see specific values of a column:

SELECT empId FROM employee;

To see all the values or the entire table

SELECT * FROM employee;
 DATATYPE DEFINITION

Numeric

INT Used for storing whole numbers without decimal points. (-2,147,483,648 to 2,147,483,647 (signed
integer)

BIGINT Used for storing large whole numbers. (-9,223,372,036,854,775,808 to 9,223,372,036,854,775,807)

FLOAT Used for storing decimal numbers. (4-byte)

DOUBLE Used for storing decimal numbers. (8-byte) more precised than float

DECIMAL(p, s) Used for exact numeric representation. p is the precision and s is the scale

IMPORTANT By default all the numeric datatypes can have negative as well as positive values. Thisrestrict the range
so if we know there is only +ve values which are stored we use UNSIGNED attribute (0-255). for eg-
salary can never be in negative or age

Command : CREATE TABLE example1 ( id INT UNSIGNED );

Character Data
Types

CHAR(n) Fixed-length character strings can be stored. (0-255)

VARCHAR(n) Variable-length character strings can be stored.(0-255)

TEXT Variable-length character string with no specified limit.

Date & Time Data

Types

DATE- Used for storing date values. (YYYY-MM-DD)

TIME Used for storing time values. (hh:mm:ss)

DATETIME/TIMES Used for storing date and time values. (yyyy-mm-dd hh:mm:ss)
TAMP
Command :
CREATE TABLE example1
( createdTs TIMESTAMP
);

Boolean Data BOOLEAN- Used to store a true or false value.

Types
Command :

CREATE TABLE example1

( isActive BOOLEAN
);
 Binary Data
Types

BINARY- Used for fixed-length binary data.

VARBINARY Used for storing variable-length binary data

BLOB (Binary Used for storing large amounts of binary data.(var len)
Large Object)
Command :

CREATE TABLE document ( data BLOB );

01 Unique constraint: CREATE TABLE example1 (

phoneNbr INT UNIQUE
);

02 Not null constraint: CREATE TABLE example1 (

address VARCHAR(50) NOT NULL
);

03 Check constraint: CREATE TABLE example1 (

age INT CHECK (age >= 18)
);

04 Default constraint: CREATE TABLE example1 (

enrolled VARCHAR(20) DEFAULT ' no '
);

Primary Key :
A primary key is a key which uniquely identifies each record in a table. It ensures that each
tuple or record can be uniquely identified within the table. It is always Unique + Not null
Foreign Key :
A foreign key is a field in a table that refers to the primary key in another table. It
establishes a relationship between two tables.

Referenced table - Table having primary key (pk)

Referencing table- Table having foreign key(fk)

New table with foreign key CREATE TABLE course (

course_id INT PRIMARY KEY,
Id INT,
FOREIGN KEY (Id) REFERENCES baseTableName(Id)
);

To make foreign key in existing table

NOTE- Foreign key helps to perform operations related to the parent table, such as joining
tables or ensuring referential integrity

REFERENTIAL INTEGRITY- Any of the updates that happen in the base table that need to
be done in the child table is called referential integrity. It helps us to maintain data integrity.

CASCADING IN FOREIGN KEY

Cascading is a set of rules which dictate what actions should be taken automatically when
a referenced row in the parent table is modified or deleted.
 COMMAND USE EXAMPLE
CASCADE If a row in the parent table is updated or
deleted, all related rows in the child table will
be automatically updated or deleted.

.SET NULL If a row in the parent table is updated or

deleted, all corresponding foreign key values
in the child table will be set to NULL.

RESTRICT or NO Blocks the modification or deletion of a

ACTION: referenced row in the parent table if related
rows exist in the child table, thus maintaining
referential integrity.

ON DELETE The ON DELETE CASCADE clause indicates CREATE TABLE childtableName ( childId INT
CASCADE that if a row in the parent table (parent_table) PRIMARY KEY, baseId INT, FOREIGN KEY
is deleted, all corresponding rows in the child (baseId) REFERENCES baseTableName(baseId)
table (child_table) will be automatically ON DELETE CASCADE );
deleted as well

ON UPDATE The ON UPDATE CASCADE clause CREATE TABLE childtableName ( childId INT
CASCADE indicates that if a row in the parent table PRIMARY KEY, baseId INT, FOREIGN KEY
(parent_table) is updated, all corresponding (baseId) REFERENCES parenttableName(childId)
rows in the child table (child_table) will be ON UPDATE CASCADE );
automatically updated as well.
 UPDATE AND DELETE COMMANDS
UPDATE The UPDATE command in SQL is used to modify existing records UPDATE table_name SET columnName1=
in a table. value1(to be set) , columnName2 =value2(to
be set) WHERE condition;

If you get a safe mode error while executing queries run this query

SET SQL_SAFE_UPDATES=0;

DELETE The DELETE command in SQL is used to remove records from a DELETE FROM table_name
table (Basically it delete the entire raw) WHERE condition;

OPERATOR IN SQL
Arithmetic Operators ●​ addition (+) SELECT * FROM employee WHERE age+1
●​ subtraction (-) =60;
●​ multiplication (*)
●​ division (/)
●​ modulus (%) - Gives remender

Comparison ●​ equal to (=) SELECT * FROM employee WHERE age >

Operators ●​ not equal to (<> or !=) 20;
●​ greater than (>) less than (<)
●​ greater than or equal to (>=)
●​ less than or equal to (<=)

Logical Operators AND It combines two conditions and returns SELECT * FROM employee WHERE city=
true if both are true 'Pune ' AND age > 18;

OR It combines two conditions and returns : SELECT * FROM employee WHERE city=
true if either is true 'Pune ' OR age > 18;

NOT It reverses the result of a condition, : SELECT * FROM employee WHERE

returns true if the condition is false department NOT IN ('IT' , 'HR');

IN Operator IN(Checks if a value matches in a list of values) SELECT * FROM employee WHERE
department IN ('IT' , 'HR');

IS NULL / IS NOT IS NULL (checks for null values) , IS NOT SELECT * FROM employee WHERE
NULL Operators NULL(checks for not null values) department IS NOT NULL;

LIKE & Wildcard LIKE operator is used to search for a specified pattern 1.% (percent sign): It matches for any
Operators : in a column. It uses wildcard operators for matching sequence of zero or more characters.
patterns.
QUERY :
SELECT * FROM employee WHERE name
LIKE 'A%' ;

2. _ (underscore): It matches for any single

character.

QUERY :
 SELECT * FROM employee WHERE name
LIKE '_A%' ;

BETWEEN Operator Checks if a value is within a range of values. SELECT * FROM employee WHERE salary
: BETWEEN 1200 AND 1500;

AGGREGATE FUNCTIONS
Aggregate functions perform some operations on a set of rows and then returns a single
value summarising the data. These are used with SELECT statements to perform
calculations

COUNT() It counts the number of rows in a table or the number of Query :

non-null values in a column.
This counts how many things are in a list or a group. SELECT count(name) FROM employee

this will tell the number of employees in

a company

SUM() It calculates the sum of all values in a numeric column. Query :

SELECT SUM(salary) FROM employee

this will tell the total amount company is

paying to its employees

AVG() It computes the average of all values in a numeric column. Query :

It finds the average, or the " middle " number, of all the SELECT AVG(salary) FROM employee
numbers in a list.
this willl tell the avg amount company is
paying to its employees

MIN() It helps to find the smallest number in a list. Query :

SELECT MIN(salary) FROM employee

this willl tell the minimum salary

company is paying to its employees

MAX() It finds the maximum value in a column. Query :

SELECT MAX(salary) FROM employee

this willl tell the max salary company is

paying to its employees
 CLAUSE
CLAUSE-The sql clauses can help filter out the data according to the users' needs.

WHERE It filters the rows based on specified SELECT col1 col2 FROM tableName WHERE
conditions. condition;

LIMIT The LIMIT clause in SQL is used to restrict ex : SELECT * FROM employee LIMIT 2;
the number of rows returned by a query.
This query retrieves the first 2 rows from the
table.

ORDER BY It is used to sort the results in ascending or QUERY :

descending order. By default it returns the SELECT col1 , col2 FROM tableName ORDER
result in ascending order BY col1 (ASC/DESC), col2 (ASC/DESC)

ex : SELECT * FROM employee ORDER BY

salary DESC;

GROUP BY This is used to group rows that have the QUERY :

same values into together SELECT col1, aggregateFun(col2) AS avgsal
FROM tableName GROUP BY col1 ;
It helps to organise data into groups so that
you can do calculations, like finding totals or ex: SELECT department, AVG(salary) AS avgsal
averages, for each group FROM employee GROUP BY department;

Step 1- first it select the department

Step 2- group the department that have the
same value into together
Step 3- then perform an aggregative function on
group
Step 4 - then As keyword create an alice or
alternative name for column which is not the part
of physical column.and display the aggregate
group by value.

In the above example As or alice column name is

avgsal

HAVING ●​ It is used with the GROUP BY clause QUERY :

●​ The HAVING clause is just like
WHERE clause but the main SELECT col1, col2 aggregateFun(col3) FROM
difference is it works on aggregated tableName GROUP BY col1 col2 HAVING
data condition;
●​ It helps to filter groups based on
given con conditions. ex : SELECT department, AVG(salary) AS
avgsal FROM employe GROUP BY department
HAVING avgsal>1500;
 SQL JOINS

Joins are used to combine rows from two or more tables based on a related or shared or
common column between them.

JOINS ARE OF TWO TYPE

1.​ INNER JOIN

2.​ LEFT JOIN
3.​ RIGHT JOIN
4.​ FULL JOIN/OUTER JOIN
5.​ SELF JOIN
6.​ CROSS JOIN

INNER JOIN It helps us in getting the rows that have Query:

matching values in both tables, according to the SELECT columns FROM table1
given join condition INNER JOIN table2 ON
table1.colName = table2.colName;

To avoid the replicated column id

Left Join/Left It is used to fetch all the records from the left Query:
Outer Join table along with matched records from the right
table. If there are no matching records in the SELECT columns FROM table1
right table, NULL values are returned for the LEFT JOIN table2 ON
columns of the right table. table1.colName = table2.colName;
Right Join/ Right It is used to fetch all the records from the right Query:
Outer Join table along with matched records from the left
table. If there are no matching records in the left SELECT columns FROM table1
table, NULL values are returned for the columns RIGHT JOIN table2 ON
of the left table table1.colName = table2.colName;

Full Join/Full ●​ It returns the matching rows of both left and Query:
Outer Join: right table and also includes all rows from
both tables even if they don’t have matching SELECT columns FROM table1
rows LEFT JOIN table2 ON
table1.colName = table2.colName;
●​ It is based on the principal of union ( left +
right +common) UNION

●​ If there is no match, NULL values are SELECT columns FROM table1

returned for the columns of the missing table. RIGHT JOIN table2 ON
table1.colName = table2.colName;
●​ In MySQL, the syntax for a full join is different
compared to other SQL databases like
PostgreSQL or SQL Server.

●​ MySQL does not support the FULL JOIN

keyword directly. So we use a combination of
LEFT JOIN, RIGHT JOIN, and UNION to
achieve the result.

Cross Join: ●​ It combines each row of the first table with Query:
every row of the second table
SELECT * FROM table1 CROSS
●​ It is similar to the cartesian product JOIN table2;

●​ It results in a new table where the number of

rows is equal to the product of the number of
rows in each table. (m*n)
Self Join A self join in SQL is a type of join where a table Query:
is joined with itself. It is a type of inner join.
SELECT columns FROM table as t1
JOIN table as t2 ON t1.colName =
t2.colName

WAY WE NEED SELF JOIN? NOTE- join are basically used to

join two different table but in self join
E.g., an Employee table may have a we join a table with itself which may
SupervisorID column that points to the give an error so that ue use a AS
employee that is the boss of the current (aliases as s1 and s2)
employee. Which creates an alternative table
which is not a part of physical table

​
 EXCLUSIVE JOIN IN SQL
Exclusive joins are used when we want to retrieve data from two tables excluding matched
rows. They are a part of outer joins or full outer join.
Left Exclusive JOIN When we retrieve records from the left table Query:
excluding the ones matching in both the left
and right table . SELECT columns FROM table1
LEFT JOIN table2 ON
table1.colName = table2.colName;
WHERE table2.colName IS NULL;

Here we are using WHERE

ORDERS.ID IS NULL exclude all
the value which are common in both
the table

Right Exclusive JOIN When we retrieve records from the right Query:
table excluding the ones matching in both
the left and right table . SELECT columns FROM table1
RIGHT JOIN table2 ON
table1.colName = table2.colName;
WHERE table1.colName IS NULL;

-- RIGHT EXCLUSIV JOIN

SELECT * FROM SHOP

RIGHT JOIN ORDERS
ON SHOP.SID = ORDERS.OID
WHERE SHOP.SID IS NULL;

Full Exclusive JOIN When we retrieve records from the right Query:
table and left table excluding the ones
matching in both left and right table SELECT columns FROM table1
LEFT JOIN table2 ON
table1.colName = table2.colName;
WHERE table2.colName IS NULL;

UNION

SELECT columns FROM table1

RIGHT JOIN table2 ON
table1.colName = table2.colName;
WHERE table1.colName IS NULL;
 DIFFERENCE BETWEEN UNION AND UNION ALL OPERATOR

UNION UNION ALL

Union operator in sql combines the rows of two tables UNION operator in SQL is used to combine the results
in a single set and gives unique rows by removing of two or more SELECT queries into a single result set
duplicate rows. and gives all rows by not removing duplicate rows.

QUERY: QUERY:

SELECT columns SELECT columns

FROM table1 FROM table1
UNION SELECT columns UNION ALL SELECT columns
FROM table2 FROM table2;
 SQL Subqueries/Nested queries
SQL subquery is a query nested within another SQL statement. Whenever we want to retrieve data based on the
result of another query we use nested queries.

How can we use Subqueries?

Subqueries can be used with clauses such as SELECT,WHERE,FROM, INSERT, UPDATE, or

DELETE to perform complex data retrieval.

WHERE CLAUSE

Subqueries can be used with WHERE clause to filter data based on conditions.

QUERY:

SELECT * FROM tableName

WHERE column name operator (subquery);

QUESTION - Find all the employees who have salary greater than the min salary

STEP-1- find the min salary of an employee

STEP-2- Find employee having salary greater than min salary

FROM CLAUSE
QUESTION - Find the employees who is having age greater than min_age

STEP-1 - first we have to fin min_age

QUERY:
SELECT min(age) AS min_age FROM employee;

STEP-2- then we find employee having age> min_age

QUERY:

SELECT emp.name
FROM employee emp, (subquery)
AS subquery
WHERE emp.age > subquery.min_age;
using subqueries in SELECT:

QUESTION :- Print the employees with the average age and age of employees

STEP -1 - Find the avg age

QUERY:
SELECT AVG(age) FROM employee

STEP -2 - Print the employee age and avg_age

QUERY:
SELECT (subquery)AS avg_age , age FROM employee;
 Nth Highest Salary

Steps to find the nth highest salary:

●​ Step 1: Select the column which you want to show the final result i.e salary.
●​ Step 2: Order the salary in descending order so that you have the max at the first.
●​ Step 3: Now the value of n could be 1,2,3....till n, so we have to make the query in
such a way so that whatever be the value of n it can provide the result.
●​ Step 4: So at the end of the query we will provide a LIMIT so that on the data set
which we have got after ordering the salary in descending order, we can fetch the nth
highest one.

QUERY:
SELECT DISTINCT Salary FROM // Distinct so that no value is repeated
tableName
ORDER BY Salary DESC LIMIT n-1, 1; //n is nth value which we want to calculate.
 Views In SQL

A view is a virtual table in SQL. It helps in providing a filtered view of data for security purposes.

Some time we in company we have to share some data to other and at the same time we have to
maintain data abstraction so that we use view command

It helps in Data Abstraction, Security and simplify complex queries.

QUERY:
CREATE VIEW viewName AS SELECT columns FROM baseTableName;

Columns - (Specify the columns to be included in the view)

 Stored Procedures
These are programs that can perform specific tasks based on the stored query. It is basically a collection of
pre-written SQL statements grouped together under a specific name.

Query:
(to create a procedure)
CREATE PROCEDURE procedureName()
BEGIN
Query
END;

Query:
(to call the procedure)
CALL procedureName();

WHAT IS DELIMITER - a delimiter is a special character used to separate statements within

a query, stored procedure, or script. It helps MySQL interpret and execute the statements
correctly. The default delimiter in MySQL is a semicolon (;)

Examples: Return the details of the order by id (Stored procedure with params)

Query 2:
CREATE PROCEDURE getAllOrderDetailsById(IN id int)
BEGIN
SELECT *FROM Orders WHERE id = id;
END;