AMITY INTERNATIONAL SCHOOL, MAYUR VIHAR

CLASS XII
CBSE SOLVED QUESTION BANK
DATABASE CONCEPTS AND SQL

1) Which SQL command can change the degree of an existing relation?

Ans) Alter

2) What will be the output of the query?

SELECT * FROM products WHERE product_name LIKE 'App%';
(A) Details of all products whose names start with 'App'
(B) Details of all products whose names end with 'App'
(C)Names of all products whose names start with 'App'
(D)Names of all products whose names end with 'App'
Ans) A

3) In which datatype the value stored is padded with spaces to fit the specified
length. (A) DATE (B) VARCHAR (C) FLOAT (D) CHAR
Ans) D

4) Which aggregate function can be used to find the cardinality of a table?

(A) sum() (B) count() (C)avg() (D)max()
Ans) B
5) Assertion (A): A SELECT command in SQL can have both WHERE and HAVING
clauses.
Reasoning (R): WHERE and HAVING clauses are used to check conditions,
therefore, these can be used interchangeably.
Ans) A is True but R is False
6) In a table in MYSQL database, 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 by attribute A and attribute B?
a. 20,6 b. 6,20 c. 9,6 d. 6,9
Ans) Option b 6,20
7) In MYSQL database, if a table, Alpha has degree 5 and cardinality 3, and another
table, Beta has degree 3 and cardinality 5, what will be the degree and
cardinality of the Cartesian product of Alpha and Beta?
a. 5,3 b. 8,15 c. 3,5 d. 15,8
Ans) Option b 8,15
8) Which of the following statements is FALSE about keys in a relational
database? a. Any candidate key is eligible to become a primary key.
b. A primary key uniquely identifies the tuples in a relation.
c. A candidate key that is not a primary key is a foreign key.
d. A foreign key is an attribute whose value is derived from the primary key of
another relation
 Ans) Option c A candidate key that is not a primary key is a foreign key
9) Define the term Domain with respect to RDBMS. Give one example to support
your answer.
Ans) Domain is a set of values from which an attribute can take value in
each row. For example, roll no field can have only integer values and so its
domain is a set of integer values

10) Give one difference between alternate key and candidate key.
Ans) All keys that have the properties to become a primary key are candidate
keys. The candidate keys that do not become primary keys are alternate keys.

11) Fill in the blank: ______ command is used to remove primary key from the
table in SQL. (a) update (b)remove (c) alter (d)drop
Ans) c

12) Which of the following commands will delete the table from MYSQL
database? (a) DELETE TABLE (b) DROP TABLE (c) REMOVE TABLE (d) ALTER
TABLE
Ans) b

13) Fill in the blank: _________ is a non-key attribute, whose values are
derived from the primary key of some other table.
(a) Primary Key (b) Foreign Key (c) Candidate Key (d) Alternate key
Ans) b

14) Fill in the blank: The SELECT statement when combined with __________
clause, returns records without repetition. (a) DESCRIBE (b) UNIQUE (c)
DISTINCT (d) NULL
Ans) c

15) Which function is used to display the total number of records from table
in a database? (a) sum(*) (b) total(*) (c) count(*) (d) return(*)
Ans) c

16) Which SQL operator performs pattern matching?

a. BETWEEN operator b. LIKE operator c. EXISTS operator d. =
Ans) b

17) The primary key is selected from the set of ___________

(A) composite keys (B) alternate keys
(C) candidate keys (D) foreign keys
Ans) C

18) In SQL, which command will be used to add a new record in a table ?
 (A) UPDATE (B) ADD (C) INSERT (D) ALTER TABLE
Ans) D

19)Mr. Ravi is creating a field that contains alphanumeric values and fixed
lengths. Which MySQL data type should he choose for the same ? 1
(A) VARCHAR (B) CHAR (C) LONG (D) NUMBER
Ans) B

20) Fill in the blank :

In a relational model, tables are called _________, that store data for
different columns.
(a) Attributes(b) Degrees (c) Relations(d) Tuple
Ans) c

2 mark questions

21) A) What constraint should be applied on a table column so that duplicate values
are not allowed in that column, but NULL is allowed.
Ans) Unique
B) Write an SQL command to remove the Primary Key constraint from a table,
named MOBILE. M_ID is the primary key of the table
Ans) alter table mobile drop primary key;

22)A) What constraint should be applied on a table column so that NULL is not
allowed in that column, but duplicate values are allowed.
Ans) not null
B) Write an SQL command to make the column M_ID the Primary Key of an
already existing table, named MOBILE.
Ans) alter table mobile add primary key (M_ID);

23)Ms. Shalini has just created a table named “Employee” containing columns
Ename, Department and Salary. After creating the table, she realized that she
has forgotten to add a primary key column in the table. Help her in writing an
SQL command to add a primary key column EmpId of integer type to the table
Employee. Thereafter, write the command to insert the following record in the
table: EmpId- 999 Ename- Shweta Department: Production Salary: 26900
Ans) ALTER TABLE Employee ADD EmpId INTEGER PRIMARY KEY;
INSERT INTO Employee VALUES("Shweta","Production",26900,999);
INSERT INTO Employee(EmpId,Ename,Department,Salary)
VALUES(999,"Shweta","Production",26900);

24)Zack is working in a database named SPORT, in which he has created a table

named “Sports” containing columns SportId, SportName, no_of_players, and
category.
 After creating the table, he realized that the attribute, category has to be deleted
from the table and a new attribute TypeSport of data type string has to be
added. This attribute TypeSport cannot be left blank. Help Zack write the
commands to complete both the tasks.
Ans) To delete the attribute, category: ALTER TABLE Sports DROP category;
To add the attribute, TypeSport
ALTER TABLE Sports ADD TypeSport char(10) NOT NULL;

25)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. For example: In the tables TRAINER and
COURSE given below, TID is primary key in TRAINER table but foreign key in
COURSE table.

26)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 column that is given as argument.
In below table select count(*) from empl; returns 5 but select count(job)
from empl returns 3

27)Categorize the following commands as DDL or DML: INSERT, UPDATE, ALTER,

DROP
Ans) DDL- ALTER, DROP DML – INSERT, UPDATE

28)Differentiate between char(n) and varchar(n) data types with respect to

databases.
 Ans) char(n): • stores a fixed length string between 1 and 255 characters • if the
value is of smaller length, adds blank spaces • some space is wasted
varchar(n) : • stores a variable length string • no blanks are added even if value
is of smaller length • no wastage of space

29)Write the output of the queries (a) to (d) based on the table, Furniture given
below:

a) SELECT SUM(DISCOUNT) FROM FURNITURE WHERE COST>15000;

Ans) 29
b) SELECT MAX(DATEOFPURCHASE) FROM FURNITURE;
Ans) 19-Jul-2021
c) SELECT * FROM FURNITURE WHERE DISCOUNT>5 AND FID LIKE "T%";
Ans) T006 Console Table 17-Nov2019 15000 12
d) SELECT DATEOFPURCHASE FROM FURNITURE WHERE NAME IN ("Dining
Table", "Console Table");
Ans) DateofPurchase
10-Mar- 2020
17-Nov-2019

30)(i) Which command is used to view the list of tables in a database?

Ans) show tables
(ii) Give one point of difference between an equi-join and a natural join
Ans) Equi- join: • The join in which columns from two tables are compared
for equality • Duplicate columns are shown Natural Join • The join in which
only one of the identical columns existing in both tables is present • No
duplication of columns

31)Consider the table, MOVIEDETAILS given below:

 (a) Identify the degree and cardinality of the table.
Ans) Degree: 5 Cardinality: 6
(b) Which field should be made the primary key? Justify your answer.
Ans MOVIEID should be made the primary key as it uniquely identifies each record
of the table.

OR

(a) Identify the candidate key(s) from the table MOVIEDETAILS.

Ans) MOVIEID and TITLE
(b) Consider the table SCHEDULE given below

Which field will be considered as the foreign key if the tables MOVIEDETAILS
and SCHEDULE are related in a database?
Ans) MOVIEID

32)I) A table, ITEM has been created in a database with the following fields:
ITEMCODE, ITEMNAME, QTY, PRICE Give the SQL command to add a new
field, DISCOUNT (of type Integer) to the ITEM table.
Ans) ALTER TABLE Item ADD (Discount INT);
ii) Categorize following commands into DDL and DML commands? INSERT
INTO, DROP TABLE, ALTER TABLE, UPDATE...SET
Ans) DDL: DROP TABLE, ALTER TABLE DML: INSERT INTO, UPDATE. ..SET

33)Differentiate between % (percentage) and _(underscore) characters used with

the LIKE operator in SQL with appropriate examples.
Ans) % (Percentage):
• Matches any sequence of characters (including empty sequence).
• Example: LIKE 'T%' matches all those strings starting with the letter 'T'. The
string with just 1 character 'T' will also be considered.
_ (Underscore):
• Matches a single character.
 • Example: LIKE '_ _T' on the other hand will search for a three letter string,
whose 3rd letter is 'T'. At first two places any two character can appear

34)Differentiate between DROP and DELETE commands in SQL with appropriate

examples
Ans) DROP is a DDL command in SQL and can be used to remove tables (or
database).
Example: 'DROP TABLE STUDENT;' will remove the table STUDENT from the
database.
DELETE is a DML command used to remove or delete rows/records from a table.
Example: 'DELETE FROM STUDENT WHERE PER < 33;' will remove all those
records from the table STUDENT where the percentage is less than 33.

35)Consider the following two commands with reference to a table, named

Employee having a column named Department:
(a) Select count(Department) from Employee;
(b) Select count(*) from Employee; If these two commands are producing
different results,
(i) What may be the possible reason?
(ii) Which command (a) or (b) might be giving a higher value?
Ans) • COUNT(*) returns the count of all rows in the table, whereas COUNT() is used
with Column_Name passed as an argument and counts the number of non-NULL
values in a column that is given as an argument. Hence the result may differ.
• The SQL command with COUNT(*) may have higher value as it count all rows in the
table.

36)Write the output of the SQL queries (a) to (d) based on the table TRAVEL
given below :

i) SELECT START, END FROM TRAVEL WHERE FARE <= 4000 ;

Ans)
Start End
Delhi Bengaluru

ii) SELECT T_ID, FARE FROM TRAVEL WHERE T_DATE LIKE '2021-12-%' ;
Ans)
 T_ID Fare
101 4500
102 4000

iii) SELECT T_ID, T_DATE FROM TRAVEL WHERE END = 'CHENNAI'ORDER BY

FARE ;
Ans)
T_ID T_Date
101 2021-12-25
103 2020-12-10

iv) SELECT START, MIN(FARE) FROM TRAVEL GROUP BY START ;

Ans)
Start Min(Fare)
Delhi 4000
Mumbai 5000

37)Write the output of the SQL queries (a) and (b) based on the following two
tables FLIGHT and PASSENGER belonging to the same database :

a) SELECT NAME, DEPART FROM FLIGHTNATURAL JOIN PASSENGER ;

Name Depart
Prakash Delhi
Noor Mumbai
Annie Mumbai

b) SELECT NAME, FARE FROM PASSENGER P, FLIGHT F WHERE F.FNO = P.FNO

AND F.DEPART = 'MUMBAI' ;
Ans)
Name Fare
Noor 5500
Annie 5000

38)Explain Primary Key in the context of Relational Database Model.

Support your answer with suitable example.
Ans) A primary key in a table that uniquely identifies each row and column or set
of columns in the table.
Example PNO is the primary key in the below table
Table : BATSMEN
PNO NAME SCORE
P1 RISHABH 52
P2 HUSSAIN 45
P3 ARNOLD 23

39)Consider the table

(i) Identify and write the name of the Candidate Keys in the given table BATSMEN.
Ans) PNO and Name
(ii) How many tuples are there in the given table BATSMEN ?
Ans) 5

40)Mr. Atharva is given a task to create a database, Admin. He has to

create a table, users in the database with the following columns :
User_id – int
User_name – varchar(20)
Password – varchar(10)
Help him by writing SQL queries for both tasks.
Ans)
Create database Admin;
Use admin;
Create table users(user_id int, user_name varchar(20), password varchar(10));

41)Ms. Rita is a database administrator at a school. She is working on the table,

student containing the columns like Stud_id, Name, Class and Stream. She has
been asked by the Principal to strike off the record of a student named Rahul
with student_id as 100 from the school records and add another student who
has been admitted with the following details :
Stud_id – 123
Name – Rajeev
Class – 12
Stream – Science
Help her by writing SQL queries for both tasks.
Ans) delete from student where stud_id=100;
Insert into student values(123,”Rajeev”,12,”Science”);
 42)Explain the usage of HAVING clause in GROUP BY command in RDBMS with
the help of an example.
Ans) The HAVING clause was introduced in SQL to allow the filtering of query results
based on aggregate functions and groupings
Example in the below table
If Department wise max salary has to be queried for only those departments having
max salary>45000 then the query will be
Select department, max(salary) from employee group by department having
max(salary)>45000;

43) Differentiate between IN and BETWEEN operators in SQL with

appropriate examples.
Ans) The IN Operator in SQL is used to specify multiple values/sub-queries in
the WHERE clause.
Example
SELECT Fname, Lname FROM employee
WHERE Address IN ('Delhi','Himachal');
The BETWEEN operator is used to filter results that fall within a specified
range of values such as numbers, dates or text while the IN operator is used
to filter results based on a list of specific values.
SELECT Name FROM Emp WHERE Salary BETWEEN 30000 AND 45000;

3 mark questions

44)Consider the table CLUB given below and write the output of the SQL queries
that follow
(i) SELECT COUNT(DISTINCT SPORTS) FROM CLUB;
Count(distinct(sports))
4
(ii) SELECT CNAME, SPORTS FROM CLUB WHERE DOAPP<"2006-04-30"
AND CNAME LIKE "%NA";
CNAME SPORTS
AMINA CHESS
(iii) SELECT CNAME, AGE, PAY FROM CLUB WHERE GENDER = "MALE" AND
PAY BETWEEN 1000 AND 1200;
CNAME AGE PAY
AMRIT 28 1000
VIRAT 35 1050

45)Consider the table Personal given below: Table: Personal

Based on the given table, write SQL queries for the following:
(i) Increase the salary by 5% of personals whose allowance is known.
Ans) UPDATE Personal SET Salary=Salary + Salary*0.5 WHERE
Allowance IS NOT NULL;
(ii) Display Name and Total Salary (sum of Salary and Allowance) of all
personals. The column heading ‘Total Salary’ should also be displayed.
Ans) SELECT Name, Salary + Allowance AS "Total Salary" FROM Personal;
(iii) Delete the record of personals who have salary greater than 25000
Ans) DELETE FROM Personal WHERE Salary>25000;

46)a) Consider the following tables – Bank_Account and Branch:

What will be the output of the following statement?

SELECT * FROM Bank_Account NATURAL JOIN Branch;
(b) Write the output of the queries (i) to (iv)
based on the table, TECH_COURSE given
below:

(i) SELECT DISTINCT TID FROM

TECH_COURSE;
Ans) DISTINCT TID
101
NULL
102
104
103

(ii) SELECT TID, COUNT(*), MIN(FEES) FROM TECH_COURSE GROUP BY TID

HAVING COUNT(TID)>1;
Ans)
TID COUNT(*) MIN(FEES)
101 12000

(iii)SELECT CNAME FROM TECH_COURSE WHERE FEES>15000 ORDER BY

CNAME;
Ans) CNAME
Digital marketing
Mobile Application Development

(iv) SELECT AVG(FEES) FROM TECH_COURSE WHERE FEES BETWEEN 15000

AND 17000;
Ans) Avg(Fees)
15500.00

47)(a) Write the outputs of the SQL

queries (i) to (iv) based on the
relations Teacher and
Placement given below:

i) SELECT Department,
avg(salary) FROM Teacher
GROUP BY Department;

Ans)
 ii) SELECT MAX(Date_of_Join),MIN(Date_of_Join) FROM Teacher;
Ans)

iii) SELECT Name, Salary, T.Department, Place FROM Teacher T, Placement P

WHERE T.Department = P.Department AND Salary>20000;
Ans)

iv) SELECT Name, Place FROM Teacher T, Placement P WHERE Gender='F' and
T.Department=P.Department;
Ans

b) Write the command to view all tables in a database.

Ans) show tables;

48)Charu has to create a database named MYEARTH in MYSQL. She now needs to
create a table named CITY in the database to store the records of various cities
across the globe. The table CITY has the following structure:

Help her to complete the task by suggesting appropriate SQL commands

Ans)
CREATE DATABASE MYEARTH;

CREATE TABLE CITY ( CITYCODE

CHAR(5)PRIMARY KEY , CITYNAME CHAR(30),
SIZE INT, AVGTEMP INT, POPULATIONRATE INT,
POPULATION INT, );

49)(a) Consider the table, BOOK and MEMBER given below:

What will be the output of the following

statement? SELECT * FROM BOOK NATURAL
JOIN MEMBER;
Ans)

i SELECT NAME, PROJECT FROM EMPLOYEE ORDER BY NAME DESC;

Ans)

ii) SELECT NAME, SALARY FROM EMPLOYEE WHERE NAME LIKE 'A%';
Ans)
NAME SALARY
Akhtar 125000
Alex 75000

iii) SELECT NAME, DOJ FROM EMPLOYEE WHERE SALARY BETWEEN 100000 AND
200000;
NAME DOJ
Ranjan 2015-01-21
Akhtar 2015-02-01
Muneera 2018-08-19

iv) SELECT * FROM EMPLOYEE WHERE PROJECT = 'P01';

Eid Name DOB DOJ Salary Project
E01 Rannja 1990-07-12 2015-01-21 150000 P01
E03 Muneera 1996-11-15 2018-08-19 135000 P01

50)Consider the following tables – FACULTY and COURSES :

What will be the output of the following statements

i) SELECT FID, MIN(FEES), MAX(FEES) FROM COURSES GROUP BY FID;
Ans)

ii) SELECT AVG(SALARY) FROM FACULTY WHERE FNAME LIKE '%a';

AVG(SALARY)
29500

iii) SELECT FNAME, CNAME FROM FACULTY F, COURSES C WHERE

F.FID=C.FID AND COURSES.FID='F04';
 FNAME CNAME
Neha Python
Neha Computer Network

iv) SELECT FNAME, CNAME , FEES FROM FACULTY F , COURSES C WHERE

F.FID = C.FID AND FEE>15000;
FNAME CNAME FEES
Anishma Grid Computing 40000
Neha Python 17000

(b) Write the name of the command to display the structure of a table in a database.
Ans) desc or describe

51)Navdeep creates a table RESULT with a set of records to maintain the marks
secured by students in Sem1, Sem2, Sem3, and their divisions. After the
creation of the table, he entered data of 7 students in the table.

Based on the data given above answer the following questions:

i) Identify the columns which can be considered as candidate keys?
Ans) Candidate Keys : ADMNO, ROLLNO
ii) If 2 more columns are added and 3 rows are deleted from the table result,
what will be the new degree and cardinality of the above table?
Ans) Degree-8, Cardinality=4
iii) Write a statement to increase the SEM2 marks by 3% for the students
securing marks between 70 to 100.
Ans) Update result set SEM2=SEM2+.03*SEM2 where SEM2 between 70 and 100;

9) Rashmi has forgotten the names of the databases, tables and the structure
of the tables that she had created in Relational Database Management System
(RDBMS) on her computer.
(a) Write the SQL statement to display the names of all the databases
present in RDBMS application on her computer.
Ans) show databases;
(b) Write the statement which she should execute to open the database
named "STOCK".
Ans) use stock;
(c)Write the statement which she should execute to display the structure of the table
"ITEMS" existing in the above opened database "STOCK".
Ans) desc items;

52) Consider the table Stationery given below and write the output of the SQL queries that follow

(i) SELECT DISTRIBUTOR, SUM(QTY) FROM STATIONERY GROUP BY DISTRIBUTOR;

Ans)
Distributor Sum(Qty)
Reliable Stationers 100
Classic Plastics 400
Clear Deals 410

(ii) SELECT ITEMNO, ITEM FROM STATIONERY WHERE DISTRIBUTOR = "Classic Plastics"
AND PRICE > 10;
Ans)
ITEMNo ITEM
402 Gel Pen Premium
406 Gel Pen Classic

iii) SELECT ITEM, QTY * PRICE AS "AMOUNT" FROM STATIONERY WHERE ITEMNO = 402;
Ans)
ITEM Amount
Gel Pen Premium 3000

11) Consider the table Rent_cab, given below

Based on the given table, write SQL queries for the following :
(i) Add a primary key to a column name Vcode.
Ans) alter table ren_cab add primary key (VCode);
ii) Increase the charges of all the cabs by 10%.
Update Rent_cab set charges =1.1*charges;

iii) Delete all the cabs whose maker name is "Carus".

 Delete from Rent_cab where mak=’Carus’;

53) (a) Consider the following tables Student and Sport :

What will be the output of the following statement ?

SELECT * FROM Student, Sport;
Admno Name Class Admno Game
1100 Meena X 1100 Cricket
1101 Vani XI 1100 Cricket
1100 Meena X 1103 Football
1101 Vani XI 1103 Football

(b) Write the output of the queries (i) to (iv) based on the table, GARMENT given below

(i) SELECT Count(Distinct(FCODE))FROM GARMENT;

Ans) 3
(ii) SELECT FCODE, COUNT(*), MIN(PRICE) FROM GARMENT GROUP BY
FCODE HAVING COUNT(*)>1;
Ans)
FCode, Count(*), Min(price)
F02 2 750
F01 3 1000
(iii) SELECT TYPE FROM GARMENT WHERE ODR_DATE >'2021-02-01' AND
PRICE <1500;
Ans) Type
Frock
 (iv) SELECT * FROM GARMENT WHERE TYPE LIKE 'F%';
Ans)
Gcode Type Price Fcode Odr_date
G103 Frock 1000 F01 2021-09-09
G106 Formal Pant 1250 F01 2019-01-06

54) Write the output of any three SQL queries (i) to (iv) based on the tables
COMPANY and CUSTOMER given below :

(i) SELECT PRODUCTNAME, COUNT(*)FROM COMPANY GROUP BY

PRODUCTNAME HAVING COUNT(*)> 2;
Ans)
ProductName Count(*)
Mobile 3

(ii) SELECT NAME, PRICE, PRODUCTNAME FROM COMPANY C, CUSTOMER CT

WHERE C.CID = CU.CID AND C_NAME = 'SONY';
Ans) Name Price ProductName
Mohan Kumar 30000 TV

(iii) SELECT DISTINCT CITY FROM COMPANY;

Ans) Distinct City
Delhi
Mumbai
Chennai

(iv)SELECT * FROM COMPANY WHERE C_NAME LIKE '%ON%';

Ans)
 CID C_Name City PRODUCTNAME
111 SONY DELHI TV
333 ONIDA DELHI TV
444 SONY MUMBAI MOBILE

4 mark questions

55)

A) Write the following queries:

I) To display the total Quantity for each Product, excluding Products with total Quantity
less than 5.
Ans) select Product, sum(Quantity) from orders group by product having
sum(Quantity)>=5;
II) To display the orders table sorted by total price in descending order.
Ans) select * from orders order by price desc;
III) To display the distinct customer names from the Orders table
Ans) Select distinct(C_Name) from orders;
iv) Display the sum of Price of all the orders for which the quantity is null.
Ans) Select sum(price) from orders where quantity is NULL;

55)For the above table write the output

I) Select c_name, sum(quantity) as total_quantity from orders group by
c_name;
Ans)

II) Select * from orders where

product like '%phone%';
Ans)

III) Select o_id, c_name, product, quantity, price from orders where price
between 1500 and 12000;
Ans)

iv) Select max(price) from orders;

Ans) max(Price)
12000

56) Saman has been entrusted with the management of Law University
Database. He needs to access some information from FACULTY and COURSES
tables for a survey analysis. Help him extract the following information by
writing the desired SQL queries as mentioned below.

I) To display complete details (from both the tables) of those Faculties whose
salary is less than 12000
Ans) select * from faculty natural join courses where salary<12000;
OR
Select * from FACULTY, COURSES where Salary < 12000 and
facuty.f_id=courses.f_id;

II) To display the details of courses whose fees is in the range of 20000 to
50000 (both values included).
Ans) Select * from courses where fees between 20000 and 50000;

iii) To increase the fees of all courses by 500 which have "Computer" in their
Course names.
Ans) Update courses set fees=fees+500 where CName like '%Computer%';

iv) (A) To display names (FName and LName) of faculty taking System Design.

Ans) Select FName, LName from faculty natural join courses where
Came="System Design";
Or
Select FName, LName from faculty, courses where Came="System Design"
and facuty.f_id=courses.f_id;

v) To display the Cartesian Product of these two tables.

Ans) Select * from FACULTY, COURSES;
 57) Navdeep creates a table RESULT with a set of records to maintain the
marks secured by students in Sem 1, Sem2, Sem3 and their division. After
creation of the table, he has entered data of 7 students in the table

Based on the data given above answer the following questions:

i) Identify the most appropriate column, which can be considered as Primary
key
Ans) : ROLL_NO

ii) 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

iii) Write the statements to:

a. Insert the following record into the table – Roll No- 108, NameAadit,
Sem1- 470, Sem2-444, Sem3-475, Div – I.
Ans) INSERT INTO RESULT VALUES (108, ‘Aadit’, 470, 444, 475, ‘I’);
b. 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%”;
OR

iii. Write the statements to: a. Delete the record of students securing IV division.

Ans) DELETE FROM RESULT WHERE DIV=’IV’;

b. Add a column REMARKS in the table with datatype as varchar with 50 characters
ALTER TABLE RESULT ADD (REMARKS VARCHAR(50));

58)
Write SQL queries for the following:
(i) Display product name and brand name from the tables PRODUCT and
BRAND.
Ans) SELECT PName, BName product natural join brand;
(ii) Display the structure of the table PRODUCT.
Ans) DESC PRODUCT;
(iii) Display the average rating of Medimix and Dove brands
Ans) SELECT BName, AVG(Rating) FROM product natural join brand GROUP BY
BName where BName='Medimix' OR BName='Dove';
(iv) Display the name, price, and rating of products in descending order of rating.
Ans) SELECT PName, UPrice, Rating FROM PRODUCT ORDER BY Rating DESC;

59)Write queries (a) to (d) based on the tables EMPLOYEE and DEPARTMENT given
below:

(a) To display the average salary of all employees, department wise.

Ans) SELECT DeptID, AVG(SALARY) FROM EMPLOYEE GROUP BY DEPTID;
 (b) To display name and respective
department name of each employee
whose salary is more than 50000.
Ans) select name, deptname from
employee natural join department where
salary>50000;
OR
SELECT NAME, DEPTNAME FROM
EMPLOYEE, DEPARTMENT WHERE
EMPLOYEE.DEPTID=
DEPARTMENT.DEPTID AND
SALARY>50000;

(c) To display the names of employees

whose salary is not known, in alphabetical order.
Ans) SELECT NAME FROM EMPLOYEE WHERE SALARY IS NULL ORDER BY
NAME;

(d) To display DEPTID from the table EMPLOYEE without repetition.

Ans) SELECT DISTINCT DEPTID FROM EMPLOYEE;

60)Write SQL queries for (a) to (d) based on the tables CUSTOMER and
TRANSACT given below

a) Write the SQL statements to delete the records from table TRANSACT whose
amount is less than 1000.
Ans) delete from Transact where amount<1000;
 b) Write a query to display the total AMOUNT of all DEBITs and all CREDITs.
Ans) select ttype,sum(amount) from transact group by ttype;
c) Write a query to display the NAME and corresponding AMOUNT of all
CUSTOMERs who made a transaction type (TTYPE) of CREDIT.
Ans) select name, amount from customer natural join transact where
ttype=’Credit’;
Or
Select name,amount from customer c, transact t where c.cno=t.cno and
ttype=’Credit’;

d) Write the SQL statement to change the Phone number of customer

whose CNO is 1002 to 9988117700 in the table CUSTOMER
Ans) update customer set phone=9988117700 where cno=1002;

61) Consider the tables GAMES and PLAYERS given below :

Write SQL queries for the following :

(i) Display the game type and average number of games played in each
type.
Ans) Select type,avg(number) from games group by type;
(ii) Display prize money, name of the game, and name of the players from the tables
Games and Players.
Ans) select prizemoney, gamename, name from games natural join players
(iii) Display the types of games without repetition.
Ans) select distinct (type) from games;
(iv) Display the name of the game and prize money of those games whose
prize money is known.
Ans) select gamename, prizemoney from games where prizemoney is not NULL;

8) The ABC Company is considering to maintain their salespersons records

using SQL to store data. As a database administrator, Alia created the table
Salesperson and also entered the data of 5 Salespersons
Based on the data given above, answer the following questions :
i) Identify the attribute that is best suited to be the Primary Key and why ?
Ans) S_ID because it uniquely identifies the records (Not NULL and Unique)
ii) The Company has asked Alia to add another attribute in the table.
What will be the new degree and cardinality of the above table ?
Ans) Degree 6
Cardinality 5
iii) Write the statements to :
a) Insert details of one salesman with appropriate data.
Ans) Insert into salesperson values(‘S006’,’Pratham’,43,23400,’North’);
b) Change the region of salesman “Shyam” to “South”
Ans) Update Salesperson set region=”South” where S_name=”Shyam”;

OR

Write the statement to :

(a) Delete the record of salesman RISHABH, as he has left the company.
Ans) Delete from salesperson where S_Name=’Rishabh’;
(b) Remove an attribute REGION from the table.
Ans) Alter table salesperson drop column region;