Question 1

Answer the following questions:
a) Define RDBMS. Name any two RDBMS software.
b) What is the purpose of the following clauses in a select statement?
i) ORDER BY
ii) HAVING
c) Site any two differences between Single_row functions and Aggregate functions.
d) What do you understand by Cartesian Product?
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.
ii) To display the specified number of characters from a particular position of the given string.
iii) To display the name of the month in which you were born.
iv) To display your name in capital letters.

Accepted Answer

a) RDBMS (Relational Database Management System) is a database management system based on the relational model that stores data in tables (relations) consisting of rows and columns. Examples include MySQL and Oracle.

b) Purpose of clauses:
i) ORDER BY: Used to sort the result set of a query by one or more columns either in ascending or descending order.
ii) HAVING: Used to filter groups created by GROUP BY clause based on a condition.

c) Differences between Single_row functions and Aggregate functions:
- Single_row functions operate on single row and return a single value (e.g., UPPER(), LENGTH()).
- Aggregate functions operate on multiple rows and return a single summarised value (e.g., SUM(), AVG()).

- Single_row functions do not group data; Aggregate functions are used with GROUP BY.

d) Cartesian Product is the result of joining every row of one table with every row of another table, producing all possible combinations.

e) Functions:
i) DAYNAME() function to display the day name from a date.
ii) MID() or SUBSTR() function to display specified number of characters from a string starting at a particular position.
iii) MONTHNAME() function to display the month name from a date.
iv) UPPER() function to convert text to uppercase. — Step-by-step explanation:
- RDBMS definition and examples are standard.
- ORDER BY sorts data; HAVING filters grouped data.
- Single_row functions work on individual rows; Aggregate functions summarize multiple rows.
- Cartesian product explained as all combinations.
- Functions named correspond to SQL standard functions for the operations.

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);

Accepted Answer

a) POW(2,3) returns 8 because 2 raised to the power 3 is 8.

b) ROUND(123.2345, 2) returns 123.23 (rounded to 2 decimal places).
   ROUND(342.9234, -1) rounds to nearest 10, returns 340.

c) LENGTH("Informatics Practices") returns 20 (number of characters including space).

d) YEAR("1979/11/26") returns 1979.
   MONTH("1979/11/26") returns 11.
   DAY("1979/11/26") returns 26.
   MONTHNAME("1979/11/26") returns 'November'.

e) LEFT("INDIA",3) returns 'IND'.
   RIGHT("Computer Science",4) returns 'ence'.

f) MID("Informatics",3,4) returns 'forma' (starting at position 3, 4 characters).
   SUBSTR("Practices",3) returns 'actices' (from position 3 to end). — Each function's output is explained:
- POW calculates power.
- ROUND rounds numbers to specified decimal or tens place.
- LENGTH counts characters.
- YEAR, MONTH, DAY, MONTHNAME extract date parts.
- LEFT and RIGHT extract substrings from left or right.
- MID and SUBSTR extract substrings from specified positions.

Question 3

Consider the following table named "Product", showing details of products being sold in a grocery shop.

|  PCode | PName | UPrice | Manufacturer  |
| --- | --- | --- | --- |
|  P01 | Washing Powder | 120 | Surf  |
|  P02 | Tooth Paste | 54 | Colgate  |
|  P03 | Soap | 25 | Lux  |
|  P04 | Tooth Paste | 65 | Pepsodant  |
|  P05 | Soap | 38 | Dove  |
|  P06 | Shampoo | 245 | Dove  |

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.
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.
iv. Add a new column Discount to the table Product.
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.
vi. Increase the price by 12 per cent for all the products manufactured by Dove.
vii. Display the total number of products manufactured by each manufacturer.

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;
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;

Accepted Answer

a) SQL Queries:

i. CREATE TABLE Product (
  PCode VARCHAR(5) PRIMARY KEY,
  PName VARCHAR(50),
  UPrice INT,
  Manufacturer VARCHAR(50)
);

ii. Primary key is PCode.

iii. SELECT PCode, PName, UPrice FROM Product ORDER BY PName DESC, UPrice ASC;

iv. ALTER TABLE Product ADD COLUMN Discount DECIMAL(5,2);

v. UPDATE Product SET Discount = CASE WHEN UPrice > 100 THEN UPrice * 0.10 ELSE 0 END;

vi. UPDATE Product SET UPrice = UPrice * 1.12 WHERE Manufacturer = 'Dove';

vii. SELECT Manufacturer, COUNT(*) AS TotalProducts FROM Product GROUP BY Manufacturer;

b) Outputs:

i. SELECT PName, Average(UPrice) FROM Product GROUP BY PName;
- Tooth Paste: (54+65)/2 = 59.5
- Soap: (25+38)/2 = 31.5
- Washing Powder: 120
- Shampoo: 245

ii. SELECT DISTINCT Manufacturer FROM Product;
- Surf
- Colgate
- Lux
- Pepsodant
- Dove

iii. SELECT COUNT(DISTINCT PName) FROM Product;
- 4 (Washing Powder, Tooth Paste, Soap, Shampoo)

iv. SELECT PName, MAX(UPrice), MIN(UPrice) FROM Product GROUP BY PName;
- Tooth Paste: MAX=65, MIN=54
- Soap: MAX=38, MIN=25
- Washing Powder: MAX=120, MIN=120
- Shampoo: MAX=245, MIN=245 — Stepwise:
- Table creation with data types and primary key.
- Sorting with ORDER BY on multiple columns.
- Adding column with ALTER.
- Conditional update with CASE.
- Updating prices with WHERE.
- Grouping and counting manufacturers.
- Aggregate functions AVG, COUNT, MAX, MIN applied as per queries.

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.
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.

c) Display the name of the costliest car with fuel type "Petrol".
d) Calculate the average discount and total discount available on Car4.
e) List the total number of cars having no discount.

Accepted Answer

a) ALTER TABLE INVENTORY ADD COLUMN Discount DECIMAL(5,2);

b) UPDATE INVENTORY SET Discount = CASE 
  WHEN Model = 'LXI' THEN 0
  WHEN Model = 'VXI' THEN Price * 0.10
  ELSE Price * 0.12
END;

c) SELECT CarName FROM INVENTORY WHERE FuelType = 'Petrol' ORDER BY Price DESC LIMIT 1;

d) SELECT AVG(Discount) AS AverageDiscount, SUM(Discount) AS TotalDiscount FROM INVENTORY WHERE CarName = 'Car4';

e) SELECT COUNT(*) FROM INVENTORY WHERE Discount = 0; — Stepwise:
- Adding column with ALTER.
- Using CASE in UPDATE to assign discounts based on model.
- Selecting costliest petrol car by ordering descending and limiting to 1.
- Calculating average and sum of discounts for Car4.
- Counting cars with zero discount.

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).

|  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:

a) Create the database Streams_Of_Students.
b) Create the table Student by choosing appropriate data types based on the data given in the table.
c) Identify the Primary keys from tables Student and Stream. Also, identify the foreign key from the table Stream.
d) Jay has now changed his stream to Humanities. Write an appropriate SQL query to reflect this change.
e) Display the names of students whose names end with the character 'a'. Also, arrange the students in alphabetical order.
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).
g) List the number of students in each stream having more than 1 student.
h) Display the names of students enrolled in different streams, where students are arranged in descending order of admission number.
i) Show the Cartesian product on the Student and Stream table. Also mention the degree and cardinality produced after applying the Cartesian product.
j) Add a new column "TeacherIncharge" in the Stream table. Insert appropriate data in each row.
k) List the names of teachers and students.
l) If Cartesian product is again applied on Student and Stream tables, what will be the degree and cardinality of this modified table?

Accepted Answer

a) CREATE DATABASE Streams_Of_Students;

b) CREATE TABLE Student (
  AdmNo INT PRIMARY KEY,
  Name VARCHAR(50),
  StCode VARCHAR(5),
  FOREIGN KEY (StCode) REFERENCES Stream(StCode)
);

CREATE TABLE Stream (
  StCode VARCHAR(5) PRIMARY KEY,
  Stream VARCHAR(50)
);

c) Primary keys:
- Student: AdmNo
- Stream: StCode
Foreign key:
- Student.StCode references Stream.StCode

d) UPDATE Student SET StCode = 'S03' WHERE Name = 'Jay';

e) SELECT Name FROM Student WHERE Name LIKE '%a' ORDER BY Name ASC;

f) SELECT S.Name FROM Student S JOIN Stream St ON S.StCode = St.StCode WHERE St.Stream IN ('Science', 'Humanities') ORDER BY S.Name ASC, S.AdmNo ASC;

g) SELECT StCode, COUNT(*) AS NumberOfStudents FROM Student GROUP BY StCode HAVING COUNT(*) > 1;

h) SELECT Name FROM Student ORDER BY AdmNo DESC;

i) SELECT * FROM Student CROSS JOIN Stream;
Degree = sum of columns in Student and Stream = 3 + 2 = 5 columns.
Cardinality = number of rows in Student * number of rows in Stream = 6 * 3 = 18 rows.

j) ALTER TABLE Stream ADD COLUMN TeacherIncharge VARCHAR(50);

-- Insert data example:
UPDATE Stream SET TeacherIncharge = 'Mr. Sharma' WHERE StCode = 'S01';
UPDATE Stream SET TeacherIncharge = 'Ms. Gupta' WHERE StCode = 'S02';
UPDATE Stream SET TeacherIncharge = 'Mrs. Singh' WHERE StCode = 'S03';

k) SELECT TeacherIncharge, Name FROM Stream JOIN Student ON Stream.StCode = Student.StCode;

l) After adding TeacherIncharge column, degree = 6 (Student columns) + 3 (Stream columns including TeacherIncharge) = 6 columns.
Cardinality remains 18 (6*3).

Note: Degree is total number of columns; Cardinality is total number of rows. — Stepwise:
- Database and table creation with keys.
- Updating data with UPDATE.
- Using LIKE and ORDER BY.
- JOIN queries for filtering and ordering.
- GROUP BY with HAVING.
- Cartesian product explanation with degree and cardinality.
- Altering table to add column and updating data.
- Joining tables to list teachers and students.
- Recalculation of degree and cardinality after modification.

Question 6

Which of the following is NOT a relation in the CARSHOWROOM database schema?

Accepted Answer

SALESPERSON — The CARSHOWROOM database schema consists of four relations: INVENTORY, CUSTOMER, SALE, and EMPLOYEE. SALESPERSON is not listed as a separate relation.

Question 7

What is the primary purpose of the SALE relation in the CARSHOWROOM database?

Accepted Answer

Stores details of car sales including invoice and payment — The SALE relation stores invoice number, car ID, customer ID, sale date, payment mode, employee ID, and selling price for each car sold.

Question 8

Which SQL function would you use to calculate the remainder when dividing two numbers?

Accepted Answer

MOD() — MOD(A, B) returns the remainder after dividing A by B. POWER() calculates exponentiation, ROUND() rounds a number, and AVG() calculates average.

Question 9

What will be the output of the SQL query: SELECT ROUND(2912.564, 1); ?

Accepted Answer

2912.6 — ROUND(2912.564, 1) rounds the number to 1 decimal place, so the output is 2912.6.

Question 10

In the context of the CARSHOWROOM database, what does the SQL command ALTER TABLE INVENTORY ADD FinalPrice Numeric(10,1); do?

Accepted Answer

Adds a new column named FinalPrice to INVENTORY with numeric type and 1 decimal place — The ALTER TABLE command adds a new column FinalPrice with data type Numeric(10,1) to the INVENTORY table.

Question 11

Write an SQL query to calculate the GST as 12% of Price from the INVENTORY table and round it off to one decimal place.

Accepted Answer

SELECT ROUND(12/100*Price,1) AS GST FROM INVENTORY; — The query calculates 12% of Price using 12/100*Price and rounds the result to one decimal place using ROUND function. It displays the result as GST.

Question 12

Explain the difference between single row functions and aggregate functions in SQL with examples.

Accepted Answer

(a) Single row functions operate on one row at a time and return a single value for each row. Examples include ROUND(), UPPER(), and DAY().

(b) Aggregate functions operate on a group of rows and return a single value for the entire group. Examples include COUNT(), SUM(), AVG(), MAX(), and MIN().

(c) Single row functions can be used in SELECT, WHERE, and ORDER BY clauses, while aggregate functions are typically used with GROUP BY in SELECT clause.

(d) For example, ROUND(Price,1) rounds the price for each row, whereas SUM(Price) calculates the total price of all rows.

These functions help in data analysis and summarization in SQL queries. — The answer explains the operational difference between single row and aggregate functions, their usage, and provides examples to illustrate the concepts clearly.

Question 13

Using the SALE table, write an SQL query to display InvoiceNo and commission rounded to zero decimal places.

Accepted Answer

SELECT InvoiceNo, ROUND(Commission, 0) FROM SALE; — The query selects InvoiceNo and rounds the Commission column to zero decimal places using ROUND function.

Question 14

What SQL function would you use to convert a string to uppercase? Provide an example with the string 'Informatics Practices'.

Accepted Answer

The UCASE() or UPPER() function converts a string to uppercase. Example: SELECT UPPER('Informatics Practices'); — UCASE() and UPPER() functions convert all characters in the string to uppercase letters.

Question 15

Using the CUSTOMER table, write an SQL query to display the length of each email and the substring before the '@' character.

Accepted Answer

SELECT LENGTH(Email), LEFT(Email, INSTR(Email, '@') - 1) FROM CUSTOMER; — LENGTH(Email) returns the number of characters in the email. LEFT() extracts substring from left up to position before '@' using INSTR() to find '@' position.

Querying and SQL

Querying and SQL — Study Notes

1.1 Introduction

1.2 Functions in SQL

1.2.1 Single Row Functions

Practice Questions — Querying and SQL

All 7 Chapters in Informatics Practices