Task 13(inc_out)

–A scrap recycling company is examining status of their outlets.

–Assuming each outlet starts with $10000 dollars find the date which is was profitable,

–in case the outlet is not profitable, write the last date an income was recorded.

–To determine the profit of a company we will look at the Outcome_o only and not look at either incomes tables

–or the the outcome table.

–Result set: Outlet ID (point), profitable date (or last working day), profit (or remaining to be profit).

select point, max([date]), MAX(res) 10000 from

(

select x.point, min([date]) date, min(x.res) res from

(select o1.point

, ISNULL( o1.out +

(select SUM(o2.out) from Outcome_o o2 where o1.point = o2.point and o1.date > o2.date ), o1.out) res

, o1.date

from Outcome_o o1) x

where

x.res >= 10000

group

by x.point

union

select

x.point, max([date]) date, max(x.res) from

(select o1.point

, ISNULL( o1.out +

(select SUM(o2.out) from Outcome_o o2 where o1.point = o2.point and o1.date > o2.date ), o1.out) res

, o1.date

from Outcome_o o1) x

where

x.res < 10000

group

by point

)

data

group

by point

forum:

SELECT

oo.Point, MAX(oo.Date) AS MaxDate, (SUM(oo.Out))-10000 AS AchievedSUM

FROM

Outcome_o AS oo

WHERE

NOT EXISTS

(SELECT o1.Point, SUM(o1.Out)

FROM Outcome_o AS o1

WHERE o1.Date < oo.Date

AND o1.Point = oo.Point

GROUP BY o1.Point

HAVING SUM(o1.Out) > 10000)

GROUP

BY oo.Point

–Разбор решения задачи: =))

–Пока внешний запрос перебирает все записи, внутренний запрос для каждого значения даты ‘oo.Date’,
–переданного во внутренний запрос(и для того же самого пункта приема ‘o1.Point = oo.Point’),
–суммирует все записи о выдаче средств (до даты ‘oo.Date’) и определяет: было ли выполнено условие
–(1)  20.000 – SUM(Out) + 2*SUM(Out) > 30.000, равносильное
—                               SUM(o1.Out) > 10000
–И если это условие было выполнено, то это означает, что на дату ‘oo.Date’, пункт ‘oo.Point’
–Выполнил свою задачу.
–Но в нашем случае, я использую ‘NOT EXISTS’ и условие ‘o1.Date < oo.Date’, т.к. мне важно, чтобы
–во внешнем запросе были отобраны только те записи, для которых условие (1) еще не выполнено.
–Тут нужно обратить внимание на то, что дату я беру "не включительно", это позволяет сделать так,
–что дата выполнения обязательства, наступления события (1) гарантированно попала во внешний запрос,
–Но если событие (1) так и не наступило, то записи попадут все (это важно для неуспешных пунктов).

–Так, теперь, после обработки всех внутренних запросов, но еще до выполнения оператора ‘GROUP BY oo.Point’,
–мы имеем в полученной таблице записи:
–а) Для успешных пунктов – все записи вплоть до даты выполнения обязательства(включительно)
–б) Для неуспешных пунктов – вообще все записи.

–А далее – очевидно, просто агрегируем по пунктам приема: ‘GROUP BY oo.Point’,
–находя при этом максимальную дату (выполнения или невыполнения (1)) и общую вырученную сумму
–к последней дате в полученной таблице минус 10.000.
–Это условие по-сути равносильно (1) и является ответом на вопрос задачи:
— Для пунктов, справившихся с заданием, определить дату его выполнения и сумму денежных средств,
— полученных сверх плана на эту дату;
— Для пунктов, которые не справились с заданием, определить на последнюю отчетную дату
— сумму денежных средств, недостающих до его выполнения.
 

Task 12 (painting)

–Under assumption that there are holes among IDs (q_id) of squares, find the minimal and maximal "free" IDs in a range

–between available maximal and minimal IDs.

–If holes are not present, deduce NULL.

–Example: For the sequence of square IDs 1,2,5,7, the result must be 3 and 6

 

select q_min, q_max from

(

select MIN(ID) q_min from

(

select Q_ID + 1 ID from utQ

WHERE

Q_ID <= (SELECT MAX(Q_ID) from utQ )

except

select

Q_ID from utQ) z) z

,

(select MAX(ID) q_max from

(

select Q_ID 1 ID from utQ

WHERE

Q_ID <= (SELECT MAX(Q_ID) from utQ ) AND Q_ID > (SELECT MIN(Q_ID) from utQ )

except

select

Q_ID from utQ) y) y

 
Forum’s solution:
 
select Min(x), Max(x) from
  (select q_id-1 from utQ where q_id > (select Min(q_id) from utQ)
    union all
   select q_id+1 from utQ where q_id < (select Max(q_id) from utQ)
    except
   select q_id   from utQ) Q(x)

cost 0.02839327044785
operations 19
то же, но через left join:

select Min(x), Max(x) from 
  (select q_id-1 from utQ where q_id > (select Min(q_id) from utQ) 
    union all
   select q_id+1 from utQ where q_id < (select Max(q_id) from utQ)) Q(x)
  left join utQ on x=q_id where q_id is null

cost	0.024536108598113
operations	17

то же, но через not exists:

select Min(x), Max(x) from 
  (select q_id-1 from utQ where q_id > (select Min(q_id) from utQ) 
    union all
   select q_id+1 from utQ where q_id < (select Max(q_id) from utQ)) Q(x)
where not exists (select * from utQ where q_id=x)

cost	0.024512492120266
operations	16

Но еще лучше, конечно же вот так:

select Min(x), Max(x) from 
  (select q_id+a from utQ, (select -1 union all select 1) A(a)
    where q_id > (select Min(q_id) from utQ) 
      and q_id < (select Max(q_id) from utQ)) Q(x)
where not exists (select * from utQ where q_id=x)

cost	0.015696810558438
operations	14

Task 11 (computer)

–Find all the makers who have all their models of PC type in the PC table.

select maker from Product p

left

outer join PC pc on pc.model = p.model

where type=‘PC’

group

by maker

having

count(p.model) = count(pc.model)

Task 10 (aero)

–Find the passengers which have spent the most time during flyings than others.
–Result set: passenger name, total flying time in minutes.

select top 1 with ties p.name, sum(case
                            when DATEDIFF(minute, time_out, time_in) >= 0 THEN DATEDIFF(minute, time_out, time_in)
                            else 1440 – (DatePart(hour, time_out) * 60 + DatePart(minute, time_out)) + (DatePart(hour, time_in) * 60 + DatePart(minute, time_in))
                            end) time from trip t
                            inner join Pass_in_trip pt on pt.trip_no = t.trip_no
                            inner join Passenger p on pt.ID_psg = p.ID_psg
                            group by p.ID_psg, p.name
                            order by time desc

Task 9 (aero)

–Among the clients which only use a single company, find the different passengers who have flown more often than others.

–Result set: passenger name, number of trips, and company name.

select p.name, cnt, c.name from

(

select pt.ID_psg, t.ID_comp, COUNT(ID_comp) cnt from Trip t

inner join Pass_in_trip pt on pt.trip_no = t.trip_no

where pt.ID_psg in

(select ID_psg from

(select distinct pt.ID_psg, t.ID_comp from Trip t

inner join Pass_in_trip pt on pt.trip_no = t.trip_no) x

group by ID_psg

having COUNT(ID_comp) = 1)

group by pt.ID_psg, t.ID_comp) x

inner

join Company c on c.ID_comp = x.ID_comp

inner

join Passenger p on p.ID_psg = x.ID_psg

where

cnt =

(

select max(cnt) from

(select pt.ID_psg, t.ID_comp, COUNT(ID_comp) cnt from Trip t

inner join Pass_in_trip pt on pt.trip_no = t.trip_no

where pt.ID_psg in

(select ID_psg from

(select distinct pt.ID_psg, t.ID_comp from Trip t

inner join Pass_in_trip pt on pt.trip_no = t.trip_no) x

group by ID_psg

having COUNT(ID_comp) = 1)

group by pt.ID_psg, t.ID_comp) x )

 
from forum:
 

select

p.name, cnt, c.name

from

(

select

top 1 with ties pt.id_psg, count(*) as cnt, min( id_comp ) as id_comp

from pass_in_trip pt

inner join trip t on t.trip_no = pt.trip_no

group by pt.id_psg

having count( distinct id_comp ) = 1

order by count(*) desc

)

t

inner

join passenger p on t.id_psg = p.ID_psg

inner

join Company c on c.ID_comp = t.ID_comp

Task 7 (aero)

–For each company, find the number of passengers (if any) that have been transported in April 2003 by ten-day periods.
 

select c.name, ISNULL(first, 0) first, ISNULL(second, 0) second, ISNULL(third, 0) third from Company c

left

join

(

select name, COUNT(pt.ID_psg) first from Trip t

inner

join Pass_in_trip pt on pt.trip_no = t.trip_no

inner

join Company c on c.ID_comp = t.ID_comp

where

pt.date between ‘2003-04-01 00:00:00.000’ AND ‘2003-04-10 00:00:00.000’

group

by name

)

x on x.name = c.name

left

join

(

select name, COUNT(pt.ID_psg) second from Trip t

inner

join Pass_in_trip pt on pt.trip_no = t.trip_no

inner

join Company c on c.ID_comp = t.ID_comp

where

pt.date between ‘2003-04-11 00:00:00.000’ AND ‘2003-04-20 00:00:00.000’

group

by name) y on y.name = c.name

left

join

(

select name, COUNT(pt.ID_psg) third from Trip t

inner

join Pass_in_trip pt on pt.trip_no = t.trip_no

inner

join Company c on c.ID_comp = t.ID_comp

where

pt.date between ‘2003-04-21 00:00:00.000’ AND ‘2003-04-30 00:00:00.000’

group

by name) z on z.name = c.name

where

NOT (first IS NULL and second IS NULL and third IS NULL)

order

by c.name

Task 6(ships)

–For each country, determine battles in which the ships of the given country did not participate.

–Result set: country, battle

select y.country, name from

(

select distinct country from Classes) y

CROSS

join

(

select name from Battles) z

where

name not in

(

select battle from

(select battle, country from Classes c

inner join ships s on s.class = c.class

inner join Outcomes o on s.name = o.ship

union

select battle, country from Classes c

inner join Outcomes o on o.ship = c.class) u

where u.country = y.country

)

order

by y.country

Task 5(ships)

For each ship from Ships table, determine the name of the time-nearest battle from Battles table, in which the ship could participate after year of launch .
If year of launch is unknown, take the latest battle. If there is no battle which have occured after the ship was launched, deduce NULL instead of the name of battle.
Remark: Consider that the ship can participate in battle which has occured in the year of launch of the ship.
Result set: name of the ship, year of launch, name of battle 

select

s.name, s.launched, (select

CASE

WHEN (cast(’01/01/’ + cast(s.launched as char(4)) as datetime) > (SELECT TOP 1 date

from Battles

order by date desc)) THEN NULL

WHEN s.launched IS NULL THEN (SELECT TOP 1 Battles.name from Battles order by date desc)

ELSE (SELECT TOP 1 Battles.name

from Battles

where date >= cast(’01/01/’ + cast(s.launched as char(4)) as datetime)

order by date)

END battle

)

battle from ships s

order

by s.name

Task 4 (computer)

–The database scheme consists of four tables:

–Product(maker, model, type)

–PC(code, model, speed, ram, hd, cd, price)

–Laptop(code, model, speed, ram, hd, screen, price)

–Printer(code, model, color, type, price)

–The table "Product" includes information about the maker, model number, and type (‘PC’, ‘Laptop’, or ‘Printer’).

–It is assumed that model numbers in the Product table are unique for all the makers and product types.

–Each PC uniquely specifying by a code in the table "PC" is characterized

–by model (foreign key referencing to Product table), speed (of the processor in MHz), total amount of RAM – ram (in Mb),

–hard disk drive capacity – hd (in Gb), CD ROM speed – cd (for example, ‘4x’), and the price.

–The table "Laptop" is similar to that one of PCs except for the CD ROM speed,

–which is replaced by the screen size – screen (in inches). For each printer in the table "Printer" it is told whether

–the printer is color or not (color attribute is ‘y’ for color printers; otherwise it is ‘n’),

–printer type (laser, jet, or matrix), and the price.

–Calculate the sum of digits in each model’s ID (model column) from Product table.

–Result set: model, sum of digits

–It should take into account that model column may contain not only digits, but also non-digit symbols according to the data type of the column – varchar (50).

–If a model does not contain digits at all , deduce 0.

select model,

(

LEN(REPLACE(model, ‘1’,’11’)) LEN(model)) +

2

*(LEN(REPLACE(model, ‘2’,’22’)) LEN(model)) +

3

*(LEN(REPLACE(model, ‘3’,’33’)) LEN(model)) +

4

*(LEN(REPLACE(model, ‘4’,’44’)) LEN(model)) +

5

*(LEN(REPLACE(model, ‘5’,’55’)) LEN(model)) +

6

*(LEN(REPLACE(model, ‘6’,’66’)) LEN(model)) +

7

*(LEN(REPLACE(model, ‘7’,’77’)) LEN(model)) +

8

*(LEN(REPLACE(model, ‘8’,’88’)) LEN(model)) +

9

*(LEN(REPLACE(model, ‘9’,’99’)) LEN(model))

from

Product

Task 3

–The firm has a few outlets that receive items for recycling. Each of the outlets receives funds to be paid to deliverers.

–Information on received funds is registered in a table:

–Income_o(point, date, inc)

–The primary key is (point, date), thus receiption of money (inc) takes place not more than once a day

–(date column does not include time component of the date). Information on payments to deliverers is registered in the table:

–Outcome_o(point, date, out)

–In this table the primary key (point, date) also ensures bookkeeping of the funds distribution at each point not more than

— once a day.

–In case incomes and expenses may occur more than once a day, another database schema is used. Corresponding tables include

–code column as primary key:

–Income(code, point, date, inc)

–Outcome(code, point, date, out)

–In this schema date column does not also include the day time.

–Find inlets with greater total payout in different pairs of inlets with equal inlet

–No’s from outcome and outcome_o tables considering only those dates when waste was collected at least at one of the inlets involved.

–Result set: Inlet #, date, text: – "once a day", if daily reporting inlet is in the lead; – "more than once a day",

–if several times a day reporting inlet is in the lead; – "both", for draw cases.

select COALESCE (x.point, y.point) point,

COALESCE (x.date, y.date) date,

lider

= case

when (x.out IS NULL) or (x.out < y.out) then ‘once a day’

when (y.out IS NULL) or (x.out > y.out) then ‘more than once a day’

else ‘both’

end

from

(

select point, date, SUM(out) out from Outcome

group

by point, date) x

full

join

(

select point, date, out from Outcome_o) y on x.point = y.point and x.date = y.date

inner

join (select distinct o1.point from Outcome o1, Outcome_o o2

where

o1.point = o2.point) z on z.point = x.point or z.point = y.point