Querying and SQL Functions

This page contains the NCERT Informatics Practicesclass 12 chapter 1 Querying and SQL Functions from the book Informatics Practices. You can find the solutions for the chapter 1 of NCERT class 12 Informatics Practices, for the Short Answer Questions, Long Answer Questions and Projects/Assignments Questions in this page. So is the case if you are looking for NCERT class 12 Informatics Practices related topic Querying and SQL Functions question and answers.
Buy Class 12 Informatics Practices Books NOW!
EXERCISE
1.
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 specifi ed 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.
a) RDBMS: RDBMS stands for Relational Database Management System. It is a database management system based on the relational model, where data is stored in tables and relationships are maintained among the data.
Two RDBMS Software: Examples are MySQL and Oracle.
b) i) Purpose of ORDER BY clause: It is used to sort the results of a SQL query in ascending or descending order based on one or more columns.
b) ii) Purpose of HAVING clause: It is used to apply a filter to groups created by the `GROUP BY` clause, allowing you to include or exclude rows after they have been grouped.
c) Differences Between Single_row functions and Aggregate Functions:
The following are the differences between Single_row Functions and Aggregate Functions:
Aspect
Single_row Functions
Aggregate Functions
1. Operational Scope:
Operate on individual values
Operate on a collection of values to return a single value
2. Usage in Clauses:
Can be used in various SQL clauses.
Typically used with the `GROUP BY` clause.
d) Cartesian Product: It is the result of an SQL query that combines all rows from two or more tables. This usually occurs when a join condition is not properly specified, leading to all possible combinations of rows from the involved tables.
e) i) Name of the function to display the day like “Monday”, “Tuesday”, from the date when India got independence: `DAYNAME('1947-08-15')`.
e) ii) Name of the function to display the specified number of characters from a particular position of the given string: `SUBSTRING(string, start, length)`
e) iii) Name of the function to display the name of the month in which you were born: `MONTHNAME(your-birth-date)`.
e) iv) Name of the function to display your name in capital letters: `UCASE('your-name')` or `UPPER('your-name')`.
2.
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);
a) Output produced by the sql command: SELECT POW(2, 3);
mysql> SELECT POW(2, 3);
Output:
+----------+
| POW(2,3) |
+----------+
| 8        |
+----------+
b) Output produced by the sql command: SELECT ROUND(123.2345, 2), ROUND(342.9234,-1);
mysql> SELECT
    ROUND(123.2345, 2),
    ROUND(342.9234, -1);
Output:
+---------------------+----------------------+
| ROUND(123.2345, 2)  | ROUND(342.9234,-1)   |
+---------------------+----------------------+
| 123.23              | 340                  |
+---------------------+----------------------+
c) Output producted by the sql command: SELECT LENGTH("Informatics Practices");
mysql> SELECT LENGTH("Informatics Practices");
Ouput:
+--------------------------------+
| LENGTH("Informatics Practices")|
+--------------------------------+
| 21                             |
+--------------------------------+
d) Output produced by the sql command: SELECT YEAR(“1979/11/26”), MONTH(“1979/11/26”), DAY(“1979/11/26”), MONTHNAME(“1979/11/26”);
SELECT
    YEAR(“1979/11/26”),
    MONTH(“1979/11/26”),
    DAY(“1979/11/26”),
    MONTHNAME(“1979/11/26”);
Output:
+-------------------+--------------------+------------------+----------------------------+
| YEAR("1979/11/26")| MONTH("1979/11/26")| DAY("1979/11/26")| MONTHNAME("1979/11/26")    |
+-------------------+--------------------+------------------+----------------------------+
| 1979              | 11                 | 26               | November                   |
+-------------------+--------------------+------------------+----------------------------+
e) Output produced by the sql command: SELECT LEFT("INDIA",3), RIGHT("Computer Science",4);
mysql> SELECT
    LEFT("INDIA", 3),
    RIGHT("Computer Science",4);
Output:
+----------------+----------------------------+
| LEFT("INDIA",3)| RIGHT("Computer Science",4)|
+----------------+----------------------------+
| IND            | ence                       |
+----------------+----------------------------+
f) Output produced by the sql command: SELECT MID("Informatics",3,4), SUBSTR("Practices",3);
mysql> SELECT
    MID("Informatics", 3, 4),
    SUBSTR("Practices", 3);
Output:
+-----------------------+-------------------------+
| MID("Informatics",3,4)| SUBSTR("Practices",3)   |
+-----------------------+-------------------------+
| form                  | actices                 |
+-----------------------+-------------------------+
3.
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 per cent 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;
iii.
SELECT COUNT(DISTINCT PName) FROM NOTES Product;
iv.
SELECT PName, MAX(UPrice), MIN(UPrice) FROM Product GROUP BY PName;
a) i) Write SQL query to create the table Product with appropriate data types and constraints:
CREATE TABLE Product (
    PCode VARCHAR(10) PRIMARY KEY,
    PName VARCHAR(50),
    UPrice DECIMAL(10, 2),
    Manufacturer VARCHAR(50)
);
a) ii) Write SQL query to identify the primary key in Product: The primary key for the ‘Product’ table would logically be PCode, as it uniquely identifies each product (as asked in the quesion, the writing of the query to identify the primary key is not something feasible, and it does not make sense too.).
a) iii) Write SQL query to 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:
SELECT PCode,
    PName,
    UPrice 
FROM Product 
ORDER BY PName DESC,
    UPrice ASC;
a) iv) Write SQL query to add a new column Discount to the table Product:
ALTER TABLE Product 
    ADD Discount DECIMAL(10, 2);
a) v) Write SQL query to 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:
UPDATE Product 
    SET Discount = CASE 
        WHEN UPrice > 100 THEN UPrice * 0.10 
        ELSE 0 
    END;
a) vi) Write SQL query to increase the price by 12 per cent for all the products manufactured by Dove.
UPDATE Product 
    SET UPrice = UPrice * 1.12 
    WHERE Manufacturer = 'Dove';
a) vii) Write SQL query to display the total number of products manufactured by each manufacturer.
SELECT Manufacturer,
    COUNT(*) AS TotalProducts 
FROM Product 
GROUP BY Manufacturer;
b) i) Write the output(s) produced by executing the following queries on the basis of the information given above in the table Product:
SELECT PName,
    Average(UPrice)
FROM Product
GROUP BY Pname;
Output:
+----------------+-------------+
| PName          | AVG(UPrice) |
+----------------+-------------+
| Washing Powder | 120.00      |
| Tooth Paste    | 59.50       |
| Soap           | 31.50       |
| Shampoo        | 245.00      |
+----------------+-------------+
Explanation: The average price for each product name is calculated. Since “Washing Powder” and “Shampoo” appear only once, their average prices are their unit prices. For “Tooth Paste” and “Soap”, the averages are calculated from their respective prices.
b) ii) Write the output(s) produced by executing the following queries on the basis of the information given above in the table Product:
SELECT DISTINCT Manufacturer
FROM Product;
Output:
+-------------+
| Manufacturer|
+-------------+
| Surf        |
| Colgate     |
| Lux         |
| Pepsodant   |
| Dove        |
+-------------+
Explanation: This query lists all distinct manufacturers in the table.
b) iii) Write the output(s) produced by executing the following queries on the basis of the information given above in the table Product:
SELECT COUNT(DISTINCT PName)
FROM Product;
Output:
+------------------------+
| COUNT(DISTINCT PName)  |
+------------------------+
| 4                      |
+------------------------+
Explanation: There are four distinct product names: “Washing Powder”, “Tooth Paste”, “Soap”, and “Shampoo”.
b) iv) Write the output(s) produced by executing the following queries on the basis of the information given above in the table Product:
SELECT PName,
    MAX(UPrice),
    MIN(UPrice)
FROM Product
GROUP BY PName;
Output:
+----------------+-------------+-------------+
| PName          | MAX(UPrice) | MIN(UPrice) |
+----------------+-------------+-------------+
| Washing Powder | 120.00      | 120.00      |
| Tooth Paste    | 65.00       | 54.00       |
| Soap           | 38.00       | 25.00       |
| Shampoo        | 245.00      | 245.00      |
+----------------+-------------+-------------+
Explanation: This query shows the maximum and minimum prices for each product name. For “Washing Powder” and “Shampoo”, both the maximum and minimum prices are their respective unit prices. For “Tooth Paste” and “Soap”, the maximum and minimum prices are shown based on the provided data.
4.
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.
For your information, the CLASSROOM database that we need to use comprises of the following tables.
INVENTORY
CarId
CarName
Price
Model
YearManufacture
Fueltype
D001
Car1
582613.00
LXI
2017
Petrol
D002
Car1
673112.00
VXI
2018
Petrol
B001
Car2
567031.00
Sigma1.2
2019
Petrol
B002
Car2
647858.00
Delta1.2
2018
Petrol
E001
Car3
355205.00
5 STR STD
2017
CNG
E002
Car3
654914.00
CARE
2018
CNG
S001
Car4
514000.00
LXI
2017
Petrol
S002
Car4
614000.00
VXI
2018
Petrol
CUSTOMER
CustId
CustName
CustAdd
Phone
Email
C0001
AmitSaha
L-10, Pitampura
4564587852
amitsaha2@gmail.com
C0002
Rehnuma
J-12, SAKET
5527688761
rehnuma@hotmail.com
C0003
CharviNayyar
10/9, FF, Rohini
6811635425
charvi123@yahoo.com
C0004
Gurpreet
A-10/2, SF, MayurVihar
3511056125
gur_singh@yahoo.com
SALE
InvoiceNo
CarId
CustId
SaleDate
PaymentMode
EmpID
SalePrice
I00001
D001
C0001
2019-01-24
Credit Card
E004
613247.00
I00002
S001
C0002
2018-12-12
Online
E001
590321.00
I00003
S002
C0004
2019-01-25
Cheque
E010
604000.00
I00004
D002
C0001
2018-10-15
Bank Finance
E007
659982.00
I00005
E001
C0003
2018-12-20
Credit Card
E002
369310.00
I00006
S002
C0002
2019-01-30
Bank Finance
E007
620214.00
EMPLOYEE
EmpID
EmpName
DOB
DOJ
Designation
Salary
E001
Rushil
1994-07-10
2017-12-12
Salesman
25550
E002
Sanjay
1990-03-12
2016-06-05
Salesman
33100
E003
Zohar
1975-08-30
1999-01-08
Peon
20000
E004
Arpit
1989-06-06
2010-12-02
Salesman
39100
E006
Sanjucta
1985-11-03
2012-07-01
Receptionist
27350
E007
Mayank
1993-04-03
2017-01-01
Salesman
27352
E010
Rajkumar
1987-02-26
2013-10-23
Salesman
31111
a) Using the CARSHOWROOM database given in the chapter, write the SQL query to add a new column Discount in the INVENTORY table.
ALTER TABLE INVENTORY 
ADD Discount DECIMAL(10, 2);
b) Using the CARSHOWROOM database given in the chapter, write the SQL query to 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.
UPDATE INVENTORY 
SET Discount = CASE
    WHEN Model = 'LXI' THEN 0
    WHEN Model = 'VXI' THEN Price * 0.10
    ELSE Price * 0.12
END;
c) Using the CARSHOWROOM database given in the chapter, write the SQL query to display the name of the costliest car with fuel type “Petrol”:
SQL Query:
SELECT CarName 
FROM INVENTORY
WHERE FuelType = 'Petrol' 
ORDER BY Price DESC
LIMIT 1;
Output:
+--------+
| CarName|
+--------+
| Car1   |
+--------+
d) Using the CARSHOWROOM database given in the chapter, write the SQL query to calculate the average discount and total discount available on Car4:
SQL Query:
SELECT AVG(Discount) AS AvgDiscount,
    SUM(Discount) AS TotalDiscount
FROM INVENTORY
WHERE CarName = 'Car4';
Output:
+------------+--------------+
| AvgDiscount| TotalDiscount|
+------------+--------------+
| 30700.00   | 61400.00     |
+------------+--------------+
e) Using the CARSHOWROOM database given in the chapter, write the SQL query to list the total number of cars having no discount:
SQL Query:
SELECT COUNT(*)
FROM INVENTORY
WHERE Discount = 0;
Output:
+----------+
| COUNT(*) |
+----------+
| 2        |
+----------+
5.
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?
a) Create the database Streams_Of_Students:
SQL Query:
CREATE DATABASE Streams_Of_Students;
b) Create the table Student by choosing appropriate data types based on the data given in the table:
SQL Query:
CREATE TABLE Student (
    AdmNo INT PRIMARY KEY,
    Name VARCHAR(255),
    StCode VARCHAR(10) REFERENCES Stream(StCode)
);
c) Identify the Primary keys from tables Student and Stream. Also, identify the foreign key from the table Stream:
●
Primary Key of Student: AdmNo
●
Primary Key of Stream: StCode
●
Foreign Key in Student: StCode (referencing StCode in Stream)
d) Jay has now changed his stream to Humanities. Write an appropriate SQL query to reflect this change:
SQL Query:
UPDATE Student
    SET StCode = 'S03'
WHERE Name = 'Jay';
* e) Display the names of students whose names end with the character ‘a’. Also, arrange the students in alphabetical order:
SELECT Name
FROM Student
WHERE Name LIKE '%a'
ORDER BY Name;
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):
SELECT Name, AdmNo 
FROM Student 
WHERE StCode IN ('S01', 'S03') 
ORDER BY Name, AdmNo;
Or you can use the following query:
SELECT S.Name, S.AdmNo 
FROM Student S 
JOIN Stream ST ON S.StCode = ST.StCode 
WHERE ST.Stream IN ('Science', 'Humanities') 
ORDER BY S.Name, S.AdmNo;
g) List the number of students in each stream having more than 1 student:
SELECT StCode, COUNT(*) AS NumberOfStudents
FROM Student
GROUP BY StCode
HAVING COUNT(*) > 1;
Alternatively, we can use the following query:
SELECT ST.Stream, COUNT(*) 
FROM Student S 
JOIN Stream ST ON S.StCode = ST.StCode 
GROUP BY ST.Stream 
HAVING COUNT(*) > 1;
h) Display the names of students enrolled in different streams, where students are arranged in descending order of admission number:
SELECT Name, StCode
FROM Student
WHERE StCode IS NOT NULL
ORDER BY AdmNo DESC;
i) Show the Cartesian product on the Student and Stream table. Also mention the degree and cardinality produced after applying the Cartesian product:
SQL Query:
SELECT *
FROM Student, Stream;
Output:
+-------+----------+--------+--------+------------+
| AdmNo | Name     | StCode | StCode | Stream     |
+-------+----------+--------+--------+------------+
| 211   | Jay      | NULL   | S01    | Science    |
| 211   | Jay      | NULL   | S02    | Commerce   |
| 211   | Jay      | NULL   | S03    | Humanities |
| 241   | Aditya   | S03    | S01    | Science    |
| 241   | Aditya   | S03    | S02    | Commerce   |
| 241   | Aditya   | S03    | S03    | Humanities |
| 290   | Diksha   | S01    | S01    | Science    |
| 290   | Diksha   | S01    | S02    | Commerce   |
| 290   | Diksha   | S01    | S03    | Humanities |
| 333   | Jasqueen | S02    | S01    | Science    |
| 333   | Jasqueen | S02    | S02    | Commerce   |
| 333   | Jasqueen | S02    | S03    | Humanities |
| 356   | Vedika   | S01    | S01    | Science    |
| 356   | Vedika   | S01    | S02    | Commerce   |
| 356   | Vedika   | S01    | S03    | Humanities |
| 380   | Ashpreet | S03    | S01    | Science    |
| 380   | Ashpreet | S03    | S02    | Commerce   |
| 380   | Ashpreet | S03    | S03    | Humanities |
+-------+----------+--------+--------+------------+
After applying the Cartesian product to the “Student” and “Stream” tables, the resulting table’s degree and cardinality are as follows:
●
Degree (Number of Attributes):
●
The degree of a relation in a database is the number of attributes (columns) it contains.
●
The “Student” table has 3 attributes: AdmNo, Name, StCode.
●
The “Stream” table has 2 attributes: StCode, Stream.
●
Therefore, the degree of the Cartesian product is the sum of the attributes from both tables: 3 + 2 = 5 attributes.
●
Cardinality (Number of Rows):
●
The cardinality of a relation is the number of tuples (rows) it contains.
●
Let’s assume the “Student” table has {n} rows and the “Stream” table has {m} rows.
●
The cardinality of the Cartesian product is the product of the number of rows in each table: {n × m}.
With the given data:
○
The “Student” table has 6 rows.
○
The “Stream” table has 3 rows.
●
Hence, the cardinality of the Cartesian product is 6 × 3 = 18 rows.
So, the Cartesian product of these two tables results in a relation with a degree of 5 and a cardinality of 18.
j) Add a new column ‘TeacherIncharge” in the Stream table. Insert appropriate data in each row:
SQL Query to add a new column “TeacherIncharge”:
ALTER TABLE Stream 
    ADD TeacherIncharge VARCHAR(255);
SQL Query to Insert data for each steam’s teacher:
UPDATE Stream
    SET TeacherIncharge = 'Dr. Sharma'
WHERE StCode = 'S01';
UPDATE Stream
    SET TeacherIncharge = 'Prof. Gupta'
WHERE StCode = 'S02';
UPDATE Stream
    SET TeacherIncharge = 'Ms. Iyer'
WHERE StCode = 'S03';
k) List the names of teachers and students:
This can be answered in two ways. One way is to list all the names. The other way is to list the name of each student along with his teacher, based on the stream he is attending. Both the queries are given below.
SQL Query to display the names of all the students and teachers:
SELECT TeacherIncharge AS Name 
FROM Stream
UNION
SELECT Name FROM Student;
SQL Query to display the name of each student along with his teacher:
SELECT Student.Name AS StudentName, Stream.TeacherIncharge AS TeacherName
FROM Student
JOIN Stream ON Student.StCode = Stream.StCode;
l) If Cartesian product is again applied on Student and Stream tables, what will be the degree and cardinality of this modified table?:
If the Cartesian product is applied again on the Student and Stream tables, taking into account the new ‘TeacherIncharge’ column added to the Stream table, the degree and cardinality of the resulting table will change as follows:
●
Degree (Number of Attributes):
●
The degree of a relation in a database is determined by the number of attributes (columns) it contains.
●
Originally, the ‘Student’ table had 3 attributes (AdmNo, Name, StCode) and the ‘Stream’ table had 2 attributes (StCode, Stream).
●
With the addition of the ‘TeacherIncharge’ column to the ‘Stream’ table, it now has 3 attributes (StCode, Stream, TeacherIncharge).
●
Therefore, the new degree of the Cartesian product will be the sum of the attributes from both tables: 3 + 3 = 6 attributes.
●
Cardinality (Number of Rows):
●
The cardinality of a relation is determined by the number of tuples (rows) it contains.
●
Let’s assume the ‘Student’ table has {n} rows and the ‘Stream’ table has {m} rows.
●
The cardinality of the Cartesian product is the product of the number of rows in each table: {n × m}.
●
For example, if the ‘Student’ table has 6 rows and the ‘Stream’ table has 3 rows (as in the given scenario), the cardinality of the Cartesian product will be 6 × 3 = 18 rows.
Therefore, with the updated ‘Stream’ table, the Cartesian product of these two tables will result in a relation with a degree of 6 and a cardinality of 18.