CH-1
Page 23 (Question 1)
1. Answer the following questions:
a) Define RDBMS. Name any two RDBMS software.
The provided sources mention learning how to create databases using MySQL. However,
they do not explicitly provide a definition for RDBMS (Relational Database Management
System) nor do they name a second RDBMS software.
b) What is the purpose of the following clauses in a select statement? i) ORDER BY The
ORDER BY clause is used for arranging records in ascending or descending order.
ii) HAVING The HAVING Clause in SQL is used to specify conditions on the rows with
GROUP BY clause.
c) Site any two differences between Single_row functions and Aggregate functions. Two
differences between Single_row functions and Multiple_row (Aggregate) functions are:
1. Single_row functions operate on a single row at a time, while multiple_row
functions operate on groups of rows.
2. Single_row functions return one result per row, while multiple_row functions return
one result for a group of rows.
d) What do you understand by Cartesian Product? Cartesian product operation combines
tuples from two relations. It results in all pairs of rows from the two input relations. The
degree (number of attributes) of the resulting relation is the sum of the degrees of both
relations, and the cardinality (number of rows) is calculated as the product of the cardinality
of the relations on which it is applied.
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. DAYNAME()
ii) To display the specified number of characters from a particular position of the given
string. MID(), SUBSTRING(), or SUBSTR()
iii) To display the name of the month in which you were born. MONTHNAME()
iv) To display your name in capital letters. UCASE() or UPPER()
Page 24 (Question 2 and 3a)
2. Write the output produced by the following SQL commands:
a) SELECT POW(2,3); 8
b) SELECT ROUND(123.2345, 2), ROUND(342.9234,-1); The sources define ROUND(N,D)
as rounding number N to D decimal places. ROUND(123.2345, 2): 123.23 (Based on the
function description)
c) SELECT LENGTH("Informatics Practices"); (The function returns the number of
characters in the specified string. "Informatics Practices" has 22 characters.) 22
d) SELECT YEAR(“1979/11/26”), MONTH(“1979/11/26”), DAY(“1979/11/26”),
MONTHNAME(“1979/11/26”); YEAR: 1979 MONTH: 11 DAY: 26 MONTHNAME: November
e) SELECT LEFT("INDIA",3), RIGHT("Computer Science",4); LEFT("INDIA", 3):
IND RIGHT("Computer Science", 4): ence
f) SELECT MID("Informatics",3,4), SUBSTR("Practices",3); MID("Informatics",
3, 4): form SUBSTR("Practices", 3): actices
3. Consider the following table named “Product”
a) Write SQL queries for the following:
i. Create the table Product with appropriate data types and constraints.
CREATE TABLE Product (
PCode VARCHAR(5) PRIMARY KEY,
PName VARCHAR(50),
UPrice NUMERIC,
Manufacturer VARCHAR(50)
);
ii. Identify the primary key in Product. 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.
SELECT PCode, PName, UPrice FROM Product ORDER BY PName DESC, UPrice ASC;
iv. Add a new column Discount to the table Product.
ALTER TABLE Product ADD Discount NUMERIC(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.
UPDATE Product SET Discount = UPrice * 0.10 WHERE UPrice > 100;
UPDATE Product SET Discount = 0 WHERE UPrice <= 100;
vi. Increase the price by 12 per cent for all the products manufactured by Dove.
UPDATE Product SET UPrice = UPrice * 1.12 WHERE Manufacturer = 'Dove';
vii. Display the total number of products manufactured by each manufacturer.
SELECT Manufacturer, COUNT(*) FROM Product GROUP BY Manufacturer;
Page 25 (Question 3b and 4)
3. 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;
PName Average(UPrice)
Washing Powder 120
Tooth Paste 59.5
Soap 31.5
Shampoo 245
ii. SELECT DISTINCT Manufacturer FROM Product;
Manufacturer
Surf
Colgate
Lux
Pepsodant
Dove
iii. SELECT COUNT(DISTINCT PName) FROM Product; 4
iv. 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 38 25
Shampoo 245 245
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.
ALTER TABLE INVENTORY ADD Discount NUMERIC(10,2);
b) Set appropriate discount values for all cars keeping in mind the following: i) No discount
is available on the LXI model.
UPDATE INVENTORY SET Discount = 0 WHERE Model = 'LXI';
ii) VXI model gives a 10% discount.
UPDATE INVENTORY SET Discount = Price * 0.10 WHERE Model = 'VXI';
iii) A 12% discount is given on cars other than LXI model and VXI model.
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”.
SELECT CarName FROM INVENTORY WHERE FuelType = 'Petrol' ORDER BY Price DESC
LIMIT 1;
d) Calculate the average discount and total discount available on Car4.
SELECT AVG(Discount), SUM(Discount) FROM INVENTORY WHERE CarName = 'Car4';
e) List the total number of cars having no discount.
SELECT COUNT(*) FROM INVENTORY WHERE Discount = 0;
Page 26 (Question 5)
5. Consider the following tables Student and Stream in the Streams_of_Students
database.
a) Create the database Streams_Of_Students.
CREATE DATABASE Streams_Of_Students;
b) Create the table Student by choosing appropriate data types based on the data given in the
table.
CREATE TABLE Student (
AdmNo INT PRIMARY KEY,
Name VARCHAR(50),
StCode CHAR(3),
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.
Primary Key (Student): AdmNo
Primary Key (Stream): StCode
Foreign Key (Stream): The Stream table itself does not contain a foreign key.
StCode acts as the foreign key in the Student table.
d) Jay has now changed his stream to Humanities (S03). Write an appropriate SQL query to
reflect this change.
UPDATE Student SET StCode = 'S03' WHERE AdmNo = 211;
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 ASC;
f) Display the names of students enrolled in Science (S01) and Humanities (S03) stream,
ordered by student name in alphabetical order, then by admission number in ascending order
(for duplicating names).
SELECT Name FROM Student WHERE StCode = 'S01' OR StCode = 'S03' ORDER BY
Name ASC, AdmNo ASC;
g) List the number of students in each stream having more than 1 student.
SELECT StCode, COUNT(*) FROM Student GROUP BY StCode HAVING COUNT(*) > 1;
h) Display the names of students enrolled in different streams, where students are arranged in
descending order of admission number.
SELECT [Link], [Link] FROM Student S JOIN Stream T ON [Link] = [Link]
ORDER BY [Link] DESC;
i) Show the Cartesian product on the Student and Stream table. Also mention the degree and
cardinality produced after applying the Cartesian product.
Query:
SELECT * FROM Student, Stream;
Degree: The Student table has 3 attributes and the Stream table has 2 attributes. The
resulting degree is the sum of the degrees: 5 (3 + 2).
Cardinality: The Student table has 6 records and the Stream table has 3 records. The
resulting cardinality is the product of the cardinalities: 18 (6 * 3).