Table for employee having attributes (emp_id, emp_name, emp_dept)
Table for branch (emp_id, emp_current_branch, emp_salary)
Enter 5 dummy records in database
-
All the columns should be Not Null
-
Emp_salary should not be greater than 25000
-> create table emp420(emp_id number(10) primary key,emp_name varchar(30) not null,emp_dept varchar(30) not null )
>>>>create table branch420(emp_id number(10) not null,emp_current_branch varchar(30) not null,emp_salary number(10) not null check(emp_salary >=25000 ))
-
Show the emp_id and emp_current branch for those having id E1001.
-
->1.select emp_id,emp_current_branch from branch420 where emp_id=111
-
Match the pattern for Emp_name who have last alphabet ‘R’ in their name.
->2.select *from emp420 where emp_name like ‘%d’
-
Count number of employee working in ‘HR’ department.
-
->3.select count(emp_dept) from emp420 where emp_dept=’HR’
-
Add a new column in Employee table as “Permanent Address”
-
>4.alter table emp420 add addres varchar(30)
-
Add a new column Company name and having the default entry as “COGNIZANT”
-
->5.alter table emp420 add company varchar(30) default ‘COGNIZANT’
-
Put a restriction over branch table that it would can never insert any extra entry, that is not present in employee table.
-
(add foreign key)->alter table branch420 add foreign key(emp_id) references emp420(emp_id)
-
Update the size of emp_dept to 20
-
->6.alter table emp420 modify emp_dept varchar(20)
-
Update the salary of employee E1001 from 10000 to 24000
-
update branch set emp_salary=24000 where emp_id=’111′
-
Rename the column “Permanent address” to “Address”
-
alter table emp420 remane column addres to permanent_address
Aryan Blog 