Accounting
Anthropology
Archaeology
Art History
Banking
Biology & Life Science
Business
Business Communication
Business Development
Business Ethics
Business Law
Chemistry
Communication
Computer Science
Counseling
Criminal Law
Curriculum & Instruction
Design
Earth Science
Economic
Education
Engineering
Finance
History & Theory
Humanities
Human Resource
International Business
Investments & Securities
Journalism
Law
Management
Marketing
Medicine
Medicine & Health Science
Nursing
Philosophy
Physic
Psychology
Real Estate
Science
Social Science
Sociology
Special Education
Speech
Visual Arts
Design
Q:
A relation is in third normal form (3NF) if and only if it is in 2NF and ________.
A) all non-key attributes are determined by the entire primary key
B) there are no non-key attributes determined by another non-key attribute
C) every attribute is a candidate key
D) every candidate key is a determinant
Q:
A relation is in second normal form (2NF) if and only if it is in 1NF and ________.
A) all non-key attributes are determined by the entire primary key
B) there are no non-key attributes determined by another non-key attribute
C) every attribute is a candidate key
D) every candidate key is a determinant
Q:
A table that meets the definition of a relation is in ________.
A) First Normal Form
B) Second Normal Form
C) Third Normal Form
D) Boyce-Codd Normal Form
Q:
Suppose that you need to update one value of the column SalesCost in a relation. The way the relation is constructed, this value actually needs to be changed in three different rows. However, you only change the value in two of the rows. You have just created a(n) ________.
A) normalization anomaly
B) insertion anomaly
C) update anomaly
D) deletion anomaly
Q:
If the removal of facts about one entity results in the unintentional loss of data about another entity, this is referred to as a(n) ________.
A) normalization anomaly
B) insertion anomaly
C) update anomaly
D) deletion anomaly
Q:
Having to enter facts about two entities when we want to enter facts about only one is an example of ________.
A) insertion anomaly
B) update anomaly
C) deletion anomaly
D) normalization anomaly
Q:
A ________ is used to limit the possible values of a foreign key.
A) composite key
B) surrogate key
C) functional dependency
D) referential integrity constraint
Q:
Referential integrity constraints are used to limit the possible values of a ________.
A) composite key
B) candidate key
C) foreign key
D) surrogate key
Q:
A key consisting of one or more columns that is a primary key in another relation is a ________.
A) composite key
B) candidate key
C) foreign key
D) surrogate key
Q:
An artificial column added to a relation to serve as the primary key is a ________.
A) composite key
B) candidate key
C) foreign key
D) surrogate key
Q:
When designing a database, one of the candidate keys in a relation is selected as the ________.
A) composite key
B) primary key
C) foreign key
D) surrogate key
Q:
A determinant that determines all the other columns in a relation is a ________.
A) record
B) surrogate key
C) foreign key
D) candidate key
Q:
A combination of two or more columns used to identify particular rows in a relation is a ________.
A) record
B) composite key
C) foreign key
D) surrogate key
Q:
A combination of one or more columns used to identify particular rows in a relation is a ________.
A) record
B) field
C) key
D) tuple
Q:
The only reason(s) for having relations is to ________.
A) store instances of functional dependencies
B) store equation components
C) store equation results
D) Both B and C are correct
Q:
Which of the following is true about the functional dependency (A, B) → (C, D)?
A) A is the determinant of C.
B) A and B together are determined by C and D together.
C) A and B together determine D.
D) C and D together determine A.
Q:
Which of the following is NOT true about the functional dependency A → (X, Y)?
A) X is functionally dependent on A.
B) A determines Y.
C) X and Y together determine A.
D) X and Y are functionally dependent on A.
Q:
Given the functional dependency (A, B) → C, then ________.
A) A and B together → C
B) A → C
C) B → A
D) Both A and B are correct
Q:
Given the functional dependency (A, B) → C, (A, B) is a ________.
A) dependent variable
B) determinant
C) composite determinant
D) Both B and C are correct
Q:
Given the functional dependency A → (B, C), A is a(n) ________.
A) independent variable
B) dependent variable
C) determinant
D) composite determinant
Q:
Saying that two entities are functionally dependent means that ________.
A) the entities are always connected by a mathematical equation
B) for one of the entities, if we are given the value of that entity, we can determine the value of one other entity
C) for both of the entities, if we are given the value of one entity, we can determine the value of the other entity
D) the functional dependency will have to be removed through normalization
Q:
An attribute is also known as a ________.
A) table
B) relation
C) row
D) field
Q:
A tuple is also known as a ________.
A) table
B) relation
C) row
D) field
Q:
A relation is also known as a(n) ________.
A) table
B) tuple
C) relationship
D) attribute
Q:
Which of the following is true for a relation?
A) Entities in a column vary as to kind.
B) The order of the columns is important.
C) The order of the rows is unimportant.
D) More than one column can use the same name.
Q:
Which of the following is not true about a relation?
A) Has rows containing data about an entity
B) Has columns containing data about attributes of the entity
C) Has cells that hold only a single value
D) Can have two identical rows
Q:
A relation that is in domain/key normal form is assured to be free from all anomalies.
Q:
Domain/key normal form requires that every constraint be a logical consequence of the definition of domains and keys.
Q:
A relation is in 4NF if it is in BCNF and it has no multivalued dependencies.
Q:
A relation is in 4NF when multivalued dependencies are isolated in their own relation.
Q:
The multivalued dependency noted as A → → B, means that the value of A determines a set of values of B.
Q:
A multivalued dependency exists when a determinant is matched to a set of values.
Q:
When designing or normalizing relations, each relation should have only one theme.
Q:
The essence of normalization is taking a relation that is not in BCNF and breaking it into multiple relations such that each one is in BCNF.
Q:
What are SQL Built-in Functions?
Q:
Given the table CUSTOMER(CustID, Name, PhoneNumber, AccountBalance), write the standard SQL query to retrieve the Name and PhoneNumber of customers whose name begins with 'S'.
Q:
Given the table CUSTOMER(CustID, Name, PhoneNumber, AccountBalance), write the standard SQL query to retrieve the Name and PhoneNumber of customers with a balance greater than 50.
Q:
The following database will be used in this question:
GENERAL SALES DATABASE:
SALESREP SalesRepNo
RepName
HireDate 654
Jones
01/02/2005 734
Smith
02/03/2007 345
Chen
01/25/2004 434
Johnson
11/23/2004 CUSTOMER CustNo
CustName
Balance
SalesRepNo 9870
Winston
500
345 8590
Gonzales
350
434 7840
Harris
800
654 4870
Miles
100
345 Explain the use of the GROUP BY keyword. Include an example based on the CUSTOMER table from the General Sales database.
Q:
The following database will be used in this question:
GENERAL SALES DATABASE:
SALESREP SalesRepNo
RepName
HireDate 654
Jones
01/02/2005 734
Smith
02/03/2007 345
Chen
01/25/2004 434
Johnson
11/23/2004 CUSTOMER CustNo
CustName
Balance
SalesRepNo 9870
Winston
500
345 8590
Gonzales
350
434 7840
Harris
800
654 4870
Miles
100
345 Explain the use of the SQL keyword LIKE. Include an example based on the CUSTOMER table from the General Sales database; use at least one wildcard.
Q:
The following database will be used in this question:
GENERAL SALES DATABASE:
SALESREP SalesRepNo
RepName
HireDate 654
Jones
01/02/2005 734
Smith
02/03/2007 345
Chen
01/25/2004 434
Johnson
11/23/2004 CUSTOMER CustNo
CustName
Balance
SalesRepNo 9870
Winston
500
345 8590
Gonzales
350
434 7840
Harris
800
654 4870
Miles
100
345 Explain the use of the of SQL keyword SELECT. Include an example based on the CUSTOMER table in the General Sales database.
Q:
Briefly describe subqueries and joins. Explain when each is not an acceptable alternative for the other.
Q:
Explain why it is important to learn SQL.
Q:
What is SQL?
Q:
Suppose your company stores EMPLOYEE and CUSTOMER data in separate tables. If you want to find all customers who are not also employees, which SQL keyword would you most likely use?
A) INTERSECT
B) UNION
C) EXCEPT
D) UNION ALL
Q:
Suppose your company stores EMPLOYEE and CUSTOMER data in separate tables. If you want to find all employees who are also customers, which SQL keyword would you most likely use?
A) INTERSECT
B) UNION
C) EXCEPT
D) UNION ALL
Q:
Suppose tables EMPLOYEE and CUSTOMER both store address information, and you want to send a letter to all employees and customers of your company to make a major announcement. Which SQL keyword would you most likely use here?
A) INTERSECT
B) UNION
C) UNION ALL
D) JOIN
Q:
In an SQL query of two tables, which SQL keyword indicates that we want data from all the rows of one table to be included in the result, even if the row does not correspond to any data in the other table?
A) LEFT JOIN
B) RIGHT JOIN
C) INCLUDE
D) Both A and B are correct
Q:
Regarding the interchangeability of subqueries and joins, ________.
A) a join can always be used as an alternative to a subquery, and a subquery can always be used as an alternative to a join
B) a join can sometimes be used as an alternative to a subquery, and a subquery can sometimes be used as an alternative to a join
C) a join can always be used as an alternative to a subquery, and a subquery can sometimes be used as an alternative to a join
D) a join can sometimes be used as an alternative to a subquery, and a subquery can always be used as an alternative to a join
Q:
In an SQL query, which SQL keyword is used to specify the column names to be used in a join?
A) FROM
B) JOIN
C) SELECT
D) WHERE
Q:
In an SQL query, which SQL keyword is used to specify the names of tables to be joined?
A) FROM
B) HAVING
C) JOIN
D) WHERE
Q:
When one SQL query is embedded in another SQL query, the top level SQL query can still contain an SQL ________ clause.
A) FROM or SORT BY
B) UNIQUE IN
C) GROUP BY
D) WHERE IN
Q:
When one SQL query is embedded in another SQL query to simulate a join, the second SQL query is embedded in the ________ of the first query.
A) SELECT
B) FROM
C) WHERE
D) HAVING
Q:
When one SQL query is embedded in another SQL query, this is referred to as a ________.
A) subquery
B) join
C) WHERE Query
D) subset query
Q:
In an SQL query, which SQL keyword is used to implement a subquery?
A) GROUP BY
B) HAVING
C) ORDER BY
D) SELECT
Q:
Based on the tables below, which of the following commands in ANSI SQL would return only the name of the sales representative and the name of the customer for each customer that has a balance greater than 400?
GENERAL SALES DATABASE:
SALESREP SalesRepNo
RepName
HireDate 654
Jones
01/02/2005 734
Smith
02/03/2007 345
Chen
01/25/2004 434
Johnson
11/23/2004 CUSTOMER CustNo
CustName
Balance
SalesRepNo 9870
Winston
500
345 8590
Gonzales
350
434 7840
Harris
800
654 4870
Miles
100
345 A) SELECT *
FROM SALESREP, CUSTOMER
WHERE Balance > 400;
B) SELECT DISTINCT RepName, CustName
FROM SALESREP, CUSTOMER
WHERE Balance > 400;
C) SELECT *
FROM SALESREP, CUSTOMER
WHERE SALESREP.SalesRepNo = CUSTOMER.SalesRepNo
AND Balance > 400;
D) SELECT RepName, CustName
FROM SALESREP, CUSTOMER
WHERE SALESREP.SalesRepNo = CUSTOMER.SalesRepNo
AND Balance > 400;
Q:
Based on the tables below, which of the following ANSI SQL commands would return the average customer balance grouped by SalesRepNo?
GENERAL SALES DATABASE:
SALESREP SalesRepNo
RepName
HireDate 654
Jones
01/02/2005 734
Smith
02/03/2007 345
Chen
01/25/2004 434
Johnson
11/23/2004 CUSTOMER CustNo
CustName
Balance
SalesRepNo 9870
Winston
500
345 8590
Gonzales
350
434 7840
Harris
800
654 4870
Miles
100
345 A) SELECT AVG (Balance)
FROM CUSTOMER
WHERE SalesRepNo;
B) SELECT AVG (Balance)
FROM CUSTOMER
GROUP BY SalesRepNo;
C) SELECT AVG (Balance)
FROM CUSTOMER, SALESREP
WHERE SALESREP.SalesRepNo = CUSTOMER.SalesRepNo;
D) SELECT AVG (Balance)
FROM CUSTOMER
ORDER BY SalesRepNo;
Q:
Given a table with the structure: EMPLOYEE (EmpNo, Name, Salary, HireDate), which of the following is not a valid ANSI SQL command?
A) SELECT *
FROM EMPLOYEE
WHERE Name LIKE 'Ja%';
B) SELECT COUNT(*)
FROM EMPLOYEE
WHERE Salary < 30000;
C) SELECT HireDate, COUNT(*)
FROM EMPLOYEE
GROUP BY HireDate;
D) SELECT HireDate, COUNT(*)
FROM EMPLOYEE
WHERE Salary < 30000;
Q:
In an SQL query, which SQL keyword is used with GROUP BY to select groups meeting specified criteria?
A) AND
B) WHERE
C) HAVING
D) IN
Q:
In an SQL query, which SQL keyword is used with built-in functions to group together rows that have the same value in a specified column or columns?
A) GROUP BY
B) ORDER BY
C) SELECT
D) SORT BY
Q:
Assuming the "Quantity" column of an ORDER table contains integer data, what does COUNT(Quantity) compute?
A) The number of rows in the ORDER table
B) The number of non-zero values in the Quantity column
C) The number of distinct values in the Quantity column
D) The number of non-null values in the Quantity column
Q:
In an SQL query, which built-in function is used to compute the number of rows in a table?
A) AVG
B) COUNT
C) MAX
D) SUM
Q:
In an SQL query, the built-in functions SUM and AVG work with columns containing data of which of the following data types?
A) Integer
B) Numeric
C) Char
D) Both A and B are correct
Q:
In an SQL query, which built-in function is used to obtain the smallest value of numeric columns?
A) AVG
B) COUNT
C) MAX
D) MIN
Q:
In an SQL query, which built-in function is used to obtain the largest value of numeric columns?
A) AVG
B) COUNT
C) MAX
D) MIN
Q:
In an SQL query, which built-in function is used to compute the average value of numeric columns?
A) AVG
B) MEAN
C) MAX
D) SUM
Q:
In an SQL query, which built-in function is used to total numeric columns?
A) AVG
B) COUNT
C) MAX
D) SUM
Q:
Given a table with the structure: EMPLOYEE (EmpNo, Name, Salary, HireDate), which of the following would find all employees whose name begins with the letter "S" using Microsoft Access?
A) SELECT *
FROM EMPLOYEE
WHERE Name IN ['S'];
B) SELECT EmpNo
FROM EMPLOYEE
WHERE Name LIKE 'S';
C) SELECT *
FROM EMPLOYEE
WHERE Name LIKE 'S*';
D) SELECT *
FROM EMPLOYEE
WHERE Name LIKE 'S%';
Q:
Given a table with the structure: EMPLOYEE (EmpNo, Name, Salary, HireDate), which of the following would find all employees whose name begins with the letter "S" using standard SQL?
A) SELECT *
FROM EMPLOYEE
WHERE Name IN ['S'];
B) SELECT EmpNo
FROM EMPLOYEE
WHERE Name LIKE 'S';
C) SELECT *
FROM Name
WHERE EMPLOYEE LIKE 'S*';
D) SELECT *
FROM EMPLOYEE
WHERE Name LIKE 'S%';
Q:
In an SQL query, which SQL keyword is used to sort the result table by the values in one or more columns?
A) GROUP BY
B) ORDER BY
C) SELECT
D) SORT BY
Q:
In an SQL query, which of the following symbols is used by Microsoft Access to represent a single unspecified character?
A) _ (underscore)
B) ? (question mark)
C) * (asterisk)
D) % (percent)
Q:
In an SQL query, which of the following symbols is used by ANSI SQL to represent a single unspecified character?
A) _ (underscore)
B) ? (question mark)
C) * (asterisk)
D) % (percent)
Q:
In an SQL query, which SQL keyword is used to determine if a column value is equal to any one of a set of values?
A) AND
B) EXISTS
C) OR
D) IN
Q:
In an SQL query, which SQL keyword is used to link two conditions that both must be true for the rows to be selected?
A) AND
B) EXISTS
C) OR
D) IN
Q:
In an SQL query, which SQL keyword is used to state the condition that specifies which rows are to be selected?
A) SET
B) FROM
C) SELECT
D) WHERE
Q:
In an SQL query, which of the following symbols is used by ANSI SQL to represent all the columns in a single table?
A) _ (underscore)
B) ? (question mark)
C) * (asterisk)
D) % (percent)
Q:
In an SQL query, which SQL keyword must be used to remove duplicate rows from the result table?
A) DELETE
B) DISTINCT
C) UNIQUE
D) KEY
Q:
In an SQL query, which SQL keyword is used to specify the table(s) to be used?
A) EXISTS
B) FROM
C) SELECT
D) WHERE
Q:
In an SQL query, which SQL keyword actually begins the query?
A) EXISTS
B) FROM
C) SELECT
D) WHERE
Q:
When making an SQL query, we are using SQL as a(n) ________.
A) DDL
B) DML
C) embedded language
D) SET
Q:
SQL is a ________.
A) data sublanguage
B) data manipulation language
C) data definition language
D) programming language