Informatics Practices
Consider the following table named "GARMENT".
| GCODE | GNAME | SIZE | COLOUR | PRICE |
|---|---|---|---|---|
| 111 | T-Shirt | XL | Red | 1400.234 |
| 112 | Jeans | L | Blue | 1600.123 |
| 113 | Skirt | M | Black | 1100.65 |
| 115 | Trousers | L | Brown | 1500.50 |
| 116 | Ladies Top | L | Pink | 1200.25 |
Write SQL queries using SQL functions to perform the following operations:
(i) Display the name and price after rounding off to one decimal place.
(ii) Display all Gname in upper case.
(iii) Display the last three characters from Gname.
(iv) Display the highest Gcode from the table GARMENT.
(v) Display the sum of all prices of size 'L'.
Answer
(i)
SELECT GNAME, ROUND(PRICE, 1) AS ROUNDED_PRICE
FROM GARMENT;
Output
+------------+---------------+
| GNAME | ROUNDED_PRICE |
+------------+---------------+
| T-Shirt | 1400.2 |
| Jeans | 1600.1 |
| Skirt | 1100.7 |
| Trousers | 1500.5 |
| Ladies Top | 1200.3 |
+------------+---------------+
(ii)
SELECT UPPER(GNAME) AS GNAME_UPPERCASE
FROM GARMENT;
Output
+-----------------+
| GNAME_UPPERCASE |
+-----------------+
| T-SHIRT |
| JEANS |
| SKIRT |
| TROUSERS |
| LADIES TOP |
+-----------------+
(iii)
SELECT RIGHT(GNAME, 3) AS LAST_THREE_CHARACTERS
FROM GARMENT;
Output
+-----------------------+
| LAST_THREE_CHARACTERS |
+-----------------------+
| irt |
| ans |
| irt |
| ers |
| Top |
+-----------------------+
(iv)
SELECT MAX(GCODE) AS HIGHEST_GCODE
FROM GARMENT;
Output
+---------------+
| HIGHEST_GCODE |
+---------------+
| 116 |
+---------------+
(v)
SELECT SUM(PRICE) AS TOTAL_PRICE_SIZE_L
FROM GARMENT
WHERE SIZE = 'L';
Output
+--------------------+
| TOTAL_PRICE_SIZE_L |
+--------------------+
| 4300.873 |
+--------------------+
Related Questions
Consider the following table named "Student":
RollNo Name Marks Grade Fees Stream 1 Mishra 30 C 6000 Commerce 2 Gupta 48 B 15000 Arts 3 Khan 66 A 4800 Science 4 Chaddha 24 C 12500 Commerce 5 Yadav 23 A 10000 Arts Write the SQL functions which will perform the following operations:
(i) To show the sum of fees of all students.
(ii) To display maximum and minimum marks.
(iii) To count different types of grades available.
(iv) Write a query to count grade-wise total number of students.
(i) Consider the dataframe "EMP":
Name Basic Da Hra E1 Sanya 9500 3000 2000 E2 Krish 7000 5000 1900 E3 Rishav 9650 1500 2100 E4 Deepak 7500 2000 2700 E5 Kriti 9200 1800 500Give the output
EMP.iloc[1, 2]=8000 EMP.Hra = EMP.Hra + 200 print(EMP)(ii) Write a Python statement to change the name 'Rishav' to 'Rishab' in the above dataframe.
Or
(Option for part ii only)
Write a Python statement to calculate the sum of Basic, Da, and Hra and assign it to the column 'Salary'.
Write the SQL functions which will perform the following operations:
(i) To return the summation of all non-NULL values of a data set.
(ii) To extract a substring starting from a position with a specific length.
(iii) To convert a string to uppercase.
(iv) To return the year for a specified date.
(v) To round off a number to a specified number of decimal places.
A company in Mega Enterprises has 4 wings of buildings as shown in the diagram:
Centre-to-centre distances between various buildings:
W3 to W1 — 50m
W1 to W2 — 60m
W2 to W4 — 25m
W4 to W3 — 170m
W3 to W2 — 125m
W1 to W4 — 90mNumber of computers in each of the wings:
W1 — 150
W2 — 15
W3 — 15
W4 — 25Computers in each wing are networked but the wings are not networked. The company has now decided to connect the wings also.
(i) Suggest the most suitable cable layout for the above connections.
(ii) Suggest the most appropriate topology of the connection between the wings.
(iii) The company wants internet accessibility in all the wings. Suggest a suitable technology.
(iv) Suggest the placement of the repeater with justification.
(v) Suggest the placement of the Hub/Switch with justification if the company wants to minimize network traffic.