CBSE Class 12 Informatics Practices Database Query using Sql

Question. What are different categories of commands in SQL?
Answer: i) Data Definition Language(DDL)
ii) Data Manipulation Language(DML)
iii) Transaction Control Language(TCL)
iv) Session Control Commands.
v) System Control Commands.

Question. What do you understand by Candidate Keys in a table? Give a suitable example of Candidate Keys from a table containing some meaningful data
Answer: Table:Item

In the above table Item, ItemNo can be a candidate key

Question. A table Employee contains 5 Rows and 4 Columns and another table PROJECT contains 5 Rows and 3 Columns. How many rows and columns will be there if we obtain Cartesian product of these two tables?
Answer: Rows = 5 x 5 = 25
Columns = 4 + 3 = 7

Question. A table ‘Customers’ in a database has 5 columns and no rows in it. What is its degree and cardinality? Later on two columns were deleted from the table and 10 rows added in the table. What will be the new degree and cardinality?
Answer: Degree:- 5 and cardinality:-0.After changes Degree:- 3 and Cardinality :-10

Question. For the above given database STUDENT-PROJECT, can we perform the following operations?
(a) Insert a student record with missing roll number value.
(b) Insert a student record with missing registration number value.
(c) Insert a project detail without submission-date.
Insert a record with Registration_ID IP-101-19 and ProjectNo 206 in table PROJECT_ASSIGNED.
Answer: (a) No
(b) Yes
(c) Yes
(d) No, because ProjectNo 206 does not exist in PROJECT table.

Question. Database __________ which is the logical design of the database, and the database _________ which is snapshot of the data in the database at a given instant in time.
Answer: Schema, Instance

Question. STUDENT:

For the given database STUDENT-PROJECT, answer the following:
(a) Name Primary key of each table.
(b) Find Foreign key(s) in table PROJECT-ASSIGNED.
(c) Is there any alternate key in table STUDENT? Give justification for your answer.
Can a user assign duplicate value to the field RollNo of STUDENT table? Justify
Answer: (a) STUDENT: Roll No
PROJECT: ProjectNo
PROJECT_ASSIGNED: Registration_ID
(b) PROJECTNO
(c) Name can be an alternate key as it stores unique values for each row.
(d) No, RollNo being the primary key must be unique for each row.

Question. For the above given database STUDENT-PROJECT, can we perform the following operations?
(d) Insert a student record with missing roll number value.
(e) Insert a student record with missing registration number value.
(f) Insert a project detail without submission-date.
(g) Insert a record with Registration_ID IP-101-19 and ProjectNo 206 in table PROJECT_ASSIGNED.
Answer: (e) No
(f) Yes
(g) Yes
(h) No, because ProjectNo 206 does not exist in PROJECT table.

TOPIC – SQL BASICS ( DDL AND DML COMMANDS)

PART A , Section I

Question. Which command is used to add new record in table?
Answer: INSERT INTO

Question. Which option of ORDER BY clause is used to arrange the output in descending order?
Answer: DESC

Question. Which command is used to change the existing information of table?
Answer: UPDATE

Question. Rajisadatabaseprogrammer,HehastowritethequeryfromEMPLOYEEtabletosearchfor the employee whose name begins from letter „R‟, for this he has written the query as: SELECT * FROM EMPLOYEE WHERENAME=‟R%‟;
Butthequeryisnotproducingthecorrectoutput,helpRajandcorrectthequerysothathe gets the desired output.
Answer:  SELECT * FROM EMPLOYEE WHERE NAME LIKE ‟R%‟;

Question. Rajisadatabaseprogrammer,HehastowritethequeryfromEMPLOYEEtabletosearchfor the employee who are not getting any commission, for this he has written the query as: SELECT * FROM EMPLOYEE WHEREcommission=null;
Butthequeryisnotproducingthecorrectoutput,helpRajandcorrectthequerysothathe
gets the desired output.
Answer: SELECT * FROM EMPLOYEE WHERE commission IS null;

Question. Rajisadatabaseprogrammer,hastowritethequeryfromEMPLOYEEtabletosearchforthe employeewhoareworkingin„Sales‟or„IT‟department,forthishehaswrittenthequeryas: SELECT*FROMEMPLOYEEWHEREdepartment=‟Sales‟or„IT‟;
Butthequeryisnotproducingthecorrectoutput,helpRajandcorrectthequerysothathe
gets the desired output.
Answer: SELECT*FROMEMPLOYEEWHEREdepartment=‟Sales‟ordepartment=„IT‟; OR
SELECT * FROM EMPLOYEE WHERE department IN (‘Sales’,’IT’)

Question. IfTableSalescontains5recordsandRajexecutedthefollowingqueries;findouttheoutput of both thequery.
(i) Select 100+200 fromdual;
(ii) Select 100+200 fromSales;
Answer: (i) 300
(ii) 300
300
300
300
300

Question. Query to delete all record of table without deleting the table:
a. DELETE TABLETABLE_NAME
b. DELETE FROMTABLE_NAME
c. DROP TABLETABLE_NAME
d. DELETE TABLE FROMTABLE_NAME
Answer: B

Question. Identify the wrong statement about UPDATE command
a. IfWHEREclauseismissingalltherecordintablewillbeupdated
b. OnlyonerecordcanbeupdatedatatimeusingWHEREclause
c. MultiplerecordscanbeupdatedatatimeusingWHEREclause
d. None of theabove
Answer: B

Question. Identify the correct statement(s) to drop a column from table
a. DELETE COLUMNCOLUMN_NAME
b. DROP COLUMNCOLUMN_NAME
c. ALTERTABLETABLE_NAMEDROPCOLUMNCOLUMN_NAME
d. ALTER TABLE TABLE_NAME DROPCOLUMN_NAME
Answer: C, D

Question. Sunil decides to delete a PhoneNo column from a MySQL Table (student) after insert the data into the table. Write the command to delete that particular column in student table
Answer: ALTER TABLE student drop PhoneNo

PART A , Section II

Question. Consider the following tables EMP and SALGRADE
Answer: 

Question. To display details of all employee in descending order of their DOJ
Answer: SELECT * FROM EMPLOYEE ORDER BY DOJ DESC

Question. TodisplayNAME,DESIG,SGRADEofthoseemployeewhojoinedintheyear2009
Answer: SELECT NAME,DESIG,SGRADE FROM EMPLOYEE WHERE DOJ LIKE ‘2009%

Question. To display all SGRADE, ANNUAL_SALARY from table SALGRADE [where ANNUAL_SALARY = SALARY12]
Answer: SELECT SGRADE,SALARY12 ANNUAL_SALARY FROM SALGRADE

Question. To display number of employee working in each SALGRADE from table EMPLOYEE
Answer: SELECT SGRADE,COUNT(*) FROM EMPLOYEE GROUP BY SGRADE

Question. To display NAME, DESIG, SALARY, HRA from tables EMPLOYEE and SALGRADE
Answer: SELECT NAME,DESIG,SALARY,HRA FROM EMPLOYEE E,SALGRADE S WHERE

E.SGRADE=S.SGRADE AND SALARY<=50000
2. Give output for following SQL queries as per given table(s):

Question. SELECT MIN (AVERAGE) FROM GRADUATE
WHERE SUBJECT = “PHYSICS”;
ii. SELECT SUM(STIPEND) FROM GRADUATE WHERE DIV = 2;
iii. SELECT AVG(STIPEND) FROM GRADUATE WHERE AVERAGE >= 65;
iv. SELECT COUNT (distinct SUBJECT) FROM GRADUATE;
v. Write code to rename a table in SQL
Answer: i. 55
ii. 1000
iii. 450
iv. 4
v. ALTER TABLE RENAME TO ;

PART B, Section I)

Question. (i) Sanjay was deleting the record of empno=1234, but at the time of execution of command he forgot to add condition empno=1234, what will be the effect of delete command in this case?
(ii) Sameer is executing the query to fetch the records of employee who are getting salary between 4000 to 8000, he executed the query as –
Select * from employee where salary between 4000 to 8000;
But he is not getting the correct output, Rewrite the correct query.
Answer: (i) If where clause is missing with DELETE then it will delete all the record of table.

Question. Select * from employee where salary between 40000 and 80000

Answer: create table member(id char(6),name varchar(30),fee int(10),doj date)

Question. What is data redundancy? What are the problems associated with it?
Answer: Data redundancy means duplication of data. It causes duplicate data at different locations which destroys the integrity of the database and wastage of storage space.

Question. What are referential Integrity Constraints? Describe them?
Answer:

Question. Mr. Sanghi created two tables with City as Primary Key in Table1 and Foreign key in Table2 while inserting row in Table2 Mr Sanghi is not able to enter value in the column City. What is the possible reason for it?
Answer: Mr Sanghi was trying to enter the name of City in Table2 which is not present in
Table1 i.e. Referential Integrity ensures that value must exist in referred table.

PART B, Section II

(i) Write a query to display VisitorName, Coming From details of Female Visitors with Amount Paid more than 3000
(ii) Write a query to display all coming from location uniquely
(iii) Write a query to insert the following values- 7, „Shilpa‟,‟F‟,‟Lucknow‟,3000
Answer: (i) Select VisitorName,ComingFrom from Visitor
AmountPaid>3000
(ii) Select distinct ComingFrom from Visitor
(iii) insert into visitor values(7,’Shilpa’,’F’,’Lucknow’,3000)

Question. Create the following table

Answer: Create table emp(empno integer Primary Key, Ename varchar(20) NOT NULL, job Char(10), mgr integer, hiredate date, saldecimal(9,2) check(sal>0),comm integer, deptno integer references dept(deptno) on delete cascade);

PART B, Section III

Question. Consider the following tables GAMES. Write SQL commands for the statements (i) to (iv) and give outputs for SQL queries (v) to (vi)

(i) To display the name of all Games with their Gcodes.
(ii) To display details of those games which are having PrizeMoney more than 7000.
(iii)To display the content of the GAMES table in ascending order of ScheduleDate.
(iv) To display sum of PrizeMoney for each of the Number of participation groupings (as shown in column Number 2 or 4).
(v) SELECT COUNT(DISTINCT Number) FROM GAMES;
(vi)SELECT MAX(ScheduleDate),MIN(ScheduleDate) FROM GAMES;
​​​​​​​Answer: (i) SELECT GameName,Gcode FROM GAMES;
(ii) SELECT * FROM GAMES WHERE PrizeMoney>7000;
(iii) SELECT * FROM GAMES ORDER BY ScheduleDate;
(iv) SELECT SUM(PrizeMoney),Number FROM GAMES GROUP BY Number;
(v) 2
(vi) 19-Mar-2004 12-Dec-2003

Consider the following tables STORE and SUPPLIERS and answer (a) and (b) parts of this question:

Question. Write SQL commands for the following statements:
i. To display details of all the items in the STORE table in ascending order of LastBuy.
ii. To display ItemNo and Item name of those items from STORE table whose Rate is more than 15 Rupees.
iii. To display the details of those items whose supplier code (Scode) is 22 or Quantity in Store (Qty) is more than 110 from the table Store.
iv. To display minimum Rate of items for each supplier individually as per Scode from the table STORE.
Answer: A
i. select * from store order by lastbuy;
ii.selectitemno,item from store where rate>15;
iii. select * from store where scode=22 or qty>110;
iv. select min(rate),scode from store group by scode;

Question. Give the output of the following SQL queries:
i. SELECT COUNT(DISTINCT Scode) FROM STORE;
ii. SELECT Rate* Qty FROM STORE WHERE ItemNo = 2004;
iii. SELECT Item, Sname FROM STORE S, Suppliers P WHERE S.Scode= P.Scode AND ItemNo = 2006;
iv. SELECT MAX(LastBuy) FROM STORE;
Answer: B

TOPIC – SQL QUERIES /FUNCTIONS

Question. Character can be stored as ____________.
a. Fixed length strings
b. Variable length strings
c. Either Fixed or Variable length strings
d. None of the above

Answer: C

Question. Query to delete all record of table without deleting the table:
a. DELETE TABLE <TABLE_NAME>;
b. DELETE FROM <TABLE_NAME>;
c. DROP TABLE <TABLE_NAME>;
d. DELETE FROM TABLE <TABLE_NAME>;

Answer: B

Question. Consider a situation where Ravi is assigned a task to see list of departments (Employee Table contain columns (EmpID, Name, Department, Salary, Age), when he executed the query as:
SELECT Department FROM Employee;
He noticed that the same department name is repeated multiple times in query output. What could be possible solution to get Department name uniquely? Rewrite the above query to fetch unique values
Answer: SELECT DISNTICT Department FROM Employee;

Question. The _________ function removes the leading extra spaces from the text.
a. Right( )
b. Trim ( )
c. Left ( )
d. Mid ( )

Answer: C

Question. What will be the output of the following:
SELECT ROUND(1449.58,-2);
a. 1449.58
b. 1449.00
c. 1400
d. 1449.60

Answer: C

Question. If table Sports contains 6 rows and you executes following query, then what will be the output?
SELECT 15+35 FROM Sports;
Answer: 15 + 35
50
50
50
50
50
50

Question. What is the minimum number of columns required to create table in mysql?
a. 0
b. 1
c. 2
d. None of the above

Answer: B

Question. What are two main characters used in pattern matching in MySQL?
a. % and –
b. % and _
c. % and x
d. x and  _

Answer: B

Question. Which aggregate function includes NULL values in their final result?
a. AVG ( )
b. MIN ( )
c. COUNT( )
d. COUNT ()

Answer: D

Question. Write SQL commands for the following on the basis of given table WORKER

a. To display details of workers not working in HR and Account department.
b. To find names of workers that begins with V.
c. To retrieve the First Name and Last Name of Worker in single column as Full Name.
d. To find maximum salary of workers having joining between ‘2020-01-01’ and ‘2020-05-30’
e. To display Worker ID and number of years lapsed between Joining Date and Today.
Answer: a. SELECT * FROM worker WHERE Department NOT IN (‘HR’ , ‘Account’);
b. SELECT * FROM worker WHERE First_Name LIKE ‘V%’;
c. SELECT CONCAT(First_Name, “ “ , Last_Name) AS ‘Full Name’ FROM worker;
d. SELECT MAX(salary) AS “Maximum Salary” FROM worker WHERE Joining_Date BETWEEN ‘2020-01-01’ AND ‘2020-05-30’ ;
e. SELECT Worker_ID, YEAR(CURDATE() ) – YEAR (Joining_Date) FROM Worker;

Question. For the given table CLUB

a To display bonus (10% of Pay) for all Female coaches
b To display details of club where sports contains word ‘ball’
c Display list of coaches with Pay more than 10000 and sorted by age.
d What will be output of below query?
SELECT AVG(Pay) FROM Club WHERE Age=36;
e. What will be output of below query?
SELEC T MIN(Age) FROM Club WHERE Pay>8000;
Answer: a.SELECT Pay*0.10 AS “BONUS” FROM Club WHERE SEX=’F’;
b. SELECT * FROM Club WHERE Sports LIKE ‘%ball%’;
c. SELECT Coachname, Pay FROM Club WHERE Pay >10000 order by Age;​​​​​