STD – XII – MYSQL – NOTES

APPLICATION PROGRAMS

KCODE BOOKNAME PUB PRICE

Identify the following in the above table

1) Degree of Table - 4
2) Cardinality of Table - 4
3) Attributes of Table - 4
4) Tuple - 4
5) Data types of Book Code and Price (as studied in Python)
Book Code – Text Varchar
Price – Integer

ASSERTIONS AND REASONS

In the following questions, a statement of Assertion (A) is followed by a statement of

Reason (R) Mark the correct choice as :
a) Both A and R are true and R is the correct explanation of A.
b) Both A and R are true but R is not the correct explanation of A.
c) A is true and R is false (or partly true)
d) A is false (or partly true) but R is true
e) Both A and R are false or not fully true.

1. Assertion : The PRIMARY KEY and UNIQUE constraints are the same.
Reason : The columns with PRIMARY KEY or UNIQUE constraints unique
values for each row. Answer : Option d
2. Assertion : The treatment of NULL values is different with PRIMARY KEY and
UNIQUE constraints.
Reason : The column(s) with PRIMARY KEY do not allow the NULL value
even in a single row but UNIQUE constraint allow NULL for one of the rows.
Answer : Option a
3. Assertion : There is no restriction on the number of columns that can have
PRIMARY KEY constraint or UNIQUE constraints
Reason : There can be multiple column with UNIQUE constraint but PRIMARY
KEY constraint can be defined only once for one or more columns.
Answer : Option d
4. Assertion : There are different commands for creating and changing table design.
Reason : The CREATE TABLE command creates the tables while ALTER
TABLE command change the design of an existing table. Answer : Option a
 5. Assertion : Both DELETE and DROP TABLE carry out the same thing –
deletion in tables.
Reason : The DELETE command deletes the rows and DROP TABLE deletes
the whole table. Answer : Option d
6. Assertion : Both UPDATE and ALTER TABLE commands are similar.
Reason : The UPDATE command as well ALTER TABLE command make
changes in the table. Answer : Option e

Which pair of SQL Statement is not from the same category of SQL command
(a) create, alter
(b) insert, delete
(c) delete, drop
(d) update, delete

To view the list of tables of a database, the correct command is ____

(a) list tables
(b) show tables
(c) display tables
(d) view tables

Using python mysql connectivity the correct code to display all the available databases of
Mysql with the help of a cursor mcur is
(a) mcur.execute(“show database”)
(b) mcur.execute(“desc database”)
(c) mcur.execute(“show databases”)
(d) mcur.execute(“desc databases”)
How many candidate key and primary key a table can have? Can we declared combination
of fields as a primary key?

Mrs sunita wrote a query in SQL for student table but she is not getting desired result
select * from student where fee = NULL;
Rewrite the above query so she well gets desired result
select * from student where fee IS NULL
OR
What is the difference between delete and drop command?
Delete is DML command and it deletes only data
Drop is DDL command it delete data as well entire structure of table

(a) What is the difference between non-equi join and equi join.
Non equi join is Cartesian product of two table where each row of first table is joined with
every row of second table. If m and n is total number of rows in both table then non equi
join will have m x n rows
Equi join is applied if there exist one common column in each tables whose values can be
matched.
(a) 4
(b) 3 3
(c) 45 52
(d) 3
(b) Consider the TEACHER table given below
TEACHER SUBJECT GENDER AGE
MANOJ SCIENCE MALE 38
NIKUNJ MATHS MALE 40
TARA SOCIAL SCIENCE FEMALE 38
SUJAL SCIENCE MALE 45
RAJNI SCIENCE FEMALE 52
GEETA HINDI FEMALE 31

Write OUTPUT of the following queries

(a) select count(distinct subject) from teacher;
(b) select count(*) from teacher group by gender;
(c) select sum(age) from teacher where age >40 group by gender;
(d) select count(*) from teacher group by subject having count(*)>1;

I Identify candidate key of the Game table, if any. Justify your answer
There is no any candidate key as every field has duplicate values
II How many rows will be in output if a query is executed using DISTINCT clause in
field Captain. Answer : 3
III
(i) Which command will display the structure of GAME table
Answer : DESC GAMES;
(ii) Write a sql query that will delete the record of captain Dhoni in outside location
DELETE FROM GAME WHERE CAPTION = ‘Dhoni’ and Location = ‘Outside’ ;

OR
i (i) Write sql query to remove the column draw
ALTER TABLE DROP DRAW
ii (ii) Write sql query that change number of player for the game kabaddi to 6
UPDATE GAME SET NOPLAYER = 6 WHERE GNAME=’Kabaddi’ ;
I (I) select count(*) from patient where date_visit like ‘%2_’;
3
II (II) select count(*) from doctor group by specialization;
1
III (III) select a.dname, b.pname from doctor a, patient b where a.docid=b.did;
1
IV (IV) select dname from doctor,patient where docid=did and pname=’Arjun’;
2

(c) With reference to RDBMS define attribute. A column or field of a table is called attribute

Which of the following types of table constraints will prevent the entry of
duplicate rows?
a) Unique

b) Distinct
c) Primary Key
d) NULL

A departmental store MyStore is considering to maintain their inventory using

SQL to store the data. As a database administer, Abhay has decided that :
• Name of the database - mystore
• Name of the table - STORE
• The attributes of STORE are as follows: ItemNo
- numeric
ItemName – character of size 20
Scode – numeric
 Quantity – numeric

(a) Identify the attribute best suitable to be declared as a

primary key,
Answer : ItemNo

(b) Write the degree and cardinality of the table STORE.

Degree = 4 Cardinality = 7
(c) Insert the following data into the attributes ItemNo,
ItemName and SCode respectively in the given table STORE.
ItemNo = 2010, ItemName = “Note Book” and Scode = 25

INSERT INTO store (ItemNo,ItemName,Scode) VALUES(2010,

“Note Book”,25);
(d) Abhay want to remove the table STORE from the database
MyStore. Which command will he use from the following:
a) DELETE FROM store;
b) DROP TABLE store;
c) DROP DATABASE mystore;
d) DELETE store FROM mystore;

Answer DROP TABLE store;

(e) Now Abhay wants to display the structure of the table
STORE, i.e, name of the attributes and their respective data
types that he has used in the table. Write the query to
display the same.
Answer : Describe Store;
Which of the following is a DDL command?
a) SELECT b) ALTER c) INSERT d) UPDATE
Write any one aggregate function used in SQL.
SUM / AVG / COUNT / MAX / MIN
In SQL, what is the use of IS NULL operator?
To check if the column has null value / no value
In SQL, name the clause that is used to display the
tuples in ascending order of an attribute.
ORDER BY

SELECT Department, count(*) FROM Teacher GROUP BY Department;

SELECT Max(Date_of_Join), Min(Date_of_Join) FROM Teacher;
Max - 31/07/2018 or 2018-07-31 Min- 05/09/2007 or 2007-09-05

SELECT Teacher.name,Teacher.Department,
Posting.Place FROM Teachr, Posting WHERE
Teacher.Department = Posting.Department AND
Posting.Place=”Delhi”;
 i. To show all information about the teacher of History
department.
SELECT * FROM teacher WHERE department= “History”;

ii. To list the names of female teachers who are in Mathematics

department.

SELECT name FROM teacher WHERE department= “Mathematics”

AND gender= “F”;
iii. To list the names of all teachers with their date of joining in
ascending order.
SELECT name FROM teacher ORDER BY date_of_join;
iv. To display teacher’s name, salary, age for male teachers only.
SELECT name, salary, age FROM teacher WHERE gender=’M’;

v. To display name, bonus for each teacher where bonus is 10% of

salary.
SELECT name, salary*0.1 AS Bonus FROM teacher;

6. (a) Write the steps to connect with database “testdb” with Python programming. (2)
Ans. Steps to be followed are:
• Import mysqldb as db
• Connect
• Cursor
• Execute
• Close
(b) Which method is used to retrieve all rows and single row? (1)
Ans. fetchall(),fetchone()

To select tuples with some esalary, Arun has written the following erroneous SQL
statement:
SELECT eid, esalary FROM empsalary WHERE esalary = something;

Write the correct SQL statement.

Ans. The correct SQL statement is:
SELECT eid, esalary FROM empsalary WHERE esalary is NOT NULL;.

(i) Write a query to display name in descending order whose age is more than 23.
Ans. select name from coaching where age>23 order by name desc;
(ii) Write a query to find the average fee grouped by age from customer table.
Ans. select avg(fee) from coaching group by age;
(iii) Write query details from coaching table where fee is between 30000 and 40000.
Ans. Select * from coaching table where fee is between 30000 and 40000;
(iv) Select sum(Fee) from coaching where city like “%O% ;
Ans. 94000
(v) Select name, city from coaching group by age having count(age)>2;
Ans. Empty set

To display minimum amount transaction from the table.

Ans.
select min(amount) from Transact;

To display total amount withdrawn from table.

Ans.
select sum(amount) from Transact where type = “Withdraw”;
To display number of deposited transaction.
Ans.
select sum(amount) from Transact where type=”Deposit”;

To display ANO,DOT, AMOUNT for maximum amount transaction.

Ans.
select ANO, DOT, AMOUNT from Transact where AMOUNT = max(AMOUNT);

SELECT ANO, COUNT(*), MIN(AMOUNT) FROM TRANSACT

GROUP BY ANO HAVING COUNT(*)> 1
Ans.
ANO COUNT(*) MIN(AMOUNT)
101 2 2500
103 2 1000

SELECT COUNT(*), SUM(AMOUNT) FROM TRANSACT WHERE DOT <= '2017-06-01';

Ans.

COUNT(*) SUM(AMOUNT)

2 5000

(a) Display NAME of all the staff members who are in SALES having more than 10 years’ experience from
the table staff.
(b) Display the average Basic Salary of all staff members working in ”Finance” department using the tables
staff and salary.
(c) Display the minimum ALLOWANCE of female staff.
(d) Display the highest commission among all male staff.
(e) Select count(*) from STAFF group by sex;
(f) SELECT NAME,DEPT,BASIC FROM STAFF,SALARY WHERE DEPT=‘SALES’ AND
STAFF.ID=SALARY.ID;

(i) To display the name of all activities with their Acodes in descending order.
(ii) To display sum of PrizeMoney for each of the Number of participants groupings (as shown in column
ParticipantsNum 10,12,16)
(iii) To display the coach’s name and ACodes in ascending order of ACode from the table COACH
(iv) To display the content of the GAMES table whose ScheduledDate is earlier than 01/01/2004 in
ascending order of ParticipantNum
(v) SELECT COUNT(DISTINCT ParticipantsNum) FROM ACTIVITY;
(vi) SELECT MAX(ScheduledDate),MIN(ScheduledDate) FROM ACTIVITY;
(vii) SELECT SUM(PrizeMoney) FROM ACTIVITY;
(viii) SELECT DISTINCT ParticipantNum FROM COACH;

(i) To display all information of the students of humanities in descending order of percentage.
(ii) To display Adno, Name, Percentage and Stream of those students whose name starts with S
and ends with t.
(iii) To display SName, Percentage, Clsection of students who have highest percentage in each
stream.
(iv) To display details of students who have Percentage in range of 80 and 90(both inclusive) in
decreasing order of Percentage.
(v) SELECT COUNT(*) FROM EXAM;
(vi) SELECT Sname, Percentage FROM EXAM WHERE Name LIKE “N%”;
(vii) SELECT ROUND(Percentage,0) FROM EXAM WHERE Adno=”R005”;
(viii) SELECT DISTINCT Clsection FROM EXAM.

Important Questions for Class 12

Computer Science (Python) – Structured
Query Language
ADVERTISEMENT

Important Questions for Class 12 Computer Science

(Python) – Structured Query Language
Short Answer Type Questions

Question 1:
Write queries for (i) to (iv) and find ouputs for SQL queries (v) to (viii), which are
based on the tables.

Table :
VEHICLE

Note:

• PERKS is Freight Charges per kilometer.

• Km is kilometers Travelled
• NOP is number of passangers travelled in vechicle.
 1. To display CNO, CNAME, TRAVELDATE from the table TRAVEL in
descending order of CNO.
2. To display the CNAME of all customers from the table TRAVEL who are
travelling by vechicle with code Vo1 or Vo2
3. To display the CNO and CNAME of those customers from the table TRAVEL
who travelled between ‘2015-1231’ and ‘2015-05-01’.
4. To display all the details from table TRAVEL for the customers, who have
travel distacne more than 120 KM in ascending order of NOE
5. SELECT COUNT (*), VCODE FROM TRAVEL GROUP BY VCODE HAVING
COUNT (*) > 1;
6. SELECT DISTINCT VCODE FROM TRAVEL :
7. SELECT A.VCODE, CNAME, VEHICLETYPE FROM TRAVEL A, VEHICLE B
WHERE A. VCODE = B. VCODE and KM < 90;
8. SELECT CNAME, KM*PERKM FROM TRAVEL A, VEHICLE B WHERE
A.VCODE = B.VCODE AND A. VCODE ‘V05’;

Answer:
Question 2:
Consider the following tables SCHOOL and ADMIN and answer this question :
Give the output the following SQL queries :

1. Select Designation Count (*) From Admin Group By Designation Having Count
(*) <2;
2. SELECT max (EXPERIENCE) FROM SCHOOL;
3. SELECT TEACHER FROM SCHOOL WHERE EXPERIENCE >12 ORDER BY
TEACHER;
4. SELECT COUNT (*), GENDER FROM ADMIN GROUP BY GENDER;

Answer:

Question 3:
Write SQL qureries for (i) to (iv) and find outputs for SQL queries (v) to (viii), which
are based on the tables TRANSPORT and TRIE

Note:

• PERKS is Freight Charges per kilometer

• TTYPE is Transport Vehicle Type

Note:

• NO is Driver Number
• KM is Kilometer travelled
• NOP is number of travellers travelled in vehicle
• TDATE is Trip Date

1. To display NO, NAME, TDATE from the table TRIP in descending order of NO.
2. To display the NAME of the drivers from the table TRIP who are traveling by
transport vehicle with code 101 or 103.
3. To display the NO and NAME of those drivers from the table TRIP who
travelled between ‘2015-02-10’ and ‘2015-04-01’.
4. To display all the details from table TRIP in which the distance travelled is
more than 100 KM in ascending order of NOP
5. SELECT COUNT (*), TCODE From TRIP
GROUP BY TCODE HAVNING COUnT (*) > 1;
6. SELECT DISTINCT TCODE from TRIP;
 7. SELECT A.TCODE, NAME, TTYPE
FROM TRIP A, TRANSPORT B
WHERE A. TCODE = B. TCODE AND KM < 90;
8. SELECT NAME, KM *PERKM
FROM TRIP A, TRANSPORT B
WHERE A. TCODE = B. TCODE AND A. TCODE = 105′;

Answer:

1. SELECT NO, NAME, TDATE FROM TRIP ORDER BY NO;

2. SELECT NAME FROM TRIP
WHERE TCODE = 101 OR TCODE = 103;
3. SELECT NO AND NAME FROM TRIP
WHERE ‘2015-02-10’ < TDATE < ‘2015-04-01’;
4. SELECT NO, NAME, TDATE, KM, TCODE FROM TRIP
WHERE KM >100 ORDER BY NOP;
5. TO DISPLAY THE MORE THAN ONE COUNT OF TCODE FROM THE
TABLE TRIP
6. TO DISPALY SEPERATE TCODE OF TABLE TRIP
7. TO DISPAY THE NAME AND CODE OF THOSE TRANS PORTERS, WHO
HAVE TRAVELLED MORE THAN 90 KMS.
8. TO DISPLAY THE NAME AND EXPENDITARE OF A TRANSPORTER WHO
HAVE TCODE AS 105.

Question 4:
Write SQL query to add a column total price with datatype numeric and size 10, 2 in
a table product.
Answer:
ALTER TABLE PRODUCT ADD TOTAL PRICE NUMBER (10,2).

Question 5:
Sonal needs to display name of teachers, who have “0” as the third character in their
name. She wrote the following query.
SELECT NAME FROM TEACHER WHERE NAME = “$$0?”;
But the query is’nt producing the result. Identify the problem.
Answer:
The wildcards are incorrect. The corrected query is SELECT NAME FROM
TEACHER WHERE NAME LIKE ‘_ _0%’.

Question 6:
Deepika wants to remove all rows from the table BANK. But he needs to maintain
the structure of the table. Which command is used to implement the same?
Answer:
DELETE FROM BANK.

Question 7:
While creating table ‘customer’, Rahul forgot to add column ‘price’. Which command
is used to add new column in the table. Write the command to implement the same.
Answer:
ALTER TABLE CUSTOMER ADD PRICE NUMBER (10, 2).

Question 8:
What is the use of wildcard
Answer:
The wildcard operators are used with the LIKE operator to search a value similar to a
specific pattern in a column. There are 2 wildcard operators.
% – represents 0,1 or many characters – – represents a single number or character

Question 9:
Differentiate between DELETE and DROP table commands ?
Answer:
DELETE command is used to remove infor¬mation from a particular row or rows. If
used without condition, it will delete all row information but not the structure of the
table. It is a DML command.
DROP table command is used to remove the entire structure of the table and
information. It is a DDL command

Long Answer Type Questions

Question 1:
Write SQL commands for the queries (i) to (iv) and output for (v) & (viii) based on a
table COMPANY and CUSTOMER.

1. To display those company name which are having prize less than 30000.
2. To display the name of the companies in reverse alphabetical order.
3. To increase the prize by 1000 for those customer whose name starts with „S?
4. To add one more column totalprice with decimal] 10,2) to the table customer
5. SELECT COUNT(*) , CITY FROM COMPANY GROUP BY CITY;
6. SELECT MIN(PRICE), MAX(PRICE) FROM CUSTOMER WHERE QTY>10;
7. SELECT AVG(QTY) FROM CUSTOMER WHERE NAME LIKE “%r%;
8. SELECT PRODUCTNAME,CITY, PRICE
FROM COMPANY, CUSTOMER WHERE
COMPANY. CID=CUSTOMER.CID AND
PRODUCTNAME=”MOBILE”;

Answer:
1. SELECT NAME FROM COMPANY WHERE COMPANY.CID=CUSTOMER.
CID AND
PRICE < 30000;
2. SELECT NAME FROM COMPANY ORDER BY NAME DESC;
3. UPDATE CUSTOMER
SET PRICE = PRICE + 1000
WHERE NAME LIKE ‘S%’;
4. ALTER TABLE CUSTOMER
ADD TOTALPRICE DECIMAL(10,2);

5.
6. 50000,70000
7. 11

8.