IS 410 Final Study Guide

What is true about the JOIN..ON clause in Oracle DB? A. It does not depend on the columns in the source and target tables having identical names B. Only those columns from the source and the target tables which have identical names can be used with this clause C. It is a format of the NATURAL JOIN D. All of the above

: A, C. The join condition for the natural join is basically an equijoin of all columns with the same name. Use the ON clause to specify arbitrary conditions or specify columns to join. The join condition is separated from other search conditions. The ON clause makes code easy to understand.

Identify the capabilities of SELECT statement. A. Projection B. Selection C. Data Control D. Transaction

A & B. The SELECT statement can be used for selection, projection and joining.

2. Determine the capability of the SELECT statement demonstrated in the given query. SELECT e.ename, d.dname FROM emp e, dept d WHERE e.deptno = d.deptno AND e.sal > 1000; A. Selection B. Filtering C. Joining D. Projection

A, C, D. Projection is including only the required columns in query, while Selection is selecting only the required data. Joining means combining two tables together through a connecting column.

.In which two cases an OUTER JOIN should be used? A. If the joined tables' columns have NULL values B. If the joined tables have NOT NULL columns C. If the joined tables have only un-matched data D. If the joined tables have both matching as well as non-matching data

A, D. An outer join is created when records need to be included in the results without having corresponding records in the join tables. These records are matched with NULL records so that they're included in the output.

Which of the following are DML commands in Oracle Database? A. SELECT B. GROUP BY C. INTERSECT D. INSERT

A, D. On strict grounds, SELECT is a DML command as it is one of the mandatory clauses for manipulation of data present in tables.

What among the following is a typical use of an UPDATE statement? (Select the most appropriate answer) A. To retrieve a row and update one of more columns of that row B. To modify all the rows for some columns C. To modify all the rows for all the columns of a table D. None of the above

A. Although, the UPDATE statement can modify all column values in all rows, but typically it is used to select a row and update one or more columns.

You issue the following command to the data set shown above: INSERT INTO EMPLOYEES (employee_id , first_name , job_id) VALUES (51003,'BRUCE','CLERK'); What will be the output of this statement? A. It will insert a new row with 51003 BRUCE CLERK values B. It will throw an ORA error as there cannot be another BRUCE who is a CLERK C. It will throw an 'Constraint violated' ORA error D. None of the above

A. As there is no constraint on the columns FIRST_NAME and job_id, the INSERT will work without any error

You need to change the JOB_ID for Bruce (Employee Id 7389) to 'ACCOUNTANT'. Which of the following statements will you fire? A. UPDATE employees B. SET job_id = 'ACCOUNTANT' C. WHERE employee_id = 7389; D. INSERT INTO EMPLOYEES (employee_id , first_name , job_id ) VALUES (5100,'BRUCE', 'ACCOUNTANT'); E. UPDATE employees F. SET job_id = 'ACCOUNTANT' G. WHERE job_id = 'CLERK'; H. UPDATE employees I. SET job_id = 'ACCOUNTANT';

A. Option B fails because it modifies the job code of all clerks to ACCOUNTANT. Option C is wrong because it update job code to ACCOUNTANT for all the employees in the table.

. Which of the following commands allows undoing the changed data? A. ROLLBACK B. COMMIT C. INSERT D. UPDATE

A. The TCL command ROLLBACK is used to end the current active transaction in a session by discarding all the pending data changes.

What will be the outcome of the following query? SELECT TO_DATE('01 ## JAN / 13','DD-MON-YY') FROM dual; A. ORA error B. 01-JAN-2013 C. 01-JANUARY-13 D. 01-JAN-13

A. Use a single delimiter between the dates.

What is true when multiple joins are used in an SQL statement? A. The joins are evaluated from left to right B. The joins are evaluated from right to left C. There is no precedence in the process of the evaluation of joins D. None of the above

A. When multiple-joins exist in a statement, they are evaluated from left to right.

Chose the statements which correctly define a NULL value. A. NULL is a special value with zero bytes B. NULL is no value or unknown value C. NULL is represented by a blank space D. NULL is not same as zero

B & D. NULL is NO VALUE but neither same as zero nor as blank or space character.

Which of the following commands manipulate data basically? A. MINUS B. UPDATE C. TRUNCATE D. All of the above

B, C. UPDATE is a DML statement to modify a column value in a table. TRUNCATE manipulates the data by removing them unconditionally from a table.

Determine the output of the below query - SELECT '5+7' FROM dual; A. 12 B. 5+7 C. 5 D. 7

B, Oracle treats the values within double quotes as string expressions.

. You issue an UPDATE statement to update the employee id 7389 to 7900. You query the employee by its id '7389' before committing the transaction. What will be the outcome? A. Update will work successfully while select will show 7389. B. Update will work successfully while select will show no records. C. Constraint on EMPLOYEE_ID will not allow it to be updated D. It will update successfully but all the values for the EMPLOYEE_ID 7389 will become NULL.

B. A query in a session is consistent with the ongoing transactions. If the same query would have been executed in a different session, it would have shown the employee record with id 7389 because the active transaction in the first session is not yet committed.

Which of the following commands is used to populate table rows with data? A. DELETE B. INSERT C. SELECT D. UPDATE

B. INSERT command is used to insert rows in a table.

Which of the following commands is used to save the changed data in a table permanently? A. ROLLBACK B. COMMIT C. INSERT D. UPDATE

B. The TCL command COMMIT is used to end the current active transaction in a session by making all the pending data changes permanent in the tables.

Suppose you fire an UPDATE statement to update Bruce's JOB_ID to 'SALESMAN' (with respect to the data set shown above). What will be the outcome of the query? A. Bruce's job code will still be NULL B. Bruce's job code will be modified to 'SALESMAN' C. ORA error D. No action

B. The UPDATE will add the new value to the NULL value changing the NULL to the new value

Which of the following clauses decides how many rows are to be updated? A. SELECT B. WHERE C. FROM D. All of the above

B. UPDATE statement makes use of WHERE clause to capture the set of rows which needs to be updated.

Which of the following clause is used to suppress duplicates in a SELECT statement? A. INTERSECT B. DUPLICATE C. DISTINCT D. UNIQUE

C, D. Duplicate data can be restricted with the use of DISTINCT or UNIQUE in the SELECT statement.

What is an INNER JOIN in Oracle DB? A. The join giving the matching records between two tables is called an INNER JOIN B. An inner join can use operators like <,>, <> C. Both A and B D. None of the above

C. A join can be an inner join, in which the only records returned have a matching record in all tables, or an outer join, in which records can be returned regardless of whether there's a matching record in the join.

.What is true about the UPDATE command? A. It can update only one row at a time B. It can update only 100 rows at a time C. It can update unlimited rows at a time in bulk D. None of the above

C. An UPDATE can update multiple rows in one or more rows at a time based on the WHERE clause conditions.

Examine the structure of the EMPLOYEES table. You issue the following command: INSERT INTO EMPLOYEES (employee_id , first_name , job_id) VALUES (5100, 'BRUCE', 'CLERK'); Assuming that there is a duplicate value check constraint on the EMPLOYEE_ID column, what will be the outcome of the above statement? A. It will insert another row with 5100 BRUCE CLERK values B. It will insert another row with 51002 BRUCE CLERK values C. It will throw a 'Constraint violated' ORA error D. None of the above

C. As the row with values "5100, BRUCE, CLERK" already exists in the table, the insert statement with same data set is not possible.

What does ACID mean with respect to relational database? A. Accuracy, Consistency, Isolation, Database B. Accuracy, Concurrency, Isolation, Durability C. Atomicity, Consistency, Isolation, Durability D. Atomicity, Concurrency, Isolation, Durability

C. Atomicity, Consistency, Isolation, Durability

What does the word DML stands for in Oracle SQL? A. Durability Management Language B. Database Management Language C. Database Manipulation Language D. None of the above

C. DML stands for Data Manipulation Language.

What is true about data types in Oracle DB? A. They are given to columns for sorting purposes. B. They are given to columns for a structured representation in a table. C. They are given to columns to constrain the nature of the data it can store. D. They are not mandatory.

C. Data types define the nature of data which a column can store in a table. A column can store only one type of data. The primary data types available in Oracle are NUMBER, VARCHAR2, and DATE.

What is the difference between a INNER JOIN and an EQUI-JOIN in Oracle DB? A. They are the same in terms of syntax and result sets obtained. B. An INNER JOIN is a subset of an EQUI-JOIN C. An INNER JOIN can use operators like <,>, <> along with "=" while EQUI-JOIN only uses the "=" operator D. All of the above

C. EQUI-JOIN is a type of INNER JOIN containing "=" operator in a join condition, whereas the INNER JOIN can contain both equality as well non-equality operators

Which of the following is not a format for Outer Joins in Oracle DB? A. Right B. Left C. Centre D. Full

C. Except 'Centre', rest 3 types are the types of formats of the Outer Joins in Oracle DB. With the JOIN method for outer joins, you can add the LEFT, RIGHT, or FULL keywords.

. Which of the following queries is valid? A. SELECT b.title, b.retail, o.quantity FROM books b NATURAL JOIN orders od NATURAL JOIN orderitems o WHERE od.order# = 1005; B. SELECT b.title, b.retail, o.quantity FROM books b, orders od, orderitems o WHERE orders.order# = orderitems.order# AND orderitems.isbn=books.isbn AND od.order#=1005; C. SELECT b.title, b.retail, o.quantity FROM books b, orderitems o WHERE o.isbn = b.isbn AND o.order#=1005; D. None of the above

C. If tables in the joins have alias, the selected columns must be referred with the alias and not with the actual table names.

Which of the following commands prevents other users from making changes to a table? A. ROLLBACK B. COMMIT C. LOCK TABLE D. SAVEPOINT

C. LOCK TABLE

Which of the following commands allows enabling markers in an active transaction? A. ROLLBACK B. COMMIT C. SAVEPOINT D. None of the above

C. SAVEPOINT marks a point in a transaction which divides the transaction into smaller sections.

What is true regarding a Self-Join in Oracle DB? A. Only two tables are required for the join to work B. The columns in the result set are obtained from two tables but are displayed in one table C. Conceptually, the source table duplicates itself to create the target table. (Oracle doesn't duplicate tables) D. All of the above

C. Self-joins are used when a table must be joined to itself to retrieve the data you need. Table aliases are required in the FROM clause to perform a self-join.

You need to find the results obtained by the above query only for the departments 100 and 101. Which of the following clauses should be added / modified to the above query? A. ON (e.department_id = d.department_id ) should be added B. USING (e.department_id ) should be added C. WHERE e.department_id in (100,101) should be added D. None of the above

C. The NATURAL JOIN clause implicitly matches all the identical named columns. To add additional conditions the WHERE clause can be used.

.Which of the following commands is used to change the rows that already exist in a table? A. INSERT B. UNION C. UPDATE D. SELECT

C. UPDATE is a DML statement which is used to modify the column values in a table.

What will be the outcome of the following query? SELECT TO_DATE('01 / JAN / 13','DD-MON-YY') FROM dual; A. ORA error B. 01-JAN-2013 C. 01-JANUARY-13 D. 01-JAN-13

D. 01-JAN-13

Assuming the SYSDATE is 01-JAN-13, what will be the outcome of the following query? SELECT TO_CHAR (SYSDATE, 'DDTH') FROM dual; A. 1st of January B. 1st C. 1 ST D. 01ST

D. 01ST

What is true regarding FULL OUTER JOIN in Oracle DB? A. When both LEFT OUTER JOIN and RIGHT OUTER JOIN appear in the same query,it is called a FULL OUTER JOIN B. A FULL OUTER JOIN is the same as an OUTER JOIN C. Both A and B D. A join between two tables that returns the results of an INNER join and a LEFT and RIGHT OUTER JOIN is called a FULL OUTER JOIN

D. A full outer join includes all records from both tables, even if no corresponding record in the other table is found

What of the following can be used to fetch non-matching rows along with the matching rows between a source and a target table in Oracle DB? A. EQUI-JOIN B. SELF-JOIN C. NATURAL JOIN D. OUTER-JOIN

D. An outer join is created when records need to be included in the results without having corresponding records in the join tables. These records are matched with NULL records so that they're included in the output.

You issue the following command to the data set shown above: INSERT INTO EMPLOYEES (employee_id , first_name , job_id ) VALUES (51003,'BRUCE', NULL); What will be the output of this statement? A. It will insert a new row with 51003 BRUCE CLERK values B. It will throw an ORA error as there cannot be another BRUCE who is a CLERK C. It will throw an 'Constraint violated' ORA error D. It will insert a new row with 51003 BRUCE NULL values

D. As there is no NOT NULL constraint on the columns FIRST_NAME and JOB_ID , the NULL value will get inserted.

Which of the following ANSI SQL: 1999 join syntax joins are supported by Oracle? A. Cartesian products B. Natural joins C. Full OUTER join D. Equijoins

D. Equijoins

Which of the following data types are appropriate for general functions? A. VARCHAR2 B. NUMBER C. DATE D. All Datatypes

D. General functions are usually compatible with all primary data types like NUMBER, VARCHAR2 and DATE.

Which of the following methods is used for writing a query with columns from multiple tables? A. SELECT B. GROUP BY C. ORDER BY D. JOINS

D. Joins are used to connect multiple tables and project column data from multiple tables in Oracle

Which of the following DML commands can be considered to be a hybrid of INSERT and UPDATE in a single statement? A. INTERSECT B. INSERT C. SELECT D. MERGE

D. MERGE can perform INSERT and UPDATE actions in a single statement in Oracle.

Which of the following is an example of a nested function? A. SELECT lower(last_name) FROM employees; B. SELECT upper (last_name) FROM employees; C. SELECT concat (first_name, last_name) FROM employees; D. SELECT upper (concat(SUBSTR(first_name,1,6),'_UK')) FROM employees;

D. More than one functions in a function is known as nesting of functions.

.If the NATURAL JOIN in the above query is replaced by only JOIN which of the following should be added / modified to the above query to give the results pertaining to Department 100? A. ON (department_id = 100); B. USING (e.department_id =100); C. WHERE d.department_id = 100; D. ON (e.department_id = d.department_id and d.department_id = 100);

D. The equi-joins can be added for more conditions after the ON clause.

Which of the following is not related to a Relational Database? A. Selection B. Projection C. Joining D. None of the above

D. The options A, B and C are the major capabilities of the Oracle Relational Database.

What is true about the source table and the target table in terms of Oracle Joins? A. They must have atleast one column of same name B. All the columns should be of the same name and same data type for joining the two tables C. The source and the target tables cannot be swapped and are position specific D. None of the above

D. The source and the target tables can be swapped and are not fixed at their positions.Depending of the type of join used in the query, the result may differ or remain same.

When does a transaction complete? A. When a ROLLBACK is executed B. When a COMMIT is executed C. When TRUNCATE is executed D. All of the above

D. Transaction completes if a TCL, DCL or a DDL command is executed in the session.

Assuming the SYSDATE is 01-JAN-13 and falls on Tuesday, what will be the outcome of the following query? SELECT TO_CHAR (SYSDATE, 'fmDay')||'''s Meeting' FROM dual; A. Tuesday B. TUESDAY C. TUESDAY's Meeting D. Tuesday's Meeting

D. Tuesday's Meeting

You need to find the results obtained by the above query for all those employees who have salaries greater than 20000. Which of the following clauses should be added / modified to the above query? A. ON (e.department_id = d.department_id ) WHERE salary > 20000; B. USING (e.department_id ) WHERE salary > 20000; C. USING (department_id ) WHERE salary>20000; D. WHERE salary >20000;

D. WHERE salary >20000;