
-- DROP SCHEMA HW_QUIZ3;
CREATE SCHEMA HW_QUIZ3;
USE HW_QUIZ3;

CREATE TABLE COMPUTER(
    Id VARCHAR(20) PRIMARY KEY,
    Model  VARCHAR(20)
);

CREATE TABLE PRINTER(
    Id VARCHAR(20) PRIMARY KEY,
    Model  VARCHAR(20)
);

CREATE TABLE CONNECTION(
    Computer VARCHAR(20),
    Printer VARCHAR(20),
    PRIMARY KEY(Computer, Printer),
    FOREIGN KEY (Computer) REFERENCES COMPUTER(Id),
    FOREIGN KEY (Printer) REFERENCES PRINTER(Id)
);

INSERT INTO COMPUTER VALUES ('A', 'DELL A'), ('B', 'HP X'), ('C', 'ZEPTO D'), ('D', 'MAC Y');
INSERT INTO PRINTER VALUES ('12', 'HP-140'), ('13', 'HP-139'), ('14', 'HP-140'), ('15', 'HP-139');
INSERT INTO CONNECTION VALUES ('A', '12'), ('A', '13'), ('B', '13'), ('C', '14');

-- Write a query that returns the number of computer connected to the printer whose id is '13'.
SELECT COUNT(Computer) FROM CONNECTION WHERE Printer = '13';

-- Write a query that returns the number of different models of printers. 
SELECT COUNT(DISTINCT Model) FROM PRINTER;

-- Write a query that returns the model(s) of the printer connected to the computer whose id is 'A'.
SELECT Model FROM PRINTER, CONNECTION WHERE Computer = 'A' AND Printer = PRINTER.Id;

-- Write a query that returns the id of the computer(s) not connected to any printer.
SELECT Id FROM COMPUTER WHERE Id NOT IN (SELECT Computer FROM CONNECTION);