SQL Query Design Module Preview This guide contains a preview of the materials for all module courses in this series: Using a Relational Database Management System Single Table Query Commands Composing Structured Query Language Queries Designing Structured Query Language Syntax Be sure to follow the detailed directions found within the actual module course after you receive access. The content in this guide is only a preview and course content may have changed. For more information about taking module courses, visit the Traditional and Module Courses page (requires login). Course Materials MediaType: PG Library Title: SQL Queries for Mere Mortals: A Hands-On Guide to Data Manipulation in SQL Edition: 4th (2018) Author: Viescas, John Publisher: Pearson Book ISBN: 978-0-134-85833-3

Course Code
Module Course Title	Using a Relational Database Management System
Outcome(s)	Use a Relational Database Management System (RDBMS) for effective database installation and manipulation.

Learn: Reading and Research The reading material introduces you to Structured Query Language (SQL) Data Definition Language (DDL) syntax and commands to create, insert, update, and delete database content in SQL Server. The textbook used in this class isSQL Queries for Mere Mortals, 4^thedition.Chapters 1, 2, and 1517 are the required readings for this module. It is recommended that, before searching the web, you look in the assigned readings for how to perform the SQL content being studied. Feel free to cite the assigned readings as sources in your discussion posts and your assignments. If you do need more material, the following sites are recommended:

• W3school.com
• Tutorialspoint.com
• SQLZoo.Net

Competency Assessment This Competency Assessment assesses the following outcome(s): Use a Relational Database Management System (RDBMS) for effective database installation and manipulation. Assignment Instructions Put your name, course, module number, and date at the beginning of your assignment in your MicrosoftWorddocument. Part 1.Install SQL Server Express and Class-Required Databases Put the header "Part 1" in your assignment Word document. Install SQL Server. You candownload SQL Sequel Expressfrom the Microsoft website. Please review and follow the Module 1 Learning Activity.The Module 1 Learning Activity steps through the process of downloading and installing SQL Server Express, attaching one of the databases used in the class, and working with SQL Servers Management Studio to execute a SQL query and produce a result set. The databases you will attach to your SQL Server installation should include the following databases from the textbook resource site:

1. BowlingLeagueExample (attached)
2. EntertainmentAgencyExample (attached)
3. RecipesExample (attached)
4. SalesOrderExample (attached)
5. SchoolSchedulingExample (attached)

Directions for locating and downloading the sample databases are detailed on pages 9 and 10 of the class required textbook.

1. AdventureWorks (OPTIONAL from backup or script)

Submit a Windows Snipping Tool picture (or screenshot) of your SQL ServerManagement Studio window with the installed databases displayed. Your installed databases should include all five of the databases downloaded from the textbook resource site. Required Comment Addition for All Assignments SQL Query Questions You will be required to include a WindowsSnipping Tool picture (or screenshot) with every SQL query question you submit within all the assignments in all modules of the SQL Query Design class. Failure to include the required comment at the top of the SQL query question will result in zero points earned for the question result submission regardless of whether the SQL query syntax and result set are correct or not. /* Student: Your name here Instructor: Your instructors name here Date: 7/9/2020 */ Note:If you forget the /* */ symbols in your comment, you will get an error when the SQL query is executed in SQL Server. An example of a correct question submission from a query in SQL Server to produce all attributes and tuples within the bowlers table is provided in the following picture. Example of required question comment, SQL syntax, and result set: Part 2.Data Manipulation Language (DML) Adding Tuples Put the header "Part 2" in your assignment document. Number your answers to correspond to the number of the question you are answering (e.g., 2-1, 2-2). 2-1: Database: BowlingLeagueExample Table: Bowler Activity: Add a tuple to the database with the following attribute values (do not include the <<< >>> symbols): BowlerID: <<< 200 >>> BowlerLastName: <<< Your Last Name >>> BowlerFirstName: <<< YourFirstName >>> BowlerMiddleInit: NULL BowlerAddress: <<< 123 PurdueGlobal Ave. >>> BowlerCity: <<< West Lafayette >>> BowlerState: <<< IN >>> BowlerZip: <<< 47907 >>> BowlerPhoneNumber: <<< (765) 123-4567 >> TeamID: <<< 8 >>> 2-2: Database: BowlingLeagueExample Table: Teams Activity: Add a tuple to the database with the following attribute values (do not include the <<< >>> symbols): TeamID: <<< Quarter and Year in Numbers (e.g., 319 would be quarter 3 in the year 2019) >>> TeamName: <<< Your Initials Here (Your initials will be the team name) >>> CaptainID: <<< 200 >>> 2-3: Database: BowlingLeagueExample Table: Tournaments Activity: Add a tuple to the database with the following attribute values (do not include the <<< >>> symbols): TourneyID: <<< 99 >>> TourneyDate: <<< Use todays date >>> TourneyLocation: <<< Imperial Lanes >>> 2-4: Database: EntertainmentAgencyExample Table: Members Activity: Add a tuple to the database with the following attribute values (do not include the <<< >>> symbols): MemberID: <<< Quarter and Year in Numbers (e.g., 319 would be quarter 3 in the year 2019) >>> MbrFirstName: <<< Your first name >>> MbrLastName: <<< Your last name >>> MbrPhoneNumber: <<< 332-9817 >>> Gender: <<< NULL >>> 2-5: Database: SchoolSchedulingExample Table: Major Activity: Add a tuple to the database with the following attribute values (do not include the <<< >>> symbols): MajorID: <<< Quarter and Year in Numbers (e.g., 319 would be quarter 3 in the year 2019) >>> Major: <<< Information Tech >>> Part 3.Data Manipulation Language (DML) Updating Tuples Put the header "Part 3" in your assignment document. Number your answers to correspond to the number of the question you are answering (e.g., 3-1, 3-2). 3-1: Database: BowlingLeagueExample Table: Bowler Activity: Add a tuple to the database with the following attribute values (do not include the <<< >>> symbols): BowlerID: <<< 500 >>> BowlerLastName: <<< Your Last Name >>> BowlerFirstName: <<< YourFirstName >>> BowlerMiddleInit: NULL BowlerAddress: <<< 123 PurdueGlobal Ave. >>> BowlerCity: <<< West Lafayette >>> BowlerState: <<< IN >>> BowlerZip: <<< 47907 >>> BowlerPhoneNumber: <<< (765) 123-4567 >> TeamID: <<< 8 >>> Activity: Update the added tuple to the database. Update BowlerPhoneNumber to: <<< (555) 555-5555 >>> 3-2: Database: BowlingLeagueExample Table: Teams Activity: Add a tuple to the database with the following attribute values (do not include the <<< >>> symbols): TeamID: <<< 500 >>> TeamName: <<< Your Initials Here (Your initials will be the team name) >>> CaptainID: <<< 200 >>> Activity: Update the added tuple to the database. Update TeamName to: <<< PGU >>> 3-3: Database: BowlingLeagueExample Table: Tournaments Activity: Add a tuple to the database with the following attribute values (do not include the <<< >>> symbols): TourneyID: <<< 500 >>> TourneyDate: <<< Use todays date >>> TourneyLocation: <<< Imperial Lanes >>> Activity: Update the added tuple to the database. Update TourneyLocation to: <<< Tommy Lanes >>> 3-4: Database: EntertainmentAgencyExample Table: Members Activity: Add a tuple to the database with the following attribute values (do not include the <<< >>> symbols): MemberID: <<< 500 >>> MbrFirstName: <<< Your first name >>> MbrLastName: <<< Your last name >>> MbrPhoneNumber: <<< 332-9817 >>> Gender: <<< NULL >>> Activity: Update the added tuple to the database.Update MbrPhoneNumber to: <<< 555-5555 >>> 3-5: Database: SchoolSchedulingExample Table: Major Activity: Add a tuple to the database with the following attribute values (do not include the <<< >>> symbols): MajorID: <<< 500 >>> Major: <<< Information Tech >>> Activity: Update the added tuple to the database. Update Major to: <<< Bio Science >>> Part 4.Data Manipulation Language (DML) Deleting Tuples Put the header "Part 4" in your assignment document. Number your answers to correspond to the number of the question you are answering (e.g., 4-1, 4-2). 4-1: Database: BowlingLeagueExample Table: Bowler Activity: Delete the previously updated tuple with the BowlerPhoneNumber equal to: (555) 555-5555 4-2: Database: BowlingLeagueExample Table: Teams Activity: Delete the previously updated tuple with the TeamName equal to: PGU 4-3: Database: BowlingLeagueExample Table: Tournaments Activity: Delete the previously updated tuple with the TourneyLocation equal to: Tommy Lanes 4-4: Database: EntertainmentAgencyExample Table: Members Activity: Delete the previously updated tuple with the MbrPhoneNumber equal to: 555-5555 4-5: Database: SchoolSchedulingExample Table: Major Activity: Delete the previously updated tuple with the Major equal to: Bio Science Part 5.Data Definition Language (DDL) Creating Tables Put the header "Part 5" in your assignment document. Number your answers to correspond to the number of the question you are answering (e.g., 5-1, 5-2). Using theM1 Assignment Part 5 Reference Document, perform the following activities: 5-1: Create a new database called PetAdoptionInfo in SQL Server. 5-2: Create six new tables in the PetAdoptionInfo database with the required attributes characteristics as noted in the provided Create Table Resource Data document within the M1 Assignment Part 5 Reference Document. Make sure you create the table structures to account for the relationships and the foreign keys needed in each table based on the entity-relationship (ER) model in the Create Table Resource Data document. 5-3: Insert the provided data as new tuples in each of the six tables respectively based on the Create Table Resource Data document within the M1 Assignment Part 5 Reference Document. 5-4: Create SQL Server queries for each of the six PetAdoptionInfo database tables to display all tuples and attributes in each of the tables. After you receive access to each module, you will be able to see the submission requirements and a Checklist Rubric for the Competency Assessment.

Course Code
Module Course Title	SQL Single Table Query Commands
Outcome(s)	Apply SQL single table query commands effectively.

Learn: Reading and Research The reading material covers the basics of SQL Data Manipulation Language (DML) syntax with single table query syntax, structure, and expressions in SQL Server. Chapters 4, 5, and 6 inSQL Queries for Mere Mortals(4^thedition) are the required readings for this module. The readings in this module also cover the concepts needed to expand your single table SQL query knowledge. Practice examples are included in the latter part of the chapter. These will provide SQL questions to apply the concepts you learned throughout this modules reading requirements. Competency Assessment This Competency Assessment assesses the following outcome(s): Apply SQL single table query commands effectively. Advanced Single Table Queries Purpose In the Module 2 Assignment, you will have an opportunity to practice what you learned with advanced single table query techniques and structures. Like most types of information technology skills, SQL proficiency is accomplished through repetition and practice, and you will have an opportunity to do that in the class in general and this Module 2 in particular. Assignment Instructions This assignment includes advanced single table query questions using the sample databases attached to the SQL Server implementation installed in Module 1. Remember to add the personalized comment as noted at the beginning of Module 1 before each of your answer submissions of SQL code and corresponding result set. Failure to include the required comment with each question will result in a grade of zero. Assignment Requirements Remember to make sure the correct database is active before executing a SQL query. Visit the Writing Center for more information on APA style formatting. Also, review the university policy on plagiarism. If you have any questions, please contact your professor. Module 2 Questions: Advanced Single Table Queries Use the Sales Order Database to answer the following Module 2 Assignment questions:

1. Provide a result set with the customer name, area code, and phone number of all customers that live in a city that begins with an S.
2. Provide a result set with the product name of all products that have a retail price over 50 and a quantity on hand greater than 18. List the result set in alphabetic order.
3. Provide a result set with the vendors that we work with that do not have a website and are from Alaska.
4. Provide a result set with the product name that has Dog in it. Include product name, retail price, and quantity on hand in your query results.

Use the Entertainment Agency Database to answer the following Module 2 Assignment questions:

1. Provide a result set of all engagements that occurred during September 2017. Only include engagement number and customer id in your result set.
2. Provide a result set of the entertainers stage names that have websites. Sort the result set in descending alphabetic order.
3. Provide a result set of all the musical style names that begin with a c.
4. Provide a result set of the first and last names of members who are female. Order your result set in alphabetical order by first name within last name.

Use the School Scheduling Database to answer the following Module 2 Assignment questions:

1. Provide a result set of the staffid of all staff members who are either a professor or an associate professor and have a status of full time.
2. Provide a result set of all the classid of the classes set to start in November 2017 that are on a Monday schedule.
3. Provide a result set of all the department names that have science in them and their department chairs.
4. Provide a result set with the student ids and class ids for all instances in which students earned a grade above 80 and below 90.

Use the Bowling League Database to answer the following Module 2 Assignment questions:

1. Provide a result set of the tournaments that were held in July or August of 2018.
2. Provide a result set of all the bowler ids, first names, and last names for all bowlers who do not have a middle initial in the table. Sort the result set by bowler id in descending order.
3. Provide a result set of the bowlers who live in Bellevue, Bothell, Duvall, Redmond, or Woodinville and are on teams 58.
4. Provide a result set of the bowler ids for all bowlers who had a handicap score less than 30 above their raw score and won their game.

Use the Recipes Database to answer the following Module 2 Assignment questions:

1. Provide a result set of the ingredient class ids that have a description that begins with s or p.
2. Provide a result set of all the ingredient names that have an ingredient class id of 3, 4, or 9. Sort the ingredient names in ascending order.
3. Provide a result set of all the recipe titles that begin with S and have a value for the notes attribute.
4. Provide a result set of the ingredient names of all ingredients that have an ingredient class id of 3 or 10 and have a measure amount id greater than 5.

After you receive access to each module, you will be able to see the submission requirements and a Checklist Rubric for the Competency Assessment.

Course Code
Module Course Title	Composing Structured Query Language Queries
Outcome(s)	Compose Structured Query Language (SQL) queries for database information analysis.

Learn: Reading and Research Chapter 7: Thinking in Sets Chapter 8: Inner Joins Chapter 9: Outer Joins Chapter 10: Unions Chapter 11: Subqueries The readings in this module introduce working with and combining multiple tables of information together to provide informative result sets. Practice examples are included in the latter part of the chapters to apply the concepts you learned throughout the reading requirements. In addition, this module covers the concepts needed to expand your knowledge of multiple table queries and subqueries in SQL. Competency Assessment This Competency Assessment assesses the following outcome(s): Compose Structured Query Language (SQL) queries for database information analysis. Advanced Multiple Table Queries Purpose In this Module 3 Assignment, you will be required to answer Structured Query Language (SQL) problems involving multiple tables and subqueries. The SQL techniques you will need to apply are covered within Chapters 9, 10, and 11 of the required textbook. Assignment Instructions This assignment includes multiple table queries using the sample databases attached to the SQL Server implementation installed in Module 1. Remember to add the personalized comment as noted at the beginning of Module 1 before each of your answer submissions of SQL code and corresponding result set. Failure to include the required comment with each question will result in a grade of zero. Assignment Requirements Remember to make sure the correct database is active before executing a SQL query. Visit the Writing Center for more information on APA style formatting. Also, review the university policy on plagiarism. If you have any questions, please contact your professor. Module 3 Questions: Multiple Table Queries Use the Sales Order Database

1. Provide a result set of the customers first and last name and phone numbers for all customers who have ordered clothing. Make sure duplicate values are not included in the result set.
2. Provide a result set of the product name and quantity on hand for all products that have not been ordered.
3. Provide a result set of the employees first and last names for all employees that have sold accessories. Be sure not to include duplicates in your result set. Sort the result set in alphabetical order by employee last name.
4. Provide a result set of the product name and description for all products ordered by a vendor that has bike somewhere in the vendors name.
5. Provide the name of the product that is the most expensive we sell that is purchased from ProFormance.
6. Provide a result set of the product names we sell that are purchased from a vendor that does not have a web page.
7. Provide a result set of the vendor names of all the vendors that sell us products that are bikes.

Use Entertainment Agency Database

1. Provide a result set of all the entertainers stage names and the count of each entertainers engagements.
2. Provide a result set of all the first and last names of all customers who have a preference for salsa and have booked an engagement. Make sure there are no duplicate values in the result set.
3. Provide a result set of the stage names of entertainers who have played an engagement for customers from Bellevue. Make sure there are no duplicate values in the result set.
4. Provide a result set of the entertainers stage names for all entertainers who have not been booked to an engagement.

Use School Scheduling Database

1. Provide a result set of all the first and last names of the staff members and the count that each teaches.
2. Provide the first and last names of the students enrolled in a class that begins at 11 a.m. Sort the result set by students last name in alphabetical order.
3. Provide a result set of all the first and last names of the faculty who teach in a classroom without a phone available. Make sure there are no duplicate values in the result set.
4. Provide a result set of the first and last names of all students who attend a class in a classroom that has a capacity > 80.

Use Bowling League Database

1. Provide a result set of the first and last names of the bowlers with a count of the games each bowled.
2. Provide a result set of the first and last names of the bowlers who have bowled a game with a raw score of 200 or more. Please remove duplicates from the result set and sort the result set in alphabetical order by last name.
3. Provide a result set of the team names and a count of the number of matches each team has won.

Use Recipes Database

1. Provide a result set of the types of recipes and the count of recipes in each type. Sort the result set in descending alphabetical order.
2. Provide a result set of the recipe titles for all recipes that have to taste for any of their measurements.

After you receive access to each module, you will be able to see the submission requirements and a Checklist Rubric for the Competency Assessment.

Course Code
Module Course Title	Designing Structured Query Language Syntax IT
Outcome(s)	Design Structured Query Language (SQL) syntax to summarize and group data. Use language that is clear, concise, and organized.

Learn: Reading and Research Chapter 12: Simple Totals Chapter 13: Grouping Data Chapter 14: Filtering Grouped Data The readings in this module cover the concepts needed to expand your knowledge of grouping and filtering data in SQL. Practice examples are included in the latter part of the chapter. These will provide SQL questions to apply the concepts you learned throughout this units reading requirements. Competency Assessment This Competency Assessment assesses the following outcome(s): Design Structured Query Language (SQL) syntax to summarize and group data. Use language that is clear, concise, and organized. Grouping and Filtering Data in SQL Purpose In this Module 4 Assignment, you will be required to answer Structured Query Language (SQL) problems involving grouping and filtering data in using Structured Query Language code. The SQL techniques you will need to apply are covered within Chapters 12, 13, and 14 of the class textbook. Assignment Instructions This assignment includes multiple table queries using the sample databases attached to the SQL Server implementation installed in Module 1. Remember to add the personalized comment as noted at the beginning of Module 1 before each of your answer submissions of SQL code and corresponding result set. Failure to include the required comment with each question will result in a grade of zero. Assignment Requirements Remember to make sure the correct database is active before executing a SQL query. Visit the Writing Center for more information on APA style formatting. Also, review the university policy on plagiarism. If you have any questions, please contact your professor. Module 4 Assignments (Part A and B) Part A: Structured Query Language (SQL) Questions Use Sales Order Database

1. Produce a result set that displays the total for order 8.
2. Produce a result set that displays the number of orders that have three or fewer items.

Use Entertainment Agency Database

1. Produce a result set that displays the average contract price for entertainer with the stage name Saturday Revue.
2. Produce a result set of the number of entertainers who are from a city that begins with a B or an S.
3. Produce a result set of each agents name, the sum of the contract price for the agents engagements booked, and the agents total commission for agents whose total commission is more than $700.

Use School Scheduling Database

1. Produce a result set of the number of classes with which Tim Smith is associated.
2. Produce a result set of the number of classes taught by every staff member who is an associate professor.

Use Bowling League Database

1. Produce a result set of the average raw scores for all bowlers.
2. Produce a result set of the number of tournaments held at a tourney location that begins with S or T.
3. Produce a result set that displays the bowlers name and the average of the bowlers raw game scores for bowlers whose average is greater than 145.
4. Produce a result set that lists the first and last name of every bowler whose average handicap score is greater than the overall average handicap score.

Use Recipes Database

1. Produce a result of the recipe name that requires the most salt.

Part B. The Future Use of Structured Query Language (SQL) in All Kinds of Databases (transactional, data warehouses, big data, etc.) Write an essay focused on the predicted use of SQL in all types of databases of the future. The essay should be a minimum of 400 words. Ensure the structure of the language throughout your essay is clear, concise, and organized. Use a minimum of three good sources of information. Cite sources properly, using in-text citations and double quotes where appropriate, and have a proper reference list at the end of your essay. At least 75% of the writing must be in your own words. Assignment Requirements Your paper should be 24 pages in length. Be sure to use appropriate APA format and cite your textbook or other sources that you used in your paper. The paper should contain sufficient information to adequately answer the questions and contain no spelling, grammar, or APA errors. Points deducted from grade for each writing, spelling, or grammar error are at your instructors discretion. For more information on APA style formatting, go to Academic Writer under Academic Tools of this course. Also review the university policy on plagiarism. If you have any questions, please contact your instructor. After you receive access to each module, you will be able to see the submission requirements and a Checklist Rubric for the Competency Assessment.

Attachments:

sql-querry-de....docx