Sukriti Jaitly
18BCE0250
DBMS DA-2
create table employee(
EMP_ID NUMBER(6) NOT NULL,
FIRST_NAME VARCHAR2(20),
LAST_NAME VARCHAR2(25) NOT NULL,
EMAIL VARCHAR2 (25) NOT NULL,
PHONENUMBER VARCHAR2(15) NOT NULL,
HIREDATE DATE NOT NULL,
JOB_ID VARCHAR2(25) NOT NULL,
SALARY NUMBER(8),
COMMISION NUMBER(4,2),
MANAGER_ID NUMBER(6),
DEPARTMENT_ID NUMBER(4));
Table created.
SQL> select (SALARY*12)+100 from employee;
(SALARY*12)+100
---------------
120100
120100
240100
SQL> select LAST_NAME,HIREDATE from employee where HIREDATE between '01-jan-1995
' and '30-dec-1995';
LAST_NAME HIREDATE
------------------------- ---------
smith 19-OCT-95
SQL> select LAST_NAME,JOB_ID from employee where JOB_ID not in('IT_PROG','ST_cle
rk','sa-rep');
LAST_NAME JOB_ID
------------------------- -------------------------
sukriti 1
sam 2
smith 3
SQL> select * from employee where(job_id='president' and salary>15000) or (job_i
d='sales representative');
no rows selected
SQL> select emp_id,last_name,hiredate from employee order by salary desc;
EMP_ID LAST_NAME HIREDATE
---------- ------------------------- ---------
3 smith 19-OCT-95
1 sukriti 20-SEP-16
2 sam 20-DEC-17
SQL> select last_name,job_id,hiredate from employee where hiredate between '20-f
eb-1998'and '01-may-1998'order by hiredate asc;
no rows selected
SQL> select last_name,department_id from employee where hiredate like'%1995';
no rows selected
SQL> select last_name,department_id from employee where hiredate like'%95';
LAST_NAME DEPARTMENT_ID
------------------------- -------------
smith 3
SQL> select first_name,job_id from employee where manager_id is null;
no rows selected
SQL> select last_name,department_id from employee where department_id in (2,3) order by
first_name asc;
LAST_NAME DEPARTMENT_ID
------------------------- -------------
sam 2
smith 3
SQL> select last_name from employee where first_name like ' a%';
no rows selected
SQL> select last_name from employee where first_name like ' y%';
LAST_NAME
-------------------------
sukriti
SQL> select last_name from employee where last_name like 'a%' and last_name like '%e';
no rows selected
SQL> select last_name from employee where last_name like 'a%' and last_name like '%l';
LAST_NAME
-------------------------
sukriti
SQL> select last_name ||', '||job_id as "employee and title" from employee;
employee and title
----------------------------------------------------
sukriti, 1
sam, 2
smith, 3
SQL> select emp_id,last_name,job_id from employee where job_id like 'LA_';
no rows selected
SQL> select last_name,job_id,salary from employee where salary not in(2500,3400,7000);
LAST_NAME JOB_ID SALARY
------------------------- ------------------------- ----------
sukriti 1 10000
sam 2 10000
smith 3 20000
SQL> select hiredate as "Date" from employee;
Date
---------
20-SEP-16
20-DEC-17
19-OCT-95
SQL> select avg(salary) from employee;
AVG(SALARY)
-----------
13333.3333
SQL> select department_id from employee order by department_id asc;
DEPARTMENT_ID
-------------
SQL> select first_name from employee order by first_name desc;
FIRST_NAME
--------------------
harry
james
john
SQL> select min(salary),max(salary) from employee;
MIN(SALARY) MAX(SALARY)
----------- -----------
10000 20000
SQL> select count(*) from employee where salary>10000;
COUNT(*)
----------
SQL> select initcap(first_name) ||','|| emp_id as "name ,emp number" from employee;
name ,emp number
-------------------------------------------------------------
John,1
James,2
Harry,3
SQL> select * from employee where first_name like ' ';
EMP_ID FIRST_NAME LAST_NAME EMAIL PHONENUMBER HIREDATE JOB_ID
---------- -------------------- ------------------- -------------- --------------- ---------------------- ------------
3 harry smith artt@gmail.com 123456782 19-OCT-95 3
SALARY COMMISION MANAGER_ID DEPARTMENT_ID
---------- ---------- ---------- -------------
20000 18.22 3 3
