We define an employee's total earnings to be their monthly worked, and the maximum total earnings to be the maximum total earnings for any employee in the Employee table. Write a query to find the maximum total earnings for all employees as well as the total number of employees who have maximum total earnings. Then print these values as space-separated integers.
Select max(months*salary), count(*)
From employee
where (months*salary)=(
Select max(months*salary)
From employee
)
/
Manhattan distance（曼哈顿距离）
Definition: The distance between two points measured along axes at right angles. In a plane with p1 at (x1, y1) and p2 at (x2, y2), it is |x1 - x2| + |y1 - y2|.
Select Round( abs( MIN(lat_n)-MAX(lat_n) ) + abs( min(long_w)-max(long_w) ), 4)
FROM station;
/
Euclidean distance 欧几里得距离
Select ROUND( SQRT( POWER( MAX(LAT_N)-MIN(LAT_N),2 )+POWER( MAX(LONG_W)-MIN(LONG_W),2 ) ), 4 )
From Station
/
Median Value(统计学中间值)
A median is defined as a number separating the higher half of a data set from the lower half. Query the median of the Northern Latitudes (LAT_N) from STATION and round your answer to decimal places.
Input Format
The STATION table is described as follows:
Select round(S.LAT_N,4) median
From Station s
where ( /* the number of the lower half*/
Select count(Lat_N)
From station
where Lat_N < s.LAT_N ) = ( /* the number of the higher half */
Select count(Lat_N)
From station
Where Lat_N > s.LAT_N
)
/
Multiple tables link
Select c.company_code, c.founder,
count(distinct l.lead_manager_code),
count(distinct s.senior_manager_code),
count(distinct m. manager_code),
count(distinct e.employee_code)
From Company c, Lead_Manager l, Senior_Manager s, Manager m, employee e
Where c.company_code = l.company_code
and l.lead_manager_code = s.lead_manager_code
and s.senior_manager_code = m.senior_manager_code
and m.manager_code = e.manager_code
group by c.company_code, c.founder
Order by c.company_code
/
Regexpression(正则表达式）
Query the list of CITY names from STATION which have vowels (i.e., a, e, i, o, and u) as both their first and last characters. Your result cannot contain duplicates
SELECT DISTINCT CITY
FROM STATION
WHERE city RLIKE '^[aeiouAEIOU].*[aeiouAEIOU]$';
.(dot)表示除\n之外的任意字符。
*表示匹配0-无穷次数。
RLIKE: USING RegExpression
/
Query the list of CITY names from STATION that do not end with vowels. Your result cannot contain duplicates.
SELECT DISTINCT CITY
FROM STATION
WHERE CITY NOT RLIKE'^.*[AEIOUaeiou]$';
/
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 RLIKE '^[^aeiouAEIOU] | [^aeiouAEIOU]$';
/
Query the list of CITY names from STATION that do not start with vowels and do not end with vowels. Your result cannot contain duplicates.
SELECT DISTINCT CITY
FROM STATION
WHERE CITY regexp '^[^aeiouAEIOU].*[^aeiouAEIOU]$';
/
Distinct
Query the list of CITY names starting with vowels (i.e., a, e, i, o, or u) from STATION. Your result cannot contain duplicates.
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%'
*******************************************************************************
Let be the number of CITY entries in STATION, and let be the number of distinct CITY names in STATION; query the value of from STATION. In other words, find the difference between the total number of CITY entries in the table and the number of distinct CITY entries in the table.
Select count(CITY) - count(distinct(CITY))
From STATION;
*******************************************************************************
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.
/*more than one the shortest city name, choose the one that comes first when ordered alphabetically.*/
(
Select CITY, CHAR_LENGTH(CITY)
FROM STATION
WHERE CHAR_LENGTH(CITY)=(
Select MIN( CHAR_LENGTH(CITY) )
FROM STATION
Order by CITY ASC
)
Order by CITY LIMIT 1
)
UNION ALL
(
/*more than one the Longest city name, choose the one that comes first when ordered alphabetically.*/
Select CITY, CHAR_LENGTH(CITY)
FROM STATION
WHERE CHAR_LENGTH(CITY)=(
Select MAX( CHAR_LENGTH(CITY) )
FROM STATION
Order by CITY ASC
)
Order by CITY LIMIT 1
);
RIGHT(Name, 3) - last three characters of Name
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) asc, ID asc
Case...When...Then...Else... End
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 AND A+C>B AND B+C>A THEN
CASE
WHEN A=B AND A=C AND B=C THEN 'Equilateral'
WHEN A<>B AND A<>C AND B<>C THEN 'Scalene'
ELSE 'Isosceles'
END
ELSE
'Not A Triangle'
END
From TRIANGLES;
Concat(,,,...)
substr(queryName,start_position，Number）
Lower
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).
-
Priya(D)
Priyanka(P)
Samantha(A)
- 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.
/* 1 */
SELECT concat( name, '(', substr(occupation,1,1), ')' )
FROM OCCUPATIONS
order by name asc;
/* 3 */
SELECT concat( 'There are a total of ', count(*), ' ', lower(occupation), 's.' )
From OCCUPATIONS
group by occupation
order by count(*), Occupation asc;
output:
……..
Priya(D)
Priyanka(P)
Samantha(A)
There are a total of 3 doctors.
There are a total of 4 actors. {-truncated-}
Binary Tree
/* Enter your query here. */
select N,(
CASE
WHEN P IS NULL THEN 'Root' /* when OB.P IS NULL then he is a Root*/
WHEN EXISTS(
SELECT P
FROM BST IB
WHERE IB.P=OB.N /*If the parent node is also a child node*/
)THEN 'Inner'
ELSE 'Leaf'
END
)
from BST OB
Order by N;
OUTPUT:
1 Leaf
2 Inner
3 Leaf
4 Inner
5 Leaf
6 Inner
7 Leaf
8 Leaf
9 Inner
10 Leaf
11 Inner
12 Leaf
13 Inner
14 Leaf
15 Root
Query the average population for all cities in CITY, rounded down to the nearest integer.
Select round(avg(POPULATION), 0)
From CITY;
************************************
select SUM(POPULATION)
From CITY
WHERE COUNTRYCODE='JPN';
************************************
Select max(POPULATION)-min(POPULATION)
From CITY;
************************************
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 zeroes 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.
Ceil--round up
SELECT CEIL( AVG(Salary)-AVG( REPLACE(Salary,'0','') ) )
FROM EMPLOYEES;
/*
Enter your query here.
*/
We define an employee's total earnings to be their monthly worked, and the maximum total earnings to be the maximum total earnings for any employee in the Employee table. Write a query to find the maximum total earnings for all employees as well as the total number of employees who have maximum total earnings. Then print these values as space-separated integers.
Select max(months*salary), count(*)
From employee
where (months*salary)=(
Select max(months*salary)
From employee
)
扫一扫
427
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
