Practice Exercise: Introduction to MySQL with Simple Queries

Objectives

To learn basic MySQL commands and perform simple database operations on your machine.

Prerequisites

A machine with MySQL installed.

Exercise

Step 1. Access your machine (a1t-inf-ds-db1) by following the instructions in Lab Environment,

Step 2. Access MySQL

Access MySQL using the MySQL command-line client:

mysql -u root -p
You will be prompted to enter your MySQL password. Use the same password when you access your instance.

Step 3. Create a Database

Create a new database named academy_db;:
```
CREATE DATABASE academy_db;
```

Step 4. Use the Database - Switch to the newly created database:

    USE academy_db;

Step 5. Create a Table

Create a table named "students" with the following columns:
- id (INT, AUTO_INCREMENT, PRIMARY KEY)
- first_name (VARCHAR)
- last_name (VARCHAR)
- age (INT)
```
CREATE TABLE students (
  id INT AUTO_INCREMENT PRIMARY KEY,
  first_name VARCHAR(50),
  last_name VARCHAR(50),
  age INT
);
```

Step 6. Insert Data - Insert sample data into the "students" table:

    INSERT INTO students (first_name, last_name, age)
    VALUES
        ('John', 'Doe', 25),
        ('Jane', 'Smith', 22),
        ('Bob', 'Johnson', 28);

Step 7. Retrieve Data

Perform the following queries:

a. Retrieve all students from the "students" table:

  SELECT * FROM students;

b. Retrieve the students whose age is greater than 25:

  SELECT * FROM students WHERE age > 25;

Step 8. Update Data - Update the age of 'Jane Smith' to 23:

  UPDATE students
  SET age = 23
  WHERE first_name = 'Jane' AND last_name = 'Smith';

Step 9. Delete Data - Delete the student 'Bob Johnson' from the table:

  DELETE FROM students
  WHERE first_name = 'Bob' AND last_name = 'Johnson';

Conclusion

This lab exercise will help you get started with MySQL and practice simple database operations.