Oracle Movie Rating Database Queries

SELECT moviename, released_year, rating FROM movie_ratings ORDER BY moviename;

SELECT moviename, rating FROM movie_ratings ORDER BY rating DESC FETCH FIRST 5 ROWS ONLY;

SELECT moviename, released_year FROM movie_ratings 
WHERE director LIKE '%Rajamouli%' ORDER BY released_year;

SELECT moviename, released_year, rating FROM movie_ratings 
WHERE actor = 'Allu Arjun' ORDER BY rating DESC;

SELECT moviename, released_year FROM movie_ratings 
WHERE released_year >= EXTRACT(YEAR FROM SYSDATE) - 5 
ORDER BY released_year DESC;

SELECT moviename, review FROM movie_ratings 
WHERE REGEXP_LIKE(review, 'performance', 'i');

SELECT actor, AVG(rating) AS avg_rating, COUNT(*) AS movie_count 
FROM movie_ratings 
GROUP BY actor 
HAVING COUNT(*) >= 2 
ORDER BY avg_rating DESC;

SELECT released_year, COUNT(*) AS movie_count, 
AVG(rating) AS avg_rating, MAX(rating) AS max_rating 
FROM movie_ratings 
GROUP BY released_year 
ORDER BY released_year DESC;

SELECT moviename, rating FROM movie_ratings 
WHERE rating BETWEEN 4.5 AND 5.0 
ORDER BY rating DESC;

SELECT moviename || ' (' || released_year || ')' || ' - Directed by ' || director AS movie_info,
'Starring: ' || actor || ' | Rating: ' || rating AS details 
FROM movie_ratings;

SELECT moviename, LENGTH(review) AS review_length 
FROM movie_ratings 
WHERE LENGTH(review) > 50 
ORDER BY review_length DESC;

SELECT 
    CASE 
        WHEN rating BETWEEN 0 AND 3 THEN '0-3'
        WHEN rating BETWEEN 3 AND 4 THEN '3-4'
        WHEN rating BETWEEN 4 AND 5 THEN '4-5'
    END AS rating_range,
    COUNT(*) AS movie_count
FROM movie_ratings
GROUP BY 
    CASE 
        WHEN rating BETWEEN 0 AND 3 THEN '0-3'
        WHEN rating BETWEEN 3 AND 4 THEN '3-4'
        WHEN rating BETWEEN 4 AND 5 THEN '4-5'
    END
ORDER BY rating_range;

UPDATE movie_ratings 
SET rating = 4.7 
WHERE moviename = 'Majili';

Updates the rating of 'Majili' to 4.7

UPDATE movie_ratings 
SET review = 'Epic masterpiece that changed Indian cinema forever' 
WHERE moviename = 'Baahubali: The Beginning';

Modifies the review text for Baahubali: The Beginning

UPDATE movie_ratings 
SET rating = 4.8, 
    review = 'Even better on second viewing - true classic' 
WHERE moviename = 'RRR';

Updates both rating and review for RRR simultaneously

UPDATE movie_ratings 
SET rating = 4.6 
WHERE moviename = 'Arjun Reddy' AND rating < 4.6;

Only updates if current rating is below 4.6

UPDATE movie_ratings 
SET director = 'S.S. Rajamouli' 
WHERE director LIKE '%Rajamouli%';

Standardizes director name format for all Rajamouli movies

UPDATE movie_ratings 
SET rating = rating + 0.1 
WHERE released_year = 2022 AND rating <= 4.9;

Gives a small rating boost to all 2022 releases

UPDATE movie_ratings 
SET actor = 'Jr. NTR' 
WHERE actor = 'N.T. Rama Rao Jr.';

Changes actor name to more common alias

UPDATE movie_ratings 
SET rating = LEAST(rating + 0.1, 5.0);

Gives all movies a small rating boost without exceeding 5.0

UPDATE movie_ratings 
SET review = review || ' [Underrated]' 
WHERE rating < (SELECT AVG(rating) FROM movie_ratings);

Marks reviews of below-average movies with "[Underrated]"

UPDATE movie_ratings 
SET rating = CASE 
    WHEN director = 'S.S. Rajamouli' THEN LEAST(rating + 0.2, 5.0)
    WHEN released_year >= 2020 THEN LEAST(rating + 0.1, 5.0)
    ELSE rating
END;

Gives Rajamouli movies extra boost, recent movies small boost

ALTER TABLE movie_ratings 
ADD (box_office NUMBER(12,2));

Adds a new column to store box office collection amounts in Oracle

ALTER TABLE movie_ratings 
DROP COLUMN last_updated;

Removes the last_updated timestamp column (same syntax as MySQL)

ALTER TABLE movie_ratings 
MODIFY (rating NUMBER(3,1));

Changes rating to allow values like 4.25 (Oracle uses MODIFY instead of ALTER COLUMN)

ALTER TABLE movie_ratings 
RENAME COLUMN moviename TO title;

Changes the column name from moviename to title (same syntax as MySQL)

ALTER TABLE movie_ratings 
MODIFY (rating DEFAULT 3.0);

Sets default rating to 3.0 for new records (Oracle uses MODIFY for defaults)

ALTER TABLE movie_ratings 
ADD CONSTRAINT pk_movie_ratings PRIMARY KEY (sno);

Explicitly defines sno as primary key with a constraint name

-- First create directors table if it doesn't exist
CREATE TABLE directors (
    id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name VARCHAR2(100) NOT NULL
);

-- Then add foreign key
ALTER TABLE movie_ratings 
ADD (director_id NUMBER);

ALTER TABLE movie_ratings
ADD CONSTRAINT fk_director 
FOREIGN KEY (director_id) REFERENCES directors(id);

Sets up relationship with a directors reference table (Oracle syntax)

CREATE INDEX idx_actor ON movie_ratings(actor);

Improves performance for queries filtering by actor (Oracle uses CREATE INDEX)

RENAME movie_ratings TO telugu_movie_ratings;

Changes the table name to be more specific (Oracle uses RENAME command)

ALTER TABLE movie_ratings 
ADD (
    duration NUMBER COMMENT 'Runtime in minutes',
    language VARCHAR2(20) DEFAULT 'Telugu',
    awards_won NUMBER DEFAULT 0
);

Adds three new columns in a single statement (Oracle syntax)

ALTER TABLE movie_ratings
ADD CONSTRAINT chk_rating 
CHECK (rating BETWEEN 0.5 AND 5.0);

Adds a named check constraint in Oracle

-- Disable constraint
ALTER TABLE movie_ratings
DISABLE CONSTRAINT chk_rating;

-- Enable constraint
ALTER TABLE movie_ratings
ENABLE CONSTRAINT chk_rating;

Shows Oracle's ability to temporarily disable constraints

DELETE FROM movie_ratings 
WHERE moviename = 'Majili';

Removes the specific movie 'Majili' from the table (same syntax as MySQL)

DELETE FROM movie_ratings 
WHERE released_year < 2015;

Deletes all movies released before 2015 (same syntax as MySQL)

DELETE FROM movie_ratings 
WHERE rating < 4.0;

Removes all movies with ratings below 4.0 stars (same syntax as MySQL)

DELETE FROM movie_ratings 
WHERE actor = 'Nani' AND rating < 4.3;

Deletes Nani's movies with ratings below 4.3 (same syntax as MySQL)

DELETE FROM movie_ratings 
WHERE sno IN (
    SELECT sno FROM (
        SELECT sno FROM movie_ratings 
        ORDER BY released_year ASC
    ) WHERE ROWNUM <= 3
);

Oracle uses ROWNUM instead of LIMIT to remove the 3 oldest movies

-- Assuming we have an awards column
DELETE FROM movie_ratings 
WHERE moviename NOT IN (
    SELECT moviename FROM movie_ratings 
    WHERE awards_won > 0
);

Deletes movies that haven't won any awards (same syntax as MySQL)

DELETE FROM movie_ratings 
WHERE director = 'Sandeep Reddy Vanga';

Removes all movies directed by Sandeep Reddy Vanga (same syntax as MySQL)

DELETE FROM movie_ratings 
WHERE moviename LIKE '%test%' OR moviename LIKE '%temp%';

Removes any test or temporary entries in the database (same syntax as MySQL)

DELETE FROM movie_ratings
WHERE ROWID NOT IN (
    SELECT MIN(ROWID)
    FROM movie_ratings
    GROUP BY moviename, released_year
);

Oracle uses ROWID to identify and remove duplicate movie entries

DELETE FROM movie_ratings;

Removes all data from the table while keeping the structure intact

Note: For large tables, TRUNCATE TABLE would be more efficient

SELECT UPPER(moviename) AS uppercase_name 
FROM movie_ratings;

Converts all movie names to uppercase (same syntax as MySQL)

SELECT SUBSTR(director, 1, 5) AS director_initials 
FROM movie_ratings;

Oracle uses SUBSTR instead of LEFT for substring operations

SELECT moviename || ' (' || released_year || ')' AS movie_with_year 
FROM movie_ratings;

Oracle uses || for concatenation instead of CONCAT()

SELECT moviename, review 
FROM movie_ratings 
WHERE REGEXP_LIKE(review, 'epic', 'i');

Oracle uses REGEXP_LIKE for advanced pattern matching

SELECT moviename, 
       REPLACE(review, 'story', 'tale') AS modified_review 
FROM movie_ratings 
WHERE INSTR(review, 'story') > 0;

Oracle uses INSTR instead of LIKE for position checking

SELECT moviename, LENGTH(moviename) AS name_length 
FROM movie_ratings 
ORDER BY name_length DESC;

Same syntax as MySQL for LENGTH function

SELECT RPAD(moviename, 30, '.') AS padded_name 
FROM movie_ratings;

Shows Oracle's string padding functions

SELECT moviename,
       REGEXP_SUBSTR(moviename, '\(([^)]+)\)', 1, 1, NULL, 1) AS subtitle
FROM movie_ratings
WHERE moviename LIKE '%(%';

Demonstrates Oracle's advanced REGEXP_SUBSTR function

SELECT moviename,
       TRANSLATE(moviename, 'aeiou', 'AEIOU') AS uppercase_vowels
FROM movie_ratings;

Shows Oracle's TRANSLATE function for character replacement

SELECT moviename 
FROM movie_ratings 
WHERE SOUNDEX(moviename) = SOUNDEX('Bahubali');

Demonstrates Oracle's SOUNDEX function for phonetic matching

SELECT moviename, released_year, 
       EXTRACT(YEAR FROM SYSDATE) - released_year AS years_since_release
FROM movie_ratings;

Oracle uses EXTRACT and SYSDATE for date operations

SELECT moviename, 
       TO_DATE(released_year, 'YYYY') AS release_date
FROM movie_ratings;

Oracle's TO_DATE function converts strings to dates

SELECT moviename, released_year, 
       ADD_MONTHS(TO_DATE(released_year, 'YYYY'), 60) AS future_date
FROM movie_ratings;

Oracle uses ADD_MONTHS for date arithmetic

SELECT moviename, released_year
FROM movie_ratings
WHERE TO_DATE(released_year, 'YYYY') >= ADD_MONTHS(SYSDATE, -60)
ORDER BY released_year DESC;

Oracle date comparison with SYSDATE

SELECT moviename, 
       TO_CHAR(TO_DATE(released_year, 'YYYY'), 'DD-MON-YYYY') AS formatted_date
FROM movie_ratings;

Oracle's TO_CHAR for date formatting

SELECT moviename, released_year
FROM movie_ratings
WHERE TO_CHAR(TO_DATE(released_year, 'YYYY'), 'Q') = '1';

Oracle quarter extraction using TO_CHAR

SELECT 
    TO_DATE('2017', 'YYYY') - TO_DATE('2015', 'YYYY') AS days_between_baahubali
FROM dual;

Oracle date subtraction returns number of days

SELECT moviename,
       LAST_DAY(TO_DATE(released_year, 'YYYY')) AS year_end
FROM movie_ratings;

Oracle's LAST_DAY function

SELECT moviename,
       NEXT_DAY(TO_DATE(released_year, 'YYYY'), 'FRIDAY') AS next_friday
FROM movie_ratings;

Oracle's NEXT_DAY function

SELECT moviename,
       MONTHS_BETWEEN(SYSDATE, TO_DATE(released_year, 'YYYY')) AS months_since_release
FROM movie_ratings;

Oracle's MONTHS_BETWEEN function

SELECT AVG(rating) AS average_rating
FROM movie_ratings;

Same syntax as MySQL for basic aggregation

SELECT moviename, rating,
       RANK() OVER (ORDER BY rating DESC) AS rank,
       DENSE_RANK() OVER (ORDER BY rating DESC) AS dense_rank
FROM movie_ratings;

Oracle's advanced analytic functions

SELECT director, COUNT(*) AS movie_count
FROM movie_ratings
GROUP BY director
ORDER BY movie_count DESC;

Same syntax as MySQL for GROUP BY

SELECT director, actor, COUNT(*) AS movie_count
FROM movie_ratings
GROUP BY ROLLUP(director, actor);

Oracle's ROLLUP for hierarchical aggregates

SELECT director, released_year, AVG(rating) AS avg_rating
FROM movie_ratings
GROUP BY CUBE(director, released_year);

Oracle's CUBE for multi-dimensional analysis

SELECT director, actor, COUNT(*) AS movie_count
FROM movie_ratings
GROUP BY GROUPING SETS((director), (actor), ());

Oracle's flexible GROUPING SETS

SELECT director, 
       LISTAGG(moviename, ', ') WITHIN GROUP (ORDER BY released_year) AS movies
FROM movie_ratings
GROUP BY director;

Oracle's LISTAGG instead of MySQL's GROUP_CONCAT

SELECT *
FROM (
    SELECT director, rating
    FROM movie_ratings
)
PIVOT (
    COUNT(*)
    FOR rating IN (4.0 AS "4.0", 4.5 AS "4.5", 5.0 AS "5.0")
);

Oracle's PIVOT for cross-tab reports

SELECT 
    MIN(released_year) KEEP (DENSE_RANK FIRST ORDER BY rating) AS year_of_highest_rated,
    MAX(released_year) KEEP (DENSE_RANK LAST ORDER BY rating) AS year_of_lowest_rated
FROM movie_ratings;

Oracle's FIRST/LAST analytic functions

SELECT moviename, rating,
       NTILE(4) OVER (ORDER BY rating DESC) AS quartile
FROM movie_ratings;

Oracle's NTILE for dividing data into buckets

CREATE TABLE movie_ratings (
    sno NUMBER GENERATED ALWAYS AS IDENTITY,
    moviename VARCHAR2(100) NOT NULL,
    released_year NUMBER NOT NULL CHECK (released_year BETWEEN 1900 AND EXTRACT(YEAR FROM SYSDATE)),
    actor VARCHAR2(100) NOT NULL,
    director VARCHAR2(100) NOT NULL,
    rating NUMBER(2,1) NOT NULL CHECK (rating BETWEEN 0.5 AND 5.0),
    review CLOB,
    language VARCHAR2(20) DEFAULT 'Telugu',
    ott_platform VARCHAR2(30),
    last_updated TIMESTAMP DEFAULT SYSTIMESTAMP,
    CONSTRAINT pk_movie_ratings PRIMARY KEY (sno),
    CONSTRAINT unique_movie UNIQUE (moviename, released_year),
    CONSTRAINT chk_ott CHECK (ott_platform IN ('Netflix', 'Prime', 'Hotstar', 'Zee5', 'Aha', 'Disney+', NULL))
);

CREATE TABLE ott_platforms (
    platform_id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    platform_name VARCHAR2(30) UNIQUE NOT NULL,
    subscription_cost NUMBER(5,2)
);

ALTER TABLE movie_ratings
ADD CONSTRAINT fk_ott_platform
FOREIGN KEY (ott_platform) REFERENCES ott_platforms(platform_name);

Oracle-specific table creation with constraints

-- Disable constraint
ALTER TABLE movie_ratings DISABLE CONSTRAINT chk_ott;

-- Enable constraint
ALTER TABLE movie_ratings ENABLE CONSTRAINT chk_ott;

Oracle's constraint state management

ALTER TABLE movie_ratings
MODIFY CONSTRAINT fk_ott_platform
DEFERRABLE INITIALLY DEFERRED;

Oracle's deferrable constraint option

-- Add constraint without validating existing data
ALTER TABLE movie_ratings
ADD CONSTRAINT chk_rating_new CHECK (rating > 3.0) NOVALIDATE;

Oracle's NOVALIDATE option for constraints

-- View all constraints
SELECT constraint_name, constraint_type, status 
FROM user_constraints
WHERE table_name = 'MOVIE_RATINGS';

Querying Oracle's data dictionary for constraints

CREATE OR REPLACE VIEW high_rated_movies AS
SELECT moviename, released_year, rating, director
FROM movie_ratings
WHERE rating >= 4.5
ORDER BY rating DESC;

Same syntax as MySQL for basic views

CREATE OR REPLACE FORCE VIEW movie_summary AS
SELECT * FROM non_existent_table;

Oracle's FORCE option to create view with errors

CREATE OR REPLACE VIEW read_only_movies AS
SELECT * FROM movie_ratings
WITH READ ONLY;

Oracle's WITH READ ONLY clause

CREATE OR REPLACE VIEW recent_movies AS
SELECT * FROM movie_ratings
WHERE released_year >= EXTRACT(YEAR FROM SYSDATE) - 3
WITH CHECK OPTION CONSTRAINT chk_recent_movies;

Oracle's WITH CHECK OPTION for updatable views

CREATE OR REPLACE VIEW movie_info BEQUEATH DEFINER AS
SELECT * FROM movie_ratings;

Oracle's BEQUEATH clause for view privileges

CREATE MATERIALIZED VIEW mv_movie_stats
REFRESH COMPLETE ON DEMAND
AS
SELECT director, COUNT(*) AS movie_count, AVG(rating) AS avg_rating
FROM movie_ratings
GROUP BY director;

Oracle's materialized views for pre-computed results

CREATE OR REPLACE TYPE movie_type AS OBJECT (
    name VARCHAR2(100),
    year NUMBER,
    rating NUMBER(2,1)
);

CREATE OR REPLACE VIEW object_movies OF movie_type
WITH OBJECT IDENTIFIER(name) AS
SELECT moviename, released_year, rating
FROM movie_ratings;

Oracle's object-oriented view capabilities

CREATE OR REPLACE VIEW xml_movies AS
SELECT XMLELEMENT("movie",
    XMLATTRIBUTES(moviename AS "name", released_year AS "year"),
    XMLFOREST(rating AS "rating", director AS "director")
) AS movie_xml
FROM movie_ratings;

Oracle's XML generation capabilities in views

CREATE OR REPLACE VIEW historical_movies AS
SELECT * FROM movie_ratings
AS OF TIMESTAMP SYSTIMESTAMP - INTERVAL '1' HOUR;

Oracle's flashback query in views

CREATE OR REPLACE VIEW movie_extras AS
SELECT m.*,
    CASE WHEN rating >= 4.5 THEN 'Excellent' ELSE 'Good' END AS rating_category,
    EXTRACT(YEAR FROM SYSDATE) - released_year AS years_old
FROM movie_ratings m;

Oracle's computed columns in views

CREATE INDEX idx_moviename ON movie_ratings(moviename);

Standard B-tree index in Oracle

CREATE BITMAP INDEX idx_rating_bitmap ON movie_ratings(rating);

Oracle's bitmap index for low-cardinality columns

CREATE INDEX idx_upper_moviename ON movie_ratings(UPPER(moviename));

Oracle's function-based indexes

CREATE INDEX idx_sno_reverse ON movie_ratings(sno) REVERSE;

Oracle's reverse key indexes for sequence-based columns

CREATE TABLE movie_ratings_iot (
    sno NUMBER PRIMARY KEY,
    moviename VARCHAR2(100),
    released_year NUMBER
) ORGANIZATION INDEX;

Oracle's index-organized tables

CREATE INDEX idx_review_text ON movie_ratings(review)
INDEXTYPE IS CTXSYS.CONTEXT;

Oracle's text domain index for full-text search

CREATE INDEX idx_invisible ON movie_ratings(director) INVISIBLE;

Oracle's invisible indexes for testing

CREATE INDEX idx_partitioned ON movie_ratings(released_year)
LOCAL (PARTITION p_2010, PARTITION p_2015, PARTITION p_2020);

Oracle's partitioned indexes

-- Start monitoring
ALTER INDEX idx_moviename MONITORING USAGE;

-- Check usage
SELECT * FROM v$object_usage
WHERE index_name = 'IDX_MOVIENAME';

Oracle's index usage monitoring

CREATE INDEX idx_compressed ON movie_ratings(moviename, director)
COMPRESS 1;

Oracle's index compression

SELECT r.moviename, r.director, a.award_name, a.award_year
FROM movie_ratings r
INNER JOIN movie_awards a ON r.moviename = a.moviename
ORDER BY a.award_year DESC;

Returns only movies that have won awards, with award details

SELECT r.moviename, r.rating, a.award_name
FROM movie_ratings r
LEFT OUTER JOIN movie_awards a ON r.moviename = a.moviename
ORDER BY r.rating DESC;

Shows all movies, with award info if available (NULL for no awards)

SELECT a.award_name, a.category, r.moviename, r.director
FROM movie_ratings r
RIGHT OUTER JOIN movie_awards a ON r.moviename = a.moviename
ORDER BY a.award_year;

Shows all awards, with movie details if available

SELECT r.moviename, r.rating, a.award_name
FROM movie_ratings r
FULL OUTER JOIN movie_awards a ON r.moviename = a.moviename
ORDER BY r.moviename;

Combines all movies and all awards (shows movies without awards and awards without movies)

SELECT r.moviename, a.award_name
FROM movie_ratings r
CROSS JOIN movie_awards a
WHERE ROWNUM <= 10;  -- Limited for demonstration

Returns Cartesian product (every movie paired with every award)

SELECT a.moviename AS movie1, b.moviename AS movie2, a.director
FROM movie_ratings a
JOIN movie_ratings b ON a.director = b.director AND a.moviename < b.moviename
ORDER BY a.director;

Finds pairs of movies made by the same director

SELECT moviename, rating, award_name
FROM movie_ratings
NATURAL JOIN movie_awards;

Automatically joins on columns with the same name (moviename)

SELECT r.moviename, r.released_year, a.award_name, a.award_year
FROM movie_ratings r
JOIN movie_awards a ON r.moviename = a.moviename 
    AND a.award_year > r.released_year
ORDER BY r.moviename;

Joins movies with awards won in years after release

SELECT r.director, COUNT(a.award_id) AS award_count, AVG(r.rating) AS avg_rating
FROM movie_ratings r
LEFT JOIN movie_awards a ON r.moviename = a.moviename
GROUP BY r.director
ORDER BY award_count DESC;

Shows directors with their movie counts and average ratings

SELECT r.moviename, r.rating, recent_awards.award_name
FROM movie_ratings r
JOIN (
    SELECT moviename, award_name 
    FROM movie_awards 
    WHERE award_year >= 2022
) recent_awards ON r.moviename = recent_awards.moviename
WHERE r.rating > 4.5;

Joins high-rated movies with awards from recent years

SELECT r.moviename, a.award_name
FROM movie_ratings r
LEFT OUTER JOIN movie_awards a PARTITION BY (a.moviename)
ON r.moviename = a.moviename
ORDER BY r.moviename;

Oracle-specific join that partitions the right table before joining

SELECT r.moviename, r.rating, a.award_name
FROM movie_ratings r
JOIN movie_awards a USING (moviename)
WHERE r.rating > 4.0;

Simplifies join syntax when column names are identical

DECLARE
    v_movie_count NUMBER;
BEGIN
    SELECT COUNT(*) INTO v_movie_count 
    FROM movie_ratings 
    WHERE rating > 4.0;
    
    DBMS_OUTPUT.PUT_LINE('High-rated movies: ' || v_movie_count);
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END;
/

Simple PL/SQL block to count high-rated movies

CREATE OR REPLACE PROCEDURE update_movie_rating(
    p_moviename IN VARCHAR2,
    p_new_rating IN NUMBER
) AS
    v_old_rating NUMBER;
BEGIN
    -- Get current rating
    SELECT rating INTO v_old_rating
    FROM movie_ratings
    WHERE moviename = p_moviename;
    
    -- Update rating
    UPDATE movie_ratings
    SET rating = p_new_rating
    WHERE moviename = p_moviename;
    
    COMMIT;
    
    DBMS_OUTPUT.PUT_LINE('Updated ' || p_moviename || 
        ' from ' || v_old_rating || ' to ' || p_new_rating);
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('Movie not found');
    WHEN OTHERS THEN
        ROLLBACK;
        DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END;
/

-- Execute procedure
EXEC update_movie_rating('RRR', 4.9);

Stored procedure to update movie ratings with error handling

CREATE OR REPLACE FUNCTION get_avg_rating(
    p_director IN VARCHAR2
) RETURN NUMBER AS
    v_avg_rating NUMBER;
BEGIN
    SELECT AVG(rating) INTO v_avg_rating
    FROM movie_ratings
    WHERE director = p_director;
    
    RETURN NVL(v_avg_rating, 0);
EXCEPTION
    WHEN OTHERS THEN
        RETURN -1; -- Error indicator
END;
/

-- Use the function
SELECT director, get_avg_rating(director) AS avg_rating
FROM movie_ratings
GROUP BY director;

Function to calculate average rating for a director

DECLARE
    CURSOR c_movies IS
        SELECT moviename, rating 
        FROM movie_ratings
        WHERE rating > 4.5
        ORDER BY rating DESC;
        
    r_movie c_movies%ROWTYPE;
BEGIN
    OPEN c_movies;
    LOOP
        FETCH c_movies INTO r_movie;
        EXIT WHEN c_movies%NOTFOUND;
        
        DBMS_OUTPUT.PUT_LINE(r_movie.moviename || ': ' || r_movie.rating);
    END LOOP;
    CLOSE c_movies;
END;
/

Explicit cursor to process high-rated movies

CREATE OR REPLACE TRIGGER trg_movie_audit
BEFORE INSERT OR UPDATE OR DELETE ON movie_ratings
FOR EACH ROW
DECLARE
    v_action VARCHAR2(10);
BEGIN
    IF INSERTING THEN
        v_action := 'INSERT';
    ELSIF UPDATING THEN
        v_action := 'UPDATE';
    ELSE
        v_action := 'DELETE';
    END IF;
    
    INSERT INTO movie_audit_log (
        log_id,
        moviename,
        action,
        change_date,
        old_rating,
        new_rating
    ) VALUES (
        audit_seq.NEXTVAL,
        :NEW.moviename,
        v_action,
        SYSDATE,
        :OLD.rating,
        :NEW.rating
    );
END;
/

Trigger to audit changes to movie ratings

EXPLAIN PLAN FOR
SELECT r.moviename, a.award_name
FROM movie_ratings r
JOIN movie_awards a ON r.moviename = a.moviename
WHERE r.rating > 4.0;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

Generates and displays execution plan for query optimization

CREATE INDEX idx_movie_rating ON movie_ratings(rating);

-- Function-based index
CREATE INDEX idx_movie_upper ON movie_ratings(UPPER(moviename));

-- Bitmap index for low-cardinality columns
CREATE BITMAP INDEX idx_movie_language ON movie_ratings(language);

Different index types for various query patterns

SELECT /*+ INDEX(r idx_movie_rating) */ r.moviename, r.rating
FROM movie_ratings r
WHERE r.rating > 4.0;

SELECT /*+ FIRST_ROWS(10) */ moviename, rating
FROM movie_ratings
ORDER BY rating DESC;

Directing the optimizer with specific execution hints

-- Range partitioning by release year
CREATE TABLE movie_ratings_part (
    sno NUMBER PRIMARY KEY,
    moviename VARCHAR2(100),
    released_year NUMBER,
    rating NUMBER(3,1)
PARTITION BY RANGE (released_year) (
    PARTITION movies_pre2000 VALUES LESS THAN (2000),
    PARTITION movies_2000s VALUES LESS THAN (2010),
    PARTITION movies_2010s VALUES LESS THAN (2020),
    PARTITION movies_current VALUES LESS THAN (MAXVALUE)
);

Improving performance for large tables with partitioning

CREATE MATERIALIZED VIEW mv_movie_stats
REFRESH COMPLETE ON DEMAND
ENABLE QUERY REWRITE
AS
SELECT 
    director,
    COUNT(*) AS movie_count,
    AVG(rating) AS avg_rating,
    MIN(released_year) AS earliest_year,
    MAX(released_year) AS latest_year
FROM movie_ratings
GROUP BY director;

Pre-aggregated data for faster reporting queries