Oracle 12c Sql — Hands-on Assignments Solutions !!link!!

Find:

: This uses the SELECT statement with a calculated column. The WHERE clause filters by department, and ORDER BY ... DESC sorts the results . 2. Aggregating Data

Why LEFT JOIN? Includes members with no borrowings (COUNT returns 0). oracle 12c sql hands-on assignments solutions

Display the average salary for each department, but only for departments where the average is greater than $5,000 .

SELECT department_id, last_name, salary, RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank, DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS dense_rank, ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS row_num FROM employees WHERE department_id IS NOT NULL ORDER BY department_id, salary DESC; Find: : This uses the SELECT statement with

Add a BORROWING table to the library schema: borrow_id (PK), book_id (FK to BOOKS), member_id (FK to MEMBERS), borrow_date , return_date (can be NULL).

Calculate the exact number of months and years each employee has worked as of today's date. Output format: "14 years, 3 months". Display the average salary for each department, but

SELECT email, SUBSTR(email, 1, 2) || '****@oracle.com' AS masked_email FROM employees;

Oracle 12c introduced several game-changing features, such as (the FETCH FIRST clause) and improved Visibility into Partitioned Tables . However, the core of database mastery still lies in solving real-world problems.