Creating and Managing Coach and Activity Tables in SQL

1. Create Table Coach

CoachID INT PRIMARY KEY,

CoachName VARCHAR(20) NOT NULL,

Sports VARCHAR(15) NOT NULL,

DOJ DATE NOT NULL,

Gender CHAR(1) NOT NULL,

Salary INT NOT NULL

INSERT INTO Coach (CoachID, CoachName, Sports, DOJ, Gender, Salary)

VALUES

(1001, 'Mulund Gogoi', 'Boxing', '2004-09-01', 'M', 40000),

(1002, 'Arnab Duwara', 'Karate', '1999-10-01', 'M', 60000),

(1003, 'Nayan Bora', 'Arm Wrestling', '2005-02-28', 'M', 50000),

(1004, 'Devesh Hazarika', 'Basketball', '2010-05-05', 'M', 25000),

(1005, 'Chetan Sharma', 'Swimming', '2010-03-31', 'M', 25000),

(1006, 'Lakshmi Devi', 'Boxing', '2012-01-01', 'F', 22000);

3. Queries for Questions:

a) Display the Maximum and Minimum Salary of the Coaches

SELECT MAX(Salary) AS Max_Salary, MIN(Salary) AS Min_Salary FROM Coach;

b) Display Number of Male and Female Coaches

SELECT Gender, COUNT(*) AS Number_of_Coaches

FROM Coach

GROUP BY Gender;

c) Display Details of All Coaches Having Names Ending with 'I'

SELECT * FROM Coach

WHERE CoachName LIKE '%I';

d) Display Details of the Coaches Whose DOJ is in the Range of '1995-01-01' to '2005-01-01'

SELECT * FROM Coach

WHERE DOJ BETWEEN '1995-01-01' AND '2005-01-01';

2. Here is the Solution for the Given Question:

1. Create Table ACTIVITY

CREATE TABLE ACTIVITY ( ACT_Code VARCHAR(10) PRIMARY KEY, ACT_Name VARCHAR(50), Place VARCHAR(50), Schedule_Date DATE, Participants INT );

2. Insert Data into the Table

INSERT INTO ACTIVITY VALUES ('A001', 'High Jump', 'Stadium 1', '2022-12-01', 12), ('A002', 'Long Jump', 'Stadium 1', '2022-12-01', 15), ('A003', 'Shot Put', 'Stadium 2', '2022-12-02', 10), ('A004', 'Discus Throw', 'Stadium 2', '2022-12-02', 14), ('A005', 'Football', 'Stadium 3', '2022-12-03', 20);

Queries for Part (b):

(i) Display the Details of Activities in Ascending Order of Schedule Date

SELECT * FROM ACTIVITY ORDER BY Schedule_Date ASC;

(ii) Display the Various Places Given in the Table

SELECT DISTINCT Place FROM ACTIVITY;

(iii) Display the Details of ACT_Code A001 and A002

SELECT * FROM ACTIVITY WHERE ACT_Code IN ('A001', 'A002');

(iv) Display the ACT_Name that has Participants in the Range 10 to 15

SELECT ACT_Name FROM ACTIVITY WHERE Participants BETWEEN 10 AND 15;