Informatics Practices
Based on the SQL table CAR_SALES, write suitable queries for the following :
| NUMBER | SEGMENT | FUEL | QT1 | QT2 |
|---|---|---|---|---|
| 1 | Compact HatchBack | Petrol | 56000 | 70000 |
| 2 | Compact HatchBack | Diesel | 34000 | 40000 |
| 3 | MUV | Petrol | 33000 | 35000 |
| 4 | MUV | Diesel | 14000 | 15000 |
| 5 | SUV | Petrol | 27000 | 54000 |
| 6 | SUV | Diesel | 18000 | 30000 |
| 7 | Sedan | Petrol | 8000 | 10000 |
| 8 | Sedan | Diesel | 1000 | 5000 |
(i) Display fuel wise average sales in the first quarter.
(ii) Display segment wise highest sales in the second quarter.
(iii) Display the records in the descending order of sales in the second quarter.
Answer
(i)
SELECT FUEL, AVG(QT1) AS Avg_Sales_QT1
FROM CAR_SALES
GROUP BY FUEL;
Output
+--------+---------------+
| FUEL | Avg_Sales_QT1 |
+--------+---------------+
| Petrol | 31000.0000 |
| Diesel | 16750.0000 |
+--------+---------------+
(ii)
SELECT SEGMENT, MAX(QT2) AS HIGHEST_SALES
FROM CAR_SALES
GROUP BY SEGMENT;
Output
+-------------------+---------------+
| SEGMENT | HIGHEST_SALES |
+-------------------+---------------+
| Compact HatchBack | 70000 |
| MUV | 35000 |
| SUV | 54000 |
| Sedan | 10000 |
+-------------------+---------------+
(iii)
SELECT * FROM CAR_SALES
ORDER BY QT2 DESC;
Output
+--------+-------------------+--------+-------+-------+
| NUMBER | SEGMENT | FUEL | QT1 | QT2 |
+--------+-------------------+--------+-------+-------+
| 1 | Compact HatchBack | Petrol | 56000 | 70000 |
| 5 | SUV | Petrol | 27000 | 54000 |
| 2 | Compact HatchBack | Diesel | 34000 | 40000 |
| 3 | MUV | Petrol | 33000 | 35000 |
| 6 | SUV | Diesel | 18000 | 30000 |
| 4 | MUV | Diesel | 14000 | 15000 |
| 7 | Sedan | Petrol | 8000 | 10000 |
| 8 | Sedan | Diesel | 1000 | 5000 |
+--------+-------------------+--------+-------+-------+
Related Questions
Write the SQL functions which will perform the following operations :
(i) To display the name of the month of the current date.
(ii) To remove spaces from the beginning and end of a string, "Panorama".
(iii) To display the name of the day e.g., Friday or Sunday from your date of birth, dob.
(iv) To display the starting position of your first name(fname) from your whole name (name).
(v) To compute the remainder of division between two numbers, n1 and n2.
Write suitable SQL query for the following :
(i) Display 7 characters extracted from 7th left character onwards from the string 'INDIA SHINING'.
(ii) Display the position of occurrence of string 'COME' in the string 'WELCOME WORLD'.
(iii) Round off the value 23.78 to one decimal place.
(iv) Display the remainder of 100 divided by 9.
(v) Remove all the expected leading and trailing spaces from a column userid of the table 'USERS'.
Predict the output of the following queries based on the table CAR_SALES given below :
NUMBER SEGMENT FUEL QT1 QT2 1 Compact HatchBack Petrol 56000 70000 2 Compact HatchBack Diesel 34000 40000 3 MUV Petrol 33000 35000 4 MUV Diesel 14000 15000 5 SUV Petrol 27000 54000 6 SUV Diesel 18000 30000 7 Sedan Petrol 8000 10000 8 Sedan Diesel 1000 5000 (i) SELECT LEFT(SEGMENT, 2) FROM CAR_SALES WHERE FUEL= "PETROL";
(ii) SELECT (QT2-QT1)/2 "AVG SALE" FROM CAR_SALES WHERE SEGMENT= "SUV";
(iii) SELECT SUM(QT1) "TOT SALE" FROM CAR_SALES WHERE FUEL= "DIESEL";
Given the following table :
Table : STUDENT1
No. Name Stipend Stream AvgMark Grade Class 1 Karan 400.00 Medical 78.5 B 12B 2 Divakar 450.00 Commerce 89.2 A 11C 3 Divya 300.00 Commerce 68.6 C 12C 4 Arun 350.00 Humanities 73.1 B 12C 5 Sabina 500.00 Nonmedical 90.6 A 11A 6 John 400.00 Medical 75.4 B 12B 7 Robert 250.00 Humanities 64.4 C 11A 8 Rubina 450.00 Nonmedical 88.5 A 12A 9 Vikas 500.00 Nonmedical 92.0 A 12A 10 Mohan 300.00 Commerce 67.5 C 12C Give the output of following SQL statement :
(i) SELECT TRUNCATE(AvgMark) FROM Student1 WHERE AvgMark < 75 ;
(ii) SELECT ROUND(AvgMark) FROM Student1 WHERE Grade = 'B' ;
(iii) SELECT CONCAT(Name, Stream) FROM Student1 WHERE Class = '12A' ;
(iv) SELECT RIGHT(Stream, 2) FROM Student1 ;