50 oracle sql developer practice online free Practice Questions: Question Bank 2025

Which statement best describes the difference between a PRIMARY KEY constraint and a UNIQUE constraint in Oracle Database?

You need to display employee last names and annual salary calculated as salary * 12, with the calculated column heading ANNUAL_SALARY. Which query is correct?

Which WHERE clause correctly returns only rows where COMMISSION_PCT has not been assigned a value?

You want to return a list of distinct job IDs from the EMPLOYEES table. Which statement is correct?

You need to list employees in department 50, sorted by highest salary first and, for ties, by last name in ascending order. Which ORDER BY clause meets this requirement?

A report must display LAST_NAME in uppercase and show only the first 3 characters of FIRST_NAME. Which query fragment accomplishes this?

You want to display each employee's salary, but if COMMISSION_PCT is NULL, treat it as 0 when calculating total compensation: salary + salary*commission_pct. Which expression is best practice?

You need one row per department showing the department_id and the number of employees in that department, excluding employees with NULL department_id. Which query is correct?

You must list all departments, including those with no employees, and show the department name and the number of employees in each department. Which query satisfies the requirement?

You need to return employees whose salary is greater than the average salary of their own department. Which query correctly implements this requirement?

You need to return each employee's full name in the format "LAST, First" where LAST is uppercase and First is in initcap. The table EMP has columns FIRST_NAME and LAST_NAME. Which SQL expression should you use in the SELECT list?

A report must list all departments, including those with no employees. Tables: DEPARTMENTS(dept_id, dept_name) and EMPLOYEES(emp_id, dept_id). Which query returns all departments with any matching employees, and shows NULLs for employee columns when no match exists?

You want to eliminate duplicate rows from a query result without changing the underlying table. Which clause should you use?

You need to display customers with a derived column STATUS: 'GOLD' when CREDIT_LIMIT >= 10000, 'SILVER' when CREDIT_LIMIT >= 5000, otherwise 'BRONZE'. Which expression correctly implements this requirement?

A query includes: WHERE UPPER(email) = 'JDOE@EXAMPLE.COM'. Users complain it runs slowly, and there is an index on EMAIL. What is the best practice to improve performance while keeping the comparison case-insensitive?

You need a query that returns one row per job_id with the average salary, but only for jobs where the average salary is greater than 8000. Which clause should be used to filter on the average salary?

A developer writes: SELECT dept_id, COUNT(*) FROM employees; and gets ORA-00937: not a single-group group function. How should the query be corrected to return the employee count per department?

You need to join ORDERS(o) to CUSTOMERS(c) and return all orders, even if a customer row is missing (for example, data cleanup in progress). Which join is appropriate?

You store order amounts as VARCHAR2 in the format '1,234.56' (comma as thousands separator, dot as decimal). You need to convert this value to NUMBER reliably in SQL. Which expression is the best solution?

You need to display each employee along with the department name and the employee's manager name. Tables: EMPLOYEES(emp_id, emp_name, manager_id, dept_id) and DEPARTMENTS(dept_id, dept_name). Which query correctly returns emp_name, dept_name, and manager_name for all employees, including those with no manager?

Which statement best describes the logical relationship between a table row and a table column in a relational database?

You need to return each employee's annual salary as salary*12, labeled ANNUAL_SAL, and show only those with annual salary greater than 100000. Which query is correct?

You want to display last names in uppercase and the length of each last name from EMPLOYEES. Which statement is correct?

A report must show employee last name and department name, including employees who are not assigned to any department. Which join should you use?

You store a value in a column COMMENTS as ' New hire '. You need to remove leading and trailing spaces but keep internal spaces. Which function should you use?

You must display commission_pct, but show 0 when the value is NULL. Which expression is recommended?

You need a list of departments with the number of employees in each, including departments with zero employees. Which query is correct?

You need to display a list of unique job IDs from EMPLOYEES, sorted alphabetically, and show only the first 5 rows returned after sorting. Which statement is correct?

A query includes: WHERE TO_CHAR(hire_date,'YYYY') = '2024'. It is slow on a large EMPLOYEES table where HIRE_DATE is indexed. Which rewrite is the best practice to improve index usage?

You want to return each employee and their manager's last name from EMPLOYEES. Some employees have no manager (top-level). Which query correctly returns all employees?

You need to display each employee's annual salary as SAL*12. If SAL is NULL, the annual salary must display as 0. Which query meets the requirement?

A report must show employees ordered by department number ascending, and within each department by hire date descending. Which ORDER BY clause is correct?

Which statement correctly describes the difference between a primary key constraint and a unique constraint?

You must return only distinct department numbers from the EMP table. Which SQL statement is correct?

You want to list employees whose commission is either NULL or less than 500. Which WHERE clause correctly implements this requirement?

You need to display each employee name and a masked version of the phone number that shows only the last 4 characters. Phone numbers can have varying lengths. Which expression accomplishes this?

You need a report showing each department and the total salary paid, but only for departments whose total salary exceeds 100000. Which SQL approach is correct?

A query joins EMP and DEPT and returns fewer rows than expected because some employees have a NULL DEPTNO. You must still list those employees, showing NULL for department name. Which join should you use?

You must list departments and indicate the number of employees in each department, including departments with zero employees. Which query is correct?

You are troubleshooting a query intended to return the first 3 characters of a string, left-padded with zeros to a total length of 5. The developer wrote: LPAD(SUBSTR(code,0,3),5,0). It returns unexpected results for some rows. What is the best correction?

You need to display each employee's full name as a single column in the format "LAST, First" where LAST is uppercase and First is in initcap (first letter uppercase, rest lowercase). Which query achieves this?

A report must list distinct department_ids that have at least one employee, sorted with NULL department_ids appearing last. Which ORDER BY clause is the best solution?

You want to return only rows where commission_pct has not been assigned (unknown). Which predicate is correct?

A developer wants to display salary values in the format "$12,345.00" regardless of session NLS numeric settings. Which expression is the best choice?

You need a query that returns each department_id and the number of employees in it, but only for departments having more than 5 employees. Which clause is required to apply this filter correctly?

You need to list all departments, including those with no employees, along with the number of employees in each. Which join and aggregation approach should you use?

A query includes: SELECT employee_id, salary FROM employees ORDER BY 2 DESC; What does ORDER BY 2 mean?

You are troubleshooting a query that should return employees who earn more than the average salary of their own department. The developer wrote: SELECT e.employee_id, e.department_id, e.salary FROM employees e WHERE e.salary > (SELECT AVG(salary) FROM employees); What is the primary issue with this query?

You need to categorize employees based on salary: 'LOW' if salary < 3000, 'MID' if salary between 3000 and 8000 inclusive, otherwise 'HIGH'. Which expression is the most appropriate?

A table SALES has columns (sale_id NUMBER, amount NUMBER). You need a single-row result showing the percentage of total sales contributed by each row, but you must write it as a single SQL query without using analytic functions. Which approach is correct?