|Prova||Oracle Database 11g: SQL Fundamentals I|
|Número de questõesFormato||64
|Nota de aprovação||60%|
|Treinamento oficial recomendado||Oracle Database: SQL Fundamentals I|
Tópicos do exame
|Retrieving Data Using the SQL SELECT Statement|
|List the capabilities of SQL SELECT statements|
|Execute a basic SELECT statement|
|Restricting and Sorting Data|
|Limit the rows that are retrieved by a query|
|Sort the rows that are retrieved by a query|
|Use ampersand substitution to restrict and sort output at runtime|
|Using Single-Row Functions to Customize Output|
|Describe various types of functions available in SQL|
|Use character, number, and date functions in SELECT statements|
|Using Conversion Functions and Conditional Expressions|
|Describe various types of conversion functions that are available in SQL|
|Use the TO_CHAR, TO_NUMBER, and TO_DATE conversion functions|
|Apply conditional expressions in a SELECT statement|
|Reporting Aggregated Data Using the Group Functions|
|Identify the available group functions|
|Describe the use of group functions|
|Group data by using the GROUP BY clause|
|Include or exclude grouped rows by using the HAVING clause|
|Displaying Data from Multiple Tables|
|Write SELECT statements to access data from more than one table using equijoins and nonequijoins|
|Join a table to itself by using a self-join|
|View data that generally does not meet a join condition by using outer joins|
|Generate a Cartesian product of all rows from two or more tables|
|Using Subqueries to Solve Queries|
|Describe the types of problems that the subqueries can solve|
|List the types of subqueries|
|Write single-row and multiple-row subqueries|
|Using the Set Operators|
|Describe set operators|
|Use a set operator to combine multiple queries into a single query|
|Control the order of rows returned|
|Describe each data manipulation language (DML) statement|
|Insert rows into a table|
|Update rows in a table|
|Delete rows from a table|
|Using DDL Statements to Create and Manage Tables|
|Categorize the main database objects|
|Review the table structure|
|List the data types that are available for columns|
|Create a simple table|
|Explain how constraints are created at the time of table creation|
|Describe how schema objects work|
|Creating Other Schema Objects|
|Create simple and complex views|
|Retrieve data from views|
|Create, maintain, and use sequences|
|Create and maintain indexes|
|Create private and public synonyms|
Exemplos de questões
|1. Examine the structure of the EMP table:
You want to display the annual commission amount payable to each employee.
Which two SQL statements ensure that a value is displayed in the calculated column for all the employees? (Choose two)
2. Evaluate the following SQL statement:
SQL>SELECT TO_CHAR(1230,’00,999.99′) FROM DUAL;
What would be the outcome?
3. Examine the structure of the EMP table:
You want to generate a report that fulfills the following requirements:
You issue the following SQL statement:
SQL>SELECT ename, NVL(salary * comm_pct, 0)
What is the outcome?
4. Which statement is true regarding single-row functions?
5. Which two statements are true regarding a transaction? (Choose two)
6. You issue the following SQL command:
What would be the outcome?
B) It generates an error because SYSDATE cannot be used with the CHECK constraint
C) It generates an error because column size has not been specified for the EMPNO column
D) It generates an error because the default value SYSDATE cannot be used with the TIMESTAMP data type
You insert some rows into the EMP table.
Then you issue the following command:
EMPNO is the PRIMARY KEY.
You issue the following command and create a sequence EMP_SEQ:
You then insert some rows into the table using EMP_SEQ to populate the EMPNO column.
Which two statements are true regarding the EMP_SEQ sequence? (Choose two.)
B) It is automatically dropped when the EMP table is dropped
C) It cannot be used to populate any other column in any table
D) It is automatically dropped when the EMPNO column is dropped
E) It can be used to populate any other column in any table in the same schema
9. View the Exhibit below and examine the structures of DEPT and EMP tables.
You need to generate a report that displays all department names along with the corresponding average salary.
Which SQL statement would give the required result?
B) SELECT dname, AVG(sal) FROM emp JOIN dept USING(deptno) GROUP BY dname,sal;
C) SELECT dname, sal FROM emp JOIN dept USING(deptno) GROUP BY dname HAVING sal= AVG(sal);
D) SELECT dname, AVG(sal) FROM emp FULL OUTER JOIN dept USING(deptno) GROUP BY dname;
10. Examine the structure of the EMP table:
You want to find the highest average salary being paid in any department.
Which SQL statement gives the required result?
B) SELECT MAX(AVG(sal)) FROM emp GROUP BY deptno HAVING AVG(sal) >= MAX(sal);
C) SELECT deptno, MAX(AVG(sal)) FROM emp GROUP BY deptno;
D) SELECT deptno, MAX(AVG(sal)) FROM emp GROUP BY deptno, sal;