INFO2410- Midterm 2

JOIN

In SQL, tables are connected with a __________ statement

when the same names are used in both tables

In a join, column names need to be qualified only

IS NULL

The best test to use as a condition when writing a query for missing data

"SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY"

The six clauses of the SELECT statement must be coded in the following order

* or wildcard

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

Absence of a value

What is the definition of the term NULL?

Char

Which of the following is a fixed character type?

It always writes the same number of characters to disk It is less efficient in terms of disk storage

Which of the following is true of a fixed character data type?

Entity Relationship Diagram (ERD)

A diagram that depicts an entity relationship model's entities, attributes, and relations; shows the relationships between tables

Rows in both tables that doesn't satisfy the join condition

A full outer join includes rows that satisfy the join condition, plus

rows in both tables that don't satisfy the join condition

A full outer join includes rows that satisfy the join condition, plus

2NF

A table is in ____ if every nonkey column depends on the entire key, not just part of it.

1NF, 1NF

A table that has all key attributes defined, has no repeating groups, and all its attributes are dependent on the primary key, is said to be in ____. A table in 2NF also must be in ____

SELECT Statements

A union combines the rows from two or more

ORDER BY column_name (ASC or DESC)

For some reports it will be useful to see the results in alphabetical order. Which command will produce an alphabetical list?

AND

If you are writing a query which has two conditions, both of which must be true, you would need to use which type of Boolean operator to connect the two conditions

LEFT

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

FULL OUTER

If you want to join all of the rows in two tables whether or not they are matched, you use a/an _______________ join.

rows in the second table

In a cross join, all of the rows in the first table are joined with all of the

FOREIGN

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

Equal

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

%

In standard SQL, which symbol is used to describe any character or characters in pattern matches?

INNER JOIN

SELECT VendorName AS Vendor, InvoiceDate AS Date FROM Vendors AS V JOIN Invoices AS I ON V.VendorID = I.VendorID (Refer to code example 4-1.) This type of join is called a/an __________________ join.

none of the above

SELECT VendorName, InvoiceNumber FROM Invoices LEFT JOIN Vendors ON Invoices.VendorID = Vendors.VendorID (Refer to code example 4-2.) If the LEFT keyword is replaced with the FULL keyword, the total number of rows that are returned must equal

the number of rows in the Vendors table

SELECT VendorName, InvoiceNumber FROM Invoices LEFT JOIN Vendors ON Invoices.VendorID = Vendors.VendorID (Refer to code example 4-2.) If the LEFT keyword is replaced with the RIGHT keyword, the total number of rows that are returned must equal

the number of rows in the Invoices table

SELECT VendorName, InvoiceNumber FROM Invoices LEFT JOIN Vendors ON Invoices.VendorID = Vendors.VendorID (Refer to code example 4-2.) The total number of rows returned by this query must equal

LIKE

Text comparisons are usually made with the ______________ operator for pattern matching

WHERE state = 'UT' OR state = 'NV' OR state = 'AZ'

The SQL statement where state in ('UT','NV', 'AZ') could also be written as...

FROM

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

Not, And, Or

The order of precedence for the logical operators in a WHERE clause is

2NF, Key

To be in 3NF a table must already be in _____, and every nonkey column must depend on nothing but the _____

+ or Concatenation

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

DESC

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

The difference between UNION and JOIN

UNION combines the results of two SQL queries when there is the same number of columns and data types; JOIN returns rows when there is at least one column match

Variable Length String

Varchar and NVarchar represent what kinds of character types?

CREATE ALTER DROP

What are the clauses of DDL (Data Definition Langage)?

FROM TableName FROM Table1 JOIN Table2 ON (Inner Join) FROM Table1 LEFT JOIN Table2 ON (Outer joins L,R, Full) FROM (Cross) FROM (Self)

What are the clauses of a FROM statement?

SELECT ColumnName SELECT ColumnName AS c SELECT Column1 - Column2 (math) SELECT ColumnName + ColumnName2 (Concat) SELECT DISTINCT (Unique value) SELECT TOP __ (Top row of #)

What are the clauses of a SELECT statement?

WHERE ColumnName (= >< <> NOT AND OR) WHERE ColumnName BETWEEN x AND y WHERE ColumnName IN [List] WHERE ColumnName LIKE '%' WHERE ColumnName IS NULL

What are the clauses of a WHERE statement

ORDER BY ColumnName ASC/DESC

What is the clause for a ORDER BY statement

DDL

What is the portion of SQL used to create objects in a database?

All the Customers in Provo and the all the Customers with the last name starting with 'S'

What will be returned by this query: WHERE CustomerCity = 'Provo' OR CustomerLastName LIKE 'S%'

All inclusive of the between statement of numbers

What would be returned by the SQL clause BETWEEN 30 and 36?

Money

What would be the best datatype for an attribute named 'ProductPrice'?

WHERE CustomerPhone IS NULL

What would be the correct syntax for a WHERE clause to return all the NULL values in the CustomerPhone column?

PaymentDate IS NOT NULL AND InvoiceTotal >= 500

When coded in a WHERE clause, which search condition will return invoices when PaymentDate is not null and InvoiceTotal is greater than or equal to $500?

TOP 10 InvoiceNumber

When coded within a SELECT clause, which TOP clause will return a result set consisting of the ten largest InvoiceNumbers?

FullTable.Column_name

When identifying columns in different tables when the same column name is used in more than one table you must use

Linked or Joined

When splitting a table, conforming to first normal form, the original key must be brought over into every new table created so the tables can be

Deletion an

When tables lose information because data is not stored in a proper format

SELECT, FROM, WHERE, ORDER BY

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

The result sets must be derived from different tables.

When you code a union that combines two result sets, which of the following is not true?

includes only rows that occur in both result sets

When you code a union with the INTERSECT keyword to combine two result sets, the union

column name, alias, expression, or column number

When you code an ORDER BY clause, you can specify a

SELECT DISTINCT

Which keyword would ensure that return visitors are only counted once when running a query against a reservation system?

A document that describes all database objects and their structures, including data types

Which of the following best defines a data dictionary?

A column specification that determines what kind of data can be stored in a column

Which of the following best defines the term data type?

You cannot remove a record from the primary key table if it has related records in the foreign key table

Which of the following best describes the effect of enforcing referential integrity on deletes?

You cannot change a primary key that has related records in a foreign key table

Which of the following best describes the effect of enforcing referential integrity on updates?

It prevents orphan data It protects against bad or meaningless data

Which of the following describes some of the advantages of enforcing referential integrity?

InvoiceTotal - CreditTotal - PaymentTotal / 10

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?

NVarchar

Which of the following is a unicode character type?

Compatibility with your software and hardware Hardware and software requirements Familiarity and expertise of staff

Which of the following is important to consider when choosing a database management system?

It should be complete enough to test business rules It should contain some exceptions or even errors to test how the database deals with those

Which of the following is true of sample data?

An expanded character set that includes nonLatin characters

Which of the following provides the best definition for unicode?

CREATE TABLE

Which of the following shows how you would begin to make a table in SQL?

right outer joins can be converted to left outer joins by changing the order of the tables in the statement

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

Enforcing Re

____ prevents orphan data and it protects against bad or meaningless data

Referential

exists when a value for a foreign key can be entered only if the corresponding value already exists in the originating table