INVENTORY MANAGEMENT SYSTEM

MYSQL QUERIES

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

Table 1 =

mysql> create table Admin(Admin_id int PRIMARY KEY, U_name varchar(30)

NOT NULL, Pswd varchar(15) NOT NULL);
Query OK, 0 rows affected (0.15 sec)

mysql> Insert Into Admin (Admin_id, U_name, Pswd )

values(2375,"RiyaS","Riya#22"),(2376,"SakshiT","Sakshi#22"),(2380,"Dny
aneshwariG","Dnyaneshwari#22"),(2382,"AishwaryaP","Aishwarya#22");
Query OK, 4 rows affected (0.14 sec)
Records: 4 Duplicates: 0 Warnings: 0

mysql> select * from Admin;

+----------+---------------+-----------------+
| Admin_id | U_name | Pswd |
+----------+---------------+-----------------+
| 2375 | RiyaS | Riya#22 |
| 2376 | SakshiT | Sakshi#22 |
| 2380 | DnyaneshwariG | Dnyaneshwari#22 |
| 2382 | AishwaryaP | Aishwarya#22 |
+----------+---------------+-----------------+
4 rows in set (0.00 sec)

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

Table 2 = Product

mysql> Create table Product (PId int PRIMARY KEY, PName varchar(50)
NOT NULL, Price int NOT NULL, Category varchar(50) NOT NULL, Quantity
int NOT NULL, Admin_id int );
Query OK, 0 rows affected (0.09 sec)
mysql> Insert into Product values(1,"Lenovo
i7","36000","Laptop","20","2382");
Query OK, 1 row affected (0.05 sec)

mysql> Alter Table Product Add Constraint fk_Adm foreign key

(Admin_id) references Admin(Admin_id);
Query OK, 1 row affected (0.38 sec)
Records: 1 Duplicates: 0 Warnings: 0

mysql>

mysql> Insert into Product values(1,"Lenovo

i7","36000","Laptop","20","2382");
ERROR 1062 (23000): Duplicate entry '1' for key 'product.PRIMARY'
mysql> Insert into Product values(2,"Apple
MacBook","40000","Laptop","35","2380");
Query OK, 1 row affected (0.12 sec)

mysql> Insert into Product values(3,"Apple

22","80000","Laptop","35","2376");
Query OK, 1 row affected (0.13 sec)=

mysql> Insert into Product

values(4,"Dell","60000","Laptop","35","2375");
Query OK, 1 row affected (0.12 sec)

mysql> Insert into Product values(5,"HP intel

i5","50000","Laptop","10","2380");
Query OK, 1 row affected (0.12 sec)

mysql> select * from Product;

+-----+---------------+-------+----------+----------+----------+
| PId | PName | Price | Category | Quantity | Admin_id |
+-----+---------------+-------+----------+----------+----------+
| 1 | Lenovo i7 | 36000 | Laptop | 20 | 2382 |
| 2 | Apple MacBook | 40000 | Laptop | 35 | 2380 |
| 3 | Apple 22 | 80000 | Laptop | 35 | 2376 |
| 4 | Dell | 60000 | Laptop | 35 | 2375 |
| 5 | HP intel i5 | 50000 | Laptop | 10 | 2380 |
+-----+---------------+-------+----------+----------+----------+
5 rows in set (0.00 sec)

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

Table 3 =

mysql> create table customer(custId int PRIMARY KEY, custName

varchar(50), addr varchar(50) NOT NULL, phno bigint NOT NULL);
Query OK, 0 rows affected (10.06 sec)

mysql> insert into customer (custId , custName , addr , phno)

values(1,"Hari","Pune",9845635463),(2,"Sam","Kothrud",8765436452),(3,"
Tom","Pune",9585658745),(4,"Jerry","Nashik",8675647567);
Query OK, 4 rows affected (0.26 sec)
Records: 4 Duplicates: 0 Warnings: 0

mysql> select * from customer;

+--------+----------+---------+------------+
| custId | custName | addr | phno |
+--------+----------+---------+------------+
| 1 | Hari | Pune | 9845635463 |
| 2 | Sam | Kothrud | 8765436452 |
| 3 | Tom | Pune | 9585658745 |
| 4 | Jerry | Nashik | 8675647567 |
+--------+----------+---------+------------+
4 rows in set (0.00 sec)

mysql> alter table transaction modify tid int(10) AUTO_INCREMENT ;

Query OK, 5 rows affected, 1 warning (4.26 sec)
Records: 5 Duplicates: 0 Warnings: 1

mysql> insert into transaction

values(null,6,"Watch2",2,"2022-05-21",3);
Query OK, 1 row affected (0.27 sec)

mysql> select * from transaction;

+-----+-----+---------------+----------+------------+--------+
| tid | pid | pname | quantity | time_date | custId |
+-----+-----+---------------+----------+------------+--------+
| 101 | 1 | Lenovo i7 | 50 | 2022-03-03 | 1 |
| 102 | 2 | Apple MacBook | 2 | 2022-03-05 | 2 |
| 103 | 3 | Apple 22 | 1 | 2022-03-06 | 3 |
| 104 | 4 | Dell | 1 | 2022-01-10 | 1 |
| 105 | 5 | HP intel i5 | 1 | 2021-12-31 | 3 |
| 106 | 6 | Watch2 | 2 | 2022-05-21 | 3 |
+-----+-----+---------------+----------+------------+--------+
6 rows in set (0.00 sec)

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

Table 4 =

mysql> create table transaction (tid int PRIMARY KEY, pid int NOT
NULL, pname varchar(50) NOT NULL, quantity int NOT NULL, time_date
Date NOT NULL);
Query OK, 0 rows affected (1.74 sec)

mysql> alter table transaction add column custId int ;

Query OK, 0 rows affected (1.74 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> alter table transaction add constraint fk_cust foreign key

(custId) references customer(custId);
Query OK, 0 rows affected (4.67 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> insert into transaction (tid , pid , pname , quantity ,

time_date , custId) values(102,2,"Apple MacBook", 2 , "2022-03-05",
2);
Query OK, 1 row affected (0.11 sec)

mysql> insert into transaction (tid , pid , pname , quantity ,

time_date , custId) values(103,3,"Apple 22", 1 , "2022-03-06",
3),(104,4,"Dell",1,"2022-01-10",1),(105,5,"HP intel
i5",1,"2021-12-31",3);
Query OK, 3 rows affected (0.16 sec)
Records: 3 Duplicates: 0 Warnings: 0

mysql> select * from transaction

-> ;
+-----+-----+---------------+----------+------------+--------+
| tid | pid | pname | quantity | time_date | custId |
+-----+-----+---------------+----------+------------+--------+
| 101 | 1 | Lenovo i7 | 50 | 2022-03-03 | 1 |
| 102 | 2 | Apple MacBook | 2 | 2022-03-05 | 2 |
| 103 | 3 | Apple 22 | 1 | 2022-03-06 | 3 |
| 104 | 4 | Dell | 1 | 2022-01-10 | 1 |
| 105 | 5 | HP intel i5 | 1 | 2021-12-31 | 3 |
+-----+-----+---------------+----------+------------+--------+
5 rows in set (0.00 sec)

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

Table 5 =

mysql> create table updates (custId int, pid int);

Query OK, 0 rows affected (1.38 sec)

mysql> alter table updates add constraint fk_cust1 foreign key

(custId) references customer(custId);
Query OK, 0 rows affected (2.86 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> alter table updates add constraint fk_cust2 foreign key (pid)
references Product(Pid);
Query OK, 0 rows affected (3.25 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> insert into updates(custId , pid)

values(1,1),(2,2),(3,3),(4,4);
Query OK, 4 rows affected (0.13 sec)
Records: 4 Duplicates: 0 Warnings: 0

mysql> select * from updates;

+--------+------+
| custId | pid |
+--------+------+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
| 4 | 4 |
+--------+------+
4 rows in set (0.00 sec)

============================================================
QUERIES

1.Display all the customers whose second letter is ‘a’.

mysql> select * from customer where custName like "_a%";

+--------+----------+---------+------------+
| custId | custName | addr | phno |
+--------+----------+---------+------------+
| 1 | Hari | Pune | 9845635463 |
| 2 | Sam | Kothrud | 8765436452 |
| 18 | Sam | Pune | 9878675678 |
| 23 | Sam2 | Pune | 7658465789 |
| 25 | Sam | Pune | 7776665558 |
| 34 | Jack | Pune | 7777777777 |
| 99 | Sam | Pune | 8978987889 |
| 123 | Sam2 | Pune | 8787878797 |
+--------+----------+---------+------------+
8 rows in set (0.03 sec)

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

2.Display all the products between the price range 100000 and 200000.

mysql> select * from Product where Price between 100000 and 200000;
+-----+-------------------------+--------+------------+----------+----
------+
| PId | PName | Price | Category | Quantity |
Admin_id |
+-----+-------------------------+--------+------------+----------+----
------+
| 10 | Samsung Washing Machine | 150000 | Household | 89 |
2376 |
| 13 | Whirlpool Refrigerator | 200000 | Household | 80 |
2376 |
| 101 | Samsung TV | 200000 | Television | 90 |
2376 |
+-----+-------------------------+--------+------------+----------+----
------+
3 rows in set (0.00 sec)

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

3.Display Date format YYYY-MM-DD.

mysql> select date_format(time_date,"%Y%M%D") from transaction;

+---------------------------------+
| date_format(time_date,"%Y%M%D") |
+---------------------------------+
| 2022May25th |
| 2022May25th |
| 2022May25th |
| 2022May25th |
| 2022May25th |
| 2022May25th |
| 2022May25th |
| 2022May26th |
| 2022May26th |
| 2022June1st |
+---------------------------------+
10 rows in set (0.11 sec)

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

4.Display the customer name in alphabetical order.

mysql> select * from customer order by custName;

+--------+----------+----------+------------+
| custId | custName | addr | phno |
+--------+----------+----------+------------+
| 12 | Anu | Pune | 9988776655 |
| 1 | Hari | Pune | 9845635463 |
| 34 | Jack | Pune | 7777777777 |
| 4 | Jerry | Nashik | 8675647567 |
| 111 | Purva | Hadapsar | 8888888888 |
| 2 | Sam | Kothrud | 8765436452 |
| 18 | Sam | Pune | 9878675678 |
| 25 | Sam | Pune | 7776665558 |
| 99 | Sam | Pune | 8978987889 |
| 23 | Sam2 | Pune | 7658465789 |
| 123 | Sam2 | Pune | 8787878797 |
| 3 | Tom | Pune | 9585658745 |
| 22 | Tom | Kothrud | 7677767678 |
+--------+----------+----------+------------+
13 rows in set (0.02 sec)

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

5.Display products between the price range 10000 and 400000.

mysql> select * from Product group by Category having Price between

10000 and 400000;
+-----+-------------------------+--------+-------------+----------+---
-------+
| PId | PName | Price | Category | Quantity |
Admin_id |
+-----+-------------------------+--------+-------------+----------+---
-------+
| 1 | Lenovo i7 | 36000 | Laptop | 8 |
2382 |
| 10 | Samsung Washing Machine | 150000 | Household | 89 |
2376 |
| 14 | Motorola A5 | 30000 | Smart Phone | 23 |
2376 |
| 24 | iPhone | 50000 | Mobile | 45 |
2375 |
| 26 | Apple TV | 400000 | Television | 99 |
2376 |
+-----+-------------------------+--------+-------------+----------+---
-------+
5 rows in set (0.09 sec)

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

6.Use Inner Join to join Customer and Transaction table

mysql> Select * from transaction inner join customer on

transaction.custId=customer.custId;
+-----+-----+---------------+----------+------------+--------+--------
-----+--------+----------+---------+------------+
| tid | pid | pname | quantity | time_date | custId |
Total_price | custId | custName | addr | phno |
+-----+-----+---------------+----------+------------+--------+--------
-----+--------+----------+---------+------------+
| 112 | 1 | Lenovo i7 | 2 | 2022-05-25 | 1 |
72000 | 1 | Hari | Pune | 9845635463 |
| 113 | 1 | Lenovo i7 | 3 | 2022-05-25 | 1 |
108000 | 1 | Hari | Pune | 9845635463 |
| 114 | 1 | Lenovo i7 | 4 | 2022-05-25 | 1 |
144000 | 1 | Hari | Pune | 9845635463 |
| 115 | 1 | Lenovo i7 | 1 | 2022-05-25 | 1 |
36000 | 1 | Hari | Pune | 9845635463 |
| 116 | 1 | Lenovo i7 | 1 | 2022-05-25 | 1 |
36000 | 1 | Hari | Pune | 9845635463 |
| 117 | 1 | Lenovo i7 | 1 | 2022-05-25 | 1 |
36000 | 1 | Hari | Pune | 9845635463 |
| 118 | 1 | Lenovo i7 | 1 | 2022-05-25 | 1 |
36000 | 1 | Hari | Pune | 9845635463 |
| 119 | 2 | Apple MacBook | 3 | 2022-05-26 | 1 |
120000 | 1 | Hari | Pune | 9845635463 |
| 120 | 3 | Apple 22 | 5 | 2022-05-26 | 3 |
400000 | 3 | Tom | Pune | 9585658745 |
| 123 | 1 | Lenovo i7 | 1 | 2022-06-01 | 2 |
36000 | 2 | Sam | Kothrud | 8765436452 |
+-----+-----+---------------+----------+------------+--------+--------
-----+--------+----------+---------+------------+
10 rows in set (0.00 sec)

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

7. Use LEFT Outer join to join Customer and Transaction tables

mysql> Select * from transaction left outer join customer on

transaction.custId=customer.custId;
+-----+-----+---------------+----------+------------+--------+--------
-----+--------+----------+---------+------------+
| tid | pid | pname | quantity | time_date | custId |
Total_price | custId | custName | addr | phno |
+-----+-----+---------------+----------+------------+--------+--------
-----+--------+----------+---------+------------+
| 112 | 1 | Lenovo i7 | 2 | 2022-05-25 | 1 |
72000 | 1 | Hari | Pune | 9845635463 |
| 113 | 1 | Lenovo i7 | 3 | 2022-05-25 | 1 |
108000 | 1 | Hari | Pune | 9845635463 |
| 114 | 1 | Lenovo i7 | 4 | 2022-05-25 | 1 |
144000 | 1 | Hari | Pune | 9845635463 |
| 115 | 1 | Lenovo i7 | 1 | 2022-05-25 | 1 |
36000 | 1 | Hari | Pune | 9845635463 |
| 116 | 1 | Lenovo i7 | 1 | 2022-05-25 | 1 |
36000 | 1 | Hari | Pune | 9845635463 |
| 117 | 1 | Lenovo i7 | 1 | 2022-05-25 | 1 |
36000 | 1 | Hari | Pune | 9845635463 |
| 118 | 1 | Lenovo i7 | 1 | 2022-05-25 | 1 |
36000 | 1 | Hari | Pune | 9845635463 |
| 119 | 2 | Apple MacBook | 3 | 2022-05-26 | 1 |
120000 | 1 | Hari | Pune | 9845635463 |
| 120 | 3 | Apple 22 | 5 | 2022-05-26 | 3 |
400000 | 3 | Tom | Pune | 9585658745 |
| 123 | 1 | Lenovo i7 | 1 | 2022-06-01 | 2 |
36000 | 2 | Sam | Kothrud | 8765436452 |
+-----+-----+---------------+----------+------------+--------+--------
-----+--------+----------+---------+------------+
10 rows in set (0.00 sec)

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

8. Use RIGHT Outer join to join Customer and Transaction tables

mysql> Select * from transaction right outer join customer on

transaction.custId=customer.custId;
+------+------+---------------+----------+------------+--------+------
-------+--------+----------+----------+------------+
| tid | pid | pname | quantity | time_date | custId |
Total_price | custId | custName | addr | phno |
+------+------+---------------+----------+------------+--------+------
-------+--------+----------+----------+------------+
| 112 | 1 | Lenovo i7 | 2 | 2022-05-25 | 1 |
72000 | 1 | Hari | Pune | 9845635463 |
| 113 | 1 | Lenovo i7 | 3 | 2022-05-25 | 1 |
108000 | 1 | Hari | Pune | 9845635463 |
| 114 | 1 | Lenovo i7 | 4 | 2022-05-25 | 1 |
144000 | 1 | Hari | Pune | 9845635463 |
| 115 | 1 | Lenovo i7 | 1 | 2022-05-25 | 1 |
36000 | 1 | Hari | Pune | 9845635463 |
| 116 | 1 | Lenovo i7 | 1 | 2022-05-25 | 1 |
36000 | 1 | Hari | Pune | 9845635463 |
| 117 | 1 | Lenovo i7 | 1 | 2022-05-25 | 1 |
36000 | 1 | Hari | Pune | 9845635463 |
| 118 | 1 | Lenovo i7 | 1 | 2022-05-25 | 1 |
36000 | 1 | Hari | Pune | 9845635463 |
| 119 | 2 | Apple MacBook | 3 | 2022-05-26 | 1 |
120000 | 1 | Hari | Pune | 9845635463 |
| 123 | 1 | Lenovo i7 | 1 | 2022-06-01 | 2 |
36000 | 2 | Sam | Kothrud | 8765436452 |
| 120 | 3 | Apple 22 | 5 | 2022-05-26 | 3 |
400000 | 3 | Tom | Pune | 9585658745 |
| NULL | NULL | NULL | NULL | NULL | NULL |
NULL | 4 | Jerry | Nashik | 8675647567 |
| NULL | NULL | NULL | NULL | NULL | NULL |
NULL | 12 | Anu | Pune | 9988776655 |
| NULL | NULL | NULL | NULL | NULL | NULL |
NULL | 18 | Sam | Pune | 9878675678 |
| NULL | NULL | NULL | NULL | NULL | NULL |
NULL | 22 | Tom | Kothrud | 7677767678 |
| NULL | NULL | NULL | NULL | NULL | NULL |
NULL | 23 | Sam2 | Pune | 7658465789 |
| NULL | NULL | NULL | NULL | NULL | NULL |
NULL | 25 | Sam | Pune | 7776665558 |
| NULL | NULL | NULL | NULL | NULL | NULL |
NULL | 34 | Jack | Pune | 7777777777 |
| NULL | NULL | NULL | NULL | NULL | NULL |
NULL | 99 | Sam | Pune | 8978987889 |
| NULL | NULL | NULL | NULL | NULL | NULL |
NULL | 111 | Purva | Hadapsar | 8888888888 |
| NULL | NULL | NULL | NULL | NULL | NULL |
NULL | 123 | Sam2 | Pune | 8787878797 |
+------+------+---------------+----------+------------+--------+------
-------+--------+----------+----------+------------+
20 rows in set (0.02 sec)

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

9.Display Transaction done by Hari

mysql> select * from transaction where custId in (select custId from

customer where custName="Hari");
+-----+-----+---------------+----------+------------+--------+--------
-----+
| tid | pid | pname | quantity | time_date | custId |
Total_price |
+-----+-----+---------------+----------+------------+--------+--------
-----+
| 112 | 1 | Lenovo i7 | 2 | 2022-05-25 | 1 |
72000 |
| 113 | 1 | Lenovo i7 | 3 | 2022-05-25 | 1 |
108000 |
| 114 | 1 | Lenovo i7 | 4 | 2022-05-25 | 1 |
144000 |
| 115 | 1 | Lenovo i7 | 1 | 2022-05-25 | 1 |
36000 |
| 116 | 1 | Lenovo i7 | 1 | 2022-05-25 | 1 |
36000 |
| 117 | 1 | Lenovo i7 | 1 | 2022-05-25 | 1 |
36000 |
| 118 | 1 | Lenovo i7 | 1 | 2022-05-25 | 1 |
36000 |
| 119 | 2 | Apple MacBook | 3 | 2022-05-26 | 1 |
120000 |
+-----+-----+---------------+----------+------------+--------+--------
-----+
8 rows in set (0.00 sec)

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

10. Create view

mysql> create view view1 as select tid from transaction;

Query OK, 0 rows affected (1.00 sec)

mysql> select * from transaction;

+-----+-----+---------------+----------+------------+--------+--------
-----+
| tid | pid | pname | quantity | time_date | custId |
Total_price |
+-----+-----+---------------+----------+------------+--------+--------
-----+
| 112 | 1 | Lenovo i7 | 2 | 2022-05-25 | 1 |
72000 |
| 113 | 1 | Lenovo i7 | 3 | 2022-05-25 | 1 |
108000 |
| 114 | 1 | Lenovo i7 | 4 | 2022-05-25 | 1 |
144000 |
| 115 | 1 | Lenovo i7 | 1 | 2022-05-25 | 1 |
36000 |
| 116 | 1 | Lenovo i7 | 1 | 2022-05-25 | 1 |
36000 |
| 117 | 1 | Lenovo i7 | 1 | 2022-05-25 | 1 |
36000 |
| 118 | 1 | Lenovo i7 | 1 | 2022-05-25 | 1 |
36000 |
| 119 | 2 | Apple MacBook | 3 | 2022-05-26 | 1 |
120000 |
| 120 | 3 | Apple 22 | 5 | 2022-05-26 | 3 |
400000 |
| 123 | 1 | Lenovo i7 | 1 | 2022-06-01 | 2 |
36000 |
+-----+-----+---------------+----------+------------+--------+--------
-----+
10 rows in set (0.11 sec)

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

11. Create Index

mysql> create index ind1 on Product(PId);

Query OK, 0 rows affected (1.88 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> select * from Product;

+-----+---------------------------+--------+-------------+----------+-
---------+
| PId | PName | Price | Category | Quantity |
Admin_id |
+-----+---------------------------+--------+-------------+----------+-
---------+
| 1 | Lenovo i7 | 36000 | Laptop | 8 |
2382 |
| 2 | Apple MacBook | 40000 | Laptop | 22 |
2380 |
| 3 | Apple 22 | 80000 | Laptop | 33 |
2376 |
| 4 | Dell | 60000 | Laptop | 33 |
2375 |
| 5 | HP intel i5 | 50000 | Laptop | 33 |
2380 |
| 6 | SmartWatch | 2999 | Watch | 33 |
2382 |
| 7 | Titan | 5000 | Watch | 19 |
2376 |
| 8 | SmartWatch2 | 1500 | Watch | 33 |
2382 |
| 9 | Allen solly | 7500 | Watch | 5 |
2376 |
| 10 | Samsung Washing Machine | 150000 | Household | 89 |
2376 |
| 11 | Whirlpool Washing Machine | 90000 | Household | 50 |
2376 |
| 12 | LG Microwave | 69000 | Household | 45 |
2376 |
| 13 | Whirlpool Refrigerator | 200000 | Household | 80 |
2376 |
| 14 | Motorola A5 | 30000 | Smart Phone | 23 |
2376 |
| 23 | Camera 255 | 3999 | Camera | 7 |
2382 |
| 24 | iPhone | 50000 | Mobile | 45 |
2375 |
| 25 | DSLR Camera | 22000 | Camera | 83 |
2376 |
| 26 | Apple TV | 400000 | Television | 99 |
2376 |
| 43 | Canon Camera | 65000 | Camera | 32 |
2376 |
| 45 | Samsung A52 | 28000 | Smart Phone | 90 |
2376 |
| 48 | OnePlus TV | 60000 | Television | 80 |
2376 |
| 71 | LG TV | 40000 | Television | 60 |
2376 |
| 78 | Vivo V20 | 20000 | Smart Phone | 67 |
2376 |
| 79 | Sony Camera | 70000 | Camera | 23 |
2376 |
| 89 | Redmi Note 6 | 15000 | Smart Phone | 45 |
2376 |
| 90 | Bajaj Oven | 56000 | Household | 12 |
2376 |
| 91 | Oppo A21 | 30000 | Smart Phone | 35 |
2376 |
| 92 | LG Series Camera | 6590 | Camera | 23 |
2376 |
| 100 | W series Camera | 90000 | Camera | 78 |
2376 |
| 101 | Samsung TV | 200000 | Television | 90 |
2376 |
+-----+---------------------------+--------+-------------+----------+-
---------+
30 rows in set (0.00 sec)

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

12. Write a procedure to update the Price to 10050

mysql> DELIMITER $$
mysql> Create Procedure update_price(pid int,pname varchar(30))
-> begin
-> update Product set Price=10050 where PId=pid and PName=pname;
-> end $$
Query OK, 0 rows affected (0.26 sec)

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

13.Trigger to convert product name to uppercase before inserting a new

record in transaction table.

mysql> delimiter //
mysql> create trigger tr11
-> before insert on transaction
-> for each row
-> begin
-> set new.PName=upper(new.PName);
-> end//
Query OK, 0 rows affected (0.88 sec)

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