Comprehensive Practice: SQL Server Basics

Hands-On Assignment: Retail Store System

Scenario: You are tasked with creating the backend database for a new online retail store. You need to set up the database, create tables for categories and products, enforce data rules, and populate it with sample data.

Task 1: Database Management

Write the SQL command to CREATE a database named RetailStoreDB.
Write the command to ALTER the database to rename it from RetailStoreDB to ShopDB_Temp, and then rename it back to RetailStoreDB.
Write the command to ALTER the database to SINGLE_USER mode (to simulate performing maintenance).
Write the command to switch the database back to MULTI_USER mode.

Task 2: Table Creation & Constraints

Ensure you are executing these against the RetailStoreDB database.

Create the Parent Table: Create a table named Categories.
- CategoryID (Integer, Primary Key)
- CategoryName (Varchar(50), Cannot be null, must be Unique)
Create the Child Table: Create a table named Products.
- ProductID (Integer, Primary Key)
- ProductName (Varchar(100), Cannot be null)
- Price (Decimal, apply a Check constraint so price is > 0)
- StockQuantity (Integer, apply a Default value of 10)
- CategoryID (Integer, Foreign Key linking to Categories(CategoryID). Enable ON DELETE CASCADE).

Task 3: Data Manipulation (INSERT & SELECT)

INSERT Data: Add two records into the Categories table:
- 1, 'Electronics'
- 2, 'Clothing'
INSERT Data: Add three records into the Products table. (Hint: Try omitting the StockQuantity on one of them to test your Default constraint!)
SELECT Data: Write a query to retrieve all columns and rows from the Products table to verify your insertions.

Task 4: Data Manipulation (UPDATE & DELETE)

UPDATE Data: Write a query to increase the Price of a specific product using its ProductID.
Test Cascading Integrity: Write a DELETE statement to remove the 'Electronics' category from the Categories table.
SELECT Data: Query the Products table again. Observe what happened to the electronics products due to the cascading referential integrity.

Task 5: Cleanup Operations

Write the command to DROP the Products table entirely.
Write the command to DROP the Categories table.
Optional: Write the command to drop the RetailStoreDB database.