class 12 CHAPTER 1 QUERYING AND SQL FUNCTIONS
Answer the following questions:
Define RDBMS. Name any two RDBMS software.
RDBMS stands for Relational Database Management
System.
It allows creating a relationship between more than one
table and access data from multiple tables easily.
Basically, RDBMS is a program that offers commands to
create, update, and manage the database with multiple
tables.
The following are examples of RDBMS software:
MySQL
PostgreSQL
Oracle
SQL Server
SQLite
What is the purpose of the following clauses in a select
statement?NCERT textbooks
ORDER BY: It is used to sort the records in ascending or
descending order. If you want to sort records in
descending order use the desc keyword at the end of the
Select query.
HAVING: Having is used to restrict the result of the group
by function rows according to condition. It will work as
same as where clause.
Site any two differences between Single_row functions
and Aggregate functions.
Single row functions operate on a single row from the
table at a time whereas aggregate functions operate on a
set of rows at a time.
Single row function display output for single row and
display number rows whereas aggregate functions display
out as a single value from a set of rows.
Single row function can be used with select, where and
order by clause whereas aggregate functions can be only
used with select clause.
Single row function examples are math, string and
date/time functions. Aggregate functions examples are
ma, min, average, count etc.
What do you understand by Cartesian Product?
Cartesian product refers to all possible pairs of rows from
two relations.
It will just return a product of rows and the sum of
columns from multiple relations with or without common
attributes.
Suppose two tables student and result have 6 and 8 rows
as well as 4 and 5 columns respectively. Then the
cartesian product will return 48 rows and 9 columns as a
result.
Write the name of the functions to perform the following
operations:
To display the day like “Monday”, “Tuesday” from the
date when India got independence.
dayname()
To display the specified number of characters from a
particular position of the given string.
substr(),mid()
To display the name of the month in which you were
born.
monthname()
To display your name in capital letters.
upper(),ucase()
Write the output produced by the following SQL
commands:
SELECT POW(2,3);
8
SELECT ROUND(123.2345, 2), ROUND(342.9234,-1);
123.23 340
SELECT LENGTH(“Informatics Practices”);
21
SELECT YEAR(“1979/11/26”), MONTH(“1979/11/26”),
DAY(“1979/11/26”), MONTHNAME(“1979/11/26”);
1979 11 26 November
SELECT LEFT(“INDIA”,3), RIGHT(“Computer Science”,4);
IND ence
SELECT MID(“Informatics”,3,4), SUBSTR(“Practices”,3);
form actices
Consider the following table named “Product”, showing
details of products being sold in a grocery shop.
Pcode PName UPrice Manufacture
P01 Washing Powder 120 Surf
P02 Tooth Paste 54 Colgate
P03 Soap 25 Lux
P04 Tooth Paste 65 Pepsodent
P05 Soap 38 Dove
P06 Shampoo 245 Dove
a) Write SQL queries for the following:
Create the table Product with appropriate data types and
constraints.
create table product(
-> pcode char(3) Primary key,
-> Pname varchar(25) Not Null,
-> Uprice int(4),
-> Manufacturer varchar(30));
mysql>insert into product values("P01","Washing
Powder",120,"Surf");
Identify the primary key in Product. > pcode
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;
Add a new column Discount to the table Product.
alter table product add discount decimal(8,2);
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 =0;
update product set discount=0.10*uprice where uprice
>100;
Increase the price by 12 per cent for all the products
manufactured by Dove.
update product set uprice =uprice+0.12*uprice where
manufacturer="dove";
Display the total number of products manufactured by
each manufacturer.
select manufacturer, count(*)
-> from product group by manufacturer;
b) 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;
Error – Average() function does’nt exist in MySQL
SELECT DISTINCT Manufacturer FROM Product;
Manufacturer |
+--------------+
| Surf |
| Colgate |
| Lux |
| Pepsodant |
| Dove |
+--------------+
SELECT COUNT(DISTINCT PName) FROM Notes Product;
+-----------------------------+
| COUNT(DISTINCT PName) |
+-----------------------------+
| 4|
SELECT PName, MAX(UPrice), MIN(UPrice) FROM Product
GROUP BY PName;
+----------------+-------------+-------------+
| PName | MAX(UPrice) | MIN(UPrice) |
+----------------+-------------+-------------+
| Washing Powder | 120 | 120 |
| Tooth Paste | 65 | 54 |
| Soap | 43 | 25 |
| Shampoo | 274 | 274 |
Using the CARSHOWROOM database given in the
chapter, write the SQL queries for the following:
Add a new column Discount in the INVENTORY table.
alter table inventory add disc decimal(9,2);
Set appropriate discount values for all cars keeping in
mind the following:
No discount is available on the LXI model.
update inventory set disc =0 where
model="LXI";
VXI model gives a 10% discount.
update inventory set disc =0.10*price where
model="VXI";
A 12% discount is given on cars other than LXI model and
VXI model.
update inventory set disc =0.12*price where model NOT
IN ("LXI","VXI");
Display the name of the costliest car with fuel type
“Petrol”.
select max(price) from inventory where
fueltype="petrol";
Calculate the average discount and total discount
available on Car4.
select avg(disc),sum(disc) from inventory where
carname="car4";
List the total number of cars having no discount.
select count(*) from inventory where disc=0;
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:
Create the database Streams_Of_Students.
mysql> create database Streams_Of_Students
Create the table Student by choosing appropriate data
types based on the data given in the table.
mysql> create table student(admno int,name
varchar(30), stcode char(6));
Identify the Primary keys from tables Student and
Stream. Also, identify the foreign key from the table
Stream.
Student:-> Primary key: admno, Foreign Key: stcode
Stream:-> Primary Key: Stcode, NO Foreign Key
Jay has now changed his stream to Humanities.
Mysql>update student set stcode=”S03” where
admno=211;
Display the names of students whose names end with the
character ‘a’. Also, arrange the students in alphabetical
order.
Mysql> select name from student where name like “%a”
order by name;
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).
Mysql> select name from student where stcode
in(“S01”,”S03”) order by name, admno;
List the number of students in each stream having more
than 1 student.
mysql> Select stcode,count() From student Group by
stcode Having count() >1;
Display the names of students enrolled in different
streams, where students are arranged in descending
order of admission number.
select s1.admno, s1.name, s2.stream from student s1,
stream s2 where s1.stcode=s2.stcode order by s1.admno
desc;
Show the Cartesian product on the Student and Stream
table. Also, mention the degree and cardinality produced
after applying the Cartesian product.
select * from student, stream;
The cartesian product on the student and stream table
has degree of 5 and cardinality of 18.
Add a new column ‘TeacherIncharge” in the Stream table.
Insert appropriate data in each row. (Do yourself)
List the names of teachers and students.
select s1.teacherincharge,s2.name from stream s1,
student s2 where s1.stcode=s2.stcode;
If Cartesian product is again applied on Student and
Stream tables, what will be the degree and cardinality of
this modified table?
The degree will be 6 and the cardinality will be 18.
