RDBMS LAB FILE

Om Utsav [IOT And Intelligent System - 2021]

 | Om Utsav [IOT And Intelligent System - 2021]
RDBMS LAB FILE

Contents
Create New Database..................................................................................................................................3
Select Database to use................................................................................................................................3
Create a Table and Display all Tables in Database........................................................................................4
Insert record into Table................................................................................................................................5
Display All Records in Table.........................................................................................................................6
Modify Table Attributes...............................................................................................................................6
Add a Constraint..........................................................................................................................................6
Drop Constraint...........................................................................................................................................7
Setting Up Foreign Key Constraint...............................................................................................................7
Display Selected Attributes from Table........................................................................................................7
Display Records in Order.............................................................................................................................8
Creating Alias...............................................................................................................................................9
Rename a column......................................................................................................................................10
Change Dimensions or Column Size...........................................................................................................10
Update Entire Column in a Table...............................................................................................................10
Rename a Table.........................................................................................................................................11
Update a Record........................................................................................................................................11
Delete a Record from Table.......................................................................................................................11
Delete a Table............................................................................................................................................12
GROUPBY Clause........................................................................................................................................12
HAVING Clause..........................................................................................................................................12
Using Wildcards.........................................................................................................................................13
BETWEEN Clause.......................................................................................................................................13
IN Clause....................................................................................................................................................14
Create a View.............................................................................................................................................14
Left Join.....................................................................................................................................................15
Right Join...................................................................................................................................................15
Full Join......................................................................................................................................................16
Self-Join.....................................................................................................................................................17
EXPLAIN Command....................................................................................................................................17
Create and Show Index..............................................................................................................................17

1|Page
 | Om Utsav [IOT And Intelligent System - 2021]
RDBMS LAB FILE

Drop Index.................................................................................................................................................18
Create a Trigger.........................................................................................................................................18
Show Triggers............................................................................................................................................20

2|Page
 | Om Utsav [IOT And Intelligent System - 2021]
RDBMS LAB FILE

Date: 03 Feb, 2023

Create New Database

1. create database invoicemgsys_clone;

Select Database to use

2. use invoicemgsys_clone;

3|Page
 | Om Utsav [IOT And Intelligent System - 2021]
RDBMS LAB FILE

Create a Table and Display all Tables in Database

3. create table customers( id int primary key auto_increment,

invoice varchar(10),
name varchar(25),
email varchar(25),
address_1 varchar(255),
address_2 varchar(225),
town varchar(25),
country varchar(20),
postcode int(6),
name_ship varchar(25),
address_ship varchar(25),
town_ship varchar(25),
country_ship varchar(25),
postcode_ship varchar(6)
);

4. show tables;

4|Page
 | Om Utsav [IOT And Intelligent System - 2021]
RDBMS LAB FILE

Insert record into Table

5. insert into customers(id,invoice ,name, email,

address_1,address_2,town,country,postcode,name_ship ,address_ship,town_
ship, country_ship,postcode_ship ) values(001, 'INV01', 'Annanya_Sood',
'annanya@muj.manipal.edu','-', '-', 'Shimla', 'India', 171001, 'Annanya',
'Home','Shimla, HP', 'India', '171001');
insert into customers(id,invoice ,name, email,
address_1,address_2,town,country,postcode,name_ship ,address_ship,town_
ship, country_ship,postcode_ship ) values(0051, 'INV01', 'Ishika',
'ishika@muj.manipal.edu','-', '-', 'Saharanpur', 'India', 303001, 'Ishika',
'Home','Saharanpur, UP', 'India', '-');
insert into customers(id,invoice ,name, email,
address_1,address_2,town,country,postcode,name_ship ,address_ship,town_
ship, country_ship,postcode_ship ) values(002, 'INV02', 'Om_Utsav',
'om@muj.manipal.edu','-', '-', 'Faridabad', 'India', 965220, 'Om',
'Home','Faridabad, HR', 'India', '965220');
insert into customers(id,invoice ,name, email,
address_1,address_2,town,country,postcode,name_ship ,address_ship,town_
ship, country_ship,postcode_ship ) values(003, 'INV03', 'Abhijeet Kumar',
'abhijeet@muj.manipal.edu','-', '-', 'Patna', 'India', 852369, 'Abhijeet',
'Home','Patna, BH', 'India', '-');
insert into customers(id,invoice ,name, email,
address_1,address_2,town,country,postcode,name_ship ,address_ship,town_
ship, country_ship,postcode_ship ) values(004, 'INV04', 'Isha Chauhan',
'isha@muj.manipal.edu','-', '-', 'Kotdwara', 'India', 510222, 'Isha',
'Home','Kotdwara, UK', 'India', '-');

5|Page
 | Om Utsav [IOT And Intelligent System - 2021]
RDBMS LAB FILE

Display All Records in Table

6. select * from customers;

Date: 10 Feb, 2023

Modify Table Attributes

1. alter table customers

modify town varchar(255) default 'Shimla';

insert into customers(id,invoice ,name, email,

address_1,address_2,country,postcode,name_ship ,address_ship,town_ship,
country_ship,postcode_ship ) values(0096, 'INV01', 'Brudo',
'Brudo@abc.com','-', '-', 'India', 171001, 'Annanya', 'Home','Shimla, HP',
'India', '171001');

Add a Constraint

2. alter table customers

add constraint myConstraint Unique(id, invoice);

6|Page
 | Om Utsav [IOT And Intelligent System - 2021]
RDBMS LAB FILE

Drop Constraint

3. alter table invoices

drop constraint myConstraint

Setting Up Foreign Key Constraint

4. alter table customers

add foreign key (id) references invoices(id);

Display Selected Attributes from Table

5. select name, email, country from customers

7|Page
 | Om Utsav [IOT And Intelligent System - 2021]
RDBMS LAB FILE

where town= 'Shimla' || town = 'Saharanpur';

Date: 17 Feb, 2023

Display Records in Order

1. select id,name from customers

where town='Shimla' && country= 'India'
order by id;

2. select id,name from customers

where town='Shimla' && country= 'India'
order by id desc;

8|Page
 | Om Utsav [IOT And Intelligent System - 2021]
RDBMS LAB FILE

Creating Alias

3. select c.name, c.town, d.status

from customers c, invoices d
where c.id= d.id and d.status= 1 || status= 'open';

9|Page
 | Om Utsav [IOT And Intelligent System - 2021]
RDBMS LAB FILE

Date: 3 March, 2023

Rename a column

1. ALTER TABLE store_customers RENAME COLUMN address_1 TO

full_address;

Change Dimensions or Column Size

2. alter table customers

modify postcode_ship int(6);

Update Entire Column in a Table

3. update customers set postcode= '171001';

Rename a Table

10 | P a g e
 | Om Utsav [IOT And Intelligent System - 2021]
RDBMS LAB FILE

4. RENAME TABLE invoices TO bill;

Update a Record

5. update customers set town= 'Jaipur' where name= 'Brudo' && id= 96;
select * from customers where id= 96;

Delete a Record from Table

6. DELETE FROM bill

WHERE id= 1;

Delete a Table

11 | P a g e
 | Om Utsav [IOT And Intelligent System - 2021]
RDBMS LAB FILE

7. drop table bill;

Date: 10 March, 2023

GROUPBY Clause

1. SELECT COUNT(id), Country

FROM customers
GROUP BY Country;

HAVING Clause

2. SELECT invoice, discount, status

FROM invoices
WHERE id<45
HAVING status= 'open' || status= 1;

Using Wildcards

12 | P a g e
 | Om Utsav [IOT And Intelligent System - 2021]
RDBMS LAB FILE

3. SELECT id, product from invoice_items WHERE product LIKE 'Cam%';

4. SELECT id, product from invoice_items WHERE invoice LIKE 'INV0_'

BETWEEN Clause

5. SELECT product, discount, qty

FROM invoice_items
WHERE price BETWEEN 20000 AND 30000;

13 | P a g e
 | Om Utsav [IOT And Intelligent System - 2021]
RDBMS LAB FILE

IN Clause

6. select total, status, id

FROM invoices
WHERE total in (29500);

Date: 17 March, 2023

Create a View

1. create view views as

select bill.shipping, customers.name, customers.email, customers.postcode from
customers
inner join bill
on customers.id= bill.id;views

14 | P a g e
 | Om Utsav [IOT And Intelligent System - 2021]
RDBMS LAB FILE

Left Join

2. -- to check for a customers who have not cleared the payment

select bill.status, customers.name, customers.id from customers
left join bill
on customers.id= bill.id
where bill.status=0;

Right Join

3. -- to display all details of customer along with billing status details, pending
payment > 400
select bill.status, bill.total, customers.name from customers
right join bill
on customers.id= bill.id
where bill.status='open' and bill.total> 400;

15 | P a g e
 | Om Utsav [IOT And Intelligent System - 2021]
RDBMS LAB FILE

Full Join

4. -- full join <-- does not work so we use union

-- List all customers and their personal, bill details, including those without
orders.

select * from customers

left join bill
on customers.id=bill.id
union
select * from customers
right join bill
on customers.id= bill.id;

Self-Join

5. -- self join, used to find duplicates in a table

select c1.name, c1.id from customers c1
inner join customers c2
on c1.name= c2.name;

16 | P a g e
 | Om Utsav [IOT And Intelligent System - 2021]
RDBMS LAB FILE

Date: 31 March, 2023

EXPLAIN Command

1. EXPLAIN select id from customers where id=2;

Create and Show Index

2. create index cindex_ on customers(id);

SHOW INDEXES FROM CUSTOMERS;

Drop Index

17 | P a g e
 | Om Utsav [IOT And Intelligent System - 2021]
RDBMS LAB FILE

3. drop index cindex_ on customers;

SHOW INDEXES FROM CUSTOMERS;

Create a Trigger

4. -- before delete
delimiter $$
CREATE TRIGGER Backup1 BEFORE DELETE ON employee
FOR EACH ROW
BEGIN
INSERT INTO employee_backup
VALUES (OLD.employee_no, OLD.employee_name,
OLD.job, OLD.salary);
END;

delete from employee where employee_no=3;

5. -- Before insert
delimiter $$

18 | P a g e
 | Om Utsav [IOT And Intelligent System - 2021]
RDBMS LAB FILE

CREATE TRIGGER Check_empno BEFORE INSERT ON employee

FOR EACH ROW
BEGIN
IF NEW.employee_no < 0 THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'ERROR:
Employee Number must be a positive integer';
END IF;
END;

INSERT INTO `invoicemgsys`.`employee` (`employee_no`,

`employee_name`, `job`, `salary`) VALUES ('-1', 'Annanya', 'student',
'1000');
INSERT INTO `invoicemgsys`.`employee` (`employee_no`,
`employee_name`, `job`, `salary`) VALUES ('2', 'ish', 'student', '1000');

6. -- before update
DELIMITER $$
CREATE TRIGGER before_update_trigger
BEFORE UPDATE ON employee
FOR EACH ROW
BEGIN
IF NEW.salary < OLD.salary THEN
SET NEW.salary = OLD.salary;
END IF;
END$$

19 | P a g e
 | Om Utsav [IOT And Intelligent System - 2021]
RDBMS LAB FILE

DELIMITER ;

Show Triggers

7. show triggers;

20 | P a g e