dbms set 4 gtu

SET-4

Create the database COMPANY and create given tables with all necessary constraints such as primary key, foreign key, unique key, not null and check constraints.  

EMPLOYEE (emp_id, emp_name, birth_date, gender, dept_no, address, designation, salary, experience, email)

create table employee (

               emp_id  int primary key,

               emp_name  varchar2(20),

               birth_date date,

               gender varchar2(6),

               DEPT_NO number(38) REFERENCES DEPARTMENT (DEPT_NO),

               address varchar2(100),

               designation varchar2(15),

               salary decimal(9,2),

               experience  varchar2(5),

 email  varchar2(255) CONSTRAINT chk_emp_email CHECK (REGEXP_LIKE (email,'^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,4}$'))

);

create table DEPARTMENT

(

dept_no int primary key,

dept_name varchar(10)NOT NULL UNIQUE,

 total_employees INT,

               location varchar(10)

);

 CREATE TABLE PROJECT(

proj_id INT NOT NULL PRIMARY KEY ,

type_of_project varchar2(30),

 status varchar2(10), start_date date,

                              emp_id int REFERENCES employee(emp_id)

 );

                  Select  *  from  employee;

           Select  *  from  department;

                      Select   *  from  project;

      Delete the department whose total number of employees less than 1.

 delete from depart where total_employees <1;

2       Display the names and the designation of all female employee in descending order

 select emp_name,designation,gender from employee where gender='female'order by  emp_name desc;

3       Display the names of all the employees who names starts with ‘A’ ends with ‘A’

Select  *  from employee  where  emp_name  LIKE 'a%a';

          Find the name of employee and salary for those who had obtain minimum salary?

select emp_name, salary from employee where salary=(select min(salary)from employee);

5       Add 10% raise in salary of all employees whose department is ‘CIVIL’.

 update employee set salary=salary+(salary*0.10)where dept_no=(select dept_no from department where dept_name='CIVIL');

       Count total number of employees of ‘MCA’ department.

 select count(dept_no) from employee here dept_no=(select dept_no from department  where dept_name='MCA');

                      List all employees who born in the current month.

Select  emp_name from employee where to_char(birth_date,'MON')=to_char(sysdate,'MON');

8      Print the record of employee and dept table as “Employeeworks in department ‘CE’.    
      select emp_name||'works in department', dept_name from employee, department;

      List names of employees who are fresher’s(lessthan 1 year of experience).

 select emp_name from employee where experience <=1;

1      List department wise names of employees who has more than 5 years of experience.

select d.dept_name, e.emp_name from employee e, department d where experience >5 ORDER BY d.dept_name ASC;