SQL Language - Exam 142

Author: Bekir Yagan

A comprehensive SQL learning resource featuring practical exercises with SQLite databases, covering database creation, data manipulation, and advanced querying techniques using IMDB movie data.

---

Overview

This repository contains a complete SQL examination covering fundamental to advanced database concepts. The exam is divided into two main exercises plus seven additional practice problems.

Contents

Exercise 1: Distributor Database

Create and manage a simple beverage distribution database:

• Table creation with primary keys
• Data insertion and manipulation
• Foreign key relationships
• Referential integrity concepts

Exercise 2: IMDB Database Analysis

Query a real-world movie database containing:

• 276,832+ titles (movies, TV series, shorts, etc.)
• People data (actors, directors, crew)
• Ratings and votes
• Episode information

Additional Exercises (7 Extra Problems)

• Longest runtime movies
• Most prolific directors
• Titles by decade analysis
• Multi-role performers (actor-directors)
• TV series episode counts
• Rating distribution analysis
• Living vs deceased performers statistics

---

Database Schema

Distributor Database

Drinks(DrinkId, Name, Size, Price)
Invoices(InvoiceId, DrinkId, Hour)

IMDB Database

PEOPLE(person_id, name, born, died)
TITLES(title_id, type, primary_title, original_title, is_adult, premiered, ended, runtime_minutes, genres)
CREW(title_id, person_id, category, job, characters)
RATINGS(title_id, rating, votes)
EPISODES(episode_title_id, show_title_id, season_number, episode_number)

---

Prerequisites

• Python 3.x
• Jupyter Notebook
• SQLite3
• ipython-sql package

Installation

1. Clone this repository:

git clone https://github.com/bekiryagan/sql-exam-142.git
cd sql-exam-142

2. Install required packages:

pip install jupyter ipython-sql

3. Launch Jupyter Notebook:

jupyter notebook sql-exam-142.ipynb

---

Topics Covered

Topic	Description
CREATE TABLE	Creating tables with appropriate data types
PRIMARY KEY	Choosing and implementing primary keys
FOREIGN KEY	Establishing table relationships
INSERT	Adding data to tables
SELECT	Querying data with conditions
JOIN	Combining data from multiple tables
GROUP BY	Aggregating data by categories
ORDER BY	Sorting query results
HAVING	Filtering grouped data
Subqueries	Nested queries for complex conditions
CTEs	Common Table Expressions
CASE	Conditional logic in queries

---

SQL Concepts Demonstrated

Basic Operations

• Database connection
• Table creation and deletion
• Data insertion and updates

Query Techniques

• Filtering with WHERE
• Sorting with ORDER BY
• Limiting results with LIMIT
• Using NOT IN for exclusions

Aggregation

• COUNT, AVG, SUM, MIN, MAX
• Grouping with GROUP BY
• Filtering groups with HAVING

Advanced Features

• Multi-table JOINs
• Common Table Expressions (WITH)
• CASE statements for conditional logic
• String manipulation functions

---

Sample Queries

Find highest-rated titles

SELECT t.primary_title, r.rating, r.votes
FROM titles t
JOIN ratings r ON t.title_id = r.title_id
WHERE r.votes > 5000
ORDER BY r.rating DESC, r.votes DESC
LIMIT 10;

Compare actor vs actress ratings

SELECT 
    CASE 
        WHEN c.category = 'actress' THEN 'Actresses'
        WHEN c.category = 'actor' THEN 'Actors'
    END AS category,
    ROUND(AVG(r.rating), 2) AS average_rating
FROM crew c
JOIN ratings r ON c.title_id = r.title_id
WHERE c.category IN ('actor', 'actress')
GROUP BY c.category;

---

License

This project is open source and available under the MIT License.

---

Author

Bekir Yagan

SQL Language - Exam 142