IST 272 Exam 1

A relational database can contain ___________.

one or more tables

The most common type of relationship between two tables is a/an ___________________ relationship.

one-to-many

To return all of the columns from the base table, you can code the ___________ operator in the SELECT clause.

*

To concatenate character strings, you use the _____________ operator in a string expression.

+

To retrieve rows in which an expression matches a string pattern called a mask, you can use the ______________ keyword followed by the mask.

LIKE

The SQL dialect that Microsoft SQL Server uses is called _______________.

T-SQL

To override the order of precedence in an arithmetic expression, you can use parenthesis.

True

In most cases, the join condition of an inner join compares the primary key of one table to the ____________________ key of another table.

foreign

If you want to retrieve all the rows from both tables involved in the join including all unmatched rows, you use a/an _______________ join.

full outer

Insert, Update, and Delete statements can be referred to as ______________ queries.

action

SQL statements that define the tables in a database are referred to as _______________ statements.

DDL

To sort the records that are retrieved by a SELECT statement in descending sequence by InvoiceTotal, you code _________ after ORDER BY InvoiceTotal.

DESC

To prevent duplicate rows from being returned by a SELECT statement, you can code the __________________ keyword in the SELECT clause.

DISTINCT

Which of the following is not a valid column alias name?

Total Sales

Which of the following expressions will not compute 10% of the balance due if balance due is the invoice total minus the credit total minus the payment total?

InvoiceTotal - CreditTotal - PaymentTotal / 10

When coded in a WHERE clause, which of the following search conditions will not return a result set that includes all invoices with an InvoiceTotal value of $1000 or less?

InvoiceTotal IN (0,1000)

The join shown here: SELECT VendorName AS Vendor, InvoiceDate AS Date FROM Vendors AS V JOIN Invoices AS I ON V.VendorID = I.VendorID is called a/an __________________ join.

inner

A/An __________________ uniquely identifies each row in a table

primary key

You don't ever need to code a right outer join because _____________.

right outer joins can be converted to left outer joins

In the join shown here: SELECT VendorName, InvoiceNumber FROM Invoices LEFT JOIN Vendors ON Invoices.VendorID = Vendors.VendorID The total number of rows returned by this query must equal_____________.

the number of rows in the Invoices table

When a column in a table is defined, it is given a data _________ that determines what kind of data it can store.

type

In a join, column names need to be qualified only _____________

when the same names are used in both tables.

In the join shown here: SELECT VendorName AS Vendor, InvoiceDate AS Date FROM Vendors AS V JOIN Invoices AS I ON V.VendorID = I.VendorID The name V is known as a/an ____________________________________.

correlation name (or table alias)

___________________ names can be used when you want to assign a temporary name to a table.

Correlation

The ________________ clause of the SELECT statement names the table that contains the data to be retrieved.

FROM

Because the American National Standards Institute publishes the specifications for a standard SQL language, each DBMS vendor's version of SQL follows exactly the same syntax rules.

False

In the space provided type in code for a join query that selects the ClubMember's FirstName and the AwardDescription. The table information for this problem is shown below: Table 1 is named ClubMembers and it has columns named: CID, LastName, FirstName, and Title. The CID is the primary key of this table. Table 2 is named Awards and it has columns named AwardNo, CID, AwardDescription, and DateAssigned. The PrimaryKey on this table is a composite of the AwardNo and the CID. The CID is a foreign key in addition to being part of the primary key.

SELECT FirstName, AwardDescription FROM ClubMembers JOIN Awards ON ClubMembers.CID = Awards.CID;

In the space provided type in code for a query that selects the ItemNo, Description, and Price columns from a table named Merchandise. Only select rows where the Price is greater than 5.25 and sort the rows by Description.

SELECT ItemNo, Description, Price FROM Merchandise WHERE Price > 5.25 ORDER BY Description;

In the space provided type in code for a query that selects the LastName, FirstName, and Zipcode columns from a table named Volunteers. Only select rows where the Zipcode equals 22345 and sort the rows by LastName. Do not use a LIKE.

SELECT LastName, FirstName, Zipcode FROM Volunteers WHERE Zipcode = 22345 ORDER BY LastName

In the space provided type in code for a query that selects the LastName and Salary columns from a table named Staff. Only select rows where the Lastname starts with 'Es' and sort the rows by LastName.

SELECT LastName, Salary FROM Staff WHERE LastName LIKE 'Es%' ORDER BY LastName

When you code a SELECT statement, you must code the four main clauses in the following order

SELECT, FROM, WHERE, ORDER BY

When you use the implicit syntax for coding joins, the join conditions are coded in the ____________ clause.

WHERE

In most cases, the join condition of an inner join uses the _______________ operator to compare two keys.

equal

The join shown here: SELECT VendorName AS Vendor, InvoiceDate AS Date FROM Vendors AS V JOIN Invoices AS I ON V.VendorID = I.VendorID is coded using the _____________________________ syntax.

explicit (SQL-92)

To relate one table to another, a/an __________________ in one table is used to point to the primary key in another table.

foreign key

If you want to join all of the rows in the first table of a SELECT statement with just the matched rows in a second table, you use a/an _______________ join.

left outer