SELECT Queries UPDATE Queries ALTER Queries DELETE Queries String Functions Date Functions Aggregate/Sort Constraints Views Indexes Joins

MySQL Movie Rating Database Queries

Database Schema: Practice MySQL queries on a Telugu movie rating database with string functions, date functions, and aggregate operations.

S.No	Movie Name	Year	Actor	Director	Rating	Review
1	Baahubali: The Beginning	2015	Prabhas	S.S. Rajamouli	4.8	Epic visual spectacle that revolutionized Indian cinema
2	Baahubali 2: The Conclusion	2017	Prabhas	S.S. Rajamouli	4.9	Grand conclusion to the epic saga with amazing climax
3	RRR	2022	N.T. Rama Rao Jr.	S.S. Rajamouli	4.9	Oscar-winning masterpiece with breathtaking action sequences
4	Pushpa: The Rise	2021	Allu Arjun	Sukumar	4.5	Mass entertainer with Allu Arjun's iconic performance
5	Ala Vaikunthapurramuloo	2020	Allu Arjun	Trivikram Srinivas	4.3	Perfect blend of entertainment and emotions
6	Arjun Reddy	2017	Vijay Deverakonda	Sandeep Reddy Vanga	4.2	Bold and intense romantic drama
7	Jersey	2019	Nani	Gowtam Tinnanuri	4.6	Heartwarming sports drama with brilliant performances
8	Sita Ramam	2022	Dulquer Salmaan	Hanu Raghavapudi	4.7	Beautiful period love story with soulful music
9	Eega	2012	Nani	S.S. Rajamouli	4.4	Innovative revenge story with amazing visual effects
10	Majili	2019	Nani	Shiva Nirvana	4.1	Emotional love story with realistic performances

Table Creation SQL

CREATE TABLE movie_ratings (
    sno INT AUTO_INCREMENT PRIMARY KEY,
    moviename VARCHAR(100) NOT NULL,
    released_year INT,
    actor VARCHAR(100),
    director VARCHAR(100),
    rating DECIMAL(2,1) CHECK (rating BETWEEN 0.0 AND 5.0),
    review TEXT,
    last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Insert sample data
INSERT INTO movie_ratings (moviename, released_year, actor, director, rating, review) VALUES
('Baahubali: The Beginning', 2015, 'Prabhas', 'S.S. Rajamouli', 4.8, 'Epic visual spectacle that revolutionized Indian cinema'),
('Baahubali 2: The Conclusion', 2017, 'Prabhas', 'S.S. Rajamouli', 4.9, 'Grand conclusion to the epic saga with amazing climax'),
('RRR', 2022, 'N.T. Rama Rao Jr.', 'S.S. Rajamouli', 4.9, 'Oscar-winning masterpiece with breathtaking action sequences'),
('Pushpa: The Rise', 2021, 'Allu Arjun', 'Sukumar', 4.5, 'Mass entertainer with Allu Arjun''s iconic performance'),
('Ala Vaikunthapurramuloo', 2020, 'Allu Arjun', 'Trivikram Srinivas', 4.3, 'Perfect blend of entertainment and emotions'),
('Arjun Reddy', 2017, 'Vijay Deverakonda', 'Sandeep Reddy Vanga', 4.2, 'Bold and intense romantic drama'),
('Jersey', 2019, 'Nani', 'Gowtam Tinnanuri', 4.6, 'Heartwarming sports drama with brilliant performances'),
('Sita Ramam', 2022, 'Dulquer Salmaan', 'Hanu Raghavapudi', 4.7, 'Beautiful period love story with soulful music'),
('Eega', 2012, 'Nani', 'S.S. Rajamouli', 4.4, 'Innovative revenge story with amazing visual effects'),
('Majili', 2019, 'Nani', 'Shiva Nirvana', 4.1, 'Emotional love story with realistic performances');

Interactive SELECT SQL Queries

Hover over each query to see the SQL statement and expected results.

1. Display all movies with their release year and rating, sorted alphabetically by movie name

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

2. Show the 5 movies with the highest ratings in descending order

SELECT moviename, rating FROM movie_ratings ORDER BY rating DESC LIMIT 5;

3. List all movies directed by S.S. Rajamouli in chronological order

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

4. Display Allu Arjun's movies sorted by rating (highest first)

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

5. Show movies released in the most recent 5 years

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

6. Find movies where the review contains the word "performance"

SELECT moviename, review FROM movie_ratings 
WHERE review LIKE '%performance%';

7. Calculate average ratings for actors with at least 2 movies

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;

8. Provide statistics about movies grouped by release year

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;

9. List highly rated movies (4.5 stars or above)

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

10. Return formatted movie information in a more readable way

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

11. Identify movies with lengthy reviews (more than 50 characters)

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

12. Categorize movies into rating ranges and count them

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 rating_range
ORDER BY rating_range;

UPDATE SQL Queries

Hover over each query to see the SQL statement and expected results.

1. Update Rating for a Specific Movie

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

Updates the rating of 'Majili' to 4.7

2. Update Review Text

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

3. Update Multiple Columns (Rating + Review)

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

4. Conditional Update (Only if Current Rating is Lower)

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

Only updates if current rating is below 4.6

5. Update Director Name for Multiple Movies

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

Standardizes director name format for all Rajamouli movies

6. Update Based on Release Year

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

7. Update Actor Name (Alias Correction)

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

Changes actor name to more common alias

8. Increment All Ratings by 0.1 (Max 5.0)

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

Gives all movies a small rating boost without exceeding 5.0

9. Update with Subquery (Movies Below Average Rating)

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

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

10. Bulk Update with CASE Statement

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 SQL Queries

Hover over each query to see the SQL statement and expected results.

1. Add a New Column (Box Office Collection)

ALTER TABLE movie_ratings 
ADD COLUMN box_office DECIMAL(12,2) COMMENT 'Collection in crores';

Adds a new column to store box office collection amounts

2. Drop a Column last_updated

ALTER TABLE movie_ratings 
DROP COLUMN last_updated;

Removes the last_updated timestamp column

3. Modify rating Column Data Type

ALTER TABLE movie_ratings 
MODIFY COLUMN rating DECIMAL(3,1);

Changes rating to allow values like 4.25 (more precision)

4. Rename a Column moviename

ALTER TABLE movie_ratings 
RENAME COLUMN moviename TO title;

Changes the column name from moviename to title

5. Add a Constraint (Default Value) to rating column

ALTER TABLE movie_ratings 
ALTER COLUMN rating SET DEFAULT 3.0;

Sets default rating to 3.0 for new records

6. Add a Primary Key to sno

ALTER TABLE movie_ratings 
ADD PRIMARY KEY (sno);

Explicitly defines sno as primary key (though it likely already is)

7. Add a Foreign Key (Requires Reference Table)

-- First create directors table if it doesn't exist
CREATE TABLE directors (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

-- Then add foreign key
ALTER TABLE movie_ratings 
ADD COLUMN director_id INT,
ADD FOREIGN KEY (director_id) REFERENCES directors(id);

Sets up relationship with a directors reference table

8. Add an Index to actor column for Faster Queries

ALTER TABLE movie_ratings 
ADD INDEX idx_actor (actor);

Improves performance for queries filtering by actor

9. Rename the Table

ALTER TABLE movie_ratings 
RENAME TO telugu_movie_ratings;

Changes the table name to be more specific

10. Add Multiple Columns at Once

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

Adds three new columns in a single statement

DELETE SQL Queries

Hover over each query to see the SQL statement and expected results.

1. Delete Single Movie by Name

DELETE FROM movie_ratings 
WHERE moviename = 'Majili';

Removes the specific movie 'Majili' from the table

2. Delete Movies by Release Year (Older Films)

DELETE FROM movie_ratings 
WHERE released_year < 2015;

Deletes all movies released before 2015

3. Delete Low-Rated Movies

DELETE FROM movie_ratings 
WHERE rating < 4.0;

Removes all movies with ratings below 4.0 stars

4. Delete Using Multiple Conditions

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

Deletes Nani's movies with ratings below 4.3

5. Delete with LIMIT (Top 3 Oldest)

DELETE FROM movie_ratings 
ORDER BY released_year ASC 
LIMIT 3;

Removes the 3 oldest movies in the database

6. Delete Using Subquery (Movies Without Awards)

-- 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

7. Delete All Movies by a Director

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

Removes all movies directed by Sandeep Reddy Vanga

8. Delete Using Pattern Matching (Temporary/Test Entries)

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

Removes any test or temporary entries in the database

9. Delete Duplicate Movies (Keeping Highest Rated)

DELETE m1 FROM movie_ratings m1
INNER JOIN movie_ratings m2 
WHERE m1.moviename = m2.moviename 
AND m1.sno < m2.sno 
AND m1.rating <= m2.rating;

Removes duplicate movie entries, keeping only the highest rated version

10. Delete All Data (Truncate Alternative)

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

String Function SQL Queries

Hover over each query to see the SQL statement and expected results.

1. Display all movie names in uppercase letters

SELECT UPPER(moviename) AS uppercase_name 
FROM movie_ratings;

Converts all movie names to uppercase (e.g., "BAAHUBALI: THE BEGINNING")

2. Extract the first 5 characters from all director names

SELECT LEFT(director, 5) AS director_initials 
FROM movie_ratings;

Returns first 5 characters of each director's name (e.g., "S.S. R" for Rajamouli)

3. Concatenate movie name and year with format "MovieName (Year)"

SELECT CONCAT(moviename, ' (', released_year, ')') AS movie_with_year 
FROM movie_ratings;

Creates formatted strings like "Baahubali: The Beginning (2015)"

4. Find movies where review contains "epic" (case insensitive)

SELECT moviename, review 
FROM movie_ratings 
WHERE LOWER(review) LIKE '%epic%';

Finds reviews containing "epic" in any case (e.g., "Epic", "EPIC")

5. Replace "story" with "tale" in review column

SELECT moviename, 
       REPLACE(review, 'story', 'tale') AS modified_review 
FROM movie_ratings 
WHERE review LIKE '%story%';

Changes "story" to "tale" in all reviews containing "story"

6. Display length (in characters) of each movie name

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

Shows character count for each movie name (e.g., "Ala Vaikunthapurramuloo" = 22)

7. Find movies where director's name starts with 'S'

SELECT moviename, director 
FROM movie_ratings 
WHERE director LIKE 'S%';

Returns movies directed by people whose names start with S (e.g., S.S. Rajamouli, Sukumar)

8. Extract the last word from each review

SELECT moviename, 
       SUBSTRING_INDEX(review, ' ', -1) AS last_word 
FROM movie_ratings;

Gets the final word of each review (e.g., "cinema", "sequences", "performances")

9. Display actor names with first letter capitalized

SELECT CONCAT(
    UPPER(LEFT(actor, 1)), 
    LOWER(SUBSTRING(actor, 2))
) AS formatted_name 
FROM movie_ratings;

Formats names like "Prabhas", "Allu arjun" → "Prabhas", "Allu arjun"

10. Find movies where review contains exactly 50 characters

SELECT moviename, review, LENGTH(review) AS review_length 
FROM movie_ratings 
WHERE LENGTH(review) = 50;

Returns only reviews with exactly 50 characters (including spaces)

Date Function SQL Queries

Hover over each query to see the SQL statement and expected results.

1. Display years passed since each movie was released

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

Calculates how many years ago each movie was released (e.g., 2023-2015=8 for Baahubali)

2. Find movies released in January (any year)

SELECT moviename, released_year
FROM movie_ratings
WHERE MONTH(STR_TO_DATE(released_year, '%Y')) = 1;

Returns movies released in January (assuming release date was January 1st of the year)

3. Add 5 years to release year of all movies

SELECT moviename, released_year, 
       released_year + 5 AS future_year
FROM movie_ratings;

Projects what year it would be if each movie was released 5 years later

4. Display movies released in last 5 years

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

Shows movies from current year and previous 4 years (2023-2018)

5. Format year as 'YYYY-MM-DD' (January 1st default)

SELECT moviename, 
       DATE_FORMAT(STR_TO_DATE(released_year, '%Y'), '%Y-%m-%d') AS formatted_date
FROM movie_ratings;

Displays dates as '2015-01-01' for January 1st of release year

6. Find movies released on a leap year

SELECT moviename, released_year
FROM movie_ratings
WHERE (released_year % 4 = 0 AND released_year % 100 != 0) 
   OR (released_year % 400 = 0);

Identifies movies released in leap years (2012, 2016, 2020, etc.)

7. Days between Baahubali 1 and 2 releases

SELECT DATEDIFF(
    STR_TO_DATE('2017', '%Y'), 
    STR_TO_DATE('2015', '%Y')
) AS days_between_baahubali;

Calculates ~730 days (2 years) between 2015 and 2017 releases

8. Movies released on weekend (Saturday/Sunday)

SELECT moviename, released_year
FROM movie_ratings
WHERE DAYOFWEEK(STR_TO_DATE(released_year, '%Y')) IN (1, 7);

Finds movies where January 1st of release year fell on weekend

9. Group movies by release quarter (Q1-Q4)

SELECT 
    CONCAT('Q', QUARTER(STR_TO_DATE(released_year, '%Y'))) AS quarter,
    COUNT(*) AS movie_count
FROM movie_ratings
GROUP BY quarter
ORDER BY quarter;

Shows distribution of movies across calendar quarters (all Q1 in current data)

10. Movies released more than 10 years ago

SELECT moviename, released_year
FROM movie_ratings
WHERE released_year <= YEAR(CURDATE()) - 10
ORDER BY released_year;

Returns movies from 2013 or earlier (as of 2023)

Aggregate, Sort, and GROUP BY SQL Queries

Hover over each query to see the SQL statement and expected results.

1. Find the average rating of all movies

SELECT AVG(rating) AS average_rating
FROM movie_ratings;

Calculates the overall average rating of all movies (approximately 4.44)

2. Find the highest rated movie

SELECT moviename, rating
FROM movie_ratings
ORDER BY rating DESC
LIMIT 1;

Returns the top-rated movie (Baahubali 2: The Conclusion or RRR with 4.9)

3. Count movies per director

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

Shows S.S. Rajamouli with 4 movies, others with 1 each

4. Display movies grouped by rounded rating

SELECT ROUND(rating) AS rounded_rating, 
       GROUP_CONCAT(moviename) AS movies
FROM movie_ratings
GROUP BY rounded_rating
ORDER BY rounded_rating DESC;

Groups movies into 4-star and 5-star categories

5. Average rating by year

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

Shows yearly average ratings (2022 highest with ~4.8)

6. Count movies released each year

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

2019 and 2022 have 2 movies each, other years have 1

7. Director with highest average rating (min 2 movies)

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

Returns S.S. Rajamouli with ~4.75 average rating

8. Longest and shortest movie names

(SELECT moviename, LENGTH(moviename) AS name_length
 FROM movie_ratings
 ORDER BY name_length DESC
 LIMIT 1)
UNION
(SELECT moviename, LENGTH(moviename) AS name_length
 FROM movie_ratings
 ORDER BY name_length ASC
 LIMIT 1);

Shows "Ala Vaikunthapurramuloo" (22 chars) as longest and "RRR" (3 chars) as shortest

9. Count reviews containing "performance"

SELECT COUNT(*) AS performance_reviews
FROM movie_ratings
WHERE review LIKE '%performance%';

Returns count of reviews mentioning "performance" (2 in sample data)

10. Group movies by actor with stats

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

Shows Prabhas and Nani with 3 movies each, Allu Arjun with 2, others with 1

Table Constraints in SQL

Hover over each query to see the SQL statement and expected results.

1. Complete Table Definition with Constraints

CREATE TABLE movie_ratings (
    sno INT AUTO_INCREMENT,
    moviename VARCHAR(100) NOT NULL,
    released_year INT NOT NULL CHECK (released_year BETWEEN 1900 AND YEAR(CURDATE())),
    actor VARCHAR(100) NOT NULL,
    director VARCHAR(100) NOT NULL,
    rating DECIMAL(2,1) NOT NULL CHECK (rating BETWEEN 0.5 AND 5.0),
    review TEXT,
    language VARCHAR(20) DEFAULT 'Telugu',
    ott_platform VARCHAR(30),
    last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (sno),
    UNIQUE KEY unique_movie (moviename, released_year),
    CONSTRAINT chk_ott CHECK (ott_platform IN ('Netflix', 'Prime', 'Hotstar', 'Zee5', 'Aha', 'Disney+', NULL))
);

-- Reference table for foreign key
CREATE TABLE ott_platforms (
    platform_id INT AUTO_INCREMENT PRIMARY KEY,
    platform_name VARCHAR(30) UNIQUE NOT NULL,
    subscription_cost DECIMAL(5,2)
);

-- Add foreign key constraint
ALTER TABLE movie_ratings
ADD CONSTRAINT fk_ott_platform
FOREIGN KEY (ott_platform) REFERENCES ott_platforms(platform_name);

Complete table creation with all constraints including primary key, foreign key, checks, and defaults

2. Testing NOT NULL Constraint

-- Should fail (missing moviename)
INSERT INTO movie_ratings (released_year, actor, director, rating) 
VALUES (2020, 'Prabhas', 'S.S. Rajamouli', 4.5);
-- Error: Column 'moviename' cannot be null

Demonstrates enforcement of required fields

3. Testing CHECK Constraint (Rating Range)

-- Should fail (rating too high)
INSERT INTO movie_ratings (moviename, released_year, actor, director, rating)
VALUES ('Test Movie', 2023, 'Actor', 'Director', 5.5);
-- Error: Check constraint 'movie_ratings_chk_1' is violated

Ensures ratings stay within 0.5-5.0 range

4. Testing UNIQUE Constraint

-- Insert first movie
INSERT INTO movie_ratings (moviename, released_year, actor, director, rating)
VALUES ('Baahubali', 2015, 'Prabhas', 'Rajamouli', 4.8);

-- Should fail (duplicate movie/year)
INSERT INTO movie_ratings (moviename, released_year, actor, director, rating)
VALUES ('Baahubali', 2015, 'Prabhas', 'Rajamouli', 4.9);
-- Error: Duplicate entry 'Baahubali-2015' for key 'unique_movie'

Prevents duplicate movie entries for same year

5. Testing DEFAULT Constraint

-- Insert without specifying language
INSERT INTO movie_ratings (moviename, released_year, actor, director, rating)
VALUES ('Pushpa', 2021, 'Allu Arjun', 'Sukumar', 4.5);

-- Verify default was applied
SELECT moviename, language FROM movie_ratings WHERE moviename = 'Pushpa';
-- Returns: Pushpa | Telugu

Shows automatic assignment of default 'Telugu' language

6. Testing FOREIGN KEY Constraint

-- First insert valid platform
INSERT INTO ott_platforms (platform_name, subscription_cost)
VALUES ('Prime', 149.00);

-- Valid insert (foreign key exists)
INSERT INTO movie_ratings (moviename, released_year, actor, director, rating, ott_platform)
VALUES ('RRR', 2022, 'NTR', 'Rajamouli', 4.9, 'Prime');

-- Should fail (invalid platform)
INSERT INTO movie_ratings (moviename, released_year, actor, director, rating, ott_platform)
VALUES ('Test', 2023, 'Actor', 'Director', 4.0, 'UnknownPlatform');
-- Error: Cannot add or update a child row: a foreign key constraint fails

Ensures OTT platform references exist in platforms table

7. Testing CHECK Constraint (OTT Platform Values)

-- Should fail (invalid platform)
INSERT INTO movie_ratings (moviename, released_year, actor, director, rating, ott_platform)
VALUES ('Test', 2023, 'Actor', 'Director', 4.0, 'YouTube');
-- Error: Check constraint 'chk_ott' is violated

Restricts OTT platforms to predefined list

8. Testing CHECK Constraint (Release Year)

-- Should fail (year in future)
INSERT INTO movie_ratings (moviename, released_year, actor, director, rating)
VALUES ('Future Movie', 2030, 'Actor', 'Director', 4.0);
-- Error: Check constraint 'movie_ratings_chk_2' is violated

Ensures release years are between 1900 and current year

9. Testing Multiple Constraint Violations

-- Should fail (multiple constraints violated)
INSERT INTO movie_ratings (moviename, released_year, actor, director, rating, ott_platform)
VALUES (NULL, 1800, NULL, NULL, 6.0, 'Invalid');
-- Errors for: NOT NULL, CHECK year, CHECK rating, CHECK ott_platform

Demonstrates multiple constraint validations in single operation

10. Successful Insert with All Constraints

-- Successfully passes all constraints
INSERT INTO ott_platforms (platform_name) VALUES ('Netflix');

INSERT INTO movie_ratings (moviename, released_year, actor, director, rating, ott_platform)
VALUES ('KGF', 2022, 'Yash', 'Prashanth Neel', 4.7, 'Netflix');
-- Successfully inserted

Shows valid data passing all constraint checks

Summary of Constraints Applied

Constraint Type	Applied To	Purpose
PRIMARY KEY	Sno	Uniquely identify each record
NOT NULL	moviename, released_year, actor, director, rating	Ensure essential data exists
CHECK	rating (0.5-5.0), released_year (1900-current), ott_platform (specific values)	Validate data ranges
DEFAULT	language ('Telugu'), last_updated (current timestamp)	Provide sensible defaults
UNIQUE	moviename + released_year combination	Prevent duplicate movie entries
FOREIGN KEY	ott_platform references ott_platforms table	Maintain referential integrity

Views in SQL

Hover over each query to see the SQL statement and expected results.

1. Create a View for High-Rated Movies

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

Creates a view showing only movies with 4.5 stars or higher

2. Create a View for Recent Releases

CREATE VIEW recent_movies AS
SELECT moviename, released_year, actor, rating
FROM movie_ratings
WHERE released_year >= YEAR(CURDATE()) - 3
ORDER BY released_year DESC;

Shows movies released in the last 3 years

3. Create a View with Movie Statistics

CREATE VIEW movie_stats AS
SELECT 
    director,
    COUNT(*) AS movie_count,
    ROUND(AVG(rating), 1) AS avg_rating,
    MIN(released_year) AS earliest_year,
    MAX(released_year) AS latest_year
FROM movie_ratings
GROUP BY director
ORDER BY movie_count DESC;

Provides statistical overview of movies by director

4. Create a View with Formatted Movie Info

CREATE VIEW formatted_movie_info AS
SELECT 
    CONCAT(moviename, ' (', released_year, ')') AS title_year,
    CONCAT('Director: ', director) AS director_info,
    CONCAT('Starring: ', actor) AS cast_info,
    CONCAT('Rating: ', rating, '/5') AS rating_info
FROM movie_ratings;

Creates a view with nicely formatted movie information

5. Create a View for Actor Filmography

CREATE VIEW actor_filmography AS
SELECT 
    actor,
    GROUP_CONCAT(moviename ORDER BY released_year DESC SEPARATOR ', ') AS movies,
    COUNT(*) AS movie_count,
    ROUND(AVG(rating), 1) AS avg_rating
FROM movie_ratings
GROUP BY actor
ORDER BY movie_count DESC;

Shows all movies by each actor with statistics

6. Update a View Definition

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

Modifies the high_rated_movies view to include reviews and lower threshold

7. Query Data from a View

SELECT * FROM high_rated_movies 
WHERE director = 'S.S. Rajamouli';

Retrieves Rajamouli's high-rated movies from the view

8. Drop a View

DROP VIEW IF EXISTS recent_movies;

Removes the recent_movies view from the database

9. Create a View with Calculated Columns

CREATE VIEW movie_analysis AS
SELECT 
    moviename,
    released_year,
    rating,
    CASE 
        WHEN rating >= 4.5 THEN 'Excellent'
        WHEN rating >= 4.0 THEN 'Good'
        WHEN rating >= 3.0 THEN 'Average'
        ELSE 'Below Average'
    END AS rating_category,
    YEAR(CURDATE()) - released_year AS years_since_release
FROM movie_ratings;

Creates a view with categorized ratings and age calculations

10. Show View Definition

SHOW CREATE VIEW high_rated_movies;

Displays the SQL used to create the high_rated_movies view

Indexes in SQL

Hover over each query to see the SQL statement and expected results.

1. Create a Basic Index on Movie Name

CREATE INDEX idx_moviename ON movie_ratings(moviename);

Improves performance for queries filtering or sorting by movie name

2. Create Composite Index on Director and Year

CREATE INDEX idx_director_year ON movie_ratings(director, released_year);

Optimizes queries that filter by both director and year

3. Create Unique Index on Movie+Year Combination

CREATE UNIQUE INDEX idx_unique_movie ON movie_ratings(moviename, released_year);

Ensures no duplicate movie entries for the same year

4. Show All Indexes on the Table

SHOW INDEX FROM movie_ratings;

Displays all indexes defined on the movie_ratings table

5. Drop an Index

DROP INDEX idx_moviename ON movie_ratings;

Removes the moviename index from the table

6. Create Index on Rating Column

CREATE INDEX idx_rating ON movie_ratings(rating);

Improves performance for rating-based queries and sorting

7. Explain Query with Index Usage

EXPLAIN SELECT * FROM movie_ratings 
WHERE director = 'S.S. Rajamouli' AND released_year > 2015;

Shows query execution plan and index usage

8. Create Full-Text Index on Review Column

CREATE FULLTEXT INDEX idx_review_text ON movie_ratings(review);

Enables advanced text searching in reviews

9. Use Full-Text Search with Index

SELECT moviename, review 
FROM movie_ratings 
WHERE MATCH(review) AGAINST('performance');

Performs optimized text search using the full-text index

10. Create Descending Index for Recent Movies

CREATE INDEX idx_year_desc ON movie_ratings(released_year DESC);

Optimizes queries that sort by release year in descending order

Indexing Best Practices

Index columns frequently used in WHERE, ORDER BY, and JOIN clauses
Composite indexes should follow the leftmost prefix rule
Avoid over-indexing as it slows down INSERT/UPDATE operations
Use EXPLAIN to analyze query performance and index usage
Consider full-text indexes for text search operations

SQL Joins with Movie Ratings

Hover over each query to see the SQL statement and expected results.

Note: For join examples, we'll use an additional table called movie_awards:

CREATE TABLE movie_awards (
    award_id INT AUTO_INCREMENT PRIMARY KEY,
    moviename VARCHAR(100) NOT NULL,
    award_name VARCHAR(100) NOT NULL,
    award_year INT NOT NULL,
    category VARCHAR(50),
    FOREIGN KEY (moviename) REFERENCES movie_ratings(moviename)
);

INSERT INTO movie_awards (moviename, award_name, award_year, category) VALUES
('Baahubali: The Beginning', 'National Film Award', 2016, 'Best Feature Film'),
('Baahubali: The Beginning', 'Filmfare Award', 2016, 'Best Film'),
('RRR', 'Oscar', 2023, 'Best Original Song'),
('RRR', 'Golden Globe', 2023, 'Best Foreign Language Film'),
('Pushpa: The Rise', 'Filmfare Award', 2022, 'Best Actor'),
('Sita Ramam', 'National Film Award', 2023, 'Best Music Direction'),
('Jersey', 'Filmfare Award', 2020, 'Best Actor');

1. INNER JOIN - Movies with Awards

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

2. LEFT JOIN - All Movies with Award Info

SELECT r.moviename, r.rating, a.award_name
FROM movie_ratings r
LEFT 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)

3. RIGHT JOIN - All Awards with Movie Info

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

Shows all awards, with movie details if available (theoretical example)

4. FULL OUTER JOIN - All Movies and Awards

-- MySQL doesn't directly support FULL OUTER JOIN, so we emulate with UNION:
SELECT r.moviename, r.rating, a.award_name
FROM movie_ratings r
LEFT JOIN movie_awards a ON r.moviename = a.moviename
UNION
SELECT r.moviename, r.rating, a.award_name
FROM movie_ratings r
RIGHT JOIN movie_awards a ON r.moviename = a.moviename
WHERE r.moviename IS NULL;

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

5. CROSS JOIN - All Possible Combinations

SELECT r.moviename, a.award_name
FROM movie_ratings r
CROSS JOIN movie_awards a
LIMIT 10;  -- Limited for demonstration

Returns Cartesian product (every movie paired with every award)

6. SELF JOIN - Movies by Same Director

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

7. NATURAL JOIN - Movies with Awards

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

Automatically joins on columns with the same name (moviename)

8. JOIN with Multiple Conditions

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

9. JOIN with Aggregate Functions

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

10. JOIN with Subquery

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
) AS recent_awards ON r.moviename = recent_awards.moviename
WHERE r.rating > 4.5;

Joins high-rated movies with awards from recent years

Join Type Comparison

Join Type	Description	When to Use
INNER JOIN	Returns only matching rows from both tables	When you only want records with matches in both tables
LEFT JOIN	Returns all rows from left table with matches from right	When you want all main records plus optional related data
RIGHT JOIN	Returns all rows from right table with matches from left	Rarely used - typically same as LEFT JOIN with reversed tables
FULL OUTER JOIN	Returns all rows when there's a match in either table	When you want all records from both tables combined
CROSS JOIN	Returns Cartesian product of all rows	When you need all possible combinations
SELF JOIN	Joins a table to itself	When comparing records within the same table

Advanced Queries

Try these additional challenges:

Find all movies where the review contains "performance"
Calculate days between releases of Baahubali 1 and 2
Group movies by rating ranges (0-3, 3-4, 4-5)