Databases
StackOverflow2013
Postsdbo
POUsing PL/SQL to develop relationship among two tables?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POUsing PL/SQL to develop relationship among two tables?
    primarykey
    data
    text
    <p>I am new to the PL/SQL language and I am working on a problem and was looking for advice to the right direction to go. Any help would be greatly appreciated!</p> <p>I have an Employee table and a Building table.<br> The Employee table consists of Employee_ID, Last_Name, First_Name, and Job(F/P) full or part time. The Building table consists of Employee_ID and Building_location.(This is the location where the employee works from) either a home or office.</p> <p>I want the user to type in the last_name from user input. As a result I am looking for the information from the employee: Employee Name, Job (full or part employee) and the building_location. For example, I want my output to look something like this if the user typed in Johnson:</p> <pre><code>Employee Name Job Position Building Location ==================================================== Andre Johnson Part Time Home Office Nick Johnson Full Time Downtown Office </code></pre> <p>If a user types in any string I want to list all Employees who work for the company. But if the name is on the Employee table like 'Johnson' is then it only displays Johnson. Would I need to add in another SELECT clause?</p> <p>At this point I put together some code that checks if the employee works for the company or not. I am looking to build from this.</p> <pre><code>ACCEPT p_1 PROMPT 'Please enter the last name of the employee:' DECLARE v_name employee.last_name%TYPE := '&amp;p_1'; v_lname employee.last_name%TYPE; v_fname employee.first_name%TYPE; v_jobpos employee.job%TYPE; v_buildtype building.building_type%TYPE; v_count NUMBER; BEGIN SELECT COUNT(*) INTO v_count FROM employee WHERE UPPER(last_name) = UPPER(v_name); IF v_count = 0 THEN DBMS_OUTPUT.PUT_LINE('EMPLOYEE NAME Job Position Building Location'); DBMS_OUTPUT.PUT_LINE('----------------------------------------------------' ); for i in (select * from employee order by last_name) loop DBMS_OUTPUT.PUT_LINE(i.last_name || ', ' || i.first_name) end loop; ELSE for i in (select * from employee order by last_name) loop DBMS_OUTPUT.PUT_LINE(i.last_name || ', ' || i.first_name); end loop; END IF; END; </code></pre>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USDaBulls33
    1. USDaBulls33
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POUsing PL/SQL to develop relationship among two tables?
      1. This table or related slice is empty.
      1. VTUpMod
    1. COShouldn't there be a building_id column in the employee table rather than an employee_id column in the building table?
      singulars
      1. POUsing PL/SQL to develop relationship among two tables?
      1. USDavid Aldridge
    2. COMy tables are linked together from the Employee_ID @David Aldridge
      singulars
      1. POUsing PL/SQL to develop relationship among two tables?
      1. USDaBulls33
    3. COOne building per employee?
      singulars
      1. POUsing PL/SQL to develop relationship among two tables?
      1. USDavid Aldridge
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload