Skip to main content
Documents
Basic SyntaxMath FunctionsDate FunctionsJSON FunctionsDatabasesTables & Schema ManagementString FunctionsTriggersIndexes

PostgreSQL Sample Database

PostgreSQL Sample Databases are pre-built datasets provided to help users learn and practice SQL queries in PostgreSQL. These databases are essential for testing and demonstrating database functionality without the need to create data from scratch.

Sample databases are used for educational purposes, testing, and development to explore database features and SQL skills. They offer a ready-made structure and data to experiment with various SQL queries and functions.


-- Command to connect to a sample database
\c sample_database_name

In this syntax, `\c` is a PostgreSQL command used to connect to the specified sample database.

Installation Instructions

To begin using sample databases like `dvdrental`, you need to download and set them up. After downloading, use the following command to restore the database in your PostgreSQL environment:


pg_restore -U postgres -d dvdrental <path_to_dvdrental.tar>

Replace `<path_to_dvdrental.tar>` with the path to your downloaded sample database file.

Examples

1. Connecting to a Sample Database


\c dvdrental

This command connects to the `dvdrental` sample database, allowing you to execute SQL queries within it.

2. Selecting Data from a Table


SELECT * FROM film;

This query retrieves all records from the `film` table in the connected sample database, providing insight into the movie inventory.

3. Complex Query with Joins


SELECT c.customer_id, c.first_name, c.last_name, r.rental_date
FROM customer c
JOIN rental r ON c.customer_id = r.customer_id
WHERE r.rental_date > '2023-01-01';

This example demonstrates a more complex query using joins to list customers and their rental activities after January 1, 2023.

4. Query with Aggregation


SELECT c.customer_id, COUNT(r.rental_id) AS total_rentals
FROM customer c
JOIN rental r ON c.customer_id = r.customer_id
GROUP BY c.customer_id
HAVING COUNT(r.rental_id) > 10;

This query aggregates data to find customers who have made more than ten rentals.

Tips and Best Practices

  • Use sample databases for learning. They are ideal for practicing SQL queries and understanding database management without data entry.
  • Explore various tables. Experiment with different tables to understand relationships and data structures.
  • Experiment with queries. Test different types of queries, such as aggregation and subqueries, to deepen your understanding of SQL.
  • Backup your work. If modifying a sample database, back up significant changes to avoid data loss.