Oracle 12c Sql Hands-on Assignments Solutions (2027)

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

SELECT first_name, last_name, hire_date, TRUNC(MONTHS_BETWEEN(SYSDATE, hire_date) / 12) AS years, TRUNC(MOD(MONTHS_BETWEEN(SYSDATE, hire_date), 12)) AS months, TRUNC(MONTHS_BETWEEN(SYSDATE, hire_date) / 12) || ' years, ' || TRUNC(MOD(MONTHS_BETWEEN(SYSDATE, hire_date), 12)) || ' months' AS tenure FROM employees; Mask email addresses for a report (Show first 2 letters, then ' ** ', then the domain 'oracle.com').

CREATE TABLE emp_analytics AS SELECT department_id, last_name, salary, RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS salary_rank FROM employees; Oracle 12c SQL is robust, but the secret to passing hands-on assignments is understanding the subtle differences in windowing functions and the modern Top-N syntax . Always test your OFFSET/FETCH logic on a small subset first to verify sorting order.

Write a query to page through employee data, showing rows 21 to 30 (Offset 20, Fetch 10).

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 the salary difference between each employee and the next highest paid employee in the same department.

SELECT d.department_name, l.city, COUNT(e.employee_id) AS employee_count FROM departments d LEFT JOIN employees e ON d.department_id = e.department_id LEFT JOIN locations l ON d.location_id = l.location_id GROUP BY d.department_name, l.city ORDER BY employee_count DESC; List employees who earn more than the average salary of their own department.

SELECT employee_id, first_name, last_name, hire_date FROM employees ORDER BY hire_date OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY; Problem 7: Calculate the exact number of months and years each employee has worked as of today's date. Output format: "14 years, 3 months".

Nom	Description	Période de conservation
_ga	Used to distinguish users in Google Analytics 4.	2 Years
_gid	This cookie is used by Google Analytics to store and update a unique value for each page visited.	1 Day
_gat	Used by Google Analytics to throttle the request rate, which means that the collection of data on high-traffic websites is restricted.	1 Day
_ga_AW-959191631	Used to maintain the session status.	2 years

Nom	Description	Période de conservation
__RequestVerificationToken	Saves a token for verification of the following requests. oracle 12c sql hands-on assignments solutions	Session

Nom	Description	Période de conservation
ewafutano	Tracking (anonymous history)	2 Years

Nom	Description	Période de conservation
yt-remote-device-id	Saves the user settings when retrieving a YouTube video integrated on other websites	Permanent
yt-remote-connected-devices	Saves the user settings when retrieving a YouTube video integrated on other websites	Permanent
yt-remote-session-app	Saves the user settings when retrieving a YouTube video integrated on other websites Output format: "14 years	Session
yt-remote-cast-installed	Saves the user settings when retrieving a YouTube video integrated on other websites	Session
yt-remote-session-name	Saves the user settings when retrieving a YouTube video integrated on other websites	Session
yt-remote-fast-check-period	Saves the user settings when retrieving a YouTube video integrated on other websites	Session
VISITOR_INFO1_LIVE	Trying to estimate user bandwidth on pages with integrated YouTube videos.	179 Days
YSC	Registers a unique ID to keep statistics of the YouTube videos that the user has seen.	Session
GPS	Registers a unique ID on mobile devices to enable tracking based on GPS geographic location.	1 Day

Other contents (4 Services) Integration of additional information
EMCO Career Portal eRecruiter GmbH, Austria	INFO
Evalanche SC-Networks GmbH, Germany	INFO
Google Maps Google LLC, USA	INFO
YouTube YouTube LLC, USA	INFO

Analysis / Statistics (1 Service) Anonymous evaluation for error correction, further development and personalised ads.
Google Analytics Google LLC, USA	INFO

Oracle 12c Sql Hands-on Assignments Solutions (2027)

EMCO privacy setting