one office of Ambo University Woliso Campus such as:- Dormitory . 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. Create database and tables for the selected office:
CREATE DATABASE AmboUniversity;
USE AmboUniversity;
CREATE TABLE Dormitory (
dormitory_id INT PRIMARY KEY,
dormitory_name VARCHAR(50),
capacity INT,
gender VARCHAR(10)
);
2. Insert sample data into each table:
INSERT INTO Dormitory (dormitory_id, dormitory_name, capacity, gender) VALUES
(1, 'Dorm 1', 50, 'Male'),
(2, 'Dorm 2', 60, 'Female'),
(3, 'Dorm 3', 70, 'Mixed');
3. Perform different database operations using SQL:
- Delete data:
DELETE FROM Dormitory WHERE dormitory_id = 3;
- Update data:
UPDATE Dormitory SET capacity = 80 WHERE dormitory_id = 2;
- Retrieve data:
SELECT * FROM Dormitory;
- Remove table:
DROP TABLE IF EXISTS Dormitory;
- Inserting new data:
INSERT INTO Dormitory (dormitory_id, dormitory_name, capacity, gender) VALUES
(4, 'Dorm 4', 55, 'Mixed');
4. Perform different aggregate functions using SQL:
- Sum:
SELECT SUM(capacity) FROM Dormitory;
- Total:
SELECT COUNT(*) FROM Dormitory;
- Maximum:
SELECT MAX(capacity) FROM Dormitory;
- Minimum:
SELECT MIN(capacity) FROM Dormitory;
- Average:
SELECT AVG(capacity) FROM Dormitory;
To create a database and tables for an office of Ambo University Woliso Campus, such as Dormitory, you can use the following SQL code:
Step 1: Create the database
```sql
CREATE DATABASE university;
```
Step 2: Use the database
```sql
USE university;
```
Step 3: Create table for Dormitory
```sql
CREATE TABLE dormitory (
id INT PRIMARY KEY,
name VARCHAR(50),
capacity INT,
floor INT
);
```
Step 4: Insert sample data into the Dormitory table
```sql
INSERT INTO dormitory (id, name, capacity, floor)
VALUES (1, 'Building A', 100, 5),
(2, 'Building B', 80, 4),
(3, 'Building C', 120, 6);
```
(Note: Replace the sample data with actual data for your specific office)
Step 5: Delete data
```sql
DELETE FROM dormitory
WHERE id = 3;
```
Step 6: Update data
```sql
UPDATE dormitory
SET capacity = 90
WHERE id = 2;
```
Step 7: Retrieve data
```sql
SELECT * FROM dormitory;
```
Step 8: Remove table
```sql
DROP TABLE dormitory;
```
Step 9: Inserting new data
```sql
INSERT INTO dormitory (id, name, capacity, floor)
VALUES (4, 'Building D', 110, 7);
```
Step 10: Perform aggregate functions
Sum:
```sql
SELECT SUM(capacity) AS total_capacity FROM dormitory;
```
Total:
```sql
SELECT COUNT(*) AS total_dorms FROM dormitory;
```
Maximum:
```sql
SELECT MAX(capacity) AS max_capacity FROM dormitory;
```
Minimum:
```sql
SELECT MIN(capacity) AS min_capacity FROM dormitory;
```
Average:
```sql
SELECT AVG(capacity) AS avg_capacity FROM dormitory;
```
These are the basic SQL commands to create tables, insert data, perform CRUD operations, and use aggregate functions for the Dormitory office of Ambo University Woliso Campus. Modify the table and column names as per your specific requirements.