Take your time, relax, read all directions carefully and show me your best work. When you get to the queries, give me anything you can; I’ll give you partial credit for anything useful you include. Th

Take your time, relax, read all directions carefully and show me your best work. When you get to the queries, give me anything you can; I’ll give you partial credit for anything useful you include. There’s no best score write them in Cloud9, make sure they work, paste them into the exam and fix the formatting, line breaks, etc.

Question 1

What type of JOIN returns all rows from the first table and any matching rows from the second table?



Question 2

Which records will the following FROM clause select? FROM Faculty LEFT JOIN Classes

All Faculty records and any Class records that are related to them Any Class records that are related to Faculty records

All Class records and any Faculty records that are related to them Any Faculty records that are related to Class records

Question 3

When testing for NULL values to find Customers who have no Orders, which column is best to include in the WHERE clause below: WHERE                                        IS NULL

Any foreign key from the Customers table The primary key from the Customers table The OrderDate column from the Orders table The primary key from the Orders table

Question 4

What type of JOIN returns all rows from the second table and any matching rows from the first table?



The results of a UNION cannot be sorted At the end of each SELECT statement

At the end of the last SELECT statement At the end of the first SELECT statement

Question 7

When the                       keyword is left out of a UNION, the database system automatically eliminates any duplicate rows.




Question 8

Which of the following is a requirement for the columns of the SELECT statements in a UNION?

Must have the same number of output columns specified. Neither of these are requirements.

Each corresponding column must have a comparable value. Both of these are requirements.

Question 9

A SELECT expression that is embedded within one of the clauses of a SELECT statement is a(n)                                                                                                                                        .

embedded SELECT sub-SELECT embedded query subquery

Question 10

Which of the following is a request that can be answered using a subquery?

Create a list that combines agents and entertainers.

Create an index list of all the recipe classes, recipe titles, and ingredients. Display products and the latest date the product was ordered.

Show me the students who have a grade of 85 or better in Art together with the faculty members who teach Art and have a proficiency rating of 9 or better.

Question 11

What type of subquery is an embedded SELECT expression that returns only one column and no more than one row?

Table subquery Row subquery Scalar subquery

Question 12

Which aggregate function calculates the mean for a value expression?

Question 14

When using an aggregate function, what is the name of the column in the final result set?

The name is generated by the database system. The name assigned in the AS clause.

None of these are possible names for the column.

All of these are possible names for the column. The column has no name.

Question 15

What character is used to separate the columns referenced in the GROUP BY clause?

Comma , Period . Colon :

Semicolon ;

Question 16

Which clause is used to group matching values of a specified column together?



Question 17

When the GROUP BY clause is used without aggregate functions it will give you the same result as what keyword?


Question 18

Columns referenced in the WHERE clause must be a part of what?

The columns referenced in the ORDER BY clause. Tables defined in the FROM clause.

The columns referenced in the SELECT clause. The columns used in aggregate functions.

Question 19

Columns referenced in the HAVING clause must also be referenced where?

Enclosed within an aggregate function

Question 21

What SQL feature enables you to undo a series of changes to the data?

System Updates UPDATE Undo Transactions

UPDATE statements

Question 22

What character is used to separate multiple assignment statements in the SET clause?

Colon :

Semicolon ; Comma ,

Period .

Question 23

What need would NOT be addressed with an UPDATE statement?

Increasing the price of all sports supplements by 3%

Creating a list of all supplements that contain Creatine by manufacturer

Appending the word ?powder? to all ItemDescriptions for MuscleMilk products Changing the category of all sports supplements to ?Nutrition?

Question 24

If the new value of a column involves the old or current value of the column, the column must be referenced where?

The left side of the equal sign The UPDATE clause

The WHERE clause

Both sides of the equal sign

Question 25

Data can be inserted into a table or what other database item?

View Report


Stored procedure

Question 26

When the INSERT statement is fetching rows of data to be placed into a table, the SELECT expression is used in place of what?

The VALUES clause The value expression The column name list

The table name


Question 29

What would keep the DELETE statement without the WHERE clause from deleting all the rows in a table?

System logs

Referential integrity constraints Transactions

Question 30

What is a possible method for ensuring that the correct rows are deleted from the database before the DELETE statement is run?

Using transactions

Creating a SELECT statement and then transferring the WHERE clause to the DELETE statement Using system logs

Question 31

We are planning our 1st Quarter marketing budget and need you to provide some historical sales data from the SalesOrdersModify database. Provide summarized data regarding the orders we received d out by customer city and state. Include customer city and state names along with the count of all orders, the average order total, smallest and largest order totals and the sum of all orders from that city and Please make sure the average is formatted with two digits after the decimal place. (20 rows)

Question 32

We are planning a postcard mailing to promote our performers who play Show Tunes or Standards. Using the EntertainmentAgencyModify database, list the full name and mailing address for all customers play Show Tunes or Standards so that we can include them in the mailing. (Hint: create a subquery to list all customers who DID book performers who play those musical styles and then test for NULL to s use the names of the musical styles, not the styleID numbers. (7 rows)

Our EntertainmentAgencyModify database is encountering performance issues because of its size. Archive all Engagements that both started and ended prior to March 1, 2018 into the Engagements_Arch remove them from the original Engagements table to reduce the size of that table. Remember to use transactions for each of these two queries to protect your data. (45 rows)

(Note: Refer to the schema in Question 32 for relationships.)

Question 34

We are looking for customer endorsements of the performer “Modern Dance”. Provide a list of names and phone numbers for any customers in the EntertainmentAgencyModify database who have ever bo these engagements may now be archived. Put the list of customers in alphabetical order by last name and first name. (Hint: use a SQL command that will allow you to combine the results of two similar qu Engagements_Archive, into a single result set.) (8 rows)

Take your time, relax, read all directions carefully and show me your best work. When you get to the queries, give me anything you can; I’ll give you partial credit for anything useful you include. Th
Intro to Database Management Resource Sheet If you wish to continue experimenting with and learning about databases and SQL Queries, consider trying the following resources, most of which are free. Local Database Servers I use the term “servers” loosely because you can run all of these on your personal computer in order to learn, to practice and—in some cases—to use for actual production data work. MySQL Community Edition The same database we used for class, but you can install it locally on your computer. The interface will definitely be different but the implementation of the SQL language will be the same. You’ll need the MySQL Community Server but consider using the MySQL Shell (especially if you will be working with Python and SQL) or the MySQL Workbench to write the actual queries. • Microsoft SQL Server Express “SQL Server” usually refers to Microsoft SQL Server. They make a free edition available called SQL Server Express that will allow you to create and work with databases up to 10 GB. That should be more than enough for practice and even some business use. It’s available for Windows or Linux, but not MacOS. • Microsoft Access You may not realize it but if you installed Microsoft Office on your Windows computer, you probably already have a database installed called Microsoft Access. It’s part of the business editions of Microsoft Office for Windows. The Mac editions don’t include Access. Although it usually helps you build your queries visually, it’s using the same SQL commands and structures we explored in this class. You can even write or edit SQL queries manually if you prefer. Sometimes that’s the only way to get what you need from the data. Oracle Express Edition Oracle is also one of the most recognized names in the DB world and they too make an “Express” edition available for free. It’s also available for Windows or Linux, but not MacOS. • Cloud Database Servers These database servers exist in the cloud so you can connect to them from virtually anywhere you have internet access. Amazon Web Services (AWS) While we used a tiny portion of Amazon Web Service in Cloud9, there is so much more available. Their RDS Service allows you to create and experiment with MySQL, Oracle, PostgreSQL, SQL Server and other databases systems. You can get more details and sign up for a free AWS account for 12 months at: • Microsoft Azure You can use your Sacred Heart email account to sign up for a free Azure account. You can use many Azure services (including database services) free for one year. Some services are always free. • SQL Reference SQL Queries for Mere Mortals Start with the text for this class. I chose it because it’s well-written, easy to read and focuses on the real-world database requests you’re likely to face in your careers. Read some of the chapters we didn’t cover. Go deeper into some of those we did; there’s a lot more in there than we had time to address in this class. And don’t forget the database samples and diagrams. They’re on the downloads tab at: • MySQL Documentation MySQL has a comprehensive set of documentation both for the version of MySQL that we used (5.6), the current version (8.x) and everything in between. Check it out at: • SQL Quick Reference This website has a great quick reference but also pretty in-depth reference for the SQL standard as opposed to just the MySQL version that we used in class. It’s easy to navigate, searchable and very friendly. And it’s useful for other languages too, such as Python. • Instructor Last and probably least…me. I’ve enjoyed coaching you through this process of discovering SQL and I’m still there for you later. Feel free to link to me and contact me if I can ever help: Email: [email protected] LinkedIn: Gab: Instagram: Parler:

Calculate Price

Price (USD)