# Download from dev.mysql.com (or use the direct URL)
wget https://downloads.mysql.com/docs/world-db.tar.gz

# Extract
tar -xzf world-db.tar.gz

# Import the SQL file
mysql -u root -p < world-db/world.sql

# Verify import
mysql -u root -p -e "USE world; SHOW TABLES;"

USE world;

-- Explore the schema
DESCRIBE country;
DESCRIBE city;

-- Top 10 most populous countries
SELECT Name, Population, Continent
FROM country
ORDER BY Population DESC
LIMIT 10;

-- All cities in Canada
SELECT Name, District, Population
FROM city
WHERE CountryCode = 'CAN'
ORDER BY Population DESC;

-- Average life expectancy by continent
SELECT Continent, ROUND(AVG(LifeExpectancy), 1) AS avg_life_expectancy
FROM country
WHERE LifeExpectancy IS NOT NULL
GROUP BY Continent
ORDER BY avg_life_expectancy DESC;

-- Countries with more than 5 official languages
SELECT co.Name, COUNT(*) AS language_count
FROM country co
JOIN countrylanguage cl ON co.Code = cl.CountryCode
WHERE cl.IsOfficial = 'T'
GROUP BY co.Code, co.Name
HAVING language_count > 5
ORDER BY language_count DESC;

# Download the Sakila database
wget https://downloads.mysql.com/docs/sakila-db.tar.gz

# Extract
tar -xzf sakila-db.tar.gz

# Import schema first, then data
mysql -u root -p < sakila-db/sakila-schema.sql
mysql -u root -p < sakila-db/sakila-data.sql

# Verify
mysql -u root -p -e "USE sakila; SHOW TABLES;" | head -20

USE sakila;

-- See all tables including views
SHOW FULL TABLES;

-- Explore a table's structure
DESCRIBE film;
DESCRIBE rental;

-- Show the CREATE TABLE statement for a complex table
SHOW CREATE TABLE rentalG

USE sakila;

-- Top 10 most rented films
SELECT f.title, COUNT(r.rental_id) AS times_rented
FROM film f
JOIN inventory i ON f.film_id = i.film_id
JOIN rental r ON i.inventory_id = r.inventory_id
GROUP BY f.film_id, f.title
ORDER BY times_rented DESC
LIMIT 10;

-- Revenue by film category
SELECT c.name AS category, SUM(p.amount) AS total_revenue
FROM category c
JOIN film_category fc ON c.category_id = fc.category_id
JOIN film f ON fc.film_id = f.film_id
JOIN inventory i ON f.film_id = i.film_id
JOIN rental r ON i.inventory_id = r.inventory_id
JOIN payment p ON r.rental_id = p.rental_id
GROUP BY c.category_id, c.name
ORDER BY total_revenue DESC;

-- Customers who have never rented
SELECT c.customer_id, c.first_name, c.last_name, c.email
FROM customer c
LEFT JOIN rental r ON c.customer_id = r.customer_id
WHERE r.rental_id IS NULL;

-- Average rental duration by rating
SELECT f.rating, ROUND(AVG(DATEDIFF(r.return_date, r.rental_date)), 1) AS avg_days
FROM film f
JOIN inventory i ON f.film_id = i.film_id
JOIN rental r ON i.inventory_id = r.inventory_id
WHERE r.return_date IS NOT NULL
GROUP BY f.rating
ORDER BY avg_days DESC;

# Clone from GitHub (official source)
git clone https://github.com/datacharmer/test_db.git
cd test_db

# Import (takes 1-3 minutes due to size)
mysql -u root -p < employees.sql

# Verify the import with the included test script
mysql -u root -p -t < test_employees_md5.sql

USE employees;

-- Current employees in each department
SELECT d.dept_name, COUNT(*) AS employee_count
FROM departments d
JOIN dept_emp de ON d.dept_no = de.dept_no
WHERE de.to_date = '9999-01-01'  -- current assignments
GROUP BY d.dept_no, d.dept_name
ORDER BY employee_count DESC;

-- Average salary by department (current salaries only)
SELECT d.dept_name, ROUND(AVG(s.salary), 2) AS avg_salary
FROM departments d
JOIN dept_emp de ON d.dept_no = de.dept_no
JOIN salaries s ON de.emp_no = s.emp_no
WHERE de.to_date = '9999-01-01'
  AND s.to_date = '9999-01-01'
GROUP BY d.dept_no, d.dept_name
ORDER BY avg_salary DESC;

-- Employees who have held more than 3 different titles
SELECT e.emp_no, e.first_name, e.last_name, COUNT(*) AS title_count
FROM employees e
JOIN titles t ON e.emp_no = t.emp_no
GROUP BY e.emp_no, e.first_name, e.last_name
HAVING title_count > 3
ORDER BY title_count DESC
LIMIT 10;

-- List all tables with row counts
SELECT
  TABLE_NAME,
  TABLE_ROWS,
  ROUND(DATA_LENGTH / 1024 / 1024, 2) AS data_mb,
  ROUND(INDEX_LENGTH / 1024 / 1024, 2) AS index_mb,
  TABLE_COMMENT
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'sakila'
ORDER BY DATA_LENGTH DESC;

-- List all columns in a database with their types
SELECT
  TABLE_NAME,
  COLUMN_NAME,
  COLUMN_TYPE,
  IS_NULLABLE,
  COLUMN_DEFAULT,
  COLUMN_KEY
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'sakila'
ORDER BY TABLE_NAME, ORDINAL_POSITION;

-- List all foreign key relationships in a database
SELECT
  TABLE_NAME,
  COLUMN_NAME,
  REFERENCED_TABLE_NAME,
  REFERENCED_COLUMN_NAME
FROM information_schema.KEY_COLUMN_USAGE
WHERE TABLE_SCHEMA = 'sakila'
  AND REFERENCED_TABLE_NAME IS NOT NULL
ORDER BY TABLE_NAME;

-- Find all indexes in a database
SELECT
  TABLE_NAME,
  INDEX_NAME,
  GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX) AS columns,
  INDEX_TYPE,
  NON_UNIQUE
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = 'sakila'
GROUP BY TABLE_NAME, INDEX_NAME
ORDER BY TABLE_NAME;