MySQL Sample Database

MySQL Sample Database

A MySQL sample database provides a pre-built schema with tables and data for testing, learning, and practice. It is commonly used by beginners and developers to experiment with SQL queries, practice database operations, and test new ideas.

Popular Sample Databases

1. Sakila: A database designed for a DVD rental store, including customer, inventory, and payment data.
2. World: A database containing information about countries, cities, and languages.
3. Employees: A database for HR management, including employee, department, and salary data.
4. Classicmodels: A database for a fictional car dealership with orders, products, and customers.

Steps to Use a Sample Database

1. Download the Sample Database:

   • Visit the MySQL Sample Databases page to download popular sample databases.
   • Sample files are usually provided in .sql format.

2. Load the Database into MySQL:

   • Use the mysql command-line tool or MySQL Workbench to import the .sql file.

Command-Line Example:

mysql -u root -p < path_to_sample_database.sql

MySQL Workbench Example:

1. Open MySQL Workbench.
2. Navigate to File > Open SQL Script and select the .sql file.
3. Execute the script to load the database.

Example: Sakila Sample Database

Schema Overview:

• Tables:
  • actor: Information about actors.
  • film: Details about films.
  • customer: Customer information.
  • rental: Rental transactions.
• Relationships:
  • Many-to-Many: film_actor table links film and actor.
  • One-to-Many: rental links customer and inventory.

SQL Queries:

1. Fetch all films by an actor:

   SELECT f.title
   FROM film f
   JOIN film_actor fa ON f.film_id = fa.film_id
   JOIN actor a ON fa.actor_id = a.actor_id
   WHERE a.first_name = 'Tom' AND a.last_name = 'Hanks';
   

2. List customers with overdue rentals:

   SELECT c.first_name, c.last_name, r.rental_date
   FROM customer c
   JOIN rental r ON c.customer_id = r.customer_id
   WHERE r.return_date IS NULL
     AND r.rental_date < CURDATE() - INTERVAL 7 DAY;
   

Example: World Sample Database

Schema Overview:

• Tables:
  • country: Information about countries.
  • city: Data about cities.
  • countrylanguage: Languages spoken in each country.

SQL Queries:

1. Get all countries with a population greater than 100 million:

   SELECT name, population
   FROM country
   WHERE population > 100000000
   ORDER BY population DESC;
   

2. Find cities in a specific country:

   SELECT name, population
   FROM city
   WHERE countrycode = 'USA'
   ORDER BY population DESC;
   

Example: Classicmodels Sample Database

Schema Overview:

• Tables:
  • customers: Customer details.
  • orders: Order information.
  • products: Product catalog.
  • orderdetails: Line items for orders.

SQL Queries:

1. Fetch all orders placed by a customer:

   SELECT o.orderNumber, o.orderDate, od.productCode, od.quantityOrdered, od.priceEach
   FROM orders o
   JOIN orderdetails od ON o.orderNumber = od.orderNumber
   WHERE o.customerNumber = 103;
   

2. Calculate total sales by product:

   SELECT p.productName, SUM(od.quantityOrdered * od.priceEach) AS total_sales
   FROM products p
   JOIN orderdetails od ON p.productCode = od.productCode
   GROUP BY p.productName
   ORDER BY total_sales DESC;
   

Benefits of Sample Databases

1. Learning Tool:
   • Explore database schemas and practice writing queries.
2. Testing:
   • Use sample data to test database operations before deploying on production systems.
3. Demonstration:
   • Showcase SQL skills or explain database concepts using familiar datasets.
4. Debugging:
   • Debug scripts and queries in a controlled environment.

Creating Your Own Sample Database

If you need a custom sample database, follow these steps:

1. Define the Schema:
   • Plan the tables, fields, and relationships.
2. Insert Data:
   • Use INSERT statements to populate the tables with sample data.

Example: Custom Library Database

CREATE TABLE books (
    book_id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(255),
    author VARCHAR(255),
    published_year INT
);

CREATE TABLE members (
    member_id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(100),
    last_name VARCHAR(100),
    join_date DATE
);

CREATE TABLE loans (
    loan_id INT PRIMARY KEY AUTO_INCREMENT,
    book_id INT,
    member_id INT,
    loan_date DATE,
    return_date DATE,
    FOREIGN KEY (book_id) REFERENCES books(book_id),
    FOREIGN KEY (member_id) REFERENCES members(member_id)
);

Let me know if you'd like detailed steps for setting up any specific sample database!