CLASS_XII B- COMPUTER SCIENCE

Practical-17

Write SQL Queries for the following Descriptions.

1.​ Display all the databases in MySQL.
2.​ Open a desired database to work in that database. (e.g. SCHOOL/ Any other database)
3.​ Create a table FLIGHT with the following descriptions.

Fields/ FLNO START TO F_Date Fare

Columns

Datatype char varchar varchar Date Decimal

Size 4 20 20 8,2

Constraint PRIMARY NOT NULL NOT NULL

KEY

4.Create a table PASSENGER with the following descriptions.

Fields/ PNO PName GENDER FLNO

Columns

Datatype int varchar char char

Size 4 20 1 4

Constraint PRIMARY KEY NOT NULL FOREIGN KEY

5. Display the structure of the above created tables.

6.Insert the following 5 records/rows in the table FLIGHT.

7.Insert the following 4 records/rows in the table PASSENGER.

Practical-18

Write SQL Queries for the following Descriptions.

1.​ Display details of all flights which are going to various places(no duplicate
destinations).
2.​ Display Flight No, From and To cities for the flights whose destination is Mumbai.
3.​ Display Details of Flights whose Fare is more than Rs,4800
4.​ Display Details of Flights which starts from Delhi OR Kolkata.
5.​ Display Flight Start, End and Fare details whose Fare more than or equal to 4000
and less than or equal to 4500 using BETWEEN..AND operator.
6.​ Display Flight No and Fare Amount after increasing the Fare amount by 15% with
Column Name as ‘ Revised Fare’.

Practical-19

Write SQL Queries for the following Descriptions.

1.Display Flight’s No and Destination for those which starts from DELHI OR MUMBAI
using IN operator.

2.Display Flight No, Starts and F_Date whose F_Date before ‘2021-12-01’.

3.Display details of flights whose date of flying from ‘2021-12-01’ to ‘2021-12-31’(both

dates are also inclusive).

4.Display Flight No,Destination and Fare amount whose F_Date given as NULL
value.(Consider some of the flight. Date given as NULL).

5. Display Names of Destination City of the flight table which contain the word ‘GAL’.

6.Display passenger details whose name starts with ‘A’ and ends with ‘a’.

7.Display Flight No , Start and Fare in descending order Fare amount.

Practical-20

Write SQL Queries for the following Descriptions.

1.Add a New column named ‘FL_OPERATOR’ with datatype varchar(20) and constraint
NOT NULL.

2.Change the name of columns START as FROM , END as ‘TO’ in the Flight.

3.Add constraint PRIMARY KEY for the column FL_NO.

4.Remove a constraint NOT NULL from a column ‘FL_OERATOR’.

5. Remove a column ‘‘FL_OERATOR’ from a table flight.

Practical-21

Write SQL Queries for the following Descriptions.

1.Increase the fare of flights by 12.5% which starts from Delhi.

2.Change the name of Start City from ‘Delhi’ to ‘JAIPUR’ for the flight number ‘F103’.

3. Delete the records of flight whose FL_Date beyond 15th Dec,2021.

4.Display City wise (END city) Total Amount of Fare.(Citywise group).

5.Display total Male and Female passengers in Passenger table.

6.Display Maximum and Minimum Fare of the flights in the table flight.

7.Display Total Number of flights and Name of Operators which operate more than 2
flights from the table flight.
(Consider FL_OPERATOR column contains Name of Operator).

Practical-22
Write SQL Queries for the following Descriptions.

1.Display Details of Flight and Passenger’s Name for the Passenger ‘Nita’.

2.Display Passenger wise Start and End Station.

3.Display Names of Passenger and END City who are not going to the city ‘CHENNAI’.

4.Display Names of Passenger, Gender and Fare Amount for those which are Female and
Fl_Operator is ‘ Indigo Airlines’.

5.Display flight wise no of passengers.

Practical-23

Write a Python -Database connectivity code for the following description.

Database name- AIRPORT_DATA

Table - Flight
Columns-
(FL_NO, FROM,TO, FL_DATE, FARE,FL_OPERATOR)

Write python code for

(1)Add a new record in the table ‘flight’ and accept these records' value from the user.

(2) Display Details for each record of the table ‘flight’ in Python output.
(3) Display Fight No, From, To and Fare amount for the flight which is operated by ‘AIR
INDIA’ .

Practical-24

Write a Python -Database connectivity code for the following description.

Database name- AIRPORT_DATA

Table - Flight
Columns-
(FL_NO, FROM,TO, FL_DATE, FARE,FL_OPERATOR)

Write python code for the following description.

(1)Search and Modify the Flight’s Fare Amount increased by 15% in a table ‘flight’ for the
flight No entered by user.

(2)Search and delete those Flight records whose FL_date before 01/11/2021.

(3) Display all records after performing the above operation.