Deck 3: The Relational Model: SQL

Question 1

In Access SQL, which operator do you use in the WHERE clause when you want to include a wildcard in the criterion?

A) LIKE
B) AS
C) BETWEEN
D) UNION

Answer 1

A

Question 2

SQL uses an on-screen form to create tables, update tables, and retrieve data from tables.

Answer 2

False

Question 3

Which of the following operators allows you to specify a range and is often used with date criteria?

A) BETWEEN
B) NOT
C) LIKE
D) IN

Answer 3

A

Question 4

When do you use the & operator in an expression in Access Query Design View?

A) to match a specific list of criteria
B) to reverse the result of the original condition.
C) to combine values in character fields
D) to sum values in numeric fields

Question 5

Which of the following SQL statements selects ProductDescription and Price fields for every record in the Products table with a Price value greater than $50.00?

A) SELECT ProductDescription, Price FROM Products WHERE Price > 50;
B) SELECT ProductDescription, Price > 50 FROM Products;
C) SELECT ProductDescription, Price FROM Products WITH Price > 50;
D) SELECT ProductDescription, Price FROM Products WHERE Price < 50;

Question 6

When a SQL statement consists of two queries and you place one query inside another, the inner query becomes a(n) _____.

A) aggregate query
B) Boolean query
C) compound query
D) subquery

Question 7

Which SQL commands do you use to update the values of an existing field?

A) UPDATE and SET
B) LIKE and IN
C) FROM and WHERE
D) INSERT and INTO

Question 8

When you connect query conditions using the AND operator, only one of the conditions must be true for a particular record to be selected.

Question 9

In an Access query, a "group" of records _____.

A) is rarely sorted
B) always contains numeric fields
C) has the same value in a particular field
D) is a good candidate for a subquery

Question 10

In a query that uses an aggregate function, which SQL clause do you use to include criteria that limits the number of records selected for the query?

A) RESTRICTING
B) GROUPING
C) JOINING
D) HAVING

Question 11

Which of the following is currently considered the most sought-after language for programmers?

A) Visual Basic
B) SQL
C) COBOL
D) CSS

Question 12

In Access SQL, you use the asterisk (*) as a wildcard character to represent any number of characters.

Question 13

When you use a name containing a space in Access SQL, what must you do to specify the table or column name?

A) Enclose it in "quotation marks".
B) Enclose it in [square brackets].
C) Enclose it in *asterisks*.
D) Enclose it in (parentheses).

Question 14

Fields appear in alphabetical order based on fieldname in the query results of a SELECT clause.

Question 15

In what type of SQL operation must two tables have the same field structure?

A) union
B) join
C) concatenation
D) extract

Question 16

While not always required, how should you end an SQL command?

A) with a comma (,)
B) with a period (.)
C) with a colon (:)
D) with a semicolon (;)

Question 17

Which part of a SQL query specifies the table names that contain the data you want to display in the query results?

A) SELECT clause
B) WHERE clause
C) WITHIN clause
D) FROM clause

Question 18

What SQL command can you use to join two tables?

A) CONTAINS
B) WHERE
C) EXTRACT
D) MERGE

Question 19

What would you most likely use in SQL to perform calculations on groups of records?

A) Boolean operators
B) aggregate functions
C) data definition language
D) lookup functions

Question 20

A good name for an Access table might be tbl!Student.

Question 21

Based on the table description and SQL shown, which query increases the FeeAmount by 2 percent? Fees ( FeeID, FeeType, FeeAmount )

A) UPDATE Fees SET FeeAmount = FeeAmount * 1.2;
B) UPDATE Fees SET FeeAmount = FeeAmount * 2%;
C) UPDATE Fees SET FeeAmount = FeeAmount * 1.02;
D) SELECT Fees WHERE FeeAmount = FeeAmount * 1.02;

Question 22

Based on the table description and SQL shown, which query lists the first name and last name of all students, separated by a space? ​
Students (StudentID, FirstName, LastName, Street, City, State, PostalCode)

A) SELECT FirstName & LastName AS FullName FROM Students;
B) SELECT FirstName & ' ' & LastName AS FullName FROM Students;;
C) SELECT FirstName ' ' LastName AS FullName FROM Students;
D) SELECT FirstName, LastName AS FullName FROM Students;

Question 23

When you place one query inside another, the inner query is called a(n) ___________________ and is evaluated first.

A) joined query
B) aggregate query
C) subquery
D) compound query

Question 24

Based on the SQL shown, which query lists the name and balance of all customers with balances greater than or equal to $2,000 and less than or equal to $5,000? Customers ( CustomerNum, CustomerName, Street, City, State, PostalCode, Balance, CreditLimit, RepNum )

A) SELECT CustomerName, Balance FROM Customers WHERE Balance BETWEEN 2000 AND 5000;
B) SELECT CustomerName, Balance FROM Customers WHERE Balance > 2000;
C) SELECT Balance FROM Customers WHERE Balance BETWEEN 2000 AND 5000;
D) SELECT CustomerName, Balance FROM Customers WHERE Balance BETWEEN 2000 OR 5000;

Question 25

Which of the following SQL commands sorts records by ProductName within ProductType?

A) SORT BY ProductType, ProductName
B) LIST ProductName IN ProductType
C) ORDER BY ProductName IN ProductType
D) ORDER BY ProductType, ProductName

Question 26

What SQL command do you use to add new data to a table?

A) INSERT
B) APPEND
C) ADDTO
D) SELECT

Question 27

What SQL keyword do you use in a query that saves the results of the query as a table?

A) UPDATE
B) INSERT
C) INTO
D) DELETE

Question 28

Which query will return the number of rows from the Customers table where the State field is 'AZ'?

A) SELECT FROM Customers WHERE State='AZ' COUNT(*);
B) SELECT COUNT(*) FROM Customers WHERE State='AZ';
C) SELECT FROM Customers COUNT(*) WHERE State='AZ';
D) SELECT COUNT(*) WHERE State='AZ' FROM Customers;

Question 29

Which statement creates a table named PostalCodes with a five-character PostalCode text field and a two-character Abbrev text field?

A) CREATE TABLE PostalCodes (PostalCode TEXT, Abbrev TEXT);
B) CREATE TABLE PostalCodes (PostalCode (TEXT 5), Abbrev (TEXT 2));
C) CREATE TABLE PostalCodes (PostalCode TEXT (5), Abbrev TEXT (2));
D) CREATE TABLE (PostalCode TEXT (5), Abbrev TEXT (2));

Question 30

Based on the table description and SQL shown, which query finds the description and price of the items that have a Price field value greater than Wireless charger, ItemNum W208? Items (ItemNum, Description, OnHand, Category, Storehouse, Price )

A) SELECT Description, Price FROM Items WHERE Price >
(SELECT Price FROM Items WHERE ItemNum = 'W208');
B) SELECT Description, Price FROM Items WHERE Price > ItemNum = 'W208');
C) SELECT Price FROM Items WHERE ItemNum > 'W208'
(SELECT Description, Price FROM Items);
D) SELECT Description, Price FROM Items WHERE Price >
(SELECT ItemNum = 'W208' FROM Items);

Question 31

The Clients table contains 12 records with three different values in the PostalCode field. If you create a query that groups the records based on the PostalCode field, how many records do the results contain?

A) 12
B) 36
C) 3
D) 4

Question 32

Based on the SQL shown, for each sales rep, which query lists the rep number, the number of customers assigned to the rep, and the average balance of the rep's customers? Group and order the records by rep number. Customers ( CustomerNum, CustomerName, Street, City, State, PostalCode, Balance, CreditLimit, RepNum )

A) SELECT RepNum, AVG(Balance) FROM Customers GROUP BY RepNum ORDER BY RepNum;
B) SELECT RepNum, COUNT(RepNum), AVG(Balance) FROM Customers GROUP BY RepNum ORDER BY RepNum;
C) SELECT RepNum, COUNT(*), AVG(Balance) FROM Customers GROUP BY RepNum ORDER BY RepNum;
D) SELECT RepNum, COUNT(*) FROM Customers GROUP BY RepNum ORDER BY RepNum;

Question 33

Based on the SQL shown, which query lists the last names and bonuses of employees who have a Bonus field value greater than Juliana Suarez, EmployeeID 14? Employees ( EmployeeID, LastName, FirstName, HireDate, JobTitle, Bonus )

A) SELECT LastName, Bonus FROM Employees WHERE Bonus >
(SELECT Bonus FROM Employees WHERE EmployeeID = '14');
B) SELECT LastName, Bonus FROM Employees HAVING Bonus > EmployeeID = '14';
C) SELECT LastName, Bonus FROM Employees WHERE Bonus > UNION
(SELECT LastName, Bonus FROM Employees WHERE EmployeeID = '14');
D) SELECT LastName, Bonus FROM Employees WHERE Bonus > UPDATE
SET EmployeeID = '14';

Question 34

Based on the SQL shown, which query finds how many items are in the TOY category? Items (ItemNum, Description, OnHand, Category, Storehouse, Price )

A) SELECT SUM(*) FROM Items WHERE Category='TOY' ;
B) SELECT COUNT(*) FROM Items WHERE Category='TOY' ;
C) SELECT COUNT FROM Items WHERE Category='TOY' ;
D) SELECT COUNT* FROM Items WHERE Category='TOY' ;

Question 35

Based on the table description and SQL shown, which query lists the states in the Students table and displays the number of students from each state? Students (StudentID, FirstName, LastName, Street, City, State, PostalCode)

A) SELECT State, COUNT(*) AS CountOfState FROM Students ORDER BY State;
B) SELECT State, COUNT(*) AS CountOfState FROM Students HAVING State;
C) SELECT State, COUNT(*) AS CountOfState FROM Students;
D) SELECT State, COUNT(*) AS CountOfState FROM Students GROUP BY State;

Question 36

Which function calculates the number of records in a table?

A) COUNT
B) SUM
C) MAX
D) MIN

Question 37

In Access SQL, which operator could you use to list all fields from a Customers table where the City field is Madison, Middleton, or Belleville?

A) LIKE
B) IN
C) BETWEEN
D) AND

Question 38

What SQL command do you use to make changes to existing data in a table?

A) MODIFY
B) CHANGE
C) SELECT
D) UPDATE

Question 39

Based on the SQL shown, which query lists the name and available credit for all customers with credit limits that exceed their balances? Customers ( CustomerNum, CustomerName, Street, City, State, PostalCode, Balance, CreditLimit, RepNum )

A) SELECT CustomerName AS AvailableCredit FROM Customers WHERE CreditLimit > Balance;
B) SELECT CustomerName, CreditLimit AS AvailableCredit FROM Customers WHERE CreditLimit > Balance;
C) SELECT CustomerName, Balance AS AvailableCredit FROM Customers WHERE CreditLimit > Balance ;
D) SELECT CustomerName, CreditLimit - Balance AS AvailableCredit FROM Customers WHERE CreditLimit > Balance;

Question 40

Based on the SQL shown, which query selects the LastName, JobTitle, and increased bonus for all employees with a job title of SalesRep? The increased bonus is calculated as $100 more than the current bonus. Employees ( EmployeeID, LastName, FirstName, HireDate, JobTitle, Bonus )

A) SELECT LastName, JobTitle, Bonus FROM Employees WHERE JobTitle = 'SalesRep' AND IncreasedBonus = Bonus + 100;
B) SELECT LastName, JobTitle, Bonus AS IncreasedBonus + 100 FROM Employees WHERE JobTitle = 'SalesRep';
C) SELECT * FROM Employees WHERE IncreasedBonus = Bonus + 100;
D) SELECT LastName, JobTitle, Bonus + 100 AS IncreasedBonus FROM Employees WHERE JobTitle = 'SalesRep';

Question 41

Explain the requirements of two sets of records involved in a union.

Question 42

List five Access data types and their corresponding SQL data types.

Question 43

When you create a query using SQL View to display fields and records, Access automatically places the _______________ keyword in the query window.

A) SELECT
B) UPDATE
C) CREATE
D) FIND

Question 44

Which SQL keyword can you use to join two tables that have a one-to-many relationship?

A) LINK
B) RELATE
C) UNION
D) WHERE

Question 45

How are compound SQL criteria conditions formed?

Question 46

How can you improve your SQL skills?

A) Practice using online SQL tutorials.
B) Create Access queries exclusively in Query Design View.
C) Take a course in JavaScript.
D) Use a search engine to conduct searches for general information.

Question 47

Why is SQL a highly sought-after language for programmers?

A) SQL is difficult to learn because it does not use English-like commands.
B) SQL is a universal language for selecting and maintaining data in a relational database.
C) SQL provides a foundation for learning about artificial intelligence.
D) SQL is a popular language for manipulating graphical objects.

Question 48

What are five restrictions placed on table and field names in Access?

Question 49

Which command joins the related Customers and Orders tables based on the common CustomerID field?

A) WHERE CustomerID.Customers = CustomerID.Orders
B) JOIN Customers.CustomerID = Orders.CustomerID
C) WHERE Customers.CustomerID = Orders.CustomerID
D) SELECT Customers.CustomerID AND Orders.CustomerID

Question 50

Explain how to use three SQL clauses to select fields and join tables using the WHERE keyword in SQL.