The WITH clause lets you assign a name to a subquery block. You can then reference the subquery block multiple places in the query by specifying the query name. Oracle Database optimizes the query by treating the query name as either an inline view or as a temporary table. You can specify this clause in any top-level SELECT statement and in most types of subqueries. The query name is visible to the main query and to all subsequent subqueries except the subquery that defines the query name itself. The query syntax is as follows:
WITH query_name1 AS (subquery)
[, query_name2 AS (subquery) ]
SELECT ... FROM ... WHERE ...
The WITH clause has been incorporated starting from Oracle 9.2 as a tool for materializing subqueries to save Oracle from having to recompute them multiple times. The advantage of loading subquery data into a temporary table is that repeated references to the subquery may be more efficient as the data is easily retrieved from the temporary table, rather than being requeried by each reference.
The WITH clause is subject to the following restrictions:
- You can specify only one WITH clause in a single SQL statement. You cannot specify a query_name in its own subquery. However, any query_name defined in the WITH clause can be used in any subsequent named query block in the WITH clause.
- In a compound query with set operators, you cannot use the query_name for any of the component queries, but you can use the query_name in the FROM clause of any of the component queries.
[Regular query]
There was nothing wrong with this query except maybe the total time of execution: 15 minutes, which is understandable due to the fact that the table was missing obvious indexes. The same query refactored using WITH clause looked like this:
[Refactored query using WITH clause]
First of all, this query is way easier to read and is pretty much self-explanatory, therefore it's easier to maintain the query, but the biggest benefit of refactoring was the performance gain as the query using WITH clause ran 1s, which is 900 times faster with 0.11% of the time the regular query required to run. Of course, not all queries when refactoring to use WITH clause will give that kind of performance gain, but every developer should always remember to check if WITH clause can speed up the query or at least simplify it so it's easier to read it and it makes other developers more confident with the changes or tweaks to the query.
One of the exam requirements is "Use the data dictionary views to research data on your objects". Knowing dictionary views and tables is essential as dictionary data contains information about the database (concept of metadata) and its state. They are used for management, tunning and monitoring of the database. The data dictionary is created when the Oracle database is created. It is owned by the SYS user, and is stored principally in the SYSTEM tablespace, though some components are stored in the SYSAUX tablespace in Oracle Database 10g.The data dictionary views come in three main flavors:
- User views - views with information about the user's objects
- All views - views with information about objects accessible to the user
- DBA views - views with information of all objects in the database
- ALL_ALL_TABLES - Description of all object and relational tables accessible to the user
- ALL_ASSOCIATIONS - All associations available to the user
- ALL_CATALOG - All tables, views, synonyms, sequences accessible to the user
- ALL_COLL_TYPES - Description of named collection types accessible to the user
- ALL_COL_COMMENTS - Comments on columns of accessible tables and views
- ALL_COL_PRIVS - Grants on columns for which the user is the grantor, grantee, owner, or an enabled role or PUBLIC is the grantee
- ALL_COL_PRIVS_MADE - Grants on columns for which the user is owner or grantor
- ALL_COL_PRIVS_RECD - Grants on columns for which the user, PUBLIC or enabled role is the grantee
- ALL_CONSTRAINTS - Constraint definitions on accessible tables
- ALL_CONS_COLUMNS - Information about accessible columns in constraint definitions
- ALL_CONS_OBJ_COLUMNS - List of types an object column or attribute is constrained to in the tables accessible to the user
- ALL_DB_LINKS - Database links accessible to the user
- ALL_DEPENDENCIES - Dependencies to and from objects accessible to the user
- ALL_DIRECTORIES - Description of all directories accessible to the user
- ALL_ERRORS - Current errors on stored objects that user is allowed to create
- ALL_EXTERNAL_LOCATIONS - Description of the external tables locations accessible to the user
- ALL_EXTERNAL_TABLES - Description of the external tables accessible to the user
- ALL_FILE_GROUPS - Details about file groups
- ALL_INDEXES - Descriptions of indexes on tables accessible to the user
- ALL_INDEXTYPES - All indextypes available to the user
- ALL_IND_COLUMNS - Columns comprising indexes on accessible tables
- ALL_IND_EXPRESSIONS - Functional index expressions on accessible tables
- ALL_JOIN_IND_COLUMNS - Join Index columns comprising the join conditions
- ALL_LIBRARIES - Description of libraries accessible to the user
- ALL_MVIEWS - All materialized views in the database
- ALL_NESTED_TABLES - Description of nested tables in tables accessible to the user
- ALL_NESTED_TABLE_COLS - Columns of nested tables
- ALL_OBJECTS - Objects accessible to the user
- ALL_RULES - Rules seen by the user
- ALL_SEQUENCES - Description of SEQUENCEs accessible to the user
- ALL_SYNONYMS - All synonyms for base objects accessible to the user and session
- ALL_TABLES - Description of relational tables accessible to the user
- ALL_TAB_COLS - Columns of user's tables, views and clusters
- ALL_TAB_COLUMNS - Columns of user's tables, views and clusters
- ALL_TAB_COL_STATISTICS - Columns of user's tables, views and clusters
- ALL_TAB_COMMENTS - Comments on tables and views accessible to the user
- ALL_TAB_PRIVS - Grants on objects for which the user is the grantor, grantee, owner, or an enabled role or PUBLIC is the grantee
- ALL_TAB_PRIVS_MADE - User's grants and grants on user's objects
- ALL_TAB_PRIVS_RECD - Grants on objects for which the user, PUBLIC or enabled role is the grantee
- ALL_TYPES - Description of types accessible to the user
- ALL_UNUSED_COL_TABS - All tables with unused columns accessible to the user
- ALL_UPDATABLE_COLUMNS - Description of all updatable columns
- ALL_USERS - Information about all users of the database
- ALL_USTATS - All statistics
- ALL_VIEWS - Description of views accessible to the user
- CAT - Synonym for USER_CATALOG
- COLS - Synonym for USER_TAB_COLUMNS
- COLUMN_PRIVILEGES - Grants on columns for which the user is the grantor, grantee, owner, or an enabled role or PUBLIC is the grantee
- DICT - Synonym for DICTIONARY
- DICTIONARY - Description of data dictionary tables and views
- DICT_COLUMNS - Description of columns in data dictionary tables and views
- GV$ACTIVE_INSTANCES - Synonym for GV_$ACTIVE_INSTANCES
- GV$NLS_PARAMETERS - Synonym for GV_$NLS_PARAMETERS
- GV$TIMEZONE_NAMES - Synonym for GV_$TIMEZONE_NAMES
- GV$VERSION - Synonym for GV_$VERSION
- IND - Synonym for USER_INDEXES
- NLS_INSTANCE_PARAMETERS - NLS parameters of the instance
- NLS_SESSION_PARAMETERS - NLS parameters of the user session
- OBJ - Synonym for USER_OBJECTS
- RECYCLEBIN - Synonym for USER_RECYCLEBIN
- ROLE_ROLE_PRIVS - Roles which are granted to roles
- ROLE_SYS_PRIVS - System privileges granted to roles
- ROLE_TAB_PRIVS - Table privileges granted to roles
- SEQ - Synonym for USER_SEQUENCES
- SESSION_PRIVS - Privileges which the user currently has set
- SESSION_ROLES - Roles which the user currently has enabled.
- SM$VERSION - Synonym for SM_$VERSION
- SYN - Synonym for USER_SYNONYMS
- TABLE_PRIVILEGES - Grants on objects for which the user is the grantor, grantee, owner, or an enabled role or PUBLIC is the grantee
- TABS - Synonym for USER_TABLES
- USER_ALL_TABLES - Description of all object and relational tables owned by the user's
- USER_CATALOG - Tables, Views, Synonyms and Sequences owned by the user
- USER_COL_COMMENTS - Comments on columns of user's tables and views
- USER_COL_PRIVS - Grants on columns for which the user is the owner, grantor or grantee
- USER_COL_PRIVS_MADE - All grants on columns of objects owned by the user
- USER_COL_PRIVS_RECD - Grants on columns for which the user is the grantee
- USER_CONSTRAINTS - Constraint definitions on user's own tables
- USER_CONS_COLUMNS - Information about accessible columns in constraint definitions
- USER_CONS_OBJ_COLUMNS - List of types an object column or attribute is constrained to in the tables owned by the user
- USER_DB_LINKS - Database links owned by the user
- USER_DEPENDENCIES - Dependencies to and from a users objects
- USER_ERRORS - Current errors on stored objects owned by the user
- USER_EXTERNAL_LOCATIONS - Description of the user's external tables locations
- USER_EXTERNAL_TABLES - Description of the user's own external tables
- USER_FREE_SPACE - Free extents in tablespaces accessible to the user
- USER_INDEXES - Description of the user's own indexes
- USER_INDEXTYPES - All user indextypes
- USER_INDEXTYPE_ARRAYTYPES - All array types specified by the indextype
- USER_INDEXTYPE_COMMENTS - Comments for user-defined indextypes
- USER_INDEXTYPE_OPERATORS - All user indextype operators
- USER_IND_COLUMNS - columns comprising user's indexes and indexes on user's tables
- USER_IND_EXPRESSIONS - Functional index expressions in user's indexes and indexes on user's tables
- USER_JOIN_IND_COLUMNS - Join Index columns comprising the join conditions
- USER_LIBRARIES - Description of the user's own libraries
- USER_MVIEWS - All materialized views in the database
- USER_NESTED_TABLES - Description of nested tables contained in the user's own tables
- USER_NESTED_TABLE_COLS - Columns of nested tables
- USER_OBJECTS - Objects owned by the user
- USER_OBJECT_TABLES - Description of the user's own object tables
- USER_PASSWORD_LIMITS - Display password limits of the user
- USER_RECYCLEBIN - User view of his recyclebin
- USER_ROLE_PRIVS - Roles granted to current user
- USER_RULES - Rules owned by the user
- USER_SEQUENCES - Description of the user's own SEQUENCEs
- USER_SYNONYMS - The user's private synonyms
- USER_SYS_PRIVS - System privileges granted to current user
- USER_TABLES - Description of the user's own relational tables
- USER_TABLESPACES - Description of accessible tablespaces
- USER_TAB_COLS - Columns of user's tables, views and clusters
- USER_TAB_COLUMNS - Columns of user's tables, views and clusters
- USER_TAB_COMMENTS - Comments on the tables and views owned by the user
- USER_TAB_PRIVS - Grants on objects for which the user is the owner, grantor or grantee
- USER_TAB_PRIVS_MADE - All grants on objects owned by the user
- USER_TAB_PRIVS_RECD - Grants on objects for which the user is the grantee
- USER_TYPES - Description of the user's own types
- USER_UNUSED_COL_TABS - User tables with unused columns
- USER_UPDATABLE_COLUMNS - Description of updatable columns
- USER_USERS - Information about the current user
- USER_USTATS - All statistics on tables or indexes owned by the user
- USER_VIEWS - Description of the user's own views
- V$NLS_PARAMETERS - Synonym for V_$NLS_PARAMETERS
- V$NLS_VALID_VALUES - Synonym for V_$NLS_VALID_VALUES
- V$OPEN_CURSOR - Synonym for V_$OPEN_CURSOR
- V$SESSION - Synonym for V_$SESSION
- V$SQLTEXT_WITH_NEWLINES - Synonym for V_$SQLTEXT_WITH_NEWLINES
- V$SQL_PLAN - Synonym for V_$SQL_PLAN
- V$TIMEZONE_NAMES - Synonym for V_$TIMEZONE_NAMES
- V$TRANSACTION - Synonym for V_$TRANSACTION
- V$VERSION - Synonym for V_$VERSION
Examine the data in the DOCNO column of the DOC_DETAILS table:
DOCNO
123-456-7890
233-67-90876
45-789-23456
You need to extract the digits between the hyphens as follows:
SUBSTR
456
67
789
Which SQL statement gives the required result?
A. SELECT REGEXP_SUBSTR(docno,'-[^-]+') "SUBSTR" FROM doc_details;
B. SELECT REGEXP_SUBSTR(docno,'^-[^-]+-')"SUBSTR" FROM doc_details;
C. SELECT REGEXP_SUBSTR(docno,'-[^-]+',2) "SUBSTR" FROM doc_details;
D. SELECT REGEXP_SUBSTR(docno, '[^-]+',1,2) "SUBSTR" FROM doc_details;
Theory:
Since the question contains queries with REGEXP_SUBSTR function and regular expression syntax, so the information required to answer it can found as follows: REGEXP_SUBSTR and REGULAR EXPRESSION SYNTAX.
- source_string is a character expression that serves as the search value. It is commonly a character column and can be of any of the datatypes CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB.
- pattern is the regular expression. It is usually a text literal and can be of any of the datatypes CHAR, VARCHAR2, NCHAR, or NVARCHAR2. It can contain up to 512 bytes.
- position is a positive integer indicating the character of source_string where Oracle should begin the search. The default is 1, meaning that Oracle begins the search at the first character of source_string.
- occurrence is a positive integer indicating which occurrence of pattern in source_string Oracle should search for. The default is 1, meaning that Oracle searches for the first occurrence of pattern.
- match_parameter is a text literal that lets you change the default matching behavior of the function. You can specify one or more value (ie. 'i' specifies case-insensitive matching)
- "+" - matches one or more occurrences
- "^" - matches the beginning-of-line character. By default it matches only the beginning of an entire string.
- "$" - matches the end-of-line character. By default it matches only the end of an entire string.
- "[]" - specify a matching list that should match any one of the expressions represented in the list. A nonmatching list expression begins with a circumflex (^) and specifies a list that matches any character except for the expressions represented in the list.
A - Wrong - the query finds a string that starts with a dash character and that has no other dash character, which would be numbers in the middle of the docno, so the query displays: -456, -67, -789 (dash character is part of the match).
B - Wrong - the search patterns looks for a string that doesn't start with a dash character and that has no dash character, which would be numbers at the beginning of the docno, so the query displays: 123, 233, 45
C - Wrong - the query has the same search pattern as the query from the answer A, except it starts looking from the 2nd character. As a result, it produces the same results as query from the answer A: -456, -67, -789
D - Correct - the search pattern looks for strings that don't have a dash character, so all numbers from the docno match the pattern, but the query takes the second group from the match, so the query displays: 456, 67, 789
View the Exhibit and examine the structure of the EMP table belonging to the user SCOTT. The EMP table contains the details of all the current employees in your organization. EMPNO is the PRIMARY KEY. User SCOTT has created an ENAME_IDX index on the ENAME column and an EMP_VW view that displays the ENAME and SALARY columns. The recyclebin is enabled in the database. SCOTT executes the following command:
SQL> DROP TABLE emp;
Which details would be stored in the recycle bin? (Choose all that apply)
A. EMP_VW
B. ENAME_IDX
C. The PRIMARY KEY constraint
D. Only the structure of the EMP table
E. Structure and data of the EMP table
Exhibit:
Theory:
Since the question is about FLASHBACK (DROP TABLE and RECYCLE BIN), the required information needed to answer this question can be found as follows: FLASHBACK TECHNOLOGY and FLASHBACK TABLE. So when a user drops a table, Oracle database does not immediately remove the space associated with the table. Instead, the table is renamed and, along with any associated objects, it is placed in the Recycle Bin of the database, and the Flashback Drop operation can recover the table from the recycle bin. The recycle bin is a logical container for all dropped tables and their dependent objects. When a table is dropped, the database will store the table (structure and data), along with its dependent objects in the recycle bin so that they can be recovered later. Dependent objects which are stored in the recycle bin include indexes, constraints, triggers, nested tables, LOB segments and LOB index segments. The table and its dependent objects will remain in the recycle bin until they are purged from the recycle bin. Dropped objects still appear in the views USER_TABLES, ALL_TABLES, DBA_TABLES, USER_INDEX, ALL_INDEX and DBA_INDEX. A new column, DROPPED, is set to YES for these objects. Any stored procedures that referenced the original object, though, are invalidated.
Answer:
A - Wrong - dropping table does not drop view
B - Correct - index gets stored
C - Correct - primary key constraint gets stored
D - Wrong - not only the structure of the table but also the data
E - Correct - the structure and the data of the table gets stored
Examine the structure of the DEPT table:
You successfully execute the following SQL statement:
SQL>CREATE TABLE emp
(emp_no NUMBER(3) PRIMARY KEY,
emp_name VARCHAR2(25) UNIQUE,
job_id VARCHAR2(10) NOT NULL,
deptno NUMBER(2) REFERENCES dept(deptno),
salary NUMBER(10,2) CHECK (salary > 0));
For which columns would an index be generated automatically? (Choose all that apply)
A. EMP_NO
B. SALARY
C. JOB_ID
D. DEPT_NO
E. EMP_NAME
Theory:
Since the question contains a CREATE TABLE query with in-line constraints, the information required to answer it can be found as follows: CREATE TABLE and CONSTRAINTS.
CONSTRAINTS are used to define an integrity constraint - a rule that restricts the values in a database. Oracle Database lets you create six types of constraints.
- A NOT NULL constraint prohibits a database value from being null.
- A unique constraint prohibits multiple rows from having the same value in the same column or combination of columns but allows some values to be null.
- A primary key constraint combines a NOT NULL constraint and a unique constraint in a single declaration. That is, it prohibits multiple rows from having the same value in the same column or combination of columns and prohibits values from being null.
- A foreign key constraint requires values in one table to match values in another table.
- A check constraint requires a value in the database to comply with a specified condition.
- A REF column by definition references an object in another object type or in a relational table. A REF constraint lets you further describe the relationship between the REF column and the object it references.
The constrains can be declared in two ways.
- As part of the definition of an individual column or attribute. This is called INLINE specification.
- As part of the table definition. This is called OUT-OF-LINE specification.
Answer:
A - Correct - Primary Key constraint automatically generates a unique index
B - Wrong - Check constraint does not automatically generate any index
C - Wrong - Not Null constraint does not automatically generate any index
D - Wrong - Foreign Key constraint does not automatically generate any index
E - Correct - Unique constraint automatically generates a unique index
Evaluate the following SQL statement:
(Note that the numbers 2,3 etc in the SQL statement are line numbers and not part of the syntax)
SQL> CREATE TABLE product
2 (prod_id NUMBER(3),
3 prod_name VARCHAR2(25),
4 qty NUMBER(7,2),
5 price NUMBER(10,2),
6 CONSTRAINT prod_id_pk PRIMARY KEY(prod_id),
7 CONSTRAINT prod_name_uq UNIQUE (prod_name),
8 CONSTRAINT price_nn NOT NULL (price));
What is the outcome of executing this command?
A. It generates an error at line 6.
B. It generates an error at line 7.
C. It generates an error at line 8.
D. It executes successfully and creates the PRODUCTS table.
Theory:
Since the question asks about the syntax of creating a table and constrains, the information required to answer the question can be found as follows: CREATE TABLE and CONSTRAINTS.
CONSTRAINTS are used to define an integrity constraint - a rule that restricts the values in a database. Oracle Database lets you create six types of constraints.
- A NOT NULL constraint prohibits a database value from being null.
- A unique constraint prohibits multiple rows from having the same value in the same column or combination of columns but allows some values to be null.
- A primary key constraint combines a NOT NULL constraint and a unique constraint in a single declaration. That is, it prohibits multiple rows from having the same value in the same column or combination of columns and prohibits values from being null.
- A foreign key constraint requires values in one table to match values in another table.
- A check constraint requires a value in the database to comply with a specified condition.
- A REF column by definition references an object in another object type or in a relational table. A REF constraint lets you further describe the relationship between the REF column and the object it references.
The constrains can be declared in two ways.
- As part of the definition of an individual column or attribute. This is called INLINE specification.
- As part of the table definition. This is called OUT-OF-LINE specification.
NOT NULL constraints must be declared inline. All other constraints can be declared either inline or out of line.
Answer:
A - Wrong - the Primary Key constraint syntax is correct
B - Wrong - the Unique constraint syntax is correct
C - Correct - the Not Null constrain cannot be declared out of line
D - Wrong - the Not Null constrain syntax is invalid, hence the query doesn't execute successfully
View the Exhibit and examine the structure of the EMP table. You want to display the names and salaries of only those employees who earn the highest salaries in their departments. Which two SQL statements give the required output? (Choose two.)
A. SELECT ename, sal
FROM emp e
WHERE sal = (SELECT MAX(sal)
FROM emp
WHERE deptno = e.deptno);
B. SELECT ename, sal
FROM emp
WHERE sal = ALL (SELECT MAX(sal)
FROM emp
GROUP BY deptno);
C. SELECT ename, sal
FROM emp e
WHERE EXISTS (SELECT MAX(sal)
FROM emp WHERE deptno = e.deptno);
D. SELECT ename, sal
FROM emp
NATURAL JOIN (SELECT deptno, MAX(sal) sal
FROM emp
GROUP BY deptno);
Exhibits:
EMP table:
Theory:
Since all the queries are SELECT queries with subqueries, EXISTS and MAX functions, ALL operator and NATURAL join, the information needed to answer the question can be found as follows: SELECT, JOINS, COMPARISON OPERATORS, MAX and EXISTS.
- ANY = SOME - compares a value to each value in a list or returned by a query. Must be preceded by =, !=, >, <, <=, >=. Can be followed by any expression or subquery that returns one or more values. It evaluates to TRUE if at least value meets the comparison criteria, and evaluates to FALSE if the query returns no rows.
- ALL - compares a value to every value in a list or returned by a query. Must be preceded by =, !=, >, <, <=, >=. Can be followed by any expression or subquery that returns one or more values. It evaluates to TRUE only if all values meet the comparison criteria, and evaluates to TRUE if the query returns no rows.
A - Correct
B - Wrong - it would require a salary to be equal to the amount of maximum salary in each department at the same time, which is not true unless that amount would be identical for each department, but that's not stated in the question
C - Wrong - it would only check if there is any employee with salary in the department of the employee and would display all employees no matter if their salary is the highest or not.
D - Correct
View the Exhibit and examine the structure of the LOCATIONS and DEPARTMENTS tables. You need to display all those cities that have only one department. Which query gives the correct output?
A.SELECT location_id, city
FROM locations l
WHERE 1 = (SELECT COUNT(*)
FROM departments
WHERE location_id = l.location_id);
B. SELECT location_id, city
FROM locations WHERE EXISTS (SELECT COUNT(*)
FROM departments
GROUP BY location_id HAVING COUNT(*) = 1);
C. SELECT location_id, city
FROM locations WHERE
1 = (SELECT COUNT(*) FROM departments
GROUP BY location_id);
D. SELECT l.location_id, city
FROM locations l JOIN departments d ON (l.location_id = d.location_id)
WHERE EXISTS (SELECT COUNT(*)
FROM departments d
WHERE l.location_id =d.location_id);
Exhibit:
LOCATIONS table:
DEPARTMENTS table:
Theory:
Since the question contains SELECT statements with a correlated and non-correlated subquery and a function EXISTS, so the information needed to answer this question can be found as follows: SELECT, SUBQUERIES, EXISTS and COUNT..
A SUBQUERY or INNER QUERY is a SELECT statement that exists within another SQL statement (QUTER QUERY). A subquery can be used within SELECT, INSERT, UPDATE and DELETE statements. A subquery is non-correlated when it can be executed successfully on its own, whereas a correlated subquery uses values from the outer query. Any valid SELECT statement can qualify as a subquery include those that retrieve data from one or more tables or views, those that use complex expressions or aggregate functions, and those that include the WHERE and GROUP BY clauses, HAVING or ORDER BY clauses, or any other other features available to SELECT statements. EXISTS or IN operators tests for existence of rows in a subquery, creating a semi-join condition. A semi-join between two tables returns rows from the first table where one or more matches are found in the second table. The difference between a semi-join and a conventional join is that rows in the first table will be returned at most once. A semijoin returns rows that match an EXISTS subquery without duplicating rows from the left side of the predicate when multiple rows on the right side satisfy the criteria of the subquery. Note that EXISTS will not scan all rows in the subquery, only one row is required to determine whether the outcome is TRUE or FALSE. However, NOT EXISTS must scan all rows, which may cause performance problems. COUNT function counts occurrences of data, not NULL values. However, when combined with the asterisk - COUNT(*) - it counts all rows including duplicate records and those with all NULL values in the results. COUNT function never returns NULL value as it returns a value of 0 if it encounters no values.
Answer:
A - Correct
B - Wrong - Since Count function always returns a value, so EXISTS function will always return TRUE value even if a city has more or has no departments.
C - Wrong - A subquery is not correlated to the outer query, hence the query will display all cities if there is one that has with one department.
D - Wrong - Even though a subquery is correlated, the COUNT function will always return a value, which taken by EXISTS will yield TRUE condition, hence the query will return all cities.
View the Exhibit and examine the structure of the EMPLOYEES and DEPARTMENTS tables. You want to display the last names and hire dates of all latest hires in their respective departments in the location ID 1700. You issue the following query:
SQL>SELECT last_name, hire_date
FROM employees
WHERE (department_id, hire_date) IN
(SELECT department_id, MAX(hire_date)
FROM employees JOIN departments
USING(department_id)
WHERE location_id = 1700
GROUP BY department_id);
What is the outcome?
A. It executes but does not give the correct result
B. It executes successfully and gives the correct result
C. It generates an error because of the pairwise comparison
D. It generates an error because the GROUP BY clause cannot be used with table joins in a subquery
Exhibit:
EMPLOYEES table:
Theory:
Since the query from the question is a SELECT statement with IN condition and a subquery, which uses INNER JOIN that uses USING keyword, so the here is what you need to know to answer that question - SELECT, SUBQUERY, IN CONDITION and JOINS.
A SUBQUERY or INNER QUERY is a SELECT statement that exists within another SQL statement (QUTER QUERY). A subquery can be used within SELECT, INSERT, UPDATE and DELETE statements. A subquery is non-correlated when it can be executed successfully on its own, whereas a correlated subquery uses values from the outer query. Any valid SELECT statement can qualify as a subquery include those that retrieve data from one or more tables or views, those that use complex expressions or aggregate functions, and those that include the WHERE and GROUP BY clauses, HAVING or ORDER BY clauses, or any other other features available to SELECT statements. IN operator compares one or multiple expressions on the left side of the operator to a set of one or more values on the right side of the operator. When using multiple expressions (like 2 column - pairwise comparison), the number and datatypes of expressions in the list must match on both sides of the operator. When you are specifying an equijoin of columns that have the same name in both tables, the USING (columns) clause indicates the columns to be used. You can use this clause only if the join columns in both tables have the same name. Within this clause, do not qualify the column name with a table name or table alias.
Answers:
A - Wrong - the query is correct and does give the correct results as it displays all employees that were hired last within the department with the location id =1700
B - Correct
C - Wrong - pairwise comparison is correct
D - Wrong - a subquery can be any valid SELECT query with any JOIN and GROUP BY clause
View the Exhibit and examine the structure of the DEPARTMENTS and LOCATIONS tables. You want to display all the cities and the corresponding departments in them, if any. Which query would give you the required output?
A. SELECT location_id LOC, city, department_id DEPT
FROM locations LEFT OUTER JOIN departments
USING (location_id);
B. SELECT location_id LOC, city, department_id DEPT
FROM locations RIGHT OUTER JOIN departments
USING (location_id);
C. SELECT l.location_id LOC, l.city, d.department_id DEPT
FROM locations l LEFT OUTER JOIN departments d
USING (location_id);
D. SELECT l.location_id LOC, l.city, d.department_id DEPT
FROM locations l FULL OUTER JOIN departments d
USING (location_id);
Exhibit:
DEPARTMENTS table:
Theory:
Since the question or rather answers contain only SELECT statements with different types of JOIN, hence the information required to answer the question can be found in the documentation on SELECT and JOINS. The syntax of JOIN queries within SELECT statement is as follows:
Answer:
A - Correct
B - Wrong - the query would display all departments and cities if any in that departments instead of displaying all cities and departments if any
C - Wrong - cannot use table aliases when joining with USING
D - Wrong - this query would display all cities and all departments event those that don't have a city
1. View the Exhibit and examine the structure of the EMP and SALGRADE tables. You want to display the names of all employees whose salaries belong to GRADE 5. Which SQL statements give the required output? (Choose all that apply)
A.SELECT ename
FROM emp JOIN salgrade
USING (sal BETWEEN losal AND hisal) AND grade = 5;
B. SELECT ename
FROM emp e JOIN salgrade s
ON (e.sal BETWEEN s.losal AND s.hisal AND s.grade = 5);
C. SELECT ename
FROM emp e JOIN salgrade s
ON (e.sal BETWEEN s.losal AND s.hisal) AND s.grade = 5;
D. SELECT ename
FROM emp e JOIN salgrade s
ON (e.sal BETWEEN s.losal AND s.hisal) WHERE s.grade=5;
E. SELECT ename
FROM emp e JOIN salgrade s
WHERE e.sal BETWEEN s.losal AND s.hisal AND s.grade = 5;
Exhibit:
EMP table:
Theory:
This question tests the ability to use joins in SELECT queries. A lot of very useful information on JOIN can be found on the following website - Creating and maintaining joins and here as far as select statement - SELECT. Here is the syntax of using JOIN in the SELECT queries:
Answer:
A - Wrong - USING requires column name not condition
B - Correct
C - Correct
D - Correct
E - Wrong - JOIN requires ON keyword if joins without USING keyword
After having finished the certification path to MCPD Enterprise Application Developer 3.5 I had to choose the next exam. I couldn't take .NET 4.0 exams as they were not available yet, so I chose SQL Developer path. I think it is obvious as I can't even recall the application I worked on that didn't have any database layer. Of course sometimes I worked on business layer, but the application did have data layer that was using either Oracle, MS SQL or IBM DB2 database. Being a very good database developer is as much important for a software developer as being a very good software developer. I have seen quite a few solutions where not really bad software developers did very bad job as database developers, which had very bad results on application performance and stability. Those examples encouraged me to stay focused on database development almost on the same level as staying on software development. That's why I used the gap between .NET exams to take one of the database development exams. So far I have taken only one - 70-441 MCTS SQL Server 2005, so my choice was either SQL Server 2008 or Oracle SQL Expert, and I decided to take 1Z0-047 as at the moment I work more with Oracle Database than SQL Server. So as always before starting studying to an exam, I try to find any information on the exam from people who have taken the exam. Unfortunately, there's not much about this exam. Below are two links I found most useful:
Oracle-Database-Expert-Exam-Guide-Reviews
Oracle Forum
The first link gives you some information on the book "OCA Oracle Database SQL Expert Exam Guide: Exam 1Z0-047" and whether the book is sufficient to the exam. Not much information but always some. The forum link contains a lot more information. Here are some most useful suggestions you can find there:
- read the exam topics
- read the Oracle documentations regarding to the topics
- if you don't like the Oracle docs, then get books from amazon, etc.
- practice on daily basis if you don't work as a developer...
- "To be honest I didn't like this exam because I had to read everything 3 times at least, it was really easy to make mistakes..."
- "Anybody who took it will be subject to non disclosure agreements, so would be very limited in their comments. The only thing I can suggest is to look over the topics for the exam and the sample questions and practice. If you know the topics, carefully view the questions then our should be all right. If its your first exam ever then it will seem a little more daunting."
It is not much but gives you some understanding. So my strategy for this exam is to read the following books and ebooks:
- OCA Oracle Database SQL Expert Exam Guide: Exam 1Z0-047
- Oracle Database 11g The Complete Reference
- SQL Language Reference
- Oracle Database Reference
I think both books and ebooks should give me very deep understanding of SQL and Oracle Database 11g. Once I have read them, I will read again information on the exam topics that you can find on the official Oracle web site. That should guarantee that my studying covered all exam topics since one of the reviews of the exam guide book said that the exam covered several topics not mentioned in the book. The exam guide book has 2 complete sample tests so I think before taking the real test they should help me evaluate my skills and show the topics I need to focus. I also believe that the best way to study is by doing the examples, so I will practice each exam topic to make sure I understand it correctly and there's no error in the book. As far as posts on the exam, I will use sample questions presented on the Oracle official exam web site, as I believe they test the knowledge that is more than likely to appear on the exam.
Subscribe to:
Posts (Atom)
