Coding using SQL for register's office
A.Create database and Tables for your selected office
B.Insert sample data into each table
C.Your have perform different database operations such as:- Delete data ,Update ,Retrieve, Remove table ,Inserting new data etc…using SQL
D.You have to perform different aggregate functions such as:-Sum , Total, Maximum, Minimum, Average etc…. using SQL
A. Creating Database and Tables for Register's Office:
```sql
CREATE DATABASE registers_office;
USE registers_office;
CREATE TABLE students (
student_id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100),
age INT,
class VARCHAR(100),
registration_date DATE
);
CREATE TABLE courses (
course_id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100),
instructor VARCHAR(100),
fee DECIMAL(10, 2)
);
CREATE TABLE registrations (
registration_id INT AUTO_INCREMENT PRIMARY KEY,
student_id INT,
course_id INT,
registration_date DATE,
FOREIGN KEY (student_id) REFERENCES students(student_id),
FOREIGN KEY (course_id) REFERENCES courses(course_id)
);
```
B. Inserting Sample Data:
```sql
INSERT INTO students (name, age, class, registration_date) VALUES
('John Doe', 18, '12th Grade', '2022-01-01'),
('Jane Smith', 17, '11th Grade', '2022-02-15');
INSERT INTO courses (name, instructor, fee) VALUES
('Mathematics', 'Mr. Johnson', 150.00),
('Science', 'Ms. Anderson', 200.00);
INSERT INTO registrations (student_id, course_id, registration_date) VALUES
(1, 1, '2022-03-01'),
(1, 2, '2022-03-05'),
(2, 1, '2022-03-02');
```
C. Performing Different Database Operations:
1. Update data:
```sql
UPDATE students SET age = 19 WHERE student_id = 1;
```
2. Retrieve data:
```sql
SELECT * FROM students;
```
3. Delete data:
```sql
DELETE FROM registrations WHERE registration_id = 3;
```
4. Insert new data:
```sql
INSERT INTO courses (name, instructor, fee) VALUES
('English Literature', 'Ms. Davis', 180.00);
```
5. Remove table:
```sql
DROP TABLE registrations;
```
D. Performing Different Aggregate Functions:
1. Sum:
```sql
SELECT SUM(fee) FROM courses;
```
2. Total:
```sql
SELECT COUNT(*) FROM students;
```
3. Maximum:
```sql
SELECT MAX(age) FROM students;
```
4. Minimum:
```sql
SELECT MIN(fee) FROM courses;
```
5. Average:
```sql
SELECT AVG(age) FROM students;
```
Sure, here are the step-by-step instructions for coding using SQL for a register's office:
A. Create database and tables for your selected office:
1. Start by creating a new database. Use the following SQL syntax:
```
CREATE DATABASE register_office;
```
2. Switch to the newly created database:
```
USE register_office;
```
3. Create the necessary tables for your register's office. Here's an example of creating a "students" table:
```
CREATE TABLE students (
student_id INT PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
age INT,
registration_date DATE
);
```
You can create other tables similarly based on your requirements.
B. Insert sample data into each table:
1. To insert data into the "students" table, use the following SQL syntax:
```
INSERT INTO students (student_id, first_name, last_name, age, registration_date)
VALUES (1, 'John', 'Doe', 20, '2021-01-01'),
(2, 'Jane', 'Smith', 22, '2021-02-05'),
(3, 'David', 'Johnson', 21, '2021-03-10');
```
Repeat this step for other tables by modifying the table name and values accordingly.
C. Perform different database operations using SQL:
1. Delete data: To delete data from a table, use the following SQL syntax:
```
DELETE FROM students WHERE student_id = 1;
```
2. Update data: To update data in a table, use the following SQL syntax:
```
UPDATE students SET age = 23 WHERE student_id = 2;
```
3. Retrieve data: To retrieve data from a table, use the following SQL syntax:
```
SELECT * FROM students;
```
4. Remove table: To remove a table, use the following SQL syntax:
```
DROP TABLE students;
```
5. Inserting new data: To insert new data into a table, use the following SQL syntax:
```
INSERT INTO students (student_id, first_name, last_name, age, registration_date)
VALUES (4, 'Sarah', 'Williams', 19, '2021-04-15');
```
Repeat this step for other tables by modifying the table name and values accordingly.
D. Perform different aggregate functions using SQL:
1. Sum: To find the sum of a column, use the following SQL syntax:
```
SELECT SUM(age) FROM students;
```
2. Total: To find the total count of rows in a table, use the following SQL syntax:
```
SELECT COUNT(*) FROM students;
```
3. Maximum: To find the maximum value in a column, use the following SQL syntax:
```
SELECT MAX(age) FROM students;
```
4. Minimum: To find the minimum value in a column, use the following SQL syntax:
```
SELECT MIN(age) FROM students;
```
5. Average: To find the average value in a column, use the following SQL syntax:
```
SELECT AVG(age) FROM students;
```
Remember to modify the table and column names based on your specific database structure.