Instructions: Write the SQL code to perform the tasks requested in each problem. Screenshots are required for each SQL statement for a grade to be given.
PART 1; SELECT
1) List the customers numbers, the name (first and last), and the balance of customers.
2) List the customer number, the name (first and last), and the balance of customers who reside in Colorado (CustState is Co).
3) List all columns of the product table for products costing more than $50. Order the result by product manufacturer (ProdMfg) and product name.
4) List the order number, order date, and shipping name (OrdName) of orders sent to addresses in Denver or Englewood.
5) List the customer number, the name (first and last), the city, and the balance of customers who reside in Denver with a balance greater than $150 or who reside in seattle with a balance greater than $300.
6) List the cities and states where orders have been placed. Remove duplicates from the result.
7) List all columns of the OrderTbl table for internet orders placed in January 2010. An internet order does not have an associated employee.
8) List all columns of the OrderTbl table for phone orders placed in February 2010. A phone order has an associated employee.
9) List all columns of the product table that contain the word ink jet in the product name.
10) List the order number, order date, and customer number of orders placed after January 23, 2010, shipped to Washington recipients.
11) List the order number, order date, customer number, and customer name (first and last) of orders placed in January 2010 sent to Colorado recipient.
12) List the average balance of customers by city. Include only customers residing in Washington state (WA).
PART 2 INSERT, UPDATE, AND DELETE statement
1) Insert yourself as a new row in the customer table
2) Insert your roommate, best friend, or significant other as a new row in the Employee table.
3) Insert a new OrderTbl row with you as the customer, the person from problem 2 (part 2) as the employee, and your choice of values for the other columns of the OrderTbl table.
4) Insert two rows in the OrdLinetable corresponding to the OrderTbl row inserted in problem 3 (part 2).
5) Increase the price by 10 percent of products containing the words Ink Jet.
6) Change the address (street, city, and zip) of the new row inserted in problem 1 (part 2).
7) Identify an order that respects the rules about deleting referenced rows to delete the rows inserted in problems 1 to 4 (part 2).
8) Delete the new row(s) of the table listed first in the order for problem 7 (part2).