SQL Assignment

SQL, Structure Query Language, is a programming language designed to manage data in relational database systems. It is the language running behind the scenes of a number of database systems from small personal ones created using Microsoft Access to applications that interface with large enterprise systems such as EPIC electronic medical record system used in places such as the Barnes Jewish Medical System, The Mayo Clinic, Johns Hopkins, MD Anderson and our local Saint Francis Healthcare system. SQL, or a similar version, is also used in a number of different industries and is pretty much the go to choice for querying large data sets. (One of my former MBA students works in Accounts Receivable at the Barnes Jewish system and he uses SQL, Tableau, Access, Pivot Tables and Excel regularly.)

Although large companies will probably have an IT department to create large queries and reports, individual users may be given limited access to data relating to their job functions. It will be up to those users to be able to pull the data that they need from the system for analyzing on their own or perhaps downloading into Excel or some other tool for analysis and report creation.

In this exercise, we will be taking an introductory look at SQL. This is a basic assignment and we will just be scratching the surface to get an idea of what SQL is and how it works. I think SQL is a good language for business data users to have some familiarity. I highly encourage you to keep the exercise instruction's tutorial links and consider revisiting SQL when you have more time. (I know that some of you have indicated that you use SQL in your workplace, so if any of you have any resources you would like to recommend, I would appreciate your passing along that information to me.)

Instructions

After you have worked through the SQLcoursee.com tutorials, using the SQL interpreter on the sqlcourse.com web site, write and test the SQL code needed to answer the following questions and then paste the code and your results beneath each question. Make sure your SQL statements work for you before pasting them below.

For questions 1 - 3, use the Employee table that was used in SQLcourses.com tutorials for Table Basics and Selecting Data. The questions below follow along with the tutorial. Please keep in mind that the questions build upon what you have learned on previous screens, so if you don't remember how to do something, look back at what you have learned thus far.

For Questions 1 - 3, please use the Employee table that was used in SQLcourses.com tutorials for Table Basics and Selecting Data.For each question, (1) write the SQL statements below and (2) copy your results. NOTE: I suggest that you write the SQL statements in the SQL Interpreter at sqlcourse.com and then paste them below. (Word puts some odd special characters into content that may cause your statements not to work if you paste from Word into the SQL Interpreter. (Please remember to include the semicolon ";" at the end of the SQL statement.)

1. Write a SQL statement to display all of the fields and all of the records in the empinfo table. (Be sure to include your exact SQL Statement below followed by a screen capture of your statement's results. Please do this for each question.)

2. Write a SQL statement to display only the first name, last name, city and state for everyone who is not from Arizona.

3. Write a SQL statement to display the first name, last name, and city of everyone with a ‘r' anywhere in their last name.

4. Create Accounts Receivabletable and use for the next set of questions.

Create a AR table containing the following fields: firstname, lastname, company, state, moneydue. Name the table with your first initial, last initial,the word artableand a random number to look something like "dsartable26" - this stands for Dana Schwieger ARTable 26.

4a. Copy the code that you used to create your table below. (SQL is case sensitive. Please note how you type your fieldnames.)

5. Enter the following customers into your table and add three more of your own with payments between 4000 and 5000 dollars. (Please make up data and do not input any people that you know.)

• Joe, Lynn, Acme Box, Missouri, 3050
• Amber, Wilson, Superstore, Illinois, 3550
• Tyler, Thomas, Fast Cars, Illinois, 3475
• Austin, Powers, Big Bike, Missouri, 4650
• Tyler, Andrews, Accounting, Kentucky, 4250

5a. Show the SQL code for two of your records. (In writing the SQL code below, you only need to show me the code for two of your records (part a), but show the results for all of the entries (part b).

5b. Show the results for all of the entries (part b).

6. Write a SQL statement to display the first name, last name, company and moneydue of all clientsowing more than 3500.

7. Write a SQL statement to update all Missouri states to MO.

8. Write a SQL statement to delete all customers with the state value of MO.

9. Write a SQL statement to drop your table to delete it from the system. Run your SQL statement to drop your table.

Use the Items Ordered Table for the following questions. (Be sure to paste your SQL statements and results beneath each question.)

10. Write a SQL statement to display customerid, order_date, item and price from the items_ordered table that end with the letter ‘s'.

11. Write a SQL statement to find the total number of tents ordered.

12. Write a SQL statement to find how many unique orders occurred each day in the items_ordered table. Select the display date, item and the number ordered for each date.

13. Write a SQL statement to find out the sum of customer orders and the total number of items each customer purchased. Select the customerID, the sum of the quantity that was purchased, and the sum of the price.

14. Write a SQL statement to display the customerid, order_date, item, and price from the items_ordered table where the price is less than 10.00 Display the results in descending order based upon customerid.

15. Write a SQL statement using a join to determine which items were ordered by each of the customers in the customers table. Select the customerid, firstname, lastname, state, order_date, item, quantity, and price for everything each customer purchased in the items_ordered table.