I drew an ER diagram for the parkinplace model.
![ER diagram created wit h the dia tool](https://i.stack.imgur.com/HGWJ3.png)
(The dia file can be found here)
A parkingplace can be assigned to at most one employee. There may be parkingplaces that are not assigned to employes (e.g. parkingplaces for guests).
One can store the column/row coordinate of each parking place. This may be expected for this homework problem. One may not do this in practice because these coordinates are not realy useful
when on administrates the parking places. If there are parking places added that are not on this area then column and rows may not make sense. But if one has no column/row attribute a string attribute for specifying the location may be useful. This attribbute could hold the column/row information as string.
So basically the parking place table containts 100 entries, one for each parking place.
create table employee(employee_id number primary key,
first_name varchar2(30),
last_name varchar2(30))
/
create table reservation(name varchar2(30) primary key)
/
create table parkingplace(parkingplace_id number
primary key,
reservation_name varchar2(30)
references reservation(name),
employee_id number
references employee(employee_id),
grid_column number not null,
grid_row number not null,
unique(grid_column,grid_row),
check(grid_column between 1 and 5),
check(grid_row between 1 and 20),
check(employee_id is null
or reservation_name !='guest') )
/
create index idx_reservation_name on parkingplace(reservation_name)
/
create index idx_employee_id on parkingplace(employee_id)
/
The following script fills the tables with testdata
insert into reservation(name) values('employees')
/
insert into reservation(name) values('guests')
/
insert into reservation(name) values('handicap')
/
insert into reservation(name) values('executives')
/
insert into reservation(name) values('contractors')
/
declare
n number;
begin
n:=0;
for r in 1..20 loop
for c in 1..5 loop
n:=n+1;
insert into parkingplace(
parkingplace_id,
reservation_name,
grid_column,
grid_row) values(n,'employees',c,r);
end loop;
end loop;
end;
/
update parkingplace
set reservation_name='handicap'
where grid_column=1 or grid_column=5
/
update parkingplace
set reservation_name='guests'
where grid_row=1
/
insert into employee(employee_id, first_name, last_name)
values(1,'Clark','Kent')
/
update parkingplace
set employee_id=1
where parkingplace_id=6
/
insert into employee(employee_id, first_name, last_name)
values(2,'Loise','Lane')
/
update parkingplace
set employee_id=2
where parkingplace_id=7
/
insert into employee(employee_id, first_name, last_name)
values(3,'Lana','Lang')
/
update parkingplace
set employee_id=3
where parkingplace_id=8
/
insert into employee(employee_id, first_name, last_name)
values(4,'Bruce','Wayne')
/
update parkingplace
set employee_id=4
where parkingplace_id=9
/
If you want to display all 100 parkin places with the employee names if there is an employee assigned then you have to use a LEFT OUTER JOIN
.
select
grid_row,
grid_column,
reservation_name,
first_name,last_name
from
parkingplace left outer join
employee on (parkingplace.employee_id=employee.employee_id);
Because we have stored the geometrical information (row and column number) we can output the row * column matrix with the appropriate query
select grid_row,c1,c2,c3,c4,c5
from (
select
grid_row,
grid_column,
first_name,
last_name
from
parkingplace left outer join
employee on (parkingplace.employee_id=employee.employee_id)
)
pivot (max(first_name||' '||last_name) for grid_column in (
1 as c1,
2 as c2,
3 as c3,
4 as c4,
5 as c5))
order by grid_row;
A demonstration can be found in sqlfiddle
This PIVOT
extension of the select statement is implemented in Oracle since version 11. Prior version of Oracle have to use technics similar to that shown by @kkarns in a comment.
select
grid_row,
max(decode(grid_column,1, first_name||' '||last_name,null)) c1,
max(decode(grid_column,2, first_name||' '||last_name,null)) c2,
max(decode(grid_column,3, first_name||' '||last_name,null)) c3,
max(decode(grid_column,4, first_name||' '||last_name,null)) c4,
max(decode(grid_column,5, first_name||' '||last_name,null)) c5
from
parkingplace left outer join
employee on (parkingplace.employee_id=employee.employee_id)
group by grid_row
order by grid_row
;
Best Answer
It seems to me the specification failed to mention that the ordering should be done using only the hour and minute parts of the date.
The below returns the expected output.
Output: