DBMS Lab Cycle 2 (student_info)

1. Create the table STUDENT_INFO with Columns: Sid, Stud_name & stude_score.

create table STUDENT_INFO(
sid NUMBER(3) NOT NULL,
stud_name VARCHAR2(100) NOT NULL,
stud_score NUMBER(3) DEFAULT 20,
primary key(sid)
);

-->  Insert  values into STUDENT_INFO with the following constraints: Stud name NOT NULL and stude_score DEFAULT value of 20.

INSERT INTO STUDENT_INFO (sid,stud_name)
VALUES(9,'Adharsh Sunny');

INSERT INTO STUDENT_INFO
VALUES(2'Dimal Mathew',200);

INSERT INTO STUDENT_INFO
VALUES(3,'Arun Joji',170);

INSERT INTO STUDENT_INFO
VALUES(4,'MAnuel Cherian',180);

INSERT INTO STUDENT_INFO
VALUES(5,'Christin Chacko',90);

INSERT INTO STUDENT_INFO
VALUES(6,'Jithu Sebastian',110);

INSERT INTO STUDENT_INFO
VALUES(7,'Fazil Habeeb',160);

INSERT INTO STUDENT_INFO

VALUES(8,'Visakh harikumar',101);

-->  Update stude_score by adding a value of 5 to stude_score in the table STUDENT_INFO for the rows satisfying the condition of stude_score >150

update  STUDENT_INFO set stud_score = stud_score + 5 where stud_score > 150;


2.   Create the table EMP_DETAILS with columns EMP_ID integer and EMP_SAL float with the below mentioned constraints.

• Add the constraint DEFAULT to EMP_SAL with 10000.
• Add the constraint UNIQUE to EMP_ID.

create table emp_details(

emp_id NUMBER(3) UNIQUE,

emp_sal NUMBER(8,2) DEFAULT 10000

);

INSERT INTO emp_details (emp_id)

VALUES(101);

INSERT INTO emp_details

VALUES(102,10001);

INSERT INTO emp_details

VALUES(103,20000);

INSERT INTO emp_details

VALUES(104,30000);

INSERT INTO emp_details

VALUES(105,40000);

INSERT INTO emp_details

VALUES(106,50000);

INSERT INTO emp_details

VALUES(107,60000);

INSERT INTO emp_details

VALUES(108,70000);

INSERT INTO emp_details

VALUES(109,90000);

---> Update all rows of EMP_SAL of table as EMP_SAL = EMP_SAL +500

update emp_details set emp_sal=emp_sal + 500;

3. Create the tables  worker and bonus with the following fields. The primary key of Worker table is WorkerID

WORKER table

BONUS table

create table worker(

worker_id NUMBER(3) not null,

first_name VARCHAR2(20) not null,

last_name VARCHAR2(20) not null,

salary NUMBER(8,2) not null,

joining_date timestamp(0) not null,

department varchar2(15),

primary key (worker_id)

);

insert into worker

values(101,'Monika','Arora',100000,to_date('14-02-2014 09:00:00', 'DD-MM-YYYY HH24:MI:SS'),'HR');

insert into worker

values(102,'Niharika','Verma',80000,to_date('11-06-2014 09:00:00', 'DD-MM-YYYY HH24:MI:SS'),'Admin');

insert into worker

values(103,'Vishal','Singhal',300000,to_date('14-02-2014 09:00:00', 'DD-MM-YYYY HH24:MI:SS'),'HR');

insert into worker

values(104,'Amitabh','Sigh',500000,to_date('14-02-2014 09:00:00', 'DD-MM-YYYY HH24:MI:SS'),'Admin');

insert into worker

values(105,'Vivek','Bhati',500000,to_date('11-06-2014 09:00:00', 'DD-MM-YYYY HH24:MI:SS'),'Admin');

insert into worker

values(106,'Vipul','Diwan',200000,to_date('11-06-2014 09:00:00', 'DD-MM-YYYY HH24:MI:SS'),'Account');

insert into worker

values(107,'Satish','Kumar',75000,to_date('20-01-2014 09:00:00', 'DD-MM-YYYY HH24:MI:SS'),'Account');

insert into worker

values(108,'Geetika','Chauhan',90000,to_date('11-04-2014 09:00:00', 'DD-MM-YYYY HH24:MI:SS'),'Admin');

create table bonus(

worker_id NUMBER(3) references worker(worker_id),

bonus_date timestamp(0),

bonus_amount NUMBER(8,2)

);

insert into bonus

values(101,to_date('20-02-2016 00:00:00', 'DD-MM-YYYY HH24:MI:SS'),5000);

insert into bonus

values(102,to_date('11-06-2016 00:00:00', 'DD-MM-YYYY HH24:MI:SS'),3000);

insert into bonus

values(103,to_date('20-02-2016 00:00:00', 'DD-MM-YYYY HH24:MI:SS'),4000);

insert into bonus

values(101,to_date('20-02-2016 00:00:00', 'DD-MM-YYYY HH24:MI:SS'),4500);

insert into bonus

values(102,to_date('11-06-2016 00:00:00', 'DD-MM-YYYY HH24:MI:SS'),3500);

--> Write An SQL Query To Fetch “FIRST_NAME” From Worker Table Using The Alias Name As <WORKER_NAME>.

select first_name "Worker Name" from worker;

--> Write An SQL Query To Print All Worker Details From The Worker Table Order By FIRST_NAME Ascending.

select * from worker order by first_name asc;

--> Write An SQL Query To Print All Worker Details From The Worker Table Order By FIRST_NAME Ascending And DEPARTMENT Descending.

select * from worker order by first_name,department desc;

--> Write An SQL Query To Print Details Of Workers Excluding First Names, “Vipul” And “Satish” From Worker Table.

select * from worker where first_name not in ('Vipul','Satish');

--> Write An SQL Query To Print Details Of Workers With DEPARTMENT Name As “Admin”.

select * from worker where department like 'Admin';

--> Write An SQL Query To Print Details Of The Workers Whose SALARY Lies Between 100000 And 500000.

select * from worker where salary between 100000 and 500000;

--> Write An SQL Query To Fetch “FIRST_NAME” From Worker Table In Upper Case. (upper( )).

select upper(first_name) from worker;

--> Write An SQL Query To Fetch Unique Values Of DEPARTMENT From Worker Table.

select distinct department from worker;

--> Write An SQL Query To Print First Three Characters Of  FIRST_NAME From Worker Table.( substring( )).

select substr(first_name,0,3) from worker;

--> Write An SQL Query To Print The FIRST_NAME From Worker Table After Removing White Spaces From The Right Side( RTRIM ( )).

select rtrim(first_name) from worker;

--> Write An SQL Query To Print The DEPARTMENT From Worker Table After Removing White Spaces From The Left Side. ( LTRIM ( )).

select ltrim(department) from worker;

--> Write An SQL Query That Fetches The Unique Values Of DEPARTMENT From Worker Table And Prints Its Length.( length()).

select distinct department,length( department) from worker;

-->Write An SQL Query To Print The FIRST_NAME From Worker Table After Replacing ‘a’ With ‘A’.( REPLACE( ))

select replace(first_name,'a','A') from worker;

--> Write An SQL Query To Print Details Of The Workers Whose FIRST_NAME Contains ‘A’.

select *  from worker where first_name like '%A%';

--> Write An SQL Query To Print Details Of The Workers Whose FIRST_NAME Ends With ‘A’

select *  from worker where first_name like '%A' or first_name like '%a';

--> Find the First name , last name ,Department, Salary and Bonus of   employees whose bonus amount is greater than 4000.

select w.first_name,w.last_name,w.department,w.salary,b.bonus_amount
from worker w,bonus b

where b.bonus_amount > 4000 and b.worker_id=w.worker_id;


ALTERNATIVE SOLUTION


select w.first_name,w.last_name,w.department,w.salary,b.bonus_amount
from bonus b
left outer join worker w on w.worker_id = b.worker_id

where b.bonus_amount > 4000;

--> Find the First name,Last name ,department and Salary of  employees whose salary is greater than 30000


select first_name,last_name,department,salary from worker where salary > 30000;