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 instructions 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.)

Tutorials

There are a number of good free tutorials online. In this exercise, I suggest using www.sqlcourse.com because it is short, straightforward, free and provides a SQL interface in which you can work through the exercise. I wrote the questions below to follow along with the SQLcourse tutorial.

If you would like to try another tutorial, you may want to try https://www.codecademy.com/learn I started working through this tutorial and liked it as well. Its a little longer and provides more depth on database terminology. DoFactory has a nice site as well as a SQL interpreter http://www.dofactory.com/sql/tutorial Vertabelo has a very nice summary of 18 online tools that you can use in order to learn SQL found at https://academy.vertabelo.com/blog/18-best-online-resources-for-learning-sql-and-database-concepts/

Instructions

After you have worked through the SQLcourse.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. To get a screen capture of your results, you may want to use the Windows Snippet tool or you can click the key on your keyboard to copy your contents to the Windows clipboard and the paste the contents into Word V.)

5. Create and use an Accounts Payable (AP) table for the following questions

Create an AP table containing the following fields: firstname, lastname, company, item, state, bill. Name the table with your first initial, last initial, the word artable and a random number to look something like dsartable26 - this stands for Dana Schwieger AP Table 26. (I just added 26 as a random number since there may be other people with those initials.)

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

6. Enter the following vendors into your table and add two more of your own with invoices between 2000 and 4000 dollars. (Please make up data and do not input any people that you know.) As far as I know, you will need to write the code to enter each record individually.

James, Box, Acme Medical, Gauze tape, Missouri, 2050

Bill, Smart, Computer Store, Tablets, Illinois, 2550

Jamie, Ernst, Orthotics, Inc., Splints, Illinois, 2475

Bob, Armstrong, Hospital Supplies, Inc., Splints, Missouri, 3650

Martha, Waters, Office Supply Store, Gauze tape, Kentucky, 3250

6a. Show the SQL code for entering 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).

6b. Use a SELECT statement to show the results for all of the entries (part b).

7. Write a SQL SELECT statement to display the first name, last name, company and bill of all vendors with a bill over 2500.

8. Write a SQL statement to update all Illinois states to IL.

9. Write a SQL statement to delete all vendors with the item value of Splints.

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

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

11. Write a SQL statement to display order_date, item, quantity and price from the items_ordered table for items whose quantity sold was equal to 2 or more.

12. Write a SQL statement to find the total number of products containing the word cycle ordered.

13. Write a SQL statement to display the customerid, order_date, item, and price from the items_ordered table where the price is between 50.00 and 100.00 Display the results in ascending order based upon item.

14. 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, lastname, city, state, order_date, item, quantity, and price for everything each customer purchased in the items_ordered table.