DSS Test 2

SELECT

Which of the following types of queries does not change data in Access?

*

Which symbol is a wildcard representing any number of characters?

| |

Whichof the following is not a valid comparison operator?

Relational Algebra

_____ forms the foundational knowledge for SQL, Structured Query Language, which is the most popular way developers select, edit, and add data to a relational database.

SELECT Title, COUNT(*) AS CountOfTitleFROM EmployeesGROUP BY Title;

Which SQL statement lists the job titles at JCC and displays the number of employees that have each title?

DECIMAL

Which of the following is not an Access SQL data type

An aggregate function sums, averages, or counts, for example, the records in a group.

Which of the following describes an Access aggregate function?

MonthlyRate: [AnnualRate] / 12

Which of the following is an example of a computed field?

Each intersection of a row and column in a table may contain more than one value.

Which of the following is not a characteristic of a healthy relation?

SUBTOTAL

Which of the following is not an aggregate function used to calculate information on groups of records?

A calculated field starts by determining how to group the records

Which of the following is not true about computed or calculated field?

SQL

Which of the following is the most sought-after language for programmers, according to codingsight.com?

Criteria joined by OR may select more records than if joined by AND

Which of the following is true?

A record is all of the attribute values for one item in an entity.

Which of the following sentences explains how entities, attributes, and records work together?

UPDATE Patient SET Balance = Balance*1.02 WHERE Balance>2000;

Write the SQL code for the following: Increase the balance by 2% of any patient whose balance is greater than $2,000.

SELECT City, LastName, Balance FROM Patient WHERE City="Waterville" AND Balance>1000;

Write the SQL code for the following: List the city, last name, and balance of patients who live in Waterville and have a balance greater than $1,000.

SELECT LastName, City, Balance FROM Patient WHERE City="Waterville" OR Balance>2000;

Write the SQL code for the following: List the city, last name, and balance of patients who live in Waterville or have a balance greater than $2,000.

SELECT ClassName, Room, Location, Day, Time FROM Classes;

Write the SQL code for the following: List the class name, room, location, day, and time for all classes.

SELECT Therapist.FirstName, Therapist.LastName FROM Therapist, Session WHERE Therapist.TherapistID = Session.TherapistID AND SessionDate = #10/16/2021#;

Write the SQL code for the following: List the full name of the therapist scheduled to work on 10/16/2021.

SELECT LastName, Balance FROM Patient WHERE Balance>1000;

Write the SQL code for the following: List the last names of patients whose balance is greater than $1,000.

SELECT SessionDate, SessionNum FROM Session WHERE SessionDate BETWEEN #10/18/2021# AND #10/20/2021#;

Write the SQL code for the following: List the session dates and numbers for those sessions scheduled between 10/18/2021 and 10/20/2021.

SELECT * FROM Reservations ORDER BY ClassDate, ClassID;

Write the SQL code for the following: Order the reservations by class date and then by class ID. Display all fields.

UPDATE Reservations SET ClassPrice = ClassPrice*1.01;

Write the SQL code for the following: Update all class prices to reflect a 1% increase.

Projects.ClientID = Clients.ClientID

Complete the following statements to list the project ID, project start date, client ID, and client name for each project. JOIN Projects Clients WHERE _____ GIVING Temp1PROJECT Temp1 OVER (ProjectID, ProjectStartDate, ClientID, ClientName) GIVING Answer

SELECT InstructorFirstName & " " & InstructorLastName AS InstructorFullName

How would you write the SELECT statement in a longer query if you wanted to concatenate the first and last name of the instructors and display that as InstructorFullName?

NOT

Preceding a condition with the __________Operator reverses the result of the original condition

primary key field

The _____ contains values that uniquely identify each record in a table and serves as the linking field in the table on the "one" (parent) side of a one-to-many relationship.

SELECT InstructorLastName, LengthofTime/60*20 AS AmountEarned

What SELECT statement would you use in a longer query to calculate the amount of money earned by each instructor based on the length of time for the class and $20 per hour. Display the instructor's last name and the amount earned.

TEXT to City and TEXT to Zip

What data types would you assign to the City and Zip fields when creating the Zips table?

OR criteria, AND criteria

With _____, only one criterion must evaluate true in order for a record to be selected and with _____, all criteria must be evaluate true in order for a record to be selected.

SELECT * FROM Patient ORDER BY City;

Write the SQL code for the following: List all the information in the patient's table sorted by city.

IN

Which SQL operator provides a concise way of creating a condition to match a specific list of criteria?

SELECT Salary * 0.1 AS BonusFROM Employees;

Which SQL statement calculates the Bonus field in the Employees table as 10 percent of Salary?

SELECT COUNT (TshirtID) AS NumberOfTshirts, SUM (TshirtPrice) AS TotalPriceOfTshirts FROM Tshirts;.

Write an SQL query that would ask the database to count the number of different types of t-shirts available and total their price. Your output should show only the field names: NumberOfTshirts TotalPriceOfTshirts.

To answer the question correctly, you would need only the Classes table. If you used all the tables in the query and some classes had not been reserved, you might be missing some of that output.

Suppose you want to list information on all the classes that Pitt Fitness offers, including the day of the week, time, location, and length of class. To do this, you could create a query. What table(s) should you add to the query? If you use only the Classes table instead of all the tables together, does it make a difference to the output? What if someone had never reserved a specific class?

TaskID = 'CODE05'

Complete the following statement to list all information from the TaskMasterList table for task ID CODE05. SELECT TaskMasterListWHERE _____ GIVING Answer

PROJECT

Complete the following statement to list the employee ID, first name, and last name of all employees. _____EMPLOYEES OVER (EmployeeID, LastName, FirstName) GIVING Answer

UNION

Complete the following statements to list the project ID and project start date of all projects that were created for client ID 5 or 6. JOIN Projects Clients WHERE Projects.ClientID = Clients.ClientID GIVING Temp1PROJECT Temp1 OVER (ProjectID, ProjectStartDate, ClientID) GIVING Temp2SELECT Temp2 WHERE ClientID = 5 GIVING Temp3PROJECT Temp3 OVER (ProjectID, ProjectStartDate) GIVING Temp4 SELECT Temp2 WHERE ClientID = 6 GIVING Temp5PROJECT Temp5 OVER (ProjectID, ProjectStartDate) GIVING Temp6_____ Temp5 WITH Temp6 GIVING Answer

WHERE ClientID = 5

Complete the following statements to list the project ID and project start date of all projects that were placed by client ID 5. JOIN Projects Clients WHERE Projects.ClientID = Clients.ClientID GIVING Temp1PROJECT Temp1 OVER (ProjectID, ProjectStartDate, ClientID) GIVING Temp2SELECT Temp2 _____ GIVING Temp3PROJECT Temp3 OVER (ProjectID, ProjectStartDate) GIVING Temp4

SUBTRACT

Complete the following statements to list the project ID and project start date of all projects with a project start date of 3/1/2020 but not for client ID 7. SELECT Projects WHERE ProjectStartDate ='3/1/2020' GIVING Temp1PROJECT Temp1 OVER (ProjectID, ProjectStartDate) GIVING Temp2SELECT Temp2 WHERE ClientID = 7 GIVING Temp3PROJECT Temp3 OVER (ProjectID, ProjectStartDate) GIVING Temp4_____ Temp4 FROM Temp2 GIVING Answer

Use the Sort row and specify the sort fields in a left-to-right order.

How do you sort data in Access Query Design View?

CREATE TABLE Tshirts (TshirtID TEXT(5), TshirtDescription TEXT(25), TshirtPrice CURRENCY);

Pitt Fitness is selling a line of exercise clothing at their three locations. At the beginning of this venture, they decide to sell only three types of t-shirts with the Pitt Fitness logo: two women's sizes and one men's size. How would you use SQL to create a new table in the Pitt Fitness database to capture the line of t-shirts and their retail price? This new table would be used for advertising purposes only, so no quantity-on-hand fields are necessary.

UNION

The _____ operator allows you to select the records that match each condition separately, and then combine them into a single view.

First, create a query that counts the number of classes offered at each location. Next, create a query that counts the number of reservations at each location. Finally, to refine the answer, you could create a query that counts the number of reservations for each specific class at the low-enrollment location and compare that to the other locations.

The owner of Pitt Fitness is considering whether to consolidate his clubs and offer classes in only one location to ensure profitability. To explore his question, what query or queries would you create to answer this business strategy question?

The ClientID field is the primary key field in the Clients (parent) table and the foreign key field in the Projects (child) table that creates the one-to-many relationships between the tables.

Use the terms primary key field, foreign key field, one-to-many relationship, parent table and child table to describe the following WHERE clause:WHERE Clients.ClientID = Projects.ClientID

SELECT * FROM TaskMasterList WHERE CategoryID = 'Database';

What SQL statement lists all the fields from the records in the TaskMasterList table with a CategoryID of Database?

SELECT EmployeeID, LastName, FirstName FROM Employees;

What SQL statement lists only the EmployeeID, LastName, and FirstName fields of all employees in the Employees table?

You may not create orphan records by entering a phony foreign key field value in the "many" table.

What does it mean to enforce referential integrity on a one-to-many relationship?

inner query

What is another term for a subquery?

It is the field in the table on the "many" (child) side of a one-to-many relationship.

What is the purpose of the foreign key field?

Phone numbers typically contain only numbers but should still be created as TEXT (CHAR or VARCHAR) fields, given they do not represent numeric information and will never be used in calculations.

What would be a good Access SQL data type for a Phone Number field?

IN

Which Condition lets you determine whether a value is in some specific collection of values

ORDER BY LastName, FirstName

Which SQL clause sorts the selected records in ascending order by FirstName within LastName?

WHERE Customers.CustID = Orders.CustID

Which SQL clause would join a Customers table with an Orders table assuming that they are related in a one-to-many relationship on a field called CustID in both tables?

WHERE

Which SQL command would be used to join the TaskMasterList and ProjectLineItems tables?

SELECT FirstName &" "& LastName AS FullName FROM Therapist WHERE NOT ZipCode=72511;

Write an SQL query to display every therapist's first name and last name as their full name, but only for those instructors not living in zip code 72511.

JOIN

Complete the following statements to list the project ID, project start date, client ID, and client name for each project created for the employee with the last name of Winter. _____ Projects Clients WHERE Projects.ClientID = Clients.ClientID GIVING Temp1JOIN Temp1 Clients WHERE Temp1.EmployeeID = Employees.EmployeeID GIVING Temp2SELECT Temp2 WHERE Employees.LastName = 'Winter' GIVING Temp3PROJECT Temp3 OVER (ProjectID, ProjectStartDate, ClientID, ClientName) GIVING Answer

Modify the update query to divide by 1.05 and run the query.

If you made a mistake on the update query in question 17 to add a 5 percent surcharge to accounts with balances greater than $1,000, how would you undo the change?

The first question needs the Patient, Session, and Therapist tables. The second question would also need the Therapies table.

If you want to find out which therapist is meeting a particular patient on a specific day, what tables do you need for your query? If you also want to know the description of the therapy, does that change your query design? If so, how?

the same row, different rows

In the query design grid in Access Query Design View, you place AND criteria on _____, and you place OR criteria on _____.

WHERE TaskMasterList.TaskID = ProjectLineItems.TaskIDAND Projects.ProjectID = ProjectLineItems.ProjectID

What would the WHERE clause look like in a SELECT query that selects fields from the TaskMasterList, ProjectLineItems, and Projects tables?

True

When you connect simple conditions using the AND operator, all the simple conditions must be true for the compound condition to be true? True or False?

ORDER BY CategoryID, TaskID

Which SQL command would be used to sort all TaskMasterList records in ascending order based on the value of their TaskID within CategoryID?

FROM

Which SQL keyword identifies the tables that are involved in a SELECT query?

SELECT

Which SQL keyword starts an SQL statement to select fields and records from one or more related tables?

OR

Which SQL keyword would be used to find all TaskMasterList records with a CategoryID field value of Testing or Support?

WHERE NOT Per = 'Hour'

Which SQL keyword would be used to select all records except those with a Per field value of Hour?

INSERT INTO

Which SQL keyword(s) start an SQL statement to append a new record to a table?

BETWEEN

Which SQL operator can be rewritten with >= AND <= operators?

SELECT LastName, SalaryFROM EmployeesWHERE Salary < (SELECT Salary FROM Employees WHERE EmployeeID = '72');

Which SQL statement lists the last names and salaries of employees who have a Salary field value less than Amir Nasser, EmployeeID 72?

LIKE

Which condition uses wildcards to select rows?

SELECT * FROM Therapies WHERE Description LIKE "*bath*" OR Description LIKE "*hot*" OR Description LIKE "*electrical*";

Write an SQL query that displays the therapies and their unit of time for the therapies that include the word bath, hot, or electrical.

SELECT COUNT(ReservationID) AS CountOfReservationID FROM Reservations WHERE ClassDate=#1/3/2021#;

Write the SQL code for the following: Count the number of reservations for 1/3/2021 and display that number with a CountOfReservationID heading.

SELECT LastName, StreetAddress FROM Customers WHERE StreetAddress LIKE "*Negley*";

Write the SQL code for the following: Display the last name and street address of the customers who live on Negley.

SELECT Customers.LastName, Reservations.ClassDate FROM Customers, Reservations WHERE Customers.CustomerID = Reservations.CustomerID AND Reservations.ClassDate=#1/2/2021#;

Write the SQL code for the following: Display the last names of the customers who have registered for a class on 1/2/2021.

SELECT Session.LengthOfSession FROM Therapist, Session WHERE Therapist.TherapistID = Session.TherapistID AND Therapist.LastName='Shields';

Write the SQL code that answers the following question: How long are the therapy sessions for the therapist whose last name is Shields? List the length of each session.

SELECT COUNT(TherapyCode) AS CountOfTherapyCode FROM Therapies;

Write the SQL code that answers the following question: How many therapies are offered? Answer with one number only.

SELECT ClassName, Day, Location FROM Classes WHERE Day='Wednesday' AND Location='Downtown';

Write the SQL code that answers the following question: Which classes are scheduled for Wednesdays at the Downtown location? List the class name, the day, and the location.

SELECT InstructorLastName FROM Instructors WHERE InstructorZipCode = "15217";

Write the SQL code that answers the following question: Which instructors (showing last name only) live in zip code 15217?

SELECT Description FROM Therapies WHERE Description LIKE "*movement*";

Write the SQL code that answers the following question: Which therapies have the word "movement" in their description? List the therapy description only.