Database: customer_deliveries

CREATE TABLE `Products` (
`product_id` INTEGER PRIMARY KEY,
`product_name` VARCHAR(20),
`product_price` DECIMAL(19,4),
`product_description` VARCHAR(255)
);
CREATE TABLE `Addresses` (
`address_id` INTEGER PRIMARY KEY,
`address_details` VARCHAR(80),
`city` VARCHAR(50),
`zip_postcode` VARCHAR(20),
`state_province_county` VARCHAR(50),
`country` VARCHAR(50)
);
CREATE TABLE `Customers` (
`customer_id` INTEGER PRIMARY KEY,
`payment_method` VARCHAR(10) NOT NULL,
`customer_name` VARCHAR(80),
`customer_phone` VARCHAR(80),
`customer_email` VARCHAR(80),
`date_became_customer` DATETIME
);
CREATE TABLE `Regular_Orders` (
`regular_order_id` INTEGER PRIMARY KEY,
`distributer_id` INTEGER NOT NULL,
FOREIGN KEY (`distributer_id` ) REFERENCES `Customers`(`customer_id` )
);
CREATE TABLE `Regular_Order_Products` (
`regular_order_id` INTEGER NOT NULL,
`product_id` INTEGER NOT NULL,
FOREIGN KEY (`product_id` ) REFERENCES `Products`(`product_id` ),
FOREIGN KEY (`regular_order_id` ) REFERENCES `Regular_Orders`(`regular_order_id` )
);
CREATE TABLE `Actual_Orders` (
`actual_order_id` INTEGER PRIMARY KEY,
`order_status_code` VARCHAR(10) NOT NULL,
`regular_order_id` INTEGER NOT NULL,
`actual_order_date` DATETIME,
FOREIGN KEY (`regular_order_id` ) REFERENCES `Regular_Orders`(`regular_order_id` )
);
CREATE TABLE `Actual_Order_Products` (
`actual_order_id` INTEGER NOT NULL,
`product_id` INTEGER NOT NULL,
FOREIGN KEY (`product_id` ) REFERENCES `Products`(`product_id` ),
FOREIGN KEY (`actual_order_id` ) REFERENCES `Actual_Orders`(`actual_order_id` )
);
CREATE TABLE `Customer_Addresses` (
`customer_id` INTEGER NOT NULL,
`address_id` INTEGER NOT NULL,
`date_from` DATETIME NOT NULL,
`address_type` VARCHAR(10) NOT NULL,
`date_to` DATETIME,
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` ),
FOREIGN KEY (`address_id` ) REFERENCES `Addresses`(`address_id` )
);
CREATE TABLE `Delivery_Routes` (
`route_id` INTEGER PRIMARY KEY,
`route_name` VARCHAR(50),
`other_route_details` VARCHAR(255)
);
CREATE TABLE `Delivery_Route_Locations` (
`location_code` VARCHAR(10) PRIMARY KEY,
`route_id` INTEGER NOT NULL,
`location_address_id` INTEGER NOT NULL,
`location_name` VARCHAR(50),
FOREIGN KEY (`location_address_id` ) REFERENCES `Addresses`(`address_id` ),
FOREIGN KEY (`route_id` ) REFERENCES `Delivery_Routes`(`route_id` )
);
CREATE TABLE `Trucks` (
`truck_id` INTEGER PRIMARY KEY,
`truck_licence_number` VARCHAR(20),
`truck_details` VARCHAR(255)
);
CREATE TABLE `Employees` (
`employee_id` INTEGER PRIMARY KEY,
`employee_address_id` INTEGER NOT NULL,
`employee_name` VARCHAR(80),
`employee_phone` VARCHAR(80),
FOREIGN KEY (`employee_address_id` ) REFERENCES `Addresses`(`address_id` )
);
CREATE TABLE `Order_Deliveries` (
`location_code` VARCHAR(10) NOT NULL,
`actual_order_id` INTEGER NOT NULL,
`delivery_status_code` VARCHAR(10) NOT NULL,
`driver_employee_id` INTEGER NOT NULL,
`truck_id` INTEGER NOT NULL,
`delivery_date` DATETIME,
FOREIGN KEY (`truck_id` ) REFERENCES `Trucks`(`truck_id` ),
FOREIGN KEY (`actual_order_id` ) REFERENCES `Actual_Orders`(`actual_order_id` ),
FOREIGN KEY (`location_code` ) REFERENCES `Delivery_Route_Locations`(`location_code` ),
FOREIGN KEY (`driver_employee_id` ) REFERENCES `Employees`(`employee_id` )
);

Question: Find the ids of orders whose status is 'Success'.
SQL: SELECT actual_order_id FROM actual_orders WHERE order_status_code  =  'Success'

Question: Find the name and price of the product that has been ordered the greatest number of times.
SQL: SELECT t1.product_name ,   t1.product_price FROM products AS t1 JOIN regular_order_products AS t2 ON t1.product_id  =  t2.product_id GROUP BY t2.product_id ORDER BY count(*) DESC LIMIT 1

Question: Find the number of customers in total.
SQL: SELECT count(*) FROM customers

Question: How many different payment methods are there?
SQL: SELECT count(DISTINCT payment_method) FROM customers

Question: Show the details of all trucks in the order of their license number.
SQL: SELECT truck_details FROM trucks ORDER BY truck_licence_number

Question: Find the name of the most expensive product.
SQL: SELECT product_name FROM products ORDER BY product_price DESC LIMIT 1

Question: Find the names of customers who are not living in the state of California.
SQL: SELECT customer_name FROM customers EXCEPT SELECT t1.customer_name FROM customers AS t1 JOIN customer_addresses AS t2 ON t1.customer_id  =  t2.customer_id JOIN addresses AS t3 ON t2.address_id  =  t3.address_id WHERE t3.state_province_county  =  'California'

Question: List the names and emails of customers who payed by Visa card.
SQL: SELECT customer_email ,  customer_name FROM customers WHERE payment_method  =  'Visa'

Question: Find the names and phone numbers of customers living in California state.
SQL: SELECT t1.customer_name ,  t1.customer_phone FROM customers AS t1 JOIN customer_addresses AS t2 ON t1.customer_id  =  t2.customer_id JOIN addresses AS t3 ON t2.address_id  =  t3.address_id WHERE t3.state_province_county  =  'California'

Question: Find the states which do not have any employee in their record.
SQL: SELECT state_province_county FROM addresses WHERE address_id NOT IN (SELECT employee_address_id FROM Employees)

Question: List the names, phone numbers, and emails of all customers sorted by their dates of becoming customers.
SQL: SELECT customer_name ,  customer_phone ,  customer_email FROM Customers ORDER BY date_became_customer

Question: Find the name of the first 5 customers.
SQL: SELECT customer_name FROM Customers ORDER BY date_became_customer LIMIT 5

Question: Find the payment method that is used most frequently.
SQL: SELECT payment_method FROM Customers GROUP BY payment_method ORDER BY count(*) DESC LIMIT 1

Question: List the names of all routes in alphabetic order.
SQL: SELECT route_name FROM Delivery_Routes ORDER BY route_name

Question: Find the name of route that has the highest number of deliveries.
SQL: SELECT t1.route_name FROM Delivery_Routes AS t1 JOIN Delivery_Route_Locations AS t2 ON t1.route_id  =  t2.route_id GROUP BY t1.route_id ORDER BY count(*) DESC LIMIT 1

Question: List the state names and the number of customers living in each state.
SQL: SELECT t2.state_province_county ,  count(*) FROM customer_addresses AS t1 JOIN addresses AS t2 ON t1.address_id  =  t2.address_id GROUP BY t2.state_province_county