LANCER’S CONVENT SCHOOL
INFORMATICS PRACTICES (065)
MYSQL PRACTICAL FILE QUESTIONS
CLASS XI
1. Create database with name :- SCHOOL
2. Write SQL query to create a table ‘Player’ with the following structure:
3. Write the commands for the following purpose:
a) To display the list of the database already existing in MySQL.
b) To use the database named School.
c) To remove the pre-existing database named ABC.
d) To display structure of table Player.
e) To display the list of the tables already existing in database School.
4. Create below table TEACHER.
Write the SQL commands for the following:
a) To display all information about teachers of PGT category.
b) To list the names of female teachers of Hindi department.
c) To list names, departments and date of hiring of all the teachers in ascending order of date of joining.
d) To display details of teachers whose salary is more than 20000.
e) To display teacher name starts with letter “S”.
f) To delete the column Gender.
g) To remove the primary key from the table.
h) Display all the departments available in teacher table.
i) To change the salary of teacher James from 25000 to 35000.
j) To display teacher name contains “Ka”.
k) To delete the record of teacher name Sonali form the table.
l) Display the distinct teacher category from the table.
5. Create the table MOVIE.
Write the SQL commands for the following:
a) Retrieve movies information without mentioning their column names.
b) List business done by the movies showing only MovieID, MovieName and BusinessCost.
c) List the different categories of movies.
d) Find the net profit of each movie showing its ID, Name and Net Profit. (Hint: Net Profit =
BusinessCost – ProductionCost) Make sure that the new column name is labelled as NetProfit. Is this
column now a part of the MOVIE relation. If no, then what name is coined for such columns? What
can you say about the profit of a movie which has not yet released? Does your query result show
profit as zero?
e) List all movies with ProductionCost greater than 80,000 and less than 1,25,000 showing ID, Name
and ProductionCost.
f) List all movies which fall in the category of Comedy or Action.
g) List the movies which have not been released yet.
6. Create table FLIGHT given below.
Write commands in SQL for (a) to (d) and output for (e).
a) Display details of all flights starting from Delhi.
b) Display details of flights that have more than 4 number of flights operating.
c) Display flight codes, starting place, destination, number of flights in descending order of number of
flights.
d) Display destinations along with flight codes of all the destinations starting with ‘A’.
e) SELECT DISTINCT(NO_STOPS) FROM FLIGHT;