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

DBMS Lab Cycle 5

   5. The schema of book store database is as follows

customer (cust_id, cust_name, cust_phone)

book (book_id, title, auth_name, unit_price, pub_name, pub_year)

sale (sale_id, cust_id, book_id, order_date, quantity)

author(auth_name, country)

publisher(pub_name, pub_addr)

first attribute in each table is the primary key, foreign keys have same name as primary key.

i. Calculate the sales for a financial year 2021-22? 

ii. Display publisher wise total price of books published, except for a particular publisher PHI

iii. Write the SQL Query to Find the publisher wise total price of books published

iv. Write the SQL Query to display the name of the customer and title of the books purchased by them.

v.Find the title of books with author name and country published in year 2021;

vi. Get the titles and author name of all books written by authors not living in india.

vii.Write the SQL Query to display the cusomer ids and the total quantity of books purchased by them in the decreasing order of total quantity purcased.

TEST CASES

mysql> select * from customer;

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

| cust_id | cust_name | cust_phone |

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

|     100 | hari      |      12345 |

|     101 | john      |      23456 |

|     102 | ali       |      34567 |

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

mysql> select * from author;

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

| auth_name | country |

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

| Brown     | US      |

| Forouzan  | US      |

| Navathe   | India   |

| Samantha  | India   |

| Tanenbaum | US      |

| V Prasad  | India   |

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

mysql> select * from publisher;

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

| pub_name | pub_addr |

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

| Pearson  | London   |

| PHI      | Delhi    |

| TMH      | UP       |

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

mysql> select * from book;

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

| book_id | title  | auth_name | unit_price | pub_name | pub_year |

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

|    2001 | DS     | Samantha  |        500 | PHI      |     2020 |

|    2002 | CP     | V Prasad  |        300 | TMH      |     2019 |

|    2003 | DBMS   | Navathe   |        900 | Pearson  |     2018 |

|    2004 | DC     | Forouzan  |        700 | TMH      |     2021 |

|    2005 | CN     | Tanenbaum |        800 | Pearson  |     2018 |

|    2006 | Python | Brown     |        800 | TMH      |     2021 |

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

mysql> select * from sale;

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

| sale_id | cust_id | book_id | order_date | quantity |

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

|     301 |     100 |    2001 | 2021-01-02 |        3 |

|     302 |     100 |    2002 | 2019-04-02 |        4 |

|     303 |     100 |    2003 | 2019-04-02 |        2 |

|     304 |     101 |    2002 | 2021-05-02 |        2 |

|     305 |     101 |    2005 | 2021-05-02 |        1 |

|     306 |     102 |    2006 | 2022-01-02 |        2 |

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

DBMS Lab Cycle 4

 Create view for following. 

i.            View (student_info) for student table(exp no:2) containing studentNo, studentName, department, address

ii.            View (student_mark) for student table(exp no:2) containing studentNo, studentName, maths, physics, chemistry, cProgramming.

iii.            Display the studentNo, studentName, totalMarks, address from student_info view and student_mark view without referring student table.  

iv.            Create a view (emp_dept) for exp no:3 containing employee name and department name. (NULL values are expected in the department name field for the employees having invalid department numbers).

  v.            Find the name of employees having invalid department numbers by referring the emp_dept view.