Oracle SQL (Structured Query Language)
- Creating a user:
create user Trupti identified by Trupti ;
SQL> User created.
2. Altering a user:
alter user Trupti identified by Trupti;
SQL>User altered.
Unlock user
alter user Trupti identified by Trupti account unlock; (from sys as sysdba login)
or
alter user Trupti account unlock; (from sys as sysdba login)
My Remind: Trupti cannot log into her account since she hasn’t create session system privilege:
3. Creating a Session for the User:
grant create session to Trupti
;
SQL>Grant succeeded.
4. Changing the Password of the existing user:
connect (or you can write conn) Trupti / Trupti
password
Changing password for Trupti
Old Password: Trupti
New Password: Fire
Retype New Password: Fire
Password changed.
5. Creating a Table:
create table EMP
( EMP_NUM NUMBER(4) NOT NULL,
EMP_FNAME VARCHAR(20) NOT NULL,
EMP_LNAME VARCHAR(20) NOT NULL,
JOB_CODE VARCHAR(3) ,
HIREDATE DATE NOT NULL )
;
SQL>Table created.
6. Inserting values in the Table:
a. Inserting values for all fields
insert into EMP
values (101, ‘John’, ’News’, ‘DBD’, ‘1-Jan-1990’)
;
SQL>1 row inserted.
b. Inserting values for particular fields
insert into EMP
(EMP_NUM, EMP_FNAME, EMP_LNAME,HIREDATE)
values (101, ‘John’, ’News’, ‘1-Jan-1990’)
;
SQL>1 row inserted.
7. Viewing all records from the Table:
select * from EMP;
EMP_NUM EMP_FNAME EMP_LNAME JOB_CODE HIREDATE
101 John News DBD 1-Jan-1990
Constraints in Create Table
a. UNIQUE / Candidate Key
create table EMP1
( EMP_NUM NUMBER(5) NOT NULL,
SAL NUMBER(7,3),
constraint unique1 UNIQUE (EMP_NUM) )
;
SQL>Table created.
b. Primary Key
create table EMP1
( EMP_NUM NUMBER(5) NOT NULL,
SAL NUMBER(7,3),
constraint prime1 PRIMARY (EMP_NUM) )
;
SQL>Table created.
c. Foreign Key
create table EMP1
( EMP_NUM NUMBER(5) NOT NULL,
SAL NUMBER(7,3),
constraint prime1 FOREIGN KEY (SAL)
references SALARY (SAL) )
;
SQL>Table created.
d. The Check Constraint
create table SALARY
(SAL NUMBER(5,2) NOT NULL CHECK (SAL > 10000) )
;
SQL>Table created.
8. Dropping (Deleting a Table)
drop table SALARY
;
SQL>Table created.
9. Altering Tables
a. Adding a new Column
alter table add
( HR_WRK NUMBER(4) NOT NULL,
CHG_HR NUMBER(5,2)
);
SQL>Table altered.
b. Modifying a new Column
alter table modify
( HR_WRK NUMBER(4) );
SQL>Table altered.
c. Dropping a new Column
alter table drop column HR_WRK;
SQL>Table altered.
10. Creating a Table from Table
create table SALARY as
select SAL from EMP;
SQL>Table created.
11. Select, From, Where and Order By
1. select EMP_NUM , EMP_FNAME from EMP
where JOB_CLASS = ‘DBA’;
2. select EMP_NUM , EMP_FNAME from EMP
where JOB_CLASS = ‘DBA’
order by HIREDATE;
3. select EMP_NUM , EMP_FNAME from EMP
where JOB_CLASS = ‘DBA’
order by HIREDATE, SALARY ;
4. select EMP_NUM , EMP_FNAME from EMP
where JOB_CLASS = ‘DBA’
order by HIREDATE, SALARY DESC; (For descending… Default Ascending)
5. select EMP_NUM , EMP_FNAME from EMP
where SALARY = 10000;
(here you can apply,
=, !=( or ^=), <=, >=, <, > )
6. select EMP_NUM , EMP_FNAME from EMP
where EMP_FNAME LIKE ‘KA%’;
(Returns the Employees which having name starting with ‘KA’ )
7. select EMP_NUM , EMP_FNAME from EMP
where EMP_FNAME LIKE ‘%A’;
(Returns the Employees which having name ending with ‘A’ )
8. select EMP_NUM , EMP_FNAME from EMP
where EMP_FNAME LIKE ‘%KA%’;
(Returns the Employees which having name with having ‘KA’ anywhere in their name)
9. select EMP_NUM , EMP_FNAME from EMP
where EMP_FNAME LIKE ‘_A%’;
(Returns the Employees which having name starting with any character second character must be ‘A’ and after that anything)
NULL and NOT NULL
1. select EMP_NUM , EMP_FNAME from EMP
where JOB_CLASS IS NULL;
2. select EMP_NUM , EMP_FNAME from EMP
where JOB_CLASS IS NOT NULL;
IN, BETWEEN..AND, OR & AND
1. select EMP_NUM , EMP_FNAME from EMP
where SALARY IN (10000, 15000, 25000);
2. select EMP_NUM , EMP_FNAME from EMP
where SALARY NOT IN (10000, 15000, 25000);
3. select EMP_NUM , EMP_FNAME from EMP
where SALARY BETWEEN 10000 AND 25000;
4. select EMP_NUM , EMP_FNAME from EMP
where SALARY NOT BETWEEN 10000 AND 25000;
5. select EMP_NUM , EMP_FNAME from EMP
where SALARY=10000
OR SALARY=25000 AND JOB_CLASS=’SYA’;
12. UPDATE, SUBQUERIES & Different CONSTRAINT relative to the following Tables
EMP
| EMP_NUM | EMP_FNAME | EMP_LNAME | JOB_CLASS | HIREDATE |
| 101 | John | News | DBD | 1-NOV-78 |
| 102 | David | Senior | SYA | 3-OCT-88 |
| 103 | June | Arbough | EEG | 23-JUN-99 |
| 104 | Anne | Ramoras | SYA | 30-FEB-92 |
| 105 | Alice | Johnson | DBD | 15-AUG-95 |
| 106 | William | Smithfield | PRG | 31-OCT-02 |
| 114 | Annelise | Jones | | |
| 118 | default value | Frommer | | |
JOB
| JOB_CODE | JOB_CLASS | CHG_HOUR |
| APD | Application Designer | $48.10 |
| DBD | Database Designer | $105.00 |
| EEG | Elec. Engineer | $84.50 |
| GSP | General Support | $18.36 |
| PRG | Programmer | $37.75 |
| SYA | Systems Analyst | $96.75 |
PROJECT
| PROJ | PROJ |
| _NUM | _NAME |
| 15 | Evergreen |
| 18 | Amber Wave |
PROJ_EMP
| PROJ | EMP | |
| _NUM | _NUM | HOUR |
| 15 | 103 | 23.8 |
| 15 15 18 18 | 101 105 114 118 | 19.4 35.7 24.6 25.3 |
1. Adding Default Value to a Field of the Table
alter table EMP
( EMP_FNAME VARCHAR(20) NOT NULL DEFAULT ‘UNKNOWN PERSON’ );
2. Add a new attribute to the JOB table named OvertimeCharge that must always have a value.
alter table JOB add
(OT_CHARG NUMBER(5) NOT NULL);
3. Add a constraint to the JOB table that will ensure all CHG_HOUR data is >= 25.
alter table JOB
add constraint chk_chg_hour
( check (CHK_HOUR >= 25) );
4. Change the CHG_HOUR column to store numbers up to 12 digits in length (including decimal points).
Drop the column CHG_HOUR, and then add the column with new size.
5. Drop the constraint chk_chg_hour from the CHG_HOUR column.
alter table JOB
drop constraint chk_chg_hour;
Data Manipulation Queries:
6. Update the employee fname to Jim instead of James for employee 118
update EMP set EMP_FNAME = ‘James’
where EMP_NUM = 118;
7. Update the Database Designer chg_hour value by 10%
update JOB set CHG_HOUR = CHG_HOUR + (CHG_HOUR * 0.10);
update JOB set CHG_HOUR = CHG_HOUR + (CHG_HOUR * 0.10);
8. Remove the row from the job table for job code PRG
delete from JOB where JOB_CODE = ‘PRG’;
delete from JOB where JOB_CODE = ‘PRG’;
9. Remove the employees from the employee table where the hiredate is before 1-Jan-90
delete from EMP where HIREDATE < ‘1-Jan-1990’;
delete from EMP where HIREDATE < ‘1-Jan-1990’;
Sub Queries & Diff. Select Queries:
1. Show the employee first and last names and their job class description and hourly charge.
select EMP_FNAME, EMP_LNAME, JOB_CLASS, CHG_HOUR
select EMP_FNAME, EMP_LNAME, JOB_CLASS, CHG_HOUR
from EMP E, JOB J
where E.EMP_NUM = J.EMP_NUM;
(HERE E IS ALIAS FOR EMP, SAME AS J IS ALIAS FOR JOB TABLE)
2. Same question as above, but only show the records where the chg_hour is <100
select EMP_FNAME, EMP_LNAME, JOB_CLASS, CHG_HOUR
from EMP E, JOB J
where E.JOB_CODE IN
(select JOB_CODE from JOB where CHG_HOUR>100)
AND
E.JOB_CODE = J.JOB_CODE;
EMP_FNAME EMP_LNAME JOB_CLASS CHG_HOUR
John News Database Designer 115.5
Alice Johnsan Database Designer 115.5
Anne Ramorus System Analyst 106.39
David Senior System Analyst 106.39
3. Select the hours worked and lastname and hiredate for all employees
select EMP_LNAME, HIREDATE, HOUR
from EMP E, PROJ_EMP PE
where E.EMP_NUM = PE.EMP_NUM;
EMP_LNAME HIREDATE HOUR
News 1-Jan-90 19.4
Arbough 23-Jun-99 23.8
Johnsan 15-Aug-95 35.7
Jones 23-Sep-11 24.6
Jones 23-Sep-11
Formmer 23-Sep-11 25.3
4. Select all project names and job class descriptions that the project requires. Save this select statement as a view called: EMP_JOB_SKILLS
create or replace view EMP_JOB_SKILLS as
select EMP_LNAME, PROJ_NAME, JOB_CLASS, E.JOB_CODE, CHG_HOUR
from EMP E, PROJECT, JOB J
where ( PROJ_NUM IN
(select PROJ_NUM from PROJ_EMP
Where E.EMP_NUM = PROJ_EMP.EMP_NUM)
AND
J.JOB_CODE IN
(select JOB_CODE from JOB
Where E.JOB_CODE = J.JOB_CODE)
)
;
SQL>View created.
To run that view
select * from EMP_JOB_SKILLS
EMP_LNAME PROJ_NAME JOB_CLASS JOB_CODE CHG_HOUR
Johnsan Evergreen Databs Designer DBD 115.5
News Evergreen Databs Designer DBD 115.5
My Remind: To view all table and views existing, first unlock user.
5. Create a view called: WORK_INFO which will select the hours worked and lastname and hiredate for all employees hired before 1-jan-02.
create or replace view WORK_INFO as
select EMP_LNAME, HIREDATE, HOUR
from EMP E, PROJ_EMP PE
where ( E.EMP_NUM = PE.EMP_NUM
AND
HIREDATE < ‘1-Jan-2002’ )
;
select * from WORK_INFO;
EMP_LNAME HIREDATE HOUR
News 1-Jan-90 19.4
Arbough 23-Jun-99 23.8
Johnsan 15-Aug-95 35.7
6. Select employee fname and lname and all projects that they are working on. Save this select statement as a view called: EMP_PROJECTS
create or replace view EMP_PROJECTS as
select EMP_FNAME, EMP_LNAME, PE.PROJ_NUM, PROJ_NAME
from EMP E, PROJ_EMP PE,PROJECT P
where
E.EMP_NUM = PE.EMP_NUM
AND
P.PROJ_NUM = PE.PROJ_NUM
;
select * from EMP_PROJECTS
;
EMP_FNAME EMP_LNAME PROJ_NUM PROJ_NAME
Analise Jones 15 Evergreen
Alice Johnsan 15 Evergreen
June Arbough 15 Evergreen
John News 15 Evergreen
Jim Formmer 18 Amberware
Analise Jones 18 Amberware
QUERY LIST 2:
Table: DEPT
| DEPTNO | DNAME | LOCATION |
| 10 | ACCOUNTING | NEW YORK |
| 20 | RESEARCH | DALLAS |
| 30 | SALES | CHICAGO |
| 40 | OPERATIONS | BOSTON |
Table: EMPLY
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | |
| 7369 | SMIT | CLERK | 7902 | 17-DEC-80 | 800 | - | 20 | |
| 7499 | ALLEN | SALESMAN | 7698 | 20-FEB-81 | 1600 | 300 | 30 | |
| 7521 | WARD | SALESMAN | 7698 | 22-FEB-81 | 1250 | 500 | 30 | |
| 7566 | JONES | MANAGER | 7839 | 02-APR-81 | 2975 | - | 20 | |
| 7654 | MARTIN | SALESMAN | 7698 | 28-SEP-81 | 1250 | 1400 | 30 | |
| 7698 | BLAKE | MANAGER | 7839 | 01-MAY-81 | 2850 | - | 30 | |
| 7782 | CLARK | MANAGER | 7839 | 09-JUN-81 | 2450 | - | 10 | |
| 7788 | SCOT | ANALYST | 7566 | 19-APR-87 | 3000 | - | 20 | |
| 7339 | KING | PRESIDENT | - | 17-NOV-81 | 5000 | - | 10 | |
| 7344 | TURNER | SALESMAN | 7698 | 08-SEP-81 | 1500 | 0 | 30 | |
| 7876 | ADAMS | CLERK | 7788 | 23-MAY-87 | 1100 | - | 20 | |
| 7900 | JAMES | CLERK | 7698 | 03-DEC-81 | 950 | - | 30 | |
| 7902 | FORD | ANALYST | 7566 | 23-JAN-82 | 1300 | - | 20 | |
| 7934 | MILLER | CLERK | 7782 | 23-JAN-82 | 1300 | - | 10 | |
1. Creating Tables
create table DEPT
( DEPT_NO NUMBER(4,2),
DNAME VARCHAR2(25),
LOCATION VARCHAR2(30)
)
;
create table EMPLY
( EMPNO NUMBER(4),
ENAME VARCHAR2(20),
JOB VARCHAR2(15),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(5,2),
COMM NUMBER(4,2),
DEPTNO NUMBER(4,2)
)
;
2. Displaying all data of both tables
select * from DEPT
;
DEPT_NO DNAME LOCATION
------------- ---------------------- ------------------
10 Accounting New York
20 Research Dallas
30 Sales Chicago
40 Operations Boston
-- select * from EMPLY
;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------------- -------- ----------------- ------- ----------- -------------
7521 Ward Salesman 7698 22-FEB-81 1250.0 500.0 30
7369 Smit Clerk 7902 17-DEC-80 800.0 20
7499 Allen Salesman 7698 20-FEB-81 1600.0 300.0 30
7566 Jones Manager 7839 02-APR-81 2975.0 20
7654 Martin Salesman 7698 28-SEP-81 1250.0 1400.0 30
7696 Blake Manager 7839 01-May-81 2850.0 30
7782 Clark Manager 7839 09-Jun-81 2450.0 10
7788 Scott Analyst 7566 19-Apr-87 3000.0 20
7339 King President 17-Nov-81 5000.0 10
7344 Turner Salesman 7698 08-Sep-81 1500.0 .0 30
7876 Adams Clerk 7788 23-May-87 1100.0 20
7900 James Clerk 7698 03-Dec-81 950.0 30
7902 Ford Analyst 7566 25-Jan-82 1300.0 20
7934 Miller Clerk 7782 25-Jan-82 1300.0 10
Queries
1. List all the employee name, salary in the order of salary from highest to lowest
select ENAME, SAL
from EMPLY
order by SAL desc;
ENAME SAL
---------- -------
King 5000.0
Scot 3000.0
Jones 2975.0
Blake 2850.0
Clark 2450.0
Allen 1600.0
Turner 1500.0
Ford 1300.0
Miller 1300.0
Ward 1250.0
Martin 1250.0
Adams 1100.0
James 950.0
Smit 800.0
14 rows selected.
2. List all the name of employees whose name contains two occurrence of ‘A’ or ‘a’.
select ENAME
from EMPLY
where ENAME LIKE '%A%a%'
;
ENAME
----------
Adams
27th sep, 2011
3. List details of employee who hired in year 81
FIRST,
A view is created to store only date in character string format and EMPNO.
SQL> CREATE OR REPLACE VIEW TEMP_DT AS
2 select EMPNO, TO_CHAR(HIREDATE,'YY') AS EMP_D
3 FROM EMPLY;
View created.
SQL> SELECT * FROM TEMP_DT
2 ;
EMPNO EMP_DT
---------- -------------
7521 81
7369 80
7499 81
7566 81
7654 81
7696 81
7782 81
7788 87
7339 81
7344 81
7876 87
7900 81
7902 82
7934 82
14 rows selected.
SECONDLY,
Select records whose year is = 81
SQL> select E.EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO
from EMPLY E, TEMP_DT
where
( EMP_DT = 81
AND
E.EMPNO = TEMP_DT.EMPNO
)
;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------------------------------------------------------------------------------------------------- 7521 Ward Salesman 7698 22-FEB-81 1250.0 500.0 30
7499 Allen Salesman 7698 20-FEB-81 1600.0 300.0 30
7566 Jones Manager 7839 02-APR-81 2975.0 20
7654 Martin Salesman 7698 28-SEP-81 1250.0 1400.0 30
7696 Blake Manager 7839 01-MAY-81 2850.0 30
7782 Clark Manager 7839 09-JUN-81 2450.0 10
7339 King President 17-NOV-81 5000.0 10
7344 Turner Salesman 7698 08-SEP-81 1500.0 .0 30
7900 James Clerk 7698 03-DEC-81 950.0 30
9 rows selected.
4. Display average salary paid to employees working in department number 20.
SQL> SELECT * FROM EMPLY
2 WHERE DEPTNO = 20;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------------- -------- ---- -- ------- ------- --------------------------
7369 Smith Clerk 7902 17-DEC-80 800.0 20
7566 Jones Manager 7839 02-APR-81 2975.0 20
7788 Scott Analyst 7566 19-APR-87 3000.0 20
7876 Adams Clerk 7788 23-MAY-87 1100.0 20
7902 Ford Analyst 7566 25-JAN-82 1300.0 20
SQL> select SUM(SAL) from EMPLY
where DEPTNO = 20 ;
SUM (SAL)
9175
SQL> select AVG(SAL) from EMPLY
where DEPTNO = 20;
AVG (SAL)
----------
1835
5. List employee name, hiredate and month in which they joined,
//First, Creating a view
SQL> create or replace view TEMP_MONTH as
select EMPNO, TO_CHAR (HIREDATE, 'MONTH') EMP_MONTH
from EMPLY;
View created.
SQL> select * from TEMP_MONTH
;
EMPNO EMP_MONTH
---------- -------------------
7521 FEBRUARY
7369 DECEMBER
7499 FEBRUARY
7566 APRIL
7654 SEPTEMBER
7696 MAY
7782 JUNE
7788 APRIL
7339 NOVEMBER
7344 SEPTEMBER
7876 MAY
7900 DECEMBER
7902 JANUARY
7934 JANUARY
14 rows selected.
//Firing main query
SQL> select E.EMPNO, HIREDATE, EMP_MONTH
from EMPLY E, TEMP_MONTH TM
where
E.EMPNO = TM.EMPNO
;
EMPNO HIREDATE EMP_MONTH
---------- -------------- -----------------
7521 22-FEB-81 FEBRUARY
7369 17-DEC-80 DECEMBER
7499 20-FEB-81 FEBRUARY
7566 02-APR-81 APRIL
7654 28-SEP-81 SEPTEMBER
7696 01-MAY-81 MAY
7782 09-JUN-81 JUNE
7788 19-APR-87 APRIL
7339 17-NOV-81 NOVEMBER
7344 08-SEP-81 SEPTEMBER
7876 23-MAY-87 MAY
7900 03-DEC-81 DECEMBER
7902 25-JAN-82 JANUARY
7934 25-JAN-82 JANUARY
14 rows selected.
6. List employee name ,hiredate and month in which they joined for all the employee who hired in the month “DECEMBER”
// We can find length of a field like this
SQL> select empno, length(emp_month) from temp_month;
EMPNO LENGTH(EMP_MONTH)
--------- ---------------------------------
7521 9
7369 9
7499 9
7566 9
7654 9
7696 9
7782 9
7788 9
7339 9
7344 9
7876 9
7900 9
7902 9
7934 9
14 rows selected.
// Here, % is used because there are extra spaces in the field EMP_MONTH…
SQL> select E.EMPNO, HIREDATE, EMP_MONTH
from EMPLY E, TEMP_MONTH TM
where
E.EMPNO = TM.EMPNO
and
TM.EMP_MONTH LIKE 'DECEMBER%' ;
EMPNO HIREDATE EMP_MONTH
--------- --------- --------------------------------------
7369 17-DEC-80 DECEMBER
7900 03-DEC-81 DECEMBER
// Here, SPACE is used because there are extra spaces in the field EMP_MONTH…
SQL> select E.EMPNO, HIREDATE, EMP_MONTH
from EMPLY E, TEMP_MONTH TM
where
E.EMPNO = TM.EMPNO
and
TM.EMP_MONTH = 'DECEMBER ' ;
EMPNO HIREDATE EMP_MONTH
--------- --------- --------------------------------------
7369 17-DEC-80 DECEMBER
7900 03-DEC-81 DECEMBER
//We can use trim function to truncate the spaces
SQL> select E.EMPNO, HIREDATE, EMP_MONTH
from EMPLY E, TEMP_MONTH TM
where
E.EMPNO = TM.EMPNO
and
TRIM(TM.EMP_MONTH) = 'DECEMBER' ;
EMPNO HIREDATE EMP_MONTH
--------- --------- --------------------------------------
7369 17-DEC-80 DECEMBER
7900 03-DEC-81 DECEMBER
7. List number of employee hired in year 80
SQL> select COUNT(*) from TEMP_DT
where EMP_DT = 80;
COUNT(*)
------------
1
8. List employee name whose name sounds like sound of ‘Milar’
SQL> select ENAME from EMPLY
where SOUNDEX ( ENAME ) = SOUNDEX ('Milar');
ENAME
----------
Miller
9. List number of employees working as a ‘MANAGER’
SQL> select ENAME, JOB from EMPLY
where JOB = 'Manager';
ENAME JOB
---------- ---------------
Jones Manager
Blake Manager
Clark Manager
10. List Employee name and designation (job) in the manner of right align
SQL> select LPAD(ENAME,20,' ') AS ENAME_RIGHT_ALIGN,
LPAD(JOB,15,' ') AS JOB_RIGHT_ALIGN
from EMPLY;
ENAME_RIGHT_ALIGN JOB_RIGHT_ALIGN
------------------------------ ----------------------------
Ward Salesman
Smit Clerk
Allen Salesman
Jones Manager
Martin Salesman
Blake Manager
Clark Manager
Scot Analyst
King President
Turner Salesman
Adams Clerk
James Clerk
Ford Analyst
Miller Clerk
QUERY LIST 2
1. List employee name and hiredate of all the employee who hired between 01 to 07 date of any month and any year (as they hired in a first week)
SQL> select ENAME, HIREDATE
from EMPLY E, TEMP_DAY
where
E.EMPNO = TEMP_DAY.EMPNO
AND
TEMP_DAY.EMP_DAY BETWEEN 1 AND 7
;
ENAME HIREDATE
---------- --------------
Jones 02-APR-81
Blake 01-MAY-81
James 03-DEC-81
2. List employee name , salary and Commission who are getting commission
SQL> select ENAME, SAL, COMM AS COMMISSION
2 from EMPLY
3 where NVL(COMM,0) != 0;
ENAME SAL COMMISSION
---------- ---------- ---------------------
Ward 1250.00 500
Allen 1600.00 300
Martin 1250.00 1400
3. List number of employee who are getting commission
SQL> create or replace view EMP_COMM AS
select ENAME, SAL, COMM
from EMPLY COMMISSION
where NVL(COMM,0) != 0;
View created.
SQL> select COUNT(*) from EMP_COMM;
COUNT(*)
---------------
3
4. List employee name, salary and Commission who are not getting commission
SQL> select ENAME, SAL, COMM AS COMMISSION
from EMPLY
where NVL(COMM,0) = 0;
ENAME SAL COMMISSION
---------- ---------- ----------
Smit 800.00
Jones 2975.00
Blake 2850.00
Clark 2450.00
Scot 3000.00
King 5000.00
Turner 1500.00 0
Adams 1100.00
James 950.00
Ford 1300.00
Miller 1300.00
11 rows selected.
5. List employee number , name and MGR who are reporting to employee number 7902
SQL> select EMPNO, ENAME, MGR from EMPLY
where MGR = 7902;
EMPNO ENAME MGR
---------- ---------- ---------
7369t 7902
6. List employee name, salary , commission and total salary paid to all the employee.(Total Salary = Sal + COMM)
SQL> select ENAME, SAL, COMM, SAL+NVL(COMM,0) AS TOTAL_SAL
from EMPLY;
ENAME SAL COMM TOTAL_SAL
---------- ------- ------- -------------------------
Ward 1250.0 500.0 1750
Smit 800.0 800
Allen 1600.0 300.0 1900
Jones 2975.0 2975
Martin 1250.0 1400.0 2650
Blake 2850.0 2850
Clark 2450.0 2450
Scot 3000.0 3000
King 5000.0 5000
Turner 1500.0 .0 1500
Adams 1100.0 1100
James 950.0 950
Ford 1300.0 1300
Miller 1300.0 1300
14 rows selected.
7. List employee name, salary and Bonus of all the employee (Bonus =10% of salary)
SQL> select ENAME, SAL, SAL*0.10 AS BONUS
from EMPLY;
ENAME SAL BONUS
---------- ------- ------------------
Ward 1250.0 125
Smit 800.0 80
Allen 1600.0 160
Jones 2975.0 297.5
Martin 1250.0 125
Blake 2850.0 285
Clark 2450.0 245
Scot 3000.0 300
King 5000.0 500
Turner 1500.0 150
Adams 1100.0 110
James 950.0 95
Ford 1300.0 130
Miller 1300.0 130
14 rows selected.
8. List details of employee who hired on ’03-dec-1981’
SQL> select * from EMPLY
2 where HIREDATE = TO_DATE('3-Dec-1981');
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- ---------------------------------------------------------------------
7900 es Clerk 7698 03-DEC-81 950.0 30
9. List all the employee name and length of name of each and every employee
SQL> select ENAME, length(ENAME) AS EMPNAME_LENGTH
from EMPLY;
ENAME EMPNAME_LENGTH
---------- --------------------------
Ward 4
Smit 4
Allen 5
Jones 5
Martin 6
Blake 5
Clark 5
Scot 4
King 4
Turner 6
Adams 5
James 5
Ford 4
Miller 6
14 rows selected.
10. Display highest salary from all the employees
SQL> select MAX(SAL) from EMPLY;
MAX(SAL)
-------------
5000
QUERY LIST 3
1. Display lowest salary paid to employee who are working as a ‘SALESMAN’
// here LOWEST_SALARY is an alias for the column MIN(SAL)
SQL> select MIN(SAL) AS LOWEST_SALARY
from EMPLY
where JOB = 'Salesman';
LOWEST_SALARY
-----------------------
1250
2. Display number of employee working as an ‘ANALYST’
SQL> select COUNT(*) as NO_OF_ANALYSTS
from EMPLY
where JOB = 'Analyst';
NO_OF_ANALYSTS
------------------------
2
3. List system date
SQL> select SYSDATE as SYSTEM_DATE from DUAL;
SYSTEM_DATE
-------------------
27-SEP-11
4. List employee name and hiredate who hired in a current month
SQL> select ENAME, HIREDATE
from EMPLY
where TO_CHAR(HIREDATE,'MON') = TO_CHAR(CURRENT_DATE,'MON') ;
ENAME HIREDATE
---------- -----------------
Martin 28-SEP-81
Turner 08-SEP-81
5. List all the employee name whose name composed of exactly four characters. Note(without using like operator)
SQL> select ENAME from EMPLY
where LENGTH(TRIM(ENAME)) = 4 ;
ENAME
----------
Ward
Smit
Scot
King
Ford
6. Display list of all the tables
SQL> select * from TAB;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
AVG1 TABLE
BIN$iKpRGTacTbebOOciYy6SXA==$0 TABLE
COUNTRIES TABLE
DEPARTMENTS TABLE
DEPT TABLE
EMP TABLE
EMPLOYEE TABLE
EMPLOYEES TABLE
EMPLY TABLE
EMP_DETAILS_VIEW VIEW
EMP_JOB_SKILLS VIEW
EMP_PROJECTS VIEW
JOB TABLE
JOBS TABLE
JOB_HISTORY TABLE
LOCATIONS TABLE
PROJECT TABLE
PROJ_EMP TABLE
REGIONS TABLE
SAL TABLE
TEMP TABLE
TEMP_DAY VIEW
TEMP_DT VIEW
TEMP_MONTH VIEW
WORK_INFO VIEW
25 rows selected.
7. List number of employee who hired in month ‘FEB’
SQL> select * from EMPLY
where TO_CHAR(HIREDATE,'MON') = 'FEB' ;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------------- ----- --------- ------- ------- ---------------------------
7521 Ward Salesman 7698 22-FEB-81 1250.0 500.0 30
7499 Allen Salesman 7698 20-FEB-81 1600.0 300.0 30
8. List employee name , salary, job and deptno who are working in department number 20 and getting salary more then 3000 or working as a ‘CLERK’
SQL> select ENAME, JOB, SAL, DEPTNO
from EMPLY
where
DEPTNO = 20
AND
SAL >= 3000
OR
JOB = 'Clerk'
;
ENAME JOB SAL DEPTNO
---------- --------------------------- ----------
Smit Clerk 800.0 20
Scot Analyst 3000.0 20
Adams Clerk 1100.0 20
James Clerk 950.0 30
Miller Clerk 1300.0 10
9. List name of the employee who is not reporting to any other employee
SQL> select ENAME as EMP_NOT_REPORTING, JOB
from EMPLY
where NVL(MGR,0) = 0;
EMP_NOT_REPORTING JOB
-------------------- ---------------------
King President
10. List name and salary of all the employees (Salary must composed of six digits in the manner of 006783
SQL> select ENAME, LPAD(SAL,6,0) as SALARY
from EMPLY ;
ENAME SALARY
---------- ---------------
Ward 001250
Smit 000800
Allen 001600
Jones 002975
Martin 001250
Blake 002850
Clark 002450
Scot 003000
King 005000
Turner 001500
Adams 001100
James 000950
Ford 001300
Miller 001300
14 rows selected.
QUERY LIST 4
Based on Group By and Having Clause :
1. List department wise total salary
SQL> select DEPTNO, SUM(SAL) TOTAL_SALARY
from EMPLY
GROUP BY DEPTNO ;
DEPTNO TOTAL_SALARY
----------- -------------------
30 9400
20 9175
10 8750
2. List number of employee working in each and every department , for only those department in which more than 3 employee are working
SQL> select DEPTNO, COUNT(*) AS EMPLOYEE_AMOUNT
from EMPLY
GROUP BY DEPTNO
HAVING COUNT(*) > 3;
DEPTNO EMPLOYEE_AMOUNT
---------- -----------------------------
30 6
20 5
3. Display designation wise lowest salary
SQL> select JOB, MIN(SAL) AS LOWEST_SALARY
from EMPLY
GROUP BY JOB ;
JOB LOWEST_SALARY
---------- ----------------------
Clerk 800
Manager 2450
Analyst 1300
President 5000
Salesman 1250
My Remainder: COUNT(*) & COUNT(1) does the same thing i.e. row counting, but COUNT(1) gives better performance.
4. Display number of employees hired in each and every month
SQL> select TO_CHAR(HIREDATE,'MONTH') AS MONTH, COUNT(*) AS NO_OF_EMPLOYEES
from EMPLY
GROUP BY TO_CHAR(HIREDATE, 'MONTH') ;
MONTH NO_OF_EMPLOYEES
----------- ---------------------------
FEBRUARY 2
JANUARY 2
APRIL 2
JUNE 1
NOVEMBER 1
DECEMBER 2
SEPTEMBER 2
MAY 2
8 rows selected.
5. Display month and number of employee hired in which month more than ONE employees are hired. Output must be in descending order of number of employees.
SQL> select TO_CHAR(HIREDATE,'MONTH') AS MONTH, COUNT(1) AS NO_OF_EMPLOYEES
from EMPLY
GROUP BY TO_CHAR(HIREDATE, 'MONTH')
HAVING COUNT(1) > 1
ORDER BY COUNT(1) DESC ;
MONTH NO_OF_EMPLOYEES
----------- ---------------------------
FEBRUARY 2
JANUARY 2
MAY 2
DECEMBER 2
SEPTEMBER 2
APRIL 2
6 rows selected.
6. List various values of salary which is paid to more than one employee
SQL> select SAL AS SALARY, COUNT(1) AS NO_OF_EMPLOYEES
from EMPLY
GROUP BY SAL
HAVING COUNT(1) > 1 ;
SALARY NO_OF_EMPLOYEES
---------- --------------------------
1300 2
1250 2
7. List employee number and number of employees reporting to each and every employees....
//HERE,
Three aliases of one table is made i.e. OE, E AND M for table EMPLY, one alias is R for the query which finds the managers and their no of reporting employees. Then (+) is used for taking those employees from table EMPLY which are not manager but they are employees of table EMPLY. That means, it also takes the employees which are not in table R but in table EMPLY.
SQL> select OE.EMPNO, OE.ENAME, NVL(R.REPORT_ME,0) AS REPORTING_EMPLYS
from
( select M.EMPNO, M.ENAME, COUNT(1) REPORT_ME
from EMPLY E, EMPLY M
where E.MGR = M.EMPNO
GROUP BY M.EMPNO, M.ENAME ) R,
EMPLY OE
where OE.EMPNO = R.EMPNO(+)
;
EMPNO ENAME REPORTING_EMPLYS
---------- ---------- ----------------------------
7902 Ford 1
7782 Clark 1
7566 Jones 2
7788 Scot 1
7344 Turner 0
7521 Ward 0
7339 King 0
7654 Martin 0
7499 Allen 0
7696 Blake 0
7934 Miller 0
7369 Smit 0
7876 Adams 0
7900 James 0
14 rows selected.
8. List department wise sum of total salary (Total salary =salary + commission
SQL> select DEPTNO, SUM(SAL+NVL(COMM,0)) AS TOTAL_DEP_WISE_SALARY
from EMPLY
GROUP BY DEPTNO ;
DEPTNO TOTAL_DEP_WISE_SALARY
---------- ----------------------------------
30 11600
20 9175
10 8750
9. List average salary of all the employee who are working as a ‘SALESMAN’
SQL> select AVG(SAL) as AVERAGE_SALARY_FOR_SALESMAN
from EMPLY
where JOB = 'Salesman' ;
AVERAGE_SALARY_FOR_SALESMAN
----------------------------------------------
1400
10. List employee number and name of employee to whom highest number of employees are reporting
SQL> CREATE OR REPLACE VIEW REPORT_MGR AS
select M.EMPNO, M.ENAME, COUNT(1) CNT_REPORT_EMP
from EMPLY E, EMPLY M
where E.MGR = M.EMPNO
GROUP BY M.EMPNO, M.ENAME ;
View created.
SQL> select * from REPORT_MGR;
EMPNO ENAME CNT_REPORT_EMP
---------- ---------- -----------------------
7902 Ford 1
7782 Clark 1
7566 Jones 2
7788 Scot 1
SQL> select R.EMPNO, R.ENAME, R.CNT_REPORT_EMP
from REPORT_MGR R
where R.CNT_REPORT_EMP =
( select MAX(CNT_REPORT_EMP) from REPORT_MGR)
;
EMPNO ENAME CNT_REPORT_EMP
---------- ---------- ------------------------
7566 Jones 2
QUERY LIST 5
1. Write a query which used to create a new table name backup_emp from existing table
SQL> CREATE TABLE BACKUP_EMPLY
AS
( SELECT * FROM EMPLY) ;
Table created.
2. Increase salary of all the employee to 1000 rupees who are working in department 10
SQL> update EMPLY
set SAL = SAL + 1000
where DEPTNO = 10;
3 rows updated.
3. Delete all the records who are working as a ‘ANALYST’
SQL>delete from EMPLY
where JOB = ‘Analyst’ ;
2 rows deleted.
4. Update salary to 10000 and commission to 5000 who are working as a ‘PRESIDENT’
SQL> update EMPLY
set SAL = 10000 , COMM = 5000
where JOB = 'President' ;
1 row updated.
5. Update commission of all the employees as 10% of their salary who are not getting commission
SQL> update EMPLY
set COMM = SAL*0.10
where NVL(COMM,0) = 0 ;
My Remainder: HERE, NVL() replaces the NULL value to 0.
10 rows updated.
6. Drop table name emply
SQL>drop table EMPLY;
Table dropped.
7. Create a table name EMP from an existing table name backup_emply
SQL> CREATE TABLE EMPLY
AS
( SELECT * FROM BACKUP_EMPLY) ;
Table created.
8. Query which display all the tables in current user
SQL> select * from TAB;
TNAME TABTYPE CLUSTERID
------------------------------------------------------------------------
VG1 TABLE
BACKUP_EMPLY TABLE
BIN$iKpRGTacTbebOOciYy6SXA==$0 TABLE
BIN$qASZcpwUQ9algubcwZRA0Q==$0 TABLE
CNT_MGR VIEW
COUNTRIES TABLE
DEPARTMENTS TABLE
DEPT TABLE
EMP TABLE
EMPLOYEE TABLE
EMPLOYEES TABLE
EMPLY TABLE
EMP_CNT_MGR VIEW
EMP_COMM VIEW
EMP_DETAILS_VIEW VIEW
EMP_JOB_SKILLS VIEW
EMP_PROJECTS VIEW
JOB TABLE
JOBS TABLE
JOB_HISTORY TABLE
LOCATIONS TABLE
PROJECT TABLE
PROJ_EMP TABLE
REGIONS TABLE
REPORT_MGR VIEW
SAL TABLE
TEMP TABLE
TEMP_DAY VIEW
TEMP_DT VIEW
TEMP_MONTH VIEW
WORK_INFO VIEW
31 rows selected.
