1. Suppose that a PRODUCT table contains two attributes, PROD_CODE and VEND_CODE. Those two attributes have values of ABC, 125, DEF, 124, GHI, 124, and JKL, 123, respectively. The VENDOR table contains a single attribute, VEND_CODE, with values 123, 124, 125, and 126, respectively. (The VEND_CODE attribute in the PRODUCT table is a foreign key to the VEND_CODE in the VENDOR table.) Given that information, what would be the query output for a INTERSECT query based on these two tables?
a. The query output will be: 125,124,123,126
b. The query output will be: 123
c. The query output will be: 125,124,124,123,123,124,125,126
d. The query output will be: 123,124,125
2. What is the difference between UNION and UNION ALL?
a. A UNION ALL operator will yield all rows of both relations, including duplicates
b. UNION yields unique rows
c. UNION eliminates duplicates rows
d. All of these choices are correct.
3. A(n) ______________ is a block of PL/SQL code that is automatically invoked by the DBMS upon the occurrence of a data manipulation event (INSERT, UPDATE or DELETE.)
a. stored procedure
b. trigger
c. view
d. function
4. __________________ means that the relations yield attributes with identical names and compatible data types.
a. duplicated
b. Set comparable
c. Union compatible
d. compatible-oriented
5. Which of the following a parts of the definition of a trigger?
a. The triggering level
b. The triggering action
c. The triggering timing
d. All of these choices are correct.
6. Which of the following relational set operators does NOT require that the relations are union-compatible?
a. INTERSECT
b. PROJECT
c. MINUS
d. UNION
7. Suppose that you have two tables, EMPLOYEE and EMPLOYEE_1. The EMPLOYEE table contains the records for three employees: Alice Cordoza, John Cretchakov, and Anne McDonald. The EMPLOYEE_1 table contains the records for employees John Cretchakov and Mary Chen. Given that information, what is the query output for the INTERSECT query?
a. The query output will be: John Cretchakov and Mary Chen
b. The query output will be: Alice Cordoza, John Cretchakov, Anne McDonald and Mary Chen
c. The query output will be: John Cretchakov
d. The query output will be: Alice Cordoza, John Cretchakov, Anne McDonald, John Cretchakov and Mary Chen
8. A _____________________ is a join that performs a relational product (or Cartesian product) of two tables.
a. CROSS JOIN
b. DUPLICATE JOIN
c. OUTER JOIN
d. INNER JOIN
9. What Oracle function should you use to calculate the number of days between the current date and January 25, 1999?
a. SELECT SYSDATE()-#25-JAN-1999#;
b. SELECT SYSDATE - TO_DATE('25-JAN-1999', 'DD-MON-YYYY') FROM DUAL;
c. SELECT SYSDATE FROM DUAL;
d. SELECT SYSDATE()-JAN-#25-1999#;
10 . Using tables named T1 and T2, write a query example for a LEFT OUTER JOIN, assuming that T1 and T2 share a common column named C1.
a. SELECT * FROM T1 OUTER JOIN T2 ON T1.C1 = T2.C2;
b. SELECT * FROM T1 JOIN T2 ON T2.C1 = T2.C1;
c. SELECT * FROM T1 LEFT JOIN T2 ON T1.C1 = T2.C1;
d. SELECT * FROM T1 LEFT OUTER JOIN T2 ON T1.C1 = T2.C1;
11. Suppose that you have two tables, EMPLOYEE and EMPLOYEE_1. The EMPLOYEE table contains the records for three employees: Alice Cordoza, John Cretchakov, and Anne McDonald. The EMPLOYEE_1 table contains the records for employees John Cretchakov and Mary Chen. Given that information, what is the query output for the MINUS query (specifically, SELECT * FROM EMPLOYEE MINUS SELECT * FROM EMPLOYEE_1)?
a. The query output will be: Alice Cordoza, John Cretchakov, Anne McDonald and Mary Chen
b. The query output will be: Alice Cordoza, John Cretchakov, Anne McDonald, John Cretchakov and Mary Chen
c. The query output will be: Mary Chen
d. The query output will be: Alice Cordoza and Anne McDonald
12. What Oracle function should you use to return the current date?
a. TODAY
b. SYSDATE
c. NOW
13. Suppose that a PRODUCT table contains two attributes, PROD_CODE and VEND_CODE. Those two attributes have values of ABC, 125, DEF, 124, GHI, 124, and JKL, 123, respectively. The VENDOR table contains a single attribute, VEND_CODE, with values 123, 124, 125, and 126, respectively. (The VEND_CODE attribute in the PRODUCT table is a foreign key to the VEND_CODE in the VENDOR table.) Given that information, what would be the query output for a UNION ALL query based on these two tables?
a. The query output will be: 125,124,124,123,123,124,125,126
b. The query output will be: 125,124,124,123
c. The query output will be: 125,124,123,126
d. The query output will be: 123
14. _________________is a term used to refer to SQL statements that are contained within an application programming language such as COBOL, C++, ASP, Java, or ColdFusion.
a. NoSQL
b. Embedded SQL
c. Dynamic SQL
15. The order of the operands (tables) matter in a _______ query.
a. PROJECT
b. MINUS
c. UNION
d. INTERSECT
16. Which of the following is true of Oracle sequences?
a. You can have as many sequences as you want and they are not tied to any particular table.
b. A sequence is a completely independent object.
c. The sequence-generated value is not tied to any field in any table and can, therefore, be used on any attribute in any table.
17. Which of the following is true of Oracle sequences?
a. You can have as many sequences as you want and they are not tied to any particular table.
b. A sequence is a completely independent object.
c. The sequence-generated value is not tied to any field in any table and can, therefore, be used on any attribute in any table.
d. All of these choices are correct.
18. A(n) ______________ is a named collection of procedural and SQL statements that are stored in the database and that can be used to encapsulate and represent business transactions.
a. trigger
b. stored procedure
c. view
19. Suppose that you have two tables, EMPLOYEE and EMPLOYEE_1. The EMPLOYEE table contains the records for three employees: Alice Cordoza, John Cretchakov, and Anne McDonald. The EMPLOYEE_1 table contains the records for employees John Cretchakov and Mary Chen. Given that information, what is the query output for the UNION query?
a. The query output will be: Alice Cordoza, John Cretchakov, Anne McDonald and Mary Chen
b. The query output will be: John Cretchakov and Mary Chen
c. The query output will be: Alice Cordoza, John Cretchakov, Anne McDonald, John Cretchakov and Mary Chen
d. The query output will be: John Cretchakov
20. A subquery can appear in which of the following places in a SQL statement?
a. as part of a FROM clause,
b. to the right of a HAVING clause conditional operator,
c. in a EXISTS operator,
d. All of these choices are correct.
21. A __________ is a query (expressed as a SELECT statement) that is located inside another query and is normally executed first.
a. multi-query
b. subquery
c. outside query
d. special query
22. What string function (in Oracle) should you use to list the first three characters of a company's EMP_LNAME values using a table named EMPLOYEE?
a. SELECT STR(EMP_LNAME, 1, 3) FROM EMPLOYEE;
b. SELECT STRING(EMP_LNAME, 1, 3) FROM EMPLOYEE;
c. SELECT SUBSTRING(EMP_LNAME, 1, 3) FROM EMPLOYEE;
d. SELECT SUBSTR(EMP_LNAME, 1, 3) FROM EMPLOYEE;
23. _____________ is a term used to describe an environment in which the SQL statement is not known in advance; instead, the SQL statement is generated at run time.
a. PL/SQL
b. Embedded SQL
c. Dynamic SQL
d. NoSQL
24. Suppose that a PRODUCT table contains two attributes, PROD_CODE and VEND_CODE. Those two attributes have values of ABC, 125, DEF, 124, GHI, 124, and JKL, 123, respectively. The VENDOR table contains a single attribute, VEND_CODE, with values 123, 124, 125, and 126, respectively. (The VEND_CODE attribute in the PRODUCT table is a foreign key to the VEND_CODE in the VENDOR table.) Given that information, what would be the query output for a MINUS query (VENDOR MINUS PRODUCT) based on these two tables?
a. The query output will be: 124
b. The query output will be: 125,124,123,126
c. The query output will be: NULL
d. The query output will be: 126
25. In the relational model, SQL operators are ________________ because they operate over entire sets of rows and columns (or tables) at once.
a. set-oriented
b. table-oriented
c. completeness-oriented
26. Which of the followings explains the difference between a regular subquery and a correlated subquery?
a. A correlated subquery will execute once for each row evaluated by the outer query
b. A regular subquery executes only once and the result is held for use by the outer query
c. A regular subquery executes before the outer query
d. All of these choices are correct.
27. If you do not specify a join condition when joining tables, the result will be a ______________ or PRODUCT operation.
a. CROSS JOIN
b. DUPLICATE JOIN
c. INNER JOIN
d. OUTER JOIN
28. A ______________ is a subquery that executes once for each row in the outer query; it will run the outer query first, and then it will run the inner subquery once for each row returned in the outer subquery.
a. outside subquery
b. uncorrelated subquery
c. correlated subquery
29. A relational view has which of the following characteristics?
a. views are dynamically updated
b. views may be used as the basis for reports or queries, just like tables
c. views provide a level of security in the database
30. A(n) ______________ is a special type of object that generates unique numeric values in ascending or descending order; it can be used to assign values to a primary key field in a table and it provides functionality similar to the Autonumber data type in MS Access.
a. sequence
b. key
c. instance
d. view
31. which of the following is NOT considered an advantage of a stored procedure?
a. Increase performance
b. Help reduce code duplication through code isolation and code sharing
c. Reduce network traffic
d. None of these choices are correct.
32. What are the types of results a subquery can return?
a. a list of values only
b. a single value, a list of values, or a virtual table
c. a single value only
d. a virtual table only
33. The SQL standard prescribes three different types of __________ operations: LEFT, RIGHT, and FULL.
a. OUTER join
b. INNER join
c. DUPLICATE join
34. A(n) __________________ is a type of JOIN operation that yields all rows with matching values in the join columns as well as all unmatched rows ( those without matching values in the join columns).
a. INNER JOIN
b. OUTER JOIN
c. CROSS JOIN
d. DUPLICATE JOIN
35. Suppose that a PRODUCT table contains two attributes, PROD_CODE and VEND_CODE. Those two attributes have values of ABC, 125, DEF, 124, GHI, 124, and JKL, 123, respectively. The VENDOR table contains a single attribute, VEND_CODE, with values 123, 124, 125, and 126, respectively. (The VEND_CODE attribute in the PRODUCT table is a foreign key to the VEND_CODE in the VENDOR table.) Given that information, what would be the query output for a UNION query based on these two tables?
a. The query output will be: 123,124,125,126
b. The query output will be: 125,124,124,123
c. The query output will be: 125,124,123,126
d. The query output will be: 123
36. Triggers are critical to proper database operation and management in which of the following ways?
a. add functionality by automating critical actions and providing appropriate warnings for remedial action.
b. used to enforce constraints that cannot be enforced at the DBMS design and implementation levels.
c. can be used to enforce referential integrity, to update table values, insert records in tables, and call other stored procedures.
d. All of these choices are correct.
37. A(n) ________ is a virtual table based on a SELECT query.
a. view
b. copy
c. schema
38. Suppose that you have two tables, EMPLOYEE and EMPLOYEE_1. The EMPLOYEE table contains the records for three employees: Alice Cordoza, John Cretchakov, and Anne McDonald. The EMPLOYEE_1 table contains the records for employees John Cretchakov and Mary Chen. Given that information, what is the query output for the UNION ALL query?
a. The query output will be: Alice Cordoza, John Cretchakov, Anne McDonald and Mary Chen
b. The query output will be: John Cretchakov and Mary Chen
c. The query output will be: John Cretchakov
d. The query output will be: Alice Cordoza, John Cretchakov, Anne McDonald, John Cretchakov and Mary Chen
39. The ______________________ will yield all rows with matching values in the join columns, plus all of the unmatched rows from the right table.
a. CENTER OUTER JOIN
b. RIGHT OUTER JOIN
c. LEFT OUTER JOIN
d. FULL OUTER JOIN
Attachment:- Document.rar