Class 12 Informatics Practices Code 065 Ch 1 Querying and SQL Function – NCERT Book Solution
Summary:
- 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;
Class 12 Informatics Practices- Ch.1 Querying & SQL Function – NCERT Solution
Question 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.
Answer: ALTER TABLE INVENTORY ADD DISCOUNT DECIMAL(10,2);
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.
Answer: (I) UPDATE INVENTORY SET DISCOUNT = NULL WHERE MODEL = ‘LXI’;
(II) UPDATE INVENTORY SET DISCOUNT = PRICE * 0.10 WHERE MODEL = ‘VXI’;
(II) UPDATE INVENTORY SET DISCOUNT = PRICE * 0.12 WHERE MODEL NOT IN (‘LXI’, ‘VXI’)
c) Display the name of the costliest car with fuel type “Petrol”.
Answer: SELECT CARNAME FROM INVENTORY WHERE PRICE = (SELECT MAX(PRICE) FROM INVENTORY WHERE FUELTYPE = ‘PETROL’ );
d) Calculate the average discount and total discount available on Car4.
Answer: SELECT AVG(DISCOUNT), SUM(DISCOUNT) FROM INVENTORY GROUP BY CARNAME HAVING CARNAME = ‘CAR4’;
e) List the total number of cars having no discount.
Answer: SELECT * FROM INVENTORY
WHERE DISCOUNT IS NULL;
Class 12 Informatics Practices- Ch.1 Querying & SQL Function – NCERT Solution
Question 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).
Write SQL queries for the following:
a) Create the database Streams_Of_Students.
Answer: mysql> CREATE DATABASES STREAMS_OF_STUDENTS;
b) Create the table Student by choosing appropriate data types based on the data given in the table.
Answer: mysql> CREATE TABLE STREAM (
STCODE VARCHAR(4) PRIMARY KEY,
STREAM VARCHAR(30) );
mysql> CREATE TABLE STUDENT (
ADMNO INT(4) PRIMARY KEY,
NAME VARCHAR(30) NOT NULL,
STCODE VARCHAR(4),
FOREIGN KEY (STCODE) REFERENCES STREAM(STCODE) );
c) Identify the Primary keys from tables Student and Stream. Also, identify the foreign key from the table Stream.
Answer: TABLE : STUDENT
PRIMARY KEY : ADMNO, FOREIGN KEY : STCODE
TABLE : STREAM
PRIMARY KEY : STCODE
d) Jay has now changed his stream to Humanities. Write an appropriate SQL query to reflect this change.
Answer: mysql> UPDATE STUDENT SET STCODE = ‘HUMANITIES’ WHERE NAME = ‘JAY’;
e) Display the names of students whose names end with the character ‘a’. Also, arrange the students in alphabetical order.
Answer: mysql> SELECT NAME FROM STUDENT
WHERE NAME LIKE ‘%A’
ORDER BY NAME ASC;
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).
Answer: mysql> SELECT NAME, STREAM FROM STUDENT, STREAM
WHERE STUDENT.STCODE = STREAM.STCODE
AND STREAM.STREAM IN (‘SCIENCE’, ‘HUMANITIES’)
ORDER BY NAME, ADMNO
g) List the number of students in each stream having more than 1 student.
Answer: mysql> SELECT STREAM, COUNT(STREAM) FROM STUDENT, STREAM
WHERE STUDENT.STCODE = STREAM.STCODE
GROUP BY STREAM
HAVING COUNT(STREAM) > 1;
h) Display the names of students enrolled in different streams, where students are arranged in descending order of admission number.
Answer: mysql> SELECT NAME, STREAM FROM STUDENT, STREAM
WHERE STUDENT.STCODE = STREAM.STCODE
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.
Answer: mysql> SELECT * FROM STUDENT, STREAM;
DEGREE OF CARTESIAN PRDUCT OF STUDENT & STREAM = 5 (3+2)
CARDINALITY OF CARTESIAN PRODUCT OF STUDENT & STREAM = 18 (6 X 3)
j) Add a new column ‘TeacherIncharge” in the Stream table. Insert appropriate data in each row.
Answer: ALTER TABLE STREAM ADD COLUMN TEACHERINCHAGE VARCHAR(30);
Insert appropriate data in each row
a) mysql>UPDATE STREAM SET TEACHERINCHARGE = ‘ANJEEV SINGH’ WHERE STREAM = ‘SCIENCE’;
b) mysql>UPDATE STREAM SET TEACHERINCHARGE = ‘RASHMI’ WHERE STREAM = ‘COMMERCE’;
c) mysql> UPDATE STREAM SET TEACHERINCHARGE = ‘AMRIT SINGH WHERE STREAM = ‘HUMANITIES’;
k) List the names of teachers and students.
Answer: mysql>SELECT NAME, TEACHERINCHARGE
FROM STUDENT, STREAM
WHERE 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?
Answer: => CARDINALITY OF STUDENT & STREAM TABLE : IS 18 (SAME) Because no any new rows are added
=> DEGREE OF STUDENT & STREAM TABLE: 3 + 3= 6, Because 1 column is added in STREAM table.
Class 11 Informatics Practices NCERT Solution
Class 12 Informatics Practices NCERT Solution
Class 11 Computer Science NCERT Solution
Class 12 Computer Science NCERT Solution
