Lab Activity: SQL Commands

Set up XAMPP and access MySQL/MariaDB:

1. Install XAMPP: Download from Apache Friends and run the installer with default settings.
2. Launch Services: Open XAMPP Control Panel. Click Start for both Apache and MySQL. Wait for them to turn green.
3. Open Terminal: Launch CMD (Windows) or Terminal (Mac/Linux) and navigate to MySQL bin:

   cd C:\xampp\mysql\bin

4. Login to MySQL: Connect as root user (no password):

   mysql -u root

   You should see MariaDB [(none)]> or mysql> prompt.

What is SQL?

SQL (Structured Query Language) is a standard language for managing relational databases. It allows you to create, modify, and query data stored in database systems like MySQL, PostgreSQL, and SQL Server.

Key SQL Command Categories:

• DDL (Data Definition Language): CREATE, ALTER, DROP - Used to define database structure
• DML (Data Manipulation Language): INSERT, SELECT, UPDATE, DELETE - Used to manipulate data
• DCL (Data Control Language): GRANT, REVOKE - Used to control access permissions

Common Mistakes to Avoid:

• Forgetting semicolons (;) at the end of SQL statements
• Using UPDATE or DELETE without a WHERE clause (affects all rows!)
• Mixing single and double quotes inconsistently
• Not checking table structure before inserting data

Best Practices:

• Always use uppercase for SQL keywords (SELECT, FROM, WHERE) for readability
• Use descriptive table and column names
• Test SELECT queries before running UPDATE/DELETE operations
• Use transactions for critical operations when available

Step 1.1: Create database named LibraryDB.

CREATE DATABASE LibraryDB;

Step 1.2: Verify and select database.

SHOW DATABASES;
USE LibraryDB;

Step 1.3: Create Books table with columns for book information.

CREATE TABLE Books (
    BookID int,
    Title varchar(255),
    Author varchar(255),
    PublishedYear int
);

Step 2.1: Insert data into the Books table:

INSERT INTO Books (BookID, Title, Author, PublishedYear)
VALUES (1, 'The Great Gatsby', 'F. Scott Fitzgerald', 1925);

Step 2.2: Read data from the table:

Query all data:

SELECT * FROM Books;

Query specific columns:

SELECT Title, Author FROM Books;

Step 2.3: Update data using WHERE clause:

UPDATE Books SET PublishedYear = 1926 WHERE BookID = 1;

Step 2.4: Delete data using WHERE clause:

DELETE FROM Books WHERE BookID = 1;

Step 3.1: Modify table structure by adding a Genre column:

ALTER TABLE Books ADD Genre varchar(100);

Step 3.2: Create index to speed up title searches:

CREATE INDEX idx_title ON Books(Title);

Step 3.3: Clean up by removing objects:

Drop index:

DROP INDEX idx_title ON Books;

Drop table:

DROP TABLE Books;

Drop database:

DROP DATABASE LibraryDB;

What to Expect in the Assessment:

• You will complete 10 sequential tasks testing your SQL command knowledge
• Tasks include writing SQL commands for database creation, table operations, and CRUD operations
• You'll need to apply concepts from all three parts of this lab guide
• The assessment tracks your completion time and progress

Key Points to Remember:

• Always end SQL statements with semicolons
• Use WHERE clauses carefully in UPDATE and DELETE commands
• Remember the difference between DROP (removes structure) and DELETE (removes data)
• Practice the challenge tasks before attempting the assessment

Practice Recommendations:

• Complete all steps in Parts 1, 2, and 3 at least once
• Try the Challenge section tasks without looking at solutions first
• Review the Concept Overview section if you're unsure about any concept