DMW - Mid semester exam answer sheet

Deepak Sharma (Instructor-in-Charge)

Question no. 18

SQL code demonstration

Creating a dummy database

CREATE DATABASE E_SHOPPING;
USE E_SHOPPING;

CREATE TABLE BUYERS ( 
ID INT NOT NULL,
NAME VARCHAR ( 20 ) NOT NULL,
DOB DATE,
PRIMARY KEY ( ID ) 
) ;

CREATE TABLE SELLERS ( 
ID INT NOT NULL,
NAME VARCHAR ( 20 ) NOT NULL,
DOB DATE,
PRIMARY KEY ( ID ) 
) ;

INSERT INTO BUYERS  ( ID, NAME, DOB )   VALUES ( 1 , 'DINESH', '1991-09-13');
INSERT INTO SELLERS ( ID, NAME, DOB )   VALUES ( 1 , 'DINESH', '1992-04-30');
INSERT INTO BUYERS  ( ID, NAME, DOB )   VALUES ( 2 , 'SHREYA', '1993-08-11');
INSERT INTO SELLERS ( ID, NAME, DOB )   VALUES ( 3 , 'ANITHA', '1994-09-15');

Solution for part-1

SELECT BUYERS.ID AS BID, SELLERS.ID AS SID
FROM BUYERS, SELLERS
WHERE BUYERS.ID = SELLERS.ID;

Solution for part-2

INSERT INTO PRODUCTS ( PID, NAME, MANUFACTURED, EXPIRY, RATING )   VALUES ( 453 , 'POTATO CHIPS', '2021-09-15', '2022-08-14', 4);
INSERT INTO PRODUCTS ( PID, NAME, MANUFACTURED, EXPIRY, RATING )   VALUES ( 500 , 'HONEY', '2021-09-15', '2022-08-14', 4.3);
INSERT INTO PRODUCTS ( PID, NAME, MANUFACTURED, EXPIRY, RATING )   VALUES ( 503 , 'WHEAT FLOUR', '2021-09-15', '2022-08-14', 5);
INSERT INTO PRODUCTS ( PID, NAME, MANUFACTURED, EXPIRY, RATING )   VALUES ( 623 , 'FRUIT CAKE', '2021-09-15', '2022-08-14', 3);
INSERT INTO PRODUCTS ( PID, NAME, MANUFACTURED, EXPIRY, RATING )   VALUES ( 124 , 'GREEN TEA', '2021-09-15', '2022-08-14', 2);