NCERT solutions for इन्फोर्मेटिक्स प्रैक्टिसिज़ [इंग्रजी] इयत्ता १२ chapter 1 - Querying and SQL Functions [Latest edition]

Using the table SALE of CARSHOWROOM database, write SQL queries for the following:

1. Display the InvoiceNo and commission value rounded off to zero decimal places.
2. Display the details of SALE where payment mode is credit card..

Using the table INVENTORY from CARSHOWROOM database, write SQL queries for the following:

1. Convert the CarMake to uppercase if its value starts with the letter ‘B’.
2. If the length of the car’s model is greater than 4 then fetch the substring starting from position 3 till the end from attribute Model.

Using the table EMPLOYEE from CARSHOWROOM database, write SQL queries for the following:

1. Display employee name and the last 2 characters of his EmpId.
2. Display designation of employee and the position of character ‘e’ in designation, if present.

Using the table EMPLOYEE of CARSHOWROOM database, list the day of birth for all employees whose salary is more than 25000.

Can we use arithmetic operators (+, −. *Can we use arithmetic operators (+, −. *, or /) on date functions?, or /) on date functions?

Find sum of Sale Price of the cars purchased by the customer having ID C0001 from table SALE.

Find the maximum and minimum commission from the SALE table.

List the total number of cars sold by each employee.

List the maximum sale made by each employee.

Define RDBMS. Name any two RDBMS software.

What is the purpose of the following clause in a select statement?

ORDER BY

What is the purpose of the following clauses in a select statement?

HAVING

Site any two differences between Single Row Functions and Aggregate Functions.

What do you understand by Cartesian Product?

Write the name of the functions to perform the following operation:

To display the day like “Monday”, and “Tuesday”, from the date when India got independence.

Write the name of the functions to perform the following operation:

To display the specified number of characters from a particular position of the given string.

Write the name of the functions to perform the following operation:

To display the name of the month in which you were born.

Write the name of the functions to perform the following operation:

To display your name in capital letters.

Write the output produced by the following SQL statement:

SELECT POW(2, 3);

Write the output produced by the following SQL statement:

SELECT ROUND(123.2345, 2),
ROUND(342.9234, −1);

Write the output produced by the following SQL statement:

SELECT LENGTH("Informatics Practices");

Write the output produced by the following SQL statement:

SELECT    YEAR(“1979/11/26”),
MONTH(“1979/11/26”),    DAY(“1979/11/26”), MONTHNAME(“1979/11/26”);

Write the output produced by the following SQL statement:

SELECT    LEFT("INDIA", 3),    RIGHT("Computer Science", 4), 

Write the output produced by the following SQL statement:

SELECT MID("Informatics",3,4), SUBSTR("Practices",3);

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

Write SQL queries for the following:

1. Create the table Product with appropriate data types and constraints.
2. Identify the primary key in the Product.
3. 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.
4. Add a new column Discount to the table Product.
5. Calculate the value of the discount in the table Product as 10 percent of the UPrice for all those products where the UPrice is more than 100, otherwise, the discount will be 0.
6. Increase the price by 12 percent for all the products manufactured by Dove.
7. Display the total number of products manufactured by each manufacturer. Write the output(s) produced by executing the following queries on the basis of the information given above in the table Product:
8. SELECT PName, Average (UPrice) FROM Product GROUP BY Pname;
   i) SELECT DISTINCT Manufacturer FROM Product;
   j) SELECT COUNT (DISTINCT PName) FROM Product;
   k) SELECT PName, MAX(UPrice), MIN(UPrice) FROM Product GROUP BY PName;

Using the CARSHOWROOM database given in the chapter, write the SQL queries for the following:

1. Add a new column Discount in the INVENTORY table.
2. 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 percent discount.
   (iii) A 12 percent discount is given on cars other than the LXI model and VXI model.
3. Display the name of the costliest car with the fuel type “Petrol”.
4. Calculate the average discount and total discount available on Baleno cars.
5. List the total number of cars having no discount.

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:

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