Database Exam 2
_____ is used to specify which logical operators are evaluated first. [] () {} None of the above
()
The __A__ statement displays rows from a table named in the __B__ clause. A: DISPLAY B: FROM A: PRINT B: TABLE A: SELECT B: FROM None of the above
A: SELECT B: FROM
An SQL __A__ is a complete command composed of one or more __B__ A: Sentence B: Words A: Statement B: Clause A: Sentence B: Clause A: Statement B: Word
A: Statement B: Clause
The __A__ clause is combined with the __B__ statement to filter the result set. A: FILTER B: DISPLAY A: FILTER B: SELECT A: WHERE B: DISPLAY A: WHERE B: SELECT
A: WHERE B: SELECT
The HAVING clause must appear __A__ the GROUP BY clause but __B__ the optional ORDER BY clause. A: after B: before A: before B: after A: before B: before A: after B: after
A: after B: before
GROUP BY clause must appear __A__ the ORDER BY clause and __B__ the WHERE clause. A: after B: before A: before B: after A: before B: before A: after B: after
A: before B: after
When two tables are combined by comparing columns from the first and second tables, usually a __A__ key of one table to the __B__ key of another are compared. A: primary B: primary A: foreign B: foreign A: foreign B: primary None of the above
A: foreign B: primary
Physical design affects query __A__ but never affects query __B__. A: results, B: performance A: performance, B: results A: results, B: quality A: quality, B: results
A: performance, B: results
Which of the following is "not equal"? LastName != 'Smith' NOT LastName = 'Smith' LastName <> 'Smith' All of the above
All of the above
Which of the following is NOT advantage of views? A view can be used to hide sensitive data from database users by permitting access to the view but NOT to the base table. Complex or difficult SELECT statements can be saved as a view and database users can use the view with a simpler query. An optimal SELECT statement can be saved as a view and distributed to database users All are advantages
All of the above are advantages
Which of the following is NOT a common alternative for table structures? Heap table Sorted table Hash table All of the above are common alternatives
All of the above are common alternatives
Which of the following is a common error when database users write INSERT statements? Inserting duplicate primary key values or foreign key values that do not match an existing primary key. Inserting primary key values for auto-increment columns. Inserting NULL values for columns that are NOT NULL. All of the above are common errors.
All of the above are common errors
Which of the following is a reason why using views in INSERT, UPDATE, and DELETE statements is problematic? A view many NOT contains the primary key of the base table. A view may contains a column that does NOT correspond to a column of a base table. E.g. A derived/computed column from two or more columns A view may join two or more base tables and thus DELETE operations in a view may not be defined to clearly show which row should be deleted from a base table or base tables. All of the above are reasons why using views in INSERT, UPDATE, and DELETE statements is problematic.
All of the above are reasons why using views in INSERT, UPDATE, and DELETE statements is problematic.
Which of the following is NOT a step of physical design? Create initial physical design Identify slow queries Analyze slow queries with some tools E.g. EXPLAIN command. Create or drop index Partition large tables All of the above are steps of physical design
All of the above are steps
Which of the following is NOT a sublanguage of SQL? Data Definition Language (DDL) Data Query Language (DQL) Data Manipulation Language (DML) Data Control Language (DCL) All of the above are subqueries of SQL.
All of the above are subqueries of SQL.
When a SELECT query is executed, the database examines the WHERE clause and estimates _____. Hit ratio Filter factor Selectivity All of the above because they are synonyms.
All of the above because they are synonyms.
If an indexed value appears in a higher level of multi-level index, the value is not repeated at lower levels in _____. A-tree A+tree B-tree B+tree
B-tree
____ returns the string that results from concatenating the string arguments CONCAT(s1, s2, ...) REPLACE(s, from, to) SUBSTRING(s, pos, len) none of the above
CONCAT(s1, s2, ...)
A constraint can be given a name using the ____ keyword, followed by the constraint name and declaration. CONSTRAINT NAME DECLARATION None of the above
CONSTRAINT
____ TABLE statement creates a new table. ADD CREATE MAKE None of the above
CREATE
_____ creates a materialized view. CREATE VIEW CREATE MATERIALIZED VIEW CREATE VIEW WITH DATA None the above
CREATE MATERIALIZED VIEW
The ____ statement create a view.
CREATE VIEW
The ____ statement create a view. ADD VIEW CREATE VIEW MAKE VIEW None of the above
CREATE VIEW
____ returns the difference between 2 dates in number of days. DAY() DATE() DATEDIFF() None of the above
DATEDIFF()
The ____ constraint is used in a CREATE TABLE statement to specify a column's default value when no value is provided. DEFAULT MAKE DEFAULT SET DEFAULT None of the above
DEFAULT
The ____ statement remove existing rows from a table. DELETE REMOVE UPDATE None of the above
DELETE
The ____ clause is used with a SELECT statement to return only unique values or unique rows. DISTINCT ONLY UNIQUE UNIQUE None of the above
DISTINCT
____ DATABASE deletes the database including all tables in the database. DELETE DROP ERASE REMOVE
DROP
____ TABLE deletes a table including all records (rows) of the table DELETE DROP ERASE REMOVE
DROP
____ grants and revokes permissions to and from users. grants and revokes permissions to and from users. Data Query Language (DQL) Data Manipulation Language (DML) Data Control Language (DCL)
Data Control Language (DCL)
Correlated subqueries commonly use the ____ operator CHECK EVALUATE EXISTS None of the above
EXIST
____ operator, which returns TRUE if a subquery selects at least one row and FALSE if no rows are selected.
EXIST
T or F When a table is partitioned, table indexes are NOT partitioned.
F
______ (CustomerID) REFERENCES Customer (CustomerID) can create a foreign key constraint when you create Customer Order table. FOREIGN KEY PRIMARY KEY KEY None of the above
FOREIGN KEY
.T or F Column-oriented storage performs better than row-oriented storage for most transactional databases
False
T of F Different databases and storage engines support the same table structures and index types.
False
T or F Multi-level indexes are usually implemented as B-trees.
False
T or F Since all levels above the bottom are sparse in multi-level indexes, levels rapidly become smaller but the top level never does not fit in one block.
False
T or F Single-level indexes are faster than multi-level indexes on most queries.
False
T or F Sparse indexes are much slower than dense indexes since sparse indexes have more entries and occupy more blocks.
False
T or F Usually, small and large tables should share a tablespace.
False
T or F When a table with a foreign key constraint references a table's primary key, the table with primary key can be deleted with a DROP TABLE statement even when the table with the foreign key constraint is still in the database.
False
T or F You can NOT nest a SELECT statement in another SELECT statement.
False
Aggregate functions are commonly used with the ____ clause DISTINCT GROUP BY ORDER BY None of the above
GROUP BY
The ____ clause groups rows with identical values into a set of summary rows when you want to use an aggregate function for each group. DISTINCT GROUP BY ORDER BY None of the above
GROUP BY
The ____ clause is used with the GROUP BY clause to filter group results FILTER HAVING WHERE None of the above
HAVING
Which of the following is NOT an index type supported by databases? Hash Bitmap Logical All of them are index types supported by databases although used less often.
Hash
HireDate BETWEEN '2000-01-01' AND '2020-01-01' is equivalent to HireDate >= '2000-01-01' AND HireDate <= '2019-12-31' HireDate > '2000-01-01' AND HireDate < '2019-12-31' HireDate <= '2000-01-01' AND HireDate >= '2019-12-31' HireDate < '2000-01-01' AND HireDate > '2019-12-31'
HireDate >= '2000-01-01' AND HireDate <= '2019-12-31'
The ____ operator is used in a WHERE clause to determine if a value matches one of several values. = IN MATCH None of the above
IN
The ____ clause in an INSERT statement specifies the table and columns where data is to be added. INTO SPECIFY VALUES None of the above
INTO
____ tests if a value is not NULL. =NULL !=NULL IS NOT NULL IS NULL
IS NOT NULL
____ tests if a value is NULL. =NULL !=NULL IS NOT NULL IS NULL
IS NULL
While = requires exact match (e.g. 'abcde' = 'abcde'), the ____ operator with the two wildcard characters % and _ allows you to do partial match. For example, 'abcde' ____ 'ab%' or 'abcde' ____ 'abc_e' LIKE PART= PARTIAL MATCH None of the above
LIKE
____ returns the lowercase s. LOWERS(S) REPLACE(S) TRIM(S) UPPER(S)
LOWER(S)
____ returns the month from date data value Day() MONTH() YEAR() None of the above
MONTH()
Which of the following logical operator is NOT supported by the WHERE clause? AND OR NOR NOT
NOR
The ______ clause sorts a query result set by one or more columns in ascending or descending order. ASCEND BY DESCEND BY ORDER BY SORT BY
ORDER BY
______ (EmployeeID) can create the primary key constraint when you create Employee table. KEY CONSTRAINT PRIMARY CONSTRAINT PRIMARY INDEX PRIMARY KEY
PRIMARY KEY
_____ specifies indexes, table structures, and partitions. Analysis Logical design Physical design None of the above
Physical design
Which of the following is NOT an aggregate function? AVG() COUNT() MAX() MIN() SUM() ROUND() All of the above are aggregate functions
ROUND()
____ returns n rounded to d decimal places. POW(x,y) RAND(x,y) ROUND(x,y) None of the above
ROUND(x,y)
The ____ constraint limits the range of a column's values. CHECK RANGE SET RANGE None of the above
SET RANGE
____ is a high-level computer language for storing, manipulating, and retrieving data in a relational database. COBOL Java script Python SQL
SQL
Which of the following is NOT part of physical design? Specify an index is CLUSTERED. Determine foreign keys Specify a table structure is a sorted table. All of the above are part of physical design
Specify an index is CLUSTERED.
T of F In a correlated subquery, the subquery is executed for each row the outer query tries to examine.
T
____ returns the substring from string that starts at position pos and has length len REPLACE(string, pos, len) SUBSTRING(string, pos, len) TRIM(string, pos, len) None of the above
TRIM(string, pos, len)
A(n) _______ is a scheme for organizing rows in blocks on storage media index table index structure table structure None of the above
Table structure
A view can be used in a SELECT statement as if it is a table. E.g. SELECT * FROM EmployeeView WHERE LastName = 'Smith' ORDER BY FirstName True False
True
One table has many partitions that do not overlap and, together, contain all table data.
True
T of F Inserts, updates, and deletes to tables may require re-organization of single-level indexes.
True
T of F Logical indexes are always secondary indexes and require a separate primary index on the same table.
True
T of F Partitions improve query performance by reducing the amount of data accessed by INSERT, UPDATE, DELETE, and SELECT statements
True
T or F A database reads data in a uniform size called a block (Microsoft SQL Server calls it a page) when transferring data between main memory and storage media.
True
T or F An index requires fewer blocks than a table to store because an index contains only an indexed column value and pointer.
True
T or F Any number of rows may be added with a single INSERT statement.
True
T or F Database administrators can improve query performance by assigning frequently accessed tables to tablespaces (or file groups in SQL Server) stored on fast storage media.
True
T or F Each partition is stored in a separate tablespace, specified either explicitly by the database administrator or automatically by the database.
True
T or F For a table with a large column such as a 1M image or a 10M video, each row usually contains a link to the large column, which is stored in a different area.
True
T or F If small tables are commonly accessed in the same query, storing those tables in one tablespace may improve query performance.
True
T or F In multi-level indexes, each level above the bottom level is a sparse sorted index to the level below.
True
T or F In multi-level indexes, usually the top two levels are small and retained in memory, therefore the query reads just one index block from storage media if the index has three levels.
True
T or F Inserts, updates, and deletes to tables may require re-organization of single-level indexes
True
T or F Physical design is dependent on a specific storage engine, which is usually specific to each relational database product, such as Oracle, SQL Server, MySQL, etc.
True
T or F Primary and clustering indexes are on sort columns and usually sparse.
True
T or F Relational databases usually store an entire row within one block.
True
T or F Secondary indexes are on non-sort columns and therefore are always dense
True
T or F Secondary indexes are on non-sort columns and therefore are always dense.
True
T or F Sometimes, an entire index is small enough to reside in main memory and index scan time is insignificant.
True
T or F The result set of an outer join contains the NULL values in the columns from a table for the rows that do NOT have a matching row in that table because there is no data value to display.
True
T or F When a large column is stored in a separate area, a relational database must read at least two different areas of a disk or two different disks to retrieve all the columns of a record.
True
T or F With column-oriented storage, reading or writing an entire row requires accessing multiple blocks.
True
T or F An SQL statement may be written on a single line, but good practice is to write each clause on a separate line.
True
T or F When two tables are combined by comparing columns from the first and second tables, those columns must have the same/comparable data types.
True
T or F You can use all of JOIN, subquery, aggregate functions with GROUP BY and HAVING, WHERE, and ORDER BY in one query.
True
The _____ constraint ensures that all column values are unique. MAKE UNIQUE SET UNIQUE UNIQUE None of the above
UNIQUE
The ____ statement modifies existing rows in a table. CHANGE MODIFY UPDATE None of these
UPDATE
When you modify existing rows in a table , you use the ____ clause to determine which rows are updated. Without it, all rows in that table will be updated. DETERMINE SET WHERE None of theses
WHERE
A(n) ____ is a temporary name assigned to a column or table using the AS keyword although AS is NOT requried. E.g. SELECT LastName AS LN FROM Customer AS C or SELECT LastName LN FROM Customer C.
alias
A(n) ____ is a temporary name assigned to a column or table using the AS keyword although AS is NOT requried. E.g. SELECT LastName AS LN FROM Customer AS C or SELECT LastName LN FROM Customer C. alias subname tempname none of the above
alias
In a ______, the database repeatedly splits the index in two until it finds the entry containing the search value binary search index search table search None of the above
binary search
* is used to display all _____ in a table. columns row rows that contain the NULL value None of the above
columns
A subquery is ____ when the subquery's WHERE clause references a column from the outer query
correlated
A ____ combines two tables without comparing columns and all possible combinations of rows from both tables appear in the result.
cross join
A _____ index contains an entry for every table block. dense sparse full None of the above
dense
Unlike base table data, a view _____ normally store data values.
does NOT
A(n) ____ compares columns of two tables with the = operator equijoin non-equijoin self-join none of the above
equijoin
A(n) ____ compares columns with an operator other than =, such as <, >. equijoin non-equijoin self-join none of the joins above
equijoin
The number of index entries per block is called the _____ of a multi-level index fan-in fan-out levels None of the above
fan-out
When two tables are combined by comparing columns from those, what are the first and second tables in the FROM clause called? first: first and second: second first: left and second: right first: top and second: bottom first: west and second: east
first: left and second: right
____ selects all left and right table rows, regardless of match. full outer join inner join left outer join right outer join
full outer join
A _____ is optimal for inserts and deletes of individual rows. hash table heap table sorted table table cluster
hash table
In a _____, rows are assigned to buckets using a certain function. hash table heap table sorted table table cluster
hash table
Which of the following is NOT what the database can perform to execute a SELECT query? An index scan A heap scan A table scan The database can perform all of the above
heap scan
In a _____, no order is imposed on rows. hash table heap table sorted table table cluster
heap table
A ______ partition is a subset of table rows. horizontal vertical hybrid None of the above
horizontal
The Employee table has a HireDate column. One partition is created for each year. Employees are assigned to partitions based on the year hired. This is an example of horizontal partition vertical partition index partition None of the above
horizontal partition
A(n) _____ scan is a database operation that reads index blocks sequentially, in order to locate the needed table blocks. index heap table None of the above
index
If hit ratio is low and WHERE clause contains an indexed column, the database performs a(n) ______ index scan heap scan table scan None of the above
index scan
selects only matching left and right tables rows
inner join
A(n) ____ is a SELECT statement that combines data from two tables into a single query result. add combine join
join
____ selects all left table rows, but only matching right table rows. full outer join inner join left outer join right outer join
left outer join
An index with primary key values rather than block pointers is called a _____. bitmap index hash index logical index physical index
logical index
A ____ view is a view for which data is stored at all times, which requires refreshing the view when data in a base table used in the view are changed. data materialized stored none of the above
materialized
When two tables are combined by comparing columns from the first and second tables, those columns ____ data types. can be any must have comparable must have the exact same none of the above
must have comparable
A(n) _____ scan is a database operation that reads table blocks directly, without accessing an index. index heap table None of the above
none of the above
A(n) _____ is a subset of table data. tablespace index partition none of the above
partition
Using views in INSERT, UPDATE, and DELETE statements is ____. common slow but no other problem problematic always recommended
problematic
____ selects all right table rows, but only matching left table rows. full outer join inner join left outer join right outer join
right outer join
_____ is good for transactional applications. page storage columnar storage row-oriented storage None of the above
row-oriented storage
A ____ index is an index that is not on the sort column. primary clustering secondary None of the above
secondary
In a ____, a table is joined to itself.
self-join
In a _____, the database designer identifies a sort column that determines physical row order, which is usually the primary key but can be a non-key column or group of columns. hash table heap table sorted table table cluster
sorted table
A ____, sometimes called a nested query or inner query, is a query within another SQL query. complex query simple query subquery none of the above
subquery
The ____ is placed inside parentheses (). self-query subquery transitive query
subquery
A _____ interleaves rows of two or more tables in the same storage area. hash table heap table sorted table table cluster
table cluster
A ______ is optimal when joining interleaved tables on the cluster key, which is usually the primary key of one table and the corresponding foreign key of another. hash table heap table sorted table table cluster
table cluster
If hit ratio is high, the database performs a(n) ______. index scan heap scan table scan None of the above
table scan
Imbalanced multi-level indexes are undesirable because processing time is ______. always slow slower than single-level index unpredictable None of the above
unpredictable
The Employee table has 20 columns. One partition contains the Employee ID, Last Name, Middle Name, First Name. Another partition has Employee ID and 16 columns other than Last Name, Middle Name, First Name. This is an example of horizontal partition vertical partition hybrid partition None of the above
vertical partition
Two tables are combined by comparing columns from the first and second tables, usually ____ the = operator. with without none of the above
with