DBMS Lab Cycle

Experiment -1

1. Create an Employee table for the following fields

(Empno, Empname, Empaddress, salary, dept)

1. Calculate the total of the salary field

2. List the count of employees who have salary less than 50000

3. Find the details of employee having maximum salary

4. Add a new field title to the emplyee table with a default value as junior software engineer

5. Update the title of employee whose employee number 2004 to "Senor software engineer"

6. Delete the employee record of employee whose emplyee number is 2005

TEST CASE

mysql> select * from employee;

+-------+---------+------------+--------+--------------+

| empNo | empName | empAddress | salary | dept         |

+-------+---------+------------+--------+--------------+

|  2001 | Hari    | Palakkad   |  30000 | Reasearch    |

|  2002 | Devi    | Kasaragod  |  40000 | Network      |

|  2003 | Gupta   | Kannur     |  50000 | System Admin |

|  2004 | Ravi    | Calicut    |  60000 | HR           |

|  2005 | Rani    | Calicut    |  70000 | HR           |

+-------+---------+------------+--------+--------------+

5 rows in set (0.00 sec)

Experiment -2

1. Create Student table for the following fields:

(studentNo, studentName, maths, physics, chemistry, cProgramming, department, address)

1. List the studentno and studentname who is having marks less than 50 in C-programming in ascending order of their marks.
2. List the studentno, studentname and department, in descending order of their department and ascending order of their name.

3. List the name of students whose name starts with ‘S’.

4. Find the name of student who obtained second largest mark in in C-programming.

5. List the number of students in each department along with their average mark in maths.

6. List the number of students who have secured marks between 70 and 80 in physics.

mysql> select * from student;

+-----------+-------------+-------+---------+-----------+--------------+------------+-----------+

| studentNo | studentName | maths | physics | chemistry | cProgramming | department |     

address   |

+-----------+-------------+-------+---------+-----------+--------------+------------+-----------+

|       100 | Hari        |    50 |      60 |        45 |           75 | CSE        | Kasaragod |

|       101 | Devi        |    60 |      55 |        78 |           40 | CSE        | Kasaragod |

|       102 | Sam         |    45 |      77 |        88 |           45 | IT         | Kannur    |

|       103 | SreeHari    |    90 |      75 |        77 |           60 | IT         | Calicut   |

|       104 | Rani        |    91 |      98 |        89 |           52 | ECE        | Kannur    |

|       105 | Raj         |    88 |      77 |        67 |           48 | CSE        | Palakkad  |

+-----------+-------------+-------+---------+-----------+--------------+------------+-----------+

6 rows in set (0.00 sec)

Experiment-3

 

Experiment-4

Experiment-5

 

Experiment-6

 

Create a table file_server(user_id,file_name,create_date). First two attributes are part of the primary key.

1. List all unique extensions of the files in the file server.
2. Write the SQL Query to display the user ids, file extensions and count of files in each extensions.
3. Identify all files created after seven days or before seven days from 15-Aug-2024;

mysql> select * from file_server;
+---------+--------------+-------------+
| user_id | file_name    | create_date |
+---------+--------------+-------------+
|       1 | debug.txt    | 2024-06-01  |
|       1 | exp.txt        | 2024-07-01  |
|       1 | string.java  | 2024-07-10  |
|       2 | add.c          | 2024-08-01  |
|       2 | sub.asm      | 2024-08-09  |
|       2 | sub2.Asm    | 2024-08-14  |
|       3 | even.c       | 2024-08-18  |
|       3 | fibonacci.c  | 2024-08-12  |
|       3 | Hello.txt    | 2024-08-27  |
|       3 | traffic.java | 2024-08-09  |
|       4 | EMP.TXT      | 2024-08-21  |
|       4 | employee.txt | 2024-08-19  |
+---------+--------------+-------------+

Experiment-7

Question Link:

Experiment-8

Question Link: 

 

Experiment-9

Network Lab Pgms

Pgm -1

TCP - Server Pgm

TCP - Client Pgm

TCP - Algorithm

Pgm -2

UDP - Server Pgm

UDP - Client Pgm

Pgm -3

Implement Link State Routing algorithm.

Sample-Output

Pgm - 4

Implement leaky bucket algorithm for congestion control.

Sample-Output 

Pgm - 5 (FTP)

Develop a file server which will provide the file requested by a client if it

exists. If not, the server sends appropriate message to the client. Server should also

send its process ID (PID) to clients for display along with the file or the message.

Sample-Output

Pgm - 6 

Implement Selective Repeat ARQ flow control protocol using UDP.

Sample-Output

 

Pgm - 7

Implement a Concurrent Server application to perform sorting using TCP to execute the program at a remote server. Client sends an array and its size to the server (using a structure variable), server sort the array and send back the result to the client. Client displays the result.

TCP - Server - Fork

TCP - Client

 

Pgm - 8

Sample Concurrent Server application using UDP.

UDP - Server - Fork

UDP - Client

 

 

 

Install MySQL on Ubuntu (22.04)

 

Follow the below commands to install MySQL on ubuntu 22.04 

For more details refer: https://linuxhint.com/install-mysql-on-ubuntu-22-04/

sudo apt update
sudo apt install mysql-server
systemctl is-active mysql
sudo pkill -f mysql_secure_installation
sudo mysql
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password by 'Root@123';

exit
sudo mysql_secure_installation
 mysql -u root -p

DBMS Lab Cycle 8

 

 8. Implement a banking environment with following tables, procedures and function.

bank_customer(cust_id,cust_name,email)

cust_id is the primary key and it must be auto generated by the databse system.

account(acc_no,acc_type,cust_id,balance)

acc_no is the primary key and cust_id is foreign key.

The account_type field will accept ONLY two values saving and current. 

if the account type is saving then the minimum balance should be RS.1000 however there is no minimum balance condition for the current account.

 

1. Import DATA of account table from a csv file. [Download CSV]

mysql> SHOW GLOBAL VARIABLES LIKE 'local_infile';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| local_infile  | OFF   |
+---------------+-------+
1 row in set (0.90 sec)

mysql> SET GLOBAL local_infile=1;
Query OK, 0 rows affected (0.05 sec)

mysql> SHOW GLOBAL VARIABLES LIKE 'local_infile';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| local_infile  | ON    |
+---------------+-------+
1 row in set (0.01 sec)

mysql> quit

mysql> mysql --local-infile=1 -u root -p

mysql> use DBNAME;

mysql> LOAD DATA LOCAL INFILE '/file_name.csv' INTO TABLE table_name FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 LINES;

2. create a function getBalance(acc_no) to get the balance details of a bank account.

 

3. create two procedures

    credit(acc_no,amount)

    debit(acc_no,amount)

Display appropriate messages of every operations and errors.

 

4. Use curosr and create a procedure to list all email ids of customer separated by semicolon;

(Refer: https://www.mysqltutorial.org/mysql-cursor/)

account

acc_no acc_type     cust_id balance

1001 saving 1 10000

1002 saving 2 20000

1003 saving 3 30000

2001 current 4 10000

2002 current 5 20000

bank_customer

cust_id cust_name email

1 hari hari@gmail.com

2 sam sam@gmail.com

3 sree sree@lbscek.ac.in

4 ali ali@lbscek.ac.in

5 john john@yahoo.co.in

select * from account;

select getBalance(1001) as currentBalance;

call credit(1001,2000); 12000

call credit(1004,1000); ERROR

call debit(1001,1000); 11000

call debit(1004,1000); ERROR

call debit(1001,10500); ERROR

call debit(1001,10000); 1000

call debit(2001,10000); 0

select getBalance(2001) as currentBalance;

select * from account;

Function and Procedures

 

===============================================

DELIMITER //

CREATE PROCEDURE GetNameByID(IN employeeId int(4))

BEGIN

SELECT * FROM employee WHERE empNo = employeeId;

END //

DELIMITER ;

call GetNameByID(2001);

===============================================

DELIMITER //

CREATE PROCEDURE CountByDept(IN deptName VARCHAR(25), OUT total INT)

BEGIN

SELECT count(empNo) INTO total FROM employee WHERE dept = deptName;

END //

DELIMITER ;

call CountByDept('HR',@total);

select @total;

===============================================

DELIMITER //

CREATE PROCEDURE set_counter(INOUT count INT(4),IN inc INT(4))

BEGIN

SET count = count + inc;

END //

DELIMITER ;

SET @counter = 1;

CALL set_counter(@counter,1); -- 2

CALL set_counter(@counter,1); -- 3

CALL set_counter(@counter,5); -- 8

SELECT @counter; -- 8

===============================================

DROP FUNCTION IF EXISTS countByDeptFn;

DELIMITER //

CREATE FUNCTION countByDeptFn(deptName VARCHAR(25)) RETURNS int DETERMINISTIC

BEGIN

DECLARE total INT;

SELECT count(empNo) into total FROM employee WHERE dept = deptName;

RETURN total;

END //

DELIMITER ;

select countByDeptFn('HR') as EmpCount;

DBMS Lab Cycle 7

 

  7. Create tables based on the following schema

Item(item_id,item_desc,qty_on_hand,unit_price,category);

Sales(sale_id,item_id,qty_sold, unit_price, total_price, tax_amount,grand_total);

item_id is primary key in Item table and item_id is foreign key in Sales table.

sale_id is primary key in Sales table.

    i. Create trigger for calculating total_price, tax_amount and grand_total in sales table while inserting values.

The tax rate of vaious category as shown below.

book (10%)

stationery (20%)

chocolate (30%)

other (5%)

    ii. Create a trigger to calculate the stock of an Item after sales of an Item.

select * from Item;

+---------+-----------+-------------+------------+------------+

| item_id | item_desc | qty_on_hand | unit_price | category   |

+---------+-----------+-------------+------------+------------+

|     100 | TextBook  |          20 |         20 | book       |

|     101 | milkybar  |          30 |          5 | chocolate  |

|     102 | apple     |          50 |         10 | fruit      |

|     103 | pen       |         100 |          8 | stationery |

+---------+-----------+-------------+------------+------------+

4 rows in set (0.00 sec)

insert into Sales(sale_id,item_id,qty_sold) values(200,100,10);

insert into Sales(sale_id,item_id,qty_sold) values(201,101,25);

insert into Sales(sale_id,item_id,qty_sold) values(202,102,10);

insert into Sales(sale_id,item_id,qty_sold) values(203,103,20);

 select * from Sales;

+---------+---------+----------+------------+-------------+------------+-------------+

| sale_id | item_id | qty_sold | unit_price | total_price | tax_amount | grand_total |

+---------+---------+----------+------------+-------------+------------+-------------+

|     200 |     100 |       10 |         20 |         200 |         20 |         220 |

|     201 |     101 |       25 |          5 |         125 |         38 |         163 |

|     202 |     102 |       10 |         10 |         100 |          5 |         105 |

|     203 |     103 |       20 |          8 |         160 |         32 |         192 |

+---------+---------+----------+------------+-------------+------------+-------------+

4 rows in set (0.00 sec)

select * from Item;

+---------+-----------+-------------+------------+------------+

| item_id | item_desc | qty_on_hand | unit_price | category   |

+---------+-----------+-------------+------------+------------+

|     100 | TextBook  |          10 |         20 | book       |

|     101 | milkybar  |           5 |          5 | chocolate  |

|     102 | apple     |          40 |         10 | fruit      |

|     103 | pen       |          80 |          8 | stationery |

+---------+-----------+-------------+------------+------------+

4 rows in set (0.00 sec)

Trigger

Trigger

========================Insert =========================

mysql> create table table1(

-> var1 int(3) NOT NULL,

-> var2 int(3) NOT NULL,

-> sum int(3) NULL

-> );

Query OK, 0 rows affected (0.52 sec)

mysql>

mysql> Create trigger updateSumTable1

-> Before insert

-> On table1

-> For each row

-> Set new.sum=new.var1+new.var2;

Query OK, 0 rows affected (0.08 sec)

mysql>

mysql> insert into table1 values(3,6,null);

Query OK, 1 row affected (0.08 sec)

mysql> select * from table1;

+------+------+------+

| var1 | var2 | sum |

+------+------+------+

| 3 | 6 | 9 |

+------+------+------+

1 row in set (0.10 sec)

========================Delete=========================

mysql> create table table2(

-> var1 int(3) NOT NULL,

-> var2 int(3) NOT NULL,

-> sum int(3) NULL

-> );

Query OK, 0 rows affected (0.52 sec)

mysql> Create trigger updateTable2

-> After delete-> On table1

-> For each row

-> insert into table2 values(old.var1,old.var2,old.sum);

Query OK, 0 rows affected (0.09 sec)

mysql>

mysql> select * from table2;

Empty set (0.00 sec)

mysql>

mysql> delete from table1 where var1=3;

Query OK, 1 row affected (0.11 sec)

mysql> select * from table1;

Empty set (0.00 sec)

mysql> select * from table2;

+------+------+------+

| var1 | var2 | sum |

+------+------+------+

| 3 | 6 | 9 |

+------+------+------+

1 row in set (0.00 sec)

========================UPDATE=========================

mysql> create table table3(

-> var int NOT NULL,

-> sum int NULL

-> );

Query OK, 0 rows affected (0.44 sec)

mysql> DELIMITER //

mysql> Create trigger updateSumTable3

-> Before update

-> On table3

-> For each row

-> begin

-> if(new.var>old.var) then

-> Set new.sum=old.sum+new.var;

-> end if;

-> END //

Query OK, 0 rows affected (0.13 sec)

mysql> DELIMITER ;

mysql> insert into table3 values(3,3);Query OK, 1 row affected (0.12 sec)

mysql> select * from table3;

+-----+------+

| var | sum |

+-----+------+

| 3 | 3 |

+-----+------+

1 row in set (0.00 sec)

mysql> update table3 set var=5 where var=3;

Query OK, 1 row affected (0.18 sec)

Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from table3;

+-----+------+

| var | sum |

+-----+------+

| 5 | 8 |

+-----+------+

1 row in set (0.00 sec)

mysql> update table3 set var=2 where var=5;

Query OK, 1 row affected (0.36 sec)

Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from table3;

+-----+------+

| var | sum |

+-----+------+

| 2 | 8 |

+-----+------+

1 row in set (0.00 sec)

');

https://drive.google.com/file/d/1FTrB6tlzTCDmkdc_O0Dg9m2Q4wEGXCil/view?usp=share_link