Patrick Lafferty
Test por , creado hace más de 1 año

Correct of oracle assesment

101
0
0
Patrick Lafferty
Creado por Patrick Lafferty hace alrededor de 8 años
Cerrar

Corrections

Pregunta 1 de 23

1

CUBE will cross-reference the columns listed in the ______ clause to create a superset of groups.

Selecciona una de las siguientes respuestas posibles:

  • GROUP BY

  • WHERE

  • SELECT

Explicación

Pregunta 2 de 23

1

You use GROUPING functions to:

Selecciona una de las siguientes respuestas posibles:

  • Produce subtotal and cross-tabulated values

  • Identify the extra row values created by either a ROLLUP or CUBE operation

  • Aggregate rows using SUM, MIN, MAX, and COUNT

Explicación

Pregunta 3 de 23

1

The EMPLOYEES table contains these columns:
ID_NUMBER NUMBER Primary Key
NAME VARCHAR2 (30)
DEPARTMENT_ID NUMBER
SALARY NUMBER (7,2)
HIRE_DATE DATE
Evaluate this SQL statement:
SELECT id_number, name, department_id, SUM(salary)
FROM employees
WHERE salary > 25000
GROUP BY department_id, id_number, name
ORDER BY hire_date;
Why will this statement cause an error?

Selecciona una de las siguientes respuestas posibles:

  • The HAVING clause is missing.

  • The WHERE clause contains a syntax error.

  • The WHERE clause contains a syntax .

  • The HIRE_DATE column is NOT included in the GROUP BY clause.

Explicación

Pregunta 4 de 23

1

The PRODUCTS table contains these columns:
PRODUCT_ID NUMBER(9) PK
CATEGORY_ID VARCHAR2(10)
LOCATION_ID NUMBER(9)
DESCRIPTION VARCHAR2(30)
COST NUMBER(7,2)
PRICE NUMBER(7,2)
QUANTITY NUMBER
You display the total of the extended costs for each product category by location.
You need to include only the products that have a price less than $25.00.
The extended cost of each item equals the quantity value multiplied by the cost value.
Which SQL statement will display the desired result?

Selecciona una de las siguientes respuestas posibles:

  • SELECT category_id, SUM(cost * quantity) TOTAL,location_id
    FROM products
    WHERE price > 25.00
    GROUP BY category_id, location_id;

  • SELECT SUM(cost * quantity) TOTAL, location_id
    FROM products
    WHERE price < 25.00
    GROUP BY location_id;

  • SELECT category_id, SUM(cost * quantity) TOTAL, location_id
    FROM products
    WHERE price < 25.00
    GROUP BY category_id, location_id;

  • SELECT SUM(cost * quantity) TOTAL
    FROM products
    WHERE price < 25.00;

Explicación

Pregunta 5 de 23

1

The PLAYERS and TEAMS tables contain these columns:
PLAYERS
PLAYER_ID NUMBER NOT NULL, PRIMARY KEY
LAST_NAME VARCHAR2 (30) NOT NULL
FIRST_NAME VARCHAR2 (25) NOT NULL
TEAM_ID NUMBER
POSITION VARCHAR2 (25)
TEAMS
TEAM_ID NUMBER NOT NULL, PRIMARY KEY
TEAM_NAME VARCHAR2 (25)
You need to create a report that lists the names of each team with more than three goal keepers.
Which SELECT statement will produce the desired result?

Selecciona una de las siguientes respuestas posibles:

  • SELECT t.team_name, COUNT(p.player_id)
    FROM players p, teams t
    ON (p.team_id = t.team_id)
    WHERE UPPER(p.position) = 'GOAL KEEPER'
    GROUP BY t.team_name;

  • SELECT t.team_name, COUNT(p.player_id)
    FROM players
    JOIN teams t ON (p.team_id = t.team_id)
    WHERE UPPER(p.position) = 'GOAL KEEPER'
    HAVING COUNT(p.player_id) > 3;

  • SELECT t.team_name, COUNT(p.player_id)
    FROM players p, teams t
    ON (p.team_id = t.team_id)
    WHERE UPPER(p.position) = 'GOAL KEEPER'
    GROUP BY t.team_name
    HAVING COUNT(p.player_id) > 3;

  • SELECT t.team_name, COUNT(p.player_id)
    FROM players p
    JOIN teams t ON (p.team_id = t.team_id)
    WHERE UPPER(p.position) = 'GOAL KEEPER'
    GROUP BY t.team_name
    HAVING COUNT(p.player_id) > 3;

Explicación

Pregunta 6 de 23

1

If a select list contains both a column as well as a group function then what clause is required?

Selecciona una de las siguientes respuestas posibles:

  • HAVING clause

  • JOIN clause

  • ORDER BY clause

  • GROUP BY clause

Explicación

Pregunta 7 de 23

1

Group functions can be nested to a depth of?

Selecciona una de las siguientes respuestas posibles:

  • Three

  • Four

  • Two

  • Group function cannot be nested

Explicación

Pregunta 8 de 23

1

The EMPLOYEES table contains the following columns:
EMPLOYEE_ID NUMBER(10) PRIMARY KEY
LAST_NAME VARCHAR2(20)
FIRST_NAME VARCHAR2(20)
DEPARTMENT VARCHAR2(20)
HIRE_DATE DATE
SALARY NUMBER(10)
You want to create a report that includes each employee's last name, employee identification number, date of hire, and salary. The report should include only those employees who have been with the company for more than one year and whose salary exceeds $40,000.
Which of the following SELECT statements will accomplish this task?

Selecciona una de las siguientes respuestas posibles:

  • SELECT employee_id, last_name, salary
    FROM employees
    WHERE salary > 40000
    AND hire_date =
    (SELECT hire_date
    FROM employees
    WHERE (sysdate-hire_date) / 365 > 1);

  • SELECT employee_id, last_name, hire_date, salary
    FROM employees
    WHERE salary > 40000 AND hire_date =
    (SELECT hire_date
    FROM employees
    WHERE (sysdate-hire_date) / 365 > 1);

  • SELECT employee_id, last_name, hire_date, salary
    FROM employees
    WHERE salary > 40000
    AND (sysdate-hire_date) / 365 > 1;

  • SELECT employee_id, last_name, salary
    FROM employees
    WHERE salary > 40000
    AND hire_date IN (sysdate-hire_date) / 365 > 1);

Explicación

Pregunta 9 de 23

1

Which statement about group functions is true?

Selecciona una de las siguientes respuestas posibles:

  • Group functions ignore null values.

  • Group functions can only be used in a SELECT list.

  • Group functions can be used in a WHERE clause.

  • A query that includes a group function in the SELECT list must include a GROUP BY clause.

Explicación

Pregunta 10 de 23

1

Evaluate this SELECT statement:
SELECT COUNT(employee_id), department_id
FROM employees
GROUP BY department_id;
You only want to include employees who earn more than 15000.
Which clause should you include in the SELECT statement?

Selecciona una de las siguientes respuestas posibles:

  • WHERE salary > 15000

  • HAVING salary > 15000

  • WHERE SUM(salary) > 15000

  • HAVING SUM(salary) > 15000

Explicación

Pregunta 11 de 23

1

Which comparison operator can only be used with a single-row subquery?

Selecciona una de las siguientes respuestas posibles:

  • ANY

  • ALL

  • < >

  • IN

Explicación

Pregunta 12 de 23

1

The TEACHERS and CLASS_ASSIGNMENTS tables contain these columns:
TEACHERS
TEACHER_ID NUMBER(5) Primary Key
NAME VARCHAR2 (25)
SUBJECT_ID NUMBER(5)
CLASS_ID NUMBER(5)
CLASS_ASSIGNMENTS
CLASS_ID NUMBER (5) Primary Key
TEACHER_ID NUMBER (5)
DATE
MAX_CAPACITY NUMBER (3)
All MAX_CAPACITY values are greater than 10. Which two SQL statements correctly use subqueries? (Choose two.)

(Choose all correct answers)

Selecciona una o más de las siguientes respuestas posibles:

  • SELECT *
    FROM class_assignments
    WHERE max_capacity = (SELECT AVG(max_capacity) FROM class_assignments);

  • SELECT *
    FROM teachers
    WHERE teacher_id = (SELECT teacher_id FROM class_assignments WHERE class_id = 45963);

  • SELECT *
    FROM teachers
    WHERE teacher_id = (SELECT teacher_id, class_assignments WHERE max_capacity > 0);

  • SELECT *
    FROM teachers
    WHERE teacher_id LIKE (SELECT teacher_id FROM class_assignments WHERE max_capacity > 1000);

  • SELECT *
    FROM class_assignments
    max_capacity = (SELECT AVG(max_capacity) FROM class_assignments GROUP BY teacher_id);

Explicación

Pregunta 13 de 23

1

You need to create a report to display the names of products with a cost value greater than the average cost of all products. Which SELECT statement should you use?

Selecciona una de las siguientes respuestas posibles:

  • SELECT product_name
    FROM products
    WHERE cost > (SELECT AVG(cost)
    FROM products);

  • SELECT product_name
    FROM products
    WHERE cost > AVG(cost);

  • SELECT AVG(cost), product_name
    FROM products
    WHERE cost > AVG(cost)
    GROUP by product_name;

  • SELECT product_name
    FROM (SELECT AVG(cost) FROM product)
    WHERE cost > AVG(cost);

Explicación

Pregunta 14 de 23

1

Using a subquery in which clause will return a syntax error?

Selecciona una de las siguientes respuestas posibles:

  • WHERE

  • FROM

  • HAVING

  • You can use subqueries in all of the above clauses.

Explicación

Pregunta 15 de 23

1

Which statement about subqueries is true?

Selecciona una de las siguientes respuestas posibles:

  • Subqueries should be enclosed in double quotation marks.

  • Subqueries cannot contain group functions.

  • Subqueries are often used in a WHERE clause to return values for an unknown conditional value.

  • Subqueries generally execute last, after the main or outer query executes.

Explicación

Pregunta 16 de 23

1

Which statement is false?

Selecciona una de las siguientes respuestas posibles:

  • The WITH clause retrieves the results of one or more query blocks.

  • The WITH clause decreases performance.

  • The WITH clause makes the query simple to read.

  • The WITH clause stores the results for the user who runs the query.

Explicación

Pregunta 17 de 23

1

Which answer is INCORRECT? The parent statement of a correlated subquery can be:

Selecciona una de las siguientes respuestas posibles:

  • A SELECT statement

  • An INSERT statement

  • An UPDATE statement

  • A DELETE statement

Explicación

Pregunta 18 de 23

1

The Oracle server performs a correlated subquery when the subquery references a column from a table referred to in the parent. True or False?

Selecciona uno de los siguientes:

  • VERDADERO
  • FALSO

Explicación

Pregunta 19 de 23

1

The STUDENTS table contains these columns:
STU_ID NUMBER(9) NOT NULL
LAST_NAME VARCHAR2 (30) NOT NULL
FIRST_NAME VARCHAR2 (25) NOT NULL
DOB DATE
STU_TYPE_ID VARCHAR2(1) NOT NULL
ENROLL_DATE DATE
You create another table, named FT_STUDENTS, with an identical structure.You want to insert all full-time students who have a STU_TYPE_ID value of "F" into the new table. You execute this INSERT statement:
INSERT INTO ft_students
(SELECT stu_id, last_name, first_name, dob, stu_type_id, enroll_date
FROM students
WHERE UPPER(stu_type_id) = 'F');
What is the result of executing this INSERT statement?

Selecciona una de las siguientes respuestas posibles:

  • All full-time students are inserted into the FT_STUDENTS table.

  • An error occurs because the FT_STUDENTS table already exists.

  • An error occurs because you CANNOT use a subquery in an INSERT statement.

  • An error occurs because the INSERT statement does NOT contain a VALUES clause.

Explicación

Pregunta 20 de 23

1

You have been instructed to add a new customer to the CUSTOMERS table. Because the new customer has not had a credit check, you should not add an amount to the CREDIT column.
The CUSTOMERS table contains these columns:
CUST_ID NUMBER(10)
COMPANY VARCHAR2(30)
CREDIT NUMBER(10)
POC VARCHAR2(30)
LOCATION VARCHAR2(30)
Which two INSERT statements will accomplish your objective?

(Choose all correct answers)

Selecciona una o más de las siguientes respuestas posibles:

  • INSERT INTO customers (cust_id, company, poc, location)
    VALUES (200, 'InterCargo', 'tflanders', 'samerica');

  • INSERT INTO customers
    VALUES (200, 'InterCargo', null, 'tflanders', 'samerica');

  • INSERT INTO customers
    VALUES (cust_id, company, credit, poc, location) (200, 'InterCargo', 0, 'tflanders', 'samerica');

  • INSERT INTO customers
    VALUES (200, InterCargo, 0, tflanders, samerica);

Explicación

Pregunta 21 de 23

1

Which two commands can be used to modify existing data in a database row?

(Choose all correct answers)

Selecciona una o más de las siguientes respuestas posibles:

  • DELETE

  • MERGE

  • SELECT

  • UPDATE

Explicación

Pregunta 22 de 23

1

You need to delete a record in the EMPLOYEES table for Tim Jones, whose unique employee identification number is 348. The EMPLOYEES table contains these columns:
EMPLOYEE_ID NUMBER(5) PRIMARY KEY
LAST_NAME VARCHAR2(20)
FIRST_NAME VARCHAR2(20)
ADDRESS VARCHAR2(30)
PHONE NUMBER(10)
Which DELETE statement will delete the appropriate record without deleting any additional records?

Selecciona una de las siguientes respuestas posibles:

  • DELETE FROM employees
    WHERE employee_id = 348;

  • DELETE FROM employees
    WHERE last_name = jones;

  • DELETE *
    FROM employees
    WHERE employee_id = 348;

  • DELETE 'jones'
    FROM employees;

Explicación

Pregunta 23 de 23

1

What keyword in an UPDATE statement specifies the column that you want to change?

Selecciona una de las siguientes respuestas posibles:

  • SELECT

  • WHERE

  • SET

  • HAVING

Explicación

Anterior
Siguiente