SubQuery (extra-2)

CREATE DATABASE DBLab
USE DBLab

CREATE TABLE Product(
    pname VARCHAR(50) PRIMARY KEY,
    price INT,
    catagory VARCHAR(50),
    manufacturer VARCHAR(50),
    cid INT,
);

CREATE TABLE Purchase(
    buyer VARCHAR(50),
    seller VARCHAR(50),
    store VARCHAR(50),
    product VARCHAR(50),
);

CREATE TABLE Person(
    persname VARCHAR(50) PRIMARY KEY,
    phonenumber INT,
    city VARCHAR(50),
);

CREATE TABLE Company(
    cid INT,
    cname VARCHAR(50),
    city VARCHAR(50),
);

INSERT INTO Product(pname, price, catagory, manufacturer, cid)
VALUES('Shirt' ,1000, 'Clothing', 'A', '1')
INSERT INTO Product(pname, price, catagory, manufacturer, cid)
VALUES('Mobile' ,15000, 'Gadgets', 'Samsung', '2')
INSERT INTO Product(pname, price, catagory, manufacturer, cid)
VALUES('Laptop' ,60000, 'Clothing', 'Asus', '3')
INSERT INTO Product(pname, price, catagory, manufacturer, cid)
VALUES('Smartphone' ,10000, 'Clothing', 'Asus', '3')


INSERT INTO Company(cid, cname, city)
VALUES(3, 'Asus', 'China')

SELECT Company.cname, COUNT(Product.pname) AS numberofProducts
FROM Product
INNER JOIN Company
ON Product.cid = Company.cid
GROUP BY Company.cname

SELECT DISTINCT c.cname, (SELECT COUNT(*) FROM Product p WHERE p.cid=c.cid)
FROM Company c

SELECT pname FROM(SELECT * FROM Product WHERE price>5000)
AS p WHERE p.price<50000