CREATE TABLE Manufacturers (
    Code NUMBER PRIMARY KEY,
    Name TEXT,
    Headquarter TEXT,
    Founder TEXT,
    Revenue NUMBER
);
CREATE TABLE Products (
    Code NUMBER PRIMARY KEY,
    Name TEXT,
    Price NUMBER,
    Manufacturer NUMBER,
    FOREIGN KEY ("Manufacturer") REFERENCES Manufacturers("Code")
);

Question: Find the number of different products that are produced by companies at different headquarter cities.
SQL: SELECT count(DISTINCT T1.name) ,  T2.Headquarter FROM products AS T1 JOIN manufacturers AS T2 ON T1.Manufacturer  =  T2.code GROUP BY T2.Headquarter

Question: How many different products are produced in each headquarter city?
SQL: SELECT count(DISTINCT T1.name) ,  T2.Headquarter FROM products AS T1 JOIN manufacturers AS T2 ON T1.Manufacturer  =  T2.code GROUP BY T2.Headquarter

Question: Find number of products which Sony does not make.
SQL: SELECT count(DISTINCT name) FROM products WHERE name NOT IN (SELECT T1.name FROM products AS T1 JOIN manufacturers AS T2 ON T1.Manufacturer  =  T2.code WHERE T2.name  =  'Sony')

Question: How many products are not made by Sony?
SQL: SELECT count(DISTINCT name) FROM products WHERE name NOT IN (SELECT T1.name FROM products AS T1 JOIN manufacturers AS T2 ON T1.Manufacturer  =  T2.code WHERE T2.name  =  'Sony')

Question: Find the name of companies that do not make DVD drive.
SQL: SELECT name FROM manufacturers EXCEPT SELECT T2.name FROM products AS T1 JOIN manufacturers AS T2 ON T1.Manufacturer  =  T2.code WHERE T1.name  =  'DVD drive'

Question: What are the names of companies that do not make DVD drives?
SQL: SELECT name FROM manufacturers EXCEPT SELECT T2.name FROM products AS T1 JOIN manufacturers AS T2 ON T1.Manufacturer  =  T2.code WHERE T1.name  =  'DVD drive'

Question: Find the number of products for each manufacturer, showing the name of each company.
SQL: SELECT count(*) ,  T2.name FROM products AS T1 JOIN manufacturers AS T2 ON T1.Manufacturer  =  T2.code GROUP BY T2.name

Question: How many products are there for each manufacturer?
SQL: SELECT count(*) ,  T2.name FROM products AS T1 JOIN manufacturers AS T2 ON T1.Manufacturer  =  T2.code GROUP BY T2.name

Question: Select the names of all the products in the store.
SQL: SELECT Name FROM Products

Question: What are the names of all products?
SQL: SELECT Name FROM Products

Question: Select the names and the prices of all the products in the store.
SQL: SELECT name ,  price FROM products

Question: What are the names and prices of all products in the store?
SQL: SELECT name ,  price FROM products

Question: Select the name of the products with a price less than or equal to $200.
SQL: SELECT name FROM products WHERE price  <=  200

Question: What are the names of products with price at most 200?
SQL: SELECT name FROM products WHERE price  <=  200

Question: Find all information of all the products with a price between $60 and $120.
SQL: SELECT * FROM products WHERE price BETWEEN 60 AND 120

Question: What is all the information of all the products that have a price between 60 and 120?
SQL: SELECT * FROM products WHERE price BETWEEN 60 AND 120

Question: Compute the average price of all the products.
SQL: SELECT avg(price) FROM products

Question: What is the average price across all products?
SQL: SELECT avg(price) FROM products

Question: Compute the average price of all products with manufacturer code equal to 2.
SQL: SELECT avg(price) FROM products WHERE Manufacturer  =  2

Question: What is the average price of products with manufacturer codes equal to 2?
SQL: SELECT avg(price) FROM products WHERE Manufacturer  =  2

Question: Compute the number of products with a price larger than or equal to $180.
SQL: SELECT count(*) FROM products WHERE price >= 180

Question: How many products have prices of at least 180?
SQL: SELECT count(*) FROM products WHERE price >= 180

Question: Select the name and price of all products with a price larger than or equal to $180, and sort first by price (in descending order), and then by name  (in ascending order).
SQL: SELECT name ,  price FROM products WHERE price  >=  180 ORDER BY price DESC ,  name ASC

Question: What are the names and prices of products that cost at least 180, sorted by price decreasing and name ascending?
SQL: SELECT name ,  price FROM products WHERE price  >=  180 ORDER BY price DESC ,  name ASC

Question: Select all the data from the products and each product's manufacturer.
SQL: SELECT * FROM products AS T1 JOIN Manufacturers AS T2 ON T1.manufacturer  =  T2.code

Question: What is all the product data, as well as  each product's manufacturer?
SQL: SELECT * FROM products AS T1 JOIN Manufacturers AS T2 ON T1.manufacturer  =  T2.code

Question: Select the average price of each manufacturer's products, showing only the manufacturer's code.
SQL: SELECT AVG(Price) ,  Manufacturer FROM Products GROUP BY Manufacturer

Question: What are the average prices of products, grouped by manufacturer code?
SQL: SELECT AVG(Price) ,  Manufacturer FROM Products GROUP BY Manufacturer

Question: Select the average price of each manufacturer's products, showing the manufacturer's name.
SQL: SELECT avg(T1.Price) ,  T2.name FROM products AS T1 JOIN Manufacturers AS T2 ON T1.manufacturer  =  T2.code GROUP BY T2.name

Question: What are the average prices of products, grouped by manufacturer name?
SQL: SELECT avg(T1.Price) ,  T2.name FROM products AS T1 JOIN Manufacturers AS T2 ON T1.manufacturer  =  T2.code GROUP BY T2.name

Question: Select the names of manufacturer whose products have an average price higher than or equal to $150.
SQL: SELECT avg(T1.Price) ,  T2.name FROM products AS T1 JOIN Manufacturers AS T2 ON T1.manufacturer  =  T2.code GROUP BY T2.name HAVING avg(T1.price) >= 150

Question: What are the names and average prices of products for manufacturers whose products cost on average 150 or more?
SQL: SELECT avg(T1.Price) ,  T2.name FROM products AS T1 JOIN Manufacturers AS T2 ON T1.manufacturer  =  T2.code GROUP BY T2.name HAVING avg(T1.price) >= 150

Question: Select the name and price of the cheapest product.
SQL: SELECT name ,  price FROM Products ORDER BY price ASC LIMIT 1

Question: What is the name and price of the cheapest product?
SQL: SELECT name ,  price FROM Products ORDER BY price ASC LIMIT 1

Question: Select the name of each manufacturer along with the name and price of its most expensive product.
SQL: SELECT T1.Name ,  max(T1.Price) ,  T2.name FROM products AS T1 JOIN Manufacturers AS T2 ON T1.manufacturer  =  T2.code GROUP BY T2.name

Question: For each manufacturer name, what are the names and prices of their most expensive product?
SQL: SELECT T1.Name ,  max(T1.Price) ,  T2.name FROM products AS T1 JOIN Manufacturers AS T2 ON T1.manufacturer  =  T2.code GROUP BY T2.name

Question: Select the code of the product that is cheapest in each product category.
SQL: SELECT code ,  name ,  min(price) FROM products GROUP BY name

Question: What are the codes and names of the cheapest products in each category?
SQL: SELECT code ,  name ,  min(price) FROM products GROUP BY name