- 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.
SELECT Name
FROM STUDENTS
WHERE Marks > 75
ORDER BY RIGHT(NAME,3), ID;LEFT / RIGHT 란?
: LEFT는 문자열의 왼쪽부터 일정수치까지의 문자열을 자르는 함수이며, RIGHT는 문자열의 오른쪽부터 일정수치까지의 문자열을 자르는 함수.
LEFT('[문자열]', [길이])
RIGHT('[문자열]', [길이])
- Write a query that prints a list of employee names (i.e.: the name attribute) from the Employee table in alphabetical order.
SELECT name
FROM Employee
ORDER BY 1;147.Write a query that prints a list of employee names (i.e.: the name attribute) for employees in Employee having a salary greater than per month who have been employees for less than months. Sort your result by ascending employee_id.
SELECT name
FROM Employee
WHERE (salary > 2000) and (months < 10)
ORDER BY employee_id;- 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 AND B = C AND A = C THEN 'Equilateral'
WHEN A + B <= C OR B + C <= A OR A + C <= B THEN 'Not A Triangle'
WHEN A = B OR B = C OR A = C THEN 'Isosceles'
WHEN A != B AND B!= C AND A != C THEN 'Scalene'
END TYPE
FROM TRIANGLES;- Generate the following two result sets:
Query an alphabetically ordered list of all names in OCCUPATIONS, immediately followed by the first letter of each profession as a parenthetical (i.e.: enclosed in parentheses). For example: AnActorName(A), ADoctorName(D), AProfessorName(P), and ASingerName(S).
Query the number of ocurrences of each occupation in OCCUPATIONS. Sort the occurrences in ascending order, and output them in the following format:
There are a total of [occupation_count][occupation]s.
where [occupation_count] is the number of occurrences of an occupation in OCCUPATIONS and [occupation] is the lowercase occupation name. If more than one Occupation has the same [occupation_count], they should be ordered alphabetically.
Note: There will be at least two entries in the table for each type of occupation.
SELECT CONCAT(Name, '(',SUBSTR(Occupation, 1,1),')')
FROM OCCUPATIONS
ORDER BY Name;
SELECT CONCAT('There are a total of',' ',COUNT(Name),' ',LOWER(Occupation),'s.' )
FROM OCCUPATIONS
GROUP BY Occupation
ORDER BY 1;- Query a count of the number of cities in CITY having a Population larger than .
SELECT COUNT(NAME)
FROM CITY
WHERE POPULATION > 100000;- Query the total population of all cities in CITY where District is California.
SELECT SUM(POPULATION)
FROM CITY
WHERE DISTRICT = 'California';152.Query the average population of all cities in CITY where District is California.
SELECT AVG(POPULATION)
FROM CITY
WHERE DISTRICT = 'California';- Query the average population for all cities in CITY, rounded down to the nearest integer.
SELECT ROUND(AVG(POPULATION),0)
FROM CITY;154.Query the sum of the populations for all Japanese cities in CITY. The COUNTRYCODE for Japan is JPN.
SELECT SUM(POPULATION)
FROM CITY
WHERE COUNTRYCODE = 'JPN'- Query the difference between the maximum and minimum populations in CITY.
SELECT (MAX(POPULATION)-MIN(POPULATION))
FROM CITY인사이트
148번의 경우 case when구문의 작동순서에 대해 묻는 질문이었다.
SELECT CASE
WHEN A = B AND B = C AND A = C THEN 'Equilateral'
WHEN A + B <= C OR B + C <= A OR A + C <= B THEN 'Not A Triangle'
WHEN A = B OR B = C OR A = C THEN 'Isosceles'
WHEN A != B AND B!= C AND A != C THEN 'Scalene'
END TYPE
FROM TRIANGLES;이 순서가 아니라면 정답이 나오지 않는다. 그 이유는 'Not A Triangle' 와 밑의 조건이 겹칠 수 있기때문에 먼저 삼각형이 될수 없는 경우를 걸러준뒤, 남은것들에서 조건을 추가해줘야한다!
그리고 149번의 경우는 사실, python이라면 훨씬 간단하게 구현할 수 있지 않을까 하는 생각이 들었다.
