http://mrbool.com/10-tips-about-sql-server-that-every-developer-should-know/4982
Category: SQL
Task 19(ships)
–1. There can be a class which does not contain the head ship therefore
— it is fallacious to use the class name instead of the ship name.
–2. The head ship should be identified even if the year of launching of all the ships of this class is not known,
–BUT at least one of them took part in the sea battle before the year 41.
–3. The head ship the year of launching of which is not known, may be included only into Ships table.
–4. Note that the column [date] has datetime data type.
select name from ships
where launched < 1941
union
select ship from Outcomes where
battle in
(select name from Battles
where date < ‘1941-01-01 00:00:00.000’)
union
select s.class from ships s
inner join
(select ship from Outcomes where
battle in
(select name from Battles
where date < ‘1941-01-01 00:00:00.000’)
union
select name from ships
where launched < 1941
) x on x.ship = s.name
inner join Ships s1 on s1.name = s.class
union
select s.class from ships s
inner join
(select ship from Outcomes where
battle in
(select name from Battles
where date < ‘1941-01-01 00:00:00.000’)
union
select name from ships
where launched < 1941
) x on x.ship = s.name
inner join Outcomes o on o.ship = s.name
Finding the Identity Value that was Inserted
SELECT SCOPE_IDENTITY() as NewRec
Task 18 (inc_out)
select
DENSE_RANK() over (order by startDate ) gr, LTRIM(str(year(startDate)))+case when month(startDate) < 10 then ‘-0’ + ltrim(str(month(startDate))) + ‘-01’ else ltrim(str(month(startDate))) + ‘-01’ end, LTRIM(str(year(startDate)))+case when month(startDate) < 10 then ‘-0’ + ltrim(str(month(startDate))) else ltrim(str(month(startDate))) end +‘-‘+ ltrim(str(day((DateAdd(day, –1, DATEADD(MONTH, 1, startDate)))))),code
, point, dt, amt from(
select DATEADD(day, –DAY(date) + 1, date) startDate, code, point, date dt, inc amt from Income where date <=(
select dateadd( MONTH, –2, MAX(date)) from(
select MAX(date) date from outcome unionselect
MAX(date) from income) maxDate)union
allselect
DATEADD(day, –DAY(date) + 1, date) startDate, code, point, date dt, –out amt from Outcome where date <=(
select dateadd( MONTH, –2, MAX(date)) from(
select MAX(date) date from outcome unionselect
MAX(date) from income) maxDate)) x–Output all records from Outcome and Income tables which dates are not less than 2 calendar months distant
–from the maximum date selected from these both tables
–( ex. when max date is "2009-12-05" last output date must be less or equal "2009-09-30").
–Partition these records and assign a sequenced number to every month (in view of a year) which has been selected.
–Result set: the sequenced number of the month, the first day of the month in "yyyy-mm-dd" format,
–the last day of the month in "yyyy-mm-dd" format, code, point, sum (which should be negative for Outcome table).
–num startDate endDate code point date sum
–1 2001-03-01 2001-03-31 1 1 2001-03-14 00:00:00.000 -15348.0000
–1 2001-03-01 2001-03-31 1 1 2001-03-22 00:00:00.000 15000.0000
–1 2001-03-01 2001-03-31 2 1 2001-03-23 00:00:00.000 15000.0000
–1 2001-03-01 2001-03-31 2 1 2001-03-24 00:00:00.000 -3663.0000
–1 2001-03-01 2001-03-31 3 1 2001-03-24 00:00:00.000 3600.0000
–1 2001-03-01 2001-03-31 3 1 2001-03-26 00:00:00.000 -1221.0000
–1 2001-03-01 2001-03-31 4 1 2001-03-28 00:00:00.000 -2075.0000
–1 2001-03-01 2001-03-31 4 2 2001-03-22 00:00:00.000 10000.0000
–1 2001-03-01 2001-03-31 5 1 2001-03-29 00:00:00.000 -2004.0000
–1 2001-03-01 2001-03-31 5 2 2001-03-24 00:00:00.000 1500.0000
–1 2001-03-01 2001-03-31 8 1 2001-03-22 00:00:00.000 15000.0000
–1 2001-03-01 2001-03-31 9 2 2001-03-24 00:00:00.000 1500.0000
–1 2001-03-01 2001-03-31 10 2 2001-03-22 00:00:00.000 -1440.0000
–1 2001-03-01 2001-03-31 11 1 2001-03-24 00:00:00.000 3400.0000
–1 2001-03-01 2001-03-31 11 2 2001-03-29 00:00:00.000 -7848.0000
–1 2001-03-01 2001-03-31 13 1 2001-03-24 00:00:00.000 -3500.0000
–1 2001-03-01 2001-03-31 14 2 2001-03-22 00:00:00.000 -1440.0000
–1 2001-03-01 2001-03-31 15 1 2001-03-29 00:00:00.000 -2006.0000
–2 2001-04-01 2001-04-30 6 1 2001-04-11 00:00:00.000 -3195.0400
–2 2001-04-01 2001-04-30 6 1 2001-04-13 00:00:00.000 5000.0000
–2 2001-04-01 2001-04-30 7 1 2001-04-13 00:00:00.000 -4490.0000
–2 2001-04-01 2001-04-30 8 1 2001-04-27 00:00:00.000 -3110.0000
–2 2001-04-01 2001-04-30 10 1 2001-04-13 00:00:00.000 5000.0000
–2 2001-04-01 2001-04-30 12 2 2001-04-02 00:00:00.000 -2040.0000
–3 2001-05-01 2001-05-31 7 1 2001-05-11 00:00:00.000 4500.0000
–3 2001-05-01 2001-05-31 9 1 2001-05-11 00:00:00.000 -2530.0000
Task 17(aero)
–Group the all paintings by days, months and years separately.
–Format the identifier of each group as follows: "yyyy" for a year, "yyyy-mm" for a month and "yyyy-mm-dd" for a day.
–Get only those groups that include more than 10 paintings at distinct moments of time (b_datetime).
–Result set: group identifier, the total quantity of a paint used within a group.
–period vol
–2003-01-01 8679
–2003-01 8679
–2003 9070
–One should take into account the period of time, and not the number painting works, as within one time period the square(s) can be painted several times.
–The mistake leads to counting the groups in which there are more than 10 paintings, though the number of various moments of time is less than 10.
select
ltrim(str(year(b_datetime))) + ‘-‘ + case when month(b_datetime) > 9 then ltrim(str(month(b_datetime))) else ‘0’ + ltrim(str(month(b_datetime))) end + ‘-‘ + case when day(b_datetime) > 9 then ltrim(str(day(b_datetime))) else ‘0’ + ltrim(str(day(b_datetime))) end ,sum(b_vol) from utBgroup
by year(b_datetime), month(b_datetime), day(b_datetime)having
COUNT(distinct b_datetime) > 10union
allselect
ltrim(str(year(b_datetime))) + ‘-‘ + case when month(b_datetime) > 9 then ltrim(str(month(b_datetime))) else ‘0’ + ltrim(str(month(b_datetime))) end , sum(b_vol) from utBgroup
by year(b_datetime), month(b_datetime)having
COUNT(distinct b_datetime) > 10union
allselect
ltrim(year(b_datetime)), sum(b_vol) from utBgroup
by year(b_datetime)having
COUNT(distinct b_datetime) > 10Task 16(aero) p2
Select name, cast(x/100.00 as decimal(18, 2)),
CAST(CAST(y * 100.00 / sum(x) over() AS DECIMAL(18, 2)) AS VARCHAR(50)) + ‘%’,
case when cast(y * 100.00/(sum(x) over()) as decimal(18, 2)) between 0 and 80 then ‘A’
when cast(y * 100.00/(sum(x) over()) as decimal(18, 2)) between 80 and 95 then ‘B’
else ‘C’ end
from (
Select a1.name, a1.x, sum(a2.x) y
from
(Select
name, p.ID_psg, SUM(datediff(ss, time_out, case when time_out < time_in then 0 else 1 end + time_in)) x
from Pass_in_trip p, Trip t, passenger pa where p.trip_no = t.trip_no and pa.ID_psg = p.ID_psg
group by name, p.id_psg) a1,
(Select
name, p.ID_psg, SUM(datediff(ss, time_out, case when time_out < time_in then 0 else 1 end + time_in)) x
from Pass_in_trip p, Trip t, passenger pa where p.trip_no = t.trip_no and pa.ID_psg = p.ID_psg
group by name, p.id_psg) a2
where a1.x < a2.x or a1.x = a2.x and (a1.name > a2.name or a1.name = a2.name and a1.ID_psg <= a2.id_psg)
group by a1.name, a1.x, a1.id_psg
) mT
My solution without using WITH is:
select name, income, cast(res as varchar(6))+ ‘%’, case
when res > 0 and res <= 80 then ‘A’
when res >= 80.01 and res <= 95 then ‘B’
else ‘C’
end from
(
select s0.ID, s0.name, s0.income, round( cast(
(
SUM(s1.income)
) * 100 /
(select SUM(income) from
(select p.ID_psg ID, name, sum(case
when datediff(SS, time_out, time_in) > 0 then datediff(SS, time_out, time_in) * 0.01
else datediff(SS, time_out, dateadd( DAY, 1, time_in)) * 0.01
end
) income
from Passenger p
inner join Pass_in_trip pt on pt.ID_psg = p.ID_psg
inner join Trip t on pt.trip_no = t.trip_no
group by p.ID_psg, name
) PersonalData
) as numeric(10, 2)), 2
) res from
(select p.ID_psg ID, name, sum(case
when datediff(SS, time_out, time_in) > 0 then datediff(SS, time_out, time_in) * 0.01
else datediff(SS, time_out, dateadd( DAY, 1, time_in)) * 0.01
end
) income
from Passenger p
inner join Pass_in_trip pt on pt.ID_psg = p.ID_psg
inner join Trip t on pt.trip_no = t.trip_no
group by p.ID_psg, name
) s0
inner join (select p.ID_psg ID, name, sum(case
when datediff(SS, time_out, time_in) > 0 then datediff(SS, time_out, time_in) * 0.01
else datediff(SS, time_out, dateadd( DAY, 1, time_in)) * 0.01
end
) income
from Passenger p
inner join Pass_in_trip pt on pt.ID_psg = p.ID_psg
inner join Trip t on pt.trip_no = t.trip_no
group by p.ID_psg, name
) s1
on (s0.income < s1.income) or (s0.income = s1.income and s0.ID <= s1.ID) or (s0.income = s1.income and s0.ID = s1.ID)
group by s0.name, s0.ID, s0.income
) x;
Task 16 (aero)
–Taking this fact into account, perform ABC analysis of passengers attractiveness (an airline company does not matter).
–ABC analysis is based on Pareto principle – 80% of your sales come from 20% of your clients.
–After the analysis, passengers should be grouped into 3 categories: A, B, and C. The algorithm:
–1. Calculate ratio of each passenger’s profit contribution with running totals (S) to total profit from all passengers (TS).
–Contribution with running totals for each passenger is calculated by summation of his profit with profits from all those
–passengers that give larger or equal profits.
–In the case of equal profits, the smaller running totals will be for the passenger, name of which goes earlier in alphabetic order.
–2. Group the passengers over ABC categories. Category "A" – passengers with S/TS between 0.00% and 80.00% (inclusive).
–Category "B" – S/TS from 80.01% up to 95.00%. Category "C" – S/TS from 95.01% up to 100%.
–Result set: Passenger’s name, sum of profit from this passenger ($), S/TS of the passenger in percent (accuracy within 0.01),
–ABC category of the passenger.
–name dohod nakop_itog ABC
–1Bruce Willis 593.40 71.52% A
–2George Clooney 390.00 94.16% B
–3Kevin Costner 472.80 89.36% B
–4Jennifer Lopez 199.20 99.15% C
–5Ray Liotta 473.40 83.54% B
–6Nikole Kidman 206.40 96.70% C
–7Alan Rickman 69.00 100.00% C
–8Kurt Russell 1078.20 42.06% A
–9Harrison Ford 1080.00 28.80% A
–10Russell Crowe 504.00 77.72% A
–11Steve Martin 864.00 64.21% A
–12Michael Caine 1260.00 15.51% A
–13Mullah Omar 936.00 53.58% A
–Consider calculation of running totals for a situation when namesakes have brought the identical profits.
–As passengers are added to results one by one, having identical profits they will have different percent of running totals.
Using WITH
–http://support.microsoft.com/kb/290136
–http://www.sqlteam.com/article/calculating-running-totals
–http://www.1keydata.com/sql/sql-running-totals.html
WITH
PersonalData(ID,
name, income) AS
(select p.ID_psg, name, sum(case
when
datediff(SS, time_out, time_in) > 0 then datediff(SS, time_out, time_in) * 0.01
else
datediff(SS, time_out, dateadd( DAY, 1, time_in)) * 0.01
end)
from Passenger
p
inner join Pass_in_trip pt on
pt.ID_psg = p.ID_psg
inner join Trip t on pt.trip_no = t.trip_no
group by p.ID_psg, name)
select ID, name, income, cast(res as varchar(6))+ ‘%’,
case
when res > 0 and res <= 80 then ‘A’
when res >= 80.01 and res <= 95 then ‘B’
else ‘C’
end from
(
select s0.ID, s0.name, s0.income, round(cast
(
( SUM(s1.income) * 100 / (select SUM(income) from PersonalData)
) as
float), 2) res from
PersonalData s0
inner join PersonalData s1
on (s0.income < s1.income) or (s0.income = s1.income and s0.ID <= s1.ID) or (s0.income = s1.income and s0.ID = s1.ID)
group by s0.name, s0.ID, s0.income
) x
order by name;
Task 15(computers)
–Dima and Misha use products of the same maker.
–Type of Tanya’s printer is not the same as that of Viktor, but a "color" property is match.
–Screen size of Dima’s laptop is more by 3 inches then Olga’s one.
–Misha’s PC is 4 times more expensive then Tanya’s printer.
–Model numbers of Viktor’s printer and Olga’s laptop differ with third symbol.
–Konstantin’s PC processor speed equals to that of Misha’s PC,
–hard drive equals to that of Dima’s laptop,
–ram equals to that of Olga’s laptop,
–and the price is the same as that of Viktor’s printer.
–List all the possible model numbers of Konstantin’s PC.
–model
–1232
select
distinct model from PCinner
join (select Laptop.hd, x.speed, x.price, x.ram from Laptop inner join (select distinct maker, PC.speed, x.screen, x.price, x.ram from Printer p inner join (select distinct p.price, p.color, p.type, l.screen, l.ram from printer p –Viktor’s printer and Olga’s Laptop cross join Laptop l where substring(p.model, 1, 2) = substring(l.model, 1, 2) and ( substring(p.model, 4, len(p.model)) = substring(l.model, 4, len(l.model) )) and (substring(p.model, 3, 1) <> substring(l.model, 3, 1) and len(l.model) > 2 and len(p.model) > 2) ) x on x.color = p.color and x.type <> p.type –Tanya’s printer inner join PC on PC.price = p.price * 4 inner join Product prod on prod.model = PC.model ) x on Laptop.screen = x.screen + 3 and Laptop.model in (select model from Product where maker = x.maker) ) xon
PC.speed = x.speedand
PC.hd = x.hdand
PC.ram = x.ramand
PC.price = x.priceTask 8(added)
–For each battle find out the first and the last day of the month when the
–battle took place.
–Result set: tne battle name, the first day of month, the last day of month.
–Note: dates must be given in the format "yyyy-mm-dd".
–battle firstD lastD
–Guadalcanal 1942-11-01 1942-11-30
–North Atlantic 1941-05-01 1941-05-31
–North Cape 1943-12-01 1943-12-31
–Surigao Strait 1944-10-01 1944-10-31
–#Cuba62a 1962-10-01 1962-10-31
–#Cuba62b 1962-10-01 1962-10-31
select
name, replace(str(Year(date), 4), ‘ ‘, ‘0’) + ‘-‘ + replace(str(month(date), 2), ‘ ‘, ‘0’) + ‘-01’, replace(str(Year(dateadd( DAY, –1 , (dateadd(month, 1, replace(str(Year(date), 4), ‘ ‘, ‘0’) + ‘-‘ + replace(str(month(date), 2), ‘ ‘, ‘0’) + ‘-01’)))), 4), ‘ ‘, ‘0’) + ‘-‘ + replace(str(month(dateadd( DAY, –1 , (dateadd(month, 1, replace(str(Year(date), 4), ‘ ‘, ‘0’) + ‘-‘ + replace(str(month(date), 2), ‘ ‘, ‘0’) + ‘-01’)))), 2), ‘ ‘, ‘0’) + + ‘-‘ + replace(str(day(dateadd( DAY, –1 , (dateadd(month, 1, replace(str(Year(date), 4), ‘ ‘, ‘0’) + ‘-‘ + replace(str(month(date), 2), ‘ ‘, ‘0’) + ‘-01’)))), 2), ‘ ‘, ‘0’)from
BattlesEx 14
–For Product table, receive result set in the form of a table with columns: maker, pc, laptop, and printer.
–For each maker, this table must include "yes" if a maker has products of corresponding type or "no" otherwise.
–In the first case (yes), specify in brackets (without spaces) the quantity of available distinct models of corresponding type
–(i.e. being in PC, Laptop, and Printer tables).
–maker pc laptop printer
–A yes(2) yes(2) yes(3)
–B yes(1) yes(1) no
–C no yes(1) no
–D no no yes(2)
–E yes(1) no yes(1)
select
maker, case when pc = 0 then ‘yes(0)’ when pc > 0 then ‘yes(‘ + rtrim(ltrim(cast(pc as varchar))) +‘)’ else ‘no’ end , case when laptop = 0 then ‘yes(0)’ when laptop > 0 then ‘yes(‘ + rtrim(ltrim(cast(laptop as varchar))) +‘)’ else ‘no’ end , case when printer = 0 then ‘yes(0)’ when printer > 0 then ‘yes(‘ + rtrim(ltrim(cast(printer as varchar))) +‘)’ else ‘no’ endfrom
(
select distinct maker, ( select case when (select count(model) from product where type=‘pc’ and maker = po.maker) > 0 then (select count(distinct pc.model) from product p1 inner join pc on p1.model = pc.model where maker = po.maker) else NULL end ) pc, ( select case when (select count(model) from product where type=‘laptop’ and maker = po.maker) > 0 then (select count(distinct laptop.model) from product p1 inner join laptop on p1.model = laptop.model where maker = po.maker) else NULL end ) laptop, ( select case when (select count(model) from product where type=‘printer’ and maker = po.maker) > 0 then (select count(distinct printer.model) from product p1 inner join printer on p1.model = printer.model where maker = po.maker) else NULL end ) printerfrom
product po) x