Database Design DE Semester 1 Final Exam
When translating an arc relationship to a physical design, you must turn the arc relationships into foreign keys. Assuming you are implementing an Exclusive Design, you must also create two Unique Key Constraints to ensure the Arc is implemented correctly. True or False?
False
Which clause would you include in a SELECT statement to sort the rows returned by the LAST_NAME column?
ORDER BY
You query the database with this SQL statement: SELECT * FROM students; Why would you use this statement? To insert data To view data To display the table structure To delete data
To view data
The SELECT statement retrieves information from the database. In a SELECT statement, you can do all of the following EXCEPT: Projection Manipulation Joining Selection
Manipulation
The explanation below is a column integrity constraint. True or False? A column must contain only values consistent with the defined data format of the column.
True
Which comparison condition means "Less Than or Equal To"? =) +< >= <=
<=
If a primary key is a set of columns, then one column must be null. True or False?
False
In the Analysis phase, the tables are created and populated with test data. True or False?
False
Which comparison operator searches for a specified character pattern?
LIKE
The Oracle Database can implement a many-to-many relationship. You simply create two foreign keys between the two tables. True or False?
False
The SQL statement ALTER TABLE EMPLOYEES DELETE COLUMN SALARY is a valid statement. True or False?
False
The explanation below is a User Defined integrity rule and must, therefore, be manually coded; the Database cannot enforce this rule automatically. True or False? A primary key must be unique, and no part of the primary key can be null.
False
Which symbol represents the not equal to condition? # +' != ~
!=
You want to retrieve a list of customers whose last names begin with the letters 'Fr' . Which symbol should you include in the WHERE clause of your SELECT statement to achieve the desired result?
%
The f_customers table contains the following data: ID Name Address City State Zip 1 Cole Bee 123 Main Street Orlando FL 32838 2 Zoe Twee 1009 Oliver Avenue Boston MA 02116 3 Sandra Lee 22 Main Street Tampa FL 32444 If you run the following statement: DELETE FROM F_CUSTOMERS WHERE ID <= 2; How many rows will be left in the table?
1
You query the database with this SQL statement: SELECT price FROM products WHERE price IN(1, 25, 50, 250) AND (price BETWEEN 25 AND 40 OR price > 50); Which two values could the statement return? (Choose two.) 1 50 25 10 250
25, 250
In a SELECT clause, what is the result of 2 + 3 * 2?
8
A primary key is Identify all of the incorrect statements that complete this sentence: A primary key is: (Choose three)A single column that uniquely identifies each column in a table. One or more columns in a table that uniquely identifies each row in that table. A set of columns in one table that uniquely identifies each row in another table. Only one column that must be null.
A single column that uniquely identifies each column in a table A set of columns in one table that uniquely identifies each row in another table Only one column that must be null
In the default order of precedence, which operator would be evaluated first? Subtractions and Additions are at the same level and would be evaluated first based on left to right order Multiplications and Divisions are at the same level and would be evaluated first based on left to right order. Additions and Multiplications are at the same level and would be evaluated first based on left to right order. Divisions and Subtractions are at the same level and would be evaluated first based on left to right order.
Additions and Multiplications are at the same level and would be evaluated first based on left to right order.
What would you use in the SELECT clause to return all the columns in the table?
An asterisk (*)
A table must have a primary key. True or False?
False
A table must have at least one candidate key, as well as its primary key. True or False?
False
Attributes become tables in a database. True or false?
False
You need to change the default sort order of the ORDER BY clause so that the data is displayed in reverse alphabetical order. Which keyword should you include in the ORDER BY clause?
DESC
What will be the result of the SELECT statement and what will display? SELECT last_name, salary, salary + 300 FROM employees; Display the last name, salary, and the results of adding 300 to each salary for all the employees Display the last name and salary of all employees who have a salary greater than 300. Modify the salary column by adding 300 and only display the last name and the new salary. Display the last name, salary, and the results of adding 300 to the salary of the first employee row
Display the last name, salary, and the results of adding 300 to each salary for all the employees
What does the DISTINCT keyword do when it is used in a SELECT clause? Hides NULL values Eliminates all unique values and compares values Eliminates duplicate rows in the result Eliminates only unique rows in the result
Eliminates duplicate rows in the result
You need to combine the FIRST_NAME and LAST_NAME columns in the EMPLOYEES table and display the columns as a combined character string. Which operator should you use?
I I
What command can be used to create a new row in a table in the database?
INSERT
To resolve a many to many relationship in a physical model you create a/an ___________________?
Intersection table
The Concatenation Operator does which of the following? Links rows of data together inside the database. Links two or more columns or literals to form a single output column Is represented by the asterisk (*) symbol Separates columns.
Links two or more columns or literals to form a single output column
Evaluate this SELECT statement: SELECT last_name, first_name, email FROM employees ORDER BY email; If the EMAIL column contains null values, which statement is true? Null email values will be displayed first in the result. Null email values will be displayed last in the result. Null email values will not be displayed in the result. The result will not be sorted.
Null email values will be displayed last in the result.
Which logical operator returns TRUE if either condition is true?
OR
The transformation from an ER diagram to a physical design involves changing terminology. Primary Unique Identifiers in the ER diagram become __________ and relationships become ____________.
Primary keys, Foreign keys
Evaluate this SQL statement: SELECT e.employee_id, e.last_name, e.first_name, m.manager_id FROM employees e, employees m ORDER BY e.last_name, e.first_name WHERE e.employee_id = m.manager_id; This statement fails when executed. Which change will correct the problem? Reorder the clauses in the query. Remove the table aliases in the WHERE clause. Remove the table aliases in the ORDER BY clause. Include a HAVING clause.
Reorder the clauses in the query.
What command will return data from the database to you?
SELECT
The PLAYERS table contains these columns: PLAYERS TABLE: LAST_NAME VARCHAR2 (20) FIRST_NAME VARCHAR2 (20) SALARY NUMBER(8,2) TEAM_ID NUMBER(4) MANAGER_ID NUMBER(9) POSITION_ID NUMBER(4) You want to display all players' names with position 6900 or greater. You want the players names to be displayed alphabetically by last name and then by first name. Which statement should you use to achieve the required results? SELECT last_name, first_name FROM players WHERE position_id >= 6900 ORDER BY last_name, first_name; SELECT last_name, first_name FROM players WHERE position_id > 6900 ORDER BY last_name, first_name; SELECT last_name, first_name FROM players WHERE position_id <= 6900 ORDER BY last_name, first_name; SELECT last_name, first_name FROM players WHERE position_id >= 6900 ORDER BY last_name DESC, first_name;
SELECT last_name, first_name FROM players WHERE position_id >= 6900 ORDER BY last_name, first_name;
The EMPLOYEES table includes these columns: EMPLOYEE_ID NUMBER(4) NOT NULL LAST_NAME VARCHAR2(15) NOT NULL FIRST_NAME VARCHAR2(10) NOT NULL HIRE_DATE DATE NOT NULL You want to produce a report that provides the last names, first names, and hire dates of those employees who were hired between March 1, 2000, and August 30, 2000. Which statements can you issue to accomplish this task? SELECT last_name, first_name, hire_date FROM employees WHERE hire_date BETWEEN '01-MAR-2000' AND '30-AUG-2000'; SELECT last_name, first_name, hire_date FROM employees WHERE hire_date BETWEEN '30-AUG-2000' AND '01-MAR-2000'; SELECT last_name, first_name, hire_date FROM employees GROUP BY hire_date >= '01-MAR-2000' and hire_date <= '30- AUG-2000'; SELECT last_name, first_name, hire_date FROM employees AND hire_date >= '01-MAR-2000' and hire_date <= '30-AUG-2000';
SELECT last_name, first_name, hire_date FROM employees WHERE hire_date BETWEEN '01-MAR-2000' AND '30-AUG-2000';
The PLAYERS table contains these columns: PLAYERS TABLE: LAST_NAME VARCHAR2 (20) FIRST_NAME VARCHAR2 (20) SALARY NUMBER(8,2) TEAM_ID NUMBER(4) MANAGER_ID NUMBER(9) POSITION_ID NUMBER(4) You must display the player name, team id, and salary for players whose salary is in the range from 25000 through 100000 and whose team id is in the range of 1200 through 1500. The results must be sorted by team id from lowest to highest and then further sorted by salary from highest to lowest. Which statement should you use to display the desired result? SELECT last_name, first_name, team_id, salary FROM players WHERE (salary > 25000 OR salary < 100000) AND team_id BETWEEN 1200 AND 1500 ORDER BY team_id, salary; SELECT last_name, first_name, team_id, salary FROM players WHERE salary BETWEEN 25000 AND 100000 AND team_id BETWEEN 1200 AND 1500 ORDER BY team_id, salary DESC; SELECT last_name, first_name, team_id, salary FROM players WHERE salary > 24999.99 AND salary < 100000 AND team_id BETWEEN 1200 AND 1500 ORDER BY team_id ASC, salary DESC; SELECT last_name, first_name, team_id, salary FROM players WHERE salary BETWEEN 24999.99 AND 100000.01 AND team_id BETWEEN 1200 AND 1500 ORDER BY team_id DESC, salary DESC;
SELECT last_name, first_name, team_id, salary FROM players WHERE salary BETWEEN 25000 AND 100000 AND team_id BETWEEN 1200 AND 1500 ORDER BY team_id, salary DESC;
Which SELECT statement should you use to limit the display of product information to those products with a price of less than 50? SELECT product_id, product_name FROM products WHERE price < 50; SELECT product_id, product_name FROM products HAVING price < 50; SELECT product_id, product_name FROM products WHERE price <= 50; SELECT product_id, product_name FROM products GROUP BY price < 50; SELECT product_id, product_name FROM products WHERE price < 50.00 GROUP BY price;
SELECT product_id, product_name FROM products WHERE price < 50;
The EMPLOYEES table contains these columns: SALARY NUMBER(7,2) BONUS NUMBER(7,2) COMMISSION_PCT NUMBER(2,2) All three columns contain values greater than zero. There is one row of data in the table and the values are as follows: Salary = 500, Bonus = 50, Commission_pct = .5 Evaluate these two SQL statements: 1. SELECT salary + bonus + commission_pct * salary - bonus AS income FROM employees; 2. SELECT (salary + bonus ) + commission_pct * (salary - bonus) income FROM employees; What will be the result? Statement 1 will return a higher value than statement 2. Statement 2 will return a higher value than statement 1. Statement 1 will display a different column heading.
Statement 2 will return a higher value than statement 1.
Which of the following are TRUE regarding the logical AND operator? TRUE AND TRUE return FALSE TRUE AND FALSE return TRUE FALSE AND TRUE return NULL TRUE AND FALSE return FALSE (*)
TRUE AND FALSE return FALSE
Evaluate this SELECT statement: SELECT * FROM employees WHERE salary > 30000 AND department_id = 10 OR email IS NOT NULL; Which statement is true? The OR condition will be evaluated before the AND condition. The AND condition will be evaluated before the OR condition. The OR and AND conditions have the same precedence and will be evaluated from left to right The OR and AND conditions have the same precedence and will be evaluated from right to left
The AND condition will be evaluated before the OR condition.
Evaluate this SELECT statement: SELECT last_name, first_name, salary FROM employees; How will the results of this query be sorted? The database will display the rows in whatever order it finds it in the database, so no particular order. The results will be sorted ascending by the LAST_NAME column only. The results will be sorted ascending by LAST_NAME and FIRST_NAME only. The results will be sorted ascending by LAST_NAME, FIRST_NAME, and SALARY.
The database will display the rows in whatever order it finds it in the database, so no particular order.
Evaluate this SELECT statement: SELECT last_name, first_name, salary FROM employees; How will the heading for the FIRST_NAME column appear in the display by default in Oracle Application Express? The heading will display with the first character capitalized and centered. The heading will display with the first character capitalized and left justified. The heading will display as uppercase and centered. The heading will display as uppercase and left justified.
The heading will display as uppercase and centered.
Which statement about the logical operators is true? The order of operator precedence is AND, OR, and NOT. The order of operator precedence is AND, NOT, and OR. The order of operator precedence is NOT, OR, and AND. The order of operator precedence is NOT, AND, and OR.
The order of operator precedence is NOT, AND, and OR.
During which phases of the System Development Life Cycle would you test the system before rolling it out to the users?
Transition and Production
The transformation from an ER diagram to a physical design involves changing terminology. Secondary Unique Identifiers become __________.
Unique constraints
The _______ clause can be added to a SELECT statement to return a subset of the data.
WHERE
Which clause would you include in a SELECT statement to restrict the data returned to only the employees in department 10?
WHERE
You want to determine the orders that have been placed by customers who reside in the city of Chicago. You write this partial SELECT statement: SELECT orderid, orderdate, total FROM orders; What should you include in your SELECT statement to achieve the desired results? AND city = Chicago; AND city = 'Chicago'; WHERE city = 'Chicago'; WHERE city = Chicago;
WHERE city = 'Chicago';
The EMPLOYEES table contains these columns: LAST_NAME VARCHAR2(25) FIRST_NAME VARCHAR2(25) EMAIL VARCHAR2(50) You are writing a SELECT statement to retrieve the names of employees that have an email address. SELECT last_name||', '||first_name "Employee Name" FROM employees; Which WHERE clause should you use to complete this statement?
WHERE email = NULL; WHERE email != NULL; WHERE email IS NULL; WHERE email IS NOT NULL;
