ORACLE ASSGN-2

1. Create an employee table with attributes

emp_id not null number(6)
First_name varchar2(20)
Last_name not null varchar2(25)
Email not null varchar2(25)
Phone_no varchar2(20)
Hire_date not null date
Job_id not null varchar2(10)
Salary number(8,2)
Commission number(8,2)
Manager_id number(6)
Dept_id number(4)

2. Insert 10 records. Confirm the addition of records into the table

3. Create a query to display the last name, job_id, hire_date, emp_id for each employee with emp_id appearing first. Provide an alias startdate for the hire_date column. Save your SQL statements to a file named lab_2.sql.

4. Create a query to display unique job_IDfrom the employee table
display the last name concatenated with the job_id separated by a comma and space, and name the column ‘employee and title’ .

5. Create a query to display all the data from the employee table. Separate each column by a comma. Name the column output.

6 Change the last name of employee 3 to ‘Alexander’
7 Change the salary to 10000 for all the employees with a salary < 9000. Verify the changes into the table.
8 Delete employee 4 record from employee table. Confirm the deletion.
9. Create a department table with attribute
Dept_id
Dept_name
Manage_id
Location_id

Insert 10 records into it.

10. Creat a dept table and populate the dept table with data from the department table. include the only columns you need
11. Create the emp table and populate the emp table with data from the employee table
modify the emp table to allow for longer employee last names. Confirm your modification
12. Create the emp_2 table based on the employee table. Include only emp_id, first_name, Clast_name, Salary and dept_id.
13. Drop the emp table
14. Rename the emp_2 table as emp
15. Drop the first_name column from the emp table