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)

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

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

(c) Select Round(123.7898,2);

(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.

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

(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

    (c) 123.79

    (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


   (c) Select Pcode, PName, UPrice from Product ORDER BY PName DESC, UPrice ASC;


   (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


6:

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.


👉See Also:

No comments:

Post a comment

We love to hear your thoughts about this post!