SQL Final
How can you write "not equal to" in the WHERE-clause?
!= ^= <>
The default keyword for group functions is ____.
ALL
If the DISTINCT keyword is not included in the STDDEV function, the ____ keyword will be assumed.
AVG SUM NVL
When you add the concept of time to your data model, you are:
Adding complexity to your model.
Which of the following is an example of a structural business rule?
All employees must belong to at least one department
If an intersection entity is formed that contains no attributes of its own, its uniqueness may be modeled by
Barring the relationships to the original entities.
How do you turn "data" into "information"?
By querying it or accessing it
Which of the following are examples of data vs. information:
- Student age vs. average age of all students in class - Bank deposit amount vs. total account balance - Price of a computer vs. total sales of all computers for a company
When you resolve a M-M by creating an intersection entity, this new entity will always inherit:
A relationship to each entity from the original M-M.
When you resolve a M:M by creating an intersection entity, this new entity will always inherit
A relationship to each entity from the original M:M.
The ____ function is based upon the concept of a normal distribution.
STDDEV
Which of the following would best be represented by an arc?
STUDENT ( University, Trade School)
Consider the recommended drawing conventions for ERD's. Indicate which of the following accurately describes diagramming conventions for entities and attributes:
The * means that an attribute is mandatory or required; the entity name should be singular
Why would you want to model a time component when designing a system that lets people buy bars of gold?
The price of gold fluctuates and, to determine the current price, you need to know the time of purchase
If the DISTINCT keyword is not included in the SUM function, the ____ keyword will be assumed.
UNIQUE RANDOM
Secondary UID's are
Useful as an alternative means of identifying instances of an entity
If you are tracking employment dates for an employee, do you need to have an "End Date" attribute?
Yes, if the company wants to track multiple start dates and insure that multiple employment dates for an employee do not overlap
The MAX function can be used with which type of columns?
all of the above
Which of the following describes a type of view that is based on a subquery that retrieves or derives data from one or more tables, and may also contain functions or grouped data?
complex view
Combining the contents of two or more columns is known as ____.
concatenation
he subquery used to create a(n) ____ view can contain an ORDER BY clause.
inline
When the WHERE clause contains multiple types of operators, which of the following is resolved last?
logical operators
An outer query is also referred to as a(n) ____ query.
parent query
Which of the following types of joins refers to joining a table to itself?
self-join
Which of the following types of views cannot contain grouped data?
simple
Which of the following types of views cannot include a group function?
simple view
The operators =, <, >, <=, >=, and <> are referred to as ____ operators.
single-row
he > operator is referred to as a(n) ____ operator.
single-row
n this simple diagram, what comprises the unique identifier for the student class entity?
student id and course id
A foreign key always refers to a primary key in the same table. True or False?
F
A single-row subquery can return several columns, but only one row, of results to the outer query.
F
An entity can be on 2nd Normal Form even if it has repeated values. True or False?
F
Entity names are always plural. True or False?
F
Generic models are generally less complex than a specific model. True or False?
F
Group functions return a group of results per row processed.
F
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. The Layout is insignificant. True or False?
F
Non-transferable relationships can only be mandatory, not optional. True or False?
F
Personal computers (PCs) have been in existence since 1950. True or False?
F
Relationships on an ERD can only be transformed into UIDs in the physical model? True or False?
F
Rows cannot be added to a table through a complex view that was created with the ORDER BY clause.
F
Single-row functions return one result per group of rows processed.
F
The SQL statement ALTER TABLE EMPLOYEES DROP COLUMN SALARY will delete all of the rows in the employees table. True or False?
F
The YR element can be included in a format argument of the TO_CHAR function to spell out the year of the specified date.
F
You can determine the number of characters in a column by using the COUNT function.
F
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
F
This diagram could also be expressed as a supertype/subtype construction. True or False?
False
The transformation from an ER diagram to a physical design involves changing terminology. Relationships in the ER diagram become __________ , and primary unique identifiers become ____________.
Foreign keys, Primary keys
If a SELECT statement contains HAVING, GROUP BY, and WHERE clauses, the ____ clause will be processed last.
HAVING
A business rule such as "All accounts must be paid in full within 10 days of billing" is best enforced by:
Hiring a programmer to create additional programming code to identify and report accounts past due
Why is it important to identify and document business rules?
It allows you to create your data model, then check for accuracy.
With the following data in Employees (last_name, commission_pct, manager_id) what is the result of the following statement? DATA: King, null, null Kochhar, null, 100 Vargas, null, 124 Zlotkey, .2, 100
King, -1 Kochhar, 100 Vargas, 124 Zlotkey, .2
All of the following would be instances of the entity PERSON except which?
Male
What is the default mode for the CREATE VIEW command?
NOFORCE
When sorting the results in ascending order, which of the following values will be presented last in the output?
NULL
If it is possible for a subquery to return a NULL value to the outer query for comparison, the ____ function should be used to substitute an actual value for the NULL.
NVL
Which of the following functions can be used to substitute another value for a NULL value during calculations?
NVL
Which of the following functions allows for different options, depending upon whether a NULL value exists?
NVL2
When data is stored in more than one place in a database, the database violates the rules of ___________.
Normalization
Modeling historical data produces efficient ways for a business to operate such as:
Providing valuable information via reports to management.
DML operations are not allowed on a view that includes the pseudo column ____.
ROWNUM
Based upon the contents of the BOOKS tables, which of the following SQL statements will return the number of different publishers represented in the table?
SELECT COUNT(DISTINCT pubid) FROM books;
Based on the contents of the BOOKS table, which of the following SQL statements will return the total profit generated by books provided by publisher 4?
SELECT SUM(retail-cost) FROM books WHERE pubid = 4;
Based on the contents of the CUSTOMERS table, which SQL statement will display the customers residing in the same state as customer#1013?
SELECT customer# FROM customers WHERE state = (SELECT state FROM customers WHERE customer#=1013);
Which of the following is a valid SQL statement to retrieve data from the ORDERS table?
SELECT order#, orderdate, shipdate FROM orders;
Which of the following format model elements can be used to spell out a number?
SP
With a MERGE statement, a series of ____ actions can occur with a single SQL statement.
DML
Parentheses can be used to override the order of operations in an arithmetic expression
T
Rows cannot be added to a table through a complex view that is based on a group function.
T
The AVG function only includes non-NULL values in its calculations.
T
The percent sign (%) and underscore (_) symbols can be used with the LIKE comparison operator to create a search pattern.
T
The purpose of an ERD is to document the proposed system and facilitate discussion and understanding of the requirements captured by the developer. True or False?
T
The ____ constraint ensures that any DML operations performed on the view (e.g., adding rows, changing data) will not prevent the row from being accessed by the view because it no longer meets the condition in the WHERE clause.
WITH CHECK OPTION
When all attributes are single-valued, the database model is said to conform to:
1st Normal Form
Relationships always exist between
2 entities (or one entity and itself)
What is used to indicate the end of an SQL statement?
;
Which of the following represents the correct sequence of steps in the Database Development Process?
Analyze, Design, Build
Many to many relationships between entities usually hide what? Mark for Review
Another entity
To visually represent exclusivity between two or more relationships in an ERD you would most likely use a/an ________.
Arc
Which of the following commands can be used to view the structure of a table?
DESCRIBE
Which of the following format argument elements indicates that the name of the day of the week for the specified date should be displayed?
DAY
Which of the following is true?
Date values are enclosed in single quotation marks
A non-transferable relationship is represented by which of the following symbols?
Diamond
Which of the following is a TRUE statement about the diagram below?
Every A is a B
Which of the following is true about supertypes and subtypes?
Subtypes inherit the relationships and attributes of the supertype (*)
The "Arc Implementation" is a synonym for what type of implementation?
Supertype and Subtype Implementation
A subtype can have a relationship not shared by the supertype. True or False?
T
A(n) UPDATE operation can be performed on a complex view that contains an arithmetic operation.
T
All instances of a subtype must be an instance of the supertype. True or False?
T
In Oracle11g, a MERGE statement compares data between two tables and can perform a series of DML actions to assist in synchronizing the data of the two tables.
T
Multiple-row subqueries are nested queries that can return more than one row of results to the parent query.
T
When are relationships unnecessary?
When you can derive the relationship from other relationships in the model
In a Cartesian join, linking a table that contains 10 rows to a table that contains 9 rows will result in ____ rows being displayed in the output
90
Which of the following is the best scenario for using supertype/subtype entities:
A vehicle dealership that sells boats on trailers, cars, and trucks
What is the rule of Second Normal Form?
All non-UID attributes must be dependent upon the entire UID.
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
A relationship can be both recursive and hierachal at the same time. True or False?
F
A unique identifier can only be made up of one attribute. True or False?
F
A well structured ERD will show only some parts of the finished data model. You should never try to model the entire system in one diagram, no matter how small the diagram might be. True or False?
F
All data models MUST include generic components in order to remain flexible. True or False?
F
An entity can only have one UID. True or False?
F
Audit trail attributes cannot be placed in the entities they are auditing, they must be placed in separate, new entities, created just for that purpose. True or False?
F
Choosing specific columns to be displayed by a SELECT statement is known as selection
F
Data Modeling is the last step in the database development process. True or False?
F
Each section of a SQL command that begins with a keyword is known as a statement.
F
Many-to-Many relationships are perfectly acceptable in a finished ERD. There is no need to do any more work on them. True or False?
F
No database in the world is ever truly on 3rd Normal Form. Everyone always stops after 2nd Normal Form. True or False?
F
Table aliases MUST be used with columns referenced in the JOIN USING clause. True or False?
F
The JOIN keyword is used in the WHERE clause to indicate the tables that should be joined or linked.
F
In a physical data model, a relationship is represented as a:
Foreign Key
How do you know when to use the different types of time in your design?
It depends on the functional needs of the system.
When resolving a M:M relationship, the new relationships will always be __________ on the many side.
Mandatory
What uncommon relationship is described by the statements: "Each LINE must consist of many POINTS and each POINT must be a part of many LINES"
Many to Many Mandatory
When sorting the results in descending order, which of the following values will be presented first in the output?
NULL
What are the three properties that every relationship should have?
Name, optionality, cardinality
Which of the following is the definition for Third Normal Form?
No non-UID attribute can be dependent on another non-UID attribute
Based upon the contents of the BOOKS table in the accompanying figure, which of the following queries will retrieve all books published in 2001?
SELECT * FROM books WHERE pubdate BETWEEN '01-JAN-01' AND '31-DEC-01'; SELECT * FROM books WHERE pubdate LIKE '%01'; SELECT * FROM books WHERE pubdate >= '01-JAN-01' AND pubdate <= '31-DEC-01';
Based upon the contents of the BOOKS table, which of the following SQL statements will display five rows in its results?
SELECT DISTINCT pubid FROM books;
Which of the following SQL statements will display the gift that should be sent to any customer who orders the book titled SHORTEST POEMS?
SELECT gift FROM promotion, books WHERE retail BETWEEN minretail AND maxretail AND title = 'SHORTEST POEMS';
Based upon the structure of the ORDERS table, which of the following is a valid SQL statement?
SELECT order#, orderdate DateOrdered FROM orders;
Which of the following SQL statements will display the title and cost of each book stored in the BOOKS table, as well as the name of the contact person and the phone number to call to reorder the book?
SELECT title, cost, contact, phone FROM publisher JOIN books USING (pubid);
A table alias is assigned in the FROM clause.
T
A table does not have to have a primary key. True or False?
T
A(n) non-equality join is used when there are no equivalent rows of data in the tables that are being joined.
T
Oracle was one of the first relational database systems available commercially. True or False?
T
Examine the following entity and decide which attribute breaks the 2nd Normal Form rule: ENTITY: CLASS ATTRIBUTES: CLASS ID DURATION SUBJECT TEACHER NAME AND ADDRESS
TEACHER NAME AND ADDRESS
The transformation from an ER diagram to a physical design involves changing terminology. Entities in the ER diagram become __________ , and attributes become ____________.
Tables, Columns
A barrred Relationship will result in a Foreign Key column that also is part of:
The Primary Key
If a relationship can be moved between instances of the entities it connects, it is said to be:
Transferable
The SORT BY clause is used for displaying the results of a query in a sorted order.
F
The SORT BY clause is used to present query results in a specific order.
F
The following entity is in 1st normal form: True or False? ENTITY: VEHICLE ATTRIBUTES: REGISTRATION MAKE MODEL COLOR DRIVER PASSENGER 1 PASSENGER 2 PASSENGER 3
F
The overall mission of the Oracle Corporation is to use the internet and fast processing servers to build its own network
F
Use MON in the format argument to spell out the specified month.
F
All attributes that are part of the UID are mandatory. True or False?
T
As a database designer, you have a responsibility to store data in only one place and in the best possible place. True or False?
T
Attributes are written inside the entity to which they belong. True or False?
T
Attributes become columns in a database table. True or False?
T
If subtypes are listed, a supertype should have at least two subtypes. True or False?
T
In a payroll system, it is desirable to have an entity called DAY with a holiday attribute when you want to track special holidays. True or False?
T
In an ERD, it is a good idea to group your entities according to the expected volumes. By grouping high volume entities together, the diagrams could become easier to read. True or False?
T
Modeling historical data can produce a unique identifier that includes a date. True or False?
T
Which of the following clauses is used to restrict the rows returned by a query?
WHERE
Changes in computing have affected many of our day-to-day activities. Are all of the following activities examples of this change? Yes or No?In the past you used to use the phone system to call directory assistance to get a phone number. Today you can use your PC to look up a phone number online.In the past you used to have to go to the shoe store to buy shoes. Today you can use your PC to order shoes online.In the past you had to use your PC to send a person an email. Today you can use your phone to send a text message.
YES
A(n) ____ is used to combine the results of two queries
set operator
