MS SQL Server SQL和DB2 SQL刷题记录

SELECT CAST(ROUND(SUM(LAT_N), 4) AS DECIMAL(10, 4))
FROM STATION
WHERE LAT_N > 38.7880 AND LAT_N < 137.2345;

SELECT CAST(ROUND(LAT_N, 4) AS DECIMAL(10, 4))
FROM STATION
WHERE LAT_N > 38.7780 
ORDER BY LAT_N ASC
OFFSET 0 ROWS FETCH NEXT 1 ROWS ONLY;

SELECT CAST(ROUND(LONG_W, 4) AS DECIMAL(10, 4))
FROM STATION
WHERE LAT_N < 137.2345
ORDER BY LAT_N DESC
OFFSET 0 ROWS FETCH NEXT 1 ROWS ONLY;

with t1 as(
select 
    min(LAT_N) as a,
    min(LONG_W) as b,
    max(LAT_N) as  c,
    max(LONG_W) as d
from station)


select  CAST(ROUND(abs(a-c)+abs(b-d), 4) AS DECIMAL(10, 4))
from t1

with t1 as(
select 
    min(LAT_N) as a,
    min(LONG_W) as b,
    max(LAT_N) as  c,
    max(LONG_W) as d
from station)


select  CAST(ROUND(sqrt(abs(a-c)*abs(a-c)+abs(b-d)*abs(b-d)), 4) AS DECIMAL(10, 4))
from t1

SELECT  CAST(ROUND(PERCENTILE_CONT(0.5) , 4) AS DECIMAL(10, 4))
WITHIN GROUP (ORDER BY LAT_N) AS median_value
FROM station;

SELECT CAST(ROUND(AVG(x), 4) AS DECIMAL(10, 4))
                   AS Median
FROM (
    SELECT TOP 50 PERCENT LAT_N AS x
    FROM STATION
    ORDER BY LAT_N
) AS y

WITH NumberedRows AS (
  SELECT LAT_N, ROW_NUMBER() OVER (ORDER BY LAT_N) AS RowNum
  FROM STATION
)
SELECT CAST(ROUND(AVG(LAT_N), 4) AS DECIMAL(10, 4)) AS Median
FROM (
  SELECT LAT_N, RowNum, COUNT(*) OVER () AS TotalRows
  FROM NumberedRows
) Subquery
WHERE CASE 
  WHEN TotalRows % 2 = 0 THEN 
    CASE 
      WHEN RowNum = TotalRows / 2 THEN 1
      WHEN RowNum = TotalRows / 2 + 1 THEN 1
      ELSE 0
    END
  ELSE 
    CASE 
      WHEN RowNum = (TotalRows + 1) / 2 THEN 1
      ELSE 0
    END
  END = 1

with up as
    (
        select 
            lat_n,
            row_number() over(order by lat_n desc) as nr_desc,
            row_number() over(order by lat_n) as nr_asc
        from station
    )
select 
    CAST(ROUND(lat_n, 4) AS DECIMAL(10, 4))  as median
from up
where nr_desc = nr_asc

with t1 as(
    select s.name,g.grade,s.marks
    from Students  s
    left join Grades g
    on s.marks>=g.min_mark and s.marks<=g.max_mark
)

select 
(case when grade<=7 then null else name end) as name,
grade,
marks
from t1
order by grade desc,name asc

with t1 as(
select c.company_code,c.founder,
lm.lead_manager_code,
sm.senior_manager_code,
m.manager_code,
e.employee_code
from Company c
left join Lead_Manager lm
on c.company_code=lm.company_code
left join Senior_Manager sm
on c.company_code=sm.company_code and lm.lead_manager_code =sm.lead_manager_code 
left join Manager m
on c.company_code=m.company_code and lm.lead_manager_code =m.lead_manager_code and sm.senior_manager_code=m.senior_manager_code
left join Employee e
on c.company_code=e.company_code and lm.lead_manager_code =e.lead_manager_code and sm.senior_manager_code=e.senior_manager_code and m.manager_code=e.manager_code
)

select company_code,founder,
count(distinct lead_manager_code),
count(distinct senior_manager_code),
count(distinct manager_code),
count(distinct employee_code)
from (select distinct * from t1)t2
group by company_code,founder
order by company_code asc

with t1 as (
select w.*,wp.age,wp.is_evil
from  wands w
left join wands_property wp
on w.code=wp.code
where wp.is_evil=0
)

select id,age,coins_needed,power from
(
select *,row_number() over(partition by age,power order by coins_needed asc)  as rn
from t1
) t2
where rn=1
order by power desc, age desc,id asc

-- Can you name the columns in the table properly???

with t1 as(
select h.hacker_id, h.name,
s.submission_id,s.challenge_id,s.score,
c.difficulty_level,c.hacker_id as creator_id,
d.score as full_score
from hackers h
left join submissions s
on h.hacker_id=s.hacker_id
left join challenges c
on s.challenge_id=c.challenge_id
left join difficulty d
on d.difficulty_level=c.difficulty_level
)
,
t2 as(
select  hacker_id,name,challenge_id,submission_id
from t1
where score=full_score)
,
t3 as (
select hacker_id,name,challenge_id,count(*) as full_score_cnt
from t2
group by hacker_id,name,challenge_id)

select hacker_id,name
from t3
group by hacker_id,name
having count(distinct challenge_id)>=2
order by count(distinct challenge_id) desc, hacker_id asc

---- ms sql server
SELECT CONCAT(NAME, '(', LEFT(OCCUPATION, 1), ')') AS ConcatenatedName
FROM OCCUPATIONS 
UNION
SELECT 'There are a total of ' + CAST(COUNT(*) AS VARCHAR(10)) + ' ' + LOWER(OCCUPATION) + 's.' 
AS OccupationStats
FROM OCCUPATIONS
GROUP BY OCCUPATION
ORDER BY 1 asc


----- DB2
WITH ConcatenatedNames AS (
  SELECT NAME || '(' || SUBSTR(OCCUPATION, 1, 1) || ')' AS ConcatenatedName
  FROM OCCUPATIONS
  ORDER BY ConcatenatedName asc
),
OccupationStats AS (
  SELECT 'There are a total of ' || CAST(COUNT(*) AS VARCHAR(10)) || ' ' || LOWER(OCCUPATION) || 's.' AS OccupationStats
  FROM OCCUPATIONS
  GROUP BY OCCUPATION
  ORDER BY OccupationStats asc
)


SELECT OccupationStats
FROM OccupationStats
UNION ALL
SELECT ConcatenatedName
FROM ConcatenatedNames;

with t1 as (
select h.*,c.challenge_id 
from hackers h
left join challenges c
on h.hacker_id=c.hacker_id
)
,
t2 as(
select hacker_id,name,count(distinct challenge_id) as cnt
from t1
group by hacker_id,name
)


SELECT DISTINCT *
FROM t2
WHERE cnt NOT IN (
  SELECT cnt
  FROM (
    SELECT cnt, DENSE_RANK() OVER (ORDER BY cnt DESC) AS RowNum
    FROM t2
    GROUP BY cnt
    HAVING COUNT(*) > 1
  ) t
  WHERE RowNum > 1
)
order by cnt desc,hacker_id asc