Assignment 2

Student Name: Maitreyee Kaushik Shendye

USN Number: UCE2023460
Division: A
Batch: A3
Problem Statement: A company wants to manage its customers, items,
and purchase transactions. They have provided the following data
elements:
Customer: Customer number, customer name, and customer city.
Item: Item number, item name, item type, item price, and item count
(quantity in stock).
Purchase: Purchase number, purchase date, purchase quantity,
customer number, and item number.
Design and create database tables for the given entities, ensuring data
integrity and consistency.

Query statements and output:

1. Display Customer table, Item table and Purchase table.

mysql> show databases;

6 rows in set (0.01 sec)

mysql> use my__shop;
Database changed
mysql> show tables;
3 rows in set (0.01 sec)
mysql> desc customer;

4 rows in set (0.01 sec)

mysql> desc items;

5 rows in set (0.00 sec)

mysql> desc purchase;

5 rows in set (0.00 sec)

mysql> select * from customer;
 10 rows in set (0.00 sec)

mysql> select * from items;

10 rows in set (0.00 sec)

mysql> select * from purchase

10 rows in set (0.00 sec)

2. Update price of item 1009 as 580.
mysql> update items set i_price = 580 where i_num=1009;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from items;

 10 rows in set (0.00 sec)
3. Delete purchase record no 510
mysql> delete from purchase where p_num = 510;
Query OK, 1 row affected (0.01 sec)

mysql> select * from purchase;

9 rows in set (0.00 sec)

4. Display the Customer table, Item table and Purchase table
List all the records of customer tables.
mysql> select * from customer;
 10 rows in set (0.00 sec)
5. Display name and type of item 1001
mysql> select i_name,i_type from items where i_num = 1001;

1 row in set (0.00 sec)

6. Display items in the store with following details :item
name,item type and count
mysql> select i_name,i_type,i_count from items;

10 rows in set (0.00 sec)

7. Display items in the store in the ascending order of cost.
mysql> select * from items order by i_price asc;

10 rows in set (0.00 sec)

8. Display the items in the store by descending quantity
mysql> select * from items order by i_count desc;
 10 rows in set (0.00 sec)
9. Display the items whose price is more than 500
mysql> select * from items where i_price>500;

4 rows in set (0.00 sec)

10. Display the item with minimum price.
mysql> select I1.i_num,I1.i_name,I1.i_type,I1.i_price,I1.i_count from
items as I1, (select min(i_price) as min_price from items) as I2 where

I1.i_price = I2.min_price;
1 row in set (0.01 sec)
11. Display the item with maximum price
 mysql> select I1.i_num,I1.i_name,I1.i_type,I1.i_price,I1.i_count from
items as I1, (select max(i_price) as max_price from items) as I2 where

I1.i_price = I2.max_price;
1 row in set (0.00 sec)
12. Display the customers having mobile number as 982
mysql> select * from customer where c_mobile like '982%';
Empty set (0.00 sec)

mysql> select * from customer where c_mobile like '957%';

1 row in set (0.00 sec)

13. Display customers who stays in Mumbai
mysql> select * from customer where c_city = 'Mumbai'

1 row in set (0.00 sec)

14. Display the details of customers whose name starts
with R
mysql> select * from customer where c_name like "R%";

3 rows in set (0.01 sec)

15. Display the names of the customers who has ‘h’ as the
third letter in their name
mysql> select * from customer where c_name like "__h%";
 Empty set (0.00 sec)
16. Display the name of the customers who live in the same
city where Jyoti lives
mysql> select c_num,c_name,c_city,c_mobile from customer as C1,
( select c_city as j_city from customer where c_name like "Jyoti%") as

C2 where C1.c_city = C2.j_city;

2 rows in set (0.00 sec)
17. Generate alphabetical list of customers.
mysql> select * from customer order by c_name;

10 rows in set (0.00 sec)

18. Display the item names in upper case
mysql> select Upper(i_name) from items;
 10 rows in set (0.01 sec)
19. Find average price of the items in the store
mysql> select avg(i_price) as average_price from items;

1 row in set (0.00 sec)

20. Display the price of items when incremented by 10% for
price > 400
mysql> update items set i_price = i_price+i_price*0.1 where i_pr
ice>400;
Query OK, 5 rows affected (0.02 sec)
Rows matched: 5 Changed: 5 Warnings: 0

mysql> select*from items;

10 rows in set (0.00 sec)

21. Display date and quantity for purchase no 510
mysql> select p_date,p_quantity from purchase where p_num = 510;

1 row in set (0.00 sec)

22. Write a query to remove customer 713.
mysql> insert into customer values
-> (711,'Aniket Kulkarni','Nashik',9443847293),
-> (712,'Sanika Shinde','Raigad',7665880384),
-> (713,'Shreya Deshpande','Satara',8447362626);
Query OK, 3 rows affected (0.01 sec)
 Records: 3 Duplicates: 0 Warnings: 0

mysql> select * from customer;

13 rows in set (0.00 sec)

mysql> delete from customer where c_num = 713;

Query OK, 1 row affected (0.01 sec)

mysql> select * from customer;

12 rows in set (0.00 sec)

23. Display count of records for table customer.
mysql> select count(*) as number_of_records from customer;
24. Display the cost of items when increased by 10%
mysql> update items set i_price = i_price+i_price*0.1;
Query OK, 10 rows affected (0.01 sec)
Rows matched: 10 Changed: 10 Warnings: 0

mysql> select*from items;

10 rows in set (0.00 sec)

25. Display sum of all item price
mysql> select sum(i_price) as total from items;

1 row in set (0.00 sec)

26. Display the highest item count.
mysql> select max(i_count) as highest_count from items;

1 row in set (0.00 sec)

27. Display current time in the default format as ‘YYYY-MM-
DD HH:MM:SS’
mysql> select now();
 1 row in set (0.00 sec)
28. Update the purchase record of customer with purchase
number 512. Set new purchase dates current date
mysql> insert into purchase values
-> (511,20190208,77,711,1001),
-> (512,20171117,17,708,1010),
-> (513,20121212,10,702,1006);
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0

mysql> select * from purchase;

12 rows in set (0.00 sec)

mysql> update purchase set p_date = current_date() where p_num =
512;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from purchase;

 12 rows in set (0.00 sec)

29. Display the purchase dates in following format

a) Month Day Year HH:MM:SS AM/PM
mysql> SELECT DATE_FORMAT(p_date, '%M %d %Y %h:%i:%s %p')
AS date from purchase;

12 rows in set (0.00 sec)

b) DD-MM-YYYY
mysql> SELECT DATE_FORMAT(p_date, '%d-%m-%y') AS date FROM

purchase;
12 rows in set (0.00 sec)
c) Day Month Year (dd-mm-yy)
 mysql> SELECT DATE_FORMAT(p_date, '%D %M %Y (%d-%m-%y)')
AS date FROM purchase;

12 rows in set (0.00 sec)

d) dd-mm-yyyy hh:mm:ss:ms
SELECT DATE_FORMAT(p_date, '%d-%m-%Y %H:%i:%s:%f') AS date
FROM purchase;

12 rows in set (0.00 sec)

30. Display list of all quantities with price in the range 100
to 500
mysql> select i_count as quantity from items where i_price between

100 and 500;

1 row in set (0.00 sec)
31. Display list of all items with price in the range 100 to
500 (use between clause)
mysql> select * from items where i_price between 100 and 500;

1 row in set (0.00 sec)

32. Display different item types registered with online
shopping cart. (Distinct)
mysql> select distinct(i_type) from items;

6 rows in set (0.00 sec)

33. Display the count of customer citywise (group by)
mysql> select c_city,count(*) as count from customer group by c_
city;

9 rows in set (0.00 sec)

34. Display the count of purchase done by every customer
 mysql> select distinct(c_num),c_name,count(p_num) as total_count
from customer left outer join purchase on

customer.c_num=purchase.c_no group by c_num;

12 rows in set (0.00 sec)

35. Display all quantity purchase by customer no 701 and
703 (Union)
mysql> select c_num,count(p_num) as total_count from customer left
outer join purchase on customer.c_num=purchase.c_no where
customer.c_num=701 group by c_num
-> union
-> select c_num,count(p_num) as total_count from customer left
outer join purchase on customer.c_num=purchase.c_no where
customer.c_num=703 group by c_num;

2 rows in set (0.01 sec)

36. Display only first three records of the members
table(limit)
mysql> SELECT * FROM Customer limit 3;
 3 rows in set (0.00 sec)
37. Display 5 records from customers table starting from
record 702. (limit)
mysql> SELECT * FROM Customer limit 1,5;

5 rows in set (0.00 sec)