Random Assignment of Employees to Locations Oracle SQL

I drew an ER diagram for the parkinplace model.

(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
;

When it comes to software downloads for production use, the Oracle sites, thankfully, are a little less of a maze - provided that you are familiar with the entrance.

You can find the latest media packs for production use on the Oracle Software Delivery Cloud.

After logging in and accepting terms and export restrictions, select Oracle Database and Linux x86-64. After hitting Go, you should be able to find Oracle Database 11g Release 2 (11.2.0) Media Pack for Linux x86-64 and within; the Oracle Database 11g Release 2 Client (11.2.0.1.0) for Linux x86-64.

You can find Oracle Database 12c Release 1 (12.1.0.2.0) Media Pack for Linux x86-64 and the associated Oracle Database 12c Release 1 Client (12.1.0.2.0) for Linux x86-64 (64-bit) in much the same way.

The patch from 11.2.0.1.0 to 11.2.0.2.0 should be downloaded from Metalink / My Oracle Support.

Starting with 11.2.0.2.0, any subsequent updates can be downloaded from with the installer itself, either as an integrated part of the installation process, or in advance (useful when destined server is restricted from accessing the Internet) by running the installer with the option -downloadUpdates.

I guess you have already considered just running the datapump tools from the actual Oracle home of the database in question?

These days, you may also be offered to try out a new and enhanced version of the software delivery cloud. Look for a big link on the landing page/login page.

On a final note, lots of additional information can be found in the Client Installation Guides available in the Oracle Database Online Documentation 11g Release 2 (11.2) or Oracle Database Online Documentation 12c Release 1 (12.1).