Free Oracle Database SQL Practice Test

You need to retrieve all columns from the EMPLOYEES table for employees working in department 50. Which SQL statement will accomplish this task?

Examine the structure of the PRODUCTS table: PRODUCT_ID NUMBER(6) PRODUCT_NAME VARCHAR2(50) CATEGORY_ID NUMBER(3) PRICE NUMBER(8,2) You need to display product names and prices, showing 'Expensive' for products priced above 1000, 'Moderate' for products priced between 500 and 1000, and 'Budget' for products priced below 500. Which query accomplishes this?

Which statement about the Oracle data dictionary is TRUE?

A database contains an ORDERS table with columns ORDER_ID, CUSTOMER_ID, ORDER_DATE, and TOTAL_AMOUNT. You want to select the distinct customers who have placed orders, without showing duplicate customer IDs. Which query should you use?

Examine this query: SELECT employee_id, first_name, last_name, salary FROM employees WHERE department_id IN (10, 20, 30) AND hire_date > '01-JAN-2020' OR salary > 10000; What is the result of this query?

You need to retrieve employee records sorted by department in ascending order, and within each department, sorted by salary in descending order. Which ORDER BY clause is correct?

Which WHERE clause condition will select employees whose last name starts with 'S' and has exactly 5 characters?

Examine this query: SELECT employee_id, salary FROM employees WHERE salary BETWEEN 5000 AND 10000 ORDER BY 2 DESC; What does the number 2 in the ORDER BY clause represent?

What is the result of the following expression: SELECT ROUND(1567.879, -2) FROM dual;

Which function should you use to replace NULL values in a column with a specified alternative value?

You need to display each department's name and the number of employees in that department, but only for departments with more than 5 employees. Which query is correct?

What is the result of this query: SELECT SUBSTR('Oracle Database', 8, 4) FROM dual;

Examine the EMPLOYEES and DEPARTMENTS tables: EMPLOYEES: employee_id, first_name, last_name, department_id DEPARTMENTS: department_id, department_name, location_id You need to display all employees along with their department names. Some employees are not assigned to any department (department_id is NULL), and you want to include these employees in the result. Which join should you use?

What is the difference between a correlated subquery and a non-correlated subquery?

Examine this query: SELECT employee_id, first_name, salary FROM employees WHERE salary > (SELECT AVG(salary) FROM employees WHERE department_id = 50); What does this query return?

Which statement about set operators (UNION, UNION ALL, INTERSECT, MINUS) is TRUE?

You execute this statement: INSERT INTO employees (employee_id, first_name, last_name, hire_date, salary) VALUES (207, 'John', 'Smith', SYSDATE, 5000); What must you do to make this change permanent in the database?

What is the effect of executing this statement: CREATE TABLE employees_copy AS SELECT * FROM employees WHERE department_id = 50;

Examine this statement: ALTER TABLE employees ADD CONSTRAINT emp_salary_min CHECK (salary >= 1000); What happens if you attempt to insert a row with a salary of 800 after this constraint is added?

You need to write a query that displays employee names and their manager names. The EMPLOYEES table has columns: employee_id, first_name, last_name, and manager_id (which references employee_id of the manager). Which type of join is most appropriate?