1. All instances of the subtypes must be an instance of the supertype. True or False? Mark for Review
(1) Points
True (*)
False
2. Which of the following is true about supertypes and subtypes? Mark for Review
(1) Points
Instances that belong to two subtypes of the same supertype may be modeled as a one-to-one relationship between the two subtypes
Subtypes inherit the relationships and attributes of the supertype (*)
Subtypes may have no more than 2 levels of nesting
Supertype and subtype entities must be mutually exclusive
3. When creating entities it is important to remember all of the following: (Choose Two) Mark for Review
(1) Points
(Choose all correct answers)
Create a formal description. (*)
Include attributes. (*)
Do not use synonyms.
Do use reserved words.
4. All instances of the supertype are also instances of one of the subtypes. True or False? Mark for Review
(1) Points
True (*)
False
5. Which of the following is a TRUE statement about the diagram below?
Mark for Review
(1) Points
Every Z is either an A or a B
Every B is a Z
Every A is a Z
Every A is a B (*)
6. The "Other" subtype is best used: Mark for Review
(1) Points
For instances that belong to the supertype and at least one other subtype.
For a subtype that does not have any of the same attributes as the supertype to which it belongs.
As an extra subtype to ensure that all instances of subtypes are mutually exclusive and complete. By having an "Other" subtype, all instances of the Supertype will be of one subtype type. (*)
You should never have a subtype called Other.
7. Which of the following is the best scenario for using supertype/subtype entities: Mark for Review
(1) Points
A pet store that sells small animals, because they each need different size cages and food.
An ice cream store that sells ice cream in sugar cones and regular cones.
A grocery store that gives customers a choice of plastic or paper bags.
A vehicle dealership that sells cars, trucks and boats on trailers. (*)
8. A subtype can have a relationship not shared by the supertype. True or False? Mark for Review
(1) Points
True (*)
False
9. A supertype should have at least two subtypes. True or False? Mark for Review
(1) Points
True (*)
False
10. Which of the following are valid formats for an attribute? (Choose Two) Mark for Review
(1) Points
(Choose all correct answers)
Character string (*)
Decimal
Number (*)
HEX
Publicat de Manolache Mihnea la 16:39 1 comentarii
Quiz: Documenting Business Rules
1. Why is it important to identify and document business rules? Mark for Review
(1) Points
It allows you to create your data model, then check for accuracy. (*)
It allows you to improve the client's business.
It ensures that the data model will automate all manual processes.
None of the above
2. A business rule such as "All accounts must be paid in full within 10 days of billing" is best enforced by: Mark for Review
(1) Points
Making the payment attribute mandatory.
Making the relationship between CUSTOMER and PAYMENT fully mandatory and 1:1 on both sides.
Creating a message to be printed on every bill that reminds the customer to pay within ten days.
Hiring a programmer to create additional programming code to identify and report accounts past due. (*)
3. How should you handle constraints that cannot be modeled on an ER diagram? Mark for Review
(1) Points
Always let the network architect handle them
List them on a separate document to be handled programmatically (*)
Explain them to the users so they can enforce them
All constraints must be modeled and shown on the ER diagram
4. Which of the following is an example of a structural business rule? Mark for Review
(1) Points
All employees must belong to at least one department. (*)
Buildings to be purchased by the business must be current with earthquake building code.
All overdue payments will have an added 10 % late fee.
All products will have a selling price no less than 30 % greater than wholesale.
5. Only managers can approve travel requests is an example of which of the following? Mark for Review
(1) Points
A structural business rule.
A mandatory business rule.
A procedural business rule. (*)
An optional business rule.
6. How would you model a business rule that states that on a studentﾒs birthday, they do not have to attend their classes? Mark for Review
(1) Points
Use a supertype
Use a subtype
Make the attribute Birthdate mandatory
You cannot model this. You need to document it (*)
7. Business rules are important to data modelers because: Mark for Review
(1) Points
A. They capture all of the needs, processes and required functionality of the business. (*)
B. They are easily implemented in the ERD diagram.
C. The data modeler must focus on structural rules, because they are easily represented diagrammatically and eliminate other rules that involve extra procedures or programming.
D. Both A and C are true.
Publicat de Manolache Mihnea la 16:38 0 comentarii
Quiz: Relationship Transferability
1. A non-transferable relationship is represented by which of the following symbols? Mark for Review
(1) Points
Heart
Diamond (*)
Circle
Triangle
2. Non-transferable relationships can only be mandatory, not optional. True or False? Mark for Review
(1) Points
True (*)
False
3. If a relationship can be moved between instances of the entities it connects, it is said to be: Mark for Review
(1) Points
Implicit
Transferrable (*)
Committed
Recursive
Publicat de Manolache Mihnea la 16:35 0 comentarii
Quiz: Relationship Types
1. 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" Mark for Review
(1) Points
One to Many Optional
One to Many Mandatory
Many to Many Optional
Many to Many Mandatory (*)
2. If the same relationship is represented twice in an Entity Relationship Model, it is said to be: Mark for Review
(1) Points
Replicated
Removable
Redundant (*)
Resourceful
3. When are relationships unnecessary? Mark for Review
(1) Points
When you can derive the relationship from other relationships in the model (*)
When they have the same visual structure but different meaning
When the information does not relate to the model
When the relationships connect 2 entities and they each have distinct meanings
4. Which of the following pairs of entities is most likely to be modeled as a M:M relationship? (Choose Two) Mark for Review
(1) Points
(Choose all correct answers)
STUDENT and CLASS (*)
TREE and SEEDLING
PHONE NUMBER and SIM CARD
CAR and DRIVER (*)
5. When resolving an M:M relationship, the new relationships will always be __________ on the many side. Mark for Review
(1) Points
optional
recursive
mandatory (*)
redundant
6. Which of the following are relationship types? (Choose Two) Mark for Review
(1) Points
(Choose all correct answers)
One to Some
Many to Many (*)
One to Many (*)
One to Another
7. Many to many relationships between entities usually hide what? Mark for Review
(1) Points
Another relationship
Another entity (*)
More attributes
Uniqueness
Publicat de Manolache Mihnea la 16:34 0 comentarii
Quiz: Resolving Many to Many Relationships
1. If an intersection entity is formed that contains no attributes of its own, its uniqueness may be modeled by Mark for Review
(1) Points
Creating new attributes.
Barring the relationships to the original entities. (*)
Placing the UID attributes from the original entities into the intersection entity.
None of the above.
2. When you resolve a M-M by creating an intersection entity, this new entity will always inherit: Mark for Review
(1) Points
The attributes of both related entities.
A relationship to each entity from the original M-M. (*)
The UID's from the entities in the original M-M.
Nothing is inherited from the original entities and relationship.
3. 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? Mark for Review
(1) Points
True
False (*)
Publicat de Manolache Mihnea la 16:33 0 comentarii
Quiz: Artificial, Composite and Secondary UIDs
1. A unique identifier can only be made up of one attribute. True or False? Mark for Review
(1) Points
True
False (*)
2. People are not born with "numbers", but a lot of systems assign student numbers, customer IDs, etc.ﾠA shoe has a color, a size, a style, but may not have a descriptive "number". So, to be able to uniquely and efficiently identify one instance of the entity SHOE, a/an ______________ UID can be created. Mark for Review
(1) Points
artificial (*)
unrealistic
structured
identification
3. A UID can be made up from the following: (Choose Two) Mark for Review
(1) Points
(Choose all correct answers)
Attributes (*)
Entities
Relationships (*)
Synonyms
4. An entity can only have one UID. True or False? Mark for Review
(1) Points
True
False (*)
Publicat de Manolache Mihnea la 16:31 0 comentarii
Quiz: Artificial, Composite and Secondary UIDs
1. A unique identifier can only be made up of one attribute. True or False? Mark for Review
(1) Points
True
False (*)
2. People are not born with "numbers", but a lot of systems assign student numbers, customer IDs, etc.ﾠA shoe has a color, a size, a style, but may not have a descriptive "number". So, to be able to uniquely and efficiently identify one instance of the entity SHOE, a/an ______________ UID can be created. Mark for Review
(1) Points
artificial (*)
unrealistic
structured
identification
3. A UID can be made up from the following: (Choose Two) Mark for Review
(1) Points
(Choose all correct answers)
Attributes (*)
Entities
Relationships (*)
Synonyms
4. An entity can only have one UID. True or False? Mark for Review
(1) Points
True
False (*)
Publicat de Manolache Mihnea la 16:31 0 comentarii
Quiz: Normalization and First Normal Form
1. When data is stored in more than one place in a database, the database violates the rules of ___________. Mark for Review
(1) Points
Normalization (*)
Replication
Normalcy
Decency
2. When all attributes are single-valued, the database model is said to conform to: Mark for Review
(1) Points
1st Normal Form (*)
2nd Normal Form
3rd Normal Form
4th Normal Form
3. An entity can have repeated values and still be in 1st Normal Form. True or False? Mark for Review
(1) Points
True
False (*)
4. The following entity is on 1st normal form: True or False?
ENTITY: VEHICLE
ATTRIBUTES:
REGISTRATION
MAKE
MODEL
COLOR
DRIVER
PASSENGER 1
PASSENGER 2
PASSENGER 3
Mark for Review
(1) Points
True
False (*)
Publicat de Manolache Mihnea la 16:30 0 comentarii
Quiz: Second Normal Form
1. What is the rule of Second Normal Form? Mark for Review
(1) Points
All non-UID attributes must be dependent upon the entire UID (*)
Some non-UID attributes can be dependent on the entire UID
No non-UID attributes can be dependent on any part of the UID
None of the Above
2. 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
Mark for Review
(1) Points
CLASS ID
DURATION
SUBJECT
TEACHER NAME AND ADDRESS (*)
3. All instances of the subtypes must be an instance of the supertype. Mark for Review
(1) Points
True (*)
False
4. Not all instances of the supertype are instances of one of the subtypes. Mark for Review
(1) Points
True
False (*)
5. A supertype should have at least two subtypes. Mark for Review
(1) Points
True (*)
False
6. An entity can be on 2nd Normal Form even if it has repeated values. True or False? Mark for Review
(1) Points
True
False (*)
Publicat de Manolache Mihnea la 16:29 0 comentarii
Quiz: Third Normal Form
1. As a database designer it is your job to store data in only one place and the best place. True or False? Mark for Review
(1) Points
True (*)
False
2. Examine the following Entity and decide which sets of attributes breaks the 3rd Normal Form rule: (Choose Two)
ENTITY: TRAIN (SYNONYM: ROLLING STOCK)
ATTRIBUTES:
TRAIN ID
MAKE
MODEL
DRIVER NAME
DEPARTURE STATION
NUMBER OF CARRIAGES
NUMBER OF SEATS
DATE OF MANUFACTURE
Mark for Review
(1) Points
(Choose all correct answers)
TRAIN ID, MAKE
DEPARTURE STATION, DRIVER NAME (*)
NUMBER OF CARRIAGES, NUMBER OF SEATS (*)
MODEL, DATE OF MANUFACTURE
3. No databases in the world is ever truly on 3rd Normal Form. Everyone always stops after 2nd Normal Form. True or False? Mark for Review
(1) Points
True
False (*)
Publicat de Manolache Mihnea la 16:28 0 comentarii
Quiz: Arcs
1. Which of the following can be added to a relationship? Mark for Review
(1) Points
an attribute
an arc can be assigned (*)
a composite attribute
an optional attribute can be created
2. Secondary UID's are Mark for Review
(1) Points
not permitted in data modeling
mandatory in data modeling
useful as an alternative means identifying instances of an entity (*)
always comprised of numbers
3. Which of the following would best be represented by an arc? Mark for Review
(1) Points
STUDENT (senior, junior)
STUDENT (graduating, non-graduating)
STUDENT (will-attend-university, will-not-attend-university)
STUDENT ( University, Trade School) (*)
4. If the entity CD has the attributes: #number, *title, *producer, *year, o store name, o store address, this entity is in 3rd Normal Form ("no non-UID attribute can be dependent on another non-UID attribute). True or False? Mark for Review
(1) Points
True
False (*)
5. Which of the following is the definition for Third Normal Form? Mark for Review
(1) Points
All attributes are single valued
An attribute must be dependent upon entity's entire unique identifier
No non-UID attribute can be dependent on another non-UID attribute (*)
All attributes are uniquely doubled and independent
6. To visually represent exclusivity between two or more relationships in an ERD you would most likely use an ________. Mark for Review
(1) Points
Arc (*)
UID
Subtype
Supertype
7. This diagram could also be expressed as a supertype/subtype construction. True or False?
Mark for Review
(1) Points
True
False (*)
8. All parts of a UID are mandatory. True or False? Mark for Review
(1) Points
True (*)
False
Publicat de Manolache Mihnea la 16:26 0 comentarii
Quiz: Hierarchies and Recursive Relationships
1. Which of the following would be a good Unique Identifier for its Entity? (Choose Two) Mark for Review
(1) Points
(Choose all correct answers)
Identification Number for Person (*)
Birthdate for Baby Which Includes Hour, Minute, and Seconds (*)
Order date for Order
Vehicle Type Number for Car
2. A relationship can be both recursive and hierachal at the same time. True or False? Mark for Review
(1) Points
True
False (*)
3. A recursive rationship should not be part of a UID. True or False? Mark for Review
(1) Points
True (*)
False
4. In this simple diagram, what comprises the unique identifier for the student class entity?
Mark for Review
(1) Points
student id and class id
student id, class id and course id
course id
student id and course id (*)
Publicat de Manolache Mihnea la 16:25 0 comentarii
Quiz: Modeling Historical Data
1. Historical data should always be kept. True or False? Mark for Review
(1) Points
True
False (*)
2. Modeling historical data can produce a unique identifier that includes a date. True or False? Mark for Review
(1) Points
True (*)
False
3. Which of the following scenarios should be modeled so that historical data is kept? (Choose two) Mark for Review
(1) Points
(Choose all correct answers)
LIBRARY and BOOK (*)
STUDENT and AGE
STUDENT and GRADE (*)
LIBRARY and NUMBER OF STAFF
4. 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? Mark for Review
(1) Points
True
False (*)
Publicat de Manolache Mihnea la 16:24 0 comentarii
Quiz: What is a Consultant
1. Only Consultants can develop new data models for a company, they are mandatory, so companies must find them and hire them. True or False? Mark for Review
(1) Points
True
False (*)
2. Which of the following skills are required for Consultants. (Choose Two) Mark for Review
(1) Points
(Choose all correct answers)
Communication skills (*)
Excellent drawing skills
Must be able to speak at least two languages fluently, preferably four or more
Team-working skills (*)
3. How does the dictionary define "consultant"? Mark for Review
(1) Points
A person who knows everything
One responsible for knowing everything
One who gives expert or professional advice (*)
None of the Above
Publicat de Manolache Mihnea la 16:22 0 comentarii
Quiz: Overcoming the Fear Factor
1. When you are involved in a group presentation, your group should practice before hand and agree on who presents the various parts. You should all be involved somehow. True or False? Mark for Review
(1) Points
True (*)
False
2. Your apperance at a presentation is not important, you should just show up and give the presentation in whatever clothes makes you comfortable. So feel free to wear Jeans and old T-Shirts etc. Being comfortable is more important than anything else. True or False? Mark for Review
(1) Points
True
False (*)
3. Which of the following is a valid technique for effective public speaking? Mark for Review
(1) Points
Making eye contact
Using familiar words when communicating technical information
Being enthusiastic
All of the Above (*)
Publicat de Manolache Mihnea la 16:21 0 comentarii
Quiz: Modeling Change: Time
1. When you add the concept of time to your data model, you are: Mark for Review
(1) Points
Simplifying your model.
Adding complexity to your model. (*)
Just changing the model, but this does not change the complexity of it.
None of the above.
2. It is desirable to have an entity called DAY with a holiday attribute when you want to track special holidays in a payroll system. True or False? Mark for Review
(1) Points
True (*)
False
3. What is the benefit to the users of a system that includes "time," e.g. Start Date and End Date for Employees? Mark for Review
(1) Points
Increased usability and flexibility of a system; we can the trace e.g. the different managers an employee had over time. (*)
System becomes 100% unstable; allows users to log on and log off at will.
Users are able to create complex programs in support of this component.
Reporting becomes nearly impossible, users enjoy this.
4. How do you know when to use the different types of time in your design? Mark for Review
(1) Points
The rules are fixed and should be followed
It depends on the functional needs of the system (*)
You would first determine the existence of the concept of time and map it against the Greenwich Mean Time
Always model time, you can take it out later if it is not needed
5. Which of the following would be a logical constraint when modeling time for a country entity? Mark for Review
(1) Points
People have births and deaths in their countries that must be tracked by the system.
If you are doing a system for France or Germany, you would need security clearance.
Countries may need an end date in your system, because they can change fundamentally over time, e.g. Yugoslavia. (*)
You need a constant record of countries, because they are still countries, even if leadership changes over time, e.g. France, USA and most other countries.
6. Modeling historical data produces efficient ways for a business to operate such as: Mark for Review
(1) Points
Modeling historical data does not help a business.
Providing valuable information via reports to management . (*)
Keeping track of holiday dates.
Employees can work in two time zones.
7. If you are tracking employment dates for an employee, do you need to have an "End Date" attribute? Mark for Review
(1) Points
Yes, because you always need an end date when you have a start date
No, because an end date is usually redundant
Yes, if the company wants to track employee information, like multiple start and end dates (*)
No, not if the company likes the employee
Publicat de Manolache Mihnea la 16:20 0 comentarii
Quiz: Modeling Change: Price
1. Why would you want to model a time component when designing a system that lets people buy bars of gold? Mark for Review
(1) Points
The price of gold fluctuates and for determining price, you need to know the time of purchase (*)
To allow the sales people to determine where the gold is coming from
You would not want to model this, it is not important
The Government of your country might want to be notified of this transaction.
2. Which of the following is a logical constraint that could result from considering how time impacts an example of data storage? Mark for Review
(1) Points
End Date must be before the Start Date.
ASSIGNMENT periods can overlap causing the database to crash.
An ASSIGNMENT may only refer to a COUNTRY that is valid at the Start Date of the ASSIGNMENT. (*)
Dates can be valued only with Time.
3. What is the function of logging or journaling in conceptual data models? Mark for Review
(1) Points
Allows you to track the history of attribute values, relationships and/or entire entities (*)
Gives a timestamp to all entities
Represents entities as time in the data model
Creates a fixed time for all events in a data model
4. You are doing a data model for a computer sales company, where the price goes down on a regular basis. If you want to allow them to modify the price and keep track of the changes, what is the best way to model this? Mark for Review
(1) Points
A. Create a product entity and a related price entity with start and end dates, and then let the users enter the new price whenever required.
B. Create a new item and a new price every day.
C. Use a price entity with a start and end date
D. Allow them to delete the item and enter a new one.
E. Both A and C (*)
Publicat de Manolache Mihnea la 16:18 0 comentarii
Quiz: Drawing Conventions for Readability
1. 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?
True
False (*)
2. It is a good idea to group your entities in a diagram according to the expected volumes. By grouping high volume entities together, the diagrams could become easier to read. True or False?
True (*)
False
3. Which of the following statements are true for ERD’s to enhance their readability. (Choose Two)
(Choose all correct answers)
There should be no crossing lines. (*)
All crows feet (Many-ends) of relationships should point the same way. (*)
There should be many crossing lines.
It does not matter which way the crows feet (many ends) point.
4. There are no formal rules for how to draw ERD’s. The most important thing is to make sure all entities, attributes and relationships are documented on diagram. The layout is not significant. True or False?
True
False (*)
Publicat de Manolache Mihnea la 16:18 0 comentarii
Quiz: Introduction to Relational Database Concepts
1. One or more columns in a primary key can be null. True or False?
True
False (*)
2. Foreign keys cannot be null when:
It is part of a primary key. (*)
It refers to another table.
It contains three or more columns.
3. A foreign key always refers to a primary key in the same table. True or False?
True
False (*)
4. The explanation below defines which constraint type:
A primary key must be unique, and no part of the primary key can be null.
Entity integrity. (*)
Referential integrity.
Column integrity.
User-defined integrity.
5. The explanation below defines which constraint type:
A column must contain only values consistent with the defined data format of the column.
Entity integrity.
Referential integrity.
Column integrity. (*)
User-defined integrity.
6. Column integrity refers to:
Columns always having values.
Columns always containing positive numbers.
Columns always containing values consistent with the defined data format. (*)
Columns always containing text data less than 255 characters.
7. The explanation below is an example of what constraint type:
The value in the dept_no column of the EMPLOYEES table must match a value in the dept_no column in the DEPARTMENTS table.
Entity integrity.
Referential integrity. (*)
Column integrity.
User-defined integrity.
8. Identify all of the correct statements that complete this sentence: A primary key is: (Choose Three)
A single column that uniquely identifies each row in a table. (*)
A set of columns that uniquely identifies each row in a table. (*)
A set of columns and keys in a single table that uniquely identifies each row in a single table. (*)
Only one column that cannot be null.
9. The explanation below is an example of what constraint type:
If the value in the balance column of the ACCOUNTS table is below 100, we must send a letter to the account owner which will require extra programming to enforce.
Entity integrity.
Referential integrity.
Column integrity.
User-defined integrity. (*)
10. A table does not have to have a primary key. True or False?
True (*)
False
Publicat de Manolache Mihnea la 16:16 0 comentarii
Quiz: Generic Modeling
1. Generic models are generally less complex than a specific model. True or False?
True
False (*)
2. All data models MUST have some portions of the model modeled as a generic component. True or False?
True
False (*)
3. When you transform a specific model to be generic, which of the following statements are true? (Choose Two)
You tend to end up with fewer entities in the generic model than you had in the specific model. (*)
Either all or none of the original attributes make it into the generic model. (*)
You will always have more entities in a generic model than in the corresponding specific model.
None of the original specific model attributes are allowed in a generic model.
Publicat de Manolache Mihnea la 16:15 0 comentarii
Quiz: Relationship Mapping
1. Two entities A and B have an optional (A) to Mandatory (B) One-to-One relationship. When they are transformed, the Foreign Key(s) is placed on:
The table BS (*)
The Table AS
Nowhere, One-to-One are not transformed
Both tables As and Bs get a new column and a Foreign Key.
2. Relationships on an ERD can only be transformed into UIDs in the physical model? True or False?
True
False (*)
3. What do you create when you transform a many to many relationship from your ER diagram into a physical design?
Foreign key constraints
Intersection entity
Intersection table (*)
Primary key constraints
4. One-to-One relationships are transformed into Foreign Keys in the tables created at either end of that relationship? True or False?
True
False (*)
5. A barrred Relationship will result in a Foreign Key column that also is part of:
The Table Name
The Column Name
The Check Constraint
The Primary Key (*)
6. One-to-Many Optional to Mandatory becomes a _______________ on the Master table.
Mandatory Foreign Key
Nothing (There are no new columns created on the Master table) (*)
Optional Foreign Key
Primary Key
Publicat de Manolache Mihnea la 16:15 0 comentarii
Quiz: Basic Mapping: The Transformation Process
1. In a physical data model, a relationship is represented as a:
Column
Primary Key
Unique Identifier
Foreign Key (*)
2. Why would this table name NOT work in an Oracle database?
2007_EMPLOYEES
Numbers cannot be incorporated into table names
Table names must start with an alphabetic character (*)
Underscores “_” are not allowed in table names
None of the above
3. 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 (*)
Primary keys, foreign keys
Foreign keys, mandatory business rules
Foreign keys, optional business rules
4. The transformation from an ER diagram to a physical design involves changing terminology. Entities in the ER diagram become __________ , and attributes become ____________.
Columns, Tables
Tables, Columns (*)
Foreign Keys, Columns
Tables, Foreign Keys
5. In a physical data model, an entity becomes a _____________.
Attribute
Table (*)
Constraint
Column
6. Attributes become columns in a database table. True or False?
True (*)
False
7. In an Oracle database, why would the following table name not be allowed ‘EMPLOYEE JOBS’?
The database does not understand all capital letters
EMPLOYEE is a reserved word
JOBS is a reserved word
You cannot have spaces between words in a table name (*)
Publicat de Manolache Mihnea la 16:14 0 comentarii
Quiz: SQL Introduction: Querying the Database
1. What command can be added to a select statement to return a subset of the data?
WHERE (*)
WHEN
ALL
EVERYONE
2. What command retrieves data from the database?
ALTER
SELECT (*)
DESCRIBE
INSERT
3. What command do you use to add rows to a table
INSERT (*)
ADD
ADD_ROW
NEW_ROW
4. What command can be used to show information about the structure of a table?
ALTER
SELECT
DESCRIBE (*)
INSERT
5. Examine the follolowing SELECT statement.
SELECT *
FROM employees;
This statement will retrieve all the rows in the employees table. True or False?
True (*)
False
Publicat de Manolache Mihnea la 16:13 0 comentarii
Quiz: Subtype Mapping
1. Which of the following are reasons you should consider when using a Subtype Implementation? (Choose Two)
When the common access paths for the subtypes are similar.
When the common access paths for the subtypes are different. (*)
Business functionality and business rules are similar between subtypes.
Most of the relationships are at the subtype level (*)
2. 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 optional. True or False?
True (*)
False
3. 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)
Make all relationships mandatory
Make all relationships optional (*)
Create an additional check constraint to verify that one foreign key is populated and the others are not (*)
All the above
4. The “Arc Implementation” is a synonym for what type of implementation?
Supertype Implementation
Subtype Implementation
Cascade Implementation
Supertype and Subtype Implementation (*)
Publicat de Manolache Mihnea la 16:12 0 comentarii
Quiz: Basic Table Modifications
1. The SQL statement ALTER TABLE EMPLOYEES DROP COLUMN SALARY will delete all of the rows in the employees table. True or False?
True
False (*)
2. What will the following statement do to the employee table?
ALTER TABLE employees ADD (gender VARCHAR2(1))
Add a new row to the EMPLOYEES table
Rename a column in the EMPLOYEES table
Change the datatype of the GENDER column
Add a new column called GENDER to the EMPLOYEES table (*)
3. 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 STATE=’FL’;
how many rows will be left in the table?
0
1 (*)
2
3
Publicat de Manolache Mihnea la 16:12 0 comentarii
Quiz: Anatomy of a SQL Statement
1. The SQL SELECT statement is capable of:
Selection and protection
Selection and projection (*)
Projection and updating
None of the above
2. If you want to see just a subset of the columns in a table, you use what symbol?
&
%
*
None of the above, instead of using a symbol you name the columns you want to see the data for. (*)
3. The order of operator precedence is
/ + – *
* – + /
* / + – (*)
None of the above
4. If you want to see all columns of data in a table, you use what symbol?
&
%
$
* (*)
5. SELECT * FROM departments; is a:
Keyword
Statement (*)
Declaration
Strategy
6. What is a NULL value?
A perfect zero
A known value less than zero
A blank space
An unknown value (*)
Publicat de Manolache Mihnea la 16:10 0 comentarii
Quiz: System Development Life Cycle
1. In which phases of the System Development Life Cycle will we need to use SQL as a language? (Choose Two)
Analysis
Transition (*)
Strategy
Build and Document (*)
2. The data model can be used to…
Communicate and group
Describe and specify
Analyze and copy
All of the Above (*)
3. During which phases of the System Development Life Cycle would you roll out the system to the users?
Build and Transition
Strategy and Analysis
Design and Production
Transition and Production (*)
Publicat de Manolache Mihnea la 16:09 0 comentarii
Quiz: Relational Database Technology
1. The following statements are true regarding tables in a RDBMS: (Choose Two)
A table is a logical object only. They cannot be created in a RDBMS.
A table holds all the data necessary about something in the real world, such as employees, invoices or customers. (*)
Tables contain fields, which can be found at the intersection of a row and a column. (*)
It is not possible to relate multiple tables within an RDBMS.
2. RDBMS stands for
Relational database manipulation system.
Relational database management system. (*)
Relational database mutilation system.
Relational database management style.
3. Once data has been created in a RDBMS, the ony way of getting it out again is by writing a Java or C program. No other languages can be used to access that data. True or False?
True
False (*)
4. The following table creation statement is valid. True or False?
CREATE TABLE country (
ID NUMBER(6) NOT NULL,
NAME VARCHAR2(30) NOT NULL,
LOC VARCHAR2(40),
REG_ID NUMBER,
NAME VARCHAR2(25))
True
False (*)
Publicat de Manolache Mihnea la 16:09 0 comentarii
Quiz: Limit Rows
1. Which example would limit the number of rows returned?
SELECT title FROM d_songs WHEN type_code = 88;
SELECT title FROM d_songs WHERE type_code = = 88;
SELECT title FROM d_songs WHERE type_code = 88; (*)
SELECT title FROM d_songs WHEN type_code = = 88;
2. Which of the following would be returned by this SELECT statement:
SELECT last_name, salary
FROM employees
WHERE salary <>
LAST_NAME SALARY
King 5000
LAST_NAME SALARY
Rajas 3500
LAST_NAME SALARY
Davies 3100
(*)
All of the above
3. To restrict the rows returned from an SQL Query, you should use the _____ clause:
SELECT
WHERE (*)
GROUP BY
CONDITION
All of the above
4. Which of the following statements will work?
SELECT first_name ||’ ‘||last_name NAME, department_id DEPARTMENT, salary*12 “ANNUAL SALARY”
FROM employees
WHERE name = ‘King’;
SELECT first_name ||’ ‘||last_name NAME, department_id DEPARTMENT, salary*12 “ANNUAL SALARY”
FROM employees
WHERE last_name = ‘King’;
(*)
SELECT first_name ||’ ‘||last_name NAME, department_id DEPARTMENT, salary*12 ‘ANNUAL SALARY’
FROM employees
WHERE last_name = ‘King’;
SELECT first_name ||’ ‘||last_name NAME, department_id DEPARTMENT, salary*12 ‘ANNUAL SALARY’
FROM employees
WHERE name = ‘King’;
5. Which query would give the following result?
LAST_NAME FIRST_NAME DEPARTMENT_ID
King Steven 90
SELECT last_name, first_name, department_id
FROM employees C
WHERE last_name = ‘KING’;
SELECT last_name, first_name, department_id
FROM employees
WHERE last_name = ‘King’;
(*)
SELECT last_name, first_name, department_id
FROM employees
WHERE last_name LIKE ‘k%’;
SELECT last_name, first_name, department_id
FROM employees
WHERE last_name LIKE ‘KING’;
6. Which of the following are true? (Choose Two)
Character strings are enclosed in double quotation marks
Date values are enclosed in single quotation marks (*)
Character values are not case-sensitive
Date values are format-sensitive (*)
7. How can you write not equal to in the WHERE-clause
!=
^=
<>
All of the above (*)
Publicat de Manolache Mihnea la 16:08 0 comentarii
Quiz: Working with Columns, Characters, and Rows
1. The following is a valid SQL SELECT statement. True or False?
SELECT first_name || ‘ ‘ || last_name alias AS Employee_Name
FROM employees:
True
False (*)
2. In order to eliminate duplicate rows use the ________ keyword
FIRST_ONLY
DISTINCT (*)
SINGLES_ONLY
EXCLUSIVE
3. The structure of the table can be displayed with the _________ command:
Desc
Describe
Dis
A and B (*)
4. Which of the following is NOT BEING DONE in this SQL statement?
SELECT first_name || ‘ ‘ || last_name “Name”
FROM employees;
Concatenating first name, middle name and last name (*)
Putting a space between first name and last name
Selecting columns from the employees table
Using a column alias
5. The concatenation operator …
Brings together columns or character strings into other columns
Creates a resultant column that is a character expression
Is represented by two vertical bars ( || )
All of the above (*)
Publicat de Manolache Mihnea la 16:07 0 comentarii
Quiz: Comparison Operators
1. Which of the following are examples of comparison operators used in the WHERE clause?
=, >, <, <=, >=, <>
between ___ and ___
in (..,..,.. )
like
is null
All of the above (*)
2. When using the “LIKE” operator, the % and _ symbols can be used to do a pattern-matching, wild card search. True or False?
True (*)
False
3. Which of the following WHERE clauses would not select the number 10?
WHERE hours BETWEEN 10 AND 20
WHERE hours <= 10
WHERE hours <>10 (*)
WHERE hours IN (8,9,10)
4. Which statement would select salaries that are greater than or equal to 2500 and less than or equal to 3500? Choose two correct answers.
WHERE salary >= 2500 AND salary <= 3500 (*)
WHERE salary <=2500 AND salary >= 3500
WHERE salary BETWEEN 2500 AND 3500 (*)
WHERE salary BETWEEN 3500 AND 2500
Publicat de Manolache Mihnea la 16:06 0 comentarii
Quiz: Logical Comparisons and Precedence Rules
1. Which of the following are examples of logical operators that might be used in a WHERE clause. (Choose Two)
AND, OR (*)
> <, =, <=, >=, <>
NOT IN (*)
LIKES
None of the above
2. What will be the results of the following selection?
SELECT *
FROM employees
WHERE last_name NOT LIKE ‘A%’ AND last_name NOT LIKE ‘B%’
All last names that begin with A or B
All last names that do not begin with A or B (*)
No rows will be returned. There is a syntax error
All rows will be returned
3. Which of the following would be returned by this SQL statement:
SELECT First_name, last_name, department_id
FROM employees
WHERE department_id IN(50,80)
AND first_name LIKE ‘C%’
OR last_name LIKE ‘%s%’
FIRST_NAME LAST_NAME DEPARTMENT_ID
Shelly Higgins 110
FIRST_NAME LAST_NAME DEPARTMENT_ID
Curtis Davies 50
FIRST_NAME LAST_NAME DEPARTMENT_ID
Randall Matos 50
FIRST_NAME LAST_NAME DEPARTMENT_ID
Michael Hartstein 20
All of the above (*)
4. Which of the following is earliest in the rules of precedence?
Concatenation operator
Logical condition
Comparison condition
Arithmetic operator (*)
5. Find the clause that will give the same results as:
SELECT *
FROM d_cds
WHERE cd_number NOT IN(90, 91, 92);
WHERE cd_id <=90 and cd_id >=92;
WHERE cd_id NOT LIKE (90, 91, 92);
WHERE cd_id != 90 and cd_id != 91 and cd_id != 92; (*)
WHERE cd_id != 90 or cd_id != 91 or cd_id!= 92;
6. Which logical operators in the WHERE clause means “Not Equal To”? (Choose Two)
NOT IN (…) (*)
=+
<> (*)
><
7. Which of the following statements best describes the rules of precedence when using SQL?
The order in which the columns are displayed
The order in which the expressions are sorted
The order in which the operators are returned
The order in which the expressions are evaluated and calculated (*)
All of the above
Publicat de Manolache Mihnea la 16:05 0 comentarii
Quiz: Sorting Rows
1. What columns can be added to the following SELECT statement in its ORDER BY clause? (Choose Three)
SELECT first_name, last_name, salary, hire_date
FROM employees
WHERE department_id = 50
ORDER BY ?????;
last_name, first_name. (*)
All columns in the EMPLOYEES table. (*)
The table name, EMPLOYEES, which would then automatically sort by all columns in the table.
Any column in the EMPLOYEES table, any expression in the SELECT list or any ALIAS in the SELECT list. (*)
All the columns in the database.
2. Which of the following is true of the ORDER BY clause: (Choose Two)
Must be the last clause of the SQL statement (*)
Displays the fetched rows in no particular order
Defaults to a descending order (DESC)
Defaults to an ascending order (ASC) (*)
3. What clause must you place in a SQL statement to have your results sorted from highest to lowest salary?
ORDER BY salary ASC
ORDER BY salary DESC (*)
ORDER salary BY DESC
None, the database always sorts from highest to lowest on the salary column.
4. A column alias can be specified in an ORDER BY Clause. True or False?
True (*)
False
Publicat de Manolache Mihnea la 16:02 0 comentarii
Quiz: Introduction to Functions
1. The following statement represents a multi-row function. True or False?
SELECT MAX(salary)
FROM employees
True (*)
False
2. The conversion function TO_CHAR is a single row function. True or False?
True (*)
False
3. Will the following statement return one row?
SELECT MAX(salary), MIN(Salary), AVG(SALARY)
FROM employees;
No, it is illegal. You cannot use more than one multi-row function in a SELECT statement
Yes, it will return the highest salary, the lowest salary and the average salary from all employees (*)
Yes, it will return the highest salary from each employee
Yes, it will return the average salary from the employees table.
4. The following statement represents a multi-row function. True or False?
SELECT UPPER(last_name)
FROM employees;
True
False (*)
5. The function COUNT is a single row function. True or False?
True
False (*)
Publicat de Manolache Mihnea la 15:59 0 comentarii
Quiz: Case and Character Manipulation
1. Which of the following SQL statements would correctly return a song title identified in the database as "All These Years"? Mark for Review
(1) Points
WHERE title CONTAINS 'Years';
WHERE title LIKE LOWER('all these years');
WHERE title IN('All','These','Years');
WHERE title LIKE INITCAP('%all these years'); (*)
Correct Correct
2. Which character manipulation function always returns a numerical value? Mark for Review
(1) Points
TRIM
LPAD
LENGTH (*)
SUBSTR
Correct Correct
3. Identify the output from the following SQL statement:
SELECT RPAD('SQL',6, '*')
FROM DUAL;
Mark for Review
(1) Points
******SQL
***SQL
SQL*** (*)
SQL******
Correct Correct
4. Which query selects the first names of the DJ On Demand clients who have a first name beginning with "A"? Mark for Review
(1) Points
SELECT UPPER(first_name)
FROM d_clients
WHERE first_name LIKE %a%
SELECT UPPER(first_name)
FROM d_clients v
WHERE first_name LIKE '%a%'
SELECT UPPER(first_name)
FROM d_clients
WHERE first_name LIKE 'a%'
SELECT UPPER(first_name)
FROM d_clients
WHERE LOWER(first_name) LIKE 'a%'
(*)
Correct Correct
5. Character functions accept character arguments and only return character values. True or False? Mark for Review
(1) Points
True
False (*)
Correct Correct
6. What does the following SQL SELECT statement return?
SELECT UPPER( SUBSTR('Database Programming', INSTR('Database Programming','P'),20))
FROM dual;
Mark for Review
(1) Points
Programming
PROGRAMMING (*)
Database
DATABASE
Correct Correct
7. Which query would return a user password combining the ID of an employee and the first 4 digits of the last name? Mark for Review
(1) Points
SELECT CONCAT (employee_id, SUBSTR(last_name,4,1))
AS "User Passwords"
FROM employees
SELECT CONCAT (employee_id, INSTR(last_name,4,1))
AS "User Passwords"
FROM employees
SELECT CONCAT (employee_id, INSTR(last_name,1,4))
AS "User Passwords"
FROM employees
SELECT CONCAT (employee_id, SUBSTR(last_name,1,4))
AS "User Passwords"
FROM employees
(*)
Correct Correct
8. Which of the following are types of SQL functions? (Choose two correct answers.) Mark for Review
(1) Points
(Choose all correct answers)
Multi-Row Functions (*)
Column-Row Functions
Single-Row Functions (*)
Many-to-Many Functions
Correct Correct
9. Single row functions may be used in ______, _______ and _______ clauses. (Choose two correct answers.) Mark for Review
(1) Points
(Choose all correct answers)
SELECT, FROM, ALWAYS
FROM, SELECT, ORDERS
WHERE, DECODE, ORDER BY (*)
SELECT, WHERE, ORDER BY (*)
Correct Correct
Publicat de Manolache Mihnea la 15:56 0 comentarii
Quiz: Number Functions
1. What is the result of the following SQL Statement:
SELECT ROUND(45.923,-1)
FROM DUAL;
Mark for Review
(1) Points
46
45.9
50 (*)
None of the above
Correct Correct
2. The answer to the following script is 456. True or False?
SELECT TRUNC(ROUND(456.98))
FROM dual
Mark for Review
(1) Points
True
False (*)
Correct Correct
3. ROUND and TRUNC functions can be used with which of the following Datatypes? Mark for Review
(1) Points
Dates and numbers (*)
Dates and characters
Numbers and characters
None of the above
Correct Correct
4. Which number function may be used to determine if a value is odd or even? Mark for Review
(1) Points
MOD (*)
TRUNC
ROUND
BINARY
Correct Correct
Publicat de Manolache Mihnea la 15:56 0 comentarii
Quiz: Date Functions
1. What is the result of the following query?
SELECT ADD_MONTHS ('11-JAN-94',6)
FROM dual;
Mark for Review
(1) Points
1/17/2004
1/11/1995
7/11/1994 (*)
7/17/1994
Correct Correct
2. Round and Trunc can be used on Date datatypes. True or False? Mark for Review
(1) Points
True (*)
False
3. What function would you use to return the highest date in a month? Mark for Review
(1) Points
FINAL_DAY
END_DAY
HIGHEST_DAY
LAST_DAY (*)
4. Which query would return a whole number if the sysdate is 26-MAY-04? Mark for Review
(1) Points
SELECT TRUNC(MONTHS_BETWEEN(SYSDATE,'19-MAR-79') /12)
AS YEARS
FROM DUAL;(*)
SELECT TRUNC(YEARS_BETWEEN(SYSDATE,'19-MAR-79') /12)
AS YEARS
FROM DUAL;
SELECT MONTHS_BETWEEN(SYSDATE,'19-MAR-79') /12
AS YEARS
FROM DUAL;
None of the above
5. If hire_date has a value of '03-July-03', then what is the output from this code?
SELECT ROUND(hire_date, 'Year') FROM employees;
Mark for Review
(1) Points
01-JAN-04 (*)
01-JAN-03
01-JUL-03
01-AUG-03
6. What is the result of the following query?
SELECT ADD_YEARS ('11-JAN-94',6)
FROM dual;
Mark for Review
(1) Points
This in not a valid SQL statement. (*)
7/11/1995
1/11/2000
7/11/2000
Quiz: Conversion Functions
1. A table has the following definition:
EMPLOYEES(
EMPLOYEE_ID NUMBER(6) NOT NULL,
LAST_NAME VARCHAR2(10) NOT NULL,
MANAGER_ID VARCHAR2(6))
and contains the following rows:
(1001, 'Bob Bevan', '200')
(200,'Natacha Hansen', null)
Will the following query work?
SELECT *
FROM employees
WHERE employee_id = manager_id;
Mark for Review
(1) Points
No, because the WHERE-clause will not find any matching data.
No, because the datatypes of ID and MANAGER are different.
Yes, Oracle will perform implicit datatype conversion. (*)
No. You will have to re-write the statement and perform explicit datatype conversion.
2. Which statement will return the salary of e.g. 6000 from the Employees table in the following format $6000.00? Mark for Review
(1) Points
SELECT TO_CHAR(salary, '$99999.00') SALARY
FROM employees(*)
SELECT TO_CHAR(salary, '99999.00') SALARY
FROM employees
SELECT TO_CHAR(salary, '$99999') SALARY
FROM employees
SELECT TO_CHAR(sal, '$99999.00') SALARY
FROM employees
3. The following script will run successfully. True or False?
SELECT TO_CHAR(TO_DATE("25-DEC-04",'dd-MON-yy'))
FROM dual
Mark for Review
(1) Points
True
False (*)
4. Which statement is true about SQL functions? Mark for Review
(1) Points
Functions can convert values or text to another data type.
Functions can round a number to a specified decimal place.
Functions can convert upper case characters to lower case characters.
a, b and c are true. (*)
None of the above statements are true.
5. You need to display the HIRE_DATE values in this format:
25th of July 2002.
Which SELECT statement would you use?
Mark for Review
(1) Points
SELECT enroll_date(hire_date, 'DDspth "of" Month YYYY')
FROM employees;
SELECT TO_CHAR(hire_date, 'ddth "of" Month YYYY')
FROM employees;(*)
SELECT TO_CHAR(hire_date, 'DDTH "of" Month YYYY')
FROM employees;
SELECT TO_CHAR(hire_date, 'DDspth 'of' Month RRRR')
FROM employees;
6. Sysdate is 12-MAY-2004.
You need to store the following date: 7-DEC-89
Which statement about the date format for this value is true?
Mark for Review
(1) Points
Both the YY and RR date formats will interpret the year as 1989.
Both the YY and RR date formats will interpret the year as 2089.
The RR date format will interpret the year as 1989, and the YY date format will interpret the year as 2089. (*)
The RR date format will interpret the year as 2089, and the YY date format will interpret the year as 1989.
Quiz: Null Functions
1. 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
SELECT last_name, NVL2(commission_pct, manager_id, -1) comm
FROM employees ;
Mark for Review
(1) Points
King, -1
Kochhar, -1
Vargas, -1
Zlotkey, .2
Statement will fail.
King, -1
Kochhar, 100
Vargas, 124
Zlotkey, .2
King, -1
Kochhar, -1
Vargas, -1
Zlotkey, 100(*)
2. The following statement returns 0 (zero). True or False?
SELECT 121/NULL
FROM dual;
Mark for Review
(1) Points
True
False (*)
3. Which function compares two expressions? Mark for Review
(1) Points
NVL
NULLIF (*)
NVL2
NULL
4. If quantity is a number datatype, what is the result of this statement?
SELECT NVL(200/quantity, 'zero') FROM inventory;
Mark for Review
(1) Points
zero
ZERO
The statement fails (*)
Null
5. Consider the following data in the Employees table:
(last_name, commission_pct, manager_id)
DATA:
King,null,null
Kochhar, null,100
Vargas, null, 124
Zlotkey,.2, 100
What is the result of the following statement:
SELECT last_name, COALESCE(commission_pct, manager_id, -1) comm
FROM employees ;
Mark for Review
(1) Points
Statement will fail
King, -1
Kochhar, 100
Vargas, 124
Zlotkey, .2(*)
King, -1
Kochhar, 100
Vargas, 124
Zlotkey, 100
King, null
Kochhar, 100
Vargas, 124
Zlotkey, .2
Quiz: Conditional Expressions
1. For the given data from Employees (last_name, manager_id) what is the result of the following statement:
DATA:
( King, null
Kochhar, 100
De Haan, 100
Hunold, 102
Ernst, 103)
SELECT last_name,
DECODE(manager_id, 100, 'King', 'A N Other') "Works For?"
FROM employees
Mark for Review
(1) Points
King, Null
Kochhar, King
De Haan, King
Hunold, A N Other
Ernst, A N Other
King, A N Other
Kochhar, King
De Haan, King
Hunold, A N Other
Ernst, A N Other(*)
Invalid statement.
King, A N Other
Kochhar, King
De Haan, King
Hunold, Kochhar
Ernst, De Haan
2. CASE and DECODE evaluate expressions in a similar way to IF-THEN-ELSE logic. However, DECODE is specific to Oracle syntax. True or False? Mark for Review
(1) Points
True (*)
False
3. Which of the following is a conditional expression used in SQL? Mark for Review
(1) Points
CASE (*)
DESCRIBE
WHERE
NULLIF
4. Which statement will return a listing of last names, salaries and a rating of 'Low', 'Medium', 'Good' or 'Excellent' depending on the salary value? Mark for Review
(1) Points
SELECT last_name,salary,
(CASE WHEN salary < 5000 THEN 'Low'
WHEN salary < 10000 THEN 'Medium'
WHEN salary < 20000 THEN 'Good'
ELSE 'Excellent'
END) qualified_salary
FROM employees;(*)
SELECT last_name,salary,
(CASE WHEN sal < 5000 THEN 'Low'
WHEN sal < 10000 THEN 'Medium'
WHEN sal < 20000 THEN 'Good'
ELSE 'Excellent'
END) qualified_salary
FROM employees;
SELECT last_name,sal,
(CASE WHEN sal < 5000 THEN 'Low'
WHEN sal < 10000 THEN 'Medium'
WHEN sal < 20000 THEN 'Good'
ELSE 'Excellent'
END) qualified_salary
FROM employees;
SELECT last_name,salary,
(RATING WHEN salary < 5000 THEN 'Low'
WHEN salary < 10000 THEN 'Medium'
WHEN salary < 20000 THEN 'Good'
ELSE 'Excellent'
END) qualified_salary
FROM employees;
Quiz: Cartesian Product and the Join Operations
1. If table A have 10 rows and table B have 5 rows, how many rows will be returned if you perform a equi-join on those two tables? Mark for Review
(1) Points
50
10
5
It depends on the data found in the two tables. (*)
2. Oracle proprietary JOINS can use the WHERE clause for conditions other than the join-condition. True or False? Mark for Review
(1) Points
True (*)
False
3. Will the following statement work?
SELECT department_name, last_name
FROM employees, departments
WHERE department_id = department_id; Mark for Review
(1) Points
Yes, there are no syntax errors in the statement.
No, Oracle will return a column ambiguously defined error. (*)
Yes, Oracle will resolve which department_id colum comes from which table.
No, Oracle will not allow joins in the WHERE clause.
4. What is the result of a query that selects from two tables but includes no join condition? Mark for Review
(1) Points
A Cartesian product. (*)
A selection of matched rows from both tables.
A Syntax error.
A selection of rows from the first table only.
5. When must column names be prefixed by table names in JOIN syntax? Mark for Review
(1) Points
When more than two tables participate in the join.
Only when query speed and database performance is a concern.
When the same column name appears in more than one table of the query. (*)
Never.
6. If table A have 10 rows and table B have 5 rows, how many rows will be returned if you perform a cartesian join on those two tables? Mark for Review
(1) Points
5
50 (*)
10
15
Quiz: Nonequijoins
1. Which statement about joining tables with a non-equijoin is false? Mark for Review
(1) Points
A WHERE clause must specify a column in one table that is compared to a column in the second table (*)
The number of join conditions required is always one less than the number of tables being joined
The columns being joined must have compatible data types
None of the above
2. The following statement is an example of a nonequi-join?
SELECT e.last_name, e.salary, j.grade_level
FROM employees e, job_grades j
WHERE e.salary
BETWEEN j.lowest_sal AND j.highest_sal;
True or False?
Mark for Review
(1) Points
True (*)
False
3. Which of the following operators is/are typically used in a nonequijoin? Mark for Review
(1) Points
NOT
OR
IN
>=, <=, BETWEEN ...AND (*)
*
Quiz: Outer Joins
1. 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? Mark for Review
(1) Points
Equijoin
Self join
Outer join (*)
Nonequi-Join
2. To perform a valid outer join between DEPARMENTS and EMPLOYEES to list departments without employees select the correct WHERE clause for the following select statement:
SELECT d.department_name, e.last_name
FROM employees e, departments d
WHERE
Mark for Review
(1) Points
e.department_id(+) = d.department_id (*)
e.department_id(+) = d.department_id(+)
e.department_id = d.department_id(+)
e.department_id = d.department_id
3. 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?
Mark for Review
(1) Points
True
False (*)
4. Which symbol is used to perform an outer join? Mark for Review
(1) Points
*
||
(+) (*)
#
Quiz: Self Joins and Hierarchical Queries
1. Hierarchical queries MUST use the LEVEL pseudo column. True or False? Mark for Review
(1) Points
True
False (*)
2. Which select statement will return the last name and hire data of an employee and his/ her manager for employees that started in the company before their managers? Mark for Review
(1) Points
SELECT w.last_name, w.hire_date, m.last_name, m.hire_date
FROM employees w , employees m
WHERE w.manager_id = m.employee_id AND w.hire_date < m.hire_date(*)
SELECT w.last_name, w.hire_date, m.last_name, m.hire_date
FROM employees w , employees m
WHERE w.manager_id != m.employee_id AND w.hire_date < m.hire_date
SELECT w.last_name, w.hire_date, m.last_name, m.hire_date
FROM employees w , employees w
WHERE w.manager_id = w.employee_id
AND w.hire_date < w.hire_date
SELECT w.last_name, w.hire_date, m.last_name, m.hire_date
FROM employees w , employees m
WHERE w.manager_id = m.employee_id AND w.hire_date > m.hire_date
3. Which of the following database design concepts is implemented with a self join? Mark for Review
(1) Points
Non-Transferability
Recursive Relationship (*)
Supertype
Arc
4. Hierarchical queries can walk both Top-Down and Bottom-Up. True or False? Mark for Review
(1) Points
True (*)
False
5. Which of the following database design concepts do you need in your tables to write Hierarchical queries? Mark for Review
(1) Points
Non-Transferability
Recursive Relationship (*)
Supertype
Arc
6. Which SELECT statement implements a self join? Mark for Review
(1) Points
SELECT e.employee_id, m.manager_id
FROM employees e NATURAL JOIN employees m;
SELECT e.employee_id, m.manager_id
FROM employees e, employees m
WHERE m.employee_id = e.manager_id;(*)
SELECT e.employee_id, m.manager_id
FROM employees e, manager m
WHERE e.employee_id = m.manager_id;
SELECT e.employee_id, m.manager_id
FROM employees e, departments m
WHERE e.employee_id = m.manager_id;
Quiz: Cross joins and Natural Joins
1. What happens when you create a Cartesian product? Mark for Review
(1) Points
All rows from one table are joined to all rows of another table (*)
No rows are returned as you entered wrong join-criteria
The table is joined to itself, one column to the next column, exhausting all possibilities
All rows that do not match in the WHERE clause are displayed
2. A NATURAL JOIN is based on: Mark for Review
(1) Points
Columns with the same name and datatype (*)
Columns with the same name
Columns with the same datatype and width
Tables with the same structure
3. The join column must be included in the select statement when you use the NATURAL JOIN clause. True or False? Mark for Review
(1) Points
True
False (*)
4. The ___________ join is the ANSI-standard syntax used to generate a Cartesian product. Mark for Review
(1) Points
NATURAL
ALL
FULL
CROSS (*)
Quiz: Join Clauses
1. The primary advantage of using JOIN ON is: Mark for Review
(1) Points
The join happens automatically based on matching column names and data types
It will display rows that do not meet the join condition
It easily produces a Cartesian product between the tables in the statement
It permits columns that don?t have matching data types to be joined
It permits columns with different names to be joined (*)
2. The keywords JOIN _____________ should be used to join tables with the same column names but different datatypes. Mark for Review
(1) Points
NATURAL ON
ON
WHEN
USING (*)
3. The following is a valid SQL statement.
SELECT e.employee_id, e.last_name, d.location_id, department_id
FROM employees e JOIN departments d
USING (department_id) ;
True or False?
Mark for Review
(1) Points
True (*)
False
4. Table aliases MUST be used with columns referenced in the JOIN USING clause. True or False? Mark for Review
(1) Points
True
False (*)
5. You can do nonequi-joins with ANSI-Syntax. True or False? Mark for Review
(1) Points
True (*)
False
Publicat de Manolache Mihnea la 15:43 0 comentarii
Quiz: Inner versus Outer Joins
1. If you select rows from two tables (employees and departments) using an outer join, what will you get? Use the code below to arrive at your answer:
SELECT e.last_name, e.department_id, d.department_name
FROM employees e
LEFT OUTER JOIN departments d
ON (e.department_id = d.department_id);
Mark for Review
(1) Points
All employees that do not have a department_id assigned to them
All employees including those that do not have a department_id assigned to them (*)
No employees as the statement will fail
None of the above
2. What is another name for a simple join or an inner join? Mark for Review
(1) Points
Nonequijoin
Equijoin (*)
Self Join
Outer Join
3. EMPLOYEES Table:
Name Null? Type
EMPLOYEE_ID NOT NULL NUMBER(6)
FIRST_NAME VARCHAR2(20)
LAST_NAME NOT NULL VARCHAR2(25)
DEPARTMENT_ID NUMBER (4)
DEPARTMENTS Table:
Name Null? Type
DEPARTMENT_ID NOT NULL NUMBER 4
DEPARTMENT_NAME NOT NULL VARCHAR2(30)
MANAGER_ID NUMBER (6)
A query is needed to display each department and its manager name from the above tables. However, not all departments have a manager but we want departments returned in all cases. Which of the following SQL: 1999 syntax scripts will accomplish the task?
Mark for Review
(1) Points
SELECT d.department_id, e.first_name, e.last_name
FROM employees e
LEFT OUTER JOIN departments d
WHERE (e.department_id = d.department_id);
SELECT d.department_id, e.first_name, e.last_name
FROM employees e
RIGHT OUTER JOIN departments d ON (e.employee_id = d.manager_id);(*)
SELECT d.department_id, e.first_name, e.last_name
FROM employees e
FULL OUTER JOIN departments d ON (e.employee_id = d.manager_id);
SELECT d.department_id, e.first_name, e.last_name
FROM employees e, departments d
WHERE e.employee_id RIGHT OUTER JOIN d.manager_id;
4. Given the following descriptions of the employees and jobs tables, which of the following scripts will display each employee?s possible minimum and maximum salaries based on their job title?
EMPLOYEES Table:
Name Null? Type
EMPLOYEE_ID NOT NULL NUMBER (6)
FIRST_NAME VARCHAR2 (20)
LAST_NAME NOT NULL VARCHAR2 (25)
EMAIL NOT NULL VARCHAR2 (25)
PHONE_NUMBER VARCHAR2 (20)
HIRE_DATE NOT NULL DATE
JOB_ID NOT NULL VARCHAR2 (10)
SALARY NUMBER (8,2)
COMMISSION_PCT NUMBER (2,2)
MANAGER_ID NUMBER (6)
DEPARTMENT_ID NUMBER (4)
JOBS Table:
Name Null? Type
JOB_ID NOT NULL VARCHAR2 (10)
JOB_TITLE NOT NULL VARCHAR2 (35)
MIN_SALARY NUMBER (6)
MAX_SALARY NUMBER (6)
Mark for Review
(1) Points
SELECT e.first_name, e.last_name, e.job_id, j.min_salary, j.max_salary
FROM employees e
NATURAL JOIN jobs j
USING (job_id);
SELECT first_name, last_name, job_id, min_salary, max_salary
FROM employees
NATURAL JOIN jobs;(*)
SELECT e.first_name, e.last_name, e.job_id, j.min_salary, j.max_salary
FROM employees e
NATURAL JOIN jobs j;
SELECT first_name, last_name, job_id, min_salary, max_salary
FROM employees e
FULL JOIN jobs j (job_id);
SELECT e.first_name, e.last_name, e.job_id, j.min_salary, j.max_salary
FROM employees e
NATURAL JOIN jobs j ON (e.job_title = j.job_title);
5. Which syntax would be used to retrieve all rows in both the EMPLOYEES and DEPARTMENTS tables, even when there is no match? Mark for Review
(1) Points
FULL OUTER JOIN (*)
LEFT OUTER JOIN AND RIGHT OUTER JOIN
FULL INNER JOIN
Use any equijoin syntax
6. The following statement is an example of what kind of join?
SELECT car.vehicle_id, driver.name
FROM car
LEFT OUTER JOIN driver ON (driver_id) ;
Mark for Review
(1) Points
Inner Join
Outer Join (*)
Equijoin
Optimal Join
7. For which of the following tables will all the values be retrieved even if there is no match in the other?
SELECT e.last_name, e.department_id, d.department_name
FROM employees e
LEFT OUTER JOIN departments d ON (e.department_id = d.department_id);
Mark for Review
(1) Points
employees (*)
department
both
Neither. the LEFT OUTER JOIN limits the value to the matching department id's.
Quiz: Group Functions
1. The following statement will work even though it uses the same column with different GROUP functions:
SELECT AVG(salary), MAX(salary), MIN(salary), SUM(salary)
FROM employees;
True or False?
Mark for Review
(1) Points
True (*)
False
2. Given the following data in the employees table (employee_id, salary, commission_pct)
DATA: (143, 2600, null
144, 2500, null
149, 10500, .2
174, 11000, .3
176, 8600, .2
178, 7000, .15)
What is the result of the following statement:
SELECT SUM(commission_pct), COUNT(commission_pct)
FROM employees
WHERE employee_id IN( 143,144,149,174,176,178)
Mark for Review
(1) Points
SUM = .85 and COUNT = 6
SUM = 1.85 and COUNT = 6
SUM = .85 and COUNT = 4 (*)
SUM = 1.85 and COUNT = 4
3. Given the following data in the employees table (employee_id, salary, commission_pct)
DATA: (143, 2600, null
144, 2500, null
149, 10500, .2
174, 11000, .3
176, 8600, .2
178, 7000, .15)
What is the result of the following statement:
SELECT AVG(commission_pct)
FROM employees
WHERE employee_id IN( 143,144,149,174,176,178)
Mark for Review
(1) Points
1.2125
This statement is invalid
0.2125 (*)
0.0425
4. What would the following SQL statement return?
SELECT MAX(hire_date)
FROM employees;
Mark for Review
(1) Points
The hire date of the longest serving employee
The hire date of the newest employee (*)
The hire dates of all employees in ascending order
The hire dates of all employees
5. What two group functions can be used with any datatype? Mark for Review
(1) Points
STDDEV, VARIANCE
SUM, AVG
COUNT, SUM
MIN, MAX (*)
6. You can use GROUP functions in all clauses of a SELECT statement. True or False? Mark for Review
(1) Points
True
False (*)
Quiz: Count, Distinct, NVL
1. To include null values in the calculations of a group function, you must: Mark for Review
(1) Points
Precede the group function name with NULL
Count the number of null values in that column using COUNT
Convert the null to a value using the NVL( ) function (*)
Group functions can never use null values
2. Using your existing knowledge of the employees table, would the following two statements produce the same result?
SELECT COUNT(*)
FROM employees;
SELECT COUNT(commission_pct)
FROM employees;
Mark for Review
(1) Points
The first statement is invalid
Yes
No (*)
The second statement is invalid
3. What would the following SQL statement return?
SELECT COUNT(first_name)
FROM employees;
Mark for Review
(1) Points
A listing of all non-null first names in the employees table
The total number of non-null first names in the employees table (*)
The total number of rows in the employees table
A listing of all unique first names in the employees table
4. What would the following SQL statement return?
SELECT COUNT(DISTINCT salary)
FROM employees;
Mark for Review
(1) Points
A listing of all unique salaries in the employees table
The total number of rows in the employees table
The total amount of salaries in the employees table
The number of unique salaries in the employees table (*)
Quiz: Group By and Having Clauses, ROLLUP and CUBE
1. Which of the following SQL statements could display the number of people with the same last name: Mark for Review
(1) Points
SELECT first_name, last_name, COUNT(employee_id)
FROM EMPLOYEES
GROUP BY last_name;
SELECT employee_id, COUNT(last_name)
FROM EMPLOYEES
GROUP BY last_name;
SELECT last_name, COUNT(last_name)
FROM EMPLOYEES
GROUP BY last_name;(*)
SELECT employee_id, DISTINCT(last_name)
FROM EMPLOYEES
GROUP BY last_name;
2. The use of GROUP BY GROUPING SETS(....) can speed up the execution of complex report statements? (True or False) Mark for Review
(1) Points
True (*)
False
3. The following is a valid statement:
SELECT MAX(AVG(salary))
FROM employees
GROUP BY department_id;
True or False?
Mark for Review
(1) Points
True (*)
False
4. Read the following SELECT statement. Choose the column or columns that must be included in the GROUP BY clause.
SELECT COUNT(last_name), grade, gender
FROM STUDENTS
GROUP_BY ?????;
Mark for Review
(1) Points
last_name
last_name, grade
grade, gender (*)
last_name, gender
5. Examine the following statement:
SELECT department_id, manager_id, job_id, SUM(salary)
FROM employees
GROUP BY GROUPING SETS((department_id, manager_id), (department_id, job_id))
What data will this query generate?
Mark for Review
(1) Points
Total salaries for (department_id, job_id) and (department_id, manager_id) (*)
Total salaries for (department_id, job_id, manager_id)
Total for (job_id, manager_id)
The statement will fail.
6. If you want to include subtotals and grant totals for all columns mentioned in a GROUP BY clause you should use which of the following extensions to the GROUP BY clause? Mark for Review
(1) Points
ROLLUP
CUBE (*)
GROUP BY ALL COLUMNS
HAVING
7. Is the following statement correct?
SELECT department_id, AVG(salary)
FROM employees;
Mark for Review
(1) Points
No, because a GROUP BY department_id clause is needed (*)
No, because the SELECT clause cannot contain both individual columns and group functions
No, because the AVG function cannot be used on the salary column
Yes
8. Examine the following statement:
SELECT department_id, manager_id, job_id, SUM(salary)
FROM employees
GROUP BY GROUPING SETS(.......);
Select the correct GROUP BY GROUPING SETS clause from the following list:
Mark for Review
(1) Points
GROUP BY GROUPING SETS (department_id, AVG(salary)), (department_id, job_id), (department_id, manager_id)
GROUP BY GROUPING SETS (department_id, salary), (department_id, job_id), (department_id, manager_id)
GROUP BY GROUPING SETS ((department_id, manager_id), (department_id, job_id), (manager_id, job_id)) (*)
GROUP BY GROUPING SETS ((department_id, manager_id), (department_id, SUM(salary), (manager_id, job_id))
9. How would you alter the following query to list only employees where more than one employee exists with the same last_name:
SELECT last_name, COUNT(employee_id)
FROM EMPLOYEES
GROUP BY last_name;
Mark for Review
(1) Points
SELECT last_name, COUNT(employee_id)
FROM EMPLOYEES
WHERE COUNT(*) > 1
GROUP BY last_name
SELECT last_name, COUNT(last_name)
FROM EMPLOYEES
GROUP BY last_name
HAVING COUNT(last_name) > 1;(*)
SELECT last_name, COUNT(last_name)
FROM EMPLOYEES
GROUP BY last_name
EXISTS COUNT(last_name) > 1;
SELECT employee_id, DISTINCT(last_name)
FROM EMPLOYEES
GROUP BY last_name
HAVING last_name > 1;
10. Is the following statement correct:
SELECT first_name, last_name, salary, department_id, COUNT(employee_id)
FROM employees
WHERE department_id = 50
GROUP BY last_name, first_name, department_id;
Mark for Review
(1) Points
Yes
No, beause you cannot have a WHERE-clause when you use group functions.
No, because the statement is missing salary in the GROUP BY clause (*)
Yes, because Oracle will correct any mistakes in the statement itself
11. Examine the following statement:
SELECT department_id, manager_id, job_id, SUM(salary)
FROM employees
GROUP BY ROLLUP(department_id, manager_id)
What extra data will this query generate?
Mark for Review
(1) Points
Subtotals for department_id, and grand totals for salary.
Subtotals for department_id, job_id and grand totals for salary.
Subtotals for department_id, job_id, manager_id and grand totals for salary.
The statement will fail. (*)
Quiz: Subqueries
1. What will the following statement return:
SELECT employee_id, last_name
FROM employees
WHERE salary =
(SELECT MIN(salary)
FROM employees
GROUP BY department_id);
Mark for Review
(1) Points
Nothing. It is an invalid statement. (*)
A list of last_names and salaries of employees
A list of first_names and salaries of employees in Department 50
A list of last_names and salaries of employees grouped by department_id.
2. What will the following statement return:
SELECT last_name, salary
FROM employees
WHERE salary < (SELECT salary
FROM employees
WHERE employee_id = 103)
Mark for Review
(1) Points
A list of last_names and salaries of employees that makes more than employee 103
A list of last_names and salaries of employees that makes less than employee 103 (*)
A list of first_names and salaries of employees making less than employee 103
Nothing. It is an invalid statement.
3. What will the following statement return:
SELECT last_name, salary
FROM employees
WHERE (department_id, job_id) IN (SELECT (department_id, job_id)
FROM employees
WHERE employee_id = 103)
Mark for Review
(1) Points
A list of last_names and salaries of employees that works in the same department and has the same job_id as that of employee 103. (*)
A list of last_names or salaries of employees that works in the same department and has the same job_id as that of employee 103.
A list of last_names and salaries of employees that works in the same department or has the same job_id as that of employee 103.
Nothing. It is an invalid statement.
4. Which of the following statements is a true guideline for using subqueries? Mark for Review
(1) Points
Do not enclose the subquery in parentheses.
Place the subquery on the left side of the comparison condition.
The outer and inner queries can reference more than one table. They can get data from different tables. (*)
Only one WHERE clause can be used for a SELECT statement, and if specified, it must be the outer query.
5. Subqueries can only be placed in the WHERE clause. True or False? Mark for Review
(1) Points
True
False (*)
6. Examine the following statement:
SELECT last_name, salary
FROM employees
WHERE department_id = (SELECT department_id
FROM employees
WHERE employee_id = 103) AND job_id = (SELECT job_id
FROM employees
WHERE employee_id = 103)
Is this a pair-wise or non-pair-wise Subquery?
Mark for Review
(1) Points
This is an example of a non-pair-wise subquery. (*)
This is an example of a pair-wise subquery.
Neither. This statement is illegal, and will not run.
Quiz: Single-Row Subqueries
1. Subqueries are limited to four per SQL transaction. True or False? Mark for Review
(1) Points
True
False (*)
2. In a non-correlated subquery, the outer query always executes prior to the inner query's execution. True or False? Mark for Review
(1) Points
True
False (*)
3. Single row subqueries may not include this operator: Mark for Review
(1) Points
ALL (*)
=
<>
>
4. The result of this statement will be:
SELECT last_name, job_id, salary, department_id
FROM employees
WHERE job_id =
(SELECT job_id
FROM employees
WHERE employee_id = 141) AND department_id =
(SELECT department_id
FROM departments
WHERE location_id =1500)
Mark for Review
(1) Points
All employees from Location 1500 will be displayed
An error since you canﾒt get data from two tables in the same subquery
All employees with the department id of 141
Only the employees whose job id matches employee 141 and who work in location 1500 (*)
5. If the subquery returns no rows will the outer query return any values? Mark for Review
(1) Points
No, because you are not allowed to not return any rows from a subquery
Yes. It will just run and ignore the subquery
No, because the subquery will be treated like a null value. (*)
Yes, Oracle will find the nearest value and rewrite your statement implicitly when you run it
Quiz: Multiple-Row Subqueries
1. The SQL multiple-row subquery extends the capability of the single-row syntax through the use of what three comparison operators? Mark for Review
(1) Points
IN, ANY and EQUAL
IN, ANY and ALL (*)
IN, ANY and EVERY
IN, ALL and EVERY
2. There can be more than one subquery returning information to the outer query. True or False? Mark for Review
(1) Points
True (*)
False
3. The salary column of the f_staffs table contains the following values:
4000
5050
6000
11000
23000
Which of the following statements will return the last_name and first_name of those employees who earn more than 5000.
Mark for Review
(1) Points
SELECT last_name, first_name
FROM f_staffs
WHERE salary = (SELECT salary FROM f_staffs WHERE salary > 5000);
SELECT last_name, first_name
FROM f_staffs
WHERE salary = (SELECT salary FROM f_staffs WHERE salary <>
SELECT last_name, first_name
FROM f_staffs
WHERE salary IN (SELECT salary FROM f_staffs WHERE salary > 5000);(*)
SELECT last_name, first_name
FROM f_staffs
WHERE salary IN
(SELECT last_name, first_name FROM f_staffs WHERE salary < 5000);
4. Multiple-row subqueries must have NOT, IN or ANY in the WHERE clause of the inner query. True or False? Mark for Review
(1) Points
True
False (*)
5. When a multiple-row subquery uses the NOT IN (<>ALL) operator, if one of the values returned by the inner query is a null value, the entire query returns: Mark for Review
(1) Points
A list of Nulls
All rows that were selected by the inner query including the null value(s)
All rows, minus the null value(s), that were selected by the inner query
No rows returned (*)
6. Group functions, such as HAVING and GROUP BY can be used in multiple-row subqueries. True or False? Mark for Review
(1) Points
True (*)
False
7. In a subquery the ALL operator compares a value to every value returned by the inner query. True or False? Mark for Review
(1) Points
True (*)
False
8. Group functions can be used in subqueries even though they may return many rows. True or False? Mark for Review
(1) Points
True (*)
False
Quiz: Correlated Subqueries
1. In a correlated subquery the outer and inner query are joined on one or more columns? (True or False) Mark for Review
(1) Points
True (*)
False
2. Table aliases must be used when you are writing correlated subqueries? (True or false) Mark for Review
(1) Points
True (*)
False
3. Correlated Subqueries must work on the same tables in both the inner and outer query? (True or False) Mark for Review
(1) Points
True
False (*)
4. The WITH-clause is a way of creating extra tables in the database? (True or False) Mark for Review
(1) Points
True
False (*)
Quiz: Using SET Operators
1. The difference between UNION and UNION ALL is Mark for Review
(1) Points
There is no difference between, you get exactly the same result.
UNION will remove duplicates, UNION ALL returns all rows from all queries (*)
UNION ALL is like a NATURAL JOIN
UNION is a synomym for UNION ALL
2. MINUS will give you rows from the first query not present in the second query? (True or False) Mark for Review
(1) Points
True (*)
False
3. INTERSECT will give you rows found in both queries? (True or False) Mark for Review
(1) Points
True (*)
False
4. Which ones of the following are correct SET operators? (choose two) Mark for Review
(1) Points
(Choose all correct answers)
UNION, MINUS (*)
UNION ALL, PLUS ALL
UNION ALL, INTERSECT (*)
MINUS, PLUS
Quiz: Insert Statement
1. What is the quickest way to use today's date when you are creating a new row? Mark for Review
(1) Points
Simply write today's date in the format of 'dd-mon-rr'.
Simply use the keyword DATE in the insert statement.
Use the SYSDATE function. (*)
Use the TODAYS_DATE function.
2. When inserting rows into a table all columns must be given values. True or False? Mark for Review
(1) Points
True
False (*)
3. To return a table summary on the customers table, which of the following is correct? Mark for Review
(1) Points
SHOW customers, or SEE customers
DISTINCT customers, or DIST customers
DESCRIBE customers, or DESC customers (*)
DEFINE customers, or DEF customers
4. If the employees table have 7 rows how many rows are inserted into the copy_emps table with the following statement:
INSERT INTO copy_emps (employee_id, first_name, last_name, salary, department_id)
SELECT employee_id, first_name, last_name, salary, department_id
FROM employees
Mark for Review
(1) Points
No rows, as you cannot use subqueries in an insert statement.
7 rows, as there is no WHERE-clause on the subquery. (*)
No rows, as the SELECT statement is invalid.
10 rows will be created.
5. Is it possible to insert more than one row at a time using an INSERT statement with a VALUES clause? Mark for Review
(1) Points
No, you can only create one row at a time when using the VALUES clause. (*)
Yes, you can just list as many rows as you want, just remember to separate the rows with commas.
No, there is no such thing as INSERT ... VALUES.
6. Which of the following statements will add a new customer to the customers table in the Global Fast Foods database? Mark for Review
(1) Points
INSERT IN customers (id, first_name, last_name, address, city, state, zip, phone_number);
INSERT INTO customers (id, first_name, last_name, address, city, state, zip, phone_number)
VALUES ("145", 'Katie', 'Hernandez', '92 Chico Way', 'Los Angeles', 'CA', "98008", "8586667641");
INSERT INTO customers (id, first_name, last_name, address, city, state, zip, phone_number)
VALUES (145, 'Katie', 'Hernandez', '92 Chico Way', 'Los Angeles', 'CA', 98008, 8586667641); (*)
INSERT INTO customers
(id 145, first_name 'Katie', last_name 'Hernandez', address '92 Chico Way', city 'Los Angeles', state 'CA', zip 98008, phone_number 8586667641);
7. When inserting a new row the null keyword can be included in the values list for any null column. True or False? Mark for Review
(1) Points
True (*)
False
8. DML is an acronym that stands for: Mark for Review
(1) Points
Debit Markup Language
Don't Manipulate Language
Data Markup Language
Data Manipulation Language (*)
9. Insert statements can be combined with subqueries to create more than one row per statement. True or False? Mark for Review
(1) Points
True (*)
False
__________________________________________________________________________________
SQL Mid Term Exam Review Answers
1. You issue this SQL statement:
SELECT ROUND (1282.248, -2) FROM dual;
What value does this statement produce?
1200
1282
1282.25
1300
2. Evaluate this function: MOD (25, 2) Which value is returned?
1
2
25
0
3. Which script displays '01-MAY-04' when the HIRE_DATE value is '20-MAY-04'?
SELECT TRUNC(hire_date, 'MONTH')
FROM employee;
SELECT ROUND(hire_date, 'MONTH')
FROM employee;
SELECT ROUND(hire_date, 'MON')
FROM employee;
SELECT TRUNC(hire_date, 'MI')
FROM employee;
4. You want to create a report that displays all orders and their amounts that were placed during the month of January. You want the orders with the highest amounts to appear first. Which query should you issue?
SELECT orderid, total
FROM orders
WHERE order_date LIKE '01-jan-02' AND '31-jan-02'
ORDER BY total DESC;
SELECT orderid, total
FROM orders
WHERE order_date IN ( 01-jan-02 , 31-jan-02 )
ORDER BY total;
SELECT orderid, total
FROM orders
WHERE order_date BETWEEN '01-jan-02' AND '31-jan-02'
ORDER BY total DESC;
SELECT orderid, total
FROM orders
WHERE order_date BETWEEN '31-jan-02' AND '01-jan-02'
ORDER BY total DESC;
5. Which of the following SQL statements will correctly display the last name and the number of weeks employed for all employees in department 90?
SELECT last_name, (SYSDATE-hire_date)/7 AS WEEKS
FROM employees
WHERE department_id = 90;
SELECT last name, (SYSDATE-hire_date)/7 DISPLAY WEEKS
FROM employees
WHERE department id = 90;
SELECT last_name, # of WEEKS
FROM employees
WHERE department_id = 90;
SELECT last_name, (SYSDATE-hire_date)AS WEEK
FROM employees
WHERE department_id = 90;
6. You need to subtract three months from the current date. Which function should you use?
ROUND
TO_DATE
ADD_MONTHS
MONTHS_BETWEEN
7. Which function would you use to return the current database server date and time?
DATE
SYSDATE
DATETIME
CURRENTDATE
8. You need to display the current year as a character value (for example: Two Thousand and One). Which element would you use?
RR
YY
YYYY
YEAR
9. Which three statements about functions are true? (Choose three.)
The SYSDATE function returns the Oracle Server date and time.
The ROUND number function rounds a value to a specified decimal place or the nearest whole number.
The CONCAT function can only be used on character strings, not on numbers.
The SUBSTR character function returns a portion of a string beginning at a defined character position to a specified length.
10. You need to return a portion of each employee"s last name, beginning with the first character up to the fifth character. Which character function should you use?
INSTR
TRUNC
SUBSTR
CONCAT
11. Evaluate this SELECT statement:
SELECT LENGTH(email)
FROM employee;
What will this SELECT statement display?
The longest e-mail address in the EMPLOYEE table
The email address of each employee in the EMPLOYEE table
The number of characters for each value in the EMAIL column in the employees table
The maximum number of characters allowed in the EMAIL column
12. Which functions can be used to manipulate character, number, and date column values?
CONCAT, RPAD, and TRIM
UPPER, LOWER, and INITCAP
ROUND, TRUNC, and MOD
ROUND, TRUNC, and ADD_MONTHS
13. You query the database with this SQL statement:
SELECT CONCAT(last_name, (SUBSTR(LOWER(first_name), 4))) "Default Password"
FROM employees;
Which function will be evaluated first?
CONCAT
SUBSTR
LOWER
All three will be evaluated simultaneously.
14. You query the database with this SQL statement:
SELECT LOWER(SUBSTR(CONCAT(last_name, first_name)), 1, 5) "ID"
FROM employee;
In which order are the functions evaluated?
LOWER, SUBSTR, CONCAT
LOWER, CONCAT, SUBSTR
SUBSTR, CONCAT, LOWER
CONCAT, SUBSTR, LOWER
15. The PRICE table contains this data:
PRODUCT_ID MANUFACTURER_ID
86950 59604
You query the database and return the value 95. Which script did you use?
SELECT SUBSTR(product_id, 3, 2)
FROM price
WHERE manufacturer_id = 59604;
SELECT LENGTH(product_id, 3, 2)
FROM price
WHERE manufacturer_id = 59604;
SELECT SUBSTR(product_id, -1, 3)
FROM price
WHERE manufacturer_id = 59604;
SELECT TRIM(product_id, -3, 2)
FROM price
WHERE manufacturer_id = 59604;
16. When executed, which statement displays a zero if the TUITION_BALANCE value is zero and the HOUSING_BALANCE value is null?
SELECT NVL (tuition_balance + housing_balance, 0) "Balance Due"
FROM student_accounts;
SELECT NVL(tuition_balance, 0), NVL (housing_balance), tuition_balance + housing_balance "Balance Due"
FROM student_accounts;
SELECT tuition_balance + housing_balance
FROM student_accounts;
SELECT TO_NUMBER(tuition_balance, 0), TO_NUMBER (housing_balance, 0), tutition_balance + housing_balance "Balance Due"
FROM student_accounts;
17. You need to replace null values in the DEPTARTMENT_ID column with the text "N/A". Which functions should you use?
TO CHAR and NVL
TO_CHAR and NULL
TO_CHAR and NULLIF
TO_NUMBER and NULLIF
18. Which of the following General Functions will return the first non-null expression in the expression list?
NVL
NVL2
NULLIF
COALESCE
19. The STYLES table contains this data:
STYLE_ID STYLE_NAME CATEGORY COST
895840 SANDAL 85940 12.00
968950 SANDAL 85909 10.00
869506 SANDAL 89690 15.00
809090 LOAFER 89098 10.00
890890 LOAFER 89789 14.00
857689 HEEL 85940 11.00
758960 SANDAL 86979
Evaluate this SELECT statement:
SELECT style_id, style_name, category, cost
FROM styles WHERE style_name LIKE 'SANDAL' AND NVL(cost, 0) < 15.00
ORDER BY category, cost;
Which result will the query provide?
STYLE_ID STYLE_NAME CATEGORY COST
895840 SANDAL 85940 12.00
968950 SANDAL 85909 10.00
758960 SANDAL 86979
STYLE_ID STYLE_NAME CATEGORY COST
895840 SANDAL 85909 12.00
968950 SANDAL 85909 10.00
869506 SANDAL 89690 15.00
758960 SANDAL 86979
STYLE_ID STYLE_NAME CATEGORY COST
895840 SANDAL 85909 12.00
968950 SANDAL 85909 10.00
758960 SANDAL 86979
869506 SANDAL 89690 15.00
STYLE_ID STYLE_NAME CATEGORY COST
968950 SANDAL 85909 10.00
895840 SANDAL 85940 12.00
758960 SANDAL 86979
20. 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?
2001
1901
2017
1917
21. Which functions allow you to perform explicit data type conversions?
ROUND, TRUNC, ADD_MONTHS
LENGTH, SUBSTR, LPAD, TRIM
TO_CHAR, TO_DATE, TO_NUMBER
NVL, NVL2, NULLIF
22. The EMPLOYEES table contains these columns:
EMPLOYEE_ID NUMBER(9)
LAST_NAME VARCHAR2 (25)
FIRST_NAME VARCHAR2 (25)
HIRE_DATE DATE
You need to display HIRE_DATE values in this format:
January 28, 2000
Which SELECT statement could you use?
SELECT TO_CHAR(hire_date, Month DD, YYYY)
FROM employees;
SELECT TO_CHAR(hire_date, 'Month DD, YYYY')
FROM employees;
SELECT hire_date(TO_CHAR 'Month DD', ' YYYY')
FROM employees;
SELECT TO_CHAR(hire_date, 'Month DD', ' YYYY')
FROM employees;
23. You have been asked to create a report that lists all customers who have placed orders of at least $2,500. The report's date should be displayed in the Day, Date Month, Year format (For example, Tuesday, 13 April, 2004 ). Which statement should you issue?
SELECT companyname, TO_CHAR (sysdate, 'fmdd, dy month, yyyy'), total
FROM customers NATURAL JOIN orders
WHERE total >= 2500;
SELECT companyname, TO_DATE (date, 'day, dd month, yyyy'), total
FROM customers NATURAL JOIN orders
WHERE total >= 2500;
SELECT companyname, TO_DATE (sysdate, 'dd, dy month, yyyy'), total
FROM customers NATURAL JOIN orders
WHERE total >= 2500;
SELECT companyname, TO_CHAR (sysdate, 'fmDay, dd Month, yyyy'), total
FROM customers NATURAL JOIN orders
WHERE total >= 2500
24. Which SQL Statement should you use to display the prices in this format: "$00.30"?
SELECT TO_CHAR(price, '$99,900.99')
FROM product;
SELECT TO_CHAR(price, '$99,911.99')
FROM product;
SELECT TO_CHAR(price, '$99,990.99')
FROM product;
SELECT TO_NUMBER(price, '$99,900.99')
FROM product;
25. Which best describes the TO_CHAR function?
The TO_CHAR function can be used to specify meaningful column names in an SQL statement's result set.
The TO_CHAR function can be used to remove text from column data that will be returned by the database.
The TO_CHAR function can be used to display dates and numbers according to formatting conventions that are supported by Oracle.
The TO_CHAR function can only be used on Date columns.
26. Nonequijoins are normally used with which of the following? (Choose two)
Ranges of numbers
Ranges of text
Ranges of dates
Ranges of rowids
Ranges of columns
27. Which of the following best describes the function of an outer join?
An outer join will return only those rows that do not meet the join criteria.
An outer join will return only data from the far left column in one table and the far right column in the other table.
An outer join will return data only if both tables contain an identical pair of columns.
An outer join will return all rows that meet the join criteria and will return NULL values from one table if no rows from the other table satisfy the join criteria.
28. Which statement about outer joins is true?
The tables must be aliased.
The FULL, RIGHT, or LEFT keyword must be included.
The OR operator cannot be used to link outer join conditions.
Outer joins are always evaluated before other types of joins in the query.
29. The EMPLOYEE_ID column in the EMPLOYEES table corresponds to the EMPLOYEE_ID column of the ORDERS table. The EMPLOYEE_ID column in the ORDERS table contains null values for rows that you need to display.
Which type of join should you use to display the data?
Natural join
Self-join
Outer join
Equijoin
30. Which statement about a self join is true?
The NATURAL JOIN clause must be used.
Table aliases must be used to qualify table names.
Table aliases cannot be used to qualify table names.
A self join must be implemented by defining a view.
31. You need to provide a list of the first and last names of all employees who work in the Sales department who earned a bonus and had sales over $50,000. The company president would like the sales listed starting with the highest amount first. The EMPLOYEES table and the SALES_DEPT table contain the following columns:
EMPLOYEES
EMPLOYEE_ID NUMBER(10) PRIMARY KEY
LAST_NAME VARCHAR2(20)
FIRST_NAME VARCHAR2(20)
DEPARTMENT_ID VARCHAR2(20)
HIRE_DATE DATE
SALARY NUMBER(10)
SALES_DEPT
SALES_ID NUMBER(10) PRIMARY KEY
SALES NUMBER(20)
QUOTA NUMBER(20)
MANAGER VARCHAR2(30)
BONUS NUMBER(10)
EMPLOYEE_ID NUMBER(10) FOREIGN KEY
Which SELECT statement will accomplish this task?
SELECT e.employee_id, e.last_name, e.first_name, s.employee_id, s.bonus, s.sales
FROM employees e, sales_dept s
ORDER BY sales DESC
WHERE e.employee_id = s.employee_id AND sales > 50000 AND s.bonus IS NOT NULL;
SELECT e.employee_id, e.last_name, e.first_name, s.employee_id, s.bonus, s. sales
ORDER BY sales DESC
FROM employees e, sales_dept s
WHERE e.employee_id = s.employee_id AND s.bonus IS NOT NULL AND sales > 50000;
SELECT e.employee_id, e.last_name, e.first_name, s.employee_id, s.bonus, s. sales
WHERE e.employee_id = s.employee_id
FROM employees e, sales_dept s AND s.bonus IS NOT NULL AND sales > 50000
ORDER BY sales DESC;
SELECT e.employee_id, e.last_name, e.first_name, s.employee_id, s.bonus, s. sales
FROM employees e, sales_dept s
WHERE e.employee_id = s.employee_id AND s.bonus IS NOT NULL AND sales > 50000
ORDER BY sales DESC;
32. The PATIENTS and DOCTORS tables contain these columns:
PATIENTS
PATIENT_ID NUMBER(9)
LAST_NAME VARCHAR2 (20)
FIRST_NAME VARCHAR2 (20)
DOCTORS
DOCTOR_ID NUMBER(9)
LAST_NAME VARCHAR2 (20)
FIRST_NAME VARCHAR2 (20)
You issue this statement:
SELECT patient_id, doctor_id
FROM patients, doctors;
Which result will this statement provide?
A report containing all possible combinations of the PATIENT_ID and DOCTOR_ID values
A report containing each patient's id value and their doctor's id value
A report with NO duplicate PATIENT_ID or DOCTOR_ID values
A syntax error
33. What happens when you create a Cartesian product?
All rows from one table are joined to all rows of another table
The table is joined to itself, one column to the next column, exhausting all possibilities
The table is joined to another equal table
All rows that do not match in the WHERE clause are displayed
34. What is the minimum number of join conditions required to join 5 tables together?
3
4
5
One more than the number of tables
35. You need to create a report that lists all employees in department 10 (Sales) whose salary is not equal to $25,000 per year. Which query should you issue to accomplish this task?
SELECT last_name, first_name, salary
FROM employees
WHERE salary > 25000 AND department_id = 10;
SELECT last_name, first_name, salary
FROM employees
WHERE salary = 25000 AND department_id = 10;
SELECT last_name, first_name, salary
FROM employees
WHERE salary <= 25000 AND department_id = 10;
SELECT last_name, first_name, salary
FROM employees
WHERE salary != 25000 AND department_id = 10;
36. You have two tables named EMPLOYEES and SALES. You want to identify the sales representatives who have generated $100,000, or more, in revenue. Which query should you issue?
SELECT e.first_name, e.last_name, s.sales
FROM employees e, sales s
WHERE e.employee_id = s.employee_id AND revenue > 100000;
SELECT e.first_name, e.last_name, s.sales
FROM employees e, sales s
WHERE e.employee_id = s.employee_id AND revenue >= 100000;
SELECT e.first_name, e.last_name, s.sales
FROM employees, sales
WHERE e.employee_id = s.employee_id AND revenue >= 100000;
SELECT first_name, last_name, sales
FROM employees e, sales s
WHERE e.employee_id = s.employee_id AND revenue > 100000;
37. Which type of join returns rows from one table that have NO direct match in the other table?
Equijoin
Self join
Outer join
Natural join
38. Which query represents the correct syntax for a left outer join?
SELECT companyname, orderdate, total
FROM customers c
LEFT JOIN orders o
ON c.cust_id = o.cust_id;
SELECT companyname, orderdate, total
FROM customers c
OUTER JOIN orders o
ON c.cust_id = o.cust_id;
SELECT companyname, orderdate, total
FROM customers c
LEFT OUTER JOIN orders o
ON c.cust_id = o.cust_id;
SELECT companyname, orderdate, total
FROM customers c
LEFT OUTER orders o
ON c.cust_id = o.cust_id;
39. What should be included in a SELECT statement to return NULL values from all tables?
Natural joins
Left outer joins
Full outer joins
Right outer joins
40. 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?
Natural join
Cross join
Outer join
Self-join
41. Which statement about a natural join is true?
Columns with the same names must have identical data types.
Columns with the same names must have the same precision and datatype.
Columns with the same names must have compatible data types.
Columns with the same names cannot be included in the SELECT list of the query.
42. Which of the following best describes a natural join?
A join between two tables that includes columns that share the same name, datatypes and lengths
A join that produces a Cartesian product
A join between tables where matching fields do not exist
A join that uses only one table
43. For which condition would you use an equijoin query with the USING keyword?
You need to perform a join of the CUSTOMER and ORDER tables but limit the number of columns in the join condition.
The ORDER table contains a column that has a referential constraint to a column in the PRODUCT table.
The CUSTOMER and ORDER tables have no columns with identical names.
The CUSTOMER and ORDER tables have a corresponding column, CUST_ID. The CUST_ID column in the ORDER table contains null values that need to be displayed.
44. Below find the structure of the CUSTOMERS and SALES_ORDER tables:
CUSTOMERS
CUSTOMER_ID NUMBER NOT NULL, Primary Key
CUSTOMER_NAME VARCHAR2 (30)
CONTACT_NAME VARCHAR2 (30)
CONTACT_TITLE VARCHAR2 (20)
ADDRESS VARCHAR2 (30)
CITY VARCHAR2 (25)
REGION VARCHAR2 (10)
POSTAL_CODE VARCHAR2 (20)
COUNTRY_ID NUMBER Foreign key to COUNTRY_ID column of the COUNTRY table
PHONE VARCHAR2 (20)
FAX VARCHAR2 (20)
CREDIT_LIMIT NUMBER(7,2)
SALES_ORDER
ORDER_ID NUMBER NOT NULL, Primary Key
CUSTOMER_ID NUMBER Foreign key to CUSTOMER_ID column of the CUSTOMER table
ORDER_DT DATE
ORDER_AMT NUMBER (7,2)
SHIP_METHOD VARCHAR2 (5)
You need to create a report that displays customers without a sales order. Which statement could you use?
SELECT c.customer_name
FROM customers c
WHERE c.customer_id not in (SELECT s.customer_id FROM sales_order s);
SELECT c.customer_name
FROM customers c, sales_order s
WHERE c.customer_id = s.customer_id(+);
SELECT c.customer_name
FROM customers c, sales_order s
WHERE c.customer_id (+) = s.customer_id;
SELECT c.customer_name
FROM customers c
RIGHT OUTER JOIN sales_order s
ON (c.customer_id = s.customer_id);
45. The primary advantages of using JOIN ON is: (Select two)
The join happens automatically based on matching column names and data types.
It will display rows that do not meet the join condition.
It permits columns with different names to be joined.
It permits columns that don’t have matching data types to be joined.
46. Evaluate this SELECT statement:
SELECT a.lname || ', ' || a.fname as "Patient", b.lname || ', ' || b.fname as "Physician", c.admission
FROM patient a
JOIN physician b
ON (b.physician_id = c.physician_id)
JOIN admission c
ON (a.patient_id = c.patient_id);
JOIN physician b
ON (b.physician_id = c.physician_id);
JOIN admission c
ON (a.patient_id = c.patient_id)
47. Evaluate this SELECT statement:
SELECT MIN(hire_date), department_id
FROM employees
GROUP BY department_id;
Which values are displayed?
The earliest hire date in each department.
The earliest hire date in the EMPLOYEES table.
The latest hire date in the EMPLOYEES table.
The hire dates in the EMPLOYEES table that contain NULL values.
48. Group functions can be nested to a depth of?
Three
Four
Two
Group functions cannot be nested.
49. Evaluate this SELECT statement:
SELECT MAX(salary), department_id
FROM employees
GROUP BY department_id;
Which What values are displayed?
The highest salary for all employees.
The highest salary in each department.
The employees with the highest salaries.
The employee with the highest salary for each department.
50. What will the following SQL Statement do?
SELECT job_id, COUNT(*)
FROM employees
GROUP BY job_id;
Displays all the employees and groups them by job.
Displays each job id and the number of people assigned to that job id.
Displays only the number of job_ids.
Displays all the jobs with as many people as there are jobs.
