My SQL workbench

DROP DATABASE IF EXISTS pizza;

CREATE DATABASE pizza;

use pizza;

/* Create the schema for our tables */

create table Person(name text, age int, gender text);create table Frequents(name text, pizzeria text);create table Eats(name text, pizza text);create table Serves(pizzeria text, pizza text, price decimal(10,2));

/* Populate the tables with our data */

insert into Person values('Amy', 16, 'female');

insert into Person values('Ben', 21, 'male');

insert into Person values('Cal', 33, 'male');

insert into Person values('Dan', 13, 'male');

insert into Person values('Eli', 45, 'male');

insert into Person values('Fay', 21, 'female');

insert into Person values('Gus', 24, 'male');

insert into Person values('Hil', 30, 'female');

insert into Person values('Ian', 18, 'male');

insert into Frequents values('Amy', 'Pizza Hut');

insert into Frequents values('Ben', 'Pizza Hut');

insert into Frequents values('Ben', 'Chicago Pizza');

insert into Frequents values('Cal', 'Straw Hat');

insert into Frequents values('Cal', 'New York Pizza');

insert into Frequents values('Dan', 'Straw Hat');

insert into Frequents values('Dan', 'New York Pizza');

insert into Frequents values('Eli', 'Straw Hat');

insert into Frequents values('Eli', 'Chicago Pizza');

insert into Frequents values('Fay', 'Dominos');

insert into Frequents values('Fay', 'Little Caesars');

insert into Frequents values('Gus', 'Chicago Pizza');

insert into Frequents values('Gus', 'Pizza Hut');

insert into Frequents values('Hil', 'Dominos');

insert into Frequents values('Hil', 'Straw Hat');

insert into Frequents values('Hil', 'Pizza Hut');

insert into Frequents values('Ian', 'New York Pizza');

insert into Frequents values('Ian', 'Straw Hat');

insert into Frequents values('Ian', 'Dominos');

insert into Eats values('Amy', 'pepperoni');

insert into Eats values('Amy', 'mushroom');

insert into Eats values('Ben', 'pepperoni');

insert into Eats values('Ben', 'cheese');

insert into Eats values('Cal', 'supreme');

insert into Eats values('Dan', 'pepperoni');

insert into Eats values('Dan', 'cheese');

insert into Eats values('Dan', 'sausage');

insert into Eats values('Dan', 'supreme');

insert into Eats values('Dan', 'mushroom');

insert into Eats values('Eli', 'supreme');

insert into Eats values('Eli', 'cheese');

insert into Eats values('Fay', 'mushroom');

insert into Eats values('Gus', 'mushroom');

insert into Eats values('Gus', 'supreme');

insert into Eats values('Gus', 'cheese');

insert into Eats values('Hil', 'supreme');

insert into Eats values('Hil', 'cheese');

insert into Eats values('Ian', 'supreme');

insert into Eats values('Ian', 'pepperoni');

insert into Serves values('Pizza Hut', 'pepperoni', 12);

insert into Serves values('Pizza Hut', 'sausage', 12);

insert into Serves values('Pizza Hut', 'cheese', 9);

insert into Serves values('Pizza Hut', 'supreme', 12);

insert into Serves values('Little Caesars', 'pepperoni', 9.75);

insert into Serves values('Little Caesars', 'sausage', 9.5);

insert into Serves values('Little Caesars', 'cheese', 7);

insert into Serves values('Little Caesars', 'mushroom', 9.25);

insert into Serves values('Dominos', 'cheese', 9.75);

insert into Serves values('Dominos', 'mushroom', 11);

insert into Serves values('Straw Hat', 'pepperoni', 8);

insert into Serves values('Straw Hat', 'cheese', 9.25);

insert into Serves values('Straw Hat', 'sausage', 9.75);

insert into Serves values('New York Pizza', 'pepperoni', 8);

insert into Serves values('New York Pizza', 'cheese', 7);

insert into Serves values('New York Pizza', 'supreme', 8.5);

insert into Serves values('Chicago Pizza', 'cheese', 7.75);

insert into Serves values('Chicago Pizza', 'supreme', 8.5);

1. Find the names of people who are male and over 20 and who eats cheese pizza

2. Find all pizzerias that do not serve pizzas that are $9 or more

Hint: # first find all the pizzerias that serve at least one pizza that costs >=9

SELECT ---- FROM ---- WHERE ----;

# now select the pizzerias that are not in the above list

SELECT DISTINCT pizzeria FROM --- WHERE ---- (SELECT --- FROM ---- WHERE -----);

3. Find all pizzerias that only serve pizzas that are $9 or more (which means pizzerias that do not serve any pizza that is less than $9)

Hint: # first find all the pizzerias that serve at least one pizza that costs <9

SELECT ---- FROM ---- WHERE ----;

# now select the pizzerias that are not in the above list

SELECT DISTINCT pizzeria FROM --- WHERE ---- (SELECT --- FROM ---- WHERE -----);

4. Find all pizzas eaten by at least one female over the age of 20 that cost more than $9

Hint: First, combine the information of some tables that gives you the name of all the pizzas that have been eaten by someone, then you can use a "where" clause to apply more conditions like gender being 'female' and person's age being greater than 20 and the price of pizza being greater than 9;

5. Find the names of all people who eat at least one pizza served by Dominos but who do not frequent Dominos

Hint:

# First find all the people who are Dominos frequent (inner query)

SELECT ----- FROM ----- WHERE ------

# then select all the people who eat pizza which served by Dominos (join of Person, Eats and Serves tables give you this information), then exclude whoever is in the above list

SELECT ----- FROM -----,-----, ----- WHERE ----- AND ------ AND ------ AND ----- (SELECT ----- FROM ----- WHERE ------);

6. Find all pizzas that are eaten only by people younger than 24