DATABASE MANAGEMENT SYSTEM

(using MySQL)

Classes XI & XII

Prepared by
Amit Kumar Jain
PGT(Computer Science)
K. V. No.4 Jaipur

P a g e 1 | 23 Amit Kumar Jain,PGT(CS), K.V. Sunderbani

DBMS (Database Management System)
Table : Employee
COLUMNS
ENAME DNAME JOB EMPNO HIREDATE LOC
ADAMS RESEARCH CLERK 7876 23-MAY-87 DALLAS
ALLEN SALES SALESMAN 7499 20-FEB-81 CHICAGO
BLAKE SALES MANAGER 7698 01-MAY-81 CHICAGO
ROWS CLARK ACCOUNTING MANAGER 7782 09-JUN-81 NEW YORK
FORD RESEARCH ANALYST 7902 03-DEC-81 DALLAS
JAMES SALES CLERK 7900 03-DEC-81 CHICAGO
JONES RESEARCH MANAGER 7566 02-APR-81 DALLAS
KING ACCOUNTING PRESIDENT 7839 17-NOV-81 NEW YORK
MARTIN SALES SALESMAN 7654 28-SEP-81 CHICAGO
MILLER ACCOUNTING CLERK 7934 23-JAN-82 NEW YORK
SCOTT RESEARCH ANALYST 7788 19-APR-87 DALLAS
SMITH RESEARCH CLERK 7369 17-DEC-80 DALLAS
TURNER SALES SALESMAN 7844 08-SEP-81 CHICAGO
WARD SALES SALESMAN 7521 22-FEB-81 CHICAGO

Table / Relation – Employee

Columns / Attributes / Fields – ENAME, DNAME, JOB, EMPNO,
HIREDATE, LOC
Rows / Records / Tuple – 14 records are here in the table

Degree – Number of Columns

Cardinality – Number of Rows
In Employee Table
Degree – 6
Cardinality – 14

P a g e 2 | 23 Amit Kumar Jain,PGT(CS), K.V. Sunderbani

CONSTRAINTS
Primary Key - It is a column or set of column which can uniquely identify each row
in a table but it does not accept NULL values.
E.g. – RollNo, EmpCode, CustomerNo, UID etc.

Unique Key - It is a column or set of column which can uniquely identify each row
in a table but it accept NULL values.

Foreign Key – It is a column in a table which is the primary key in another table.
CUSTOMER

ORDER
Customer_id

Here in the above example

Customer_id in table Order is a Foreign Key which is the primary key of table
Customer.

Candidate Key – There may be two or more attributes which can become the
primary key of table. These all are candidate keys.

Alternate Key – Out of all Candidate keys one will be selected as Primary key and
remaining will be known as Alternate keys.

P a g e 3 | 23 Amit Kumar Jain,PGT(CS), K.V. Sunderbani

CONSTRAINTS
NOT NULL – Not Null constraint does not accept Null values.
E.g. CREATE TABLE Customer
(SID integer NOT NULL,
Last_Name varchar(30) NOT NULL,
First_Name varchar(30));

CHECK - CHECK constraint ensures that all values in a column satisfy certain
conditions.
e.g. – CREATE TABLE Customer
(SID integer CHECK(SID>0),
Last_Name varchar(30),
First_Name varchar(30));

DEFAULT – Default constraint accept default value if no value assigned in the

column.
e.g. – CREATE TABLE Customer
( SID integer,
Last_Name varchar(30),
First_Name varchar(30) DEFAULT “Not Assigned”,
Price Float(5,2) DEFAULT 0
);

DATA TYPES
For Numbers – INT , INT(Value)
For Decimal Numbers – FLOAT(M,N)
For String/Text – CHAR(N), VARCHAR(N)
For Date - DATE

P a g e 4 | 23 Amit Kumar Jain,PGT(CS), K.V. Sunderbani

Differences between DDL and DML.
Sl.No. DDL DML
Data Definition Language Data Manipulation Language
It deals with structure of table. Like
1 It deals with data stored in rows.
Table Name , Columns
It is used to create, alter, drop table It is used to insert, update, delete
2
from database. the rows from table.
E.g. CREATE TABLE, ALTER E.g. INSERT , UPDATE, DELETE,
3
TABLE, DROP TABLE etd. SELECT

Differences between CHAR and VARCHAR data type.

Sl.No. CHAR VARCHAR
1 It accept fixed length text It accept variable length text
2 Memory wastage is high. It does not waste of memory
E.g. NAME CHAR(20)
If, data stored in NAME column is NAME VARCHAR(20)
“RAM” If, data stored in NAME column is
2 It will occupy memory space of 20 “RAM”
characters even if ,we are having less Then, It will occupy space of 3
number of characters in text. characters only.

Differences between PRIMARY KEY and UNIQUE KEY

Sl.No. Primary Key Unique Key
It is a column or set of column
It is a column or set of column which
which can uniquely identify each
can uniquely identify each row in a
1 row in a table.
table.
But it accept NULL values
But it does not accept NULL values
E.g.
E.g.
RollNo Name Class
RollNo Name Class
1 Raj XI
1 Raj XI
2 Seema XII
2 Seema XII
Null Suhana XI
3 NULL XI
2 4 Kavita XI
4 Kavita XI
Null Namita XI
5 Namita XI
Here RollNo is Unique key it has
Here RollNo is Primary Key because it
unique value for each row and also
has unique value for each row
accepts Null value.

P a g e 5 | 23 Amit Kumar Jain,PGT(CS), K.V. Sunderbani

Differences Degree and Cardinality
Sl.No. Degree Cardinality
Number of rows in a table is known
Number of Columns in a table is
1 as cardinality.
known as degree
E.g. E.g.
RollNo Name Class RollNo Name Class
1 Raj XI 1 Raj XI
2 Seema XII 2 Seema XII
2 3 NULL XI 3 Null XI
4 Kavita XI 4 Kavita XI
5 Namita XI 5 Namita XI

Degree = 3 Cardinality=5

Differences ALTER and UPDATE

Sl.No. Alter Update

1 It is a DDL Command. It is a DML command.
It is used to add, modify, remove the It is used to modify the data stored
2
column in rows
E.g. UPDATE Student
E.g. ALTER TABLE Student
SET Class=”XII”
ADD Marks INT;
WHERE Name=”Kavita”;

Differences DELETE and DROP

Sl.No. Drop Delete

1 It is a DDL Command. It is a DML command.
It is used to remove table from It is used to remove rows/records
2
database. from table.
E.g. DELETE FROM Customer
E.g. DROP TABLE Student;
WHERE Name is NULL;

P a g e 6 | 23 Amit Kumar Jain,PGT(CS), K.V. Sunderbani

SQL

DDL COMMANDS
1. CREATE TABLE
SYNTAX :
CREATE TABLE Table_Name
(
Col_Name_1 DataType Constraints,
Col_Name_2 DataType Constraints,
Col_Name_3 DataType Constraints,
.
.
Col_Name_n DataType Constraints
);

e.g. :- Create table companies data are as follows…

cnum integer Primary Key
cname character(5) Not Null
city Varchar(7)
stdcode character(5)

Ans.:- CREATE TABLE companies

( cnum Cnt Primary Key,
cname Char(5),
city Varchar(7),
stdcode Char(5)
);

NOTE

Constraints are used as per requirement -

Primary/Foreign/Unique Keys/
Default/Check/Not Null

P a g e 7 | 23 Amit Kumar Jain,PGT(CS), K.V. Sunderbani

2. ALTER TABLE
Whenever the definition of existing table need to be changed the ALTER command is
used to modify the table. ALTER Command works on Columns.

SYNTAX:-
ALTER TABLE Table_name
ADD/MODIFY/DROP(column1_name datatype(size),
column2_name datatype(size),
…………………………………
columni_name datatype(size)
);

e.g. 1:- Add a column stdcode char(7) in table companies.

Ans.:- ALTER TABLE companies

ADD (stdcode char(7));
e.g. 2:- Modify the column stdcode varchar(7) in table companies.

Ans.:- ALTER TABLE companies

MODIFY (stdcode Varchar(7));
e.g. 3:- Remove the column stdcode in table companies.

Ans.:- ALTER TABLE companies

DROP stdcode;

3. DROP TABLE

SYNTAX:-
DROP TABLE Table_Name ;
e.g. :- drop table ‘companies’.

Ans.:-DROP TABLE companies ;

P a g e 8 | 23 Amit Kumar Jain,PGT(CS), K.V. Sunderbani

4. INDEX
An Index is a data structure maintained by the database the helps it find
records within a table more quickly. An index stores sorted/ordered values
within the index field and their location in the actual table.Indexes are used
to improve search time and increase a database performance.
Advantages:-
1. Data retrieve is much faster.
2. Indexes are very useful for sorting purposes.
3. UNIQUE INDEXES guarantee uniquely identifiable records in the
database.

Disadvantages:-
1. With indexes, the performances on Insert, Update, delete
decreases.
2. Indexes consumes storage space and this increases with number of
attributes and their length.

SYNTAX :-
CREATE TABLE Table_Name
( Col1 DataType [NOT NULL/PRIMARY KEY],
Col2 DataType [NOT NULL/PRIMARY KEY],
.
.
INDEX Index_Name (Index_Col(Size) [ASC/DESC],
.
.
);
CREATE INDEX Index_Name ON TableName (Col1 [ASC/DESC],
Col2 [ASC/DESC],…….);

Some Other Commands on Index

SHOW INDEXES FROM TableName;

DROP INDEX Index_Name ON TableName;

ALTER TABLE TableName

RENAME INDEX index_name TO new_index_name;

P a g e 9 | 23 Amit Kumar Jain,PGT(CS), K.V. Sunderbani

5. CREATING VIEW
VIEW :-
It is a temporary table derived from another table.
It is used to increase understandability.
Syntax:- CREATE VIEW View_Name
As (Select Query);
Ques -Create view CODE containing CITY & STDCODE.
Ans.
CREATE VIEW CODE
AS (SELECT CITY,STDCODE
FROM CMPANIES);

P a g e 10 | 23 Amit Kumar Jain,PGT(CS), K.V. Sunderbani

DML COMMANDS
INSERT
This command is used to add/insert data in the table.
SYNTAX:::-
INSERT INTO Table_Name
VALUES(value1,value2,…………,valuei);

1. Adding data for all columns

e.g. :- insert the following data in table ‘companies’
1001,BPL,DELHI,9413921030

Ans.:- INSERT INTO companies

VALUES(1001,”BPL”,”DELHI”,”9413921030”);
2. Inserting Column specific data
e.g. :- Add the following data in table ‘companies’ for the column cnum and cname
1002,LG

Ans.:- INSERT INTO companies(cnum,cname)

VALUES(1002,”LG”);

UPDATE
Two clause UPDATE & SET are used for changing the values stored in any cell.
SYNTAX:::-
UPDATE Table_name
SET column_name = value_expression
[WHERE condition];

e.g. 1:- update all companies with stdcode 011.

Ans.:- UPDATE companies

SET stdcode = ‘011’;
e.g. 2 :- Change the stdcode to 022 for city Mumbai.
Ans.:- UPDATE companies
SET stdcode = ‘022’
WHERE city='Mumbai;
e.g. 3 :- Modify the stdcode to 011,cname to sony and city to delhi for cnum 1002.
Ans.:- UPDATE companies
SET cname=‘Sony’ , city = ‘Delhi’ , stdcode = ‘011’
WHERE cnum=1002;
e.g. 4 :- Change the stdcode to 011 where city is Delhi in table companies .
Ans.:- UPDATE companies
SET stdcode = ‘011’
WHERE city=‘Delhi’;
DELETE
P a g e 11 | 23 Amit Kumar Jain,PGT(CS), K.V. Sunderbani
We can delete particular row(s)from a table.

SYNTAX:::-
DELETE FROM Table_Name
WHERE predicate;

e.g. :- delete all rows from companies where cnum is 1003.

Ans.:-DELETE FROM companies

WHERE cnum=1003;

SELECT
Select command is used to retrieve/ fetch/ extract or display data from the Table.

SYNTAX :
SELECT [DISTINCT|ALL] column_name1,column_name2,….
FROM table_name1
[WHERE Condition]
[ORDER BY Ordering_column [ASC|DESC]
[GROUP BY Grouping_coumn_name]
[HAVING Predicate];

Displaying all columns using * keyword

e.g. :- Display all data from table COMPANIES.
cnum cname city stdcode
Ans :- SELECT *
FROM companies; 1001 BPL Delhi 011
1002 Akai Mumbai 022
1003 Voltas Chennai 044
1004 IBM Delhi 011
Displaying Specific Column/columns
e.g. :- Display city and stdcode from table COMPANIES.
Ans :- SELECT city, stdcode
FROM companies;

P a g e 12 | 23 Amit Kumar Jain,PGT(CS), K.V. Sunderbani

OUTPUT:
city stdcode
Delhi 011
Mumbai 022
Chennai 044
Delhi 011

WHERE
WHERE :- The WHERE keyword is used to select group of records based on some
condition(s).

Conditional selection used in the WHERE clause :-

=,>,<,>=,<=,<>,LIKE, IN, BETWEEN , IS NULL, IS NOT NULL

e.g. :- Display company name which have their offices in delhi.

Ans :- SELECT cname
FROM companies
WHERE city=‘Delhi’;

cname
BPL
IBM

ORDER BY

ORDER BY :-
1. ORDER BY clause is used to produce output in a logical order.
2. The order may be of Ascending or Descending for numbers or alphabet.
3. The keyword ASC and DESC are used for Ascending or Descending order.
Note : By default order by arranges all records in Ascending order.

e.g. :- Display all details from table companies in descending order of their company name.
cnum cname city stdcode
Ans.
SELECT * 1003 Voltas Chennai 044
FROM Companies 1004 IBM Delhi 011
ORDER BY cname DESC; 1001 BPL Delhi 011

1002 Akai Mumbai 022

P a g e 13 | 23 Amit Kumar Jain,PGT(CS), K.V. Sunderbani

GROUP BY :-

1. GROUP BY clause allows the user to define a subset of the values in

particular field in term of another field.
2. It also applies an aggregate function to the subset.

e.g. :- Display highest quantity sold by each company.

Ans. SELECT cnum, MAX(qty)

FROM sales
GROUP BY cnum;

P a g e 14 | 23 Amit Kumar Jain,PGT(CS), K.V. Sunderbani

JOINING OF TABLES
Referential Integrity
When result is derived from two or more tables. The references are created to
link them.
The referential integrity can be achieve using Foreign Key in DBMS.

Foreign Key – It is a column in a table which is the primary key in another table.
CUSTOMER

ORDER
Customer_id

Here in the above example

Customer_id in table Order is a Foreign Key which is the primary key of table
Customer.
Cartesian Product / Cross Join

P a g e 15 | 23 Amit Kumar Jain,PGT(CS), K.V. Sunderbani

P a g e 16 | 23 Amit Kumar Jain,PGT(CS), K.V. Sunderbani
P a g e 17 | 23 Amit Kumar Jain,PGT(CS), K.V. Sunderbani
Example :

How to join or derive result from 02 tables

Ex.1 Display employee name along with his department.

Ans. SELECT E.EName, D.DName
FROM Emp E, Dept D
WHERE E.DeptNo=D.DeptNo;

FootNote : - For 2 table based queries, always write the condition on common column that
must be equal for both tables.

P a g e 18 | 23 Amit Kumar Jain,PGT(CS), K.V. Sunderbani

Ex.2 Display employees name working in sales department.
Ans. SELECT E.EName
FROM Emp E, Dept D
WHERE E.DeptNo=D.DeptNo AND D.DName like “Sales”;

Ex.3 Count and display number of employees from each department.

Ans. SELECT D.DName, Count(*)
FROM Emp E, Dept D
WHERE E.DeptNo=D.DeptNo
GROUP BY D.DName;

TRANSACTION CONTROL LANGUAGE

(TCL)

P a g e 19 | 23 Amit Kumar Jain,PGT(CS), K.V. Sunderbani

P a g e 20 | 23 Amit Kumar Jain,PGT(CS), K.V. Sunderbani
E.g. TCL Commands →
Table : Bank
BankID Branch Balance
1001 Jaipur 10110000
1002 Kota 21023300
BankID Branch Balance
1001 Jaipur 10110000
SAVEPOINT Mark1;
1002 Kota 21023300
INSERT INTO TABLE VALUES (1003,”Ajmer”,10000230);
1003 Ajmer 10000230
SAVEPOINT Mark2;
SELECT * FROM Bank; BankID Branch Balance
1001 Jaipur 10110000
INSERT INTO TABLE VALUES (1004,”Delhi”,10233230); 1002 Kota 21023300
SAVEPOINT Mark3; 1003 Ajmer 10000230
SELECT * FROM Bank; 1004 Delhi 10233230

INSERT INTO TABLE VALUES (1005,”Dausa”,52031452);

SAVEPOINT Mark4; BankID Branch Balance
SELECT * FROM Bank; 1001 Jaipur 10110000
1002 Kota 21023300
1003 Ajmer 10000230
1004 Delhi 10233230
1005 Dausa 52031452

ROLLBACK TO Mark2;
BankID Branch Balance
1001 Jaipur 10110000
SELECT * FROM Bank;
1002 Kota 21023300
1003 Ajmer 10000230
INSERT INTO TABLE VALUES (1006,”Agra”,10076230); BankID Branch Balance
SELECT * FROM Bank; 1001 Jaipur 10110000
1002 Kota 21023300
1003 Ajmer 10000230
1006 Agra 10076230

ROLLBACK TO Mark1; BankID Branch Balance

SELECT * FROM Bank; 1001 Jaipur 10110000
1002 Kota 21023300
ROLLBACK TO Mark3;
SELECT * FROM Bank; BankID Branch Balance
1001 Jaipur 10110000
1002 Kota 21023300

P a g e 21 | 23 Amit Kumar Jain,PGT(CS), K.V. Sunderbani

Some Important Queries
1. Adding multiple rows in a table
E.g INSERT INTO Student
VALUES(1,”Raj”,10), (2,”Manoj”,10),(3,”Sobhit”,10);

Change the name of column-

ALTER TABLE Issue
CHANGE BName Subject VARCHAR(30);

CARTESIAN PRODUCT
Method1
SELECT *
FROM Student, Issue;
Method2
SELECT *
FROM Student JOIN Issue;
Method3
SELECT *
FROM Student CROSS JOIN Issue;

NATURAL JOIN
Method1
SELECT *
FROM Student NATURAL JOIN Issue;
Method2
SELECT *
FROM Student JOIN Issue
P a g e 22 | 23 Amit Kumar Jain,PGT(CS), K.V. Sunderbani
USING (RollNo);

EQUI JOIN
Method1
SELECT *
FROM Student JOIN Issue
WHERE Student.RollNo=Issue.RollNo;
Method2
SELECT *
FROM Student JOIN Issue
ON Student.RollNo=Issue.RollNo;

OUTER JOIN
(LEFT JOIN/ RIGHT JOIN)
SELECT *
FROM Student LEFT JOIN Issue
ON Student.RollNo=Issue.RollNo;

SELECT *
FROM Student RIGHT JOIN Issue
ON Student.RollNo=Issue.RollNo;

P a g e 23 | 23 Amit Kumar Jain,PGT(CS), K.V. Sunderbani