Sergio Caltagirone
CS360 - Databases
12/12/03
Project #3


1) Create a view on staff where any of the staff can only see the name, position, 
and branch number for the staff that work at that branch

DROP VIEW staffView;

CREATE VIEW staffView
AS SELECT Name, Position, BranchNo
FROM Staff;

a) Execute a query that demonstrates that the view works

UPDATE staffView
SET Salary = 70000
WHERE Name = 'Michael Berg';

-- output --
SET Salary = 70000
    *
ERROR at line 2:
ORA-00904: invalid column name
===============
SELECT *
FROM staffView;

-- output --
NAME                                               POSITION             BR
-------------------------------------------------- -------------------- --
Michael Berg                                       Manager              B1
Anne Johnson                                       Sales                B2
Tom Jones                                          Sales                B1
Mary Thompson                                      Secretary            B1
Tim Feeley                                         Assist. Mang         B2
Alison Tan                                         Sales                B3
Clancy Finey                                       Manager              B4
Terran Michaels                                    Sales                B4

8 rows selected.


-----------------------------------------------------------------------------------
2) Create a view of clients listing their memberno, fname, lname, and the movies 
they have out including the title of the movie and video number

DROP VIEW clientView;

CREATE VIEW clientView
AS SELECT C.MemberNo, C.FName, C.LName, R.VideoNo, R.Title
FROM Customer C, Rented R
WHERE C.MemberNo = R.MemberNo AND R.DateIn IS NULL;

a) Execute a query that shows the view works

SELECT *
FROM clientView;

-- output --
MEMB FNAME  LNAME    VID TITLE
---- ------ -------- --- --------------------------------------------------
525  Marci  Smith    113 Terminator
525  Marci  Smith    36  Scary Movie 2
1036 Mark   Thomas   100 28 Days Later
1036 Mark   Thomas   30  Halloween 5
1036 Mark   Thomas   42  Star Wars I

------------------------------------------------------------------------------------
3) Write a stored PL/SQL procedure that produces a report of a branch office B1, 
including the branch number, street, city and state, plus a total of the salaries 
of all employees who work at the branch

DECLARE
  bgv     Branch.BranchNo%type;
  bstreet Branch.Street%type;
  bcity   Branch.City%type;
  bstate  Branch.State%type;
  bsalary Staff.Salary%type;

procedure report_branch(
  b_gv      in     Branch.BranchNo%type,
  b_street  out    Branch.Street%type,
  b_city    out    Branch.City%type,
  b_state   out    Branch.State%type,
  b_salary  out    Staff.Salary%type) is

begin
  SELECT DISTINCT B.Street, B.City, B.State, SUM(S.Salary)
  INTO b_street, b_city, b_state, b_salary
  FROM  Branch B, Staff S
  WHERE B.BranchNo = b_gv AND S.BranchNo = B.BranchNo
  GROUP BY B.Street, B.City, B.State, B.BranchNo;
end;

begin
  bgv := 'B1';
  report_branch(bgv,bstreet,bcity,bstate,bsalary);
  dbms_output.put_line('Branch: ' || bgv);
  dbms_output.put_line(bstreet || ' ' || bcity || ', ' || bstate);
  dbms_output.put_line('Total Salary: ' || bsalary);
end;
/


a) Run the PL/SQL procedure and show the output from it

SQL> start pl
Branch: B1
23 Washington St. Davis, CA
Total Salary: 84000

PL/SQL procedure successfully completed.

------------------------------------------------------------------------------------
4) Create a package in PL/SQL that contains the following two procedures:

4.1) Takes the employee information as input name, position, etc. and tries to insert the
information into the staff table. If the employee's position is the manager of a branch 
and a manager already exists, an error is printed out, else the employee is inserted into 
the table.

4.2) A procedure to add a video to the video table. Takes the video information as input 
– Branchno, catalogno, videono – and if the catalog number exists, and is not NULL, 
the video is entered into the table, else an error message is printed.

CREATE OR REPLACE PACKAGE videoProcs AS
procedure insertEmp(
  s_name in Staff.Name%type,
  s_pos  in Staff.Position%type,
  s_sal  in Staff.Salary%type,
  s_no   in Staff.StaffNo%type,
  s_bno  in Staff.BranchNo%type
);
procedure insertVid(
  v_bno  in Video.BranchNo%type,
  v_cat  in Video.CatalogNo%type,
  v_vno  in Video.VideoNo%type
);
end videoProcs;
/
show errors

CREATE OR REPLACE PACKAGE BODY videoProcs AS
procedure insertEmp(
  s_name in Staff.Name%type,
  s_pos  in Staff.Position%type,
  s_sal  in Staff.Salary%type,
  s_no   in Staff.StaffNo%type,
  s_bno  in Staff.BranchNo%type) IS
  t_cnt Staff.StaffNo%type;

begin
  SELECT COUNT(S.StaffNo)
  INTO t_cnt FROM STAFF S
  WHERE Position='Manager';

  if(t_cnt > 0) and (s_pos='Manager') then
    dbms_output.put_line('There already exists a manager');
  else
    INSERT INTO STAFF
      VALUES(s_name,s_pos,s_sal,s_no,s_bno);
  end if;
end;

procedure insertVid(
  v_bno in Video.BranchNo%type,
  v_cat in Video.CatalogNo%type,
  v_vno in Video.VideoNo%type) IS
  t_cno VideoCatalog.CatalogNo%type;
begin
  SELECT COUNT(C.CatalogNo)
  INTO t_cno FROM VideoCatalog C
  WHERE C.CatalogNo=v_cat;

  if(t_cno > 0) and (v_cat is not null) then
    dbms_output.put_line('The catalog does not exist or the
                          catalog number provided is null');
  else
    INSERT INTO Video
      VALUES(v_bno,v_cat,v_vno);
  end if;
end;
end;
/
show errors

a) Show both these procedures work by calling them with appropriate input values

SQL>execute videoProcs.insertEmp('Bob Howdy','Manager',20000,'S20','B2');
There already exists a manager

SQL> execute videoProcs.insertEmp('Bob Howdy','Sales',20000,'S15','B2');
PL/SQL procedure successfully completed.

SQL> execute videoProcs.insertVid('B3','V29','99');
The catalog does not exist or the catalog number provided is null