MY_SQL - vimlesh-verma16/Notes GitHub Wiki

Some DDL Commands and Usage

-- Use DROP TABLE if you no longer need the table. -- Use TRUNCATE TABLE if you want to clear all data but keep the table.

---

---

---

WITH ordered_lat AS (
    SELECT 
        LAT_N,
        ROW_NUMBER() OVER (ORDER BY LAT_N) AS rn,
        COUNT(*) OVER () AS total_rows
    FROM STATION
)
SELECT ROUND(AVG(LAT_N), 4) AS median_lat
FROM ordered_lat
WHERE rn IN (
    FLOOR((total_rows + 1) / 2),
    CEIL((total_rows + 1) / 2)
);

---

SELECT ROUND(SQRT(POW(MIN(LAT_N) - MAX(LAT_N), 2) + POW(MAX(LONG_W) - MIN(LONG_W), 2)),4)
FROM STATION ;

---

Query the Western Longitude (LONG_W) for the largest Northern Latitude (LAT_N) in STATION that is less than . Round your answer to decimal places.

SELECT ROUND(MAX(LONG_W),4)
FROM STATION
WHERE  LAT_N  = (SELECT MAX(LAT_N) 
                 FROM STATION 
                 WHERE LAT_N < 137.2345)

---

SELECT CONCAT('There are a total of ', COUNT(*), ' ', LOWER(occupation), 's.') AS result
FROM OCCUPATIONS
GROUP BY occupation
ORDER BY result ASC;

---

Write a query identifying the type of each record in the TRIANGLES table using its three side lengths. Output one of the following statements for each record in the table:

Equilateral: It's a triangle with

sides of equal length. Isosceles: It's a triangle with sides of equal length. Scalene: It's a triangle with sides of differing lengths. Not A Triangle: The given values of A, B, and C don't form a triangle.

SELECT
  CASE 
    WHEN A + B <= C OR A + C <= B OR B + C <= A THEN 'Not A Triangle'
    WHEN A = B AND B = C THEN 'Equilateral'
    WHEN A = B OR B = C OR A = C THEN 'Isosceles'
    ELSE 'Scalene'
  END AS Triangle_Type
FROM TRIANGLES;

---

USE OF CAST,UNSIGNED,REPLACE

Samantha was tasked with calculating the average monthly salaries for all employees in the EMPLOYEES table, but did not realize her keyboard's

key was broken until after completing the calculation. She wants your help finding the difference between her miscalculation (using salaries with any zeros removed), and the actual average salary.

Write a query calculating the amount of error (i.e.: average monthly salaries), and round it up to the next integer.

SELECT CEIL(AVG(SALARY) - AVG(CAST(REPLACE(SALARY,"0","") AS Unsigned)))
FROM EMPLOYEES

---

Query the Name of any student in STUDENTS who scored higher than Marks. Order your output by the last three characters of each name. If two or more students both have names ending in the same last three characters (i.e.: Bobby, Robby, etc.), secondary sort them by ascending ID. please write query for this ?

SELECT NAME
FROM STUDENTS
WHERE MARKS > Marks
ORDER BY RIGHT(NAME, 3) ASC, ID ASC;

---

Query the list of CITY names from STATION that either do not start with vowels or do not end with vowels. Your result cannot contain duplicates.

SELECT DISTINCT CITY
FROM STATION
WHERE CITY NOT LIKE 'A%' 
AND CITY NOT LIKE 'E%' 
AND CITY NOT LIKE 'I%' 
AND CITY NOT LIKE 'O%' 
AND CITY NOT LIKE 'U%'
OR CITY NOT LIKE '%A' 
AND CITY NOT LIKE '%E' 
AND CITY NOT LIKE '%I' 
AND CITY NOT LIKE '%O' 
AND CITY NOT LIKE '%U';

SELECT DISTINCT CITY
FROM STATION
WHERE CITY LIKE "a%" 
   or CITY LIKE "e%"
   or CITY LIKE "i%"
   or CITY LIKE "o%"
   or CITY LIKE "u%";

---

starting and ending both with vowels

SELECT CITY  
FROM STATION  
WHERE LEFT(CITY, 1) IN ('A', 'E', 'I', 'O', 'U')  
AND RIGHT(CITY, 1) IN ('A', 'E', 'I', 'O', 'U');

---

Execution Flow

Query the two cities in STATION with the shortest and longest CITY names, as well as their respective lengths (i.e.: number of characters in the name). If there is more than one smallest or largest city, choose the one that comes first when ordered alphabetically.

(
SELECT CITY,CHAR_LENGTH(CITY) AS NAME_LENGTH
FROM STATION
ORDER BY NAME_LENGTH ASC,CITY ASC
LIMIT 1   
)
UNION ALL
(
SELECT CITY,CHAR_LENGTH(CITY) AS NAME_LENGTH
FROM STATION
ORDER BY NAME_LENGTH DESC,CITY DESC
LIMIT 1  
);

🚀 Order of Execution in SQL

1️⃣ FROM STATION

The database first identifies the table (STATION) from which it needs to fetch data.

2️⃣ SELECT CITY, CHAR_LENGTH(CITY) AS NAME_LENGTH

It retrieves the CITY column. It calculates the length of each city name using CHAR_LENGTH(CITY). The calculated length is assigned the alias NAME_LENGTH.

3️⃣ ORDER BY NAME_LENGTH ASC, CITY ASC

The database sorts the rows: First, by NAME_LENGTH in ascending order (smallest name first). If there is a tie (same length), it sorts alphabetically (A → Z) based on CITY.

4️⃣ LIMIT 1

After sorting, only the first row is returned (the shortest city name, alphabetically first in case of a tie).