Skip to content

Instantly share code, notes, and snippets.

@kflorence

kflorence/cart-mysql.sql

Last active Oct 26, 2017
Embed
What would you like to do?
sample mysql interview questions
drop table if exists customer;
create table customer(
id int auto_increment primary key,
first_name varchar(96) not null,
last_name varchar(96) not null,
address_id int not null
);
insert into customer (first_name, last_name, address_id) values ('john', 'doe', 1);
insert into customer (first_name, last_name, address_id) values ('jane', 'doe', 2);
insert into customer (first_name, last_name, address_id) values ('joe', 'schmoe', 3);
drop table if exists address;
create table address(
id int auto_increment primary key,
street varchar(128) not null,
city varchar(128) not null,
state_abbreviation char(2) not null,
zip_code char(10)
);
insert into address (street, city, state_abbreviation, zip_code) values ('123 Test St.', 'Oakland', 'CA', '94705');
insert into address (street, city, state_abbreviation, zip_code) values ('345 Test St.', 'Lakeview', 'OR', '97630');
insert into address (street, city, state_abbreviation, zip_code) values ('456 Test St.', 'Denver', 'CO', '80014');
drop table if exists item;
create table item(
id int auto_increment primary key,
name varchar(128) not null,
cost decimal(15, 2) not null
);
insert into item (name, cost) values ('baseball', 6.00);
insert into item (name, cost) values ('playstation 4', 300.00);
insert into item (name, cost) values ('2011 honda civic', 10000.00);
drop table if exists cart;
create table cart(
id int auto_increment primary key,
customer_id int not null,
item_id int not null,
quantity int not null
);
insert into cart (customer_id, item_id, quantity) values (1, 1, 12);
insert into cart (customer_id, item_id, quantity) values (1, 2, 1);
insert into cart (customer_id, item_id, quantity) values (2, 3, 1);
-- (a) Write a query returning a list of customers in the state of CA.
select c.* from customer c join address a on c.address_id = a.id where a.state_abbreviation = 'CA';
-- (b) Write a query returning the number of items and total amount of the items in the customers cart by state
select a.state_abbreviation, count(i.id) as total_items, sum(i.cost) as total_cost from item i
join cart ca on ca.item_id = i.id
join customer cu on ca.customer_id = cu.id
join address a on cu.address_id = a.id
group by a.state_abbreviation;
-- (c) Modify the query you wrote in (b) to display 0 total_items and 0 total_cost for a state which has no customer cart items.
select a.state_abbreviation, count(i.id) as total_items, coalesce(sum(i.cost), 0) as total_cost from customer cu
join address a on cu.address_id = a.id
left join cart ca on ca.customer_id = cu.id
left join item i on ca.item_id = i.id
group by a.state_abbreviation;
-- (d) Modify the query you wrote in (b) to only list states with more than $1000 worth of items in the customers cart.
select a.state_abbreviation, count(i.id) as total_items, sum(i.cost) as total_cost from item i
join cart ca on ca.item_id = i.id
join customer cu on ca.customer_id = cu.id
join address a on cu.address_id = a.id
group by a.state_abbreviation
having sum(i.cost) > 1000;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment