Premium

Oracle Advanced SQL and PL/SQL Developer Certification Questions and Answers (Dumps and Practice Questions)



Question : Which normal form is a table in if it has no multi-valued attributes and no partial dependencies?
: Which normal form is a table in if it has no multi-valued attributes and no partial dependencies?
1. First normal form
2. Second normal form
 3. Access Mostly Uused Products by 50000+ Subscribers
4. Fourth normal form


Correct Answer : Get Lastest Questions and Answer :

Explanation: A table that is in first normal form (1NF) must meet additional criteria if it is to qualify for second normal form. Specifically: a table is in 2NF if and only if it is in 1NF and no non-prime attribute is dependent on any proper subset of any candidate key of the table. A non-prime attribute of a table is an attribute that is not a part of any candidate key of the table. Put simply, a table is in 2NF if and only if it is in 1NF and every non-prime attribute of the table is dependent on the whole of every candidate key. Consider a table describing employees' skills:
Employees' Skills
Employee Skill Current Work Location
Brown Light Cleaning 73 Industrial Way
Brown Typing 73 Industrial Way
Jones Shorthand 114 Main Street
Jones Typing 114 Main Street
Neither {Employee} nor {Skill} is a candidate key for the table. This is because a given Employee might need to appear more than once (he might have multiple Skills), and a given Skill might need to appear more than once (it might be possessed by multiple Employees). Only the composite key {Employee, Skill} qualifies as a candidate key for the table. The remaining attribute, Current Work Location, is dependent on only part of the candidate key, namely Employee. Therefore the table is not in 2NF. Note the redundancy in the way Current Work Locations are represented: we are told three times that Jones works at 114 Main Street, and twice that Brown works at 73 Industrial Way. This redundancy makes the table vulnerable to update anomalies: it is, for example, possible to update Jones' work location on his "Shorthand" and "Typing" records and not update his "Whittling" record. The resulting data would imply contradictory answers to the question "What is Jones' current work location?", unless it is meant that Jones excercises different skills at different locations. A 2NF alternative to this design would represent the same information in two tables: an "Employees" table with candidate key {Employee}, and an "Employees' Skills" table with candidate key {Employee, Skill}:
Employees
Employee Current Work Location
Brown 73 Industrial Way
Harrison 73 Industrial Way
Jones 114 Main Street

Employees' Skills
Employee Skill
Brown Light Cleaning
Brown Typing
Harrison Light Cleaning
Jones Shorthand
Jones Typing
Jones Whittling
Neither of these tables can suffer from update anomalies. Not all 2NF tables are free from update anomalies




Question : Examine the types and examples of relationships that follow:
1. One-to-one a) Teacher to students
2. One-to-many b) Employees to Manager
 3. Access Mostly Uused Products by 50000+ Subscribers
4. Many-to-many d) Customers to products
Which option indicates the correctly matched relationships?
: Examine the types and examples of relationships that follow:
1. 1-a, 2-b, 3-c, and 4-d
2. 1-c, 2-d, 3-a, and 4-b
 3. Access Mostly Uused Products by 50000+ Subscribers
4. 1-d, 2-b, 3-a, and 4-c

Correct Answer : Get Lastest Questions and Answer :


Explanation: In database design, the cardinality or fundamental principle of one data table with respect to another is a critical aspect. The relationship of one to the other must be precise and exact between each other in order to explain how each table links together.

In the relational model, tables can be related as any of "one-to-many" or "many-to-many." This is said to be the cardinality of a given table in relation to another.

For example, consider a database designed to keep track of hospital records. Such a database could have many tables like:

a doctor table with information about physicians;
a patient table for medical subjects undergoing treatment;
and a department table with an entry for each division of a hospital.
In that model:

There is a many-to-many relationship between the records in the doctor table and records in the patient table because doctors have many patients, and a patient could have several doctors;
A one-to-many relation between the department table and the doctor table because each doctor may work for only one department, but one department could have many doctors.
A "one-to-one" relationship is mostly used to split a table in two in order to provide information concisely and make it more understandable. In the hospital example, such a relationship could be used to keep apart doctors' own unique professional information from administrative details.

In data modeling, collections of data elements are grouped into "data tables" which contain groups of data field names called "database attributes". Tables are linked by "key fields". A "primary key" assigns a field to its "special order table". For example, the "Doctor Last Name" field might be assigned as a primary key of the Doctor table with all people having same last name organized alphabetically according to the first three letters of their first name. A table can also have a foreign key which indicates that field is linked to the primary key of another table.

A complex data model can involve hundreds of related tables. A renowned computer scientist, C.J. Date, created a systematic method to organize database models. Date's steps for organizing database tables and their keys is called Database Normalization. Database normalization avoids certain hidden database design errors (delete anomalies or update anomalies). In real life the process of database normalization ends up breaking tables into a larger number of smaller tables, so there are common sense data modeling tactics called de-normalization which combine tables in practical ways.

In real world data models careful design is critical because as the data grows voluminous, tables linked by keys must be used to speed up programmed retrieval of data. If data modeling is poor, even a computer applications system with just a million records will give the end-users unacceptable response time delays. For this reason data modeling is a keystone in the skills needed by a modern software developer.







Question : You execute the givne sql commands:
For which substitution variables are you prompted for the input?
: You execute the givne sql commands:
1. None, because no input required
2. Both the substitution variables 'hiredate' and 'mgr_id'
 3. Access Mostly Uused Products by 50000+ Subscribers
4. Only 'mgr_id'


Correct Answer : Get Lastest Questions and Answer :

Explanation: Both single ampersand (&) and double ampersand (&&) can prefix a substitution variable name in a statement. SQL*Plus pre-processes the statement and substitutes the variable's value. The statement is then executed. If the variable was not previously defined then SQL*Plus prompts you for a value before doing the substitution. If a single ampersand prefix is used with an undefined variable, the value you enter at the prompt is not stored. Immediately after the value is substituted in the statement the value is discarded and the variable remains undefined. If the variable is referenced twice, even in the same command, then you are prompted twice. Different values can be entered at each prompt:

SQL> prompt Querying table &mytable
Enter value for mytable: employees
Querying table employees
SQL> select employee_id from &mytable where last_name = 'Jones;
Enter value for mytable: employees
EMPLOYEE_ID
-----------
195 If a double ampersand reference causes SQL*Plus to prompt you for a value, then SQL*Plus defines the variable as that value. Any subsequent reference to the variable (even in the same command) using either "&" or "&&" substitutes the newly defined value. SQL*Plus will not prompt you again: SQL> prompt Querying table &&mytable
Enter value for mytable: employees
Querying table employees
SQL> select employee_id from &mytable where last_name = 'Jones;
EMPLOYEE_ID
-----------
195 2.5 Storing a Query Column Value in a Substitution Variable
Data stored in the database can be put into substitution variables: SQL> column last_name new_value mynv
SQL> select last_name from employees where employee_id = 100; The NEW_VALUE option in the COLUMN command implicitly creates a substitution variable called "mynv". When the SELECT finishes, the variable "mynv" holds the last retrieved value from column "last_name": SQL> define mynv
DEFINE mynv = "King" (CHAR) Predefined Substitution Variables : The predefined substitution variables created when you start SQL*Plus can be seen by entering DEFINE with no arguments. Each predefined variable is prefixed with an underscore. The predefined variables can be undefined or redefined just like user defined substitution variables.




Related Questions

Question : How can you delete the values from one column of every row in a table?
: How can you delete the values from one column of every row in a table?
1. Use the DELETE COLUMN command.
2. Use the TRUNCATE COLUMN command.
3. Access Mostly Uused Products by 50000+ Subscribers
4. Use the DROP COLUMN command.




Question : Which two statements are true regarding constraints?
A. A table can have only one primary key and one foreign key.
B. A table can have only one primary key but multiple foreign keys.
C. Only the primary key can be defined at the column and table levels.
D. The foreign key and parent table primary key must have the same name.
E. Both primary key and foreign key constraints can be defined at both column and table levels.
 : Which two statements are true regarding constraints?
1. A,C
2. B,D
3. A,E
4. C,D
5. B,E



Question : In which three situations does a transaction complete?
A. When a DELETE statement is executed
B. When a ROLLBACK command is executed
C. When a PL/SQL anonymous block is executed
D. When a data definition language (DDL) statement is executed
E. When a TRUNCATE statement is executed after the pending transaction
 : In which three situations does a transaction complete?
1. A,B,C
2. A,C,E
3. A,D,E
4. B,C,E
5. B,D,E


Question : Examine the structure of the orders table:
Which statement is true regarding the outcome?
 : Examine the structure of the orders table:
1. It executes successfully and gives the correct output.
2. It gives an error because the TO_CHAR function is not valid.
3. It executes successfully but does not give the correct output.
4. It gives an error because the data type conversion in the SELECT list does not match the data type conversion in the GROUP BY clause.