1

Practical-1
Data Definition Language(DDL) commands in RDBMS
CREATE-
Syntax-
create table table_name
(
column_1 datatype,
column_2 datatype,
column_3 datatype,
);

Query-
CREATE TABLE students
(
name char(10), rollno int(2), branch char(10),
);

Output-

ALTER-
Syntax- Alter table table_name
Add column_name datatype;
Query-
mysql> alter table students add primary key(rollno);
Query OK, 0 rows affected (0.07 sec)
Records: 0 Duplicates: 0 Warnings: 0
 2

Output-

TRUNCATE-
Syntax- Truncate Table table_name;
Query- mysql> truncate table students;
Query OK, 0 rows affected (0.03 sec)

Output-

DROP-
Syntax- Drop Table table_name;
Query- mysql> drop database student;
Query OK, 1 row affected (0.04 sec)

Output-
 3

Practical-2
Data Manipulation Language(DML) and Data Control
Language(DCL)
INSERT-
Syntax-
Insert into <table name> (column list) values (column values);
Query-
mysql> insert into students values('Anuj',14,'CSE'),
-> ('Ankit',13,'CSE');
Query OK, 2 rows affected (0.01 sec)

Output-

UPDATE-
Syntax-
Update <table_name> Set column_number=value_number
where condition;
Query-
mysql> update students set name='Rohan' where rollno=14;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
 4

Output-

Delete-
Syntax- Delete from <table_name> where condition;
Query-
mysql> delete from students where name='Rohan';
Query OK, 1 row affected (0.01 sec)

Output-

Data Control Language-

GRANT-
Syntax- Grant privilege_name on object_name
To {user_name | public | role_name}
 5

Query-
CREATE USER Abhishek IDENTIFIED BY 'password';
grant select, insert, delete on CA to Abhishek;

Output-

AFTER GRANT

Revoke-
Syntax- Revoke privilege_name on object_name
To {user_name | public | role_name}
Query-
revoke insert, select, update on CA to Abhishek;
Output-
 6

Practical-3
High level language extensions with cursors
CURSOR-
Syntax-
CURSOR cursor_name is select_statement;
DECLARE-
CURSOR cur_students is
SELECT name, rollno from students;
FETCH-
FETCH cur_students into ST_name,ST_rollno;
CLOSE-
CLOSE cur_students;

Query-
mysql> delimiter //
mysql> create procedure cursor12()
-> begin
-> declare j int;
-> declare n char(10);
-> declare cur cursor for SELECT STUDENT_NAME,
STUDENT_ROLLNO from students;
-> OPEN cur;
-> FETCH cur into j,n;
-> SELECT j,n;
 7

-> CLOSE cur;

-> END; //
Query OK, 0 rows affected (0.03 sec)

Output-
 8

Practical-4
High level language extensions with Triggers
Syntax-
Create (or replace) Trigger trigger_name
{Before | After}
{Insert [or] | Update [or] | Delete}
ON table_name
[FOR EACH ROW]
DECLARE
Declaration-statements
BEGIN
Executable-statements
END;

Query-
mysql> create table account(acct_num int, amount Decimal(10,2));
Query OK, 0 rows affected (0.05 sec)
mysql> create trigger ins_sum Before insert on account
-> For Each Row Set @sum = @sum + New.amount;
Query OK, 0 rows affected (0.01 sec)
mysql> delimiter //
mysql> Set @sum = 0;
-> insert into account values(137,14.98),(141,1937.50),(97,-100.00);
-> select @sum as 'Total amount inserted';
-> //
Query OK, 0 rows affected (0.00 sec)
 9

Output-
 10

Practical-6
Database design using E-R model and Normalization
Syntax-
Create table <table_name> (Column_name1 Datatype (Size),
Column_name2 Datatype (Size)……..);

Query-
1) mysql> create table Zone(
-> ZONE_ID int PRIMARY KEY,
-> ZONE_NAME char(20));

2) mysql> create table Agency(

-> AGENCY_ID int PRIMARY KEY,
-> AGENCY_NAME char(20),
-> ZONE_ID int,
-> FOREIGN KEY(ZONE_ID) REFERENCES Zone(ZONE_ID));

3) mysql> create table Assistant(

-> ASSISTANT_ID int PRIMARY KEY,
-> NAME char(20),
-> MOB_NUMBER char(10),
-> JOINING_DATE DATE,
-> JOB_ID char(25),
-> INCOME int,
-> AGENCY_ID int,
 11

-> FOREIGN KEY(AGENCY_ID) REFERENCES

-> Agency(AGENCY_ID));

4) mysql> create table Jobs(

-> JOB_ID char(25) PRIMARY KEY,
-> JOB_TITLE char(30),
-> MIN_INCOME int,
-> MAX_INCOME int,
-> AGENCY_ID int,
-> FOREIGN KEY(AGENCY_ID) REFERENCES Agency(AGENCY_ID));

5) mysql> create table Locality(

-> LOCALITY_ID int PRIMARY KEY,
-> CITY char(25),
-> ZONE_ID char(2),
-> ASSISTANT_ID int,
-> FOREIGN KEY(ASSISTANT_ID) REFERENCES assistant(ASSISTANT_ID));
 12

Output-
 13

Practical-7
Design and implementation of payroll processing
system
Syntax-
Create table <table_name> (Column_name1 Datatype (Size),
Column_name2 Datatype (Size)……..);

Query-
1) mysql> Create Table STAFF(
-> Staff_ID integer PRIMARY KEY,
-> Staff_NAME TEXT NOT NULL,
-> Staff_ADD TEXT NOT NULL,
-> Staff_MOB TEXT NOT NULL,
-> DEPT_NO TEXT NOT NULL,
-> DEPT_NAME TEXT NOT NULL,
-> SALARY TEXT NOT NULL
-> );
Query OK, 0 rows affected (0.04 sec)
2)
mysql> insert into Staff values(101,'Abhishek','New Delhi','9592330812','2095',
-> 'Finance','20000');
mysql> insert into Staff values(102,'Ankur','New Delhi','9596881232','2136',
-> 'Finance','26000');
mysql> insert into Staff values(103,'Vishal','New Delhi','9598527705','3036',
-> 'Finance','30000');
 14

mysql> insert into Staff values(104,'Rahul','New Delhi','9767440936','4296',

-> 'Finance','35000');
mysql> insert into Staff values(105,'Manish','GNoida','9995086745','6089',
-> 'Finance','40000');

Output-
 15

Practical-8
Design and implement of Banking system
Syntax-
Create table<table_name> (column_name1 Datatype (Size),
Column_name2 datatype (Size)……);
Query-
1) mysql> CREATE TABLE branch(
-> branch_id INT NOT NULL AUTO_INCREMENT,
-> branch_name VARCHAR(30) NOT NULL,
-> assets INT NOT NULL,
-> branch_address VARCHAR(255) NOT NULL,
-> PRIMARY KEY(branch_id)
-> );

2) mysql> CREATE TABLE banker_info(

-> banker_id INT NOT NULL AUTO_INCREMENT,
-> banker_name VARCHAR(255) NOT NULL,
-> branch_id INT NOT NULL,
-> PRIMARY KEY (banker_id),
-> FOREIGN KEY (branch_id) REFERENCES branch(branch_id)
-> );

3) mysql> CREATE TABLE account(

-> account_id INT NOT NULL AUTO_INCREMENT,
-> account_type VARCHAR(30) NOT NULL,
-> account_balance INT NOT NULL,
-> branch_id INT NOT NULL,
-> PRIMARY KEY (account_id),
-> FOREIGN KEY (branch_id) REFERENCES branch(branch_id)
 16

-> );

4) mysql> CREATE TABLE customer(

-> customer_id INT NOT NULL AUTO_INCREMENT,
-> customer_name VARCHAR(30) NOT NULL,
-> mobileno VARCHAR(10) NOT NULL,
-> dob DATE,
-> account_id INT NOT NULL,
-> PRIMARY KEY (customer_id),
-> FOREIGN KEY (account_id) REFERENCES account(account_id)
-> );

5) mysql> CREATE TABLE transaction(

-> transaction_id INT NOT NULL AUTO_INCREMENT,
-> amount INT NOT NULL,
-> customer_id INT NOT NULL,
-> account_id INT NOT NULL,
-> PRIMARY KEY (transaction_id),
-> FOREIGN KEY (customer_id) REFERENCES customer(customer_id),
-> FOREIGN KEY (account_id) REFERENCES account(account_id)
-> );

6) mysql> CREATE TABLE customer_credit_card(

-> credit_card_id INT NOT NULL AUTO_INCREMENT,
-> expiry_date DATE NOT NULL,
-> card_limit INT NOT NULL,
-> customer_id INT NOT NULL,
-> account_id INT NOT NULL,
-> PRIMARY KEY (credit_card_id),
-> FOREIGN KEY (customer_id) REFERENCES customer(customer_id),
-> FOREIGN KEY (account_id) REFERENCES account(account_id)
 17

-> );

7) mysql> CREATE TABLE loan(

-> loan_id INT NOT NULL AUTO_INCREMENT,
-> issued_amount INT NOT NULL,
-> remaining_amount INT NOT NULL,
-> branch_id INT NOT NULL,
-> account_id INT NOT NULL,
-> PRIMARY KEY(loan_id),
-> FOREIGN KEY (branch_id) REFERENCES branch(branch_id),
-> FOREIGN KEY (account_id) REFERENCES account(account_id)
-> );

8) mysql> CREATE TABLE loan_payment(

-> loan_payment_id INT NOT NULL AUTO_INCREMENT,

-> amount INT NOT NULL,
-> loan_id INT NOT NULL,
-> PRIMARY KEY (loan_payment_id),
-> FOREIGN KEY (loan_id) REFERENCES loan(loan_id)
-> );

9) mysql> CREATE TABLE borrower(

-> borrower_id INT NOT NULL AUTO_INCREMENT,

-> customer_id INT NOT NULL,

-> loan_id INT NOT NULL,

-> PRIMARY KEY (borrower_id),

-> FOREIGN KEY (loan_id) REFERENCES loan(loan_id),
-> FOREIGN KEY (customer_id) REFERENCES customer(customer_id)
 18

-> );

Output-
 19

Practical-9
Design and implement of Library Information System
Syntax-
Create table<table_name> (column_name1 Datatype (Size),
Column_name2 datatype (Size)……);

Query-
1) mysql> Create table LMS_MEMBERS
-> (
-> MEMBER_ID Varchar(10),
-> MEMBER_NAME Varchar(30) NOT NULL,
-> CITY Varchar(20),
-> DATE_REGISTER Date NOT NULL,
-> DATE_EXPIRE Date ,
-> MEMBERSHIP_STATUS Varchar(15)NOT NULL,
-> Constraint LMS_cts1 PRIMARY KEY(MEMBER_ID)
-> );

2) mysql> Create table LMS_SUPPLIERS_DETAILS

-> (
-> SUPPLIER_ID Varchar(3),
-> SUPPLIER_NAME Varchar(30) NOT NULL,
-> ADDRESS Varchar(50),
-> CONTACT bigint(10) NOT NULL,
-> EMAIL Varchar(15) NOT NULL,
-> Constraint LMS_cts2 PRIMARY KEY(SUPPLIER_ID)
 20

-> );

3) mysql> Create table LMS_FINE_DETAILS

-> (
-> FINE_RANGE Varchar(3),
-> FINE_AMOUNT decimal(10,2) NOT NULL,
-> Constraint LMS_cts3 PRIMARY KEY(FINE_RANGE)
-> );

4) mysql> Create table LMS_BOOK_DETAILS

-> (
-> BOOK_CODE Varchar(10),
-> BOOK_TITLE Varchar(50) NOT NULL,
-> CATEGORY Varchar(15) NOT NULL,
-> AUTHOR Varchar(30) NOT NULL,
-> PUBLICATION Varchar(30),
-> PUBLISH_DATE Date,
-> BOOK_EDITION int(2),
-> PRICE decimal(8,2) NOT NULL,
-> RACK_NUM Varchar(3),
-> DATE_ARRIVAL Date NOT NULL,
-> SUPPLIER_ID Varchar(3) NOT NULL,
-> Constraint LMS_cts4 PRIMARY KEY(BOOK_CODE),
-> Constraint LMS_cts41 FOREIGN KEY(SUPPLIER_ID) References
LMS_SUPPLIERS_DETAILS(SUPPLIER_ID)
-> );
 21

5) mysql> Create table LMS_BOOK_ISSUE

-> (
-> BOOK_ISSUE_NO int,
-> MEMBER_ID Varchar(10) NOT NULL,
-> BOOK_CODE Varchar(10) NOT NULL,
-> DATE_ISSUE Date NOT NULL,
-> DATE_RETURN Date NOT NULL,
-> DATE_RETURNED Date NULL,
-> FINE_RANGE Varchar(3),
-> Constraint LMS_cts5 PRIMARY KEY(BOOK_ISSUE_NO),
-> Constraint LMS_Mem FOREIGN KEY(MEMBER_ID) References
LMS_MEMBERS(MEMBER_ID),

-> Constraint LMS_BookDetail FOREIGN KEY(BOOK_CODE) References

LMS_BOOK_DETAILS(BOOK_CODE),

-> Constraint LMS_FineDetail FOREIGN KEY(FINE_RANGE) References

LMS_FINE_DETAILS(FINE_RANGE)

-> );
 22

Output-
 23

Practical-11
Automatic Backup of Files and Recovery of Files
BACKUP-
Syntax-
C:\Windows\System32>cd/
C:\>cd program files
C:\Program Files>cd mysql
C:\Program Files\MySQL>dir
Volume in drive C is Windows
Volume Serial Number is 2A99-05BD
Directory of C:\Program Files\MySQL
27-01-2023 13:36 <DIR> .
27-01-2023 13:34 <DIR> ..
27-01-2023 13:34 <DIR> MySQL Server 5.7
27-01-2023 13:36 <DIR> MySQL Shell 8.0
27-01-2023 13:34 <DIR> MySQL Workbench 8.0
0 File(s) 0 bytes
5 Dir(s) 112,946,413,568 bytes free
C:\Program Files\MySQL>cd mysql*
C:\Program Files\MySQL\MySQL Server 5.7>cd bin
C:\Program Files\MySQL\MySQL Server 5.7\bin>mysqldump -u root -p account
> newaccount.sql
Enter password: ********

Query-
 24

mysql> drop database account;

Query OK, 1 row affected (0.02 sec)

RECOVERY-
Syntax-
C:\Program Files\MySQL\MySQL Server 5.7\bin>mysql -u root -p account <
newaccount.sql
Enter password: ********
C:\Program Files\MySQL\MySQL Server 5.7\bin>
Query-
mysql> create database account;
Query OK, 1 row affected (0.00 sec)

OUTPUT-

Before Recovery
 25

After Recovery