1)
use sakila;
SELECT c.first_name, c.last_name, r.rental_date
FROM customer c
JOIN rental r ON c.customer_id = r.customer_id;
2)
SELECT c.first_name, c.last_name, a.address
FROM customer c
JOIN address a ON c.address_id = a.address_id
WHERE a.district IN ('Alberta', 'Texas');
3)
SELECT f.title, c.name AS category_name
FROM film f
LEFT JOIN film_category fc ON f.film_id = fc.film_id
LEFT JOIN category c ON fc.category_id = c.category_id
ORDER BY c.name;
4)
SELECT s.store_id, COUNT(r.rental_id) AS rental_count
FROM store s
JOIN inventory i ON s.store_id = i.store_id
JOIN rental r ON i.inventory_id = r.inventory_id
GROUP BY s.store_id;
5)
SELECT c.name AS category_name, COUNT(fc.film_id) AS film_count
FROM category c
JOIN film_category fc ON c.category_id = fc.category_id
GROUP BY c.category_id, c.name
HAVING COUNT(fc.film_id) > 50;
6)
SELECT f.title
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.customer_id = (
SELECT customer_id
FROM customer
WHERE first_name = 'Mary' AND last_name = 'Smith'
);
7)
SELECT f.title, SUM(p.amount) AS total_income
FROM film f
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 f.film_id, f.title;
8)
SELECT a.first_name, a.last_name, COUNT(fa.film_id) AS film_count
FROM actor a
JOIN film_actor fa ON a.actor_id = fa.actor_id
GROUP BY a.actor_id, a.first_name, a.last_name;
SELECT rental_id, customer_id,
CASE
WHEN customer_id > 300 THEN 'New Customer'
ELSE 'Regular Customer'
END AS customer_type
FROM rental;
9)
SELECT DISTINCT f.title, f.release_year
FROM film f
JOIN inventory i ON f.film_id = i.film_id
WHERE f.title LIKE 'A%';
10)
CREATE DATABASE StudentDB;
USE StudentDB;
CREATE TABLE Students (
Student_ID INT,
Marks INT
);
INSERT INTO Students (Student_ID, Marks) VALUES (1, 85);
INSERT INTO Students (Student_ID, Marks) VALUES (2, 90);
ALTER TABLE Students RENAME COLUMN Marks TO Score;
DROP TABLE Students;
DROP DATABASE StudentDB;
