Lab 1: Database Creation and Basic SQL Operations

🎯 Learning Objectives

By the end of this lab, you will be able to: - Create and manage databases in MySQL/MariaDB - Design and create tables with appropriate data types - Insert data into tables - Write basic SELECT queries with filtering and sorting - Understand primary keys and constraints

📋 Prerequisites

• MySQL or MariaDB server installed and running
• Basic understanding of relational database concepts
• Access to a SQL client (command line, MySQL Workbench, etc.)

🚀 Getting Started

Step 1: Create Your First Database

A database is a container that holds related tables. Let's create one for our exercises:

-- CREATE a NEW DATABASE (only needed once)
CREATE DATABASE lab1_exercises;

Note: Database names are case-sensitive on some systems. Use lowercase for consistency.

Step 2: Select the Database

Before creating tables, you must specify which database to use:

-- Switch to our NEW DATABASE
USE lab1_exercises;

Important: You need to run USE database_name; every time you start a new session.

Step 3: Create Your First Table

Let's create a product catalog table with proper data types and constraints:

-- CREATE the product TABLE WITH detailed comments
CREATE TABLE product(
    pname        VARCHAR(20) PRIMARY KEY,  -- Product name (UNIQUE identifier)
    price        DECIMAL(10,2),            -- Price WITH 2 DECIMAL places
    category     VARCHAR(20),              -- Product category
    manufacturer VARCHAR(20) NOT NULL      -- Manufacturer (required field)
);

Key Concepts: - PRIMARY KEY: Ensures each product name is unique - DECIMAL(10,2): Stores numbers with up to 10 digits, 2 after decimal point - NOT NULL: This field cannot be empty - VARCHAR(20): Variable-length string up to 20 characters

Step 4: Insert Sample Data

Now let's add some products to our table:

-- INSERT sample products
INSERT INTO product VALUES('Gizmo', 19.99, 'Gadgets', 'GizmoWorks');
INSERT INTO product VALUES('PowerGizmo', 29.99, 'Gadgets', 'GizmoWorks');
INSERT INTO product VALUES('MultiTouch', 203.99, 'Household', 'Hitachi');
INSERT INTO product VALUES('SingleTouch', 149.99, 'Photography', 'Canon');

Verify your data:

-- VIEW ALL products
SELECT * FROM product;

Expected Output:

💡 Practice Exercises

Exercise #1: Filtering and Sorting

Task: Write a query to get all products with "Touch" in the name, showing only their name and price, sorted alphabetically by manufacturer.

Step-by-step approach: 1. First, let's see all our products to understand the data: sql SELECT * FROM product;

1. Now identify products with "Touch" in the name: sql SELECT * FROM product WHERE pname LIKE '%Touch%';

2. Your turn: Write the complete query here: ```sql -- Write your solution here

```

SELECT pname, price 
FROM product 
WHERE pname LIKE '%Touch%' 
ORDER BY manufacturer;

Exercise #1b: Distinct Values

Task: Write a query that returns the distinct names of manufacturers that make products with "Gizmo" in the name.

Your solution:

-- Write your solution here

SELECT DISTINCT manufacturer 
FROM product 
WHERE pname LIKE '%Gizmo%';

Exercise #2: Understanding DISTINCT and ORDER BY

Task: Before running these queries, predict what each will return. Then run them to check your understanding.

-- Query A
SELECT DISTINCT category FROM product ORDER BY category;

-- Query B  
SELECT category FROM product ORDER BY pname;

-- Query C
SELECT DISTINCT category FROM product ORDER BY pname;

Predictions: - Query A will return: __ - Query B will return: __
- Query C will return: ____

Exercise #3: Multi-Table Relationships and Foreign Keys

Learning Focus: Understanding table relationships, foreign keys, and multi-table queries.

Setting Up the Database Schema

We need to recreate our tables to demonstrate foreign key relationships:

-- Clean up existing TABLES (ORDER matters due to FOREIGN KEY constraints!)
DROP TABLE IF EXISTS product;  -- DROP child TABLE first
DROP TABLE IF EXISTS company;   -- THEN DROP parent TABLE

-- CREATE the parent TABLE (company)
CREATE TABLE company (
    cname VARCHAR(20) PRIMARY KEY,    -- Company name (UNIQUE identifier)
    stockprice DECIMAL(10,2),         -- Stock price IN dollars
    country VARCHAR(10)               -- Country of origin
);

-- INSERT company data
INSERT INTO company VALUES ('ToyWorks', 25.0, 'USA');
INSERT INTO company VALUES ('ToyFriends', 65.0, 'China');
INSERT INTO company VALUES ('ToyCo', 15.0, 'China');

Verify company data:

SELECT * FROM company;

Now create the product table with a foreign key relationship:

-- CREATE the child TABLE (product) WITH FOREIGN KEY CONSTRAINT
CREATE TABLE product(
    pname VARCHAR(10),                -- Product name
    price DECIMAL(10,2),              -- Product price
    category VARCHAR(10),             -- Product category
    manufacturer VARCHAR(10),         -- Manufacturer (REFERENCES company)
    PRIMARY KEY (pname, manufacturer), -- Composite PRIMARY KEY
    FOREIGN KEY (manufacturer) REFERENCES company(cname)  -- FOREIGN KEY CONSTRAINT
);

-- INSERT product data
INSERT INTO product VALUES('Pikachu', 19.99, 'Toy', 'ToyWorks');
INSERT INTO product VALUES('Pikachu', 19.99, 'Toy', 'ToyFriends');
INSERT INTO product VALUES('Pokeball', 29.99, 'Electronic', 'ToyCo');
INSERT INTO product VALUES('Bulbasaur', 149.99, 'Toy', 'ToyFriends');
INSERT INTO product VALUES('Charizard', 203.99, 'Toy', 'ToyCo');
INSERT INTO product VALUES('PokeCamera', 19.99, 'Electronic', 'ToyWorks');

Key Concepts Explained: - Composite Primary Key: (pname, manufacturer) together uniquely identify each row - Foreign Key: manufacturer must exist in the company table - Referential Integrity: You cannot insert a product with a non-existent manufacturer

The Challenge

Task: Find all categories of products that are made by Chinese companies.

Step-by-step approach:

1. First, explore the data: ```sql -- See all companies SELECT * FROM company;

-- See all products
SELECT * FROM product;

-- See which companies are Chinese SELECT * FROM company WHERE country = 'China'; ```

1. Your turn: Write the query to find categories made by Chinese companies: ```sql -- Write your solution here

```

SELECT DISTINCT p.category
FROM product p
JOIN company c ON p.manufacturer = c.cname
WHERE c.country = 'China';

SELECT DISTINCT category
FROM product, company
WHERE manufacturer = cname 
  AND country = 'China';

Bonus Challenge

Task: Find the names of all products made by companies with stock price > $20.

-- Write your solution here

SELECT p.pname
FROM product p
JOIN company c ON p.manufacturer = c.cname
WHERE c.stockprice > 20;

🎓 Summary

Congratulations! You've completed Lab 1. You should now be comfortable with:

✅ Database Operations: - Creating and using databases - Understanding database vs. table concepts

✅ Table Management: - Creating tables with appropriate data types - Understanding primary keys and constraints - Using foreign keys for referential integrity

✅ Basic Queries: - SELECT statements with filtering (WHERE) - Using LIKE for pattern matching - Sorting results with ORDER BY - Removing duplicates with DISTINCT

✅ Multi-table Concepts: - Understanding table relationships - Basic JOIN operations - Foreign key constraints

🔗 Additional Resources

Essential References

• MySQL Data Types: Official Documentation
• Learn about VARCHAR, DECIMAL, INT, DATE, and other data types
• CREATE TABLE Syntax: MySQL Reference
• Complete guide to table creation options
• INSERT Statement: MariaDB Guide
• Various ways to insert data efficiently

Practice Resources

• SQLZoo: Interactive SQL Tutorial
• Practice SELECT statements with immediate feedback
• W3Schools SQL: Comprehensive Tutorial
• Step-by-step learning with examples

Next Steps

Ready for more advanced topics? Continue with: - Lab 2: Advanced queries, joins, and aggregations - Foreign Keys Lab: Deep dive into referential integrity - Multi-table Queries: Complex JOIN operations

🐛 Troubleshooting

Common Issues and Solutions:

1. "Database doesn't exist" error: sql -- Make sure you created AND selected the DATABASE CREATE DATABASE lab1_exercises; USE lab1_exercises;

2. "Table already exists" error: sql -- DROP the TABLE first, THEN recreate DROP TABLE IF EXISTS table_name;

3. Foreign key constraint fails:

4. Ensure the parent table exists before creating child table

5. Check that referenced values exist in parent table

6. Syntax errors:

7. Check for missing semicolons
8. Verify proper quotation marks around strings
9. Ensure correct spelling of keywords

Need help? Check the main README for additional resources and support options.