sample mysql interview questions

cart-mysql.sql

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;