SQL Server Movie Rating Database Queries

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

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

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 >= YEAR(GETDATE()) - 5 
ORDER BY released_year DESC;

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

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 + ' (' + CAST(released_year AS VARCHAR) + ') - Directed by ' + director AS movie_info,
'Starring: ' + actor + ' | Rating: ' + CAST(rating AS VARCHAR) AS details 
FROM movie_ratings;

SELECT moviename, LEN(review) AS review_length 
FROM movie_ratings 
WHERE LEN(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 = CASE WHEN rating + 0.1 > 5.0 THEN 5.0 ELSE rating + 0.1 END;

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 CASE WHEN rating + 0.2 > 5.0 THEN 5.0 ELSE rating + 0.2 END
    WHEN released_year >= 2020 THEN CASE WHEN rating + 0.1 > 5.0 THEN 5.0 ELSE rating + 0.1 END
    ELSE rating
END;

Gives Rajamouli movies extra boost, recent movies small boost

ALTER TABLE movie_ratings 
ADD box_office DECIMAL(12,2) NULL;

Adds a new column to store box office collection amounts

ALTER TABLE movie_ratings 
DROP COLUMN last_updated;

Removes the last_updated timestamp column

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

Changes rating to allow values like 4.25 (more precision)

EXEC sp_rename 'movie_ratings.moviename', 'title', 'COLUMN';

Changes the column name from moviename to title

ALTER TABLE movie_ratings 
ADD CONSTRAINT DF_rating DEFAULT 3.0 FOR rating;

Sets default rating to 3.0 for new records

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

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

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

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

Sets up relationship with a directors reference table

CREATE INDEX idx_actor ON movie_ratings(actor);

Improves performance for queries filtering by actor

EXEC sp_rename 'movie_ratings', 'telugu_movie_ratings';

Changes the table name to be more specific

ALTER TABLE movie_ratings 
ADD duration INT NULL,
    language VARCHAR(20) DEFAULT 'Telugu',
    awards_won INT DEFAULT 0;

Adds three new columns in a single statement

DELETE FROM movie_ratings 
WHERE moviename = 'Majili';

Removes the specific movie 'Majili' from the table

DELETE FROM movie_ratings 
WHERE released_year < 2015;

Deletes all movies released before 2015

DELETE FROM movie_ratings 
WHERE rating < 4.0;

Removes all movies with ratings below 4.0 stars

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

Deletes Nani's movies with ratings below 4.3

DELETE TOP (3) FROM movie_ratings 
ORDER BY released_year ASC;

Removes the 3 oldest movies in the database (SQL Server uses TOP instead of LIMIT)

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

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

Removes all movies directed by Sandeep Reddy Vanga

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

Removes any test or temporary entries in the database

WITH Duplicates AS (
    SELECT moviename, released_year, rating,
           ROW_NUMBER() OVER (PARTITION BY moviename, released_year ORDER BY rating DESC) AS rn
    FROM movie_ratings
)
DELETE FROM Duplicates
WHERE rn > 1;

Removes duplicate movie entries, keeping only the highest rated version (SQL Server uses CTE with ROW_NUMBER)

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 (e.g., "BAAHUBALI: THE BEGINNING")

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

Returns first 5 characters of each director's name (SQL Server uses SUBSTRING instead of LEFT)

SELECT moviename + ' (' + CAST(released_year AS VARCHAR) + ')' AS movie_with_year 
FROM movie_ratings;

Creates formatted strings like "Baahubali: The Beginning (2015)" (SQL Server uses + for concatenation)

SELECT moviename, review 
FROM movie_ratings 
WHERE review LIKE '%epic%' COLLATE SQL_Latin1_General_CP1_CI_AS;

Finds reviews containing "epic" in any case (SQL Server uses COLLATE for case-insensitive search)

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"

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

Shows character count for each movie name (SQL Server uses LEN instead of LENGTH)

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

Returns movies directed by people whose names start with S

SELECT moviename, 
       REVERSE(SUBSTRING(REVERSE(review), 1, 
       CHARINDEX(' ', REVERSE(review)) AS last_word 
FROM movie_ratings;

Gets the final word of each review (SQL Server requires REVERSE function for this operation)

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

Formats names with first letter capitalized (SQL Server string concatenation syntax)

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

Returns only reviews with exactly 50 characters (SQL Server uses LEN)

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

Calculates how many years ago each movie was released (SQL Server uses GETDATE() instead of CURDATE())

SELECT moviename, released_year
FROM movie_ratings
WHERE MONTH(DATEFROMPARTS(released_year, 1, 1)) = 1;

Returns movies released in January (SQL Server uses DATEFROMPARTS to create date)

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

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

Shows movies from current year and previous 4 years (SQL Server uses GETDATE())

SELECT moviename, 
       FORMAT(DATEFROMPARTS(released_year, 1, 1), 'yyyy-MM-dd') AS formatted_date
FROM movie_ratings;

Displays dates as '2015-01-01' for January 1st of release year (SQL Server uses FORMAT and DATEFROMPARTS)

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

SELECT DATEDIFF(DAY, 
    DATEFROMPARTS(2015, 1, 1), 
    DATEFROMPARTS(2017, 1, 1)
) AS days_between_baahubali;

Calculates ~730 days (2 years) between 2015 and 2017 releases (SQL Server uses DATEDIFF with DAY)

SELECT moviename, released_year
FROM movie_ratings
WHERE DATEPART(WEEKDAY, DATEFROMPARTS(released_year, 1, 1)) IN (1, 7);

Finds movies where January 1st of release year fell on weekend (SQL Server uses DATEPART)

SELECT 
    'Q' + CAST(DATEPART(QUARTER, DATEFROMPARTS(released_year, 1, 1)) AS quarter,
    COUNT(*) AS movie_count
FROM movie_ratings
GROUP BY DATEPART(QUARTER, DATEFROMPARTS(released_year, 1, 1))
ORDER BY quarter;

Shows distribution of movies across calendar quarters (SQL Server uses DATEPART with QUARTER)

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

Returns movies from 2013 or earlier (as of 2023) (SQL Server uses GETDATE())

SELECT AVG(rating) AS average_rating
FROM movie_ratings;

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

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

Returns the top-rated movie (SQL Server uses TOP instead of LIMIT)

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

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

Groups movies into 4-star and 5-star categories (SQL Server uses STRING_AGG instead of GROUP_CONCAT)

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)

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

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

Returns S.S. Rajamouli with ~4.75 average rating (SQL Server uses TOP)

SELECT TOP 1 moviename, LEN(moviename) AS name_length
FROM movie_ratings
ORDER BY name_length DESC;
SELECT TOP 1 moviename, LEN(moviename) AS name_length
FROM movie_ratings
ORDER BY name_length ASC;

Shows "Ala Vaikunthapurramuloo" (22 chars) as longest and "RRR" (3 chars) as shortest (SQL Server uses TOP and separate queries)

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

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

SELECT actor, 
       COUNT(*) AS movie_count,
       ROUND(AVG(rating), 1) 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

CREATE TABLE movie_ratings (
    sno INT IDENTITY(1,1) PRIMARY KEY,
    moviename VARCHAR(100) NOT NULL,
    released_year INT NOT NULL CHECK (released_year BETWEEN 1900 AND YEAR(GETDATE())),
    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 DATETIME DEFAULT GETDATE(),
    CONSTRAINT unique_movie UNIQUE (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 IDENTITY(1,1) 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 (SQL Server uses IDENTITY for auto-increment)

-- Should fail (missing moviename)
INSERT INTO movie_ratings (released_year, actor, director, rating) 
VALUES (2020, 'Prabhas', 'S.S. Rajamouli', 4.5);
-- Error: Cannot insert the value NULL into column 'moviename'

Demonstrates enforcement of required fields

-- Should fail (rating too high)
INSERT INTO movie_ratings (moviename, released_year, actor, director, rating)
VALUES ('Test Movie', 2023, 'Actor', 'Director', 5.5);
-- Error: The INSERT statement conflicted with the CHECK constraint

Ensures ratings stay within 0.5-5.0 range

-- 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: Violation of UNIQUE KEY constraint 'unique_movie'

Prevents duplicate movie entries for same year

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

-- 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: The INSERT statement conflicted with the FOREIGN KEY constraint

Ensures OTT platform references exist in platforms table

-- 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: The INSERT statement conflicted with the CHECK constraint 'chk_ott'

Restricts OTT platforms to predefined list

-- Should fail (year in future)
INSERT INTO movie_ratings (moviename, released_year, actor, director, rating)
VALUES ('Future Movie', 2030, 'Actor', 'Director', 4.0);
-- Error: The INSERT statement conflicted with the CHECK constraint

Ensures release years are between 1900 and current year

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

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

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

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

Shows movies released in the last 3 years (SQL Server uses GETDATE() instead of CURDATE())

CREATE VIEW movie_stats AS
SELECT 
    director,
    COUNT(*) AS movie_count,
    ROUND(AVG(CAST(rating AS FLOAT), 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 (explicit CAST in SQL Server)

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

Creates a view with nicely formatted movie information (SQL Server uses + for concatenation)

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

Shows all movies by each actor with statistics (SQL Server 2017+ uses STRING_AGG)

ALTER 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 (SQL Server uses ALTER VIEW)

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

Retrieves Rajamouli's high-rated movies from the view (same syntax as MySQL)

DROP VIEW IF EXISTS recent_movies;

Removes the recent_movies view from the database (SQL Server 2016+ supports IF EXISTS)

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(GETDATE()) - released_year AS years_since_release
FROM movie_ratings;

Creates a view with categorized ratings and age calculations (GETDATE() in SQL Server)

SELECT definition 
FROM sys.sql_modules 
WHERE object_id = OBJECT_ID('high_rated_movies');

Displays the SQL used to create the high_rated_movies view (SQL Server system view)

CREATE INDEX idx_moviename ON movie_ratings(moviename);

Improves performance for queries filtering or sorting by movie name

CREATE INDEX idx_director_year ON movie_ratings(director, released_year);

Optimizes queries that filter by both director and year

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

Ensures no duplicate movie entries for the same year

SELECT * FROM sys.indexes 
WHERE object_id = OBJECT_ID('movie_ratings');

Displays all indexes defined on the movie_ratings table (SQL Server system view)

DROP INDEX idx_moviename ON movie_ratings;

Removes the moviename index from the table

CREATE CLUSTERED INDEX PK_movie_ratings ON movie_ratings(sno);

Creates a clustered index (SQL Server's primary index structure)

SET SHOWPLAN_TEXT ON;
GO
SELECT * FROM movie_ratings 
WHERE director = 'S.S. Rajamouli' AND released_year > 2015;
GO
SET SHOWPLAN_TEXT OFF;

Shows query execution plan and index usage (SQL Server method)

CREATE INDEX idx_high_ratings ON movie_ratings(moviename)
WHERE rating >= 4.5;

Creates an index only for high-rated movies (SQL Server filtered indexes)

CREATE INDEX idx_director_covering ON movie_ratings(director)
INCLUDE (moviename, rating);

Creates index with included columns for covering queries

ALTER INDEX idx_moviename ON movie_ratings REBUILD;

Rebuilds an index to improve performance and reduce fragmentation

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 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 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 COALESCE(r.rating, 0) DESC;

SQL Server directly supports FULL OUTER JOIN (unlike MySQL)

SELECT TOP 10 r.moviename, a.award_name
FROM movie_ratings r
CROSS JOIN movie_awards a;

Returns Cartesian product (every movie paired with every award) - TOP 10 limits results

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 d.director, top_movie.moviename, top_movie.rating
FROM (SELECT DISTINCT director FROM movie_ratings) d
CROSS APPLY (
    SELECT TOP 1 moviename, rating
    FROM movie_ratings m
    WHERE m.director = d.director
    ORDER BY rating DESC
) AS top_movie;

SQL Server's CROSS APPLY (similar to lateral join) finds top movie per director

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(CAST(r.rating AS FLOAT)) 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 (explicit CAST in SQL Server)

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

Joins high-rated movies with awards from recent years using CTE