Splitting the results into into 2 different aliases

1


using this I need to take from the henkilo table the enimi, snimi and palkka. I then need to split the palkka according to >2800 and name it suurip or <=2800 as pienip. Im not sure how to split the palkka to go into the 2 different aliases. Also this was a practice question under UNIONs but does it need a union since its from the same table?

Another under the same UNION practice questions one asked for the, ptun, pnimi and sijainti but where the sijainti was null to have it say ei ole. I wrote this
SELECT ptun, pnimi, IIF(IsNull(sijainti),"ei ole", sijainti) AS kunta
FROM projekti
I had to change it to kunta as it wouldnt let me put sijainti, is there a different way to do this?

2

can you please post sample data of enimi, snimi and palkka? as follows
create table #enimi

then

insert into #enimi
select

cant see in detail your attached picture.

3

CREATE TABLE osasto
(ostun SMALLINT PRIMARY KEY,
osnimi VARCHAR(15),
koodi VARCHAR(30))

INSERT INTO OSASTO VALUES( 1,'Tietohallinto','A421')
INSERT INTO OSASTO VALUES(2,'Taloushallinto','A444')
INSERT INTO OSASTO VALUES(3,'Tuotanto','B112')
INSERT INTO OSASTO VALUES(4,'Markkinointi','A550')

CREATE TABLE henkilo (
htun CHAR (4) PRIMARY KEY,
enimi VARCHAR (10),
snimi VARCHAR (10),
kunta VARCHAR (10),
tutkinto CHAR (8),
palkka DECIMAL(7, 2),
veropros DECIMAL(3,1),
pvm DATETIME,
ostun SMALLINT,
CONSTRAINT henk_os_fk FOREIGN KEY (ostun)
REFERENCES osasto(ostun)
ON DELETE NO ACTION);

insert into henkilo ( htun,enimi,snimi,kunta,tutkinto,palkka,veropros,pvm,ostun ) values ('2134','Keijo','Perälä','TURKU','Yo',2800.00,22.0,'2004-03-02',3)
insert into henkilo ( htun,enimi,snimi,kunta,tutkinto,palkka,veropros,pvm,ostun ) values ('2234','Minna','Metsä','TURKU','HuK',3100.00,33.0,'1993-10-15',1)
insert into henkilo ( htun,enimi,snimi,kunta,tutkinto,palkka,veropros,pvm,ostun ) values ('2245','Raija','Joki','HELSINKI','FK',3100.00,31.0,'1998-09-24',4)
insert into henkilo ( htun,enimi,snimi,kunta,tutkinto,palkka,veropros,pvm,ostun ) values ('2345','Lea','Meri','TURKU',NULL,2800.00,24.5,'2002-01-01',3)
insert into henkilo ( htun,enimi,snimi,kunta,tutkinto,palkka,veropros,pvm,ostun ) values ('2884','Jukka','Järvilehto','HELSINKI','FK',2960.00,31.0,'1993-05-12',NULL)
insert into henkilo ( htun,enimi,snimi,kunta,tutkinto,palkka,veropros,pvm,ostun ) values ('3546','Laura','Ranta-aho','TAMPERE','Yo',2650.00,22.0,'2001-09-15',1)
insert into henkilo ( htun,enimi,snimi,kunta,tutkinto,palkka,veropros,pvm,ostun ) values ('3547','Lauri','Jokinen','TAMPERE','DI',2800.00,37.0,'1993-05-12',3)
insert into henkilo ( htun,enimi,snimi,kunta,tutkinto,palkka,veropros,pvm,ostun ) values ('3641','Aimo','Hoponen','OULU','FK',3600.00,36.0,'2010-11-05',2)
insert into henkilo ( htun,enimi,snimi,kunta,tutkinto,palkka,veropros,pvm,ostun ) values ('3755','Alli','Latvanen','LAHTI','DI',3500.00,32.0,'2012-06-18',3)
insert into henkilo ( htun,enimi,snimi,kunta,tutkinto,palkka,veropros,pvm,ostun ) values ('3766','Leila','Joki','LAHTI','',2200.00,23.0,'2013-09-04',1)

CREATE TABLE projekti(
ptun CHAR(4) PRIMARY KEY,
pnimi VARCHAR(15),
priorit SMALLINT,
sijainti VARCHAR(15));

INSERT INTO projekti VALUES ('P1','MEDIA',2,'TURKU');
INSERT INTO projekti VALUES('P2','ASIAKKUUS',1,'HELSINKI');
INSERT INTO projekti VALUES ('P3','VARASTO',3,'HELSINKI');
INSERT INTO projekti VALUES ('P4','TUOTTEET',2,'TURKU');
INSERT INTO projekti VALUES ('P5','JOULU',3,'KUOPIO');
INSERT INTO projekti VALUES ('P6','TILASTO',NULL,NULL);
INSERT INTO projekti VALUES ('P7','RAPORTIT', NULL, NULL);

CREATE TABLE proj_henk
(ptun CHAR (4) NOT NULL,
htun CHAR (4) NOT NULL,
tunnit SMALLINT,
tunnit_suun SMALLINT,
CONSTRAINT prhe_pk PRIMARY KEY (ptun, htun),
CONSTRAINT prhe_henk FOREIGN KEY (htun)
REFERENCES henkilo (htun)
ON UPDATE CASCADE,
CONSTRAINT prhe_proj FOREIGN KEY (ptun)
REFERENCES projekti (ptun)
ON DELETE CASCADE);

INSERT INTO proj_henk VALUES ('P1','2134',300,300);
INSERT INTO proj_henk VALUES ('P1','2245',200,300);
INSERT INTO proj_henk VALUES ('P1','3546',400,500);
INSERT INTO proj_henk VALUES ('P1','2884',100,200);
INSERT INTO proj_henk VALUES ('P1','2234',200,NULL);
INSERT INTO proj_henk VALUES ('P1','2345',100,100);
INSERT INTO proj_henk VALUES ('P1','3547',300,200);
INSERT INTO proj_henk VALUES ('P2','2134',300,NULL);
INSERT INTO proj_henk VALUES ('P2','2245',400,500);
INSERT INTO proj_henk VALUES ('P3','2245',200,100);
INSERT INTO proj_henk VALUES ('P4','2245',200,200);
INSERT INTO proj_henk VALUES ('P4','2234',300,400);
INSERT INTO proj_henk VALUES ('P4','2884',400,600);
INSERT INTO proj_henk VALUES ('P6','3641',200,400);
INSERT INTO proj_henk VALUES ('P6','3755',100,300);

this is the code for it we are working off

4
  1. surrip and pienip (not sure what the heck that means)

CREATE TABLE suurip (
enimi VARCHAR (10),
snimi VARCHAR (10),
palkka DECIMAL(7, 2)
)
insert into suurip
select enimi, snimi, palkka from henkilo where palkka> 2800

CREATE TABLE pienip (
enimi VARCHAR (10),
snimi VARCHAR (10),
palkka DECIMAL(7, 2)
)
insert into pienip
select enimi, snimi, palkka from henkilo where palkka<= 2800

5

Since you're supossed to use "union" the answer to the first question could be:

select enimi
      ,snimi
      ,palkka as suurip
      ,null as pienip
  from henkilo
 where palkka<=2800
union all
select enimi
      ,snimi
      ,null as suurip
      ,palkka as pienip
  from henkilo
 where palkka<=2800
;

You could do the same without the "union" like this:

select enimi
      ,snimi
      ,case when palkka<=2800 then palkka else null end as suurip
      ,case when palkka>2800 then palkka else null end as pienip
  from henkilo
;

For the second question, you could do:

select ptun
      ,pnimi
      ,isnull(sijainti,'ei ole') as sijainti
  from projekti
;
1 Like
6

Maybe just join to the table twice or run some embedded syntax..

select
id,
a.[palkka] [>2800 value],b.[palkka] [=<2800 value]
from
(
select id, palkka from henkilo a (nolock) where [whatver]>2800
)a
join
(
select id, palkka from henkilo a (nolock) where [whatver]=<2800
) b on a.id =b.id

7

+1 for the case, nice solution!!!

8

Thank you, I never thought about using the null to do it. :smiley: