Cuestiones
ayuda
option
Mi Daypo

TEST BORRADO, QUIZÁS LE INTERESESQL Fundamentals I Oracle Database 12c

COMENTARIOS ESTADÍSTICAS RÉCORDS
REALIZAR TEST
Título del test:
SQL Fundamentals I Oracle Database 12c

Descripción:
Examen de preparación

Autor:
La chiquitina
(Otros tests del mismo autor)

Fecha de Creación:
22/04/2019

Categoría:
Informática

Número preguntas: 133
Comparte el test:
Facebook
Twitter
Whatsapp
Comparte el test:
Facebook
Twitter
Whatsapp
Últimos Comentarios
No hay ningún comentario sobre este test.
Temario:
2 Data Retrieval Using the SQL SELECT Statement 2.01 List the Capabilities of SQL SELECT Statements 2.02 Execute a Basic SELECT Statement.
Which query creates a projection of the DEPARTMENT_NAME and LOCATION_ID columns from the DEPARTMENTS table? (Choose the best answer.) A. SELECT DISTINCT DEPARTMENT_NAME, LOCATION_ID FROM DEPARTMENTS; B. SELECT DEPARTMENT_NAME, LOCATION_ID FROM DEPARTMENTS; C. SELECT DEPT_NAME, LOC_ID FROM DEPT; D. SELECT DEPARTMENT_NAME AS “LOCATION_ID” FROM DEPARTMENTS;.
After describing the EMPLOYEES table, you discover that the SALARY column has a data type of NUMBER(8,2). Which SALARY value(s) will not be permitted in this column? (Choose all that apply.) A. SALARY=12345678 B. SALARY=123456.78 C. SALARY=12345.678 D. SALARY=123456 E. SALARY=12.34.
After describing the JOB_HISTORY table, you discover that the START_DATE and END_ DATE columns have a data type of DATE. Consider the expression END_DATE-START_ DATE. (Choose two correct statements.) A. A value of DATE data type is returned. B. A value of type NUMBER is returned. C. A value of type VARCHAR2 is returned. D. The expression is invalid since arithmetic cannot be performed on columns with DATE data types. E. The expression represents the days between the END_DATE and START_DATE less one day.
The DEPARTMENTS table contains a DEPARTMENT_NAME column with data type VARCHAR2(30). (Choose two true statements about this column.) A. This column can store character data up to a maximum of 30 characters. B. This column must store character data that is at least 30 characters long. C. The VARCHAR2 data type is replaced by the CHAR data type. D. This column can store data in a column with data type VARCHAR2(50) provided that the contents are at most 30 characters long.
Which statement reports on unique JOB_ID values from the EMPLOYEES table? (Choose all that apply.) A. SELECT JOB_ID FROM EMPLOYEES; B. SELECT UNIQUE JOB_ID FROM EMPLOYEES; C. SELECT DISTINCT JOB_ID, EMPLOYEE_ID FROM EMPLOYEES; D. SELECT DISTINCT JOB_ID FROM EMPLOYEES;.
Choose the two illegal statements. The two correct statements produce identical results. The two illegal statements will cause an error to be raised: A. SELECT DEPARTMENT_ID|| ' represents the '|| DEPARTMENT_NAME||' Department' as "Department Info" FROM DEPARTMENTS; B. SELECT DEPARTMENT_ID|| ' represents the || DEPARTMENT_NAME||' Department' as "Department Info" FROM DEPARTMENTS; C. select department_id|| ' represents the '||department_name|| ' Department' "Department Info" from departments; D. SELECT DEPARTMENT_ID represents the DEPARTMENT_NAME Department as "Department Info" FROM DEPARTMENTS;.
Which expressions do not return NULL values? (Choose all that apply.) A. select ((10 + 20) * 50) + null from dual; B. select 'this is a '||null||'test with nulls' from dual; C. select null/0 from dual; D. select null||'test'||null as “Test” from dual;.
Choose the correct syntax to return all columns and rows of data from the EMPLOYEES table. A. select all from employees; B. select employee_id, first_name, last_name, first_name, department_id from employees; C. select % from employees; D. select * from employees; E. select *.* from employees;.
The following character literal expression is selected from the DUAL table: SELECT 'Coda''s favorite fetch toy is his orange ring' FROM DUAL; (Choose the result that is returned.) A. An error would be returned due to the presence of two adjacent quotes B. Coda's favorite fetch toy is his orange ring C. Coda''s favorite fetch toy is his orange ring D. Coda''s favorite fetch toy is his orange ring'.
There are four rows of data in the REGIONS table. Consider the following SQL statement: SELECT '6 * 6' “Area” FROM REGIONS; How many rows of results are returned and what value is returned by the Area column? (Choose the best answer.) A. 1 row returned, Area column contains value 36 B. 4 rows returned, Area column contains value 36 for all 4 rows C. 1 row returned, Area column contains value 6 * 6 D. 4 rows returned, Area column contains value 6 * 6 for all 4 rows E. A syntax error is returned.
3 Restricting and Sorting Data 3.01 Limit the Rows Retrieved by a Query 3.02 Sort the Rows Retrieved by a Query 3.03 Ampersand Substitution.
Which two clauses of the SELECT statement facilitate selection and projection? A. SELECT, FROM B. ORDER BY, WHERE C. SELECT, WHERE D. SELECT, ORDER BY.
Choose the query that extracts the LAST_NAME, JOB_ID, and SALARY values from the EMPLOYEES table for records having JOB_ID values of either SA_REP or MK_MAN and having SALARY values in the range of $1,000 to $4,000. The SELECT and FROM clauses are SELECT LAST_NAME, JOB_ID, SALARY FROM EMPLOYEES: A. WHERE JOB_ID IN ('SA_REP','MK_MAN') AND SALARY > 1000 AND SALARY < 4000; B. WHERE JOB_ID IN ('SA_REP','MK_MAN') AND SALARY BETWEEN 1000 AND 4000; C. WHERE JOB_ID LIKE 'SA_REP%' AND 'MK_MAN%' AND SALARY > 1000 AND SALARY < 4000; D. WHERE JOB_ID = 'SA_REP' AND SALARY BETWEEN 1000 AND 4000 OR JOB_ID='MK_MAN';.
Which of the following WHERE clauses contains an error? The SELECT and FROM clauses are SELECT * FROM EMPLOYEES: A. WHERE HIRE_DATE IN ('02-JUN-2004'); B. WHERE SALARY IN ('1000','4000','2000'); C. WHERE JOB_ID IN (SA_REP,MK_MAN); D. WHERE COMMISSION_PCT BETWEEN 0.1 AND 0.5;.
Choose the WHERE clause that extracts the DEPARTMENT_NAME values containing the character literal "er" from the DEPARTMENTS table. The SELECT and FROM clauses are SELECT DEPARTMENT_NAME FROM DEPARTMENTS: A. WHERE DEPARTMENT_NAME IN ('%e%r'); B. WHERE DEPARTMENT_NAME LIKE '%er%'; C. WHERE DEPARTMENT_NAME BETWEEN 'e' AND 'r'; D. WHERE DEPARTMENT_NAME CONTAINS 'e%r';.
Which two of the following conditions are equivalent to each other? A. WHERE COMMISSION_PCT IS NULL B. WHERE COMMISSION_PCT = NULL C. WHERE COMMISSION_PCT IN (NULL) D. WHERE NOT(COMMISSION_PCT IS NOT NULL).
Which two of the following conditions are equivalent to each other? A. WHERE SALARY <=5000 AND SALARY >=2000 B. WHERE SALARY IN (2000,3000,4000,5000) C. WHERE SALARY BETWEEN 2000 AND 5000 D. WHERE SALARY > 2000 AND SALARY < 5000 E. WHERE SALARY >=2000 AND <=5000.
Choose one false statement about the ORDER BY clause. A. When using the ORDER BY clause, it always appears as the last clause in a SELECT statement. B. The ORDER BY clause may appear in a SELECT statement that does not contain a WHERE clause. C. The ORDER BY clause specifies one or more terms by which the retrieved rows are sorted. These terms can only be column names. D. Positional sorting is accomplished by specifying the numeric position of a column as it appears in the SELECT list, in the ORDER BY clause.
The following query retrieves the LAST_NAME, SALARY, and COMMISSION_PCT values for employees whose LAST_NAME begins with the letter “R”. Based on the following query, choose the ORDER BY clause that first sorts the results by the COMMISSION_PCT column, listing highest commission earners first, and then sorts the results in ascending order by the SALARY column. Any records with NULL COMMISSION_PCT must appear last: SELECT LAST_NAME, SALARY, COMMISSION_PCT FROM EMPLOYEES WHERE LAST_NAME LIKE 'R%' A. ORDER BY COMMISSION_PCT DESC, 2; B. ORDER BY 3 DESC, 2 ASC NULLS LAST; C. ORDER BY 3 DESC NULLS LAST, 2 ASC; D. ORDER BY COMMISSION_PCT DESC, SALARY ASC;.
The DEFINE command explicitly declares a session-persistent substitution variable with a specific value. How is this variable referenced in an SQL statement? Consider an expression that calculates tax on an employee’s SALARY based on the current tax rate. For the following session-persistent substitution variable, which statement correctly references the TAX_RATE variable? DEFINE TAX_RATE=0.14 A. SELECT SALARY * :TAX_RATE TAX FROM EMPLOYEES; B. SELECT SALARY * ampersandTAX_RATE TAX FROM EMPLOYEES; C. SELECT SALARY * :ampersandampersandTAX TAX FROM EMPLOYEES; D. SELECT SALARY * TAX_RATE TAX FROM EMPLOYEES;.
When using ampersand substitution variables in the following query, how many times will you be prompted to input a value for the variable called JOB the first time this query is executed? SELECT FIRST_NAME, 'ampersandJOB' FROM EMPLOYEES WHERE JOB_ID LIKE '%'||ampersandJOB||'%' AND ‘ampersandampersandJOB’ BETWEEN 'A' AND 'Z'; A. 0 B. 1 C. 2 D. 3.
4 Single-Row Functions 4.01 Describe Various Types of Functions Available in SQL 4.02 Use Character, Number, and Date Functions in SELECT Statements.
Which statements regarding single-row functions are true? (Choose all that apply.) A. They may return more than one result. B. They execute once for each record processed. C. They may have zero or more input parameters. D. They must have at least one mandatory parameter.
Which of these are single-row character-case conversion functions? (Choose all that apply.) A. LOWER B. SMALLER C. INITCASE D. INITCAP.
What value is returned after executing the following statement? (Choose the best answer.) SELECT LENGTH('How_long_is_a_piece_of_string?') FROM DUAL; A. 29 B. 30 C. 24 D. None of the above.
What value is returned after executing the following statement? (Choose the best answer.) SELECT SUBSTR('How_long_is_a_piece_of_string?', 5,4) FROM DUAL; A. long B. _long C. ring? D. None of the above.
What value is returned after executing the following statement? (Choose the best answer.) SELECT INSTR('How_long_is_a_piece_of_string?','_',5,3) FROM DUAL; A. 4 B. 14 C. 12 D. None of the above.
What value is returned after executing the following statement? (Choose the best answer.) SELECT REPLACE('How_long_is_a_piece_of_string?','_','') FROM DUAL; A. How long is a piece of string? B. How_long_is_a_piece_of_string? C. Howlongisapieceofstring? D. None of the above.
What value is returned after executing the following statement? (Choose the best answer.) SELECT MOD(14,3) FROM DUAL; A. 3 B. 42 C. 2 D. None of the above.
Assuming SYSDATE=07-JUN-1996 12:05pm, what value is returned after executing the following statement? (Choose the best answer.) SELECT ADD_MONTHS(SYSDATE,-1) FROM DUAL; A. 07-MAY-1996 12:05pm B. 06-JUN-1996 12:05pm C. 07-JUL-1996 12:05pm D. None of the above.
What value is returned after executing the following statement? Take note that 01-JAN-2009 occurs on a Thursday. (Choose the best answer.) SELECT NEXT_DAY('01-JAN-2009','wed') FROM DUAL; A. 07-JAN-2009 B. 31-JAN-2009 C. Wednesday D. None of the above.
Assuming SYSDATE=30-DEC-2007, what value is returned after executing the following statement? (Choose the best answer.) SELECT TRUNC(SYSDATE,'YEAR') FROM DUAL; A. 31-DEC-2007 B. 01-JAN-2008 C. 01-JAN-2007 D. None of the above.
5 Using Conversion Functions and Conditional Expressions 5.01 Describe Various Types of Conversion Functions Available in SQL 5.02 Use the TO_CHAR, TO_NUMBER, and TO_DATE Conversion Functions 5.03 Apply Conditional Expressions in a SELECT Statement.
What type of conversion is performed by the following statement? (Choose the best answer.) SELECT LENGTH(3.14285) FROM DUAL; A. Explicit conversion B. Implicit conversion C. 7 D. None of the above.
Choose any incorrect statements regarding conversion functions. (Choose all that apply.) A. TO_CHAR may convert date items to character items. B. TO_DATE may convert character items to date items. C. TO_CHAR may convert numbers to character items. D. TO_DATE may convert date items to character items.
What value is returned after executing the following statement? (Choose the best answer.) SELECT TO_NUMBER(1234.49, 999999.9) FROM DUAL; A. 1234.49 B. 001234.5 C. 1234.5 D. None of the above.
What value is returned after executing the following statement? (Choose the best answer.) SELECT TO_CHAR(1234.49, '999999.9') FROM DUAL; A. 1234.49 B. 001234.5 C. 1234.5 D. None of the above.
If SYSDATE returns 12-JUL-2009, what is returned by the following statement? (Choose the best answer.) SELECT TO_CHAR(SYSDATE, 'fmMONTH, YEAR') FROM DUAL; A. JUL, 2009 B. JULY, TWO THOUSAND NINE C. JUL-09 D. None of the above.
If SYSDATE returns 12-JUL-2009, what is returned by the following statement? (Choose the best answer.) SELECT TO_CHAR(SYSDATE, 'fmDDth MONTH') FROM DUAL; A. 12TH JULY B. 12th July C. TWELFTH JULY D. None of the above.
If SYSDATE returns 12-JUL-2009, what is returned by the following statement? (Choose the best answer.) SELECT TO_CHAR(TO_DATE(TO_CHAR(SYSDATE,'DD'),'DD'),'YEAR') FROM DUAL; A. 2009 B. TWO THOUSAND NINE C. 12-JUL-2009 D. None of the above.
What value is returned after executing the following statement? (Choose the best answer.) SELECT NVL2(NULLIF('CODA','SID'),'SPANIEL','TERRIER') FROM DUAL; A. SPANIEL B. TERRIER C. NULL D. None of the above.
What value is returned after executing the following statement? (Choose the best answer.) SELECT NVL(SUBSTR('AM I NULL',10),'YES I AM') FROM DUAL; A. NO B. NULL C. YES I AM D. None of the above.
If SYSDATE returns 12-JUL-2009, what is returned by the following statement? (Choose the best answer.) SELECT DECODE(TO_CHAR(SYSDATE,'MM'),'02','TAX DUE','PARTY') FROM DUAL; A. TAX DUE B. PARTY C. 02 D. None of the above.
6 Reporting Aggregated Data Using the Group Functions 6.01 Describe the Group Functions 6.02 Identify the Available Group Functions 6.03 Group Data Using the GROUP BY Clause 6.04 Include or Exclude Grouped Rows Using the HAVING Clause.
What result is returned by the following statement? (Choose the best answer.) SELECT COUNT(*) FROM DUAL; A. NULL B. 0 C. 1 D. None of the above.
Choose one correct statement regarding group functions. A. Group functions may only be used when a GROUP BY clause is present. B. Group functions can operate on multiple rows at a time. C. Group functions only operate on a single row at a time. D. Group functions can execute multiple times within a single group.
What value is returned after executing the following statement? (Choose the best answer.) SELECT SUM(SALARY) FROM EMPLOYEES; Assume there are ten employee records and each contains a SALARY value of 100, except for one, which has a null value in the SALARY field. A. 900 B. 1000 C. NULL D. None of the above.
Which values are returned after executing the following statement? (Choose all that apply.) SELECT COUNT(*), COUNT(SALARY) FROM EMPLOYEES; Assume there are ten employee records and each contains a SALARY value of 100, except for one, which has a null value in their SALARY field. A. 10 and 10 B. 10 and NULL C. 10 and 9 D. None of the above.
What value is returned after executing the following statement? (Choose the best answer.) SELECT AVG(NVL(SALARY,100)) FROM EMPLOYEES; Assume there are ten employee records and each contains a SALARY value of 100, except for one employee, who has a null value in the SALARY field. A. NULL B. 90 C. 100 D. None of the above.
What value is returned after executing the following statement? (Choose the best answer.) SELECT SUM((AVG(LENGTH(NVL(SALARY,0))))) FROM EMPLOYEES GROUP BY SALARY; Assume there are ten employee records and each contains a SALARY value of 100, except for one, which has a null value in the SALARY field. A. An error is returned B. 3 C. 4 D. None of the above.
How many records are returned by the following query? (Choose the best answer.) SELECT SUM(SALARY), DEPARTMENT_ID FROM EMPLOYEES GROUP BY DEPARTMENT_ID; Assume there are 11 nonnull and 1 null unique DEPARTMENT_ID values. All records have a nonnull SALARY value. A. 12 B. 11 C. NULL D. None of the above.
What values are returned after executing the following statement? (Choose the best answer.) SELECT JOB_ID, MAX_SALARY FROM JOBS GROUP BY MAX_SALARY; Assume that the JOBS table has ten records with the same JOB_ID value of DBA and the same MAX_SALARY value of 100. A. One row of output with the values DBA, 100 B. Ten rows of output with the values DBA, 100 C. An error is returned D. None of the above.
How many rows of data are returned after executing the following statement? (Choose the best answer.) SELECT DEPT_ID, SUM(NVL(SALARY,100)) FROM EMP GROUP BY DEPT_ID HAVING SUM(SALARY) > 400; Assume the EMP table has ten rows and each contains a SALARY value of 100, except for one, which has a null value in the SALARY field. The first and second five rows have DEPT_ID values of 10 and 20, respectively. A. Two rows B. One row C. Zero rows D. None of the above.
How many rows of data are returned after executing the following statement? (Choose the best answer.) SELECT DEPT_ID, SUM(SALARY) FROM EMP GROUP BY DEPT_ID HAVING SUM(NVL(SALARY,100)) > 400; Assume the EMP table has ten rows and each contains a SALARY value of 100, except for one, which has a null value in the SALARY field. The first and second five rows have DEPT_ID values of 10 and 20, respectively. A. Two rows B. One row C. Zero rows D. None of the above.
7 Displaying Data from Multiple Tables 7.01 Write SELECT Statements to Access Data from More Than One Table Using Equijoins and Nonequijoins 7.02 Join a Table to Itself Using a Self-Join 7.03 View Data that Does Not Meet a Join Condition Using Outer Joins 7.04 Generate a Cartesian Product of Two or More Tables.
The EMPLOYEES and DEPARTMENTS tables have two identically named columns: DEPARTMENT_ID and MANAGER_ID. Which of these statements joins these tables based only on common DEPARTMENT_ID values? (Choose all that apply.) A. SELECT * FROM EMPLOYEES NATURAL JOIN DEPARTMENTS; B. SELECT * FROM EMPLOYEES E NATURAL JOIN DEPARTMENTS D ON E.DEPARTMENT_ID=D.DEPARTMENT_ID; C. SELECT * FROM EMPLOYEES NATURAL JOIN DEPARTMENTS USING (DEPARTMENT_ID); D. None of the above.
The EMPLOYEES and DEPARTMENTS tables have two identically named columns: DEPARTMENT_ID and MANAGER_ID. Which statements join these tables based on both column values? (Choose all that apply.) A. SELECT * FROM EMPLOYEES NATURAL JOIN DEPARTMENTS; B. SELECT * FROM EMPLOYEES JOIN DEPARTMENTS USING (DEPARTMENT_ ID,MANAGER_ID); C. SELECT * FROM EMPLOYEES E JOIN DEPARTMENTS D ON E.DEPARTMENT_ ID=D.DEPARTMENT_ID AND E.MANAGER_ID=D.MANAGER_ID; D. None of the above.
Which join is performed by the following query? (Choose the best answer.) SELECT E.JOB_ID,J.JOB_ID FROM EMPLOYEES E JOIN JOBS J ON (E.SALARY < J.MAX_SALARY); A. Equijoin B. Nonequijoin C. Cross join D. Outer join.
Which of the following statements are syntactically correct? (Choose all that apply.) A. SELECT * FROM EMPLOYEES E JOIN DEPARTMENTS D USING (DEPARTMENT_ID); B. SELECT * FROM EMPLOYEES JOIN DEPARTMENTS D USING (D.DEPARTMENT_ID); C. SELECT D.DEPARTMENT_ID FROM EMPLOYEES JOIN DEPARTMENTS D USING (DEPARTMENT_ID); D. None of the above.
Which of the following statements are syntactically correct? (Choose all that apply.) A. SELECT E.EMPLOYEE_ID, J.JOB_ID PREVIOUS_JOB, E.JOB_ID CURRENT_JOB FROM JOB_HISTORY J CROSS JOIN EMPLOYEES E ON (J.START_DATE=E.HIRE_DATE); B. SELECT E.EMPLOYEE_ID, J.JOB_ID PREVIOUS_JOB, E.JOB_ID CURRENT_JOB FROM JOB_HISTORY J JOIN EMPLOYEES E ON (J.START_DATE=E.HIRE_DATE); C. SELECT E.EMPLOYEE_ID, J.JOB_ID PREVIOUS_JOB, E.JOB_ID CURRENT_JOB FROM JOB_HISTORY J OUTER JOIN EMPLOYEES E ON (J.START_DATE=E.HIRE_DATE); D. None of the above.
Choose one correct statement regarding the following query: SELECT * FROM EMPLOYEES E JOIN DEPARTMENTS D ON (D.DEPARTMENT_ID=E.DEPARTMENT_ID) JOIN LOCATIONS L ON (L.LOCATION_ID =D.LOCATION_ID); A. Joining three tables is not permitted. B. A Cartesian product is generated. C. The JOIN…ON clause may be used for joins between multiple tables. D. None of the above.
How many rows are returned after executing the following statement? (Choose the best answer.) SELECT * FROM REGIONS R1 JOIN REGIONS R2 ON (R1.REGION_ID=LENGTH(R2.REGION_ NAME)/2); The REGIONS table contains the following row data: A. 2 B. 3 C. 4 D. None of the above.
Choose one correct statement regarding the following query. SELECT C.COUNTRY_ID FROM LOCATIONS L RIGHT OUTER JOIN COUNTRIES C ON (L.COUNTRY_ID=C.COUNTRY_ID) WHERE L.COUNTRY_ID is NULL; A. No rows in the LOCATIONS table have the COUNTRY_ID values returned. B. No rows in the COUNTRIES table have the COUNTRY_ID values returned. C. The rows returned represent the COUNTRY_ID values for all the rows in the LOCATIONS table. D. None of the above.
Which of the following statements are syntactically correct? (Choose all that apply.) A. SELECT JH.JOB_ID FROM JOB_HISTORY JH RIGHT OUTER JOIN JOBS J ON JH.JOB_ID=J.JOB_ID; B. SELECT JOB_ID FROM JOB_HISTORY JH RIGHT OUTER JOIN JOBS J ON (JH.JOB_ID=J.JOB_ID); C. SELECT JOB_HISTORY.JOB_ID FROM JOB_HISTORY OUTER JOIN JOBS ON JOB_HISTORY.JOB_ID=JOBS.JOB_ID; D. None of the above.
If the REGIONS table, which contains 4 rows, is cross joined to the COUNTRIES table, which contains 25 rows, how many rows appear in the final results set? (Choose the best answer.) A. 100 rows B. 4 rows C. 25 rows D. None of the above.
8 Using Subqueries to Solve Problems 8.01 Define Subqueries 8.02 Describe the Types of Problems That the Subqueries Can Solve 8.03 List the Types of Subqueries 8.04 Write Single-Row and Multiple-Row Subqueries.
Consider this generic description of a SELECT statement: SELECT select_list FROM table WHERE condition GROUP BY expression_1 HAVING expression_2 ORDER BY expression_3 ; Where could subqueries be used? (Choose all correct answers.) A. select_list B. table C. condition D. expression_1 E. expression_2 F. expression_3.
A query can have a subquery embedded within it. Under what circumstances could there be more than one subquery? (Choose the best answer.) A. The outer query can include an inner query. It is not possible to have another query within the inner query. B. It is possible to embed a single-row subquery inside a multiple-row subquery, but not the other way around. C. The outer query can have multiple inner queries, but they must not be embedded within each other. D. Subqueries can be embedded within each other with no practical limitations on depth.
Consider this statement: SELECT employee_id, last_name FROM employees WHERE salary > (SELECT avg(salary) FROM employees); When will the subquery be executed? (Choose the best answer.) A. It will be executed before the outer query. B. It will be executed after the outer query. C. It will be executed concurrently with the outer query. D. It will be executed once for every row in the EMPLOYEES table.
Consider this statement: SELECT o.employee_id, o.last_name FROM employees o WHERE o.salary > (SELECT avg(i.salary) FROM employees i WHERE i.department_id=o.department_id); When will the subquery be executed? (Choose the best answer.) A. It will be executed before the outer query. B. It will be executed after the outer query. C. It will be executed concurrently with the outer query. D. It will be executed once for every row in the EMPLOYEES table.
Consider the following statement: SELECT last_name FROM employees JOIN departments ON employees.department_id = departments.department_id WHERE department_name='Executive'; and this statement: SELECT last_name FROM employees WHERE department_id IN (SELECT department_id FROM departments WHERE department_name='Executive'); What can be said about the two statements? (Choose two correct answers.) A. The two statements should generate the same result. B. The two statements could generate different results. C. The first statement will always run successfully; the second statement will error if there are two departments with DEPARTMENT_NAME='Executive'. D. Both statements will always run successfully, even if there are two departments with DEPARTMENT_NAME='Executive'.
What are the distinguishing characteristics of a scalar subquery? (Choose two correct answers.) A. A scalar subquery returns one row. B. A scalar subquery returns one column. C. A scalar subquery cannot be used in the SELECT LIST of the parent query. D. A scalar subquery cannot be used as a correlated subquery.
Which comparison operator cannot be used with multiple-row subqueries? (Choose the best answer.) A. ALL B. ANY C. IN D. NOT IN E. All the above can be used.
Consider this statement: SELECT last_name, (SELECT count(*) FROM departments) FROM employees WHERE salary = (SELECT salary FROM employees); What is wrong with it? (Choose the best answer.) A. Nothing is wrong—the statement should run without error. B. The statement will fail because the subquery in the SELECT list references a table that is not listed in the FROM clause. C. The statement will fail if the second query returns more than one row. D. The statement will run but is extremely inefficient because of the need to run the second subquery once for every row in EMPLOYEES.
Which of the following statements are equivalent? (Choose two answers.) A. SELECT employee_id FROM employees WHERE salary < ALL (SELECT salary FROM employees WHERE department_id=10); B. SELECT employee_id FROM employees WHERE salary < (SELECT min(salary) FROM employees WHERE department_id=10); C. SELECT employee_id FROM employees WHERE salary NOT >= ANY (SELECT salary FROM employees WHERE department_id=10); D. SELECT employee_id FROM employees e JOIN departments d ON e.department_id=d.department_id WHERE e.salary < (SELECT min(salary) FROM employees) AND d.department_id=10;.
Consider this statement, which is intended to prompt for an employee’s name and then find all employees who have the same job as the first employee: SELECT last_name,employee_id FROM employees WHERE job_id = (SELECT job_id FROM employees WHERE last_name = 'ampersandName'); What would happen if a value were given for &Name that did not match with any row in EMPLOYEES? (Choose the best answer.) A. The statement would fail with an error. B. The statement would return every row in the table. C. The statement would return no rows. D. The statement would return all rows where JOB_ID is NULL.
9 Using the Set Operators 9.01 Describe the Set Operators 9.02 Use a Set Operator to Combine Multiple Queries into a Single Query 9.03 Control the Order of Rows Returned.
Which of these set operators will not sort the rows? (Choose the best answer.) A. INTERSECT B. MINUS C. UNION D. UNION ALL.
Which of these operators will remove duplicate rows from the final result? (Choose all that apply.) A. INTERSECT B. MINUS C. UNION D. UNION ALL.
If a compound query contains both a MINUS and an INTERSECT operator, which will be applied first? (Choose the best answer.) A. The INTERSECT, because INTERSECT has higher precedence than MINUS. B. The MINUS, because MINUS has a higher precedence than INTERSECT. C. The precedence is determined by the order in which they are specified. D. It is not possible for a compound query to include both MINUS and INTERSECT.
There are four rows in the REGIONS table. Consider the following statements and choose how many rows will be returned for each: 0, 4, 8, or 16. A. SELECT * FROM regions UNION SELECT * FROM regions; B. SELECT * FROM regions UNION ALL SELECT * FROM regions; C. SELECT * FROM regions MINUS SELECT * FROM regions; D. SELECT * FROM regions INTERSECT SELECT * FROM regions;.
Consider this compound query: SELECT empno, hired FROM emp UNION ALL SELECT emp_id,hired,fired FROM ex_emp; The columns EMP.EMPNO and EX_EMP.EMP_ID are integer; the column EMP.HIRED is timestamp; the columns EX_EMP.HIRED and EX_EMP.FIRED are date. Why will the statement fail? (Choose the best answer.) A. Because the columns EMPNO and EMP_ID have different names B. Because the columns EMP.HIRED and EX_EMP.HIRED are different data types C. Because there are two columns in the first query and three columns in the second query D. For all the reasons above E. The query will succeed.
Which line of this statement will cause it to fail? (Choose the best answer.) A. SELECT ename, hired FROM current_staff B. ORDER BY ename C. MINUS D. SELECT ename, hired FROM current staff E. WHERE deptno=10 F. ORDER BY ename;.
Study this statement: SELECT ename FROM emp UNION ALL SELECT ename FROM ex_emp; In what order will the rows be returned? (Choose the best answer.) A. The rows from each table will be grouped and within each group will be sorted on ENAME. B. The rows from each table will be grouped but not sorted. C. The rows will not be grouped but will all be sorted on ENAME. D. The rows will be neither grouped nor sorted.
10 Manipulating Data 10.01 Describe Each Data Manipulation Language (DML) Statement 10.02 Insert Rows into a Table 10.03 Update Rows in a Table 10.04 Delete Rows from a Table 10.05 Control Transactions.
Which of the following commands can be rolled back? A. COMMIT B. DELETE C. INSERT D. MERGE E. TRUNCATE F. UPDATE.
How can you change the primary key value of a row? (Choose the best answer.) A. You cannot change the primary key value. B. Change it with a simple UPDATE statement. C. The row must be removed with a DELETE and reentered with an INSERT. D. This is only possible if the row is first locked with a SELECT FOR UPDATE.
If an UPDATE or DELETE command has a WHERE clause that gives it a scope of several rows, what will happen if there is an error part way through execution? The command is one of several in a multistatement transaction. (Choose the best answer.) A. The command will skip the row that caused the error and continue. B. The command will stop at the error, and the rows that have been updated or deleted will remain updated or deleted. C. Whatever work the command had done before hitting the error will be rolled back, but work done already by the transaction will remain. D. The whole transaction will be rolled back.
If a table T1 has four numeric columns, C1, C2, C3, and C4, which of these statements will succeed? (Choose the best answer.) A. INSERT INTO T1 VALUES (1,2,3,null); B. INSERT INTO T1 values (‘1’,‘2’,‘3’,‘4’); C. INSERT INTO T1 SELECT * FROM T1; D. All the statements (A, B, and C) will succeed. E. None of the statements (A, B, or C) will succeed.
Study the result of this SELECT statement: SELECT * FROM t1; C1 C2 C3 C4 ---------- ---------- ---------- ---------- 1 2 3 4 5 6 7 8 If you issue this statement: INSERT INTO t1 (c1,c2) VALUES (SELECT c1,c2 FROM t1); why will it fail? (Choose the best answer.) A. Because values are not provided for all the table’s columns: there should be NULLs for C3 and C4. B. Because the subquery returns multiple rows: it requires a WHERE clause to restrict the number of rows returned to one. C. Because the subquery is not scalar: it should use MAX or MIN to generate scalar values. D. Because the VALUES keyword is not used with a subquery. E. It will succeed, inserting two rows with NULLs for C3 and C4.
Consider this statement: INSERT INTO regions (region_id,region_name) VALUES ((SELECT max(region_id)+1 FROM regions), 'Great Britain'); What will the result be? (Choose the best answer.) A. The statement will not succeed if the value generated for REGION_ID is not unique, because REGION_ID is the primary key of the REGIONS table. B. The statement has a syntax error because you cannot use the VALUES keyword with a subquery. C. The statement will execute without error. D. The statement will fail if the REGIONS table has a third column.
You want to insert a row and then update it. What sequence of steps should you follow? (Choose the best answer.) A. INSERT, UPDATE, COMMIT B. INSERT, COMMIT, UPDATE, COMMIT C. INSERT, SELECT FOR UPDATE, UPDATE, COMMIT D. INSERT, COMMIT, SELECT FOR UPDATE, UPDATE, COMMIT.
If you issue this command: UPDATE employees SET salary=salary * 1.1; what will be the result? (Choose the best answer.) A. The statement will fail because there is no WHERE clause to restrict the rows affected. B. The first row in the table will be updated. C. There will be an error if any row has its SALARY column NULL. D. Every row will have SALARY incremented by 10 percent, unless SALARY was NULL.
How can you delete the values from one column of every row in a table? (Choose the best answer.) A. Use the DELETE COLUMN command. B. Use the TRUNCATE COLUMN command. C. Use the UPDATE command. D. Use the DROP COLUMN command.
Which of these commands will remove every row in a table while keeping its structure intact? (Choose one or more correct answers.) A. A DELETE command with no WHERE clause B. A DROP TABLE command C. A TRUNCATE command D. An UPDATE command, setting every column to NULL and with no WHERE clause.
User JOHN updates some rows and asks user ROOPESH to log in and check the changes before he commits them. Which of the following statements is true? (Choose the best answer.) A. ROOPESH can see the changes but cannot alter them because JOHN will have locked the rows. B. ROOPESH will not be able to see the changes. C. JOHN must commit the changes so that ROOPESH can see them and, if necessary, roll them back. D. JOHN must commit the changes so that ROOPESH can see them, but only JOHN can roll them back.
User JOHN updates some rows but does not commit the changes. User ROOPESH queries the rows that JOHN updated. Which of the following statements is true? (Choose the best answer.) A. ROOPESH will not be able to see the rows because they will be locked. B. ROOPESH will be able to see the new values, but only if he logs in as JOHN. C. ROOPESH will see the old versions of the rows. D. ROOPESH will see the state of the data as it was when JOHN last created a SAVEPOINT.
Which of these commands will terminate a transaction? (Choose three correct answers.) A. COMMIT B. DELETE C. ROLLBACK D. ROLLBACK TO SAVEPOINT E. SAVEPOINT F. TRUNCATE.
11 Using DDL Statements to Create and Manage Tables 11.01 Categorize the Main Database Objects 11.02 Review the Table Structure 11.03 List the Data Types That Are Available for Columns 11.04 Create a Simple Table 11.05 Explain How Constraints Are Created at the Time of Table Creation.
If a table is created without specifying a schema, in which schema will it be? (Choose the best answer.) A. It will be an orphaned table, without a schema. B. The creation will fail. C. It will be in the SYS schema. D. It will be in the schema of the user creating it. E. It will be in the PUBLIC schema.
Several object types share the same namespace, and therefore cannot have the same name in the same schema. Which of the following object types is not in the same namespace as the others? (Choose the best answer.) A. Index B. PL/SQL stored procedure C. Synonym D. Table E. View.
Which of these statements will fail because the table name is not legal? (Choose two answers.) A. create table "SELECT" (col1 date); B. create table "lowercase" (col1 date); C. create table number1 (col1 date); D. create table 1number (col1 date); E. create table update (col1 date);.
What are distinguishing characteristics of heap tables? (Choose two answers.) A. A heap can store variable length rows. B. More than one table can store rows in a single heap. C. Rows in a heap are in random order. D. Heap tables cannot be indexed. E. Tables in a heap do not have a primary key.
Which of the following data types are variable length? (Choose all correct answers.) A. BLOB B. CHAR C. LONG D. NUMBER E. RAW F. VARCHAR2.
Study these statements: CREATE TABLE tab1 (c1 NUMBER(1), c2 DATE); ALTER SESSION SET nls_date_format='dd-mm-yy'; INSERT INTO tab1 VALUES (1.1,'31-01-07'); Will the insert succeed? (Choose the best answer) A. The insert will fail because the 1.1 is too long. B. The insert will fail because the '31-01-07' is a string, not a date. C. The insert will fail for both reasons A and B. D. The insert will succeed.
Which of the following is not supported by Oracle as an internal data type? (Choose the best answer.) A. CHAR B. FLOAT C. INTEGER D. STRING.
Consider this statement: CREATE TABLE t1 AS SELECT * FROM regions WHERE 1=2; What will be the result? (Choose the best answer.) A. There will be an error because of the impossible condition. B. No table will be created because the condition returns FALSE. C. The table T1 will be created but no rows inserted because the condition returns FALSE. D. The table T1 will be created and every row in REGIONS inserted because the condition returns a NULL as a row filter.
When a table is created with a statement such as the following: CREATE TABLE newtab AS SELECT * FROM tab; will there be any constraints on the new table? (Choose the best answer.) A. The new table will have no constraints, because constraints are not copied when creating tables with a subquery. B. All the constraints on TAB will be copied to NEWTAB. C. Primary key and unique constraints will be copied but not check and not null constraints. D. Check and not null constraints will be copied but not unique or primary key. E. All constraints will be copied, except foreign key constraints.
Which types of constraint require an index? (Choose all that apply.) A. CHECK B. NOT NULL C. PRIMARY KEY D. UNIQUE.
A transaction consists of two statements. The first succeeds, but the second (which updates several rows) fails partway through because of a constraint violation. What will happen? (Choose the best answer.) A. The whole transaction will be rolled back. B. The second statement will be rolled back completely, and the first will be committed. C. The second statement will be rolled back completely, and the first will remain uncommitted. D. Only the one update that caused the violation will be rolled back; everything else will be committed. E. Only the one update that caused the violation will be rolled back; everything else will remain uncommitted.
1 Relational Database Design Using Oracle 1.01 Position the Server Technologies 1.02 Understand Relational Structures 1.03 Summarize the SQL Language 1.04 Use the Client Tools 1.05 Create the Demonstration Schemas.
What components of the IT environment can Oracle Enterprise Manager Cloud Control manage? (Choose the best answer.) A. Oracle databases B. Oracle application servers C. Third-party products D. The server machines E. All of the above.
What languages can run within the database? (Choose all that apply.) A. SQL B. C C. PL/SQL D. Java E. Any other language linked to the OCI libraries.
Data that is modeled into a form suitable for processing in a relational database may be described as being (Choose the best answer.) A. First normal form B. Third normal form C. Abnormal form D. Paranormal form.
An entity-relationship diagram shows data modeled into (Choose the best answer.) A. Two-dimensional tables B. Multidimensional tables C. Hierarchical structures D. Object-oriented structures.
SQL is a set-oriented language. Which of these features is a consequence of this? (Choose the best answer.) A. Individual rows must have a unique identifier. B. Sets of users can be managed in groups. C. SQL statements can be placed within blocks of code in other languages, such as Java and PL/SQL. D. One statement can affect multiple rows.
Which of these constructs is not part of the SQL language? (Choose all that apply.) A. Iteration, based on WHILE.. B. Iteration, based on FOR..DO C. Branching, based on IF..THEN..ELSE D. Transaction control, based on COMMIT E. Transaction control, based on ROLLBACK.
Which of these statements regarding SQL Developer are correct? (Choose two answers.) A. SQL Developer cannot connect to databases earlier than release 10g. B. SQL Developer can be installed outside an Oracle Home. C. SQL Developer can store passwords. D. SQL Developer relies on an LDAP directory for name resolution.
Which of the following are requirements for using SQL Developer? (Choose two correct answers.) A. A Java Runtime Environment B. The OCI libraries C. A name resolution method such as LDAP or a TNSNAMES.ORA file D. The SQL*Plus libraries E. A graphical terminal.
Where may the demonstration schemas be created? (Choose the best answer.) A. The demonstration schemas must be created in a demonstration database. B. The demonstration schemas cannot be created in a production database. C. The demonstration schemas can be created in any database. D. The demonstration schemas can be created in any database if the demonstration user is created first.
ExamsBoost ExamsBoost.
Which two are true about aggregate functions? (Choose two.) A. You can use aggregate functions in any clause of a SELECT statement. B. You can use aggregate functions only in the column list of the select clause and in the WHERE clause of a SELECT statement. C. You can mix single row columns with aggregate functions in the column list of a SELECT statement by grouping on the single row columns. D. You can pass column names, expressions, constants, or functions as parameter to an aggregate function. E. You can use aggregate functions on a table, only by grouping the whole table as one single group. F. You cannot group the rows of a table by more than one column while using aggregate functions.
See the structure of the PROGRAMS table: Which two SQL statements would execute successfully? (Choose two.) A. SELECT NVL(ADD_MONTHS(END_DATE, 1), SYSDATE)FROM programs; B. SELECT TO_DATE(NVL(SYSDATE-END_DATE, SYSDATE))FROM programs; C. SELECT NVL(MONTHS_BETWEEN(start_date, end_date), 'Ongoing')FROM programs; D. SELECT NVL(TO_CHAR(MONTHS_BETWEEN(start_date, end_date)), 'Ongoing') FROM programs;.
Exhibit contains the structure of PRODUCTS table: What would be the outcome of executing the above SQL statement? A. It produces an error B. It shows the names of products whose list price is the second highest in the table. C. It shown the names of all products whose list price is less than the maximum list price D. It shows the names of all products in the table.
See the Exhibits and examine the structures of PRODUCTS, SALES and CUSTOMERS table: Which statement is true regarding the outcome of this query? A. It produces an error because the NATURAL join can be used only with two tables B. It produces an error because a column used in the NATURAL join cannot have a qualifier C. It produces an error because all columns used in the NATURAL join should have a qualifier D. It executes successfully.
You work as a database administrator at ABC.com. You study the exhibit carefully. Exhibit: Which statement is true regarding the execution of the above statement? A. The view will be created and you can perform DLM operations on the view B. The view will not be created because the join statements are not allowed for creating a view C. The view will not be created because the GROUP BY clause is not allowed for creating a view D. The view will be created but no DML operations will be allowed on the view.
Which three statements are true regarding the data types in Oracle Database 10g/11g? (Choose three.) A. The BLOB data type column is used to store binary data in an operating system file B. The minimum column width that can be specified for a VARCHAR2 data type column is one C. A TIMESTAMP data type column stores only time values with fractional seconds D. The value for a CHAR data type column is blank-padded to the maximum defined column width E. Only One LONG column can be used per table.
Which three are true? (Choose three.) A. A MERGE statement is used to merge the data of one table with data from another. B. A MERGE statement replaces the data of one table with that of another. C. A MERGE statement can be used to insert new rows into a table. D. A MERGE statement can be used to update existing rows in a table.
Which two statements are true regarding views? (Choose two.) A. A sub query that defines a view cannot include the GROUP BY clause B. A view is created with the sub query having the DISTINCT keyword can be updated C. A Data Manipulation Language (DML) operation can be performed on a view that is created with the sub query having all the NOT NULL columns of a table D. A view that is created with the sub query having the pseudo column ROWNUM keyword cannot be updated.
Examine the structure of the EMPLOYEES and NEW_EMPLOYEES tables: Which DELETE statement is valid? A. DELETE FROM employeesWHERE employee_id = (SELECT employee_id FROM employees); B. DELETE * FROM employeesWHERE employee_id=(SELECT employee_id FROM new_employees); C. DELETE FROM employeesWHERE employee_id IN (SELECT employee_id . FROM new_employees . WHERE name = ‘Carrey’); D. DELETE * FROM employeesWHERE employee_id IN (SELECT employee_id . FROM new_employees . WHERE name = ‘Carrey’);.
View the Exhibits and examine the structures of the PROMOTIONS and SALES tables. Exhibit 1: Which statement is true regarding the output of the above query? A. It gives details of product IDs that have been sold irrespective of whether they had a promo or not. B. It gives the details of promos for which there have been no sales. C. It gives the details of promos for which there have been sales. D. It gives details of all promos irrespective of whether they have resulted in a sale or not.
Examine the structure of the EMPLOYEES table: Which UPDATE statement is valid? A. UPDATE employeesSET first_name = ‘John’SET last_name = ‘Smith’WHERE employee_id = 180; B. UPDATE employeesSET first_name = ‘John’,SET last_name = ‘Smoth’WHERE employee_id = 180; C. UPDATE employeeSET first_name = ‘John’AND last_name = ‘Smith’WHERE employee_id = 180; D. UPDATE employeeSET first_name = ‘John’, last_name = ‘Smith’WHERE employee_id = 180;.
Denunciar test Consentimiento Condiciones de uso