Sample Question Paper

Information Technology (Code 802)

1. Differentiate data from information

Ans:
Processed data is called as Information

2. Define Data Redundancy.

Ans: Same information is stored in more than one file which result in wastage of space.

3. List any four advantages of DBMS.

Ans:
a) Reduction in Redundancy
b) Improved Consistency
c) Improved Availability
d) Improved Security
e) User Friendly

4. Explain the concept of Candidate Key, Primary Key and Alternate Key with the help of
a suitable example.
Ans:
a) Candidate Key: In a table, there may be more than one field that uniquely identifies
a record. All such fields are called candidate keys.
b) Primary Key: A Primary Key is one of the candidate keys. A table may have more
than one candidate keys but definitely has one and only one primary key.
c) Alternate Key: Alternate key is candidate key of a table which is not selected as the
primary key
Example: For a table with RollNo and Admission_no, Name, Class, Sec, Dues, if RollNo,
Admission_No may be used to uniquely identify each row in this Table, so both are
candidate keys. Then if we use Admission_No as the Primary Key, the other Candidate Key
RollNo is the Alternate Key

5. Define Referential Integrity.

Ans Referential Integrity is used to check that data entered in one relation is consistent
with the data entered in another relation.

6. Differentiate between DDL and DML.

Ans:
DDL is used to define structure and constraints of table
DML is used to insert, modify and delete data in a table.
DDL – Create table, Alter table, Drop table
DML- Insert, Update, Delete

7. Explain important terminologies of RDBMS.

Ans:
 a) Domain or Type of data: Collection of possible values from which the type of data
for a column is derived.
b) Tables or Relation: A two-dimensional representation of data using rows and
columns. The tables in a database are generally related to each other. Interrelated
tables also reduce the chances of errors in the database.
c) Record or Row or Tuple: The horizontal subset of the Table. Each row is a
collection of data about a particular entity such as person, place or thing.
d) Field or Column or Attribute: The vertical subset of the Table. Each column has a
unique name and the content within it must be of the same type of data.
e) Degree: Number of attributes in a relation.
f) Cardinality: Number of rows in a relation

8. List out MySQL Data Types with examples.

Ans:
Class Data Type Description Example
Text Char(size) Fixed-length string from 1 to 255 characters 'Maths'
enclosed in single quotes or double quotes "Text"
Text Varchar(size) Variable length string from 1 to 255 'Maths'
characters enclosed in single quotes or "Text"
double quotes.
Numeric Int or integer Storing integer values. You can specify a 76
width upto 11
Numeric Decimal(size,d) Storing number with or without the 17.32
fractional part. 345
Date Date It represents the date including day, month '2009-07-02’
and year
Time Time It represents time. Format: HH:MM:SS

9. Explain SQL Commands related to Database with suitable example.

Ans:
a) CREATE DATABASE: To create a database Ex. CREATE DATABASE School;
b) SHOW DATABASES: To view list of databases that currently exist in server
Ex. SHOW DATABASES;
c) USE: To open the database to work Ex. USE School;
d) SELECT DATABASE(): To view the current database, we are currently working
Ex. SELECT DATABASE();
e) DROP DATABASE: To remove a database Ex. DROP DATABASE School;

10. Explain CREATE TABLE command in detail with example

Ans:
CREATE TABLE: To create table, statement is used.
Ex: CREATE TABLE Learner (RollNo INTEGER, Name VARCHAR(25));
a) Column names should be unique.
b) Table and Column names should not have spaces and symbols except underscore
(_).

11. Write short notes on SHOW TABLES, DESC, DROP TABLE commands.
Ans:
a) SHOW TABLES: To list all the tables created in the current database Ex. SHOW
TABLES;
b) DESCRIBE or DESC: To see the structure of a table. It displays the Column
names, their data types Ex 1. DESCRIBE teacher; Ex 2. DESC teacher;
c) DROP TABLE: To delete table. Ex. DROP TABLE Teacher;

12. Explain different form of using Insert command in details with examples
Ans:
Insert into: To insert records into table
Following points should be kept in mind while inserting records in a relation:
a) String must be enclosed in single/ double quotes
b) Date should be entered in single/double quotes in format ‘yyyy-mm-dd’
c) NULL value should be entered as NULL without any quotes.
Examples for different forms of Insert:
a) insert into teacher values(1001,“selva", “krishna", ‘m', 4500, '1974-05-10', 1);
b) insert into teacher(first_name, last_name, gender, teacher_id, date_of_birth,
dept_no, salary) values(“selva", “krishna", ‘m', 1001, '1974-05-10', 1, 4500);
c) insert into teacher(teacher_id,gender, first_name,last_name) values(1001, ’m’,
’selva’, ’krishna’);

13. Explain different form of using Select command in details with examples
Ans:
Select: To view the data from a relation
Examples for different forms of Select:
a) Select * from teacher; shows all fields of table
b) Select teacher_id,first_name from teacher; shows only selected fields of table

14. Explain different form of using Delete command in details with examples
Ans:
Delete from: To delete tuples from relation
Examples for different forms of Insert:
f) DELETE FROM Teacher; deletes all records from table teacher
g) DELETE FROM Teacher WHERE Teacher_ID=1001; deletes only records which
has teacher_id=1001

15. Illustrate any two DML commands with the help of suitable example.
Ans: Insert command is used to insert/add a tuple in a table
Example: INSERT INTO Student VALUES (101,"Ananya", "Grover", 'F', '1984-08-11',
1);
Delete Command is used to remove the tuple from a table Example: DELETE FROM
Student;

16. Write down different types of Constraints in MySQL in details

Ans:
Sometimes data entered may be invalid. MySQL provides some rules, called Constraints,
which help us, to ensure validity of the data. These constraints are as follows:
Constraint Purpose
Sets a column or a group of columns as a primary key of the
Primary Key table. Therefore, NULLs and Duplicate values in this column
are not accepted.
Makes sure that NULLs are not accepted in the specified
Not Null
column.
Check In order to restrict the values of an attribute within a range
If a user has not entered a value for an attribute, then default
Default
value specified while creating the table is used.
Foreign Key or To check that data entered in one relation is consistent
Referential Integrity with the data entered in another relation
Make sure that duplicate values in the specified column are not
Unique
accepted.

17. Write down different ways to assign Primary Key Constraints into table with examples.
Ans:
Primary Key of a table can be specified in two ways.
a) If the primary key of the table consists of a single attribute, then the corresponding
attribute can be declared primary key along with its description.

b) If primary key contains more than one attribute, then it must be specified
separately as a list of attributes within parenthesis, separated by commas
18. Explain MySQL Operators and its types in details
Ans:
MySQL supports generally following types of operators.

Types Operators Description

+ Addition
- Subtraction
Arithmetic * Multiplication
/ Division
% Modulus or Remainder after Division
= Equal to
> Greater than
< Less than
Relational
>= Greater than or equal to
<= Less than or equal to
!= or <> Not equal to
AND Returns true if both the conditions are true.
OR Returns True if either one of the conditions is True
Logical
NOT Used for negation. It returns the result set that is opposite to the
given condition

19. Write short notes on BETWEEN AND operator with example

Ans:
BETWEEN AND: Used to fetch data based on a range of values on a column. The result set
includes the values of the upper and lower bound given in the range.
Example:
Select first_name,salary from teacher where salary between 5000 and 6000;

20. Write short notes on IS operator with examples

IS: Used to match NULL value in the expression.
Example:
1. Select * from teacher where salary is null;
2. Select * from teacher where salary is not null;

21. Write short notes on IN operator with examples

Ans:
IN: Used when we wish to fetch records which match a certain set of values
Example:
1. Select * from teacher where teacher_id in(1001,1003);
2. Select * from teacher where teacher_id not in(1001,1003);

22. Write short notes on LIKE operator with examples

Ans:
LIKE: Used for pattern matching. Following characters used for applying pattern matching:
1. % percent symbol is used to match none or more characters
2. _ underscore character is used to match occurrence of one character in the string
Example:
1. Select * from teacher where first_name like ‘s%’;
2. Select * from teacher where first_name like ‘%s’;
3. Select * from teacher where first_name like ‘_ _ _ _ _’;

23. Mr. Prason wants to create a new table ‘‘voters’’ having fields as voters_id, Name and
Age with voters_id as Primary Key. Help him to write the appropriate SQL command.
Ans:

24. State the difference between Update and Alter command with suitable examples
Ans:
Update: To update the attribute values of one or more tuples in a table.
Ex. UPDATE Teacher SET Salary=5500 WHERE Teacher_ID=1001; It updates the Salary
of teacher with Teacher_ID=1001 to 5000
Alter Table: To change structure or design of table i.e. add, remove or change column of
table. ALTER TABLE is also used to add or remove a constraint.
Ex 1: ALTER TABLE Teacher ADD Age INTEGER; It adds a column Age in the Teacher
table
Ex 2: ALTER TABLE Teacher DROP Age; It drops a column Age from a table teacher
Ex 3: ALTER TABLE Teacher MODIFY teacher_id varchar(20); It modifies teacher_id
column as varchar(20)

25. Consider the following and answer the questions.

(a) Write a query to create a table TravelAgency with following fields AgenCode Char(4)
Primary Key, AgentName Varchar(20), Location Varchar(25), Package Varchar(30),
Charges Decimal
Ans: Create table TravelAgency(AgentCode char(4) primary key, AgentName varchar(20),
Location Varchar(25), Package Varchar(30), Charges decimal));
(b) Write a query to add a new row to a table TravelAgency with “A006”, “John Sharon”,
“Sam Ana Travel”, “Domestic”, 45000
Ans: Insert into TravelAgency values(“A006”, “John Sharon”, “Sam Ana Travel”,
Domestic”, 45000);
(c) Write a query to add a new column EmailId to a table TravelAgency
Ans: Alter table TravelAgency Add EmailId varchar(30);

26. Ms Prabha has mistakenly entered ‘‘Mridul’’ instead of ‘‘Mridula’’ in ‘name’ field of
table ‘student’. Help her to write the correct SQL command to make the desired changes in
the table.
Ans: Update student set name=’Mridula’ where name=’Mridul’;

27. Sambhav has created the Table Item in a database ‘‘Company’’. Based on the above
information, write the SQL command to do the following:
(a) View the structure of the table.
Ans: desc item;
(b) View the contents of the table.
Ans: select * from item;

28. State the characteristics of Foreign Key or Referential Integrity Constraints

Ans:
a) It accepts only Primary Key values
b) Duplicate and null values are allowed
c) You cannot delete values of primary key, if the same value is available in foreign
key. So first you need to delete foreign key values and then primary key values
d) You cannot drop table with primary key, if there is a foreign key table. So first drop
foreign key table and then drop primary key table.

29. What do you mean by Self Referential Table?

Ans: Table in which Foreign key of that table refers to primary key of that same table

30. Explain in details usages of Set null, Cascade and Restrict

a) Set null: If user deletes or updates primary key values which are also available in
foreign key, then foreign key values are updated as null.
b) Cascade: If user deletes or updates primary key values, then simultaneously foreign
key values are too deleted or updated
c) Restrict: User cannot delete or update primary key values, if same values are
available in foreign key
31. How will you delete foreign key field which has named constraints
Ans: First named constraint for foreign key should be dropped by Alter table command
and then only Foreign key field can be dropped.
Ex:
a) Alter table teacher drop foreign key teacher_fk; - deletes only named constraint of
foreign key teacher_fk not foreign key field dept_no
b) Alter table teacher drop dept_no; - deletes foreign key field dept_no

32. How will you delete primary key field which has named constraints
Ans: Named constraint for primary key cannot be dropped but primary key field can be
dropped by Alter table command.
Ex:
a) Alter table teacher drop teacher_id; - deletes field teacher_id along with named
constraint teacher_pk.

33. Consider the following table PLAYER

a) Identity and write the name of the most appropriate column from the given table
PLAYER that can be used a Primary key
Ans: Primary key: PNO
b) Define the term Degree in relational data model. What is the Degree of the given
table PLAYER?
Ans: Degree: Number of columns of attributes or fields in a relation. Degree of table
Player is 3
34. Rohan is learning to work upon Relational Database Management System (RDBMS)
application. Help him to perform following tasks:
a) To open the database named “LIBRARY”.
Ans: USE LIBRARY;
b) To display the names of all the tables stored in the opened database.
Ans: SHOW TABLES;
c) To display the structure of the table “BOOKS” existing in the already opened
database “LIBRARY”
Ans: DESC BOOKS; or DESCRIBE BOOKS;
35. Navdeep creates a table RESULT with a set of records to maintain the marks secured
by students in Sem1, Sem2, Sem3 and their division. After creation of the table, he has
entered data of 7 students in the table.
Table: RESULT
Based on the data given above answer the following questions:
a) Identify the most appropriate column, which can be considered as Primary key.
Ans: ROLL_NO
b) If two columns are added and 2 rows are deleted from the table result, what will be
the new degree and cardinality of the above table?
Ans: New Degree: 8, New Cardinality: 5
c) Write the statement to insert the following record into the table – Roll No- 108,
Name- Aadit, Sem1- 470, Sem2-444, Sem3-475, Div – I.
Ans: insert into result values (108, ‘aadit’, 470, 444, 475, ‘i’);
d) Write the statement to increase the SEM2 marks of the students by 3% whose name
begins with ‘N’.
Ans: update result set sem2=sem2+ (sem2*0.03) where sname like “n%”;
e) Write the statement to delete the record of students securing IV division.
Ans: delete from result where division=’iv’;
f) Write the statement to add a column REMARKS in the table with data type as
varchar with 50 characters
Ans: alter table result add (remarks varchar(50));

36. A SQL table ITEMS contains the following columns: INO, INAME, QUANTITY,
PRICE, DISCOUNT
a) Write the SQL command to remove the column DISCOUNT from the table.
Ans: ALTER TABLE ITEMS DROP DISCOUNT;
b) Categorize the following SQL command into DDL and DML: CREATE, UPDATE,
INSERT, DROP
Ans: DDL – CREATE, DROP & DML – UPDATE, INSERT
37. Explain the use of ‘Foreign Key’ in a Relational Database Management System. Give
example to support your answer.
Ans: A foreign key is used to set or represent a relationship between two relations (or
tables) in a database. Its value is derived from the primary key attribute of another
relation.
Example: In the tables DEPARTMENT and TEACHER given below, dept_no in
DEPARTMENT TABLE is primary key in but dept_no in TEACHER table is foreign key
38. Differentiate between COUNT() and COUNT(*) functions in SQL with appropriate
example.
Ans: COUNT(*) returns the count of all rows in the table, whereas COUNT () is used with
Column_Name passed as argument and counts the number of non-NULL values in a given
column

39. Write the output of following SQL query based on the following two tables DOCTOR
and PATIENT belonging to the same database:
select pname, admdate, fees from patient p, doctor d where d.dno=p.dno and fees>1000;

Ans:

40. Write the Mysql commands for the following queries: -

a) To find all the stations, date for ticket id as T1.

Ans: select station, date from train, counter where trainid=trainno and
ticketid=’t1’;
 b) To find the total ticket amount collected from each station.
Ans: select sum(cost), station from train, counter where trainid = trainno group by
station;
c) To displays all the tables created in the current database.
Ans: show tables;
d) To delete table counter along with the information in it.
Ans: drop table counter;
41. Write SQL queries for (a) to (d) based in the tables PASSENGER and FLIGHT given
below:

a) Write a query to change the fare to 6000 of the flight whose FNO is F104
Ans: update flight set fare=6000 where fno=’f104’;
b) Write a query to display the total number of MALE and FEMALE PASSENGERS
Ans: select gender, count(*) from passenger group by gender;
c) Write a query to display the NAME, corresponding FARE and F_DATE of all
PASSENGERS who have a flight to START from DELHI.
Ans: select name, fare, f_date from passenger, flight where passenger.fno=flight.fno
and start=’delhi’;
d) Write a query to delete the records of flights which end at MUMBAI.
Ans: delete from flight where end=’mumbai’;

42. Write the output of the queries (a) to (d) based on the table, TECH_COURSE given
below:

a) select distinct tid from tech_course;

Ans:
 b) select tid, count(*), min(fees) from tech_course group by tid having count(tid)>1;
Ans:

c) select cname from tech_course where fees>15000 order by cname;

Ans:

d) select avg(fees) from tech_course where fees between 15000 and 17000;
Ans: 15500.00

43. Write the outputs of the SQL queries (a) to (d) based on the relations Teacher and
Placement given below:

Table: Placement
P_ID Department Place
1 History Ahmedabad
2 Mathematics Jaipur
3 Computer Sc Nagpur
a) select department, avg(salary) from teacher group by department;
Ans:

b) select max(date_of_join),min(date_of_join) from teacher;

Ans:

c) select name, salary, t.department, place from teacher t, placement p where

 t.department = p.department and salary>20000;
Ans:

d) select name, place from teacher t, placement p where gender='f' and

t.department=p.department;
Ans:
Name Place
Saman Ahmedabad
Samira Ahmedabad
Shalaka Jaipur
44. Write the Mysql commands for the following queries: -

(a) To find all the stations, date for ticket id as T1.

(b) To find the total ticket amount collected from each station.
(c) To displays all the tables created in the current database.
(d) To delete table counter along with the information in it.
Ans:
a) select station, date from train, counter where train.trainid=counter.trainno and
ticketid=”t1”;
b) select sum(cost), station from train, counter where train.trainid=counter.trainno
group by station;
c) show tables;
d) drop table counter;

45. Write the output of SQL queries (a) to (d) based on the table VACCINATION_DATA
given below:
Ans:
(a)
Name Age
Harjot 55
Srikanth 43
(b)
City Count(*)
Delhi 2
Mumbai 2
Kolkatta 1
(c)
DISTINCT CITY
Delhi
Mumbai
Kolkatta
(d)
MAX(Dose1) MIN(Dose2)
2022-01-01 2021-07-20

46. Write SQL queries for (a) to (d) based in the tables PASSENGER and FLIGHT given
below:
 (a) Write a query to change the fare to 6000 of the flight whose FNO is F104
(b) Write a query to display the total number of MALE and FEMALE PASSENGERS
(c) Write a query to display the NAME, corresponding FARE and F_DATE of all
PASSENGERS who have a flight to START from DELHI.
(d) Write a query to delete the records of flights which end at MUMBAI.
Ans:
(a) update flight set fare=6000 where fno=’f104’;
(b) select gender, count(*) from passenger group by gender;
(c) select name, fare, f_date from flight f, passenger p where p.fno=f.fno and
start=delhi;
(d) delete from flight where end=’mumbai’;

47. What is the purpose of HAVING Clause? How is it different from WHERE clause in
SQL? Give example to support your answer.
Ans:
Having: To apply some condition on the group of values of attribute, Having clause is used.
To apply condition on whole attribute, Where clause is used.
Example:
Tutor:
Tutor_id first_name salary dept_id
1001 Venkat 6000 101
1002 Guna 7000 102
1003 Selva 5000 103
1004 Bilkees 8000 103
1005 Edwin 9000 102

To find first_names of all tutors who are getting salary more than 5000, use

select first_name from tutor where salary>5000;

To find dept_ids which has more than one tutors, use

select dept_id, count(*) from tutor group by dept_id having count(*) > 1;