WRITE THE CODE BY SQL WITH PYTHON

ANSWER THE QUESTION BY USING THE CODE BELOW PLEASE!

Q: Define a function that reports the total price of the items with respect to their type. For example, the total price of sold paintings is 420000.

import mysql.connector

# Connect to the MySQL server

cnx = mysql.connector.connect(host="localhost", user="root",

password="Yurtdisi4")

cursor = cnx.cursor()

# Create the 'Market' database

cursor.execute('CREATE DATABASE Market;')

# Use the 'Market' database

cursor.execute('USE Market;')

cursor = cnx.cursor(buffered=True)

# Create the 'Vendor' table

cursor.execute('CREATE TABLE Vendor (vendor_id INTEGER PRIMARY KEY, vendor_name VARCHAR(255), license_start DATE, license_end DATE);')

# Create the 'Item' table

cursor.execute('CREATE TABLE Item (item_id INTEGER PRIMARY KEY, item_name VARCHAR(255), item_type VARCHAR(255), previous_price FLOAT, last_sold_date DATE);')

# Create the 'Auction' table

cursor.execute('CREATE TABLE Auction (auction_id INTEGER PRIMARY KEY, vendor_id INTEGER, item_id INTEGER, price FLOAT, FOREIGN KEY (vendor_id) REFERENCES Vendor(vendor_id), FOREIGN KEY (item_id) REFERENCES Item(item_id));')

sql1 = "INSERT INTO Vendor (vendor_id, vendor_name, license_start, license_end) VALUES (%s, %s, %s, %s)"

val1 = [(1, 'John Smith', '2021-01-01', '2022-12-31'),

(2, 'Melinda Jones', '2021-02-01', '2022-11-30'),

(3, 'Tom Wilson', '2021-03-01', '2022-10-31'),

(4, 'Rachel Williams', '2021-05-01', '2022-09-30'),

(5, 'Adam Johnson', '2021-06-01', '2022-08-31')]

cursor.executemany(sql1, val1)

cnx.commit()

print(cursor.rowcount, "was inserted.")

# We have inserted the tables here

sql2 = "INSERT INTO Item (item_id, item_name, item_type, previous_price, last_sold_date) VALUES (%s, %s, %s, %s, %s)"

val2 = [(11, 'Ball', 'Bronze', 15.50, '2022-12-31'),

(22, 'Jewelery', 'Diamond', 30000.0, '2020-01-23'),

(33, 'Watch', 'Gold', 200.0, '2021-11-30'),

(44, 'Medal', 'Silver', 120.0, '2012-10-06'),

(55, 'Wire', 'Copper', 5.0, '2022-12-15')]

cursor.executemany(sql2, val2)

cnx.commit()

print(cursor.rowcount, "was inserted.")

sql3 = "INSERT INTO Auction (auction_id, vendor_id, item_id, price) VALUES (%s, %s, %s, %s)"

val3 = [(111, 1, 11, 10.0),

(222, 2, 22, 20.0),

(333, 3, 33, 30.0),

(444, 4, 44, 40.0),

(555, 5, 55, 50.0)]

cursor.executemany(sql3, val3)

cnx.commit()

print(cursor.rowcount, "was inserted.")

cursor.execute("SHOW TABLES")

# Find the most expensive item

expensive_query = "SELECT item_name FROM Item WHERE previous_price = (SELECT MAX(previous_price) FROM Item)"

cursor.execute(expensive_query)

most_expensive = cursor.fetchone()[0]

print("The most expensive item is:", most_expensive)

# Find the most popular item

popular_query = "SELECT item_name FROM Item WHERE last_sold_date = (SELECT MAX(last_sold_date) FROM Item)"

cursor.execute(popular_query)

most_popular = cursor.fetchone()[0]

print("The most popular item is:", most_popular)

# Find the vendor with the most auctions

vendor_query = "SELECT vendor_name, COUNT(*) as num_auctions FROM Auction INNER JOIN Vendor ON Auction.vendor_id = Vendor.vendor_id GROUP BY vendor_name ORDER BY num_auctions DESC LIMIT 1"

cursor.execute(vendor_query)

most_active_vendor = cursor.fetchone()[0]

print("The vendor with the most auctions is:", most_active_vendor)

myresult = cursor.fetchall()

# Find the vendors who sold unpopular items

unpopular_query = "SELECT vendor_name FROM Auction INNER JOIN Vendor ON Auction.vendor_id = Vendor.vendor_id INNER JOIN Item ON Auction.item_id = Item.item_id WHERE last_sold_date != (SELECT MAX(last_sold_date) FROM Item)"

cursor.execute(unpopular_query)

unpopular_vendors = cursor.fetchall()

print("Vendors who sold unpopular items:")

for vendor in unpopular_vendors:

print(vendor[0])

#It gives the info

for x in myresult:

print(x)

# Close the connection to the MySQL server

cnx.close()