Lab Work

This lab guide covers practical exercises designed to strengthen your understanding of database management concepts through hands-on SQL implementation. Each exercise builds upon previous knowledge and introduces new SQL commands and concepts.

Setting Up Your Environment

Before starting the lab exercises, ensure you have:

1. A relational database management system installed (MySQL, PostgreSQL, SQLite, or SQL Server)
2. Access to a SQL command line interface or GUI client
3. Necessary permissions to create, modify, and delete database objects

Exercise 1: Creating Tables with DDL

Objective: Create a table to store employee information with specific columns.

CREATE TABLE employee (
    employee_id INT,
    name VARCHAR(100),
    department VARCHAR(50),
    salary DECIMAL(10, 2)
);

Description: This command creates a new table named ‘employee’ with four columns:

• employee_id: A numeric identifier for each employee
• name: Employee’s full name (up to 100 characters)
• department: Employee’s department (up to 50 characters)
• salary: Employee’s salary with 2 decimal places

Expected Output:

Table 'employee' created successfully.

Exercise 2: Altering Table Structure

Objective: Add a new column to the employee table.

ALTER TABLE employee
ADD COLUMN email VARCHAR(100);

Description: This command adds a new ‘email’ column to the existing employee table.

Expected Output:

Table 'employee' altered successfully.

Exercise 3: Dropping Tables

Objective: Learn how to remove a table from the database.

-- First, create a sample table to drop
CREATE TABLE customer (
    customer_id INT,
    customer_name VARCHAR(100),
    contact VARCHAR(50)
);

-- Then drop the table
DROP TABLE customer;

Description: These commands create a ‘customer’ table and then completely remove it from the database.

Expected Output:

Table 'customer' created successfully.
Table 'customer' dropped successfully.

Exercise 4: Modifying Column Data Types

Objective: Change the data type of a column in an existing table.

-- First, create an order table with an INTEGER price column
CREATE TABLE orders (
    order_id INT,
    customer_id INT,
    product_id INT,
    quantity INT,
    price INTEGER
);

-- Then modify the price column to DECIMAL
ALTER TABLE orders
MODIFY COLUMN price DECIMAL(10, 2);

Note: The syntax for modifying columns varies slightly between different database systems:

• For MySQL: ALTER TABLE orders MODIFY COLUMN price DECIMAL(10, 2);
• For PostgreSQL: ALTER TABLE orders ALTER COLUMN price TYPE DECIMAL(10, 2);
• For SQL Server: ALTER TABLE orders ALTER COLUMN price DECIMAL(10, 2);

Expected Output:

Table 'orders' created successfully.
Table 'orders' altered successfully.

Exercise 5: Creating Primary Keys

Objective: Add a primary key constraint to the employee table.

ALTER TABLE employee
ADD PRIMARY KEY (employee_id);

Description: This command establishes the employee_id column as the primary key for the employee table, ensuring each value is unique and not null.

Expected Output:

Table 'employee' altered successfully.

Exercise 6: Creating Foreign Keys

Objective: Create a foreign key relationship between tables.

-- Ensure customer table exists with primary key
CREATE TABLE customer (
    customer_id INT PRIMARY KEY,
    customer_name VARCHAR(100),
    contact VARCHAR(50)
);

-- Add foreign key to orders table
ALTER TABLE orders
ADD CONSTRAINT fk_customer
FOREIGN KEY (customer_id) REFERENCES customer(customer_id);

Description: This establishes a relationship between the orders table and the customer table, ensuring that every customer_id in the orders table must exist in the customer table.

Expected Output:

Table 'customer' created successfully.
Table 'orders' altered successfully.

Exercise 7: Creating Unique Constraints

Objective: Add a unique constraint to ensure values in a column are unique.

-- Create product table
CREATE TABLE product (
    product_id INT,
    product_name VARCHAR(100),
    price DECIMAL(10, 2)
);

-- Add unique constraint
ALTER TABLE product
ADD CONSTRAINT unique_product_id UNIQUE (product_id);

Description: This ensures that every value in the product_id column must be unique across all records.

Expected Output:

Table 'product' created successfully.
Table 'product' altered successfully.

Exercise 8: Inserting Records

Objective: Add new records to the employee table.

-- Insert a single employee
INSERT INTO employee (employee_id, name, department, salary, email)
VALUES (100, 'John Doe', 'IT', 50000, 'john.doe@example.com');

-- Insert multiple employees
INSERT INTO employee (employee_id, name, department, salary, email)
VALUES 
    (101, 'Jane Smith', 'HR', 55000, 'jane.smith@example.com'),
    (102, 'Michael Johnson', 'Sales', 60000, 'michael.johnson@example.com'),
    (103, 'Emily Brown', 'Marketing', 52000, 'emily.brown@example.com'),
    (104, 'Robert Wilson', 'IT', 65000, 'robert.wilson@example.com');

Description: These commands add one employee in the first statement and four more employees in the second statement.

Expected Output:

1 row(s) inserted.
4 row(s) inserted.

Exercise 9: Updating Records

Objective: Modify existing records in the database.

UPDATE employee
SET salary = 60000
WHERE employee_id = 100;

Description: This command increases the salary of the employee with ID 100 from 50000 to 60000.

Expected Output:

1 row(s) updated.

Exercise 10: Deleting Records

Objective: Remove records from a table.

DELETE FROM employee
WHERE employee_id = 100;

Description: This command removes the employee with ID 100 from the employee table.

Expected Output:

1 row(s) deleted.

Exercise 11: Basic SELECT Queries with Filtering

Objective: Retrieve specific records based on criteria.

SELECT * 
FROM employee
WHERE department = 'IT';

Description: This command retrieves all columns for employees who work in the IT department.

Expected Output:

employee_id | name           | department | salary  | email
---------------------------------------------------------------------------
104         | Robert Wilson  | IT         | 65000.00| robert.wilson@example.com

Exercise 12: Aggregate Functions

Objective: Use SQL functions to perform calculations on data.

SELECT 
    department,
    AVG(salary) AS average_salary
FROM employee
WHERE department = 'Sales'
GROUP BY department;

Description: This command calculates the average salary of employees in the Sales department.

Expected Output:

department | average_salary
---------------------------
Sales      | 60000.00

Exercise 13: Finding Maximum Values

Objective: Identify the record with the highest value in a specific column.

SELECT *
FROM employee
WHERE salary = (SELECT MAX(salary) FROM employee);

Description: This command finds the employee(s) with the highest salary in the company.

Expected Output:

employee_id | name           | department | salary  | email
---------------------------------------------------------------------------
104         | Robert Wilson  | IT         | 65000.00| robert.wilson@example.com

Exercise 14: Inserting Related Records

Objective: Insert records that have relationships with existing data.

-- First add some products
INSERT INTO product (product_id, product_name, price)
VALUES 
    (1, 'Laptop', 1200.00),
    (2, 'Smartphone', 800.00),
    (3, 'Tablet', 500.00);

-- Then insert an order
INSERT INTO orders (order_id, customer_id, product_id, quantity, price)
VALUES (100, 101, 1, 1, 1200.00);

Description: These commands add three products and then create an order for one laptop by customer 101.

Expected Output:

3 row(s) inserted.
1 row(s) inserted.

Exercise 15: Updating Related Records

Objective: Modify records with relationships to other tables.

UPDATE orders
SET quantity = 2
WHERE order_id = 100;

Description: This command changes the quantity of the order with ID 100 from 1 to 2.

Expected Output:

1 row(s) updated.

Exercise 16: Deleting Related Records

Objective: Remove records while maintaining referential integrity.

DELETE FROM orders
WHERE order_id = 100;

Description: This command removes the order with ID 100.

Expected Output:

1 row(s) deleted.

Exercise 17: Filtering with Multiple Conditions

Objective: Retrieve data based on complex criteria.

-- Insert more orders first
INSERT INTO orders (order_id, customer_id, product_id, quantity, price)
VALUES 
    (101, 101, 2, 1, 800.00),
    (102, 102, 3, 2, 1000.00),
    (103, 101, 1, 1, 1200.00);

-- Then query with filter
SELECT *
FROM orders
WHERE customer_id = 101;

Description: This command retrieves all orders placed by customer 101.

Expected Output:

order_id | customer_id | product_id | quantity | price
------------------------------------------------------
101      | 101         | 2          | 1        | 800.00
103      | 101         | 1          | 1        | 1200.00

Exercise 18: Basic JOIN Operations

Objective: Combine data from multiple tables.

SELECT 
    e.name AS employee_name,
    o.order_id,
    o.product_id,
    o.quantity,
    o.price
FROM 
    employee e
INNER JOIN 
    orders o ON e.employee_id = o.customer_id;

Description: This joins the employee and orders tables to show which employees have placed orders. (For this example, we’re treating employee_id as the same as customer_id.)

Expected Output (will vary based on data):

employee_name     | order_id | product_id | quantity | price
----------------------------------------------------------
Jane Smith        | 101      | 2          | 1        | 800.00
Jane Smith        | 103      | 1          | 1        | 1200.00
Michael Johnson   | 102      | 3          | 2        | 1000.00

Exercise 19: Subqueries

Objective: Use a query within another query for complex filtering.

-- Insert a high-priced product and an order for it
INSERT INTO product (product_id, product_name, price)
VALUES (4, 'Gaming PC', 2500.00);

INSERT INTO orders (order_id, customer_id, product_id, quantity, price)
VALUES (104, 103, 4, 1, 2500.00);

-- Query using subquery
SELECT 
    e.* 
FROM 
    employee e
WHERE 
    e.employee_id IN (
        SELECT 
            o.customer_id 
        FROM 
            orders o
        INNER JOIN 
            product p ON o.product_id = p.product_id
        WHERE 
            p.price > 1000
    );

Description: This finds all employees who have placed an order for a product with a price greater than 1000.

Expected Output:

employee_id | name           | department | salary  | email
---------------------------------------------------------------------------
101         | Jane Smith     | HR         | 55000.00| jane.smith@example.com
103         | Emily Brown    | Marketing  | 52000.00| emily.brown@example.com

Exercise 20: Creating Views

Objective: Create a virtual table that simplifies complex queries.

CREATE VIEW it_high_earners AS
SELECT 
    name,
    salary
FROM 
    employee
WHERE 
    department = 'IT' 
    AND salary > 50000;

Description: This creates a view that shows only IT employees who earn more than 50000.

Expected Output:

View 'it_high_earners' created successfully.

To query the view:

SELECT * FROM it_high_earners;

Expected Output:

name           | salary
-------------------------
Robert Wilson  | 65000.00

Exercise 21: Creating Stored Procedures

Objective: Write a procedure that can be called to perform a specific task.

-- MySQL syntax
DELIMITER //
CREATE PROCEDURE insert_order(
    IN p_order_id INT,
    IN p_customer_id INT,
    IN p_product_id INT,
    IN p_quantity INT,
    IN p_price DECIMAL(10,2)
)
BEGIN
    INSERT INTO orders (order_id, customer_id, product_id, quantity, price)
    VALUES (p_order_id, p_customer_id, p_product_id, p_quantity, p_price);
END //
DELIMITER ;

Note: The syntax for stored procedures varies significantly between database systems. The above example is for MySQL. For other systems, consult your database documentation.

To call the procedure:

CALL insert_order(105, 102, 2, 3, 2400.00);

Expected Output:

Procedure executed successfully.
Query OK, 1 row affected

Exercise 22: Creating Functions

Objective: Create a reusable function that performs calculations.

-- MySQL syntax
DELIMITER //
CREATE FUNCTION calculate_total_price(
    p_order_id INT
) 
RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN
    DECLARE total DECIMAL(10,2);
    
    SELECT (quantity * price) INTO total
    FROM orders
    WHERE order_id = p_order_id;
    
    RETURN total;
END //
DELIMITER ;

To use the function:

SELECT 
    order_id, 
    calculate_total_price(order_id) AS total_price
FROM 
    orders;

Expected Output:

order_id | total_price
----------------------
101      | 800.00
102      | 2000.00
103      | 1200.00
104      | 2500.00
105      | 7200.00

Exercise 23: Managing Privileges - GRANT

Objective: Control user access to database objects.

GRANT SELECT 
ON employee 
TO 'public';

Description: This grants SELECT privilege on the employee table to the ‘public’ user or role.

Expected Output:

Query OK, 0 rows affected

Exercise 24: Granting Specific Privileges

Objective: Provide limited access based on roles.

GRANT INSERT 
ON orders 
TO 'sales';

Description: This grants INSERT privilege on the orders table to the ‘sales’ user or role.

Expected Output:

Query OK, 0 rows affected

Exercise 25: Revoking Privileges

Objective: Remove previously granted permissions.

REVOKE UPDATE 
ON customer 
FROM 'marketing';

Description: This removes UPDATE privilege on the customer table from the ‘marketing’ user or role.

Expected Output:

Query OK, 0 rows affected

Exercise 26: Creating Roles

Objective: Create a role with a set of privileges.

-- MySQL 8.0+ syntax
CREATE ROLE 'manager';

GRANT SELECT, INSERT, UPDATE, DELETE
ON *.* 
TO 'manager';

Description: This creates a ‘manager’ role and grants it SELECT, INSERT, UPDATE, and DELETE privileges on all tables.

Expected Output:

Query OK, 0 rows affected

Exercise 27: Assigning Roles to Users

Objective: Grant a role to a specific user.

-- MySQL 8.0+ syntax
GRANT 'manager' 
TO 'admin'@'localhost';

Description: This assigns the ‘manager’ role to the ‘admin’ user.

Expected Output:

Query OK, 0 rows affected

Conclusion

This lab guide covers a wide range of SQL commands and concepts, from basic table creation to advanced privilege management. By completing these exercises, you’ll gain practical experience with database operations that reinforce the theoretical concepts taught in the DBMS course.

Additional Practice:

1. Try combining multiple concepts into more complex queries
2. Create a complete database schema for a real-world scenario
3. Practice optimizing your queries for better performance
4. Learn how to backup and restore your database

Remember that database practices may vary slightly between different database management systems, so always consult your specific database documentation when needed.