one office of Ambo University Woliso Campus such as:- human resource office. Write using SQL coding.
1.Create database and Tables for your selected office
2.Insert sample data into each table
3.Your have perform different database operations such as:- Delete data ,Update ,Retrieve, Remove table ,Inserting new data etc…using SQL
4.You have to perform different aggregate functions such as:-Sum , Total, Maximum, Minimum, Average etc…. using SQL
Here is an example of how you can write SQL queries for the human resource office of Ambo University Woliso Campus:
1. Create database and tables:
```sql
CREATE DATABASE AmboUniversity;
USE AmboUniversity;
CREATE TABLE Employees (
emp_id INT PRIMARY KEY,
emp_name VARCHAR(100),
emp_department VARCHAR(100),
emp_salary DECIMAL(10,2)
);
CREATE TABLE JobOpenings (
job_id INT PRIMARY KEY,
job_title VARCHAR(100),
job_description VARCHAR(255)
);
```
2. Insert sample data:
```sql
INSERT INTO Employees (emp_id, emp_name, emp_department, emp_salary)
VALUES (1, 'John Doe', 'HR', 5000.00),
(2, 'Jane Smith', 'HR', 6000.00),
(3, 'David Johnson', 'HR', 5500.00);
INSERT INTO JobOpenings (job_id, job_title, job_description)
VALUES (1, 'HR Manager', 'Responsible for managing HR team'),
(2, 'HR Assistant', 'Assist in HR activities');
```
3. Database operations:
- Delete data:
```sql
DELETE FROM Employees WHERE emp_id = 3;
```
- Update data:
```sql
UPDATE Employees SET emp_salary = 6500.00 WHERE emp_id = 1;
```
- Retrieve data:
```sql
SELECT * FROM Employees WHERE emp_department = 'HR';
```
- Remove table:
```sql
DROP TABLE JobOpenings;
```
- Inserting new data:
```sql
INSERT INTO Employees (emp_id, emp_name, emp_department, emp_salary)
VALUES (4, 'Lisa Anderson', 'HR', 5200.00);
```
4. Aggregate functions:
- Sum:
```sql
SELECT SUM(emp_salary) AS total_salary FROM Employees;
```
- Total:
```sql
SELECT COUNT(emp_id) AS total_employees FROM Employees;
```
- Maximum:
```sql
SELECT MAX(emp_salary) AS max_salary FROM Employees;
```
- Minimum:
```sql
SELECT MIN(emp_salary) AS min_salary FROM Employees;
```
- Average:
```sql
SELECT AVG(emp_salary) AS avg_salary FROM Employees;
```
Note: The above SQL queries are just examples and may need modification based on the specific requirements and structure of your database.
To demonstrate the steps using SQL coding, first we need to create a database and tables for the Human Resource office of Ambo University Woliso Campus.
Step 1: Create Database and Tables
```
CREATE DATABASE AmboUniversity;
USE AmboUniversity;
CREATE TABLE Employees (
EmployeeID INT PRIMARY KEY,
FirstName VARCHAR(50),
LastName VARCHAR(50),
Position VARCHAR(50),
Salary DECIMAL(10,2)
);
CREATE TABLE Departments (
DepartmentID INT PRIMARY KEY,
DepartmentName VARCHAR(50)
);
```
Step 2: Insert Sample Data
```
INSERT INTO Employees (EmployeeID, FirstName, LastName, Position, Salary)
VALUES (1, 'John', 'Doe', 'HR Manager', 5000.00);
INSERT INTO Employees (EmployeeID, FirstName, LastName, Position, Salary)
VALUES (2, 'Jane', 'Smith', 'HR Coordinator', 3500.00);
INSERT INTO Departments (DepartmentID, DepartmentName)
VALUES (1, 'Human Resources');
```
Step 3: Perform Database Operations
Examples of different SQL operations:
- Delete Data:
```
DELETE FROM Employees WHERE EmployeeID = 1;
```
- Update Data:
```
UPDATE Employees SET Salary = 4500.00 WHERE EmployeeID = 2;
```
- Retrieve Data:
```
SELECT * FROM Employees WHERE Position = 'HR Coordinator';
```
- Remove Table:
```
DROP TABLE Departments;
```
- Insert New Data:
```
INSERT INTO Employees (EmployeeID, FirstName, LastName, Position, Salary)
VALUES (3, 'Mark', 'Johnson', 'HR Assistant', 3000.00);
```
Step 4: Perform Aggregate Functions
Examples of different SQL aggregate functions:
- Sum:
```
SELECT SUM(Salary) FROM Employees;
```
- Total:
```
SELECT COUNT(*) FROM Employees;
```
- Maximum:
```
SELECT MAX(Salary) FROM Employees;
```
- Minimum:
```
SELECT MIN(Salary) FROM Employees;
```
- Average:
```
SELECT AVG(Salary) FROM Employees;
```
These are just examples to showcase the steps using SQL coding. You can modify and extend these queries to suit your specific needs.