Drop table jobs cascade constraints; Drop table employees cascade constraints; D

Question

Drop table jobs cascade constraints;

Drop table employees cascade constraints;

Drop table accounts cascade constraints;

CREATE TABLE employees

as select * from hr.employees;

CREATE TABLE jobs

as select * from hr.jobs;

CREATE Table accounts (cust_fname varchar(20),

       cust_lname varchar(20),

       acc_open_date date,

       balance number(20,2));

1.Insert a new job into Jobs table with job_id of ‘NET-MAN’, job_title of ‘Network Manager’, min_salary 30000, and max_salary 100000.

2.Import four columns (first_name, last_name, hire_date, salary) data from employees table into accounts table.

3.Update commission_pct to 0.25 for employees whose job_id is ‘SA_REP’. (employees table)

4.Update all the balances to Peter Hall’s balance for customers whose first name ends with ‘en’. (Accounts tables)

Hint: the first example in Slide 8 of Ch5. You need to create a subquery to get Peter Hall’s balance. In main statement, you set balance = subquery

5.Delete rows such that the year of acc_open_date is 1998. (Accounts tables)

Hint: use extract (year from acc_open_date) in Where clause

Explanation / Answer

1.

INSERT INTO Jobs VALUES('NET-MAN','Network Manager',30000,100000);

2.

INSERT INTO Accounts(first_name,last_name,hire_date,salary) select oldcloumn1,oldcolumn2,oldcolumn3,oldcolumn3 FROM employees;

3.

UPDATE employees

SET commission_pct=0.25 WHERE job_id='SA_REP';

4.

UPDATE accounts

SET balance=(SELECT min_balance FROM jobs) WHERE first_name LIKE %en;

5.

DELETE FROM accounts

WHERE acc_open_date=1998;

Related Questions

Navigate

• Browse (All)
• Browse D
• Subjects

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