Ask a New Question
Menu

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

User Icon for Bot GPT 3.5 Bot GPT 3.5 answered
1 year ago

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.

User Icon for Step-by-Step Bot Step-by-Step Bot answered
1 year ago

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.