DAV NANDRAJ PUBLIC SCHOOL
BARIATU, RANCHI
FREQUENTLY ASK QUESTIONS IN BOARD EXAMINATION (XII)
SUBJECT: COMPUTER SCIENCE WITH PYTHON
CHAPTER NAME: RELATIONAL DATABASE & MYSQL , INTERFACE PYTHON WITH SQL
1. Consider the following table named “Product”, showing details of products being sold in a
grocery shop.
PCode PName UPrice Manufacturer
P01 Washing Powder 120 Surf
P02 Toothpaste 54 Colgate
P03 Soap 25 Lux
P04 Toothpaste 65 Pepsodent
P05 Soap 38 Dove
P06 Shampoo 245 Dove
Write SQL queries for the following:
a. Create the table Product with appropriate data types and constraints
b. Identify the primary key in Product.
c. List the Product Code, Product name and price in descending order of their product name. If
PName is the same, then display the data in ascending order of price.
d. Add a new column Discount to the table Product.
e. Calculate the value of the discount in the table Product as 10 per cent of the UPrice for all those
products where the UPrice is more than 100, otherwise the discount will be 0.
f. Increase the price by 12 per cent for all the products manufactured by Dove.
g. Display the total number of products manufactured by each manufacturer.
2. Consider the above mentioned table named “Product” and write the output(s) produced by
executing the following queries on the basis of the information given above in the table
Product:
a. SELECT PName, avg(UPrice) FROM Product GROUP BY Pname;
b. SELECT DISTINCT Manufacturer FROM Product;
c. SELECT COUNT (DISTINCT PName) FROM Product;
d. SELECT PName, MAX(UPrice), MIN(UPrice) FROM Product GROUP BY PName;
e. SELECT * FROM PRODUCT ORDER BY UPRICE;
Page 1 of 3
3. Answer the following questions:
a. Define RDBMS. Name any two RDBMS software.
b. What is the purpose of the following clauses in a select statement?
i) ORDER BY ii) GROUP BY
c. Differentiate between DDL and DML statements.
d. Explain the Primary Key, Candidate key, Foreign Key and Alternate Key.
e. What do you understand by Cartesian Product?
4. Consider the following MOVIE table and write the SQL queries based on it.
MovieID MovieName Category ReleaseDate ProductionCost BusinessCost
001 Hindi_Movie Musical 2018-04-23 124500 130000
002 Tamil_Movie Action 2016-05-17 112000 118000
003 English_Movie Horror 2017-08-06 245000 360000
004 Bengali_Movie Adventure 2017-01-04 72000 100000
005 Telugu_Movie Action - 100000 -
006 Punjabi_Movie Comedy - 30500 -
a. Display all the information from the Movie table.
b. List business done by the movies showing only MovieID, MovieName and Total_Earning.
Total_ Earning to be calculated as the sum of ProductionCost and BusinessCost.
c. List the different categories of movies.
d. Find the net profit of each movie showing its MovieID, MovieName and NetProfit. Net
Profit is to be calculated as the difference between Business Cost and Production Cost.
e. List MovieID, MovieName and Cost for all movies with ProductionCost greater than
10,000 and less than 1,00,000.
f. List details of all movies which fall in the category of comedy or action.
g. List details of all movies which have not been released yet.
5. Using the sports database containing two relations (TEAM, MATCH_DETAILS) and write the
queries for the following:
a. Display the MatchID of all those matches where both the teams have scored more than 70.
b. Display the MatchID of all those matches where FirstTeam has scored less than 70 but
SecondTeam has scored more than 70.
c. Display the MatchID and date of matches played by Team 1 and won by it.
d. Display the MatchID of matches played by Team 2 and not won by it.
e. Change the name of the relation TEAM to T_DATA. Also change the attributes TeamID
and TeamName to T_ID and T_NAME respectively.
6. Define the various SQL constraints.
7. Explain various read operation like fetchone(), fetchmany() and fetchall().
8. Write a python program to create a Table ‘student’ inside the database ‘school’ using python
script mode as the interface.
9. Write a python program to insert multiple records into the table student through Python Shell.
10. Write a python program to display all the records of student using Python Shell.
11. Consider the table Personal given below:
Page 2 of 3
P_ID Name Desig Salary Allowance
P01 Rohit Manager 89000 4800
P02 Kashish Clerk NULL 1600
P03 Mahesh Superviser 48000 NULL
P04 Salil Clerk 31000 1900
P05 Ravina Superviser NULL 2100
Based on the given table, write SQL queries for the following:
(i) Increase the salary by 5% of personals whose allowance is known.
(ii) Display Name and Total Salary (sum of Salary and Allowance) of all personals. The column
heading ‘Total Salary’ should also be displayed.
(iii) Delete the record of personals who have salary greater than 25000
12. Consider the tables PRODUCT and BRAND given below:
Table: PRODUCT
PCode PName UPrice Rating BID
P01 Keyboard 120 6 M03
P02 Mouse 150 8 M02
P03 Pen Drive 200 7 M03
P04 Mouse 170 4 M04
P05 Pen Drive 250 5 M05
P06 Keyboard 200 6 M05
Table: BRAND
BID BNAME
M02 ZEBRONICS
M03 HP
M04 LENOVA
M05 DELL
Write SQL queries for the following:
(i) Display product name and brand name from the tables PRODUCT and BRAND.
(ii) Display the structure of the table PRODUCT.
(iii) Display the average rating of HP and DELL brands
(iv) Display the name, price, and rating of products in descending order of rating.
Page 3 of 3
