Q.Write SQL queries for

SQL (Structured Query Language) is fundamental for managing and querying data in relational databases. These questions test your ability to retrieve specific information, filter records, perform aggregations, and combine data from multiple tables using various SQL commands.

Here are the SQL queries and outputs:

1. To display details of all Trains which Start from New Delhi. To retrieve all columns for trains originating from 'New Delhi', we select all fields from the TRAINS table and apply a WHERE clause to filter by the START column.

   SELECT * FROM TRAINS WHERE START = 'New Delhi';

2. To display the PNR, PNAME, GENDER and AGE of all Passengers whose AGE is below 50. This query selects specific columns (PNR, PNAME, GENDER, AGE) from the PASSENGERS table, filtering records where the AGE is less than 50.

   SELECT PNR, PNAME, GENDER, AGE FROM PASSENGERS WHERE AGE < 50;

3. To display total number of MALE and FEMALE Passengers. To count passengers by gender, we use the COUNT(*) aggregate function and group the results by the GENDER column.

   SELECT GENDER, COUNT(*) FROM PASSENGERS GROUP BY GENDER;

4. To display details of all Passengers travelling in Trains whose TNO is 12015. This query retrieves all details (*) for passengers from the PASSENGERS table, specifically those associated with TNO 12015.

   SELECT * FROM PASSENGERS WHERE TNO = 12015;

5. SELECT MAX (TRAVELDATE), MIN(TRAVELDATE) FROM PASSENGERS WHERE GENDER = 'FEMALE'; This query finds the latest (MAX) and earliest (MIN) travel dates exclusively for female passengers. Output:

MAX(TRAVELDATE) MIN(TRAVELDATE)
2018-11-10      2018-05-09

(vi) SELECT END, COUNT() FROM TRAINS GROUP BY END HAVING COUNT()>1;

This query groups trains by their END station and then filters these groups to show only those stations where more than one train terminates.

Output:

END               COUNT(*)
Habibganj         2
Amritsar Junction 2 …

Understanding how to interact with databases using SQL (Structured Query Language) is fundamental in data management. SQL allows us to retrieve, insert, update, and delete data, making it a powerful tool for anyone working with information systems. The core idea behind these queries is to precisely tell the database what information you need and under what conditions.

Let's break down the requests, focusing on the logic behind each SQL statement.

SQL Queries (i) to (iv)

These tasks require us to write SQL SELECT statements, which are used to retrieve data from one or more tables. The key is to identify the correct table, the columns to display, and any conditions to filter the rows.

(i) To display details of all Trains which Start from New Delhi.

To achieve this, we look at the TRAINS table and select all columns (*) for trains where the START column has the value 'New Delhi'.

SELECT *
FROM TRAINS
WHERE START = 'New Delhi';

(ii) To display the PNR, PNAME, GENDER and AGE of all Passengers whose AGE is below 50.

We select the PNR, PNAME, GENDER, and AGE columns from the PASSENGERS table, only for passengers whose AGE is less than 50.

SELECT PNR, PNAME, GENDER, AGE
FROM PASSENGERS
WHERE AGE < 50;

(iii) To display total number of MALE and FEMALE Passengers.

Group the passengers by their GENDER and count how many fall into each category. The GROUP BY clause is essential for aggregation functions like COUNT().

SELECT GENDER, COUNT(*)
FROM PASSENGERS
GROUP BY GENDER;

(iv) To display details of all Passengers travelling in Trains whose TNO is 12015.

Select all details (*) from the PASSENGERS table where the TNO (Train Number) is 12015.

SELECT *
FROM PASSENGERS
WHERE TNO = 12015;

Outputs for SQL Queries (v) to (viii)

For these parts, we are given the SQL queries and determine what data they return based on the provided TRAINS and PASSENGERS tables.

(v) SELECT MAX (TRAVELDATE), MIN(TRAVELDATE) FROM PASSENGERS WHERE GENDER = 'FEMALE';

This finds the latest (MAX) and earliest (MIN) TRAVELDATE among female passengers.

Female passengers and their travel dates:

• P003 (S TIWARY): 2018-11-10
• P005 (S SAXENA): 2018-10-12
• P006 (P SAXENA): 2018-10-12
• P009 (R SHARMA): 2018-05-09

The maximum is 2018-11-10 and the minimum is 2018-05-09.

(vi) SELECT END, COUNT(*) FROM TRAINS GROUP BY END HAVING COUNT(*)>1;

Group the TRAINS by their END station, count trains per group, and keep only groups with more than one train.

Counting the END stations:

• Ahmedabad Junction: 1
• Ajmer Junction: 1
• Habibganj: 2
• Amritsar Junction: 2
• New Delhi: 4 (Prayag Raj Express, Shane Punjab, Shram Shakti Express, Swarna Shatabdi)
• Sealdah: 1 …