Not too simple SQL exercises

“Higher Then” Queries

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 previous three characters (i.e.: Bobby, Robby, etc.), secondary sort them by ascending ID.

Input Format

The STUDENTS table is described as follows:

The Name column only contains uppercase (A–Z) and lowercase (a–z) letters.

Sample Input

Sample Output

Ashley
Julia
Belvet

Explanation

Only Ashley, Julia, and Belvet have Marks > 75. If you look at the last three characters of each of their names, there are no duplicates, and ‘ley’ < ‘lia’ < ‘vet.’

To query the names of students in the STUDENTS table who scored higher than 75, and to order the results by the last three characters of each name, with a secondary sort by ascending ID if needed, you can use the following SQL query:

SELECT NAME
FROM STUDENTS
WHERE MARKS > 75
ORDER BY SUBSTRING(NAME, -3), ID;

Here’s an explanation of the query:

• SELECT NAME: This selects the NAME column from the STUDENTS table.
• FROM STUDENTS: This specifies the table from which to query.
• WHERE MARKS > 75: This filters the results to include only students with marks more significant than 75.
• ORDER BY SUBSTRING(NAME, -3), ID: This orders the results first by the last three characters of the NAME column (SUBSTRING(NAME, -3)) and then by the ID column as a secondary sort.

This will ensure that the students’ names are sorted by the last three characters and, in the case of a tie, sorted by the ID in ascending order.

Alphabetically print a list of #

Write a query that alphabetically prints a list of employee names (i.e., the name attribute) from the Employee table.

Input Format

The Employee table containing employee data for a company is described as follows:

where employee_id is an employee’s ID number, name is their name, months is the total number of months they’ve been working for the company, and salary is their monthly salary.

To print a list of employee names from the Employee table in alphabetical order, you can use the following SQL query:

SELECT name
FROM Employee
ORDER BY name;

Here’s how the query works:

• SELECT name: This selects the name column from the Employee table.
• FROM Employee: This specifies the table to query from.
• ORDER BY name: This orders the results by the name column in ascending alphabetical order.

This query will return the list of employee names sorted alphabetically.

Greater than # for less than #

Write a query that prints a list of employee names (i.e., the name attribute) for employees in Employee having a salary greater than $2000 per month who have been employees for less than 10 months. Sort your result by ascending employee_id.

Input Format

The Employee table containing employee data for a company is described as follows:

Where employee_id is an employee’s ID number, name is their name, months is the total number of months they’ve been working for the company, and salary is their monthly salary.

To query the list of employee names for employees who have a salary greater than $2000 per month and have been employees for less than 10 months and to sort the result by ascending employee_id, you can use the following SQL query:

SELECT name
FROM Employee
WHERE salary > 2000 AND months < 10
ORDER BY employee_id;

Here’s how the query works:

• SELECT name: This selects the name column from the Employee table.
• FROM Employee: This specifies the table to query from.
• WHERE salary > 2000 AND months < 10: This filters the results to include only those employees who have a salary greater than $2000 per month and have been employees for less than 10 months.
• ORDER BY employee_id: This orders the results by the employee_id column in ascending order.

This query will return the list of employee names that meet the specified conditions, sorted by their employee ID in ascending order.

Sum of all

Query the sum of the populations for all Japanese cities in CITY. The COUNTRYCODE for Japan is JPN. The CITY table is described as follows:

To query the sum of the populations for all Japanese cities in the CITY table where the COUNTRYCODE is JPN, you can use the following SQL query:

SELECT SUM(POPULATION) AS TotalPopulation
FROM CITY
WHERE COUNTRYCODE = 'JPN';

Here’s how the query works:

• SELECT SUM(POPULATION) AS TotalPopulation: This calculates the sum of the POPULATION column and labels the result as TotalPopulation.
• FROM CITY: This specifies the table from which to query.
• WHERE COUNTRYCODE = 'JPN': This filters the rows to include only those where the COUNTRYCODE is JPN (Japan).

This query will return the total population of all Japanese cities in the CITY table.

Query the difference between the maximum and minimum

Query the difference between the maximum and minimum populations in CITY table.

To query the difference between the maximum and minimum populations in the CITY table, you can use the following SQL query:

SELECT MAX(POPULATION) - MIN(POPULATION) AS PopulationDifference
FROM CITY;

Here’s how the query works:

• SELECT MAX(POPULATION) - MIN(POPULATION) AS PopulationDifference: This calculates the difference between the maximum population (MAX(POPULATION)) and the minimum population (MIN(POPULATION)) and labels the result as PopulationDifference.
• FROM CITY: This specifies the table to query from.

This query will return the difference between the maximum and minimum populations in the CITY table.

Find the difference between 2 calculations

To find the difference between the actual average salary and the miscalculated average salary (with zeros removed), you can use the following SQL query:

SELECT CEIL(ABS(AVG(SALARY) - AVG(CAST(REPLACE(SALARY, '0', '') AS UNSIGNED))) AS Error
FROM EMPLOYEES;

Here’s a breakdown of the query:

1. AVG(SALARY): Calculates the actual average salary.
2. REPLACE(SALARY, '0', ''): Removes zeros from the salary values. This is done using the REPLACE function which replaces all occurrences of ‘0’ with an empty string.
3. CAST(... AS UNSIGNED): Converts the resulting string back to an integer.
4. AVG(CAST(REPLACE(SALARY, '0', '') AS UNSIGNED)): Calculates the average of the modified salaries (with zeros removed).
5. ABS(...): Takes the absolute value of the difference between the actual average salary and the miscalculated average salary.
6. CEIL(...): Rounds the result up to the next integer.

This query will return the rounded-up integer value of the difference between the actual average salary and the miscalculated average salary.

Identifying record types

The TRIANGLES table is described as follows:

Each row in the table denotes the lengths of a triangle’s three sides.

To identify the type of each triangle in the TRIANGLES table based on its side lengths 𝐴A, 𝐵B, and 𝐶C, we can use SQL to classify the triangles. The query will check each row and determine if the side lengths form an equilateral, isosceles, or scalene triangle or do not form a triangle.

SELECT 
    A, 
    B, 
    C,
    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 A = C OR B = C THEN 'Isosceles'
        ELSE 'Scalene'
    END AS TriangleType
FROM 
    TRIANGLES;

Step-by-Step Explanation:

1. Not A Triangle:
   • A + B <= C OR A + C <= B OR B + C <= A
   • This condition checks if the sum of any two sides is less than or equal to the third side, which means it cannot form a triangle.
2. Equilateral:
   • A = B AND B = C
   • All three sides are equal.
3. Isosceles:
   • A = B OR A = C OR B = C
   • At least two sides are equal.
4. Scalene:
   • This is covered by the ELSE clause, meaning none of the sides are equal.

If this query still does not work, let’s also ensure the data types and table structure are correct and compatible with the query:

1. Check the Table Structure:sqlCopy codeDESCRIBE TRIANGLES;
2. Verify Data Types:
   • Ensure that A, B, and C are all integers.
3. Example Data Insertion for Testing:sqlCopy codeINSERT INTO TRIANGLES (A, B, C) VALUES (3, 4, 5), (1, 1, 1), (2, 2, 3), (1, 2, 3);
4. Run the Query:sqlCopy codeSELECT A, B, C, 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 A = C OR B = C THEN 'Isosceles' ELSE 'Scalene' END AS TriangleType FROM TRIANGLES;

This query should correctly identify and classify each triangle based on its side lengths. If you encounter any specific errors, please provide the exact error message so I can offer a more targeted solution.