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.
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
1 answer
1 answer