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

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 dont remember how to do something, look back at what you have learned thus far.

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.