Chapter 1
Querying and SQL Functions
Exercise
1. Answer the following questions:
a) Define RDBMS. Name any two RDBMS software.
Answer:
b) What is the purpose of the following clauses in a select statement?
i) ORDER BY
ii) HAVING
Answer:
c) Site any two differences between Single_row functions and Aggregate functions.
Answer:
d) What do you understand by Cartesian Product?
Answer:
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 specifi ed 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.
Answer:
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);
Answer:
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.
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.
Answer:
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 NOTES Product;
iv. SELECT PName, MAX(UPrice), MIN(UPrice) FROM Product GROUP BY PName;
Answer:
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:
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:
c) Display the name of the costliest car with fuel type “Petrol”.
Answer:
d) Calculate the average discount and total discount available on Car4.
Answer:
e) List the total number of cars having no discount.
Answer:
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:
b) Create the table Student by choosing appropriate data types based on the data given in the table.
Answer:
c) Identify the Primary keys from tables Student and Stream. Also, identify the foreign key from the table Stream.
Answer:
d) Jay has now changed his stream to Humanities. Write an appropriate SQL query to reflect this change.
Answer:
e) Display the names of students whose names end with the character ‘a’. Also, arrange the students in alphabetical order.
Answer:
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:
g) List the number of students in each stream having more than 1 student.
Answer:
h) Display the names of students enrolled in different streams, where students are arranged in descending order of admission number.
Answer:
i) Show the Cartesian product on the Student and Stream table. Also mention the degree and cardinality produced after applying the Cartesian product.
Answer:
j) Add a new column ‘TeacherIncharge” in the Stream table. Insert appropriate data in each row.
Answer:
k) List the names of teachers and students.
Answer:
l) If Cartesian product is again applied on Student and Stream tables, what will be the degree and cardinality of this modified table?
Answer: