CSC 315
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?
%
Using Oracle Proprietary join syntax, which operator would you use after one of the column names in the WHERE clause when creating an outer join?
(+)
If you use the RR format when writing a query using the date 27-Oct-17 and the year is 2001, what year would be the result?
2017
What is the minimum number of join conditions required to join 5 tables together?
4
Which of the following are examples of logical operators that might be used in a WHERE clause. (Choose Two)
AND, OR, NOT
What types of joins will return the unmatched values from both tables in the join?
Full outer join
What language is used to query data in a Relational Database?
SQL
SELECT * FROM departments; is a:
Statement
You need to replace null values in the DEPT_ID column with the text N/A. Which functions should you use?
TO_CHAR and NVL
The basic storage structure in a Relational Database is a _________:
Table
CASE and DECODE evaluate expressions in a similar way to IF-THEN-ELSE logic. However, DECODE is specific to Oracle syntax. True or False?
True
Every time you shop online, it is likely you will be accessing a database. True or False?
True
Hierarchical queries can walk both Top-Down and Bottom-Up. True or False?
True
In a payroll system, it is desirable to have an entity called DAY with a holiday attribute when you want to track special holiday dates. True or False?
True
Modeling historical data is optional. True or False?
True
Most of the well know Internet search engines use databases to store data. True or False?
True
The conversion function TO_CHAR is a single row function. True or False?
True
Which of the following are types of SQL functions? (Choose two correct answers.) A. Multi-Row Functions B. Single-Row Functions C. Column-Row Functions D. Many-to-Many Functions
A B
How do you know when to use the different types of time in your design? A. It depends on the functional needs of the system. B. Always model time; you can take it out later if it is not needed. C. You would first determine the existence of the concept of time and map it against the Greenwich Mean Time. D. The rules are fixed and should be followed.
A It depends
Which of the following elements cannot be included in a WHERE clause?
A column alias
You need to join all the rows in the EMPLOYEES table to all the rows in the EMP_REFERENCE table. Which type of join should you create?
A cross join
You need to join two tables that have two columns with the same name, datatype, and precision. Which type of join would you create to join the tables on both of the columns?
A natural join
You need to join the EMPLOYEES table and the SCHEDULES table, but the two tables do not have any corresponding columns. Which type of join will you create?
A non-equijoin
You need to join the EMPLOYEE_HIST and EMPLOYEES tables. The EMPLOYEE_HIST table will be the first table in the FROM clause. All the matched and unmatched rows in the EMPLOYEES table need to be displayed. Which type of join will you use?
A right outer join
Identify all of the correct statements that complete this sentence: A primary key is: (Choose Three) A. Only one column that cannot be null B. A set of columns and keys in a single table that uniquely identifies each row in a single table (*) C. A single column that uniquely identifies each row in a table D. A set of columns that uniquely identifies each row in a table
A single column A set of columns
Which of the following can be added to a relationship?
An arc can be assigned
To visually represent exclusivity between two or more relationships in an ERD you would most likely use an ________.
Arc
When translating an arc relationship to a physical design, you must turn the arc relationships into foreign keys. What additional step must you take with the created foreign keys to ensure the exclusivity principle of arc relationships? (Assume that you are implementing an Exclusive Design) (Choose Two) A. Make all relationships mandatory B. Make all relationships optional C. Create an additional check constraint to verify that one foreign key is populated and the others are not D. All the above
B optional C Create
Which of the following WHERE clauses would not select the number 10? A. WHERE hours BETWEEN 10 AND 20 B. WHERE hours IN (8,9,10) C. WHERE hours <>10 D. WHERE hours <= 10
C
Why would this table name NOT work in an Oracle database? this_year_end+next_year A. Table names must begin with an alphabetic character. B.It is too long. C. The Plus sign + is not allowed in object names. D. None of the above.
C
Will the following statement return one row? SELECT MAX(salary), MIN(Salary), AVG(SALARY) FROM employees; A. Yes, it will return the highest salary from each employee. B. Yes, it will return the average salary from the employees table. C. Yes, it will return the highest salary, the lowest salary, and the average salary from all employees. D. No, it is illegal. You cannot use more than one multi-row function in a SELECT statement.
C
Which of the following is a valid reason for considering a Subtype Implementation? A. The common access paths for the supertypes are different. B. The resulting table will reside in a single database and be used by just ONE user. C. Business functionality, business rules, access paths, and frequency of access are all very different between the subtypes. D. Most of the relationships are at the supertype level.
C Business
Which of the following would be a logical constraint when modeling time for a country entity? A. If you are doing a system for France or Germany, you would need security clearance. B. Daily traffic patterns must be monitored to determine which countries are overcrowded. C. Countries may change their names and/or borders over a period of time. D. People have births and deaths in their countries that must be tracked by the system
C Countries
Which of the following statements are true for ERD's to enhance their readability. (Choose Two) A. You must ensure that you have every single entity--even if hundreds of them exist--on one single, big diagram. B. The crows feet (many ends) can point whichever way is the easiest to draw. C. Avoid crossing one relationship line with another. D. It is OK to break down a large ERD into subsets of the overall picture. By doing so, you end up with more than one ERD that, taken together, documents the entire system.
C D
Which of the following is a conditional expression used in SQL?
CASE
Which of the following General Functions will return the first non-null expression in the expression list?
COALESCE
Which of the following scenarios should be modeled so that historical data is kept? (Choose two) BABY and AGE CUSTOMER and ORDERS TEACHER and AGE CUSTOMER and PAYMENTS
CUSTOMER and ORDERS CUSTOMER and PAYMENTS
In a physical data model, an attribute becomes a _____________.
Column
The explanation below is an example of which constraint type? A column must contain only values consistent with the defined data format of the column
Column Integrity
When a relationship may or may not be transferable, depending on time, this is know as a/an:
Conditional Non-transferable Relationship
Every business has restrictions on which attribute values and which relationships are allowed. These are known as:
Constraints
In an Oracle database, why would the following table name not be allowed 'EMPLOYEE JOBS'? A. The database does not understand all capital letters B. JOBS is a reserved word C. EMPLOYEE is a reserved word D. You cannot have spaces between words in a table name
D
What is the function of logging or journaling in conceptual data models? A. Represents entities as time in the data model. B. Creates a fixed time for all events in a data model. C. Gives a timestamp to all entities. D. Allows you to track the history of attribute values, relationships, and/or entire entities
D
Which of the following is a logical constraint that could result from considering how time impacts an example of data storage? A. End Date must be before the Start Date. B. EMPLOYEE periods can overlap causing the database to crash. C. Dates must be stored with Time. D. An ASSIGNMENT may only refer to an EMPLOYEE with a valid employee record at the Start Date of the ASSIGNMENT.
D
In an Oracle database, why would 1_TABLE not work as a table name? A. The database does not understand all capital letters. B. TABLE is a reserved word. C. There is no problem here. You can create a table called 1_TABLE. D. Object names must not start with a number. They must begin with a letter.
D Object
Which of the following would best be represented by an arc?
DELIVERY ADDRESS (Home, Office)
What command can be used to show information about the structure of a table?
DESCRIBE
A command that suppresses duplicates
DISTINCT
You need to display only unique combinations of the LAST_NAME and MANAGER_ID columns in the EMPLOYEES table. Which keyword should you include in the SELECT clause?
DISTINCT
Which of the following is true? A. Date values are not format-sensitive B. Date values are enclosed in single quotation marks C. Character strings must be enclosed in double quotation marks D. Character values are not case-sensitive
Date values are enclosed in single quotation marks
The structure of the table can be displayed with the _________ command:
Desc and Describe
What does the DISTINCT keyword do when it is used in a SELECT clause?
Eliminates duplicate rows in the result
Arcs are used to visually represent _________ between two or more relationships in an ERD.
Exclusivity
In order to eliminate duplicate rows use the ________ keyword
FIRST_ONLY
Where in a SQL statement can you not use arithmetic operators?
FROM
A recursive relationship must be Mandatory at both ends. True or False?
False
A single relationship can be both Recursive and Hierarchical at the same time. True or False?
False
All systems must include functionality to provide logging or journaling in conceptual data models. True or False?
False
Arcs are Mandatory in Data modeling. All ERD's must have at least one Arc. True or False?
False
Character functions accept character arguments and only return character values. True or False?
False
Formal rules exist for drawing ERD's. You must always follow them, even if it results in an ERD that is difficult to read. True or False?
False
Hierarchical queries MUST use the LEVEL pseudo column. True or False?
False
If you write queries using the BETWEEN operator, it does not matter in what order you enter the values, i.e. BETWEEN low value AND high value will give the same result as BETWEEN high value and low value. True or False?
False
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 following is a valid outer join statement: SELECT c.country_name, d.department_name FROM countries c, departments d WHERE c.country_id (+) = d.country_id (+) True or False?
False
The following statement represents a multi-row function. True or False? SELECT UPPER(last_name) FROM employees;
False
The following statement returns 0 (zero). True or False? SELECT 121/NULL FROM dual;
False
There is only one kind of software used by all computers. True or False?
False
When an Arc is transformed to the physical model every relationship in the Arc becomes a mandatory Foreign Key. True or False?
False
When mapping supertypes, relationships at the supertype level transform as usual. Relationships at subtype level are implemented as foreign keys, but the foreign key columns all become mandatory. True or False?
False
When modeling historical data the unique identifier is always made up of a barred relationship from the original two entities. True or False?
False
You must make sure all entities of a proposed system can fit onto one diagram. It is not allowed to break up a data model into more than one diagram. True or False?
False
he function COUNT is a single row function. True or False?
False
What command can be used to create a new row in a table in the database?
INSERT
What command do you use to add rows to a table
INSERT
You need to display all the rows in the EMPLOYEES table that contain a null value in the DEPARTMENT_ID column. Which comparison operator should you use?
IS NULL
To resolve a many to many relationship in a physical model you create a(n) ___________________?
Intersection Table
Which of the following scenarios should be modeled so that historical data is kept? (Choose two) LIBRARY and BOOK STUDENT and GRADE STUDENT and AGE LIBRARY and NUMBER OF BOOKS
LIBRARY and BOOK STUDENT and GRADE
You want to retrieve a list of customers whose last names begin with the letters 'Fr' . Which keyword should you include in the WHERE clause of your SELECT statement to achieve the desired result?
LIKE
The Concatenation Operator does which of the following?
Links two or more columns or literals to form a single output column
Identify all of the incorrect statements that complete this sentence: A primary key is...(Choose three) A. Only one column that must be null. (*) B. A single column that uniquely identifies each column in a table. (*) C. One or more columns in a table that uniquely identifies each row in that table. D. A set of columns in one table that uniquely identifies each row in another table.
Only One A single A set
The ID column in the CLIENT table that corresponds to the CLIENT_ID column of the ORDER table contains null values for rows that need to be displayed. Which type of join should you use to display the data
Outer join
A Recursive Relationship is represented on an ERD by a/an:
Pig's Ear
A relationship between an entity and itself is called a/an:
Recursive Relationship
A barrred Relationship will result in a Foreign Key column that also is part of:
The Primary Key
You query the database with this SQL statement: SELECT * FROM students; Why would you use this statement?
To view data
A Hierarchical relationship is a series of relationships that reflect entities organized into successive levels. True or False?
True
A particular problem may be solved using either a Recursive Relationship or a Hierarchical Relationship, though not at the same time. True or False?
True
An arc can often be modeled as Supertype and Subtypes. True or False?
True
Arcs model an Exclusive OR constraint. True or False?
True
Business organizational charts are often modeled as a Hierarchical relationship. True or False?
True
No formal rules exist for drawing ERD's. The most important thing is to make sure that all entities, attributes, and relationships are documented on the diagram, and the diagram is clear and readable. True or False?
True
When using the "LIKE" operator, the % and _ symbols can be used to do a pattern-matching, wild card search. True or False?
True
Columns in a database table contain data with the same _________:
Type
The keywords JOIN _____________ should be used to join tables with the same column names but different datatypes
USING
The _______ clause can be added to a SELECT statement to return a subset of the data.
WHERE
To restrict the rows returned from an SQL Query, you should use the _____ clause
WHERE
You need to display all the values in the EMAIL column that contains the underscore (_) character as part of that email address. The WHERE clause in your SELECT statement contains the LIKE operator. What must you include in the LIKE operator? A. The ESCAPE option (\) B. A percent sign (%) C. The (+) operator D. The ESCAPE option (\) and one or more percent signs (%)
\ %
Column integrity refers to: A. Columns always containing positive numbers B. Columns always containing values consistent with the defined data format C. Columns always containing text data less than 255 characters D. Columns always having values
format
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?
||
