SUMMARY – Chapter 1: Querying and SQL Functions
- A Function is used to perform a particular task and return a value as a result.
- Single row functions work on a single row to return a single value.
- Multiple row functions work on a set of records as a whole and return a single value.
- Numeric functions perform operations on numeric values and return numeric values.
- String functions perform operations on character type values and return either character or numeric values.
- Date and time functions allow us to deal with date type data values.
- GROUP BY function is used to group the rows together that contain similar values in a specified column. Some of the group functions are COUNT, MAX, MIN, AVG and SUM.
- Join is an operation which is used to combine rows from two or more tables based on one or more common fields between them.
NCERT Exercise Solution – Ch. 1: Querying and SQL Functions
Question 1. Answer the following questions:
a) Define RDBMS. Name any two RDBMS software.
Answer: RDBMS stands for Relational Database Management System. An RDBMS is a DBMS designed especially for relational databases, which provide the facility to store and manage large amount of data. It allows to store the data in structured format using rows and columns.
Two RDBMS Software are – MySQL, Oracle
b) What is the purpose of the following clauses in a select statement?
i) ORDER BY
ii) HAVING
Answer (i) ORDER BY – ORDER BY clause is used with SELECT statement, to arranges the result of an SQL query in either ascending or descending on the basis of particular columns (fields).
(ii) HAVING – HAVING clause used with SELECT statement to apply the condition on the grouped record. It is always use with GROUP BY.
c) Site any two differences between Single-row functions and Aggregate functions.
Answer : Single Row Functions and Aggregate Functions
- Single Row Function applied on the each row while Aggregate Functions applied on the group of rows.
- Single Row Functions return multiple output i.e. output based on each row while Aggregate function returns only one result i.e. output based on group of rows.
d) What do you understand by Cartesian Product?
Answer : A Cartesian product combines the tuples of one relation with all the tuples of the other relation. It is created when two tables are joined without any join condition.
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.
Answer : DAYNAME( )
ii) To display the specified number of characters from a particular position of the given string.
Answer : MID( ) or SUBSTRING( )
iii) To display the name of the month in which you were born.
Answer: MONTHNAME( )
iv) To display your name in capital letters.
Answer : UCASE( ) or UPPER( )
Class 12 Informatics Practices- Ch.1 Querying & SQL Function – NCERT Solution
Question 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);
Class 12 Informatics Practices- Ch.1 Querying & SQL Function – NCERT Solution
Question 3. Consider the following table named “Product”, showing details of products being sold in a grocery shop.
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.
Answer: PCode
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.
Answer: SELECT PCODE, PNAME, UPRICE
FROM PRODUCT
ORDER BY PNAME DESC, UPRICE ASC;
iv. Add a new column Discount to the table Product.
Answer: ALTER TABLE PRODUCT ADD DISCOUNT DECIMAL (10,2);
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.
Answer:
mysql> UPDATE PRODUCT SET DISCOUNT = UPRICE * 0.10
-> WHERE UPRICE > 100;
vi. Increase the price by 12 per cent for all the products manufactured by Dove.
Answer:
mysql> UPDATE PRODUCT
-> SET UPRICE = UPRICE + UPRICE * 0.12
-> WHERE MANUFACTURER = ‘DOVE’;
vii. Display the total number of products manufactured by each manufacturer.
Answer:
mysql > SELECT MANUFACTURER, COUNT(*)
-> FROM PRODUCT
-> GROUP BY MANUFACTURER;
Class 12 Informatics Practices- Ch.1 Querying & SQL Function – NCERT Solution
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, Avg(UPrice) FROM Product GROUP BY Pname;
ii. SELECT DISTINCT Manufacturer FROM Product;
iii. SELECT COUNT(DISTINCT PName) FROM Product;
iv. SELECT PName, MAX(UPrice), MIN(UPrice) FROM Product GROUP BY PName;
