Q.Write SQL queries for

Table Structure and Keys

The PASSENGER table uses PNO as its primary key to uniquely identify each passenger, while FNO acts as a foreign key linking to the FLIGHT table. The FLIGHT table has FNO as its primary key, uniquely identifying each flight record.

(a) Changing the fare for flight F104

UPDATE FLIGHT
SET FARE = 6000
WHERE FNO = 'F104';

This modifies the FARE column in the FLIGHT table where the flight number matches F104.

(b) Counting passengers by gender

SELECT GENDER, COUNT(*) AS Total
FROM PASSENGER
GROUP BY GENDER;

The GROUP BY clause groups rows by GENDER, and COUNT(*) tallies the number of passengers in each group.

(c) Displaying passenger details for Delhi departures

SELECT P.NAME, F.FARE, F.F_DATE
FROM PASSENGER P, FLIGHT F
WHERE P.FNO = F.FNO AND F.START = 'DELHI';
``` …

The question asks you to manipulate and retrieve data from two related tables—PASSENGER and FLIGHT—using SQL. The relationship is straightforward: each passenger record contains a foreign key FNO that links to a flight in the FLIGHT table. Understanding this connection is essential because some queries require you to pull information from both tables simultaneously.

Let's work through each part, thinking about what the database needs to do.

(a) Changing the fare for flight F104

You need to modify an existing record. The UPDATE statement is designed for this: it targets a specific table, sets new values for one or more columns, and uses a WHERE clause to identify which row(s) to change. Here, you want to change the FARE column in the FLIGHT table where FNO equals F104.

UPDATE FLIGHT
SET FARE = 6000
WHERE FNO = 'F104';

The WHERE clause is crucial—without it, every flight's fare would be set to 6000. Always specify the condition that isolates the row you want to update.

(b) Counting passengers by gender

This is a classic aggregation problem. You want to count how many passengers fall into each gender category. The COUNT function tallies rows, and GROUP BY splits the result set into groups—one for each distinct value of GENDER. The query scans the PASSENGER table, groups rows by MALE and FEMALE, and counts how many are in each group.

SELECT GENDER, COUNT(*) AS TOTAL
FROM PASSENGER
GROUP BY GENDER;

The result will show two rows: one for MALE (with a count of 2) and one for FEMALE (also 2). The alias TOTAL makes the output column name clearer, though it's optional.

(c) Displaying passenger details for flights starting from Delhi

Now you need information from both tables. A passenger's name is in PASSENGER, but the fare and flight date are in FLIGHT. The link is FNO. An INNER JOIN combines rows from both tables where the FNO values match. Then you filter for flights where START equals 'DELHI'.

SELECT P.NAME, F.FARE, F.F_DATE
FROM PASSENGER P
JOIN FLIGHT F ON P.FNO = F.FNO
WHERE F.START = 'DELHI';

The aliases P and F make the query more readable. This will return Nita (who is on F103, Delhi to Chennai) with fare 5500 and date 2021-12-10. Notice that Harjas, though male, is on F102 (Mumbai to Bengaluru), so he doesn't appear in the result.

(d) Deleting flights that end at Mumbai …