SRI SRI RAVISHANKAR VIDYA MANDIR, BE

CLASS 12 WORSHEET-1
Chapter 10,11,12,13
Subject: Computer Science

1. STATE TRUE OR FALSE

a. Unique is used to eliminate the duplicate rows from the output
in SQL query
b. Group functions can be applied on any data types i.e. numeric,
data, string.

2. Assertion/Reasoning Questions
a) Both A and R are True and R is the correct explanation for A
b) Both A and R are True and R is not the correct explanation for A
c) A is True but R is False
d) A is False but R is True.

A: Single row functions work with a single row.

R: A single row function returns aggregated value.

A: NULL is Special value that is stored when actual data

value is unknown for an attribute.
R: Foreign key can take NULL values

3. Primary key is selected from set of _____

a. Composite keys
b. Candidate keys
c. Determinants
d. Foreign keys

4. Consider the following MySQL statement. What type of statement is

this?
Select * from employee
a. DML
b. DDL
c. DCL
d. Integrity constraint

5. Which of the following is not a DDL command?

1
 a. Update
b. Truncate
c. Alter
d. None of these

6. The_____command of SQL lets you make queries to fetch data from

tables.
7. A non-key attribute derived from primary key of some other relation is
called ______.
8. A column in a relation is called an _____

9. What is data redundancy?

[Link] is relation?
[Link] the terms : Tuple and Attribute
[Link] do you understand by the terms Cardinality and Degree of the
table. Explain with suitable example?
[Link] are the different Data types available in MySQL

[Link] MySQL queries for (a) to (e) and find outputs for MySQL queries
for (f), based on the table SchoolBus:

2
(f) Display the maximum charges applied distance wise sorted in descending
order of distance.
(g) display the total number of students distance wise only if the distance is
either 20 or 30
(h)

[Link] the MySQL queries for (i) to (vi) and output for (vii) to (x) based on
the following table ‘CABHUB’:

(i) Display the count of white coloured Cab.

(ii) Display the total capacity of all the Cab.
(iii) Display all the cab details where charge is in the range of 10 to 14;
(iv) Display the all the cab details that ends with ‘A’ for the MAKE;
(v) To display name of vehicle, make and capacity of vehicles in
ascending order of their seating capacity.
(vi) To display the highest charges at which a vehicle can be hired
from CABHUB.
(vii) Display the maximum capacity of each make.
3
 (viii) Select max(vname) from CABHUB;
(ix) Select * from CABHUB where COLOR like ‘%I%’;
(x) Select VCODE, VNAME,MAKE from CABHUB where capacity not
between 1 and 4
(xi) Select * from CABHUB order by COLOR asc, MAKE desc;

16.a) Create a table CABHUB as per the following table instance chart
Column VCODE VNAME COLOR CAPACITY CHARGES
Name
Data Type Int Varchar Char Int float
Constraint Primary Unique Default Default
key color charges 0
white

b) Add a new column MAKE of varchar type to table CABHUB

c) update the values of MAKE in the table according the condition given
below
(i) if vehicle name is ‘XL6’ or ‘Nexon’, MAKE should be set as ‘TATA’.
(ii) if vehicle name is ‘Innova’ , MAKE should be set as ‘TOYOTA’.
d) Create a table Details which refers to the table CABHUB and has an
additional attribute cabdriver varchar. [Note: any changes should not be
allowed in CABHUB table for the attribute which is referenced by Details
table].
e) Drop the table details

4
 SRI SRI RAVISHANKAR VIDYA MANDIR, BE
CLASS 12 WORSHEET-2
Chapter 10,11,12,13
Subject: Computer Science

1. STATE TRUE OR FALSE

a. A user may specify two or more columns as using the SELECT
– DISTINCT clause
b. Any attribute which is present in the having clause without
being aggregated must not be present in the group by clause.

2. Assertion/Reasoning Questions
a. Both A and R are True and R is the correct explanation for A
b. Both A and R are True and R is not the correct explanation for A
c. A is True but R is False
d. A is False but R is True.

1. A: Between operators produces a result set based on expression

within a range.
R: An expression can be written using >= and <= operators
equivalent to Between Operator

2. A: Count(*) and Count(Column Name) returns same outputs.

R: Null values are not counted by Count()
3. List out Various commands available under DDL and DML.
4. What do you understand by domain?
5. Define the terms and explain with suitable examples: Primary Key,
Alternate Key, Candidate Key, foreign key, composite primary key
6. What is the difference between Where and Having Clause.
7. Differentiate between DDL and DML commands.

8. To remove a table as well as the table structure, use command _____

_____.
9. In select queries, the ______conditions are applicable on individual rows
whereas _____conditions are applicable on groups formed by group by
clause.
[Link] function that gives the number of rows present in the given
table including the NULL values is_____
5
 [Link] the correct command to delete an attribute A from a relation R.
a. Alter table R delete A
b. Alter table R drop column A
c. Alter table drop A from R
d. Delete A from R
[Link] of the following is true for HAVING clause?
a. Acts like a where clause but is used for columns rather than groups
b. Acts like a where clause but is used for rows rather than columns
c. Acts like where clause but is used for groups rather than individual
rows
d. Acts exactly like a where clause.
[Link] c1, c2, c3 are column names in a relation and S is another relation,
which of the following is an incorrect specification of an integrity
constraint in CREATE TABLE command?
a. Primary key(c1,c2,c3)
b. Primary key(c1)
c. Foreign key(c1,c2) references S
d. Foreign key(c1,c2)
[Link] out any two database software names.
15.

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

(b) Write the degree and cardinality of the table Empl.
6
(c) Harish wants to delete all the records of the table Empl. Structure of the
table should not be removed. Write the query for the same.
(d) Write a query to increase the salary of all the employees by 5000.
(e) Harish wants to see the structure of the table ‘Empl’. Help him to do the
same.
(f) Help Harish in adding a new column grade of integer type (size 1).

16.A company manages subscriptions to Fifty different magazines.

Customers can subscribe to receive one or more of the magazines.
- Each magazine has a category such as ‘Self-help’ ,’Sports’, ‘News’,
‘Technology’ and so forth
- Each magazine has annual subscription fee
Details of subscriptions are to be stored in a database using the
following relations:
- Magazine(MagazineID : char(5), MagazineName : Varchar(50),
Category : Varchar(50), SubscriptionFee : Float)
- Subscription(SubcriptionID : number(70), MagazineID : char(5),
CustomerID:char(8), Startdate :date, EndDate:date)
- Customer(CustomerID:char(8), CustomerName : varchar(50),
Address:Varchar(50), Pincode : char(6),Phone :
number(10,0),Email:Varchar(50))

(a) Does any table in the given database contain a composite primary key?
(b) Complete the following DDL statement to create the magazine relation
that ensures that subscriptionFee is not less than 1200.
(c) Write a SQL command to display names and email IDs of the customers
whose subscription have ended.
(d) Does any table in the given database contain foreign key? If yes, name
them.
(e) Identify anyone constraint other than primary key and Not null in the
table magazine. Name the attribute and the constraint name.
(f) Can the following record be inserted successfully in Magazine relation?
Why/why not.
‘M2100’,’Die to Live’,’Spiritual’,200
(g) Write a MySQL query to display names and phones of those customers
who have not subscribed to any magazine.
(h) Write a DDL command to create the relation containing the foreign keys.
(i) The Magazine publishing company has decided to add a column
‘TargetAudience’ to the relation Magazine, which will store if the
magazine is for children or adults.
7