Question 1 Create a new database with name upgraddbms.
If a database
of this name alreadyexists in your MySQL server, choose some other
name for this database.
Answer:-
CREATE DATABASE upgraddbms;
• Now create 3 tables with the following attributes and their data-
types. Remember that attributes with underline are primary key
attributes.
• product (product_id int, name varchar (50), quantity int, price
float, seller varchar(50))
CREATE TABLE product (
product_id int PRIMARY KEY,
name varchar(50),
quantity int,
price float,
seller varchar(50)
);
• customer (cust_id int, name varchar(50), address varchar(100))
CREATE TABLE customer (
cust_id int PRIMARY KEY,
name varchar(50),
address varchar(100)
);
• purchase (cust_id int, product_id int, dop date). In the purchase
table, cust_id and product_id are foreign key dependencies in
customer and product tables respectively.
CREATE TABLE purchase (
cust_id int,
product_id int,
dop date,
FOREIGN KEY (cust_id) REFERENCES customer(cust_id),
FOREIGN KEY (product_id) REFERENCES product(product_id)
);
• After creation of the tables, we realize that the product table must
have another attribute“unit” after quantity. Write the “alter table”
command to add this attribute after “quantity”.
ALTER TABLE product ADD unit varchar(50);
Question 2
• Add the following row to the product table (product_id int, name
varchar(50), quantity int, unit varchar(50), price float, seller
varchar(50)). The values are in thesame sequence as the
attributes mentioned a. 999, USB-C Charger, 1, pieces, 200,
Electron Traders
Answer:-
INSERT INTO product (product_id, name, quantity, unit, price,
seller)
VALUES (999, 'USB-C Charger', 1, 'pieces', 200, 'Electron
Traders');
• Add the following row to the customer table (cust_id int, name
varchar(50), addressvarchar(100)). The values are in the same
sequence as the attributes mentioned a. 7777, “Deep Kasturi”,
“Shivaji Nagar, Mumbai”
Anwer:-
INSERT INTO customer (cust_id, name, address)
VALUES (7777, 'Deep Kasturi', 'Shivaji Nagar, Mumbai');
• Update the cust_id of the row added in the previous question from
7777 to 7788.
Answer:-
UPDATE customer
SET cust_id = 7788
WHERE cust_id = 7777;
Question 3 :-
Write the following query using GROUP BY and HAVING clauses in MySQL.
From the purchase table, group all the rows according to cust_ids.
Choose a cust_id if that customer has made more than 2 purchases, and
also choose the total number of purchases the customer has done.
Answer :-
SELECT cust_id, COUNT(*) AS totalpurchases
FROM purchase
GROUP BY cust_id
HAVING COUNT(*) > 2;
