DATABASE MANAGEMENT SYSTEM
1. Introducing Databases
Database : A Database refers to the collection of interrelated data. The intention of a database is that the same
collection of data should serve as many applications as possible (Sharing). Hence, a database is often conceived of as
the repository of information needed for running certain functions in a corporation or organization.
Database Management System (DBMS): It is a computer program that manages a database effectively and efficiently.
It performs various operations:
1. Reduce the data redundancy(Duplicity) to a large extent
2. Database can control data inconsistency to a large extent
3. Databases facilitate sharing of data
4. Databases enforce standards
5. Databases can ensure data security
Flat Databases vs. Relational Databases:
Flat Database: When all the data is stored in one file then such a database is called Flat Database. A spreadsheet is an
example of flat database.
Relational Databases: In Relational Database data is stored in multiple tables that are linked via common fields.
RDBMS (Relational Database Management System) : A software or computer program that handles Relational
Database is known as RDBMS
Candidate key:
It is a set of one or more columns (attributes) that can be serve as Primary key.
Primary key:
It is a set of one or more columns (attributes) that uniquely identifies the records in a table (relation).
Alternate Key:
A candidate key which is not selected as a primary key is known as Alternate key.
Composite Primary key:
The Primary key that consists of a combination of two or more fields or attributes is known as Composite key or
composite Primary key.
Student:
Roll Name Class sec Marks
1 Udhav X A 33
2 Divya X B 89
1 Jyoti XI A 99
3 Dipankar Xii A 87
4 Pawan X B 87
5 Pawan XI B 58
Composite Primary key (roll,class,sec)
SQL Practice Notes (Udhav Kumar Dubey) Page 1
BASE : DBMS of OpenOffice.org
BASE :
• It is a freeware Software.
• It is a database component of OpenOffice.org. It is a popular RDBMS.
• BASE is also known as OpenOffice.org BASE or simply OOo BASE.
• It allows managing all your information from a single zip file that stores all related information files.
• The database file of BASE is stored with extension .odb which expands to Open Office Database.
• Within the file, you can divide your data into separate storage containers called tables.
• Following operations can be performed on TABLES:
o View, add, and update table data by using on-line forms.
o Find and retrieve just the data you want by using queries.
o Analyse or print data in a specific layout by using reports.
• BASE offers following features to let you manage your data:
o Tables
o Queries
o Forms
o Reports
Table: A table refers to a storage container storing data pertaining to single object, subject or purpose.
Queries: A Query is a statement that gives you filtered data according to your conditions and specifications.
Forms: A Form is an interface in user specified layout that lets users view, enter, change data directly in the table.
Report: A report is an effective way to present data in a printed format. It is a formal, presentable printed document
that lists data in a formatted manner.
Customer
custid Namecust Phone Add
5 Udhav 9879879877 Kudi h.b.
7 Naaz 4564564565 Ratanada
Order
OrderID Itemname Qty custid
101 Laptop 5 5
102 Mouse 3 7
103 Keyboard 6 5
104 Digital camera 3 5
SQL Practice Notes (Udhav Kumar Dubey) Page 2
SQL Notes
SQL : SQL (pronounced “ess-que-el” or “see-quel”) stands for Structured Query Language. It is used to communicate
with a relational databse.
SQL Classification:
SQL provides many different types of commands used for different purpose. SQL commands can be mainly divided
into following categories:
1. DDL (Data Definition Language) Commands : SQL commands that allow us to perform tasks related to data
definition e.g.,
i. Creating, Altering and Dropping
ii. Granting and revoking privileges and roles.
iii. Maintenance commands
2. DML (Data Manipulation Language) Commands: SQL commands that allow us to perform data manipulation
e.g., retrieval, insertion, deletion and modification of data stored in a database.
3. TCL (Transaction Control Language) Commands: SQL commands that allow us to manage and control the
transaction.
Q. What do you mean by a Transaction?
Ans: A Transaction is one complete unit of work involving many steps.
Eg:
If Mohan transfer 100 Rs. to Deepak’s account than it is called one Transaction and having following steps:
1. Open Mohan’s account
2. Get balance of Mohan
3. Deduct 100 Rs. from the balance
4. Update Mohan’s account (save the new balance into their account).
5. Open Deepak’s account
6. Get balance of Deepak
7. Add 100 Rs. into the valance
8. Update Deepak’s account (save the new balance into their account).
SQL Commands:
1. CREATE TABLE : This command is used to create a new table in the database. (DDL command)
Syntax:
Eg:
CREATE TABLE <table-name>
CREATE TABLE student
(
(
<columnName> <dataType> [(size)],
Schno int PRIMARY KEY,
<columnName> <dataType> [(size)],
Name varchar(20) NOT NULL,
<columnName> <dataType> [(size)],
Age int,
);
City varchar(20)
);
2. INSERT INTO : This command is used to insert new record(s) into the table. (DML command)
Syntax: Eg:
INSERT INTO <table-name> [<column list>] INSERT INTO student(Schno, Name, Age,City)
VALUES (<value>, <value>, …) VALUES (1,'Aditi', 15, 'Jodhpur');
3. SELECT : This command is used to retrieve data from table(s). (DML command)
Syntax:
SELECT <column list> FROM <table-name>;
Or
SELECT <column list> FROM <table-name> WHERE <condition>;
Eg: Eg:
SELECT Schno, Name, Age,City FROM student; SELECT Schno, Name, Age,City FROM student
WHERE Schno=1;
SQL Practice Notes (Udhav Kumar Dubey) Page 3
• Display All columns from table
When we need to all the columns from table(s) than we can use (*) for that or write all the columns
name separated by comma.
Eg:
SELECT Schno, Name, Age,City FROM student;
Or we can write:
SELECT * FROM student;
• Eliminating Redundant Data (with keyword DISTINCT)
SELECT DISTINCT city FROM student;
Note: Above query will not display duplicate data.
• Using Column Aliases (Nick Name)
We can display column aliases (nick name of columns) in the output:
Eg:
SELECT Schno, Name AS StudentName, Age,City FROM student;
Note : In the output the column ‘Name’ will display as ‘StudentName’.
4. UPDATE: This command is used to modify data of a table. (DML command)
Syntax:
UPDATE <table-name>
SET <columnName> = <newvalue>
WHERE <condition>;
Eg:
UPDATE student
SET age=18
WHERE Schno=1;
5. DELETE : This command is used to remove/delete record(s) from a table. (DML command)
Syntax:
DELETE FROM <table-name> WHERE condition;
Eg:
DELETE FROM student WHERE AGE=18;
SQL Practice Notes (Udhav Kumar Dubey) Page 4
Exercise 1:
Consider the following table named “GARMENT”. Write command of SQL for (i) to (iv).
Table : GARMENT
GCODE GNAME SIZE COLOUR PRICE
111 TShirt XL Red 1400.00
112 Jeans L Blue 1600.00
113 Skirt M Black 1100.00
114 Ladies Jacket XL Blue 4000.00
115 Trousers L Brown 1500.00
116 Ladies Top L pink 1200.00
(i) Create above table.
Ans:
CREATE TABLE garment
(gcode int primary key,
Gname varchar(20) not null,
Size varchar(5) not null,
Colour varchar(10) not null,
Price float(6,2) not null
);
(ii) To insert the shown records in the table.
Ans:
INSERT INTO garment (gcode,gname,size,colour,price)
VALUES(111, ‘TShirt’, ‘XL’, ‘Red’, 1400.00);
INSERT INTO garment (gcode,gname,size,colour,price)
VALUES (112, ‘Jeans’, ‘L’, ‘Blue’, 1600.00);
INSERT INTO garment(gcode,gname,size,colour,price)
VALUES (113,’Skirt’, ‘M’, ‘Black’, 1100.00),
(114,’Ladies Jacket’, ‘XL’, ‘Blue’,4000.00),
(115,’Trousers’, ‘L’, ‘Brown’, 1500.00),
(116, ‘Ladies Top’, ‘L’, ‘Pink’,1200.00);
(iii) To display names of those garments that are available in ‘XL’ size.
Ans:
SELECT gname FROM garment WHERE size=’XL’;
(iv) To display codes and names of those garments that have their price less than 1500.00.
Ans:
SELECT gcode,gname FROM garment WHERE price<1500.00;
(v) To display names of those garment that are available in ‘Blue’ colour.
Ans:
SELECT gname FROM garment WHERE colour=’Blue’;
(vi) Delete rows with price less than 1200.00
Ans:
DELETE FROM garment WHERE price<1200.00;
SQL Practice Notes (Udhav Kumar Dubey) Page 5
Exercise 2:
Consider the following table FITNESS with details about fitness products being sold in the store. Write commands to
do the following:
Table: FITNESS
PCODE PNAME PRICE MANUFACTURER
P1 Treadmill 21000 Coscore
P2 Bike 20000 Aone
P3 Cross Trainer 14000 Reliable
P4 Multi Gym 34000 Coscore
P5 Massage Chair 5500 Regrosene
P6 Belly Vibrator Belt 6500 Ambawya
(i) To create the above table.
Ans:
CREATE TABLE fitness
(
Pcode varchar(5) PRIMARY KEY,
Pname varchar(20) not null,
Price int not null,
Manufacturer varchar(15) not null
);
(ii) To insert the shown records in the table.
Ans:
INSERT INTO fitness(pcode, pname,price,manufacturer)
VALUES(‘P1’,’Trademill’, 21000, ‘Coscore’);
(iii) To display the names of all the products with price less than 20000.
Ans:
SELECT pname FROM fitness WHERE price<20000;
(iv) To display the names of all products by the manufacturer “Reliable”
Ans:
SELECT pname FROM fitness WHERE manufacturer=’Reliable’;
(v) Delete rows with price less than 8000.
Ans:
DELETE FROM fitness WHERE price<8000;
SQL Practice Notes (Udhav Kumar Dubey) Page 6
