I apologize, but as an AI language model running on the ChatGPT Android app, I don't have access to the file system to create and save databases. However, I can provide you with the SQL queries to create the database and table, and also help you with the other queries. Please follow the steps below to execute the SQL queries:
1. Create a Database called SQL Examination:
```sql
CREATE DATABASE `SQL Examination`;
```
2. Create a Table called Student and populate it with the given data:
```sql
USE `SQL Examination`;
CREATE TABLE Student (
`Reg No` INT PRIMARY KEY,
`First Name` TEXT,
`Last Name` TEXT,
`Birth Date` DATE,
`Gender` TEXT,
`Salary` NUMBER,
`Course` TEXT
);
INSERT INTO Student (`Reg No`, `First Name`, `Last Name`, `Birth Date`, `Gender`, `Salary`, `Course`)
VALUES
(100, 'David', 'Wallace', '1967-11-17', 'M', 250000, 'ADIT'),
(101, 'Jan', 'Levison', '1961-5-11', 'F', 110000, 'ADIT'),
(102, 'Michael', 'Scott', '1964-3-15', 'M', 75000, 'DITA'),
(103, 'Angela', 'Martin', '1971-6-25', 'F', 63000, 'DITA'),
(104, 'Kelly', 'Kapoor', '1980-02-05', 'F', 55000, 'CDL'),
(105, 'Stanley', 'Hudson', '1958-02-19', 'M', 69000, 'DITA'),
(106, 'Josh', 'Porter', '1969-09-05', 'M', 78000, 'DIT'),
(107, 'Andy', 'Bernard', '1973-07-22', 'M', 65000, 'DIT'),
(108, 'Jim', 'Halpert', '1978-10-01', 'M', 71000, 'CDL');
```
3. Add a column called Phone_No and update it accordingly:
```sql
ALTER TABLE Student ADD COLUMN `Phone_No` TEXT UNIQUE;
-- Assuming you have a list of phone numbers corresponding to each student, you can update them like this:
UPDATE Student SET `Phone_No` = 'phone_number' WHERE `Reg No` = 100;
-- Repeat the above statement for each student, updating the `phone_number` accordingly.
```
4. Display all male students' records:
```sql
SELECT * FROM Student WHERE `Gender` = 'M';
```
5. Display all female students' records:
```sql
SELECT * FROM Student WHERE `Gender` = 'F';
```
6. Display all records of CDL students:
```sql
SELECT * FROM Student WHERE `Course` = 'CDL';
```
7. Display Reg No, Gender, and salary of all DITA students:
```sql
SELECT `Reg No`, `Gender`, `Salary` FROM Student WHERE `Course` = 'DITA';
```
8. Display the total salary of all students:
```sql
SELECT SUM(`Salary`) AS `Total Salary` FROM Student;
```
9. Display the highest salary in the student table:
```sql
SELECT MAX(`Salary`) AS `Highest Salary` FROM Student;
```
10. Display the Average salary of all students:
```sql
SELECT AVG(`Salary`) AS `Average Salary` FROM Student;
```
Please note that you will need a database management system like MySQL or SQLite to execute these SQL queries.
Post a Comment
0Comments