Category: SQL

Task 19(ships)

–Define the names of all the ships in the database which were definitely launched before 1941.

–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

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

union

select

MAX(date) from income) maxDate)

union

all

select

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

union

select

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 utB

group

by year(b_datetime), month(b_datetime), day(b_datetime)

having

COUNT(distinct b_datetime) > 10

union

all

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 , sum(b_vol) from utB

group

by year(b_datetime), month(b_datetime)

having

COUNT(distinct b_datetime) > 10

union

all

select

ltrim(year(b_datetime)), sum(b_vol) from utB

group

by year(b_datetime)

having

COUNT(distinct b_datetime) > 10

Task 16(aero) p2

Solution from forum:

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)

–One second of a flight of each passenger gives 1 cent ($0.01) profit to airline company.

–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 PC

inner

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)

) x

on

PC.speed = x.speed

and

PC.hd = x.hd

and

PC.ram = x.ram

and

PC.price = x.price

Task 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

Battles

Ex 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’

end

from

(

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

) printer

from

product po) x