CBSE Sample Paper-2020-21
Question Bank
[1 mark questions] Q.NO. 1-21
|
1) |
The avg( ) function in MySQL is an example
of …………………. (i)
Math
Function (ii)
Text
Function (iii)
Date Function (iv)
Aggregate
Function |
|||
|
Ans:- |
Aggregate
Function |
|||
|
2) |
The …………. Command can be used to make
changes in the rows of table in SQL. |
|||
|
Ans:- |
UPDATE |
|||
|
3) |
The SQL Command that will display the
current time and date. |
|||
|
Ans :- |
Select
now(); |
|||
|
4) |
The
mid() function in MySql is an example of . a.
Math
function b.
Text
function c.
Date Function d.
Aggregate Function |
|||
|
Ans:- |
b.
Text Function |
|||
|
5) |
The function is
used in SQL to find one string into another. |
|||
|
Ans:- |
Instr(
) |
|||
|
6) |
MID () and SUBSTR () function in SQL serves the same purpose. (Yes/No) |
|||
|
Ans:- |
Yes |
|||
|
7) |
Write the output for the following SQL command: Select round(15.193 , -1); |
|||
|
Ans:- |
10 |
|||
|
8) |
Write a SQL query to display date after 10
days of current date on your system. |
|||
|
Ans:- |
Select curdate()+10; 1 mark for correct SQL command |
|||
|
9) |
Write the output for the following sql
command: Select SUBSTR(‘ABCDEFG’, -5 ,3) |
|||
|
Ans:- |
UBS |
|||
|
10) |
Which keyword is
used to arrange the result of order by clause in descending order? a.
DSEC b.
DES c.
DESCE d.
DESNO |
|||
|
Ans:- |
a. DESC |
|||
|
11) |
The clause that is
used to arrange the result of SQL command into groups a.
Order by b.
Group in c.
Groups by d.
Group by |
|||
|
Ans :- |
d.Group
By |
|||
|
12) |
Find the Output of SQL command : select concat (concat (‘Inform’,
‘atics’),‘Practices’); a. Informatics Practices b. Informatic
Practices c. Inform practices d.
Inform atics practices |
|||
|
Ans :- |
a. InformaticsPractices |
|||
|
13) |
Write the output of the following SQL
command. select round (19.88,1); a. 19.88 b.
19.8 c. 19.9 d. 20.0 |
|||
|
Ans:- |
c.
19.9 |
|||
|
14) |
The now() function in MySql is an example
of . a. Math function b. Text function c. Date Function d. Aggregate Function |
|||
|
Ans:- |
c. Date Function |
|||
|
15) |
The………….command can
be used to makes changes in the structure of a table in SQL. |
|||
|
Ans:- |
ALTER |
|||
|
16) |
Write the SQL
command that will display the time and date at which the command got
executed. |
|||
|
Ans :- |
Select sysdate(); |
|||
|
17) |
Write the output of
the following SQL command. select
round(15.872,1); a. 15.87 b.15.9 c.15.8 d.16 |
|||
|
Ans:- |
b. 15.9 |
|||
|
18) |
Manish
wants to select all the records from a table named “Students” where the value
of the column “FirstName” ends with
an “a”. Which of the following SQL
statement will do this? a. SELECT * FROM Students WHERE FirstName = ‘a’; b. SELECT * FROM Students WHERE FirstName LIKE ‘a%’; c. SELECT * FROM Students WHERE FirstName LIKE ‘%a’; d.
SELECT *
FROM Students WHERE FirstName = ‘%a%’; |
|||
|
Ans:- |
d. SELECT * FROM Students WHERE FirstName
= ‘%a%’; |
|||
|
19) |
The command can be used to add a new column to the table. |
|||
|
Ans:- |
ALTER |
|||
|
20) |
Which SQL command is used to describe the structure of the
table ? |
|||
|
Ans:- |
DESC |
|||
|
21) |
Foreign Key in a
table is used to enforce i)
Data
dependency ii)
Referential Integrity iii)
Views iv)
Index Locations |
|||
|
Ans:- |
ii)
Referential Integrity |
|||
|
22) |
A table
‘Student’ contains 5 rows and 4 columns initially. 2 more rows are added and
1 more column is added . What will be the degree and cardinality of the table
student after adding these rows and columns? i) 7, 5 ii)
5,7 iii)
5,5 iv)
None of
the above |
|||
|
Ans:- |
ii)
5,7 |
|||
|
23) |
Insert into student
values(1,’ABC’,’10 Hari Nagar’) is a type of which command : i)
DML ii)
DDL iii)
TCL iv)
DCL |
|||
|
Ans:- |
i)
DML |
|||
|
24) |
What will be the
output of - select mid('Pyhton Programming’,3,9); i)
ton Progr ii)
ton Progr iii)
hton Prog iv)
htonProg |
|||
|
Ans:- |
iii) hton Prog |
|||
|
25) |
Write the output of the following SQL statement: SELECT TRUNCATE(15.79,-1) , TRUNCATE(15.79,0),
TRUNCATE(15.79,1); a.
15 15 15.7 b.
10 15.7 15.9 c.
10 15 15.7 d.
10 10 15.9 |
|||
|
Ans:- |
c. 10 15 15.7 |
|||
|
26) |
The COUNT( ) in MySQL is an example of : a. Math function b. Text function c. Date Function d. Aggregate Function |
|||
|
Ans:- |
d. Aggregate
Funcion |
|||
|
27) |
…….. which of the following sublanguages of SQL is used
to query information from the database and to insert tuples into, delete
tuples from and modify tuples in the database? a.
DML b.
DDL c.
Query d.
Relational
Schema |
|||
|
Ans:- |
a. DML |
|||
|
28) |
The ……… clause of SELECT query allows us to select only
those rows in the result that satisfied a specified condition. a.
WHERE b.
FROM c.
HAVING d.
LIKE |
|||
|
Ans:- |
a. WHERE |
|||
|
29) |
Write the output of the following SQL
command. select substr(“COMPUTER”,3,4); a. MPUT b. PUTE c. PU d. MP |
|||
|
Ans: - |
a. MPUT |
|||
|
30) |
The now() function in MySql is an
example of ___________________. a. Math function b. Text function c. Date Function d. Aggregate Function |
|||
|
Ans :- |
c. Date Function |
|||
|
31) |
The _________ command is used to make
the changes in a table permanent. |
|||
|
Ans:- |
COMMIT |
|||
|
32) |
Give SQL command that will display
the current month from the date and time. |
|||
|
Ans
:- |
MONTH( ) |
|||
|
33) |
Which of the following keywords will you use in the following query to display all the records of
students whose name start with S? SELECT * from student
where name “S%” |
|||
|
Ans :- |
LIKE |
|||
|
34) |
Which of the following is an aggregate function: a.
Upper() b.
Trim() c.
Date() d.
Sum() |
|||
|
Ans:- |
d. SUM() |
|||
|
35) |
Write the output of the following SQL
command: SELECT left(“Jammu Region”, 5); a.
Region b.
Jammu c.
Jammu Region d.
None of the above. |
|||
|
Ans:- |
b. Jammu |
|||
|
36) |
What will be the output of the following code? SELECT
MOD(14,3); |
|||
|
Ans: |
2 |
|||
|
37) |
What will be the result of the following query based on
the table given here.
 SELECT COUNT(Salary) FROM Instructor; |
|||
|
Ans:- |
COUNT(Salary) -------------------- 5 |
|||
|
38) |
Write the command to delete all the data of the table
‘activity’ retaining only
structure. |
|||
|
Ans:- |
DELETE FROM
ACTIVITY; |
|||
|
39) |
Write the output for the following SQL commands Select round(15.193 , -1); |
|||
|
Ans:- |
10 |
|||
|
40) |
Write a SQL query to display date after 20 days of
current date on your system. |
|||
|
Ans:- |
SELECT CURDATE( )
+ 10; |
|||
|
41) |
Write the output for the following sql command Select SUBSTR(‘ABCDEFG’, -5 ,3) |
|||
|
Ans:- |
CDE |
|||
|
42) |
Which keyword is
used to arrange the result of order by clause in descending order? a. DSEC b. DES c. DESC d. DESNO |
|||
|
Ans: |
C. DESC |
|||
|
43) |
Write the output of the following SQL command. Select round(14.872,1) a)14.87 b)14.9 c)14.8 d) 15 |
|||
|
Ans:- |
b) 14.9 |
|||
|
44) |
The
command can be used to change
the size of column to the table. |
|||
|
Ans:- |
ALTER |
|||
|
45) |
The command can
be used to makes changes in the rows of a table in SQL. |
|||
|
Ans:- |
Update |
|||
|
46) |
Write the output of the following SQL
command. select round (49.88); a. 49.88 b. 49.8 c. 49.0 d. 50 |
|||
|
Ans:- |
d. 50 |
|||
|
47) |
Write the output of the following SQL command. select round (19.88,1); a. 19.88 b.
19.8 c. 19.9 d. 20.0 |
|||
|
Ans:- |
c. 19.9 |
|||
|
48) |
Select count(*) from
Employee; The
above query will not consider the following: a)
Numeric value b)
Text value c) Null value d)
Date value |
|||
|
Ans:- |
c) NULL Value |
|||
|
49) |
Which of the following is/are not correct aggregate
functions in SQL: a. AVG() b) COUNT() c) TOTAL() d) MAX() |
|||
|
Ans:- |
c) Total( ) |
|||
|
50) |
The command
can be used to make changes in the definition of a table in SQL. |
|||
|
Ans:- |
ALTER |
|||
|
51) |
Write
the SQL clause used to sort the records of a
table. |
|||
|
Ans:- |
ORDER BY |
|||
|
52) |
Write the output of the following SQL command. select round(15.857,-1); a.
15.8 b.
15.9 c.
15.0 d.
20 |
|||
|
Ans:- |
20 |
|||
|
53) |
The now()function in MySql is an example of . b.
Text function c.
Date Function d.
Aggregate
Function |
|||
|
Ans:- |
c. Date Function |
|||
|
54) |
The command
can be used to makes changes in the structure of a table in SQL. |
|||
|
Ans:- |
ALTER |
|||
|
55) |
Write the SQL
command that will display the time and date at which the command got executed. |
|||
|
Ans: |
SELECT NOW(); |
|||
|
56) |
In SQL NULL value means : (i) 0 value (ii) 1 value (iii) None value (iv) None of
the above |
|||
|
Ans:- |
iii) None value |
|||
|
57) |
Find the output of SQL Query:- SELECT MOD(11, 3); |
|||
|
Ans:- |
2 |
|||
|
58) |
The MAX() function in MySql is an example of
___________________. a. Math function b. Text function c. Date Function d. Aggregate Function |
|||
|
Ans:- |
d. Aggregate
Function |
|||
|
59) |
Write the output of the following SQL command. select round(314.82,-1); a. 314.0
b. 310.0 c. 314.8 d. 300.0 |
|||
|
Ans:- |
d. 300 |
|||
|
60)
|
What will be the
output of the following SQL command: SELECT
LTRIM(“ RAJKUMAR “); |
|||
|
Ans:- |
“RAJKUMAR “ (Removes spaces
from left side) |
|||
|
61)
|
Write the output of the following SQL command. select pow(2.37,3.45); a. 17.62 b. 19.62 c. 18.35 d. 15.82 |
|||
|
Ans:- |
b. 19.62 |
|||
|
62)
|
Having clause is used with
____________________________function. a. Math function b. Text function c. Date Function d. Aggregate Function |
|||
|
Ans:- |
Aggregate Function |
|||
|
63)
|
Write the output of the query: select instr('Toolbarbar','bar'); |
|||
|
Ans:- |
5 |
|||
|
64)
|
alter()
function in MySql is part of ___________________ a. DDL command b. DML Command c. TCL command |
|||
|
Ans |
a. DDL Command 1
mark for the correct answer |
|||
|
65)
|
The
____________command can be used to arrange data in some order in a table in
SQL. |
|||
|
Ans:- |
ORDER BY |
|||
|
66)
|
Write the name of the clause used with SELECT command to search
for a specific pattern in the strings. |
|||
|
Ans:- |
LIKE |
[2 marks questions] Q.NO. 24 - 33
|
1) |
State any two differences between single row functions and
multiple row functions. OR What is the difference between the order by and group by clause
when used along with the select
statement. Explain with an example. |
|
Ans:- |
Differences between single row functions and multiple row functions.
(i) Single row functions work on one row only whereas multiple row functions
group rows (ii) Single row functions
return one output per row whereas multiple row functions return only one
output for a specified group of rows. OR The order by clause is used to show the contents of a table/relation
in a sorted manner with respect to the column mentioned after the order by
clause. The contents of the column can be arranged in ascending or descending
order. The group by clause is used to group rows in a given column and then
apply an aggregate function eg max(), min() etc on the entire group. (any
other relevant answer) Single row v/s Multiple row functions 1 mark for each valid
point Group by v/s Order by 1 mark
for correct explanation 1 mark for appropriate example |
|
2) |
Consider the decimal number x with value 8459.2654. Write commands
in SQL to: i. round it off to a whole number ii. round it to 2 places before
the decimal. |
|
Ans:- |
i.
select round(8459.2654); ii.
select round(8459.2654,-2); 1 mark each for correct answer of part (i) , (ii) |
|
3) |
Anjali writes the following commands with respect to a table
employee having fields, empno, name, department, commission. Command1 : Select count(*)
from employee; Command2: Select
count(commission) from employee; She gets the output as 4 for the first command but gets an output
3 for the second command. Explain the output with justification. |
|
Ans:- |
This is because the column commission contains a NULL value and the
aggregate functions do not take into account NULL values. Thus Command1
returns the total number of records in the table whereas Command2 returns the
total number of non NULL values in the column commission. |
|
4) |
Consider the following SQL string: “Preoccupied” Write commands to display: a. “occupied” b. “cup” OR Considering the same string “Preoccupied” Write SQL commands to display: a. the position of the substring ‘cup’ in the string “Preoccupied”
b. the first 4 letters of the string |
|
Ans:- |
a. select substr("Preoccupied", 4); or select substring("Preoccupied", 4); or select mid("Preoccupied",4); or select right(("Preoccupied"”, 8); b. select substr("Preoccupied" ,6,3); or select substring("Preoccupied", 6,3); or select mid(("Preoccupied" ,6,3);
OR a. select instr 'Preoccupied' , ‘ 'cup')); b. select left 'Preoccupied',4); 1 mark for each correct answer of part (a) , (b) |
|
5) |
What is the difference between the where and
Having clause when used along with the select statement. Explain with an
example. OR Explain the difference
between Update and Alter command with help of an example. |
|
Ans |
Where clause is used to apply condition on individual rows and not
supports aggregate function While Having clause is used to apply condition on groups and it
supports aggregate functions. Eg: SELECT * FROM EMP WHERE SALARY > 50000; Eg: SELECT * FROM EMP GROUP BY DEPTNO HAVING COUNT(*) > 2; OR UPDATE command is a part of DML command and used to update the data
of rows of a table. While ALTER command is a part of DDL command and used to change the
structure of a table like adding column, removing it or modifying the
datatype of columns. Eg: UPDATE EMP SET SALARY = 20000; ALTER EMP ADD EMP_DOJ DATE; |
|
6) |
Write the output of following queries:- i.
SELECT SUBSTR('Aakila', -3); ii.
SELECT LEFT(‘Toolbar’, 4); |
|
Ans:- |
I.
‘ila’ II.
‘Tool’ |
|
7) |
Raghav writes the following commands with
respect to a table Flight having Fields FLCODE, START, DESTINATION, NO_STOPS. Command1 : Select count(*) from FLIGHT; Command2: Select count(DESTINATION) from
FLIGHT; He gets the output as 5 for the first command
but gets an output 3 for the second command. Explain the output with
justification. |
|
Ans:- |
This is because the column DESTINATION contains a NULL value and the
aggregate functions do not take into account NULL values. Thus Command1
returns the total number of records in the table whereas Command2 returns the
total number of non NULL values in the column DESTINATION. |
|
8) |
Write the output for following queries: i.
select MOD(11,4) "Modulus", power(3,2)
"Raised"; ii.
select CURDATE( )+10; OR i.
select length('CORONA COVID-19'); ii.
select lcase('COMputer Science'); |
|
Ans:- |
i. Modulus
Raised -------------- ---------- 3 9 ii. currentdate + 10 days aftward date will come or i. 15 ii. ‘computer science’ |
|
9) |
Consider the decimal number x with value 7459.3654. Write commands in
SQL to: i) round it off to a whole number ii)round it to 2 places before the decimal. |
|
Ans:- |
(i)
select round(7459.3654, 0) (ii)
select round(7459.3654, -2) |
|
10) |
Shailly writes the following commands with respect to a table Employee
having fields, empno, name, department, commission. Command1 : SELECT COUNT(*) FROM EMPLOYEE; Command2 : SELECT COUNT(COMMISSION) FROM EMPLOYEE; She gets the output as 7 for the first command but gets an output 5 for
the second command. Explain the output with justification. |
|
Ans:- |
This is because the column
commission contains a NULL value and the aggregate functions do not take into
account NULL values. Thus Command1 returns the total number of records in the
table whereas Command2 returns the total number of non NULL values in the
column commission. |
|
11) |
Consider the following SQL string: “SELFMOTIVATION”. Write commands to
display: a. “MOTIVATION” b. “MOT” OR Considering the same string “SELFMOTIVATION”. Write SQL commands to
display: a. the position of the substring ‘MOTIV’ in the string “SELFMOTIVATION” b. the last 6 letters of the string |
|
Ans:- |
a. select substr(“SELFMOTIVATION”,
5) b. select substr(“SELFMOTIVATION”,
5, 3) OR a. select instr(“SELFMOTIVATION”,
“MOTIV”) b. select right(“SELFMOTIVATION”,
6) (student may use other
functions like – substring/ mid/ right .. etc |
|
12) |
State any two differences between Update and alter commands. OR What is datatype? What are the main objectives of datatypes? |
|
Ans:- |
Data types are mean to identify the type of data and its associated
functions. The main objectives of datatypes is to clarify the type of data a
variable can store and which operations can be performed on it. |
|
13) |
Consider the decimal number n with value 278.6975. Write
commands in SQL : i.
That gives output 279 ii.
That gives output 280 |
|
Ans:- |
i)
select round(278.6975); (ii)
select round(278.6975,-1); or
some other queries that produces same results. 1 mark each for correct answer of part (i) , (ii) |
|
14) |
(i) Consider a table “Employee” that have fields - empno, name,
department, salary. Based on the above table “Employee”, Manvendra has entered the
following SQL command:
SELECT * FROM Employee where Salary = NULL; But the Query is not executing successfully. What do you suggest
to him in order to execute this query i.e. write the correct query. (ii) Write a SQL query to display the details of those employees
whose Salary column has some values. |
|
Ans:- |
(i)
select * from Employee where Salary is NULL; (ii)
select * from Employee where Salary is not NULL; 1 mark each for correct answer of part (i) , (ii) |
|
15) |
Consider the following SQL string: “Master Planner”. Write commands to display: a. “Master” b. “Plan” OR Considering the same string “Master Planner”. Write SQL commands to display: a. the position of the substring ‘Plan’ in the string “Master
Planner” b. the Last 4 letters of the string |
|
Ans:- |
a.
select substr("Master Planner",1,6); b.
select substr("Master Planner",8,4); or some other queries that
produces same results. 1 mark each for correct answer of part (i) , (ii) OR a.
select instr("Master Planner","Plan"); b.
select right("Master Planner",4);
or some other queries that produces same results. 1 mark each for correct answer of part (i) , (ii) |
|
16) |
What are multiple row functions? Give examples OR What is Group by clause? How can we specify condition with Group
by clause? Explain with an example. |
|
Ans:- |
|
|
17) |
Consider the decimal number N with value 87654.9876. Write
commands in SQL to:
i.
round it off to a whole number
ii.
round it to 2 places before the
decimal. |
|
Ans:- |
Round(87654.9875,0) Round(87654.9875,2) |
|
18) |
State
any two differences between single row functions and multiple row functions. OR What is the difference between the order by and group by clause
when used along with the select statement. Explain with an example. |
|
Ans:- |
(i) Single row
functions work on one row only whereas multiple row functions group rows (ii)
Single row functions return one output per row whereas multiple row functions
return only one output for a specified group of rows. OR The order by clause is used to show the contents of a
table/relation in a sorted manner with respect to the column mentioned after
the order by clause. The contents of the column can be arranged in ascending
or descending order. The group by clause is used to group rows in a given
column and then apply an aggregate function eg max(), min() etc on the entire
group. (any other relevant answer) Single row v/s Multiple row functions 1 mark for each
valid point Group by v/s Order by 1 mark for correct explanation 1
mark for appropriate example |
|
19) |
Give the output of : i)
Select
round(123.93); ii)
Select
round(123.93,1); |
|
Ans:- |
i) 124
ii) 123.9 |
|
20) |
Consider the
following SQL string: “Mental Toughness Helps You Succeed” Write commands to
display following using functions: a. “Toughness” b. “Succeed” OR Considering the same string: “Mental Toughness Helps You Succeed” Write SQL commands to display: a. the
position of the substring ‘’Helps’ in the string “Mental Toughness Helps You Succeed” the first 6 letters of the string |
|
Ans:- |
i)
Select mid(‘Mental
Toughness Helps You Succeed’, 8, 9) ii)
Select right(‘Mental
Toughness Helps You Succeed’, 7); OR i) select instr("Mental Toughness Helps You
Succeed",’Helps’); ii) select left("Mental Toughness Helps You
Succeed",6); 1 Mark each for
correct function usage |
|
21) |
Find out the error in the following SQL command and correct the
same. Select * from employee group by dept where sum(salary) >
2000000 |
|
Ans:- |
Select
* from employee group by dept where sum(salary) > 2000000 in this query in place of WHERE clause HAVING
clause to be used. |
|
22) |
Helps Abhay to Compare Having clause and Order by clause? Or Shewani has recently started working in MySQL. Help her
in understanding the difference between where and having clause. |
|
Ans:- |
Having clause is used in conjunction with group by
clause in MySQL. It is used to provide condition based on grouped data. On
the other hand, order by clause is an independent clause used to arrange
records of a table in either ascending or descending order on the basis of
one or more columns OR COUNT(*) returns the number of items in a group, including NULL values
and duplicates. COUNT(expression) evaluates expression for each row in a
group and returns the number of non null values 2 marks of correct explanation & for any other relevant answer. |
|
23) |
Write commands in SQL to: i. round off value 56789.8790
to nearest thousand’s place. ii.Display day from date 13-Apr-2020. |
|
Ans:- |
i. Select ROUND(56789.8790,-3); ii. Select DAY(‘2020-04-13’) 1 mark each for
correct answer. |
|
24) |
Given Table Course:  Find out the output for given SQL command: SELECT TID, COUNT(*), MIN(FEES) FROM COURSE GROUP BY TID HAVING
COUNT(*)>1; |
|
Ans:- |
Ans: TID COUNT(*) MIN(FEES) 101 2 12000 2 marks for
correct output |
|
25) |
Consider the following SQL
strings: S1= “INDIA” S2=”MY” & S3=”DI” Write commands to display: a. “MYINDIA” b. “india” OR Considering
the same string as above Write SQL commands to display: a.
The position of the string S3 in the string S1. The first
4 letters of the concatenation of string S1 and S2. |
|
Ans:- |
a.
Select CONCAT(s2,S1); b.
Select LCASE(S1) OR a.
Select INSTR(S1,S3); b.
Select LEFT(CONCAT(S1,S2)); 1 mark each
for correct SQL command. |
|
26) |
What is importance of
primary key in a table? How many primary keys can be there for a table? OR Explain working of TRIM( )
function with proper examples. |
|
Ans:- |
Primary Key : A column of collection of columns to identify
a tuple in a relation. It is used to search / locate row in a relation. There
can be only one primary key in a table.
1 mark for correct definition with proper significance. 1 mark for stating only one primary key in a table. OR TRIM ()
function is used to remove leading and trailing spaces from a string a table.
It can be used as TRIM(String) For example; SELECT TRIM(' bar '); ->
'bar' 1 mark for stating purpose of
the functions 1 mark for correct example. |
|
27) |
Consider the following ‘Student’ table.  (i) What will be the most
suitable datatype for the grade column and
why? (ii) Write a command to insert
Suman’s record with the data as shown in the
table. |
|
Ans:- |
(i) Gender
column datatype char(1) as all the possible values can be accommodated and it will be space efficient. (ii) INSERT INTO Student (Rollno, Sname,
Subject, Marks) VALUES (“003”,
”SUMAN”, “IP”, 75); 1 mark for
each correct answer |
|
28) |
Explain
the working of ORDER BY clause of SELECT query with proper example. |
|
Ans:- |
The ORDER
BY keyword is used to sort the result-set in ascending or descending order. The ORDER BY keyword sorts the
records in ascending order by default. To sort the records in descending
order, use the DESC keyword. 1 mark for
correct explanation. 1 mark for appropriate
example |
|
29) |
Consider a
string “AS YOU know MORE” 2 Write
the queries for the following tasks. (i) Write a command to display “know”. (ii) Write a command to display
number of characters in the string. OR Consider a string “You
Grow more” stored in a column str. What will be the output of the following
queries? (i) SELECT UPPER(str); (ii) SELECT substr(str,-9,4); |
|
Ans:- |
(i)
select mid(“AS YOU know MORE”,8,4); (ii)
select length(“AS YOU know MORE”); OR (i)
YOU GROW MORE (ii)
Grow 1 mark for
each correct answer |
[3 marks question]
Q.NO. 37
Q..1 A
relation SALESMAN is given below:
SNO SNAME SALARY BONUS DATEOFJOIN AREA
A01 Kushagra Jain 30000 45.25 29-10-2019 Delhi
A02 Prakhar Sharma 50000 25.50 13-03-2018 Ajmer
B03 Trapti Singh 30000 35.00 18-03-2017 Jhansi
B04 Shailly 80000 45.00 31-12-2018 Delhi
C05 Lakshay Lawania 20000 10.25 23-01-1989 Jaipur
C06 Naresh 70000 12.75 15-06-1987 Ajmer
D07 Krishna Singh 50000 27.50 18-03-1999 Jhansi
Write
SQL commands to perform the following operations:
i) Count the number of salesman
area-wise.
ii) Display the month name for the date
of join of salesman of area ‘Ajmer’
iii) Display the total salary paid to all salesman.
(i) select area, count(sname) as “Number of
salesman” from Salesman group by area;
(ii) select monthname(dateofjoin) from
Salesman where area=’Ajmer’;
(iii) select sum(salary) from Salesman;
Q. 2 Given the table CARDEN having following data:
|
CCode |
CarName |
Company |
Color |
Capacity |
Charges |
|
501 |
A-Star |
Suzuki |
Red |
3 |
14 |
|
503 |
Indigo |
Tata |
Silver |
3 |
12 |
|
502 |
Innova |
Toyota |
White |
7 |
15 |
|
509 |
Qualis |
Toyota |
Silver |
4 |
14 |
|
510 |
Wagon R |
Suzuki |
Red |
4 |
35 |
Write SQL Commands for the
following :
a. Display
the average charges of each type of car company having capacity more than 3.
b. Count
the totalcars manufactured by each company.
a. select company, avg(charges) from carden group by company
having capacity>3;
b. select Company, count(*) from carden group by Company;
|
P_ID |
PROD_NAME |
PROD_PRICE |
PROD_QTY |
|
P01 |
Notebook |
85 |
500 |
|
P02 |
Pencil Box |
76 |
200 |
|
P03 |
Water Bottle |
129 |
50 |
|
P04 |
School Bag |
739 |
70 |
(i) Display maximum PROD_QTY.
(ii) Display the value of each product where the value of each product is calculated as PROD_PRICE * PROD_QTY
(iii) Display average PROD_PRICE.
Answers:
(i)
SELECT MAX(PROD_QTY) FROM product;
(ii)
SELECT PROD_PRICE*PROD_QTY AS ‘Value’
FROM product;
(iii)
SELECT AVG(PROD_PRICE) FROM product;
[5 marks question] – Q.39
Q. 1 Write the SQL functions which will perform the following
operations:
i)
To display the name of the month of the
current date .
ii)
To remove spaces from the beginning and
end of a string, “ Panorama “.
iii)
To display the name of the day eg, Friday
or Sunday from your date of birth, dob.
iv)
To display the starting position of your
first name(fname) from your whole name (name).
v)
To compute the remainder of division
between two numbers, n1 and n2
Answers:
i) monthname(date(now()))
ii) trim(“ Panaroma “)
iii) dayname(date(dob))
iv) instr(name, fname)
v) mod(n1,n2)
Q. 2 Write the SQL functions which will perform the following
operations:
i) To display the name of the month of the current date.
ii) To remove spaces from the beginning and end of a
string, “ KV Sangathan “.
iii) To display the name of the day eg, Friday or Sunday
from your date of birth, dob.
iv) To print the value of square root of 2 upto 2 decimal
points.
v) To compute the remainder of division between two
numbers, n1 and n2
Answers:
(i) select month(current_date());
(ii) select trim(“ KV
Sangathan “);
(iii) select dayname(dob) from student;
(iv) select round(sqrt(2) , 2);
(v) select n1 % n2;
Q. 3 Write the SQL statement for the following:
i)
To display names “Mr. James” and “Ms. Smith”
in lower case.
ii)
To display current date and time.
iii)
To extract date from a given datetime value
‘2020-12-21 09:30:37’.
iv)
To remove trailing spaces from string “ Technology Works ”
(i) select lower(“Mr. James”), lower(“Ms. Smith”);
(ii) select now();
(iii) select date(“2020-12-21 09:30:37”);
(iv) select rtrim(“
Technology Works ”);
(v) select mod(125,17);
Q. 4 Write
the SQL functions which will perform the following operations:
i)
To
display the name of the month of your birthdate .
ii)
To remove spaces from the beginning of a string,
“ Python“.
iii)
To
display the day of the week eg, Sunday from current date.
iv)
To
display the starting 3 characters from your name .
v)
To
compute the power of 2 to the power 3
Answers:
i) select monthname(‘1998-11-20’);
ii)
select ltrim(‘
Python’);
iii)
select dayname(now());
iv) select
left(‘Nitin’,3);
iv) Select power(2,3);
OR
Q. 1
Consider the table
: Shop
Id SName Area Bonus DateofOpen S001 ABC Comp CP 1000.89 2010-11-20
S002 All Infotech GK II 2345.987 2015-09-12
S003 Tech Shoppe CP 761.46 2013-07-25
S004 Geek Tenco Soft NP 456.923 2019-10-10
S005 Hitech Solution GK II 1000.025 2008-12-20
a) Display shop name and bonus after
rounding off to zero decimal places.
b) Display the
position of occurrence of the string “tech” in shop names.
c) Display three characters from shop name
starting from second character.
d) Display the month name for the date of
opening of shop
e) Display the name of the shop in all capitals.
Answers:
a) Select Sname,round(Bonus,0) from
Shop;
b) Select instr(SName,’tech’) from
Shop;
c) Select mid(SName,2,3) from Shop;
d) Select monthname(DateOfOpen) from
shop;
e)
Select
Upper(SName) from shop;
Q. 2 Consider the following table Garments. Write SQL commands for the
following statements.
Table : Garments
|
GCode |
GName |
Price |
MCode |
Launch_Date |
|
10001 |
Formal Shirt |
1250 |
M001 |
2008-12-12 |
|
10020 |
Frock |
750 |
M004 |
2007-09-07 |
|
10007 |
Formal Pant |
1450 |
M001 |
2008-03-09 |
|
10024 |
Denim Pant |
1400 |
M003 |
2007-04-07 |
|
10090 |
T-Shirt |
800 |
M002 |
2009-05-12 |
a) a) To update the
Price of Frock to 825.
b)
To print the
average price of all the Garments.
c)
To display the
Garments Name with their price increased by 15%.
d)
To delete the
rows having MCode as M002.
e) To
display the details of all the Garments which have GCode less than 10030.
Solutions:
a. Update Garments set
price=825 where GName=’Frock’;
b. select avg(price) from Garments;
c. select DName, price*1.15 as
‘Increased_Price’ from Garments;
d. delete from Garments where
MCode=’M002’;
e. select * from Garments where
GCode<10030;
No comments:
Post a Comment
Note: Only a member of this blog may post a comment.