A project based on analzing data from Rockbuster Stealth LLC movie company using PostgreSQL.
Rockbuster Stealth LLC is an imaginary movie rental company that used to have stores around the world. Facing stiff competition from streaming services such as Netflix and Amazon Prime, they aim at locating where loyal customers are located and boosting revenue in locations based on film iventory.
Rockbuster data would likely include various details and records related to video rentals, customers, movies, transactions, and more. Visualisation for this project can be found Tableau Here
Language: PostgreSQL Software: pgAdmin, DbVisualizer,Lucidchart, Tableau, Excel
During the Rockbuster SQL project, i demonstrated a range of skills and competencies related to database management and SQL querying. Here are some of the skills i can showcase:
-
Database Design: Designing a relational database schema that reflects the structure and relationships of the data in the Rockbuster video rental store. This involves creating tables, defining primary keys, and establishing foreign key relationships.
-
Table Creation: Creating SQL scripts to generate the necessary tables for storing data related to customers, movies, rentals, employees, genres, and any other relevant entities in the video rental system.
-
Data Insertion: Demonstrating the ability to populate the database with sample data or initial records, which is essential for testing and development purposes.
-
Data Retrieval (SELECT Statements): Writing SQL queries to retrieve specific information from the database, such as customer details, movie information, rental history, and employee records.
-
Filtering and Sorting: Using SQL clauses like WHERE and ORDER BY to filter and sort query results based on various criteria, such as rental dates, customer IDs, movie genres, etc.
-
Aggregate Functions: Employing SQL aggregate functions like COUNT, SUM, AVG, and MAX/MIN to calculate and present summary statistics or metrics related to the video rental data.
-
Join Operations: Performing JOIN operations (INNER JOIN, LEFT JOIN, RIGHT JOIN, etc.) to combine data from multiple tables when necessary, such as retrieving customer rental history with movie details.
-
Subqueries: Using subqueries to retrieve data within the context of another query, such as finding the most rented movie or the customer with the highest number of rentals.
-
Data Modification (UPDATE and DELETE Statements): Writing SQL statements to update or delete records as needed, demonstrating data maintenance and management capabilities.
-
Data Integrity: Implementing constraints like UNIQUE, NOT NULL, and FOREIGN KEY constraints to ensure data integrity and prevent data inconsistencies.
-
Database Indexing: Utilizing indexing to optimize query performance, especially when dealing with large datasets.
-
Data Reporting: Generating SQL-based reports that provide insights into the Rockbuster video rental business, such as monthly rental trends, top-performing movies, or customer loyalty analysis.
-
Documentation: Creating clear and well-documented SQL scripts and queries, including comments and explanations for complex or critical sections of code.
-
Error Handling: Handling potential errors and exceptions gracefully in SQL code, providing robust and reliable database operations.
-
Query Optimization: Identifying and implementing SQL query optimization techniques to improve query performance when working with large datasets.
-
Data Security: Considering data security best practices, such as ensuring that sensitive information is appropriately protected, and access is restricted based on user roles and permissions.
Revenue Earning by category Top 10 movies that contributed the highest revenue gain 10 movies that contributed the least in revenue gain Geographical distribution of customers and the revenue generated in each country Customer location with high lifetime value Sales Distribution