Oracle & PL/SQL の面接質問

SELECT EmployeeID, FirstName, Salary,
       ROW_NUMBER()  OVER (ORDER BY Salary DESC) AS RowNum,
       RANK()        OVER (ORDER BY Salary DESC) AS RankNum,
       DENSE_RANK()  OVER (ORDER BY Salary DESC) AS DenseRank,
       NTILE(4)      OVER (ORDER BY Salary DESC) AS Quartile
FROM   Employees;

-- Rank within each department
SELECT EmployeeID, DeptID, Salary,
       RANK() OVER (PARTITION BY DeptID ORDER BY Salary DESC) AS DeptRank
FROM   Employees;

↥ back to top

You can use the UPPER or LOWER functions to convert both the search term and the column value to the same case.

Example:

SELECT * FROM employees WHERE UPPER(last_name) = UPPER('smith');

This method can be particularly useful in cases where you also want to keep track of the "duplicate" IDs.

WITH duplicates AS (
  SELECT *,
         ROW_NUMBER() OVER (PARTITION BY column1, column2, ..., columnN ORDER BY ID) AS rnum
  FROM mytable
)
SELECT * 
FROM duplicates 
WHERE rnum > 1;

In both of these methods, column1, column2, ..., columnN refer to the column or set of columns you're using to identify duplicates.

Code Example: Standard SQL

SELECT name, age, COUNT(*)
FROM mytable
GROUP BY name, age
HAVING COUNT(*) > 1;

In this example, we're looking for duplicate records based on the "name" and "age" columns from the "mytable".

Code Example: ROW_NUMBER()

WITH duplicates AS (
  SELECT *,
         ROW_NUMBER() OVER (PARTITION BY name, age ORDER BY ID) AS rnum
  FROM mytable
)
SELECT * 
FROM duplicates 
WHERE rnum > 1;

In this example, we're using ROW_NUMBER() to identify records with the same "name" and "age" and keeping track of the unique row number for each.

You can use the aggregate functions AVG, MIN, and MAX respectively.

Example:

SELECT AVG(salary), MIN(salary), MAX(salary) FROM employees;

SELECT INTO is used to assign the result of a query to variables in PL/SQL, while INSERT INTO is used to insert data into a table.

Example of SELECT INTO:

DECLARE
  l_employee_name employees.employee_name%TYPE;
BEGIN
  SELECT employee_name INTO l_employee_name FROM employees WHERE employee_id = 123;
  -- Use l_employee_name variable for further processing
END;

Example of INSERT INTO:

INSERT INTO employees (employee_id, employee_name) VALUES (123, 'John Doe');

You can use the ROWNUM pseudocolumn along with the ORDER BY clause to retrieve the top N rows.

Example:

SELECT * FROM employees WHERE ROWNUM <= 10 ORDER BY hire_date DESC;

SUBSTR is used to extract a portion of a string, while INSTR is used to find the position of a substring within a string.

Example of SUBSTR:

SELECT SUBSTR('Hello, World!', 1, 5) FROM dual;
-- Output: "Hello"

Example of INSTR:

SELECT INSTR('Hello, World!', 'World') FROM dual;
-- Output: 8

A self-join is performed when a table is joined with itself based on a common column.

Example:

SELECT e1.employee_name, e2.employee_name
FROM employees e1, employees e2
WHERE e1.manager_id = e2.employee_id;

• Simplify Complex Queries: Break the query into smaller parts for better readability and performance. Use common table expressions or derived tables to modularize SQL logic. Alternatively, you can use temporary tables.
• Limit Result Set: Use TOP, LIMIT, or ROWNUM/ROWID to restrict the number of records returned.
• Reduce JOIN Complexity: Replace multiple JOINs with fewer, multi-table JOINs and explicit JOIN notation.

Problem Statement

The task is to perform a SQL join operation between two tables and retrieve the rows where the keys match.

Solution

To accomplish this task, use the following SQL query.

MySQL

SELECT * 
FROM table1
INNER JOIN table2 ON table1.key = table2.key;

PostgreSQL

SELECT * 
FROM table1
INNER JOIN table2 USING (key);

Oracle

SELECT *
FROM table1
JOIN table2 ON table1.key = table2.key;

SQL Server

SELECT *
FROM table1
JOIN table2 ON table1.key = table2.key;

Key Points

• `INNER JOIN`: Retrieves the matching rows from both tables based on the specified condition.
• `ON`, `USING`: Specifies the column(s) used for joining.
• `SELECT`: You can specify individual columns instead of * based on requirement.
• Table Aliases: When dealing with long table names, aliases (e.g., t1, t2) provide a more concise syntax.

A view is a virtual table that is based on the result of a SQL query, while a materialized view is a physical copy of the result of a query stored as a table.

Example of a view:

CREATE VIEW active_employees AS
SELECT * FROM employees WHERE status = 'Active';

Example of a materialized view:

CREATE MATERIALIZED VIEW mv_active_employees
AS SELECT * FROM employees WHERE status = 'Active';

What's more?

A comprehensive list of questions and answers

We welcome contributions from our users to help make this resource as comprehensive and useful as possible. If you have been recently interviewed and encountered a question that is not currently covered on our website, feel free to suggest it as a new question. Your contributions will be added to our platform, and we will make sure to credit you for your contributions. We appreciate your help in making our platform a valuable tool for all job seekers.

MIT License

UNION combines the result sets of two or more SELECT statements, removing duplicate rows, while UNION ALL includes all rows, including duplicates.

Example of UNION:

SELECT employee_id FROM employees UNION SELECT employee_id FROM contractors;

Example of UNION ALL:

SELECT employee_id FROM employees UNION ALL SELECT employee_id FROM contractors;

GROUP BY is used to group rows based on specific columns, while HAVING is used to filter groups based on specific conditions.

Example of GROUP BY:

SELECT department_id, COUNT(*) FROM employees GROUP BY department_id;

Example of HAVING:

SELECT department_id, COUNT(*) FROM employees GROUP BY department_id HAVING COUNT(*) > 5;

• A primary key is used to uniquely identify each row in a table and cannot contain null values. Only one primary key is allowed per table.

• A unique key is used to ensure that each value in a column or a set of columns is unique, but it can contain null values. Multiple unique keys can be defined per table.

Example of a primary key:

CREATE TABLE employees (
  employee_id NUMBER PRIMARY KEY,
  employee_name VARCHAR2(100)
);

Example of a unique key:

CREATE TABLE departments (
  department_id NUMBER,
  department_name VARCHAR2(100),
  CONSTRAINT uk_department_name UNIQUE (department_name)
);