Practical Exercise

Kedeisha November 23, 2023

This practical exercise is designed to solidify your understanding of SQL basics by hands-on application. You’ll create a simple database, add a table to it, insert data into the table, and then retrieve this data using SELECT statements.

Task 1: Creating a Database and Table

1. Create a New Database:

Start your MariaDB client and enter the following command to create a new database named ‘practiceDB’:

CREATE DATABASE practiceDB;

2. Select the Database:

Use the newly created database with the command:

USE practiceDB;

3. Create a Table:

Create a table named ’employees’ with the following command:

CREATE TABLE employees (
  id INT AUTO_INCREMENT PRIMARY KEY,
  firstName VARCHAR(50),
  lastName VARCHAR(50),
  role VARCHAR(100),
  department VARCHAR(50)
);

Task 2: Inserting Data

1. Insert Data into the Table:

Add some records to the ’employees’ table:

INSERT INTO employees (firstName, lastName, role, department) VALUES ('John', 'Doe', 'Analyst', 'Finance');
INSERT INTO employees (firstName, lastName, role, department) VALUES ('Jane', 'Smith', 'Manager', 'Marketing');
INSERT INTO employees (firstName, lastName, role, department) VALUES ('Emily', 'Jones', 'Developer', 'IT');

Task 3: Retrieving Data Using SELECT Statements

1. Retrieve All Data:

To view all the data in the ’employees’ table, use:

SELECT * FROM employees;

2. Retrieve Specific Columns:

If you only want to see names and roles, use:

SELECT firstName, lastName, role FROM employees;