one office of Ambo University Woliso Campus such as:- register 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. Creating the database and tables:
```sql
CREATE DATABASE AmboUniversity;
USE AmboUniversity;
CREATE TABLE RegisterOffice (
student_id INT PRIMARY KEY,
student_name VARCHAR(50),
department VARCHAR(50),
year_of_study INT,
registration_date DATE
);
```
2. Inserting sample data:
```sql
INSERT INTO RegisterOffice (student_id, student_name, department, year_of_study, registration_date)
VALUES (1, 'John Doe', 'Computer Science', 2, '2020-09-01');
INSERT INTO RegisterOffice (student_id, student_name, department, year_of_study, registration_date)
VALUES (2, 'Jane Smith', 'Business Administration', 1, '2021-02-15');
INSERT INTO RegisterOffice (student_id, student_name, department, year_of_study, registration_date)
VALUES (3, 'David Johnson', 'Electrical Engineering', 3, '2019-10-05');
```
3. Performing various database operations:
- Retrieving data:
```sql
SELECT * FROM RegisterOffice;
```
- Updating data:
```sql
UPDATE RegisterOffice
SET year_of_study = 4
WHERE student_id = 2;
```
- Deleting data:
```sql
DELETE FROM RegisterOffice
WHERE student_id = 3;
```
- Dropping table:
```sql
DROP TABLE RegisterOffice;
```
- Inserting new data:
```sql
INSERT INTO RegisterOffice (student_id, student_name, department, year_of_study, registration_date)
VALUES (4, 'Mary Johnson', 'Psychology', 2, '2020-11-30');
```
4. Performing different aggregate functions:
- Sum:
```sql
SELECT SUM(year_of_study) FROM RegisterOffice;
```
- Total:
```sql
SELECT COUNT(*) FROM RegisterOffice;
```
- Maximum:
```sql
SELECT MAX(year_of_study) FROM RegisterOffice;
```
- Minimum:
```sql
SELECT MIN(year_of_study) FROM RegisterOffice;
```
- Average:
```sql
SELECT AVG(year_of_study) FROM RegisterOffice;
```
1. To create a database and tables for the register office of Ambo University Woliso Campus using SQL, follow these steps:
- Create the database:
CREATE DATABASE ambo_university;
- Use the database:
USE ambo_university;
- Create the tables:
CREATE TABLE students (
student_id INT PRIMARY KEY,
student_name VARCHAR(100),
student_email VARCHAR(100)
);
CREATE TABLE courses (
course_id INT PRIMARY KEY,
course_name VARCHAR(100),
course_credits INT
);
2. To insert sample data into each table, use the following SQL statements:
INSERT INTO students (student_id, student_name, student_email)
VALUES (1, 'John Doe', 'john.doe');
INSERT INTO students (student_id, student_name, student_email)
VALUES (2, 'Jane Smith', 'jane.smith');
INSERT INTO courses (course_id, course_name, course_credits)
VALUES (1, 'Mathematics', 4);
INSERT INTO courses (course_id, course_name, course_credits)
VALUES (2, 'Computer Science', 3);
3. To perform different database operations using SQL, you can use the following statements as examples:
- Delete data:
DELETE FROM students WHERE student_id = 2;
- Update data:
UPDATE students SET student_name = 'Jane Doe' WHERE student_id = 1;
- Retrieve data:
SELECT * FROM students;
- Remove table:
DROP TABLE courses;
- Inserting new data:
INSERT INTO students (student_id, student_name, student_email)
VALUES (3, 'Mark Johnson', 'mark.johnson');
4. To perform different aggregate functions using SQL, you can use the following statements as examples:
- Sum:
SELECT SUM(course_credits) FROM courses;
- Total:
SELECT COUNT(*) FROM students;
- Maximum:
SELECT MAX(course_credits) FROM courses;
- Minimum:
SELECT MIN(course_credits) FROM courses;
- Average:
SELECT AVG(course_credits) FROM courses;