-- Create BOOKS table with CHECK constraint CREATE TABLE books ( book_id NUMBER(10) PRIMARY KEY, title VARCHAR2(200) NOT NULL, author VARCHAR2(100), publication_year NUMBER(4) CHECK (publication_year BETWEEN 1900 AND 2025), price NUMBER(8,2) );
Write the SQL statements to create these tables with all constraints.
List each employee's name and their department's name . oracle 12c sql hands-on assignments solutions
Create a table emp_analytics containing the analytical results from Problem 9.
-- Create MEMBERS table with UNIQUE and DEFAULT constraints CREATE TABLE members ( member_id NUMBER(10) PRIMARY KEY, full_name VARCHAR2(100) NOT NULL, email VARCHAR2(100) UNIQUE, membership_date DATE DEFAULT SYSDATE ); -- Create BOOKS table with CHECK constraint CREATE
In this article, we provided a comprehensive guide to Oracle 12c SQL hands-on assignments and solutions. We covered various topics such as database design, SQL queries, data manipulation, and more. By working on these hands-on assignments, you can gain practical experience in Oracle 12c SQL and improve your problem-solving skills. Remember to practice regularly and try to solve real-world scenarios to become proficient in Oracle 12c SQL.
-- Create the database CREATE DATABASE EMPLOYEES; -- Create MEMBERS table with UNIQUE and DEFAULT
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;
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 all products in the oe.product_information table whose list price is between $50 and $200, but exclude products where the product name contains the word 'Monitor'.