Database Administration for Beginners/

...

/

Exercise: Database Architecture and Data Storage

Exercise: Database Architecture and Data Storage

Practice and test your understanding of database architecture and data storage.

We'll cover the following...

Time to apply what we’ve learned with some exercises.

Question 1

Given the following structure of the Products table:

Field

Type

ProductID

int

ProductName

varchar(50)

CategoryID

int

Price

decimal(10,2)

Stock

int

LastRestockDate

date

MonthlySales

int

InventoryTurnoverRate

decimal(5,2)

Where ProductID is the primary key and CategoryID is the foreign key from the Categories table. The table contains information about products.

Create a simple view named ActiveProducts that shows ProductID, ProductName, and Price for products that have Stock greater than 0.

Press + to interact
MySQL 8.0
-- Create view ActiveProducts showing in-stock products with ID, name, and price

If you’re stuck, click the “Show Solution” button.

Question 2

Given the following structure of the Orders table:

Field

Type

OrderID

int

CustomerID

int

OrderDate

date

TotalAmount

decimal(10,2)

ShippedDate

date

DeliveryStatus

enum('Pending','Shipped','Delivered','Cancelled')

EmployeeID

int

CreatedAt

timestamp

ExpectedDeliveryDate

date

ActualDeliveryDate

date

LateDelivery

boolean

PaymentMethod

enum('Credit Card','PayPal','Cash on Delivery')

ReturnCount

int

FraudRisk

boolean

Where OrderID is the primary key and CustomerID is the foreign key from the Customers table referring to the customer who placed the order. The table contains information about the orders placed.

Create an index named idx_orders_customer_date on the Orders table for the CustomerID and OrderDate ...