Monday 29 March 2021

CBSE Class 12 - Informatics Practices - Querying and SQL Functions (Worksheet) (#class12IP)(#cbsenotes)(#eduvictors)

CBSE Class 12 - Informatics Practices - Querying and SQL Functions (Worksheet)

Q1 (MCQ): The now() function in MySql is an example of ?

a. Math function

b. Text function

c. Date Function

d. Aggregate Function

Q2: Write the output of the following SQL command.

select round(15.857,-1);

a. 15.8

b. 15.9

c. 15.0

d. 16

Q3: A table has 4 rows and 6 column find its degree and cardinality.

Q4: What is the output of the following SQL statements

(a) Select Pow(8,2);

(b) Select MOD(80,12);

(d) Select LENGTH('APPLE');

(e) select CONCAT('Sun', 'Rays');

(f) Select UPPER('iSucceed')

(g) Select LTRIM(' APPLE IS RED ');

(h) Select TRIM(' APPLE IS RED ');

(i) Select MID('Databasse',4,2);

(j) Select MONTHNAME('2020-12-23');

(k) Select DAYNAME('2021-03-29');

(l) Select dayofweek('2021-03-29');

(m) Select MONTH('2021-03-29');

Q5: Consider the following table named “Product”, showing details of products being sold in a grocery shop.

(a) Create the table Product with appropriate data types and constraints.

(b) Identify the primary key in Product

(c) List the Product Code, Product name and price in descending order of their product name. If PName is the same then display the data in ascending order of price.

(d) Add a new column Discount to the table Product

(e) Calculate the value of the discount in the table Product as 10 per cent of the UPrice for all those products where the UPrice is more than 100, otherwise the discount will be 0.

(f) Increase the price by 12 per cent for all the products manufactured by Dove

(g) What is the output of the following SQL Statement

SELECT DISTINCT Manufacturer FROM Product;

(h) Write the output of the following SQL statement

SELECT COUNT(DISTINCT PName) FROM Product;

(i) Write SQL statement to find number of records in Product table.

(j) What is the output of the following SQL statement?

SELECT PName, MAX(UPrice), MIN(UPrice) FROM Product GROUP BY PName;

Q6: Write at least two basic differences between WHERE and HAVING clause in SQL.

👉Download Worksheet

Answers:

1: Date Function

2: d. 16

3: Degree 6 ; Cardinality 4

4: (a) 64

(b) 8

(d) 5

(e) Sun-Rays

(f) ISUCCEED

(g) 'APPLE IS RED '

(h) 'APPLE IS RED'

(i) ab

(j) December

(k) Monday

(l) 2

(m) 3

5: (a) Create TABLE Product (

PCode char(3) NOT NULL,

PName varchar(60),

UPrice Numeric(10,2),

Manufacturer varchar(30),

PRIMARY KEY (PCode)

);

(b) PCode

(d) ALTER Table Product ADD Discount int;

(e)UPDATE Product Set Discount = 0;

UPDATE Product

Set Discount = 10 Where UPrice > 100;

(f) UPDATE Product

Set UPrice = UPrice * 1.12

Where Manufacturer = 'Dove';

(g) Output is:

Manufacturer

Surf

Colgate

Lux

Pepsodent

Dove

(h) 4

(i) SELECT Count(*) From Product;

(j) Output is:

PName MAX(UPrice) MIN(UPrice)

Shampoo 274.40 274.40

Soap 42.56 25.00

Tooth Paste 65.00 54.00

Washing Powder 120.00 120.00

SNo.	WHERE Clause	HAVING Clause
1.	WHERE Clause is used with single row function like UPPER, LOWER etc.	HAVING Clause is used with multiple row function like SUM, COUNT etc.
2.	filter records from a table based on the specified condition.	filter record from groups based on the specified condition.
3.	Can be used with SELECT, UPDATE, DELETE statement.	Can only be used with SELECT statement.
4.	Used before GROUP BY Clause	Used after GROUP BY Clause.