CIT 352

In the ERD, cardinality is indicated using the ____ notation.

(min, max)

The hierarchical data model was developed in the ____.

1960s

A table that is in 2NF and contains no transitive dependencies is said to be in ____.

3NF

What syntax would be used to define a column?

<column name> { <data type> | <domain> }[ <default clause> ] [ <column constraints ] [ COLLATE <collation name> ]

The having clause is similar to the where clause in what way?

A null in the data can never satisfy a condition in either a having clause or a where clause. The only exception occurs with the is null condition

A join is an operation that matches rows in one table with rows in another so that columns from both tables may be placed side by side in the query results as if they all came from ___.

A single table

Which of the following is the correct syntax for altering and Access table?

ALTER TABLE ARTISTS ADD COLUMN ARTIST_DOB DATETIME;

When you issue the DELETE FROM tablename command without specifying a where condition ____.

All rows will be deleted

Using Oracle, what data type would be used to store binary data?

BLOB

Why can't a SELECT statement list both summarized data and detail data?

Because the output of a select statement must be like a table

In the following example, what does the keyword DISTINCT do? SELECT DISTINCT PERFORMER_NAME FROM PERFORMER_TYPE;

By using the DISTINCT keyword, the query results will include only one instance of each value

SQL character data format is/are ____.

CHAR and VARCHAR

To construct a new table, one would use the following syntax:

CREATE TABLE <table name>

Which of the following would be for creating a table?

CREATE TABLE MUSIC_TYPES ( TYPE_ID INT, TYPE_NAME VARCHAR(20) );

A list of each row in one table joined together with each row in the other table is called a ___.

Cartesian product table

One of the simplest types of joins to implement is the ___ join

Comma-separated

What does the following SQL do? SELECT CATEGORY, AVG(PRICE) AS AVG_PRICE FROM COMPACT_DISC_STOCK GROUP BY CATEGORY HAVING ON_HAND < 20;

Creates an error

The ____ serve(s) as the intermediary between the user and the database

DBMS

To delete a row from the PRODUCT table, use the ____ command.

DELETE

___ is a term that is used to describe the process of beginning with a high level of summarization and progressing to finer levels of detail.

Drilldown

An ___ is a type of join that equates the values in one or more columns in the first table to the values in one or more corresponding columns in the second table.

Equi-join

The following SQL demonstrates what type of join? SELECT * FROM CD_INVENTORY, PERFORMERS WHERE CD_INVENTORY.PERF_ID = PERFORMERS.PERF_ID;

Equi-join

The WHERE clause takes the values returned by the ____ clause (in a virtual table) and applies the search condition that is defined within the ____ clause.

FROM, WHERE

All tables have a primary key

False

In most databases, tables cannot be linked.

False

The name John would be a good primary key.

False

The primary key is like an adjective because it names the object of each row.

False

Usually, there are many more columns than rows in a given table.

False

For the most part, you'll probably find that you'll be using the HAVING clause in conjunction with the ___ clause.

GROUP BY

What type of join does the following SQL create: SELECT t.TITLE, ta.ARTIST_ID FROM CD_TITLES t INNER JOIN TITLES_ARTISTS ta ON t.TITLE_ID = ta.TITLE_ID WHERE t.TITLE LIKE ('%Blue%');

Inner

___ joins often drop some of the rows of the beginning tables if they do not have a matching row in the other table. If we want to keep these unmatched rows instead of dropping them, we need to use an ___ join.

Inner, Outer

What does the following SQL statement do? CREATE TABLE COMPACT_DISC_TYPES ( COMPACT_DISC_ID INT, CD_TITLE VARCHAR(60), TYPE_ID INT );

It creates a table named COMPACT_DISC_TYPES with three columns

What type of join is the following? SELECT i.TITLE, t.TYPE_NAME, i.STOCK FROM CD_INFO i LEFT OUTER JOIN CD_TYPE t ON i.TYPE_ID = t.TYPE_ID;

Left outer

What is the likely result of the following SQL? SELECT MAX(NUMBER_SOLD) AS MAX_SOLD FROM ARTIST_CDS;

MAX_SOLD 54

Oracle has direct support for the full outer join. Does Access?

No

Given the following SQL script, which result is most likely?SELECT *FROM s_deptWHERE NAME = 'Operations 1' ;

None of the above

How would you write an SQL query to list the name, address, city and state of any customers whose names begin with words like Athlete or Athletics

None of the above

If you are grouping by more than one column and you truly want an Other category, you will need to create it yourself as a separate step. Why?

None of the above

Review the following SELECT statement: SELECT CD_TITLE, COPYRIGHT, IN_STOCKFROM COMPACT_DISC_INVENTORYWHERE COPYRIGHT > 1989 AND COPYRIGHT < 2000; What is the source table?

None of the above

Suppose you want to retrieve the customers who have excellent credit ratings from the 22808 and 22809 zip code areas. How would you write the query?

None of the above

The SELECT clause includes what optional keywords?

None of the above

The usual way to produce a report with subtotals and grand totals is to have SQL ___.

None of the above

Unlike the MIN and MAX functions, which select the lowest and highest values from a column, the ___ function adds column values together.

None of the above

What type of join is the following? SELECT i.TITLE, t.TYPE_NAME, i.STOCK FROM CD_INFO i JOIN CD_TYPE t ON i.TYPE_ID = t. TYPE_ID;

None of the above

You can use the DISTINCT keyword to ensure what?

None of the above

The most likely data type for a surrogate key is ____.

Numeric

A ____ should be a derived attribute.

Person's age

A left outer join________.

Returns all matched rows and all unmatched rows from the left table - the table to the left of the JOIN keyword

Which of the following would return all columns from the performers table?

SELECT * FROM PERFORMERS;

Which of the following SQL scripts most likely generated this output: CD TITLE COPYRIGHT Past Light 1983

SELECT CD_TITLE, COPYRIGHT FROM CDS_ON_HAND WHERE CD_TITLE = 'Past Light';

Which of the following SQL scripts would likely generate an output where the copyright value falls within the range of 1971 through 1989?

SELECT CD_TITLE, COPYRIGHT FROM CDS_ON_HAND WHERE COPYRIGHT >= 1971AND COPYRIGHT <= 1989;

Which of the following SQL scripts most likely would be used to generate output where the inventory value is between 2 and 25?

SELECT CD_TITLE, INVENTORY FROM CDS_ON_HAND WHERE INVENTORY > 2AND INVENTORY < 25;

Which of the following SQL scripts likely generated and output where the retail price is between 14 and 16?

SELECT CD_TITLE, RETAIL_PRICE FROM CDS_ON_HAND WHERE RETAIL_PRICE BETWEEN 14 AND 16;

Which statement below shows out how many rows include a NUMBER_SOLD value greater than 20?

SELECT COUNT(*) AS TOTAL_ROWS FROM ARTIST_CDS WHERE NUMBER_SOLD > 20;

The correct SELECT statement syntax is...

SELECT [ DISTINCT | ALL ] { * | <select list> }FROM <table referenco [ { , <table referenco } ... ][ WHERE <search condition> ][ GROUP BY <grouping specification> ][ HAVING <search condition> ][ ORDER BY <order condition> ]

Which of the following pairs of SQL statements will give the same result?

SELECT id, name, phoneFROM s_customerWHERE id BETWEEN '303' AND '306 ';andSELECT id, name, phoneFROM s_customerWHERE id >= ' 303 ' AND id <= ' 306 ' ;

How would you Write a SQL query to list the name, address, city, and state of any customers who live on streets, not roads. List them in order alphabetically.

SELECT name, address, city, state FROM s_customer WHERE address LIKE '%St' ORDER BY name;

Which of the following is most likely to provide this result:NAME TITLE DEPTBell Sr. Accountant AccountingMartin Clerk Accounting

SELECT name, title, deptFROM employeeWHERE dept = 'Accounting'ORDER BY name ;

Which of the following SQL queries would be the best choice for listing the names, zip codes, and sales reps of all the customers in the S_CUSTOMER table who are not in the 22808 zip code area.

SELECT name, zip_code, sales_rep_id FROM s_customer WHERE zip_code <> '22808 ';

When a union is written within SQL code, the word union is placed between two ___.

Select statements

A union of two tables adds the rows of one table to the other table, and the two beginning tables are combined to form a ___.

Single table

The character data type...

Specifies the exact number of characters (which must be from a character set) that will be stored for each value

____ data are the result of formatting to facilitate storage, use and generation of information.

Structured

How will the following syntax be evaluated?WHERE IN_STOCK = 6 OR IN_STOCK = 27 AND LABEL_ID = 833 OR LABEL_ID = 829

The syntax will be evaluated as:WHERE IN_STOCK = 6 OR (IN_STOCK = 27 AND LABEL_ID = 833) OR LABEL_ID =829

All the cells in a column contain the same type of information.

True

The WHERE clause as a whole must evaluate to ___ in order for a row to be included in the query results.

True

____ combines all rows from two tables, excluding duplicate rows.

UNION

The SQL command that modifies an attribute 's values in one or more table 's rows ____.

UPDATE

The command you would use when making changes to a PRODUCT table is ____.

UPDATE PRODUCTSET P_INDATE = '18-JAN-2004'WHERE P_CODE = '13-Q2/P2';

The ___ contains the join condition.

WHERE clause

When is it appropriate to allow duplicate rows?

When you are the only user

Business rules are derived from ____

a detailed description of an organization's operations

A field is the same thing as a ___?

column

Sum and avg can apply only to ___ of numbers.

columns

A ____ attribute can be further subdivided to yield additional attributes.

composite

If the attribute (B) is functionally dependent on a composite key (A) but not on any subset of that ____ key, the attribute (B) is fully functionally dependent on (A).

composite

A(n) ____ is a restriction placed on the data.

constraint

In a(n) ____ diagram, the arrows above the attributes indicate all desirable dependencies.

dependency

A(n) ____ represents a particular type of object in the real world.

entity

A ____ is a character or group of characters that has a specific meaning.

field

A ____ is a collection of related records.

file

The left outer join adds back all the rows that are dropped from the ___ table.

first

The order by clause puts the rows in logical order. It ________ required in a left outer join

is not

What does the following SQL statement do:

it deletes the CD_TITLE column

In the relational model, ____ are important because they are used to ensure that each row in a table is uniquely identifiable

keys

If a field is not adequate to describe a record, then the primary key may use ___.

more than one column

A workgroup database is a(n)____ database.

multiuser

What does the following SQL do? SQL > SELECT ID, NAME, AGE, ADDRESS, SALARY FROM CUSTOMERS GROUP BY age HAVING COUNT(age) >= 2;

none of the above

What is the likely result of the following row addition? 3 + null = ___

null

All the rows with a null in the grouping column are placed within a single group called the ___.

null group

The datatype of a column determines the sort order that is applied to its data: Text columns are sorted in alphabetic order, number columns are sorted in ___ order, and date columns are sorted in date order.

numeric

A good example of data contained in a column would be ___.

one type of data about each row of the table

The OUTER keyword is ___.

optional

A common mistake made when trying to write complex conditions in the where clause is that not enough ___ are specified.

parentheses

An attribute that is part of a key is known as a(n) ____ attribute.

prime

Controlled ____ makes a relational database work.

redundancy

MySQL is an example of a(n) ____.

relational data model

Rows are dropped if they do not have a matching ___ in the other table

row

To find the number of different values in the manager_id column of the l_employees table one could use the following SQL:

select count(distinct manager_id) from I_employees;

Some attributes are classified as ____.

simple

The result of a column function is always a ___ value.

single

A ___ is a select statement embedded within another select statement.

subquery

For a column to be included in the HAVING clause, it must be a grouped column or it must be ___ in some way.

summarized

The relational model's creator, E. F. Codd, used the term relation as a synonym for ____.

table

The basic guidelines for creating a comma-separated join include:

the FROM clause must include all table names. The WHERE clause should define a join condition, avoiding a Cartesian product. The column references must be qualified when column names are shared among tables

When you use count distinct on a date column, you may not get the result you expect. Why is this?

the data in a date column may contain a time, which is often not shown

Granularity refers to ____.

the level of detail represented by the values stored in a table's row

What is the purpose of a primary key?

the primary key gives a name to each row of the table and prevents the table from having a two rows that are identical

Often, the best join technique is to combine the beginning tables ___ at a time.

two

In an ER diagram, primary keys are indicated by ____.

underlining