CHAPTER 1 - DATABASE CONCEPTS

5 Years Question Bank

1. The number of attributes in a relation is called the-------------of a relation.
a) Tuple b) Table C) Cardinality d) Degree
2. In order to restrict the values of an attribute within a range,---------constraint to be used.
a) Check b) NULL c) Default d)NOT NULL
3. command is used to eliminate duplicate values of an attribute in the select query.

a) Update b) Unique c) Distinct d) No Duplicate

4. command to used to delete the structure of the table.

a) Drop table b) Drop database c) Delete Table d) Alter Table

5. Sana has given the following query but she is unable to execute it successfully. Write
the correct query after rectifying the error.
Select Name, Marks from Student Table Where Name =”p%
Answer: Select Name, Marks from Student Table Where Name =”p%”;
6. is a collection of raw facts which have not been processed to reveal useful
information.
Answer: Data
7. constraint is used to restrict the values of an attribute within a range.

Answer: Check
8. command is used to modify the base table definition.

a) Create Table b) Alter Table c) Update Table d) Modify Table

9. command is used to modify the value of attribute of one or more tuple in a
table.
a) Update b) Modify c) Alter d) Create
10. Keyword used to display the structure of the table.
a) Describe b) show c) Select d) Display
11----------------constraint is used to restrict the values of an attribute within a range.
Answer: Check
12. SELECT DISTINCT DEPT_NAME FROM DEPARTMENT;
 What does the above query do?
a) It gives all the tuples having dept_name
b) It gives the dept_name attribute values of all tuples without repetition.
c) It gives all the dept_name attribute of all tuples
13 is used to apply condition with GROUP BY

a) HAVING b) WHERE c) ORDER BY d) APPLY

14 is not an aggregate function.

a) Sum( ) b) Count( ) c) Round ( ) d) Avg ( )

15 command which displays list of databases available on the current server.

a) Show databases b) Show tables c) Create tables d) Display tables

16 replaces zero or more number of random character, when used with Like
keyword.
a) _ b) % c) $ d) @
17. Which SQL Keyword is used to sort the result?
a) ORDER BY b) SORT c) ORDER d) SORT BY
18-------------commands deletes the tuples from a Table.
a) Drop b) Delete c) Modify d) None of the above
19. Which SQL keyword is used to display the data based on certain pattern?
a) LIKE b) IN c) BETWEEN d) RANGE
20. Sagun is creating a table SALESMAN with fields Salesman number, Name, Total Sales.
She doesn’t want the Total Sales column to be remain unfilled i.e. she wants to make entry
in this field mandatory. Which constraint she should use at the time of creating
SALESMAN table:
a) Check b) Default c) Not Null d) Primary Key
21. Ranjana wants to delete the records where name starts with ‘A’ from the table
CUSTOMER having fields Name, Amount, Discount. Identify the correct command.
a) Delete CUSTOMER where name like “A%”;
b) Delete from CUSTOMER where name like “A%”;
c) Delete CUSTOMER where name = “A%”;
d) Delete from CUSTOMER where name like “A%”;
22. The---------command is used to modify the structure of the table Student in MySQL.
a) MODIFY TABLE STUDENT b) ALTER TABLE STUDENT c)
ALTER STUDENT d) MODIFY STUDENT
23. Consider a table FACTORY
YEAR PRODUCTION
2008 12000
2009 10000
2010 10600
2011 15000
2012 14500

Reema wants to calculate the average production of the years 2008-2012. Help her select
the correct SQL command for it:
a) SELECT AVERAGE(PRODUCTION) FROM FACTORY;
b) SELECT AVERAGE(PRODUCTION) FROM FACTORY GROUP
BY PRODUCTION;
c) SELECT SUM(PRODUCTION) FROM FACTORY;
d) SELECT AVG(PRODUCTION) FROM FACTORY;
24. Garvit wants to change the default value of DISCOUNT in the CUSTOMER table from
10 to 15. Select the most appropriate command to do the same from the following options:
a) ALTER TABLE CUSTOMER ALTER DISCOUNT SET DEFAULT 15;
b) ALTER TABLE CUSTOMER DELETE DISCOUNT SET DEFAULT 15;
c) ALTER TABLE CUSTOMER MODIFY DISCOUNT SET DEFAULT 15;
d) ALTER TABLE CUSTOMER CHANGE DISCOUNT SET DEFAULT 15;
25. Consider a table: COACHING having fields CITY_NAME, ENROLMENTS, Shikhar
wants to display the data of the above table in ascending order of enrolments. Choose the
correct query/queries from the following:
i) Select * from coaching order by enrolments asc;
ii) Select * from coaching order by enrolments desc;
iii) Select * from coaching order by enrolments;
iv) Select * from coaching order by enrolments
ascending; Select the Option:
 a) Only (i) is correct
b) Both (i) and (ii) are correct
c) Both (i) and (iii) are correct
d) Only (iv) is correct
26. Geeta wants to know the usage of NULL in MySQL. Help her choose in which of
the following case NULL value cannot be assigned to the column
Admission_Number:
a) When the Admission_Number is Zero
b) When the Admission_Number is not known
c) When the Admission_Number is not available
d) When the Admission_Number is not applicable
27. Which of the following is NOT an advantage of DBMS approach?
a) All the data is stored at one place. There is no repetition of the same data
b) There is a single copy of data that is accessed or updated by all the users.
c) The DBA can provide security to the database by controlling user’s database access
rights.
d) Designing the database specifications and writing application program is a time-
consuming process.
28. An attribute A of datatype varchar(20) has the value “keshav”. The attribute B of
datatype char(20) has value “Meenakshi”. How many characters are occupied in attribute
A and attribute B?
a) 20, 6 b) 6, 20 c) 9, 6 d) 6,9
29. Consider the table ‘empsal’.
ID Salary Dept
Q101 54000 10
Q103 67000 20
Q105 75000 20
Q109 NULL 30

Read the following two commands carefully:

i) SELECT COUNT(SALARY) FROM EMPSAL;
ii) SELECT COUNT(*) FROM EMPSAL;
Choose the correct option:
 a) Both i and ii will give the same output as 3
b) The output of i is 3 and the output of ii is 4.
c) Both i and ii will give the same output as 4
d) The output of i is 4 and the output of ii is 3
30. Cardinality of a table is four and degree is three. If two columns and four rows are
added to the table what will be the new degree and cardinality of the given table:
a) 5,8 b) 3,4 c) 8,5 d) 4,3
31. Consider a table ‘STORE AND OWNER shown below’
Table- STORE
StoreID StoreName Location NoOfEmployee OpeningYear OwnerId
S101 Libas Delhi 45 2007 A145
S102 Life Style Mumbai 68 2000 B178
S103 Vogue Mumbai 50 1999 B178
S104 Pantaloon Chennai 78 2004 D112
S105 Max Fashions Delhi 80 2020 A145

Table – OWNER
OwnerId OwnerName
A145 Divyam Prakash
B178 Neha Sultan
D112 Gargi Chhabra

Choose the correct query to display the number of employees present in Delhi and
Mumbai.
i) Select SUM(noof employee), Location from STORE group by location
HAVING location in (“Delhi”, “Mumbai”);
ii) Select SUM(noof employee), Location from STORE group by location WHERE
location in (“Delhi”, “Mumbai”);
iii) Select SUM(noof employee), Location from STORE group by location
HAVING location =“Delhi” and “Mumbai”;
iv) Select SUM(noof employee), Location from STORE group by location WHERE
location =“Delhi” or “Mumbai”;
Choose the correct option
a) Both i and ii b) Both ii and iii c) Both iii and iv d) Both i and iii
32. Consider the table STORE given in above question(31). Choose the correct query to
display Stores names, Location and Date of Opening of stores that were opened
before 2015.
a) Select storename, location, OpeningYear from store where OpeningYear <
”2015”;
b) Select storename, location, OpeningYear from store where OpeningYear > ”2015”;
c) Select storename, location, OpeningYear from store where OpeningYear =”2015”;
d) Select storename, location, OpeningYear from store where OpeningYear != ”2015”;
33. Consider the table STORE given in Q31. Radhika wants to add a new column SALE
on type integer to the table. Choose the correct query for the same.
a) Add column SALE integer;
b) Alter table add SALE integer;
c) Alter table store add SALE integer;
d) Alter table store modify SALE integer
34. Consider the table STORE and OWNER given in Q31. Seema wants to display
StoreName and OwnerName of all stores in Mumbai. Help her select the correct query
from the options given below:
a) SELECT STORENAME, OWNERNAME FROM STORE, OWNER WHERE
STORE.OWNERID = OWNER.OWNERID OR LOCATION = “MUMBAI”;
b) SELECT STORENAME, OWNERNAME FROM STORE, OWNER WHERE
STORE.OWNERID = OWNER.OWNERID AND LOCATION = “MUMBAI”;
c) SELECT STORENAME, OWNERNAME FROM STORE, OWNER WHERE
LOCATION = “MUMBAI”;
d) SELECT STORENAME, OWNERNAME FROM STORE, OWNER WHERE
OWNERID = OWNERID AND LOCATION = “MUMBAI”;
35. Consider the table STORE given in Q31. Radhika wants to display the following output.
STOREID STORENAME NOOFEMPLOYEE
S101 Libas 45
S102 Life Style 68

Choose the correct command:

 i) SELECT STOREID, STORENAME, NOOFEMPLOYEE FROM
STORE WHERE STOREID=102 OR STOREID = 101;
ii) SELECT STOREID, STORENAME, NOOFEMPLOYEE FROM STORE
WHERE STORENAME = “LIBAS” OR STORENAME=”LIFE
STYLE”;
iii) SELECT STOREID, STORENAME, NOOFEMPLOYEE FROM
STORE WHERE LOCATION = “DELHI” OR LOCATION
=”MUMBAI”;
iv) SELECT STOREID, STORENAME, NOOFEMPLOYEE FROM
STORE WHERE STORENAME LIKE “L%”;
Choose the correct option:
a) i and ii are correct b) i, ii and iii are correct
c) i, ii, and iv are correct d) All are correct
36. Which of the following is true for self- referencing table?
a) A foreign key constraint can reference columns within the same table.
b) A primary key constraint can be null.
c) You cannot delete this table.
d) You cannot update or delete this table.
37. Identify the operator that performs pattern searching in MYSQL.
a) EXISTS operator b) BETWEEN operator
c) LIKE operator d) SEARH operator
38. Which of the following queries will give the same output.
a) SELCT ROLLNO, NAME FROM STUDENT WHERE SUBJECT IN
(‘INFORMATION TECHNOLOGY’, ‘INFORMATICS
PRACTICES’);
b) SELECT ROLLNO, NAME FROM STUDENT WHERE SUBJECT BETWEEN
‘INFORMATION TECHNOLOGY’ AND ‘INFORMATICS PRACTICES’;
c) SELECT ROLLNO, NAME FROM STUDENT WHERE
SUBJECT=’INFORMATION TECHNOLOGY’ OR
SUBJECT=’INFORMATICS PRACTICES’;
d) SELECT ROLLNO, NAME FROM STUDENT WHERE
SUBJECT=’INFORMATION TECHNOLOGY’ AND
SUBJECT=’INFORMATICS PRATICES’;
Answer: (a) and (c )
39. In the table teacher, Riya doesn’t want repeated values in column marks. Rewrite
the correct command.
 SELECT MARKS FROM TEACHER;
Answer: SELECT DISTINCT MARKS FROM TEACHER;
OR
SELECT DISTINT(MARKS) FROM TEACHER;
40. Reema wants to input the price of shoes as 999.99. What should be the dataype and size of
the column price in the table?
Answer: DECIMAL(5,2)
41. What is the MYSQL command to list the names of teachers in alphabetical order in the
teacher table?
Answer: SELECT NAME FROM TEACHER ORDER BY NAME;
42. Write the command to see the structure of a table?
Answer: DESC or DESCRIBE
43. The following commands are giving errors. Write the correct MYSQL commands.
a) ALTER TABLE STUDENT DELETE MARKS;
b) SELECT * FROM EMPLOYEE WHERE NAME=’%S%’;
Answer: a) ALTER TABLE STUDENT DROP MARKS;
b) SELECT * FROM EMPLOYEE WHERE NAME LIKE ’%S%’;
44. Ms. Prabha has mistakenly entered “AMIT” instead of “AMITA” in name field of table
‘TEACHER’. Help to write the correct SQL command to make the desired changes in
the table.
Answer: UPDATE TEACHER SET NAME = ‘AMITA’ WHERE NAME =’AMIT’;
45. Which aggregate function is used to find the average of all the values for a
selected attribute of a table.
Answer: AVG( )
46. Consider a table STAFF:
Name City
Akash Mumbai
Ajay Chennai
Banit Delhi
Fauzia Mumbai
Sakal Chennai
 Select the command which will count the number of people in each city.
a) SELECT COUNT(City), City FROM STAFF WHERE GROUP BY City;
b) SELECT COUNT(City), City FROM STAFF GROUP BY City;
c) SELECT COUNT(City), City FROM STAFF WHERE ORDER BY City;
d) SELECT COUNT(City), City FROM STAFF ORDER BY City;
47. Consider the Table EMPLOYEE given below:
Name Zone Age Dept
Harit West 28 10
Kritika Centre 30 10
Naveen East 40 20
Uday North 38 30
Nupur East 24 20
Moksh South 28 10
Shelly North 24 30

Choose the correct query/ queries that will give the following output;
Name
Harit
Kritika
Moksh

i) SELECT NAME FROM EMPLOYEE WHERE DEPT IN(10);

ii) SELECT NAME FROM EMPLOYEE WHERE AGE BETWEEN 25 AND 30;
iii) SELECT NAME FROM EMPLOYEE WHERE NAME LIKE ‘%I%’;
iv) SELECT NAME FROM EMPLOYEE WHERE ZONE IN (“WEST”, “CENTRE”,
“SOUTH”);
Choose the correct option
a) Only (i) b) Both (i) and (ii) c) Both (ii) and (iv) d) All (i), (ii) and (iv)
48.

49. Give any two characteristics of a relational

model. Answer: In relational model,
1. A column is called an Attribute.
 2. The value in each tuple is an atomic value.
50. Name two domains where database management may be used.
Answer: Railway Reservation, Banking System, Store Management, Education System
etc.
51. List any two advantages of DBMS.
Answer: Reduction in Redundancy, Improved Consistency, Improved Availability,
Sharing of data, Improved Security, User friendly.
52. While entering the data in the table, Seema is not able to leave the column age as blank in
the table. What can be possible reason?
Answer: Because she has set NOT NULL constraint in age column.
53. Difference between WHERE and HAVING clause.
Answer: WHERE clause is used for selecting the rows based on the condition applied on
rows. While HAVING clauses used to select the rows from the data given by group by
group by clause in SQL.
54. Differentiate between DDL and DML.
DDL
(i) DDL is a language which is used to define structure and constraints of data.
(ii) DDL – Create table, Alter table, Drop table.
DML
(i) DML is used to insert, modify and delete data in a database.
(ii) DML – Insert, Update, Delete
55. Consider the following table Doctor and answer the following Queries in SQL.
ID Department OPD_DAYS Doctor_Name Fee

H201 ENT MWF Akaash Arora 400

H308 Oncology TTS Dharma Sharma 600
H907 Pediatrics MWF Sanjay Singh 500
H896 ENT TTS Praveen Sethi 400
H675 Gynecology TTS Kavita Sharma 450
H357 Hematology MWF Dinesh Chaudhry 600
i. To display the name of all Doctors who visit OPD on ‘MWF’
 ii. To display the all the details of Doctors whose Department ends with ‘y’
iii. To count the number of Doctors in ENT Department.
iv. To display names and the fee of doctors in the descending order of fee.
v. To add a new column Phone No to a table Doctor.
Answer:
i. SELECT DOCTOR_NAME FROM DOCTOR WHERE OPD_DAYS =
“MWF”;
ii. SELECT * FROM DOCTOR WHERE DEPARTMENT LIKE ‘%Y’;
iii. SELECT COUNT(*) FROM DOCTOR WHERE DEPARTMENT =’ENT’;
iv. SELECT DOCTOR_NAME, FEE FROM DOCTOR ORDER BY FEE
DESC;
v. ALTER TABLE DOCTOR ADD PHONE_NO CHAR(10);
56. Illustrate any two DML commands with the help of suitable example.
 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 data from a table.
Example:
DELETE FROM Student Where admno=101;
57. List any four advantages of DBMS .
Answer: Following are the
advantages
1. Reduction in Redundancy
2. Improved Consistency
3. Improved Availability
4. Improved Security
5. User Friendly
58. Explain the Concept of Candidate Key with the help of a suitable example.
Answer: A Candidate key is a column, or set of columns, in a table that can uniquely
identifies a tuple and is not null. Each table may have one or more candidate keys. It is
eligible to be a primary key.
For example: In a table PERSON following are the columns
(Aadhar_number, PAN, Voter_ID_cardno, Name, Date_of_birth, Address);
Out of these Aadhar_number, PAN, Voter_ID_cardno, are the candidate keys.
59. What is the purpose of group by command? How is it different from Order
by command? Give example.
Answer: The GROUP BY statement is used with aggregate functions (COUNT, MAX,
MIN, SUM, AVG) to group the result-set by one or more columns.
The ORDER BY keyword is used to sort the result-set in ascending or descending order.
Example of Group By
SELECT Dept_No, COUNT(*) AS No_of_Teachers FROM Teacher GROUP BY
Dept_No;
Example of Order By
SELECT First_Name, Last_Name FROM Teacher ORDER BY First_Name;
60. Radha has given the following query, she is unable to run due to errors, rectify
the errors and underline the correction made.
a) Select unique department from employee;
Select distinct department from
employee;
b) Select * from employee where name=”%a”;
Select * from employee where name like “%a’;
c) Select name, salary from employee where phone=Null;
Select name, salary from employee where phone IS
Null;
61. Consider the following and answer the questions
a) Write a query to create a table TravelAgency.
AgentCode Char(4) Primary Key
 AgentName Varchar(20) Not Null
Location Varchar(25)
Package Varchar(30)
Charges Decimal(10,2)

Answer:
Create table
TravelAgency( Agentcode
char(4) primary key, AgentName
varchar(20), Location
Varchar(25),
Package Varchar(30),
Charges decimal(10,2)
);
b) Write a query to add a new row to a table TravelAgency.
(“A006”, “John Sharon”, “Sam Ana Travel”, “Domestic”, 45000)
Answer: 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. Answer: ALTER TABLE TravelAgency ADD EmailId
VARCHAR(30);
62. Define Referential Integrity.
Answer: Referential Integrity is used to check the data entered in one relation is
consistent with the data entered in another relation
63. Explain Drop table with Cascade command.
Answer: Drop Table will be dropped and with the CASCADE option i.e. all the
constraints that refer this table would also be automatically dropped.
Example: DROP TABLE TEACHER CASCADE;
64. A company is making database of Employees having personal details and Salary details
table. Personal details table have columns Empid, Name, Address, BloodGroup. Suggest
 Columns for Salary details Table.
Answer: Empid, Basic, Leave, Project_details, Deductions, Allowances
65. Define Data Redundancy.
 Answer: Same information is stored in more than one file which result in wastage of
space.
66. Mention at least 2 work areas which uses Database Management Systems.
Answer: School Management, Hotel Management, Railway reservation, Employee
management
67. Jatin has given the following query from the table Student but he is unable to execute it
successfully. Write the correct query after rectifying the error.
SELECT NAME, MARKS FROM STUDENT TABLE WHERE NAME =”p%”
Answer: SELECT NAME, MARKS FROM STUDENT TABLE WHERE NAME
LIKE =”p%”;
68. Consider the following table and write SQL statements.
Table – Menu
ID ITEM DESCRIPTION PRICE
P001 Sambar Vada South Indian 40
P002 Dal Maakhani North Indian 150
P003 Noodles Chineese 100
P004 Idli Sambar South Indian 50
P006 Sarson Saag North Indian 170
P007 Dosa South Indian 120
P008 Pizza Italian 200

(i) To add a new row with the following data “P009”, “”Lemon Rice”, “South Indian”, 140
(ii) Display details of all Menu items in the descending order of Price
(iii) Display Item and Description of those items whose price is between 100 and 150.

(iv) Cardinality and Degree of the table MENU

(v) Add a new column Rating datatype as int to the table Menu.
Answer
(i) INSERT INTO MENU VALUES(“P009”, “LEMON RICE”, “SOUTH INDIAN”, 140);
(ii) SELECT * FROM MENU ORDER BY PRICE;
(iii)SELECT ITEM, DESCRIPTION FROM MENU WHERE PRICE BETWEEN 100
AND 150;
(iv)Cardinality – 7 Degree – 4
(v) ALTER TABLE MENU ADD RATING INT;
69. Write the Mysql commands for the following queries:
TABLE – TRAIN
TrainID Station
0001 DELHI
0002 MUMBAI

TABLE – COUNTER
TicketID Date Cost TrainNo
T1 12/3/22 500 0001
T2 15/5/22 450 0002
T3 15/5/22 500 0001

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

b) To find the total ticker 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.
Answer
a) SELECT, DATE FROM TRAIN, COUNTER WHERE TRAIN.TrainID =
COUNTERR.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;