Tutorial Database Relational dg Excel oleh Andy P Hamzah

Summary

In this tutorial, the presenter guides viewers on how to create a relational database using Excel, emphasizing the use of Power Pivot. The session covers essential steps, including data preparation, table creation, and establishing relationships among four key tables: sales, inventory, and customer data. Five queries are addressed, revealing invoice details, sales figures for televisions in October, and customer information. The presenter provides clear instructions on executing queries, demonstrating how to filter and organize data effectively, ensuring a hands-on learning experience that empowers users to apply these techniques in their projects.

Takeaways

• 😀 The tutorial focuses on creating a relational database using Excel and Power Pivot.
• 📊 Four tables are involved: Sales, Inventory, Customer, and Sales Inventory.
• 🔍 There are five specific queries to address, each requiring detailed data manipulation.
• 🛠️ To begin, ranges of data must be converted into tables with appropriate names.
• 📈 The tables are then imported into the data model for relational linking.
• 🔗 Relationships between tables are established using primary and foreign keys.
• 📝 The first query retrieves invoice numbers and salespersons for each sale.
• 📅 The second query counts the number of televisions sold in October.
• 🏠 The third query lists names and addresses of customers who bought televisions in October.
• 💰 The fourth query displays invoice numbers, dates, and totals for sales made in October.
• 👨‍💼 The fifth query calculates total sales attributed to each salesperson.

Q & A

What is the primary objective of the tutorial in the video?

The primary objective is to guide viewers in creating a relational database in Excel using Power Pivot and answering five specific queries related to sales, inventory, and customer data.

How many tables are involved in the relational database, and what are they?

There are four tables involved: Sales, Inventory, Sales Inventory, and Customer.

What is the purpose of Power Pivot in this tutorial?

Power Pivot is used to manage and analyze data, enabling the user to create a relational database and execute complex queries in Excel.

What are the first steps in setting up the relational database in Excel?

The first steps involve converting data ranges into tables using the 'Format as Table' option, naming the tables (e.g., Sales, Inventory, Customer), and then adding them to the Excel Data Model.

How are relationships between tables established in the database?

Relationships are established using the 'Diagram View' in Power Pivot, where fields like 'Sales Invoice Number' and 'Customer Number' are dragged and dropped to create links between tables.

In Query 1, what fields are included to show sales invoice numbers and the salesperson for each sale?

In Query 1, the fields included are 'Sales Invoice Number', 'Salesperson', and 'Customer Name'. These fields are arranged in a Pivot Table with 'Salesperson' filtered for clarity.

How is the total number of televisions sold in October calculated in Query 2?

In Query 2, the total number of televisions sold in October is calculated by filtering the 'Description' field for 'Television' and 'October', and then summing the 'Quantity' sold.

What information is retrieved in Query 3 about customers who bought televisions in October?

Query 3 retrieves the names and addresses of customers who bought televisions in October, along with the quantity purchased. The 'Customer Name', 'Customer Address', and 'Description' (for 'Television') are used in the Pivot Table.

What data is displayed in Query 4 regarding sales invoice numbers in October?

In Query 4, the data displayed includes 'Sales Invoice Number', 'Invoice Date', and 'Total'. The results are sorted by total sales in descending order and filtered by the month of October.

What is the focus of Query 5 regarding total sales by salesperson?

Query 5 focuses on calculating the total sales per salesperson. The fields 'Salesperson', 'Quantity', and 'Unit Price' are used to show the total sales for each salesperson.