one office of Ambo University Woliso Campus such as:- Dormitory . Write using SQL Coding.

Question

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

Answer 1

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;