CREATE TABLE regions as select from hrregions CREATE TABLE

CREATE TABLE regions

as select * from hr.regions;

CREATE TABLE locations

as select * from hr.locations;

CREATE TABLE departments

as select * from hr.departments;

1. Drop the Departments table and its constraints in your account.

2. Recreate the Departments table in your account based on hr.departments table. However, your Departments table only has the data for the departments with the department names starting with ‘IT’.

3. Create tables based on the following data dictionaries. Make sure you implement all the constraints such as primary keys and foreign keys
a) Staff (Staff_ID, Staff_Fname, Staff_Lname, RoomNo, Building, Salary)

b) Staff_skill (Staff_ID, Skills

4. Add email column (100 characters, not null) into Staff table

5. Rename the column name email to staff_email (Staff table)

Solution

2)SQL> CREATE TABLE hr.departments AS

3) a) SQL>create table Staff(

staff_ID varchar2(10) Primary key,

Staff_Fname varchar2(50) not null,

Staff_Lname varchar2(50) not null,

RoomNo varchar2(20),

Building varchar2(50),

Salary number(20)

);

b) SQL>Create table Staff_Skill(

Staff_ID varchar2(10) References Staff(Staff_ID),

Skills varchar2(50)

);

4) ALTER TABLE Staff
ADD email varchar2(100);

5) ALTER TABLE Staff RENAME COLUMN email TO staff_email;

Thank You :-)