Sams Teach Yourself SQL in 10 Minutes (Fifth Edition) includes challenges at the end of some lessons. Solutions to the challenges are presented here. Just keep in mind that there is rarely one solution to a SQL challenge, so if your solutions look different but produce the desired result, that’s ok.
Create a view called CustomersWithOrders that contains all of the columns in Customers, but only includes those who have placed orders. Hint, you can JOIN the Orders table to filter just the customers you want. Then use a SELECT to make sure you have the right data.
CREATE VIEW CustomersWithOrders AS SELECT Customers.cust_id, Customers.cust_name, Customers.cust_address, Customers.cust_city, Customers.cust_state, Customers.cust_zip, Customers.cust_country, Customers.cust_contact, Customers.cust_email FROM Customers JOIN Orders ON Customers.cust_id = Orders.cust_id; SELECT * FROM CustomersWithOrders;
What is wrong with the following SQL statement? (Try to figure it out without running it):
CREATE VIEW OrderItemsExpanded AS SELECT order_num, prod_id, quantity, item_price, quantity*item_price AS expanded_price FROM OrderItems ORDER BY order_num;
ORDER BY is not allowed in views. Views are used like tables, if you need sorted data use ORDER BY in the SELECT that retrieves data from the view.