ADAN|ADEC7910 Software Tools
SQL Homework
The DataCamp course Introduction to SQL provides a baseline knowledge of the structured query language. Complete the DataCamp course. Follow the directions posted to Week 6 to install MySQL Workbench and to import the stda database.
Complete the following prompts and submit a PDF or word document with your responses. Some of the prompts require commands not covered in the DataCamp course, but are readily found online. I will be testing your ability to self teach several commands. I recommend using www.w3schools.com as your primary resource, although you may prefer others.
Part 1: Comprehension
(10 pts) Based on the DataCamp activity “Introduction to SQL,” what are the advantages of SQL over spreadsheets?
(10 pts) Based on the DataCamp activity “Introduction to SQL,” what are keywords in SQL? Are they lowercase or capital, and why?
(10 pts) Based on the DataCamp activity “Introduction to SQL,” what is a field in SQL? Are they lowercase or capital, and why?
Part 2: Implementation
(10 pts) The stda database has a table named orders, which can be referenced as stda.orders.
Create a new view called my_view. my_view should select all fields from stda.orders where the segments is equal to “Home Office” . Provide the script. that generates the specified view.
https://www.w3schools.com/sql/sql_view.asp
https://www.youtube.com/watch?v=vLLkNI-vkV8
(10 pts) Provide a line of code that will SHOW all the tables in the current schema.
Screenshot the results.
(10 pts) Provide a line of code that will SELECT all the fields FROM my_view
Screenshot the results.
(10 pts) SELECT the COUNT of records in all the fields as a result called “number of rows” WHERE the field “quantity” is greater than 4.
Screenshot the results.
https://www.w3resource.com/sql/aggregate-functions/count-function.php
Part 3: Self Teaching
(10 pts) Discuss a specific business use case of joining tables in this database.
(10 pts) Provide the code to join the suggested tables.
(10 pts) Screenshot the resulting table of the join statement.