Querying and SQL Functions NCERT Class 12 IP Solution

Querying and SQL Functions NCERT Solution || Querying and SQL Functions Class 12 Solution || Querying and SQL Functions Class 12 IP Solution || Querying and SQL Functions Information Practices Solution || NCERT Querying and SQL Functions Solution || Querying and SQL Functions Class 12 IP || Querying and SQL Functions Solution

Note:- Please Click on Question to get the Answer !! 

Q1. Answer the following questions:

a) Define RDBMS. Name any two RDBMS software.
b) What is the purpose of the following clauses in a select statement?
i) ORDER BY
ii) HAVING

c) Site any two differences between Single_row functions and Aggregate functions.
d) What do you understand by Cartesian product?
e) Write the name of the functions to perform the following operations:

i) To display the day like “Monday”, “Tuesday”, from the date when India got independence.
ii) To display the specified number of characters from a particular position of the given string.
iii) To display the name of the month in which you were born.
iv) To display your name in capital letters.




Q2. Write the output produced by the following SQL commands:

a) SELECT POW(2,3);
b) SELECT ROUND(123.2345, 2), ROUND(342.9234,-1);
c) SELECT LENGTH("Informatics Practices");
d) SELECT YEAR(“1979/11/26”), MONTH(“1979/11/26”), DAY(“1979/11/26”), MONTHNAME(“1979/11/26”);
e) SELECT LEFT("INDIA",3), RIGHT("Computer Science",4);
f) SELECT MID("Informatics",3,4), SUBSTR("Practices",3);




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

PCode    PName    UPrice    Manufacturer
P01    Washing Powder    120    Surf
P02    Tooth Paste    54    Colgate
P03    Soap    25    Lux
P04    Tooth Paste    65    Pepsodant
P05    Soap    38    Dove
P06    Shampoo    245    Dove


a)Write SQL queries for the following:

i. Create the table Product with appropriate data types and constraints.
ii. Identify the primary key in Product.
iii. 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.
iv. Add a new column Discount to the table Product.
v. Calculate the value of the discount in the table Product as 10 percent of the UPrice for all those products where the UPrice is more than 100, otherwise the discount will be 0.
vi. Increase the price by 12 per cent for all the products manufactured by Dove.
vii. Display   the   total   number   of   products   manufactured by each manufacturer.

b) Write the output(s) produced by executing the following queries on the basis of the information given above in the table Product:

i.SELECT PName, Average(UPrice) FROM Product GROUP BY Pname;
ii.SELECT   DISTINCT   Manufacturer   FROM   Product;Chapter 1.indd   2411/26/2020   12:31:31 PM




Q4. Using the CARSHOWROOM database given in the chapter, write the SQL queries for the following:

a) Add a new column Discount in the INVENTORY table.
b) Set   appropriate discount values for all cars keeping in mind the following:

(i) No discount is available on the LXI model.
(ii) VXI model gives a 10% discount.
(iii)A 12% discount is given on cars other than   LXI model and VXI model.

c) Display the name of the costliest car with fuel type “Petrol”.
d) Calculate the average discount and total discount available on Car4.
e) List the total number of cars having no discount.




Q5. Consider the following tables Student and Stream in the Streams_of_Students database. The primary key of the Stream table is StCode (stream code) which is the foreign key in the Student table. The primary key of the Student table is AdmNo (admission number).

AdmNo    Name    StCode
 211     Jay    NULL
 241    Aditya    S03
 290    Diksha    S01
 333    Jasqueen    S02
 356    Vedika    S01
 380    Ashpreet    S03
StCode    Stream
S01    Science
S02    Commerce
S03    Humanities


Write SQL queries for the following:

a) Create the database Streams_Of_Students.
b) Create the table Student by choosing appropriate data types based on the data given in the table.
c) Identify the Primary keys from tables Student and Stream.  Also, identify the foreign key from the table Stream.
d) Jay has now changed his stream to Humanities. Write an appropriate SQL query to reflect this change.
e) Display the names of students whose names end with the character ‘a’. Also, arrange the students in alphabetical order.
f)Display the names of students enrolled in Science and Humanities stream, ordered by student name in alphabetical order, then by admission number in ascending order (for duplicating names).
g) List the number of students in each stream having more than 1 student.
h) Display the names of students enrolled in different streams, where students are arranged in descending order of admission number.
i) Show the Cartesian product on the Student and Stream table.  Also mention the degree and cardinality produced after applying the Cartesian product.
j) Add a new column ‘TeacherIncharge” in the Stream table. Insert appropriate data in each row.
k) List the names of teachers and students.
l)If Cartesian product is again applied on Student and  Stream  tables,  what  will  be  the  degree  and  cardinality of this modified table?