Código: 1Z0-051

Prova Oracle Database 11g: SQL Fundamentals I
Preço R$ 272,00
Duração 120 minutos
Número de questõesFormato 64
Múltipla escolha
Nota de aprovação 60%
Treinamento oficial recomendado Oracle Database: SQL Fundamentals I

 

Importante: esta prova pode ser feita ONLINE

 

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
Define subqueries
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
 Manipulating Data
Describe each data manipulation language (DML) statement
Insert rows into a table
Update rows in a table
Delete rows from a table
Control transactions
 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:

EMP
Name Null? Type
EMPNO
ENAME
SALARY
COMM_PCT
NOT NULL NUMBER(3)
VARCHAR2(25)
NUMBER(10,2)
NUMBER(4,2)

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)

A) SELECT ename, NVL(12 * salary * comm_pct,0) FROM emp;
B) SELECT ename, NVL2(12 * salary * comm_pct,0) FROM emp;
C) SELECT ename, COALESCE(12 * salary * comm_pct,0) FROM emp;
D) SELECT ename, DECODE(12 * salary * comm_pct,NULL,0) FROM emp;

2. Evaluate the following SQL statement:

SQL>SELECT TO_CHAR(1230,’00,999.99′) FROM DUAL;

What would be the outcome?

A) 1,230
B) 01,230
C) 1,230.00
D) 01,230.00

3. Examine the structure of the EMP table:

EMP
Name Null? Type
EMPNO
ENAME
SALARY
COMM_PCT
NOT NULL NUMBER(3)
VARCHAR2(25)
NUMBER(10,2)
NUMBER(4,2)

You want to generate a report that fulfills the following requirements:
1. Displays employees’ names and commission amounts
2. Excludes employees who do not have a commission
3. Displays a zero for employees whose SALARY does not have a value

You issue the following SQL statement:

SQL>SELECT ename, NVL(salary * comm_pct, 0)
FROM emp
WHERE comm_pct <> NULL;

What is the outcome?

A) It generates an error
B) It executes successfully but displays no result
C) It executes successfully but displays results that fulfill only requirements 1 and 3
D) It executes successfully and displays results that fulfill all the requirements

4. Which statement is true regarding single-row functions?

A) They cannot be nested
B) They can accept only one argument
C) They act on each row returned and return only one result per row
D) They act on each row returned and can return multiple results per row

5. Which two statements are true regarding a transaction? (Choose two)

A) If a data manipulation statement fails in the middle of a transaction, only that statement is rolled back
B) If a data manipulation statement fails in the middle of a transaction, the entire transaction is rolled back
C) A transaction is committed when a user exits the session normally without issuing an explicit COMMIT command
D) A transaction is rolled back when a user exits the session normally without issuing an explicit COMMIT command

6. You issue the following SQL command:

SQL> CREATE TABLE emp
(empno NUMBER PRIMARY KEY,
ename VARCHAR2(25)  NOT NULL,
dob   TIMESTAMP DEFAULT SYSDATE,
hire_date  DATE CONSTRAINT h_dt_chk CHECK (hire_date <= SYSDATE));

What would be the outcome?

A) It executes successfully and creates the table

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


7. Examine the structure of the EMP table:

EMP
Name Null? Type
EMPNO
ENAME
SALARY
COMM_PCT
NOT NULL NUMBER(3)
VARCHAR2(25)
NUMBER(10,2)
NUMBER(4,2)

You insert some rows into the EMP table.

Then you issue the following command:

SQL>ALTER TABLE emp
MODIFY (salary DEFAULT 10000);

What would be the outcome?

A) It generates an error because column definitions cannot be altered to add default values
B) Only subsequent insertions where SALARY is not specified would store the value 10000
C) It generates an error because column definitions cannot be altered if the table contains rows
D) All existing rows and subsequent rows where SALARY contains NULL value get updated to 10000

8. Examine the structure of the EMP table:

EMP
Name Null? Type
EMPNO
ENAME
SALARY
COMM_PCT
NOT NULL NUMBER(3)
VARCHAR2(25)
NUMBER(10,2)
NUMBER(4,2)

EMPNO is the PRIMARY KEY.

You issue the following command and create a sequence EMP_SEQ:

SQL> CREATE 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.)

A) It is not affected by any modifications to the EMP table

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?

A) SELECT dname, AVG(sal) FROM emp LEFT OUTER JOIN dept USING(deptno) GROUP BY dname;

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;

DEPT
Name Null? Type
DEPTNO
DNAME
LOC
NOT NULL NUMBER(2)
VARCHAR2(14)
VARCHAR2(13)
EMP
Name Null? Type
EMPNO
ENAME
HIRE_DATE
SAL
DEPTNO
NOT NULL NUMBER(4)
VARCHAR2(10)
DATE
NUMBER(7, 2)
NUMBER(2)

10. Examine the structure of the EMP table:

EMP
Name Null? Type
EMPNO
ENAME
SALARY
COMM_PCT
DEPTNO
NOT NULL NUMBER(3)
VARCHAR2(25)
NUMBER(10,2)
NUMBER(4,2)
NUMBER(2)

You want to find the highest average salary being paid in any department.

Which SQL statement gives the required result?

A) SELECT MAX(AVG(sal)) FROM emp GROUP BY deptno;

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;

Respostas!

  •  AC
  •  D
  •  B
  •  C
  •  AC
  •  B
  •  B
  •  AE
  •  D
  •  A