ISDS 402 Quizzes for Exam 2
Which of the following SQL commands would be used to remove only the data from a table named STUDENT while leaving the table structure intact? DROP TABLE STUDENT; SELECT * FROM STUDENT THEN DROP; DELETE TABLE STUDENT; DELETE FROM STUDENT; REMOVE TABLE STUDENT;
DELETE FROM STUDENT;
Which SQL keyword is used to eliminate duplicate rows in the results of an SQL SELECT query? SORT UNIQUE REDUCE DISTINCT ORDER BY
DISTINCT
Which of the following SQL commands would be used to remove both the data and the table structure of a table named STUDENT? DROP TABLE STUDENT; DELETE FROM STUDENT; REMOVE TABLE STUDENT; DELETE TABLE STUDENT; SELECT * FROM STUDENT THEN DROP;
DROP TABLE STUDENT;
Which of the following cannot be done using the CONSTRAINT phrase? Create a single attribute primary key. Define a foreign key. Defining a name for the constraint. Define an attribute to be NOT NULL
Define an attribute to be NOT NULL.
A star schema resembles a star, with a dimension table at the center and fact tables radiating out from the center. T/F
False
Business Intelligence (BI) systems support operational activities. T/F
False
CREATE TABLE is one of the data manipulation language. T/F
False
Database rollback requires the use of logs, but roll forward does not. T/F
False
For a column to be defined as the primary key using table constraints, the column must have been given the property NULL. T/F
False
If you define a PK as surrogate key, you still need to provide a value for the PK when you insert a new record. T/F
False
In SQL CREATE TABLE statement, the NULL column constraint specifies that all values in that column must be NULL. T/F
False
In SQL, once a table is created, it is not possible to add or drop a column in the table any more. T/F
False
In SQL, the WHERE clause is used to specify which columns will be included in the result. T/F
False
In SQL, the order of the rows that result from a SELECT statement can be set using the SORT BY phrase. T/F
False
In database, referential integrity increases user integrity to be honest in using data. T/F
False
RFM analysis is primarily based on operational database. T/F
False
Suppose table STUDENT uses a surrogate key. When a student record is removed, the surrogate key associated with the student can be reused to add a new student to the table. T/F
False
Suppose we have the below SQL statement SELECT EmployeeNumber, FirstName, LastName, Department, OfficePhone FROM EMPLOYEE WHERE EmployeeNumber BETWEEN 3 AND 10; If an employee has an number of 10, he/she will not be selected. T/F
False
The "left" in LEFT JOIN in SQL represents the order of the join starts from the left. T/F
False
The CREATE TABLE statement is one of the data manipulation language (DML) of SQL. T/F
False
The relationship between the fact table and a dimensional table is 1:1. T/F
False
The simplest and most feasible means of recovering a database is through reprocessing. T/F
False
When using SQL to create a table, a column is defined by declaring in this order: data type, column name, and optional constraints. T/F
False
Which SQL keyword is used to apply conditions to restrict groups that appear in the results of a SELECT query that uses GROUP BY? SORT WHERE DISTINCT LIKE HAVING
HAVING
Given the table STUDENT(StudentID, Name, Advisor), which of the following SQL statements would be used to add new student row to the STUDENT table? INSERT INTO STUDENT (New Student Data) VALUES (123, 'Jones', 'Smith'); INPUT DATA STUDENT SET StudentID=123, Name=' Jones', Advisor='Smith'; INSERT INTO STUDENT VALUES (123, 'Jones', 'Smith'); INSERT DATA STUDENT SET StudentID=123, Name='Jones', Advisor='Smith'; INPUT INTO STUDENT (123, 'Jones', 'Smith');
INSERT INTO STUDENT VALUES (123, 'Jones', 'Smith');
Which of the following is not one of the five SQL built-in functions? MIN MAX SUM MODE COUNT AVG
MODE
Which of the following is not one of the five SQL built-in functions? Max Count Mode Avg Sum Min
Mode
Which type of join is created to allow unmatched rows to appear in the result of a join operation? OUTER JOIN COMBINE JOIN ODD JOIN INNER JOIN OPEN JOIN
OUTER JOIN
Which type of lock assumes that no conflicts will occur? Explicit locks Granular locks Open locks Optimistic locks Shared locks
Optimistic locks
A data mart is a collection of data that addresses a particular component or functional area of a business. T/F
True
A nested query always implements the inner most query first and then works inside out. T/F
True
All actions in an atomic transaction are completed successfully or none of the actions are completed at all. T/F
True
Business Intelligence (BI) reporting systems can analyze data using standard SQL. T/F
True
Concurrent processing of a database only poses problems when two or more processes attempt to update the same data. T/F
True
Data warehouses are populated with data prepared by extract, transform, and load (ETL) systems. T/F
True
Data warehouses store historical data as well as current data. T/F
True
Generally, the more restrictive the transaction isolation level, the less the throughput of database processing. T/F
True
In SQL, the NOT keyword is a logical operator, and it negates the associated operator. T/F
True
One of the major limitations for SQL subquery is that it can only return the columns from the top table. T/F
True
Problematic data include so-called "dirty data." T/F
True
When creating tables, the foreign key (if any) of the child table needs to be of the same data type as the primary key in the parent table. T/F
True
When using SQL to create a table, specifying a data type of Char(10) indicates a fixed length field of 10 characters. T/F
True
Which of the following is the correct SQL clause to restrict the results of a SELECT query to only records that have a value in the range of 10 to 50 in the Hours column? WHERE Hours IN [10, 50] WHERE Hours = MIN(10) and MAX(50) WHERE Hours RANGE 10 TO 50 WHERE Hours BETWEEN 10 AND 50 WHERE Hours = 10 and Hours = 50
WHERE Hours BETWEEN 10 AND 50
Given the tables STUDENT(StudentID, StudentName, AdvisorID) ADVISOR(AdvisorID, AdvisorName, Office, Phone) which of the following SQL statements would be used to implant a join between the two tables? WHERE STUDENT.AdvisorID MATCH ADVISOR.AdvisorID WHERE STUDENT JOIN ADVISOR WHERE STUDENT = ADVISOR WHERE STUDENT JOIN ADVISOR ON STUDENT.AdvisorID = ADVISOR.AdvisorID WHERE STUDENT MATCH ADVISOR
WHERE STUDENT JOIN ADVISOR ON STUDENT.AdvisorID = ADVISOR.AdvisorID
Which symbol is used in MS SQL Server as a wildcard to represent a single, unspecified character? % (percent sign) ! (exclamation mark) ; (semi-colon) ? (question mark) _ (underscore)
_ (underscore)
The term DBA refers to the database administrator database accessor database auditor data administrator data business auditor
database administrator
An ACID transaction is one that is all but ________. atomic durable guaranteed isolated consistent
guaranteed
A data mart differs from a data warehouse in that ________. it deals with a particular component or functional area of the business data mart users must have more data management expertise than data warehouse employees it is updated more frequently by the data mart users it has a larger database
it deals with a particular component or functional area of the business
Whether a lock applies to data at the record level, page level, table level, or database level is referred to as lock phasing lock exclusivity lock granularity serializable locking lock sharing
lock granularity
The recovery technique in which the database is returned to a known state and then all valid transactions are reapplied to the database is known as rollforward rollback checkpointing transaction logging reprocessing
rollforward