Consultas sql

QUESTION NO: 1

Examine the data in the EMPLOYEES and DEPARTMENTS tables.

EMPLOYEES
LAST_NAME DEPARTMENT_ID SALARY
Getz 10 3000
Davis 20 1500
King 20 2200
Davis 30 5000
Kochhar 5000

DEPARTMENTS

DEPARTMENT_ID DEPARTMENT_NAME
10 Sales
20 Marketing
30 Accounts

40 Administration

You want to retrieve all employees,whether or not they have matching departments in the departments table. Which query would you use?

A. SELECT last_name, department_name
FROM employees , departments(+);

B. SELECT last_name, department_name
FROM employees JOIN departments (+);

C. SELECT last_name, department_name
FROM employees(+) e JOIN departments d
ON (e.department_id = d.department_id);

D. SELECT last_name,department_name
FROM employees e
RIGHT OUTER JOIN departments d ON (e.department_id = d.department_id);

E. SELECT last_name, department_name
FROM employees(+) , departments
ON (e.department_id = d.department_id);

F. SELECT last_name, department_name
FROM employees e LEFT OUTER
JOIN departments d ON (e.department_id = d.department_id);

QUESTION NO: 2 – Respuesta C,E y F

Examine thestructure of the EMPLOYEES table:

EMPLOYEE_ID NUMBER Primary Key
FIRST_NAME VARCHAR2(25)
LAST_NAME VARCHAR2(25)

Which three statements inserts a row into the table? (Choose three)

A. INSERT INTO employees
VALUES ( NULL, ‘John’,‘Smith’);

B. INSERT INTO employees( first_name, last_name)
VALUES(‘John’,‘Smith’);

C. INSERT INTO employees
VALUES(‘1000’,‘John’,NULL);

D. INSERT INTO employees(first_name,last_name, employee_id)
VALUES ( 1000, ‘John’,‘Smith’);

E. INSERT INTO employees (employee_id)
VALUES (1000);

F. INSERT INTO employees (employee_id, first_name, last_name)
VALUES ( 1000, ‘John’,‘’);

QUESTION NO: 4 – Respuesta C y D

Examine the data in the EMPLOYEES table:

LAST_NAMEDEPARTMENT_ID SALARY
Getz 10 3000
Davis 20 1500
King 20 2200
Davis 30 5000

Which three subqueries work? (Choose three)

A. SELECT *
FROM employees
where salary > (SELECT MIN(salary)
FROM employees
GROUP BY department.id);

B. SELECT *
FROM employees
WHERE salary = (SELECT AVG(salary)
FROM employees
GROUP BYdepartment_id);

C. SELECT distinct department_id
FROM employees
Where salary > ANY (SELECT AVG(salary)
FROM employees
GROUP BY department_id);

D. SELECT department_id
FROM employees
WHERE SALARY > ALL (SELECT AVG(salary)
FROM employees
GROUP BY department_id);

E. SELECT last_name
FROM employees
Where salary > ANY (SELECT MAX(salary)FROM employees
GROUP BY department_id);

F. SELECT department_id
FROM employees
WHERE salary > ALL (SELECT AVG(salary)
FROM employees
GROUP BY AVG(SALARY));

QUESTION NO: 6 – Respuesta A y B

Which two statements about views are true? (Choose two.)

A. A view can be created as read only.
B. A view can be created as a join on two or more tables.
C. Aview cannot have an ORDER BY clause in the SELECT statement.
D. A view cannot be created with a GROUP BY clause in the SELECT statement.
E. A view must have aliases defined for the column names in the SELECT statement.

QUESTION NO: 7 – Respuesta B

Examine the description of the EMPLOYEES table:

EMP_ID NUMBER(4) NOT NULL
LAST_NAME VARCHAR2(30)NOT NULL
FIRST_NAME VARCHAR2(30)
DEPT_ID NUMBER(2)
JOB_CAT VARCHARD2(30)
SALARY NUMBER(8,2)

Which statement shows the maximum salary paid in each job category of each department?

A. SELECT dept_id, job_cat, MAX(salary)
FROM employees
WHERE salary > MAX(salary);

B. SELECT dept_id, job_cat, MAX(salary)
FROM...