Question

Using MySQL Workbench:

Submit the printouts of your interaction with the DBMS. ID name ID delivery_timestamp supplier supplied item city name quantity 1. [30-point] Create an SQL database of the above ER-diagram using "create table” statements. The type of attribute "quantity" is integer, "delivery_timestamp" is timestamp, and, for all the others, the type is varchar (up to 100 chars). 2. [10-point] Insert 4 records into supplier, 4 records into item, and 10 records into supplied. 3. [60-points] Perform the following queries in SQL. Present the SQL statement and the corresponding output from the DBMS for each of the following queries: a) List all suppliers (ID and name) who supply item "Bolt". b) List all items (ID and name) supplied by both S2 and S4. c) For each supplier that supplies at least one item, list its ID, name, and the total quantity supplied by the supplier.

Answer 1

Queries and outputs:

Q1)CREATING DATABASE...........

mysql> create database supplier;
Query OK, 1 row affected (2.86 sec)

mysql> use supplier;
Database changed
mysql> create table supplier(id varchar(100),name varchar(100),city varchar(100));
Query OK, 0 rows affected (2.43 sec)

mysql> alter table supplier add primary key(id);
Query OK, 0 rows affected (2.48 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> create table item(id varchar(100) primary key,name varchar(100));
Query OK, 0 rows affected (0.68 sec)

mysql> create table supplied(quantity int,delivery_timestamp timestamp,s_id varchar(100),foreign key(s_id) references supplier(id),i_id varchar(100),foreign key(i_id) references item(id));
Query OK, 0 rows affected (0.93 sec)

mysql> desc supplier;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | varchar(100) | NO   | PRI | NULL    |       |
| name | varchar(100) | YES |     | NULL    |       |
| city | varchar(100) | YES |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
3 rows in set (0.27 sec)

mysql> desc item;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | varchar(100) | NO   | PRI | NULL    |       |
| name | varchar(100) | YES |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> desc supplied;
+--------------------+--------------+------+-----+---------+-------+
| Field              | Type         | Null | Key | Default | Extra |
+--------------------+--------------+------+-----+---------+-------+
| quantity           | int(11)      | YES |     | NULL    |       |
| delivery_timestamp | timestamp    | YES |     | NULL    |       |
| s_id               | varchar(100) | YES | MUL | NULL    |       |
| i_id               | varchar(100) | YES | MUL | NULL    |       |
+--------------------+--------------+------+-----+---------+-------+
4 rows in set (0.07 sec)

Q2)INSERTING VALUES INTO SUPPLIER TABLE(4 RECORDS).........

mysql> insert into supplier values("s1","raj","vizag");
Query OK, 1 row affected (0.15 sec)

mysql> insert into supplier values("s2","ram","delhi");
Query OK, 1 row affected (0.13 sec)

mysql> insert into supplier values("s3","hari","mumbai");
Query OK, 1 row affected (0.07 sec)

mysql> insert into supplier values("s4","rajesh","new york");
Query OK, 1 row affected (0.10 sec)

mysql> select * from supplier;
+----+--------+----------+
| id | name   | city     |
+----+--------+----------+
| s1 | raj    | vizag    |
| s2 | ram    | delhi    |
| s3 | hari   | mumbai   |
| s4 | rajesh | new york |
+----+--------+----------+
4 rows in set (0.00 sec)

INSERTING VALUES INTO ITEM TABLE(5 RECORDS)......

mysql> insert into item values("i1","electronic");
Query OK, 1 row affected (0.12 sec)

mysql> insert into item values("i2","toy");
Query OK, 1 row affected (0.16 sec)

mysql> insert into item values("i3","home appliances");
Query OK, 1 row affected (0.07 sec)

mysql> insert into item values("i4","costume");
Query OK, 1 row affected (0.13 sec)

mysql> insert into item values("i5","Bolt");
Query OK, 1 row affected (0.07 sec)

mysql> select * from item;
+----+-----------------+
| id | name            |
+----+-----------------+
| i1 | electronic      |
| i2 | toy             |
| i3 | home appliances |
| i4 | costume         |
| i5 | Bolt            |
+----+-----------------+
5 rows in set (0.00 sec)

INSERTING VALUES INTO SUPPLIED TABLE(11 RECORDS)..........

mysql> insert into supplied values(50,'2001-01-01 00:00:01','s4','i3');
Query OK, 1 row affected (0.09 sec)

mysql> insert into supplied values(56,'2003-01-01 00:01:01','s3','i1');
Query OK, 1 row affected (0.19 sec)

mysql> insert into supplied values(40,'2003-10-01 00:10:01','s2','i3');
Query OK, 1 row affected (0.09 sec)

mysql> insert into supplied values(45,'2003-10-01 00:10:55','s4','i3');
Query OK, 1 row affected (0.11 sec)

mysql> insert into supplied values(25,'2020-09-01 00:06:55','s1','i1');
Query OK, 1 row affected (0.09 sec)

mysql> insert into supplied values(20,'2010-07-01 00:04:10','s2','i2');
Query OK, 1 row affected (0.14 sec)

mysql> insert into supplied values(20,'2010-07-01 00:04:10','s1','i4');
Query OK, 1 row affected (0.09 sec)

mysql> insert into supplied values(43,'2018-05-01 00:03:10','s1','i2');
Query OK, 1 row affected (0.14 sec)

mysql> insert into supplied values(35,'2008-02-29 00:06:12','s3','i4');
Query OK, 1 row affected (0.09 sec)

mysql> insert into supplied values(35,'2008-03-31 00:06:12','s3','i4');
Query OK, 1 row affected (0.09 sec)

mysql> insert into supplied values(35,'2008-03-31 00:06:12','s3','i5');
Query OK, 1 row affected (0.16 sec)
mysql> SELECT * FROM SUPPLIED;
+----------+---------------------+------+------+
| quantity | delivery_timestamp | s_id | i_id |
+----------+---------------------+------+------+
|       50 | 2001-01-01 00:00:01 | s4   | i3   |
|       56 | 2003-01-01 00:01:01 | s3   | i1   |
|       40 | 2003-10-01 00:10:01 | s2   | i3   |
|       45 | 2003-10-01 00:10:55 | s4   | i3   |
|       25 | 2020-09-01 00:06:55 | s1   | i1   |
|       20 | 2010-07-01 00:04:10 | s2   | i2   |
|       20 | 2010-07-01 00:04:10 | s1   | i4   |
|       43 | 2018-05-01 00:03:10 | s1   | i2   |
|       35 | 2008-02-29 00:06:12 | s3   | i4   |
|       35 | 2008-03-31 00:06:12 | s3   | i4   |
|       35 | 2008-03-31 00:06:12 | s3   | i5   |
+----------+---------------------+------+------+
11 rows in set (0.00 sec)

3)A) LIST SUPPLIERS(ID,NAME) WHO SUPPLY ITEM "BOLT"..............

mysql> select supplier.id,supplier.name from item,supplier,supplied where item.name="bolt" and item.id=i_id and s_id=supplier.id;
+----+------+
| id | name |
+----+------+
| s3 | hari |
+----+------+
1 row in set (0.07 sec)

B)LIST ALL ITEMS(ID,NAME) SUPPLIED BY BOTH(IN THIS DATABASE THERE ARE NO ITEMS WHICH SATISFIES THE CONSTRAINT IF THERE EXISTS THIS QUERY WILL GIVE A OUTPUT TABLE)

mysql> select item.id,item.name from supplier,item,supplied where supplier.id="s2" AND supplier.id="s4" and supplier.id=s_id and i_id=item.id;
Empty set (0.00 sec)

C)FOR EACH SUPPLIER THET SUPPLIES AT LEAST ONR ITEM,LIST IT'S ID,NAME AND THE TOTAL QUANTITY SUPPLIED BY THE SUPPLIER.......

I DID'N GET THAT YOU ARE ASKING FOR ITEM'S OR SUPPLIER'S ID,NAME? SO I AM GIVING YOU BOTH QUERIES

THIS QUERY IS TO LIST ITEM'S NAME AND ID
mysql> select item.id,item.name,sum(quantity) as Total_Quantity from supplier,item,supplied where supplier.id=s_id and i_id=item.id group by supplier.id having count(item.id>0);
+----+-----------------+----------------+
| id | name            | Total_Quantity |
+----+-----------------+----------------+
| i1 | electronic      |             88 |
| i3 | home appliances |             60 |
| i1 | electronic      |            161 |
| i3 | home appliances |             95 |
+----+-----------------+----------------+
4 rows in set (0.03 sec)

THIS QUERY IS TO LIST SUPPLIER'S NAME,ID....
mysql> select supplier.id,supplier.name,sum(quantity) as Total_Quantity from supplier,item,supplied where supplier.id=s_id and i_id=item.id group by supplier.id having count(item.id>0);
+----+--------+----------------+
| id | name   | Total_Quantity |
+----+--------+----------------+
| s1 | raj    |             88 |
| s2 | ram    |             60 |
| s3 | hari   |            161 |
| s4 | rajesh |             95 |
+----+--------+----------------+
4 rows in set (0.00 sec)

NOTE:IF YOU FIND ANY DIFFICULTY IN UNDERSTANDING IT OR IF NEED ANY EXPLANATION OR MODIFICATION PLEASE COMMENT.IF YOU ARE SATISFIED WITH MY ANSWER PLEASE UPVOTE.THANK YOU....