ISM 465: Data Acquisition and Analytics Development
Data and Databases
- Understand the look and feel of a database
- Experience using a database
- Practice interpreting the database output
For this assignment, complete the following 13 queries. Use the W3Schools link: https://www.w3schools.com/sql/trysql.asp?filename=trysql_select_all.
- Be sure to type each query (e.g. customername) versus a direct copy and paste into the SQL tool. When a query is copied and pasted, it may not work due to a format issue. If you do not know what each statement means, please refer to https://www.w3schools.com/sql/default.asp and practice each of them before you submit your homework.
- For each question, copy and paste both the query and screen shot of the results page (see example in question one).
- Grading Policy: To receive full points, you must provide both the query and screen shot results page with date/time.
You may find the answer to the first 13 questions in our SQL Lab Tutorial.
- Show first names and last names of employees in the database. See example below.
- Pull all of the attributes from the Employees table.
3a. Show CustomerName and ContactName in the city of London from the customers table.
3b. Show CustomerName and ContactName excluding the city of London from the customers table.
- Find the average price for each supplier.
- Find supplier with an average price greater than $20.
- Find suppliers with an average price greater than $20 with supplier IDs greater than
five. Sort the SupplierIDs greater than five in descending order.
- Pull all the attributes of the product with product ID greater than five, CategoryID equal to four, and price greater than $10 from the products table.
- Show SupplierID AND SupplierName of suppliers with (A) SupplierID greater than OR equal to two but smaller than ten OR (B) country equal to USA [Use WHERE OR]
- Show SupplierID AND SupplierName of suppliers with SupplierID greater than 2 but smaller than 10 [Use WHERE BETWEEN AND]
- Show SupplierID AND SupplierName of suppliers with SupplierID equal to two, four, or eight. [Use WHERE OR]
- Show SupplierID AND SupplierName of suppliers with SupplierID equal to two, four, or eight. [Use WHERE IN]
- Pull all customers’ cities that start with “S” and postal codes that start with “9”.
- Pull a list of order IDs and employee (including their last name and first name) who processed each order (organized alphabetically by employee last name).