DROP database SP;
CREATE DATABASE SP;
USE SP;

CREATE TABLE supplier(
sno varchar(20) NOT NULL PRIMARY KEY,
sname varchar(30),
city varchar(30),
phone varchar(10),
email varchar(30)
);

CREATE TABLE parts(
pno varchar(20) NOT NULL PRIMARY KEY,
pname varchar(30),
weight int,
color varchar(30)
);

CREATE TABLE sp(
sno varchar(20),
pno varchar(30),
qty int,
PRIMARY KEY (sno, pno),
CONSTRAINT FK_sno FOREIGN KEY (sno) REFERENCES
supplier(sno),
CONSTRAINT FK_pno FOREIGN KEY (pno) REFERENCES
parts(pno)
);

ALTER TABLE parts ADD dom DATE;

ALTER TABLE supplier ADD dob DATE;

ALTER TABLE sp ADD dos DATE;


INSERT INTO supplier VALUES('s101', 'saga', 'CHN', '9999999999', 'asd@gmail.com', '1969-01-01');
INSERT INTO supplier VALUES('s102', 'nipo', 'CHN', '9999999999', 'asd@gmail.com', '2012-01-01');
INSERT INTO supplier VALUES('s103', 'tipo', 'CHN', '9999999999', 'asd@gmail.com', '2015-01-01');
INSERT INTO supplier VALUES('s104', 'poiuy', 'BOM', '9999999999', 'asd@gmail.com', '2015-01-01');
INSERT INTO supplier VALUES('s105', 'werfg', 'BOM', '9999999999', 'asd@gmail.com', '2015-01-01');
INSERT INTO supplier VALUES('s123', 'wiyy', 'BOM', '9999999999', 'asd@gmail.com', '2015-01-01');

INSERT INTO parts VALUES('p101', 'bread', 20, 'b', '2011-01-01');
INSERT INTO parts VALUES('p102', 'butter', 30, 'b', '2015-01-01');
INSERT INTO parts VALUES('p103', 'jam', 10, 'b', '2015-03-01');
INSERT INTO parts VALUES('p104', 'milk', 10, 'b', '2011-01-01');
INSERT INTO parts VALUES('p105', 'tea', 50, 'b', '2015-01-01');


INSERT INTO sp VALUES('s101', 'p101', 20, '2012-01-01');
INSERT INTO sp VALUES('s102', 'p102', 30, '2015-01-01');
INSERT INTO sp VALUES('s103', 'p103', 10, '2015-01-01');
INSERT INTO sp VALUES('s104', 'p104', 10, '2012-01-01');
INSERT INTO sp VALUES('s105', 'p105', 50,'2015-01-01');
INSERT INTO sp VALUES('s123', 'p105', 50,'2018-07-01');
INSERT INTO sp VALUES('s123', 'p102', 50,'2018-07-25');
INSERT INTO sp VALUES('s123', 'p103', 50,'2015-01-01');

/* Queries*/

/* Q - 1*/

SELECT sname FROM supplier
WHERE dob = (SELECT MAX(dob) FROM supplier);

/* Q - 2*/

SELECT * FROM supplier
WHERE (YEAR(CURDATE()) - YEAR(dob) > 30);

/* Q - 3*/
SELECT city, AVG(YEAR(CURDATE()) - YEAR(dob)) as Average FROM supplier GROUP BY city;


/* Q- 4*/

SELECT sname FROM supplier
WHERE sno IN (SELECT sno from sp WHERE YEAR(dos) = 2012);

/* Q- 5*/

SELECT sname FROM supplier
WHERE sno IN (SELECT sno from sp WHERE dos > '2012-02-01');

/* Q- 6*/

SELECT pname FROM parts
WHERE pno IN (SELECT pno FROM sp WHERE dos = (SELECT MAX(dos) FROM sp));

/* Q- 7*/

SELECT sname FROM supplier WHERE sno IN ( SELECT a.sno FROM sp as a, sp as b WHERE ( (a.sno = b.sno) AND (a.pno < b.pno) AND (DATEDIFF(a.dos, b.dos) BETWEEN 0 AND 30)));

/* Q- 8*/

SELECT *, DATE_ADD(dos, INTERVAL 3 MONTH) as warranty_expiry FROM sp;

/* Q- 9*/

SELECT *, DATE_ADD(dos, INTERVAL 3 MONTH) as warranty_expiry FROM sp WHERE sno = 's123' AND CURDATE() <= DATE_ADD(dos, INTERVAL 3 MONTH);

/* Q- 10*/

SELECT sname FROM supplier WHERE sno
IN (SELECT a.sno FROM sp as a, parts as b WHERE a.dos >= DATE_ADD(b.dom, INTERVAL 6 MONTH) AND a.pno = b.pno);