INTRODUCTION

– Database is basically a computer based record keeping

system, in which data are interrelated to each other so that it
can be used by various applications.

– Some of the Popular database software are –

OSS – MySQL, PostgreSQL, MariaDB, MongoDB, SQLite

Proprietary – Oracle, MS Access, Microsoft SQL Server

PURPOSE/need OF DBMS
– Database Management System (DBMS) is a software whose
purpose is to store databases, maintaining databases and
using databases.
– Its prime purpose is to perform operations on databases and
to provide data when required.
– DBMS reduces Data Redundancy.
– It improves data security.
– It stores data in organized and Integrated form.
– Data remains error free.
– Data is available as and when required.
– Database follows a standard.
 ADVANTAGES DISADVANTAGES
Reduce Data Redundancy Complexity
Data Consistency Size
More Information from the Cost of DBMS
same amount of data
Shared Data Additional Hardware Cost
Improved Data Integrity Cost of Conversion
Improved Security Performance
Enforcement of Standards Higher impact of Failure
Economy of scale Data Damage
 Relational Database Model
– In Relational Data model, data remains in the form of tables.
– A table is a combination of rows and columns which is also
known as Relation .
– In a table, a row shows relationship between values. A table is
a collection of this relationship.
– Imagine a database has three tables- Suppliers, Items and
Shipments :
Suppliers (SuppNo., Supp_name, Status, City)
Items (ItemNo., Item_name, Price)
Colored
Shipments (SuppNo., ItemNo., Qty_Supplied) columns are
primary keys.
Relational Database Model
 Components Of a Relation
Fields
Primary Key

Record

Data Item

Byte: A group of 8 bits used to store a charater is known as byte.

Data Item: Smallest unit of data.
Record: It is a complete information composed of data item.
Table: A table is a collection of logical records.
 Relational Model Terminology
– Relational Model was given by E. F. Codd of IBM.
Terminology of Relation Model is-
– Relation: Basically a relation is a table. Its a collection of data
in rows and columns. A relation has following features-
• In a table, data in one column should be of same data type. Different
columns can have data of different data types.

• The value for each row and each column should be stomic. A column in
a row can not have multiple values.

• In a relation, each row is distinct. Any two rows can ot have exactly
same data.

• There is no specific order of rows in a relation.

• In a Relation, there is no specific order of columns.

 TERMINOLOGY
Relations: In relational data model, the data is organised into tables(in the
form of rows and columns) called relations.
Domain: A domain is the sets of values from which the actual values appearing
in a given column are drawn.
Tuple : A row (Record) in a relation (Table).
Attribute: A column in a relation (Table).
Degree : It is a number of attributes or columns in a relation (table).
Cardinality : It is a number of tuples or records in a relation.
Primary Key: A column or set of columns that uniquely identifies a row within
a table is called primary key.
Candidate Key: Candidate keys are set of fields (columns with unique values) in
the relation that are eligible to act as a primary key.
Alternate Key: It is a candidate key that is not the primary key.
Foreign Key: A non-key attribute, whose values are derived from the primary
key of some other (Base) table, is known as FOREIGN KEY in its current
table.
DDL: Data Definition/Description Language (DDL). E.g. Create Table, Alter Table
DML: Data Manipulation Language (DML). Insert, Update, Delete.
TCL: Transaction Control Language (TCL). Commit, Rollback, Savepoint
 CLASSIFICATION OF SQL COMMANDS
• DDL: Data Definition/Description Language (DDL).
– CREATE TABLE, ALTER TABLE, CREATE VIEW etc
• DML: Data Manipulation Language (DML).
– INSERT INTO, UPDATE, DELETE

• TCL: Transaction Control Language (TCL).

– COMMIT, ROLLBACK, SAVEPOINT

• SESSION CONTROL LANGUAGE.

– ALTER SESSION

• SYSTEM CONTROL LANGUAGE.

– ALTER SYSTEM
 VIEWS
– A View is a Virtual table which is based on some or specific
data of a table.
– Command for creation of a View is-
 KEYS
– Referential Integrity: Referential integrity is a system of rules
which is used by a DBMS to ensure that there is a valid
relationship between related tables or not. Refrential integrity is
possible only when follwoing conditions gets completed-
– Primary key should have unique index.
– Related fields should have same datatype.
– All tables should be of same database.
– Primary key of Master table should have a refrence with forgien
key of transaction table.
– Entry of that record in transaction table is not possible whose
forgien key value does not exist in primary key of master table.
– Deletion of that record from primary key table is not possible
which has a related record in foreign key table.
– Change in related records of primary key is not possible.
 MySQL
– MySQL is an open source Relational Database Management
System (RDBMS) which makes use of SQL (Structured Query
Language).
– It can be downloaded from [Link].
– In MySQL, information is stored in the form of tables.
– A MySQL database can have multiple tables and thousands of
records simultaneously.
– It is a better option to store fast, reliable and big amount of data.
– MySQL was developed by MySQL AB company which is now a
part of Sun Microsystems.
– SERVER : which responds to the requests of clients.
– CLIENTS : these are the programs which are attached to
database server and send requests to server.
 MySQL Features
– Fast Speed
– Easy to use.
– Free of cost.
– Support of SQL.
– Portability.
– Various Data types.
– Secure.
– Can handle large data (Scalability and limits).
– Connectivity : uses various protocols to get connected with
clients.
– Localization : server can send error messages to clients in
different laguages.
– Clients and Tools. It provides various client and utility programs.
Processing Capabilties of SQL
1. Data Definition Language (DDL)

2. Interactive Data Manipulation Language(DML)

3. Embedded Data Manipulation Language: these are developed to be

used in some programming languages

4. View Definition

5. Authorization

6. Integrity

7. Transaction Control
 Data Definition Language (DDL)
– Command under this category are used to create or modify
scheme of database. It is used to create data dictionary.
– Data Dictionary is a kind of metadata means Data about Data. A
standard DDL should have following functions-
– It should identify the types of data division.
– It should give a unique name of each data item.
– It should specify the proper data type.
– It may define the length of data items.
– It may define the range of values of Data items.
– It may specify means of checking for errors.
– It may specify privacy locks for preventing unauthorized reading
or modification of the data.
 EMP
EID NAME POST SAL DEPTNO
A101 Ashutosh Manager 20000 20
B202 Naveen Accountant 10000 10
C301 Ravi Clerk 9000 30
D405 Kamal Salesman 8000 20
E505 Anil Salesman 11000 10
F612 Ashish Manager 25000 null
INSERT INTO Emp char
VALUES ( ‘F612’ , ‘Ashish’, ‘Manager’, 25000) ; varchar
 DATA TYPE – SQL
• int
• float
• varchar
• char
• date
 DDL Commands
Following commands are under this category-
– Create, alter and drop schema Objects
• Create table, create view, create database,
• Alter Table
• Drop Table
• Drop View
• Create Index
• Alter Index
– Grant and Revoke privileges and rolls
• Grant
• Revoke
– Maintenance Commands
• Analyze Table
• Check Table
• Restore Table etc
 DDL Commands
Following commands are under this category-
– Create, alter and drop schema Objects
• Create table, create view, create database,
• Alter Table
• Drop Table
• Drop View
• Create Index
• Alter Index
– Grant and Revoke privileges and rolls
• Grant
• Revoke
– Maintenance Commands
• Analyze Table
• Check Table
• Restore Table etc
 DML Commands
– DML (Data Manipulation Language) is a kind of language used to
access data, insert data and delete data from a data model.
– Data manipulation means-
• Accessing the stored data from a Database.
• Insertion of new information into the Database.
• Deletion of information from the Database.
• modification of information in the Database.
– DMLs are basically of two types-
• Procedural : specifies what data is needed and how to get
it.
• Non- Procedural: specifies what data is needed without
specifying how to get it.
 TCL Commands
– A transaction is one complete unit of work for ex- withdrawl of
2000 Rs. From a bank Account.
– Following commands are used to successfully complete a
transaction-

1. COMMIT : it makes all the changes permanent.

2. ROLLBACK : undoes all the changes.
3. SAVEPOINT : it marks a point upto which all earlier statments have
been successfully completed.

4. SET TRANSACTION : it establishes properties for the current

transactions.
• CREATE TABLE • ALTER TABLE :
• INSERT INTO – ADD
• SELECT : – MODIFY
– DISTINCT – DROP
– WHERE clause • CREATE VIEW
• BETWEEN • DROP TABLE
• IN
• LIKE • DROP VIEW
• ORDER BY • JOIN
• AGGREGATE FUCNTION
• GROUP BY
• UPDATE
• DELETE
• OCTOBER - 31 days
– Holidays ( 4+4)
• November - 30
– Inspection, Preboard,
• december
• Open MySQL
• Enter password, if it is having, else press ENTER
PW - apsayo
• CREATE DATABASE (Folder)
MySQL > CREATE DATABASE 12ip2020;
• OPEN DATABASE
MySQL > USE 12ip2020;
• SEE LIST OF DATABASES
– MySQL > SHOW databases;
• SEE LIST OF TABLES
– MySQL > SHOW TABLES;
• SEE STRUCTURE OF TABLE
– MySQL > DESCRIBE Emp;
• CREATE TABLE
• INSERT RECORDS/TUPLES
 CONTSTRAINTS – Table, Col :
Apply security/checks on columns
• PRIMARY KEY – Stopped entering
Duplicate values in col.
• UNIQUE – same as above
• DEFAULT – Allows to store assigned value in
the column, if skipped at the time of entering
record.
• CHECK – restrict entry in given range.
• FOREIGN KEY – for JOIN
• NOT NULL–Not allowed NULL value to be entered.
 CREATE TABLE
MySQL > CREATE TABLE Student
( Roll int NOT NULL PRIMARY KEY,
Name varchar(20) NOT NULL,
Class int(4) DEFAULT 11,
Marks float(5,2) CHECK Marks
between 0 AND 100,
DOB date
);
• Char(10)
A K A S H

R A V I

• Varchar(10)
A K A S H

R A V I
 INSERT INTO (Enter Record into
given table)
MySQL > INSERT INTO Student
VALUES (1, ‘Abhinav’, 11, 89.50, ‘2007-4-7’) ;
MySQL > INSERT INTO Student
VALUES (1, ‘Abhay’, 12, 99.50, ‘2009-11-08’) ;
QN. Insert following values in the given table.
7, ‘Kartikeya’,96.5
INSERT INTO Student(Roll,Name,Marks)
VALUES (7, ‘Kartikeya’, 96.5) ;
 SELECT (DISPLAY records)
Syntax :
SELECT col_list
FROM table_nm;
Qn.
1. Display all records from table STUDENT.
2. Display entire table.
3. List all records from given table STUDENT.
SELECT Roll, Name, Class, Marks
FROM Student;
• Used to ELAMINATE (Remove) duplicate values
being displayed from the output.
Syntax SELECT DISTINCT col
FROM table_name ;
Qn. Display all classes from table student.
OR
Qn. Display each class from table student.
Ans. SELECT DISTINCT class
FROM Student ;
WHERE clause (FILTER records)
Syntax :
SELECT col_list
FROM table_nm
WHERE Condition ;
Qn.
1. Display records of class 12 from table STUDENT.
SELECT Roll, Name, Class, Marks
FROM Student
WHERE class = 12 ;
 SELECT col_list
FROM table_nm
WHERE Condition1 logi. Op. cond.2 ;
• Display records of class 12 from table STUDENT
who are getting marks more than 60.
SELECT *
FROM Student
WHERE class = 12 AND marks > 60 ;
 BETWEEN–based on NUMERIC range,
INT, FLOAT, DATE, CHAR
Syntax :
SELECT col_list
FROM table_nm
WHERE Col BETWEEN Lvalue AND Uvalue ;
Qn.
1. Display records of students scoring marks in range 60-90.
2. Display records of students other than above range..
SELECT *
FROM Student
WHERE Marks BETWEEN 60 AND 90 ;
 IN – based on list of exact values in same col
Syntax :
SELECT col_list
FROM table_nm
WHERE Col IN (value1, value2….);
Qn.
1. Display records of class XI and XII.
2. Display records of board classes.
3. Display name of students scoring marks 60,70 & 80.
SELECT *
FROM Student
WHERE class IN ( ‘XI’ , ‘XII’) ;
• Display records of students studying in board
classes scoring marks in range 60-90.
SELECT *
FROM student
WHERE CLASS IN (‘X’, ‘XII’) AND
Marks BETWEEN 60 AND 90 ;
WHERE CLASS IN (‘X’, ‘XII’) AND
Name = ‘Shristi’ ;
WHERE name = ‘Shristi’ and
(CLASS = ‘X’ OR CLASS = ‘XII’ ) ;
 LIKE–based on PATTERN matching.
WILD CARD - % (multiple chars),
_ (Single Chars)
Syntax :
SELECT col_list
FROM table_nm
WHERE Col LIKE ‘ % A _ _ ’ ;
Qn.
1. Display records of students whose name start with letter A.
SELECT *
FROM Student
WHERE Name LIKE ‘A %’ ; ‘25/12/2020’
 LIKE
Wild Card Character : %, _
SELECT * SELECT *
FROM student FROM student
WHERE Name LIKE ‘A%’ ; WHERE Name LIKE ‘_A%’ ;
SELECT * SELECT *
FROM student FROM student
WHERE Name LIKE ‘%A’ ; WHERE Name LIKE ‘%A_’ ;
SELECT * SELECT *
FROM student FROM student
WHERE Name LIKE ‘%A%’ ; WHERE Name LIKE
‘_ _B _ _ H %’ ;
More question on LIKE command ;
Display records of students whose name –
1. Ends with letter A.
2. Containing letter A.
3. Second letter A.
4. Second last letter A.
5. 3rd letter U and 6th letter V.
6. Having 4 letters name.
Display records of employees having name –
1. First letter as ‘A’.
2. Last letter as ‘A’
3. Second last letter as ‘A’.
4. Containing letter ‘A’.
5. Second and seventh letters are ‘A’ and ‘B’
respectively.
6. Having five digits in salary.
7. Containing vowels
ORDER BY – Arranging records in
Ascending/Descending order in OUTPUT
Syntax :
SELECT col_list FROM table_nm
[ WHERE cond ]
ORDER BY Col ASC/DESC ; (ASC is DEFAULT)
Points to note :
1. Order will be applied on output only not in table.
2. It will be written at last of SELECT command.
3. ASC is a default order, if skipped in query order will be in
ascending
Qn.1. Display records of students in alphabetical order.
SELECT *
FROM Student
ORDER BY Name ASC ;
 ORDER BY
1. Display records of students in descending order by
Marks.
2. Display records in DESC order by Marks of class XI.
3. Display records in alphabetical order by name.
4. Display names in descending order by salary.
5. Display name and job in ascending order by salary.
6. Display records of managers in descending order
by DeptNo.
7. Display records of salesman getting salary from
8000 to 12000.
8. Display records in alphabetical order by name and
descending order by salary.
 AGGREGATE FUNCTIONS
• SUM() Syntax :
SELECT Agg_Func(Col)
• AVG()
FROM table_name
• MAX()
[Where cond];
• MIN() Points to remember :
1. It will be applied on SINGLE col at a time.
• COUNT() DATE value.
2. It will return SINGLE NUMERIC /CHAR /

3. Aggregate Function will never be used

• COUNT(*) with WHERE clause.
4. COUNT(col ) without * will not count
NULL value
 AGGREGATE FUNCTIONS
1. Display highest salary.
2. Display maximum sal of managers.
3. Display average salary of those employees
getting salary more then 5000.
4. Display minimum salary of clerks from
DeptNo 10.
5. Count strength of employees working in
DeptNo 30.
6. Count those records having non null values in
Job
1. Display highest salary.
2. Display maximum sal of managers.
3. Display name of TOPPER from table student.

SELECT MAX(Sal)
FROM Emp ;

SELECT MAX(Sal)
FROM Emp
WHERE Post = ‘Manager’ ;
 GROUP BY
Will be applied only on that COL
containing repeated values.
Points to remember :
Syntax : 1. GROUP BY command will be applied
only on that column in which almost all
SELECT COL, Agg_Func() values are repeated.
FROM table_Name 2. In col_list of SELECT only that column
GROUP BY COL and aggregate_function can be written.
3. GROUP BY statement will only be
[HAVING cond. ] written after FROM table_name. (3rd line)
4. HAVING clause will be used for condition
and not WHERE cluase.
5. Aggregate Function can also be used in
condition with HAVING.
 GROUP BY
1. Display all/each jobs. (Display all/each class)
2. Display all/each DeptNo.
3. Display Average salary of each job.
4. List maximum salary and no. of employees of
each DeptNo.
5. Display sum of salary of those jobs whose name
start with letter ‘S’ .
6. Display minimum salary of those Dept whose
average salary is more than 8000.
7. Display sum of salary of those jobs whose
minimum salary is greater than employee
Akshay Kumar.
1. Display all/each jobs. (Display all/each class)
2. Display Average salary of each job.
3. List maximum salary and no. of employees of each DeptNo.
4. Display sum of salary of those jobs whose name start with letter
‘v’ .
ANSWER :
1. SELECT Class 3. SELECT MIN(Marks)
FROM Student FROM Student
GROUP BY Class ;
GROUP BY Class ;
4. SELECT SUM(Marks)
2. SELECT AVG(Marks) FROM Student
FROM Student GROUP BY Class
GROUP BY Class ; HAVING class LIKE ‘v%’;
 DML
(Data Manipulation Language)

•INSERT INTO
•UPDATE
•DELETE
 INSERT INTO
• Syntax :
INSERT INTO table_nm [ ( col_list) ]
VALUES (value1, value2, ….. , value N);
Question :
• Enter a new record in table EMP.
• Add a new record with values for columns
empid, name and salary only.
• Insert a new tuple leaving default constraint
column.
 UPDATE – edit tuple
• Syntax :
UPDATE table_nm
SET col = value/ expression
[ WHERE < cond > ] ;
Question :
1. Modify salary by adding 1000.
2. Increase salary of Clerk by 20%.
3. Change DeptNo from 20 to 10 for those managers
getting salary greater than 10000.
4. Increase salary of Anil by 10% who is in DeptNo 30
but getting salary in the 5000 to 10000.
5. Edit salary of Salesman Vijay by increasing 15% also
promote him to manager post.
 DELETE – remove tuple
• Syntax :
DELETE
FROM table_nm
[ WHERE < cond > ] ;
Question :
1. Make table EMP empty.
2. Remove records of those salesman whose
salary less than 5000.
3. Delete records of those employees having
NULL value in Post column.
 DDL
(Data Definition Language)
•CREATE TABLE
•CREATE VIEW
•ALTER TABLE
•DROP TABLE
•DROP VIEW
 ALTER TABLE
• Syntax :
ALTER TABLE table_nm
ADD col data_type size /
MODIFY col data_type size /
DROP col ;
Question :
1. Insert a new column COMM with data type
FLOAT and size 7, 2 .
2. Increase width of column NAME form 20 to 30
character.
3. Delete column COMM.
 VIEW
It is a virtual table which has no existence in
database, its data can be derived from base table.
• Syntax :
CREATE VIEW view_nm (col_list)
AS
( SELECT col_list
FROM table_nm
WHERE cond );
Question :
1. Create view EVIEW having Name, Post and Salary.
2. Create view Emp_Mgr having records of all managers.
3. Create view Emp_Bonus including columns Name, Job
and Bonus calculated as 10% of the salary.
 CLASS TEST : 19-08-2025
1. Write SQL command for the following.
– See structure of a table.
– Display unique value from a column.
– Display pattern of a condition
2. Write SQL query on STUDENT table
(Roll, Name, Class, Marks)
– Insert a new record with roll, name and marks only.
– Display all classes.
– Display name of students from class 12.
– Display records scoring marks more than 60.
 ANSWER
1. (a) DESCRIBE
(b) DISTINCT
(c) LIKE

2. (a) INSERT INTO Student (Roll, Name, Marks)

VALUES(8, ‘Sachin’, 99);
(b) SELECT DISTICT Class From Student ;
(c) SELECT Name FROM Student
WHERE Class = 12;
(d) SELECT * FROM Student
WHERE Marks > 60 ;
 CLASS TEST : 20-08-2025
1. Write SQL command/keyword for the following.
– To open database.
– To filter records.
– To display records based on different values from same
column
2. Write SQL query on EMP table
(EID, Name, Post, Salary)
– Insert a new record with EID, name and Post only.
– Display all posts.
– Display name of managers or analysts.
– Display records getting salary in the range 10000 –
50000.
 ANSWER (20-08-2025)
1. (a) USE
(b) WHERE
(c) IN
2. (a) INSERT INTO EMP (EID, Name, Post)
VALUES(18, ‘Suresh’, ‘Clerk’) ;
(b) SELECT DISTICT Post From EMP ;
(c) SELECT Name FROM EMP
WHERE Post IN (‘Manager’, ‘Analyst’) ;
(d) SELECT * FROM Emp
WHERE Salary BETWEEN 10000 and 50000 ;
 CLASS TEST : 21-08-2025
1. Write SQL command/keyword for the following.
(a) To arrange records in an order.
(b) To ensure two given conditions should true for display.
(c) Pattern matches exactly 1 character in given condition.
(d) To ensure a column should not left blank in entry.
2. Write SQL query on BOOK table
(ISBN, Name, Author, Publication, Price)
(a) Display all Publications.
(b) Display records whose price in the range 100 – 500.
(c) Display Name of books whose publication having ‘Depot’.
(d) Display total and average price of ABC publication.
 ANSWER (21-08-2025)
1. (a) ORDER BY (b) AND
(c) Underscore ( _ ) (d) NOT NULL
2. (a) SELECT DISTICT Publication From book ;
(b) SELECT * FROM Book
WHERE Price BETWEEN 100 AND 500 ;
(c) SELECT Name FROM Book
WHERE LIKE Publication ‘%Depot%’ ;
(d) SELECT SUM(Price), AVG(Price)
From Book
Where Publication = ‘ABC’ ;
 CLASS TEST : 22-08-2025
1. Write SQL command/keyword for the following.
(a) To ensure value specified to be filled in entry of a record.
(b) To ensure either of given conditions true for display.
(c) Wild Card characters.
(d) To ensure no duplicate value entered in a column.
2. Write SQL query on LIBRARY table
(MemID, MName, Publication, DOI, DOR, Fine)
(a) Display all Publications.
(b) Display records whose Fine in the range 10 – 100.
(c) Display member name who returns book after 1 Aug 2025.
(d) Display total and average Fine of each publication.
 ANSWER (22-08-2025)
1. (a) DEFAULT/INSERTINTO (b) OR
(c) %, Underscore ( _ ) (d) Primary Key/Unique

2. (a) SELECT DISTICT Publication From Library ;

(b) SELECT * FROM Library

WHERE Fine BETWEEN 10 AND 100 ;

(c) SELECT MName FROM Library

WHERE DOR > ‘01-08-2025’ ;
(d) SELECT SUM(Fine), AVG(Fine)
From Library
GROUP BY Publication;