04pallav
9/13/2017 - 9:53 PM
65
65
########### MS SQL
########### not MySQL
41. UNPIVOT
select chr,value
from (select cast(model as varchar(25)) model,
cast(speed as varchar(25)) speed,
cast(ram as varchar(25)) ram,
cast(hd as varchar(25)) hd,
cast(cd as varchar(25)) cd,
cast(price as varchar(25)) price from pc where code=(select max(code) from pc)) A
UNPIVOT
( value for chr in (model, speed, ram, hd, cd, price)) B
46. For each ship that participated in the Battle of Guadalcanal, get its name, displacement, and the number of guns.
select outcomes.ship, classes.displacement, classes.numguns from classes join outcomes on classes.class=outcomes.ship and outcomes.battle='Guadalcanal'
union
select outcomes.ship, classes.displacement, classes.numguns from (ships join classes on ships.class=classes.class) right join outcomes on
outcomes.ship=ships.name where outcomes.battle='Guadalcanal' and outcomes.ship not in (select outcomes.ship from classes join outcomes on classes.class=outcomes.ship and outcomes.battle='Guadalcanal')
47.
with A as (select count(model) count, maker from product group by maker )
select (row_number() over (order by count desc, A.maker asc, model asc)) num1, A.maker, model from A,product where product.maker=A.maker
select class from (select classes.class class, ship name, battle, result from classes,outcomes where classes.class=outcomes.ship
union
select ships.class class, ships.name name, battle, result from ships,outcomes where outcomes.ship=ships.name) A where result ='sunk' group by class having count(name)>=1
48.
select class from (select classes.class class, ship name, battle, result from classes,outcomes where classes.class=outcomes.ship
union
select ships.class class, ships.name name, battle, result from ships,outcomes where outcomes.ship=ships.name) A where result ='sunk' group by class having count(name)>=1
49.
select outcomes.ship from outcomes,classes where outcomes.ship=classes.class and bore=16
union
select ships.name from ships,classes where ships.class=classes.class and bore=16
51.
Find the names of the ships with the largest number of guns among all ships having the same displacement (including ships in the Outcomes table).
with A as (select ships.name name, numGuns, displacement from ships, classes where ships.class=classes.class
union
select outcomes.ship name, numGuns, displacement from outcomes, classes where outcomes.ship=classes.class)
select distinct A.name from A,(select displacement, max(numguns) max from A group by displacement) B where A.displacement=B.displacement and A.numguns=B.max
Hint
##after getting aggregate stats I merge aggregate table again with original table.
53.
select cast(avg(numguns* 1.0) as numeric(6,2)) avg from classes where type='bb'
##tinyint was converted to numeric ### see
54.
### remember if you don't select names of ships then union will remove duplicate rows
select cast(avg(numguns*1.0) as numeric(6,2)) avg from (select outcomes.ship name,type,numguns from outcomes, classes where outcomes.ship=classes.class and type='bb'
union
select ships.name name ,type,numguns from ships, classes where ships.class=classes.class and type='bb'
) A
55.
For each class, determine the year the first ship of this class was launched.
If the lead ship’s year of launch is not known, get the minimum year of launch for the ships of this class.
Result set: class, year.
Select classes.class, min(launched) from classes full join ships on classes.class=ships.class group by classes.class
56.
For each product type and maker in the Product table, find out, with a precision of two decimal places, the percentage ratio of the number of models of the actual type produced by the actual maker to the total number of models by this maker.
Result set: maker, product type, the percentage ratio mentioned above.
with C as (select maker, type, count(model) count from product group by maker, type), M as (select * from (select maker maker, count(model) total from product group by maker) A ,(select distinct type type from product) B )
select M.maker,M.type, isnull(cast( C.count*100.0/total as numeric(6,2)),0.00) prc from M left join C on (M.maker=C.maker and M.type=C.type)
### notice how empty levels are filled
#### how two table are saved
#### isnull works nullif doesnt
66.
SELECT NUM,CASE WHEN RW>1 THEN '' ELSE MAKER END MAKER,TYPE
FROM (SELECT ROW_NUMBER() OVER (ORDER BY MAKER ASC,
(CASE WHEN TYPE='PC' THEN 1 WHEN TYPE='LAPTOP' THEN 2
WHEN TYPE='PRINTER' THEN 3 ELSE 100 END) ASC ) NUM ,
ROW_NUMBER() OVER (PARTITION BY MAKER ORDER BY MAKER) RW, MAKER,TYPE
FROM (SELECT DISTINCT MAKER,TYPE FROM PRODUCT) X) A
# use of two row numbering
#first one is for display and second one is for checking if the maker appeared
#in the last row
# when you do partition by then each maker group will have its own numbering
and the first value will be 1
59.
Calculate the cash balance of each buy-back center for the database with money transactions being recorded not more than once a day.
Result set: point, balance.
select A.point, coalesce(A.sumi,0) - coalesce(B.sumo,0) from
(select point, sum( Income_o.inc) sumi from Income_o group by Income_o.point)
A full join (select point, sum( Outcome_o.out) sumo from Outcome_o
group by Outcome_o.point) B on A.point=B.point
### summing up incomes on each point individually and then join, not the other way round
### full join and not regular join because income or outcome for a particular point can be Null
### handling null using coalesce function or using case function
61.
## see how I subtracted two values from each other by using cross join
## coalesce is important because of NULL values
select coalesce(A.sumi,0)-coalesce(B.sumo,0) from (select sum(income_o.inc) sumi from income_o) A , (select sum(outcome_o.out) sumo from outcome_o) B
64.
Using the Income and Outcome tables, determine for each buy-back center the days when it received funds but made no payments, and vice versa.
Result set: point, date, type of operation (inc/out), sum of money per day.
with A as ( select sum(Income.inc) sumi, point ipoint, date idate from income group by point, date), B as ( select sum(outcome.out) sumo, point opoint, date odate from outcome group by point, date) ,C as (select ipoint,opoint,idate,odate,sumi,sumo from A full join B on (A.ipoint=B.opoint and A.idate=B.odate))
select coalesce(ipoint, opoint) point, coalesce(idate, odate) date, case when (sumi is null) then 'out' when (sumo is null) then 'inc' end as type, case when (sumi is null) then sumo when (sumo is null) then sumi end as sum from C where (idate is null or odate is null)
### see how A, B are used in the same with clause to make C.
### see why i used full join
### see join on two columns
### see how coalesce is used to choose between two null values
### see use of case statement
66.
For all days between 2003-04-01 and 2003-04-07 find the number of trips from Rostov.
Result set: date, number of trips.
with A as (select date, count(distinct A.trip_no) trip_count from trip A join Pass_in_trip B on A.trip_no=B.trip_no where town_from='Rostov' and date between '2003-04-01' and '2003-04-07'
group by date), B as (select cast('2003-04-01' as datetime) as date union select '2003-04-02' as date union select '2003-04-03' as date union select '2003-04-04' as date union select '2003-04-05' as date union select '2003-04-06' as date union select '2003-04-07' as date)
select B.date, isnull (trip_count,0) trip_count1 from A full join B on A.date=B.date
63.
with A as (select distinct Passenger. ID_psg ID from pass_in_trip join Passenger on pass_in_trip. ID_psg=Passenger.ID_psg
group by Passenger.ID_psg, pass_in_trip.place having count(trip_no)>1)
select Passenger.name from Passenger,A where Passenger.ID_psg=A.ID
### grouping by passenger names will not work because names are not unique
67.
####
with A as (select concat(town_from, town_to) route, count(trip_no) trip_count from trip group by town_from, town_to)
select count(route) from A where trip_count= (select max(trip_count) from A)
##using results of a group by for answer