CREATE TABLE Drinkers ( name VARCHAR(20) PRIMARY KEY, addr VARCHAR(200) DEFAULT

Question

CREATE TABLE Drinkers ( name VARCHAR(20) PRIMARY KEY, addr VARCHAR(200) DEFAULT ?123 Sesame St.?, phone VARCHAR(16) NOT NULL salary REAL, position VARCHAR(20) 1-add constraint to enforce 100.000> salary >1,000. 2- add constraint to enforce salary> 30,000 if position is ?Manager? 3- Create a trigger, named SalaryTrig. to record drinker name. his/her new salary and old salary (in another table. named HighManagers which you need to create first) for a ?Manager? drinker when his/her salary is changed to be higher than or equal to 50,000 if his/her old salary is less than 50.000. 4- Write insert-statements to add drinkers to add drinkers? information below Then, update the salary for all managers to be 70.000. Show the results of your HighManagers table.

Explanation / Answer

Oracle

create table drinkers
(name varchar(20) primary key,
addr varchar(200) default '123 Sesame St.',
phone varchar(16) not null,
salary real,
position varchar(20));


ALTER TABLE drinkers ADD (CONSTRAINT salary_check CHECK (salary >= 1000 AND salary <= 100000));

ALTER TABLE drinkers ADD (CONSTRAINT mgr_salary_check CHECK (position IS NULL OR (UPPER(position) = 'MANAGER' AND salary > 30000)));


create table highManager
(name varchar(20) primary key,
addr varchar(200) default '123 Sesame St.',
phone varchar(16) not null,
salary real,
position varchar(20));

CREATE OR REPLACE TRIGGER SalaryTrig
BEFORE INSERT ON drinkers FOR EACH ROW

DECLARE
   v_username varchar2(10);

BEGIN
   IF (UPPER(:old.position) = 'MANAGER')
   THEN
        dbms_output.put_line(:old.position||' '||:old.name);
    IF ((:old.salary < 50000) AND (:new.salary >= 50000))
    THEN
          -- Insert record into audit table
           INSERT INTO highManager
        (name, addr, phone, salary, position) VALUES
        (:new.name, :new.addr, :new.phone, :new.salary, :new.position);
    END IF;
   END IF;
   
END;
/

INSERT INTO drinkers (name, addr, phone, salary, position) VALUES ('larry', 'newyork', 12345, 10000, NULL);
INSERT INTO drinkers (name, addr, phone, salary, position) VALUES ('bossmeg', 'newyork', 22222, 60000, 'manager');
INSERT INTO drinkers (name, addr, phone, salary, position) VALUES ('chefjosh', 'newyork', 33333, 35000, 'manager');
INSERT INTO drinkers (name, addr, phone, salary, position) VALUES ('stewart', 'newyork', 44444, 20000, NULL);

commit;

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.