Database Exam
You form compound conditions by connecting two or more simple conditions using _____. A. AND, OR, and NOT operators B. subqueries C. multiple SELECT statements D. WHERE caluses
A. AND, OR, and NOT operators
Pete would like to set up a database called BAKE_SALE_FUNDRAISER so that he can begin defining the objects within it. Which MySQL command should Pete use? A. CREATE DATABASE BAKE_SALE_FUNDRAISER: B. CREATE DATABASE: BAKE_SALE_FUNDRAISER C. ACTIVATE DATABASE BAKE_SALE_FUNDRAISER; D. ACTIVATE DATABASE: BAKE_SALE_FUNDRAISER
A. CREATE DATABASE BAKE_SALE_FUNDRAISER:
Which clause lets you group data on a particular column? A. GROUP BY B. GROUP C. SET GROUP D. GROUPING
A. GROUP BY
Bradford would like to write a query that returns the average unit sales over the last 30 days for each of his bakery's product types: cupcakes, muffins, and cookies. There are several flavors of each in the table, but the product types are identified in a CATEGORY column as "Cupcake," "Muffin," or "Cookie." What should the last part of his query be, following the part shown below? SELECT CATEGORY, AVG(UNIT_SALES) FROM PRODUCTS A. GROUP BY CATEGORY; B. WHERE CATEGORY IN ('Cupcake', 'Muffin','Cookie'); C. ORDER BY CATEGORY; D. BETWEEN('Cupcake','Muffin','Cookie');
A. GROUP BY CATEGORY;
Which clause is used to restrict the groups that will be included in a query result? A. HAVING B. WHERE C. HAVE D. LIKE
A. HAVING
StayWell stores information about the facilities where student residents live in the _____ table. A. PROPERTY B. OFFICE C. RENTAL_UNIT D. OWNER
A. PROPERTY
KimTay sells A. Pet supplies B. sporting goods C. furniture D. electronic games
A. Pet supplies
Which of the following SQL commands changes the value in the column LAST_NAME within the row containing a REP_ID of 007? A. UPDATE SALES_REP SET LAST_NAME = 'Bond' WHERE REP_ID = '007'; B. CHANGES SALES_REP LAST_NAME = ' Bond' WHERE REP_ID = '007'; C. INSERT SALES_REP SET LAST_NAME = 'Bond' WHERE REP_ID = '007'; D. REPLACE SALES_REP LAST_NAME = 'Bond' WHERE REP_ID = '007';
A. UPDATE SALES_REP SET LAST_NAME = 'Bond' WHERE REP_ID = '007';
What is the appropriate sequence for the elements in a SQL query clause that displays the rows returned in a particular order? A. command, major sort key followed by operator (if any), minor sort key followed by operator ( if any) B. command, secondary sort key followed by operator (if any) , primary sort key followed by operator(if any) C. command, operator (if any), major sort key, minor sort key D. primary sort key followed by operator(if any), secondary sort key followed by operator(if any), command
A. command, major sort key followed by operator (if any), minor sort key followed by operator ( if any)
Diamonds are sometimes used in E-R diagrams to _____. A. describe the relationship between two tables B. identify the names of entities in a database C. represent individual tables D. indicate which entity is on the "many" side of a one-to-many relationship
A. describe the relationship between two tables
A table in second normal form should be improved to third normal form because _____. A. its determinants can cause update anomalies B. this reduces the number of relations in the database C. its candidate keys can cause update anomalies D. this eliminates repeating groups that waste space
A. its determinants can cause update anomalies
What does the following SQL query do? SELECT INVOICE_NUM FROM INVOICE_LINE WHERE PROD_CODE IN (SELECT PROD_CODE FROM PRODUCTS WHERE (PROD_TYPE = 'Cupcake')); A. lists the invoice numbers for invoices containing line items with a "Cupcake" product type B. lists the product codes for all " Cupcake" type products that appear on invoices C. cause an error because it includes more than one SELECT command D. lists all rows from the invoice line table that contain a " cupcake" type product
A. lists the invoice numbers for invoices containing line items with a "Cupcake" product type
When you need to sort data on two columns, the less important column is call the ___. A. minor sort key B. foreign sort key C. double sort key D. primary sort key
A. minor sort key
StayWell rents out and helps to maintain ____. A. one-to five- bedrooms properties B. apartments in the New York City area C. office buildings for educational organizations D. medical equipment
A. one-to five-bedrooms properties
In the following shorthand representation of a database, the underlined items are _____. INVOICES (INVOICE_NUM, INVOICE_DATE, CUST_ID) INVOICE_LINE (INVOICE_NUM, ITEM_ID, QUANTITY, QUOTED_PRICE) ITEM (ITEM_ID, DESCRIPTION, ON_HAND, CATEGORY, LOCATION, PRICE) A. primary keys B. entites C. relations D. nonkey columns
A. primary keys
The duplication of data is formally called ___. A. redundancy B. a repeating group C. an anomaly D. replication
A. redundancy
Which MySQL command do you use to define a table's structure by listing its columns, data types, and column lengths? A. DEFINE TABLE B. CREATE TABLE C. ADD TABLE D. NEW TABLE
B. CREATE TABLE
A MySQL database includes a CUSTOMER table, which stores each customer's current balance in U.S. dollars and cents, among other information. The correct data type for the BALANCE column in the CUSTOMER table is _____. A. CHAR B. DECIMAL C. SMALLINT D. INT
B. DECIMAL
Mallory would like to display the rows from a MySQL table that have "Red", "Pink",or "White" in the COLOR column. A concise way of writing her query would be to use a(n)_____. A. LIKE clause (no) B. IN clause C. BETWEEN clause D. CONTAINS clause (no)
B. IN clause
In an entity-relationship (E-R) diagram, one-to-many relationships between entities are drawn as _____. A. equals signs between shapes B. lines between shapes C. rectangles D. circles
B. Lines between shapes
What SQL command do you use to query a database? A. WHERE B. SELECT C. SET D. FROM
B. SELECT
Which SQL command is used to view the data in a table? A. DISPLAY B. SELECT C. SHOW D. VIEW
B. SELECT
A text file with a .sql extension that contains SQL commands is a ___. A. command file B. script file C. batch process D. SQL plugin
B. Script file
In general, when designing tables, which type of structure is most preferable? A. complete B. simple C. detailed D. complex
B. Simple
Which statement describing relations is false? A. The order of the columns in a relation is immaterial B. The entries in a relation may be single-or double-valued. C. A relation is a two-dimensional table D. Each column in a relation has a distinct name.
B. The entries in a relation may be single-or double-valued.
To display all the rows and columns in a table, type the word SELECT, followed by a(n) _____, followed by the keyword FROM and then the name of the table containing the data you want to view. A. slash B. asterisk C. Double quote D. single quote
B. asterisk
Jesse has written some commands in MySQL Workbench that he expects to run regularly, and he would like to save them for future use. What should he do next? A. copy and paste the query into a .txt file B. click on File, then Save Script As C. select a script from within his file system and click Open D. click on Query, then select Execute
B. click on File, then Save Script As
The KimTay INVOICE table consists of the invoice number, invoice date, and ___. A. quoted price B. customer ID C. item ID D. quantity
B. customer ID
In a relational database, ____. A. rows must be ordered numerically B. each row should be unique C. columns should be ordered correctly D. relations must include repeating groups
B. each row should be unique
One of the categories of update anomalies is ___. A. functional dependencies (no) B. inconsistent data C. functional splits D. qualifications
B. inconsistent data
The KimTay database must combine data from all of its table in order to print a(n)____. A. customer directory B. invoice C. sales rep directory D. Inventory report (no)
B. invoice
When a subquery is used in a SQL statement ____. A. the outer query is evaluated first B. the subquery is evaluated first C. a temporary table is created and displayed D. only the subquery can include a WHERE clause
B. the subquery is evaluated first
Which of the following operators can be used for a column computation? A. +, ^, and ** B. ^ C. + D. **
C. +
How many tables make up the KimTay database? A. 6 B. 7 C. 5 D. 4
C. 5
In the StayWell database, each rental property is identified by ____. A. a combination of letters and numbers B. the owner's owner ID C. A unique integer D. A unique character value
C. A unique integer
Why have Social Security numbers traditionally been used as primary keys for the database tables that store personal data on individual people? A. The Social Security number is functionally independent of most other columns in such a table B. A Social Security number column is functionally dependent on all the other columns in such a table C. All other columns in such a table are functionally dependent on the Social Security number column. D. The Social Security number is always the only candidate key in such a table.
C. All other columns in such a table are functionally dependent on the Social Security number column.
Each line in a KimTay invoice contains an item ID, the number of units of the item ordered, the quoted price, and ___ A. the customer ID B. The wholesale price C. and item description D. an office location
C. An item description
Which data type should you use for columns that contain letters and special characters or number that will not be used in calculations? A. CHARACTER B. TEXT C. CHAR D. STRING
C. CHAR
KimTay's management would like to set up a structure that contains different categories of information and the relationships between these categories. In other words, they want a _____. A. spreadsheet B. table C. database D. digital template
C. Database
Preceding a condition with which SQL operator reverses the truth of the original condition. A. OR B. REVERSE C. NOT D. NULL
C. NOT
Which statement about the command used to view all columns and rows in a database table is correct? A. The command used in SQL Server is different form that used in MySQL and Oracle. B. When using Oracle, the semicolon in the MySQLm, command must be replaced with a slash. C. The same simple version of this command can be used in MySQL, Oracle, and SQL Server. D. The command contains two symbols: a colon and a question mark.
C. The same simple version of this command can be used in MySQL, Oracle, and SQL Server.
Which statement about changing the default database is FALSE? A. The MySQL command USE SPIDERS; makes SPIDERS the default database. B. To activate a database, you execute the USE command followed by the database's name. C. There are two steps to choosing a database to use: changing the default and activation. D. Tables created in MySQL are added to the default database.
C. There are two steps to choosing a database to use: changing the default and activation.
A relational database____. A. implements relationships through repeating groups B. consists of relationships between entities C. consists of tables called relations D. implements relationships through common table rows
C. consists of tables called relations
The process of determining the particular tables and columns that will comprise a database is known as ___ A. relational management B. qualification C. database design D. normalization
C. database design
StayWell provides maintenance services across the properties they manage. The SERVICE_CATEGORY table in their database contains a _____. A. location name B. property ID C. description of the category D. location number
C. description of the category
In MySQL, Philip can use the command SELECT* FROM VACATION_PACKAGES; to _____. A. display a list of table in the VACATION_PACKAGES database B. add a new row of data to the table VACATION_PACKAGES C. display all rows and columns in the table VACATION_PACKAGES D. select only certain rows to modify from the VACATION_PACKAGES table
C. display all rows and columns in the table VACATION_PACKAGES
You receive the following requirements for a caterer's database: For a customer, store a customer ID, last name, first name, billing address, and phone number For an order, store an order number, order date, event date, customer ID, description of services, and total price for services When designing this database, which step do you take first? A. Create a CUSTOMER table that includes all the customers' attributes as columns. B. Identify and name the entities CUSTOMER and ORDER. C. Identify customer ID, last name, first name, billing address, and phone number as attributes. D. Identify the customer ID and order number as unique identifiers.
C. identify customer ID, last name, first name, billing address, and phone number as attributes.
When a script file contains more than one command, each command must end with a(n)___. A. apostrophe B. Colon C. semicolon D. period
C. semicolon
When you are designing a database, ____. A. the many part of a relationship between tables occurs where the matching column is the primary key B. attributes with functional dependencies should not be placed together in the same table C. you might be able to determine the relationships between tables directly from the requirements D. one useful approach to identifying relationships between tables is too look for matching rows.
C. you might be able to determine the relationships between tables directly form the requirements
In the SELECT clause, you can indicate that you want to include all columns using the _____. A. \ symbol B. ? symbol C. / symbol D. * symbol
D. * symbol
SQL was developed in the mid-____. A. 1990s B. 1950s C. 2000s D. 1970s
D. 1970s
Which function determines the number of rows in a table? A. CALCULATE B. ROW C. NUMBER D. COUNT
D. COUNT
Janice wants to examine the structure of the COFFEE table, which was created by a colleague, including the column names, data types, and acceptance/nonacceptance of null values. Which command should Janice execute? A. SELECT COFFEE; B. CREATE TABLE COFFEE; C. SHOW COFFEE; D. DESCRIBE COFFEE;
D. DESCRIBE COFFEE;
When you need to find rows with null values in a MySQL table, you can use the ___. A. GROUP BY NULL condition B. SELECT NULL command C. = NULL condition D. IS NULL operator
D. IS NULL operator
To determine how many Pump & Filter Kits are available in the warehouse, a KimTay employee would need to consult which database table? A. INVOICES B. SALES_REP C. INVOICE_LINE D. ITEM
D. ITEM
What was the original name for SQL? A. MYSEQUEL B. DBase C. MySQL D. SEQUEL
D. SEQUEL
which function calculates a total of the values in a column? A. TOTAL B. ADD C. CALCULATE D. SUM
D. SUM
Which statement regarding primary keys is true? A. All primary keys are values generated by database software. B. By definition, a primary key always consists of a single column C. You can identify primary keys for table by examining sample data D. Some tables include more than one column that can be used as a primary key.
D. Some tables include more than one column that can be used as a primary key.
When you add a row for which you have incomplete information to a SQL table, ____. A. You must change the table structure so that all rows are marked as NOT NULL B. The DBMS will always reject the new row the first time you execute the command C. You use the INSERT command and enter a value of null for the columns without data D. You use the INSERT command and name the columns for which you are entering data.
D. You use the INSERT command and name the columns for which you are entering data.
The SALES_REP table consists of the sales representatives' IDs, first names, last names, addresses, mobile numbers, total commissions, and ____. A. total sales B. customers C. invoice numbers D. commission rates
D. commission rates
James converts a table with this notation: INVOICES (INVOICE_NUM, INVOICE_DATE, (ITEM_ID, QUANTITY)) into a table with this notation: INVOICES (INVOICE_NUM, INVOICE_DATE, ITEM_ID, QUANTITY) James has _____. A. converted a table in first normal form into a table in second normal form B. added a repeating group to a table C. made the table design worse D. converted an unnormalized relation into a table in first normal form
D. converted an unnormalized relation into a table in first normal form
At KimTay, the invoice total appears in which section of the invoice? A. body B. top C. heading D. footing
D. footing
The invoice line in a KimTay invoice contains a total, which is usually called a(n)___. A. trailer B. sum C. extension D. footing
D. footing
The KimTay database stores several items for each customer's invoice , including the ___. A. invoice total B. discount percentage C. line totals (extensions) D. invoice date
D. invoice date
Though they haven't yet achieved an e-commerce-based business status, in the future, StayWell would like to be able to use their database in conjunction with _____. A. advertisements in student - facing publications B. live administrators taking phone calls C. an overseas call center D. mobile apps and online booking systems.
D. mobile apps and online booking systems
When a SQL query is written so as to display the results in a specific order, this order is determines by the ___. A. WHERE clause B. primary key C. operator D. sort key
D. sort key
Suppose you have a table with a primary key composed of two columns. When another column in this table is dependent on only one of the two columns in the primary key, this poses a risk for _____. A. qualification errors B. function splitting C. functional dependencies D. update anomalies
D. update anomalies
To sort data in descending order, follow the name of the sort key with the ___ operator.
DESC
In the StayWell Student Accommodation database, the corresponding value for the CATEGORY_NUMBER associated with SERVICE_ID 2 is_____.
Electrical Systems
One can successfully design a database system without fully understanding the concept of functional dependence.
FALSE
The GROUP BY clause sorts the data in a particular order.
FALSE
All SELECT statements require a WHERE clause
False
To list all the columns in a table and their properties in MySQL, use the LIST COLUMNS command
False
A relation is in ____ normal form if no repeating groups exist in it.
First
If a table includes one or more columns that can be used as a primary key, both columns are referred to as ____ _____.
Functional Dependencies
The SQL command used to add rows to a table is ___.
INSERT
table and column names must start with a(n) _____.
Letter
In the StayWell Student Accommodation database, owner number ____ owns the property with ID 6.
MO100
To list data in a specific order, use the ______ ______ clause.
ORDER BY
To determine the total of all customer balances, use the _______ function.
SUM
A determinant is any column ( or collection of columns) that determines another table
TRUE
In SQL Server, you can execute the sp_columns command to list all the columns in a table.
TRUE
The StayWell database uses a combination of letters and numbers to identify each resident.
TRUE
An example of a relationship between categories in the KimTay database is the relationship between sales reps and customers
True
If column A in a relational database determines a single value for column B at any point in time, then column B is functionally dependent on column A.
True
It is possible to place one query inside another
True
It is possible to use both a WHERE clause and a HAVING clause in a SELECT statement
True
The SERVICE_CATEGORY table in the StayWell database includes the service fees.
True
The statement "A sales rep's pay class functionally determines his or her pay rate" means that if you know the pay class, you can determine the pay rate.
True
When adding rows to character columns, make sure you enclose the values in single quotation marks.
True
You can determine functional dependence by viewing sample data
True
A(n) _______ is a question represented in a way that the DBMS can understand.
query
A(n) ____ is an association between entities
relationship
If the primary key of a relation contains only a single column, then the relation is automatically in ______ normal form.
third