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
