Step 1: Start MySQL
First, log in to the MySQL server.
mysql -u root -p
Enter the root password to access the MySQL prompt.
Step 2: Create a Database
Create a new database named company_db.
CREATE DATABASE company_db;
Switch to the newly created database:
USE company_db;
Step 3: Create Tables
Create two tables: employees and departments.
1. employees Table: Contains details of employees.
CREATE TABLE employees (
employee_id INT AUTO_INCREMENT PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
department_id INT,
salary DECIMAL(10, 2)
);
2. departments Table: Contains department details.
CREATE TABLE departments (
department_id INT AUTO_INCREMENT PRIMARY KEY,
department_name VARCHAR(100)
);
Step 4: Insert Data into Tables
1. Insert data into the departments table:
INSERT INTO departments (department_name)
VALUES
('Human Resources'),
('Finance'),
('Engineering'),
('Marketing');
2. Insert data into the employees table:
sql
CopyEdit
INSERT INTO employees (first_name, last_name, department_id, salary)
VALUES
('Alice', 'Johnson', 1, 50000.00),
('Bob', 'Smith', 2, 70000.00),
('Charlie', 'Brown', 3, 90000.00),
('Diana', 'White', 4, 60000.00);
Step 5: Query the Data
1. View all records from the employees table:
SELECT * FROM employees;
2. View all records from the departments table:
SELECT * FROM departments;
3. Join employees and departments tables to view employee details along with
department names:
SELECT
e.employee_id,
e.first_name,
e.last_name,
d.department_name,
e.salary
FROM
employees e
INNER JOIN
departments d
ON
e.department_id = d.department_id;
4.
5.
6. Filter employees earning more than $60,000:
SELECT * FROM employees WHERE salary > 60000.00;
Step 6: Update Data
1. Increase the salary of all employees in the 'Engineering' department by 10%:
UPDATE employees
SET salary = salary * 1.10
WHERE department_id = (
SELECT department_id FROM departments WHERE department_name =
'Engineering'
);
Step 7: Delete Data
1. Delete an employee with the last name 'White':
DELETE FROM employees WHERE last_name = 'White';
2. Delete the entire 'Marketing' department:
o First, delete employees in the 'Marketing' department:
DELETE FROM employees WHERE department_id = (
SELECT department_id FROM departments WHERE department_name =
'Marketing'
);
o Then, delete the department:
DELETE FROM departments WHERE department_name = 'Marketing';
Step 8: Drop Tables or Database (Optional)
1. Drop a table:
DROP TABLE employees;
2. Drop the database:
DROP DATABASE company_db;
