DBMS Lab

(RCS-551)

EXPERIMENT NO.1

AIM

To create tables and insert data into them.

THEORY & CONCEPTS

A database is an organized collection of data, stored and accessed electronically. Database designers
typically organize the data to model aspects of reality in a way that supports processes
requiring information. In relational databases, and flat file databases, a table is a set of data
elements using a model of vertical columns and horizontal rows, the cells being the unit
where a row and column intersect. A table has a specified number of columns, but can have
any number of rows. The row is called a tuple and the columns are known as attributes.

The number of tuples in a table is called as its domain. Each tale has its own schema which is created by
when the create table command is executed with given set of attributes, with the help of insert
command we record the data into the tables which is termed to be as tuple. In each table we
have different keys which helps to uniquely identify the tuple or differentiate between
different records. In DBMS the processing speed of the tables when required is very fast even
on big data.

SYNTAX/COMMANDS USED

1. create database <database_name>;

2. create table table_name(column1 datatype, column2 datatype,….);

3. show databases;

4. show tables;
5. desc <table_name>;

6. select * from <table_name>;

7. insert into table_name values (value1, value2,…….);

VISHAL SHUKLA IT-2(B2) 1709113122

DBMS Lab
(RCS-551)

LAB ASSIGNMENT

Query​: Create two tables named client_master and product_master with the following
fields mentioned below respectively.
#client_master client_no varchar(6)
city varchar(15)
state varchar(15)
pincode numeric(6)
bal_due numeric(10,2)

#product_master product_no varchar(7)

description varchar(15)
profit_percent numeric(2)
unit_measure varchar(7)
qty_on_hand numeric(4)
reorder_lvl numeric(3)
sell_price numeric(6)
cost_price numeric(6)
Solution

mysql> create database Vishal;

mysql> use Vishal;

mysql> create table client_master (client_no varchar(6),name varchar(20),city

varchar(15),state varchar(15),pincode numeric(6), Bal_due numeric(10,2));
mysql> create table product_master (product_no varchar(7),description
varchar(15),profit_percent numeric(2),unit_measure varchar(7),Qty_on_hand
numeric(4),reorder_lvl numeric(3),sell_price numeric(6),cost_pricem

VISHAL SHUKLA IT-2(B2) 1709113122

DBMS Lab
(RCS-551)

numeric(6));

Query​: Inserting the data in both the tables ​Solution

mysql> insert into client_master
values('1','Ivan','Bombay','Maharashtra','400054','15000');
Query OK, 1 row affected (0.04 sec)

mysql> insert into client_master

values(2,'Vandana','Madras','Tamilnadu',780001,0);
Query OK, 1 row affected (0.03 sec)

mysql> insert into client_master

values(3,'Pramada','Bombay','Mahaashtra',400057,5000);
Query OK, 1 row affected (0.03 sec)
mysql> insert into client_master
values(4,'Basu','Bombay','Maharashtra',400056,0);
Query OK, 1 row affected (0.03 sec)

mysql> insert into client_master values(5,'Ravi','Delhi','Delhi',100001,2000); Query OK, 1

row affected (0.03 sec)

VISHAL SHUKLA IT-2(B2) 1709113122

DBMS Lab
(RCS-551)

mysql> insert into client_master

values(6,'Rukmini','Bombay','Bombay',400050,0);
Query OK, 1 row affected (0.03 sec)

mysql> insert into product_master

values('P00001','144floppies',5,'piece',100,20,525,500);
Query OK, 1 row affected (0.04 sec)

mysql> insert into product_master

values('P03453','monitors',6,'piece',10,3,12000,11200);
Query OK, 1 row affected (0.04 sec)

mysql> insert into product_master

values('P06734','mouse',5,'piece',20,5,1050,500);
Query OK, 1 row affected (0.03 sec)

mysql> insert into product_master

values('P07865','1.22floppies',5,'piece',100,20,525,500);
Query OK, 1 row affected (0.04 sec)

mysql> insert into product_master

values('P07868','Keyboards',2,'piece',10,3,3150,3050);
Query OK, 1 row affected (0.04 sec)

mysql> insert into product_master

values('P07885','CDDrive',3,'piece',10,3,5250,5100);
Query OK, 1 row affected (0.04 sec)
mysql> insert into product_master
values('P07965','540HDD',4,'piece',10,3,8400,8000);
Query OK, 1 row affected (0.03 sec)

VISHAL SHUKLA IT-2(B2) 1709113122

DBMS Lab
(RCS-551)

mysql> insert into product_master values('P07975','1.44

Drive',5,'piece',10,3,1050,1000);
Query OK, 1 row affected (0.02 sec)

mysql> insert into product_master

values('P08865','1.22Drive',5,'piece',2,3,1050,1000);
Query OK, 1 row affected (0.03 sec)
VISHAL SHUKLA IT-2(B2) 1709113122
DBMS Lab
(RCS-551)

EXPERIMENT NO.2

AIM

To use select commands for retrieving data from tables.

THEORY & CONCEPTS

The SQL SELECT Statement : The SELECT statement is used to select data from a database.The
data returned is stored in a result table, called the result-set.

The SQL WHERE Clause : The WHERE clause is used to filter records.The WHERE clause is used to
extract only those records that fulfill a specified condition

The SQL AND, OR and NOT Operators: The WHERE clause can be combined with AND, OR, and NOT
operators.

The AND and OR operators are used to filter records based on more than one condition:

● ​The AND operator displays a record if all the conditions separated by AND is TRUE. ​● ​The OR
operator displays a record if any of the conditions separated by OR is TRUE.

The NOT operator displays a record if the condition(s) is NOT TRUE.

SYNTAX/COMMANDS USED

1. select * from tablename; (* => attributes)

2. select ____, ____ from tablename where ____ = ____; (when a condition is given)
3. using logical operator: and, &&, or, ||, not and ! (when two conditions are given)
4. select ____ from tablename where ____ between ____ and ____; (between, and
operator)
5. select ____ from tablename order by ____ ;( order by clause: to retrieve data in a
sorted array)
6. Pattern Matching (like) – (%: replaces substring, _ : one character space)
Ex. Select ____ from tablename where ____ like ‘_ _ A _ b%’;
VISHAL SHUKLA IT-2(B2) 1709113122
DBMS Lab
(RCS-551)

LAB ASSIGNMENT

Query : ​Find out the names of all the clients. ​Solution

Query : ​Retrieve the list of names and cities of all the cities. ​Solution
VISHAL SHUKLA IT-2(B2) 1709113122
DBMS Lab
(RCS-551)

Query : ​List various product available from product_master table. ​Solution

Query : ​List all the clients located in Bombay. ​Solution

Query : ​Display information of client_no 1 and client_no 2. ​Solution

VISHAL SHUKLA IT-2(B2) 1709113122
DBMS Lab
(RCS-551)

Query : ​Find the product with description as “1.44Drive” and “1.22Drive” ​Solution

Query : ​Find all the products whose price >5000. ​Solution

Query : ​Find list of all clients who stay in city “Bombay” or “Delhi” or “Madras”. ​Solution

Query : ​Find the product whose Selling Price >2000 or <=5000. ​Solution
VISHAL SHUKLA IT-2(B2) 1709113122
DBMS Lab
(RCS-551)

Query : ​List name, city, state of clients who do not belong to “Maharashtra”. ​Solution

Query : ​Find the product whose Selling Price >1500 and also update new Selling Price as Original
Selling Price*15.

Solution

Query : ​Find out clients who stay in city whose second letter is “a”. ​Solution
VISHAL SHUKLA IT-2(B2) 1709113122
DBMS Lab
(RCS-551)

Query : ​Find out names of all clients having ‘a’ as second letter in their name. ​Solution

Query : ​List the products in sorted order of their description. ​Solution

Query : ​List product_no and description whose profit % is in between 20 and 30 both inclusive.

Solution
VISHAL SHUKLA IT-2(B2) 1709113122