Informatics Practices
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";
Answer
(i) SELECT LEFT(SEGMENT, 2) FROM CAR_SALES WHERE FUEL= "PETROL";
+------------------+
| LEFT(SEGMENT, 2) |
+------------------+
| Co |
| MU |
| SU |
| Se |
+------------------+
Working
In the query SELECT LEFT(SEGMENT, 2) FROM CAR_SALES WHERE FUEL= "PETROL";, the function LEFT(SEGMENT, 2) takes the leftmost characters of each SEGMENT value, starting from the first character, and returns two characters. The WHERE FUEL = 'PETROL' clause filters the rows to include only those with 'PETROL' as the fuel type.
(ii) SELECT (QT2-QT1)/2 "AVG SALE" FROM CAR_SALES WHERE SEGMENT= "SUV";
+------------+
| AVG SALE |
+------------+
| 13500.0000 |
| 6000.0000 |
+------------+
Working
The SQL query SELECT (QT2-QT1)/2 "AVG SALE" FROM CAR_SALES WHERE SEGMENT= "SUV"; calculates the average sale for the "SUV" segment in the CAR_SALES table. It does this by subtracting the first quarter sales (QT1) from the second quarter sales (QT2) for each record in the "SUV" segment and then dividing the result by 2. The alias "AVG SALE" is assigned to the computed value.
(iii) SELECT SUM(QT1) "TOT SALE" FROM CAR_SALES WHERE FUEL= "DIESEL";
+----------+
| TOT SALE |
+----------+
| 67000 |
+----------+
Working
The query SELECT SUM(QT1) "TOT SALE" FROM CAR_SALES WHERE FUEL= "DIESEL"; calculates the total sales for the "DIESEL" fuel type in the CAR_SALES table. It does this by summing up the values in the QT1 column for rows where the FUEL column is equal to "DIESEL". The alias "TOT SALE" is assigned to the computed sum.
Related Questions
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'.
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.
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 ;
Given the table LIBRARY :
No Title Author Type Pub Qty Price 1 Data Structure Lipschutz DS McGraw 4 217 2 Computer Studies French FND Galgotia 2 75 3 Advanced Pascal Schildt PROG McGraw 4 350 4 Dbase dummies Palmer DBMS PustakM 5 130 5 Mastering C + + Gurewich PROG BPB 3 295 6 Guide Network Freed NET ZPress 3 200 7 Mastering Foxpro Seigal DBMS BPB 2 135 8 DOS guide Norton OS PHI 3 175 9 Basic for Beginners Morton PROG BPB 3 40 10 Mastering Window Cowart OS BPB 1 225 Give the output of following SQL commands on the basis of table Library.
(i) SELECT UPPER(Title) FROM Library WHERE Price < 150 ;
(ii) SELECT CONCAT(Author, Type) FROM Library WHERE Qty < 3 ;
(iii) SELECT MOD(Qty, 4) FROM Library ;