Loading
View RSS Feed

Angad Kumar Shukla's blog

Oracle SQL: How To Solve Complex SQL Queries?

Rating: 56 votes, 4.93 average.
After observing various books and posts in different sites, i wondered that without giving the database information and its schema information, how a beginner will understand the queries. So, i purposefully spent lot of time to create this Schema with values inserted into it. This will help the beginner's to understand queries and their operations in a proper way.
I believe learning the SQL practically with examples is the best way . I hope it would be helpful to you.
All the tables are properly populated with values for your better understanding.


This blog covers :

Joins in Oracle SQL

Inner queries in Oracle SQL

Sub Queries in Oracle SQL

DML Operations in Oracle SQL

DDL Operations in SQL

Right Approach to solve complex SQL Queries

Steps to Solve SQL Queries





If at any point of time, you feel you want to learn more of Oracle SQL, please see this link.

Learning Oracle SQL

Your precious replies and comments will be highly appreciated. Thanks.

[Note : If any queries or doubts, please post it in the comments in this blog]




The following tables represent data about employees, skills, projects, project teams.
The columns, their description and some sample data is provided. (If value is blank, treat them as null)

Main Tables


Employee_Portfolio
emp_id emp_name Designation join_date supervisor_id
Unique identifier Name of employee code that represents the designation. Refers to designation master (lookup) table Date of joining Employee id of supervisor
3251 Mark C2 12-1-2004
4562 Ryan C1 12-12-2000 3251
9812 Brian C1 12-12-2008 4562
CREATE:
Code:
SQL> create table employee_portfolio (emp_id varchar2(10),emp_name varchar2(30),designation varchar2
(20) ,join_date date,supervisor_id varchar2(20), foreign key(designation) references master_designat
ion_details(designation),unique(emp_id));
 
Table created.
INSERT
Code:
SQL> insert into employee_portfolio values ('&emp_id','&emp_name','&designation',to_date('&join_date
','dd-mm-yyyy'),'&supervisor_id');

Enter value for emp_id: 3251
Enter value for emp_name: Mark
Enter value for designation: C2
Enter value for join_date: 12-1-2004
Enter value for supervisor_id:
old   1: insert into employee_portfolio values ('&emp_id','&emp_name','&designation',to_date('&join_
new   1: insert into employee_portfolio values ('3251','Mark','C2',to_date('12-1-2004','dd-mm-yyyy')
 
1 row created.
 
SQL> /
Enter value for emp_id: 4562
Enter value for emp_name: Ryan
Enter value for designation: C1
Enter value for join_date: 12-12-2000
Enter value for supervisor_id: 3251
old   1: insert into employee_portfolio values ('&emp_id','&emp_name','&designation',to_date('&join_
new   1: insert into employee_portfolio values ('4562','Ryan','C1',to_date('12-12-2000','dd-mm-yyyy'
 
1 row created.
 
SQL> /
Enter value for emp_id: 9812
Enter value for emp_name: Brian
Enter value for designation: C1
Enter value for join_date: 12-12-2008
Enter value for supervisor_id: 4562
old   1: insert into employee_portfolio values ('&emp_id','&emp_name','&designation',to_date('&join_
new   1: insert into employee_portfolio values ('9812','Brian','C1',to_date('12-12-2008','dd-mm-yyyy
 
1 row created.
SELECT
Code:
SQL> select * from employee_portfolio;

EMP_ID     EMP_NAME                       DESIGNATION          JOIN_DATE SUPERVISOR_ID
---------- ------------------------------ -------------------- --------- --------------------
3251       Mark                           C2                   12-JAN-04
4562       Ryan                           C1                   12-DEC-00 3251
9812       Brian                          C1                   12-DEC-08 4562

Employee_Skill
emp_id skill_id date_effective prof_level exp_years
Unique identifier of employee whose skills and proficiency is recorded. Refers to employee master table. Unique identifier of skill for which employee’s proficiency level is recorded. Refers to skill master (lookup) table Date when employee got the skill Proficiency level of given employee for given skill. Takes values E0 thru E4 No. of years of experience the employee has on the given skill
3251 C 12-12-2006 E1 3
3251 JAV 12-12-2004 E2 4
4562 DES 12-12-2008 E0 0

CREATE
Code:
SQL> create table employee_skill (emp_id varchar2(20),skill_id varchar2(30),date_effective date,prof
_level varchar2(20),exp_years varchar2(20),foreign key(emp_id) references employee_portfolio(emp_id));
 
Table created.
INSERT
Code:
SQL> insert into employee_skill values ('&emp_id','&skill_id',to_date('&date_effective','dd-mm-yyyy'),'&prof_level','&exp_years');
Enter value for emp_id: 3251
Enter value for skill_id: C
Enter value for date_effective: 12-12-2006
Enter value for prof_level: E1
Enter value for exp_years: 3
old   1: insert into employee_skill values ('&emp_id','&skill_id',to_date('&date_effective','dd-mm-y
new   1: insert into employee_skill values ('3251','C',to_date('12-12-2006','dd-mm-yyyy'),'E1','3')
 
1 row created.
 
SQL> /
Enter value for emp_id: 3251
Enter value for skill_id: JAV
Enter value for date_effective: 12-12-2004
Enter value for prof_level: E2
Enter value for exp_years: 4
old   1: insert into employee_skill values ('&emp_id','&skill_id',to_date('&date_effective','dd-mm-y
new   1: insert into employee_skill values ('3251','JAV',to_date('12-12-2004','dd-mm-yyyy'),'E2','4'
 
1 row created.
 
SQL> /
Enter value for emp_id: 4562
Enter value for skill_id: DES
Enter value for date_effective: 12-12-2008
Enter value for prof_level: E0
Enter value for exp_years: 0
old   1: insert into employee_skill values ('&emp_id','&skill_id',to_date('&date_effective','dd-mm-y
new   1: insert into employee_skill values ('4562','DES',to_date('12-12-2008','dd-mm-yyyy'),'E0','0'
 
1 row created.
SELECT
Code:
SQL> select * from employee_skill;

EMP_ID               SKILL_ID                       DATE_EFFE PROF_LEVEL           EXP_YEARS
-------------------- ------------------------------ --------- -------------------- -----------------
3251                 C                              12-DEC-06 E1                   3
3251                 JAV                            12-DEC-04 E2                   4
4562                 DES                            12-DEC-08 E0                   0

Employee_Project
project_id date_start date_end Name
Unique identifier of project whose team is enlisted in the table Date when project started Date when project ended / ends Name of project
2056 1-1-2008 Google Testing
2078 1-1-2007 12-12-2007 Yahoo Development

CREATE
Code:
SQL> create table employee_project(project_id varchar2(20),date_start date,date_end date,Name varchar2(30));
 
Table created.
INSERT
Code:
SQL> insert into employee_project values('&project_id',to_date('&date_start','dd-mm-yyyy'),to_date('
&date_end','dd-mm-yyyy'),'&Name');
Enter value for project_id: 2056
Enter value for date_start: 1-1-2008
Enter value for date_end:
Enter value for name: Google Testing
old   1: insert into employee_project values('&project_id',to_date('&date_start','dd-mm-yyyy'),to_da
new   1: insert into employee_project values('2056',to_date('1-1-2008','dd-mm-yyyy'),to_date('','dd-
 
1 row created.
 
SQL> /
Enter value for project_id: 2078
Enter value for date_start: 1-1-2007
Enter value for date_end: 12-12-2007
Enter value for name: Yahoo Development
old   1: insert into employee_project values('&project_id',to_date('&date_start','dd-mm-yyyy'),to_da
new   1: insert into employee_project values('2078',to_date('1-1-2007','dd-mm-yyyy'),to_date('12-12-
 
1 row created.
SELECT
Code:
SQL> select * from employee_project;

PROJECT_ID           DATE_STAR DATE_END  NAME
-------------------- --------- --------- ------------------------------
2056                 01-JAN-08           Google Testing
2078                 01-JAN-07 12-DEC-07 Yahoo Development
Employee_Project_Team
emp_id project_id date_start date_end role_id
Unique identifier of employee worked /works in the project. Refers to employee table Unique identifier of project whose team is enlisted in the table. Refers to Projects table Date when employee got into project in the given role Date when employee was released from project if any Role played by employee in project
3251 2056 1-1-2008 11-12-2008 DES
3251 2056 12-12-2008 MGR
4562 2056 12-12-2008 DEV
3251 2078 1-1-2007 12-12-2007 DES

CREATE
Code:
SQL> create table Employee_project_team(emp_id varchar2(20),project_id varchar2(30),date_start date,
date_end date,role_id varchar2(20),foreign key(emp_id) references Employee_portfolio(emp_id),foreign
 key (project_id) references employee_project(project_id));
 
Table created.
INSERT
Code:
SQL> insert into employee_project_team values('&emp_id','&project_id',to_date('&date_start','dd-mm-y
yyy'),to_date('&date_end','dd-mm-yyyy'),'&role_id');
Enter value for emp_id: 3251
Enter value for project_id: 2056
Enter value for date_start: 1-1-2008
Enter value for date_end: 11-12-2008
Enter value for role_id: DES
old   1: insert into employee_project_team values('&emp_id','&project_id',to_date('&date_start','dd-
new   1: insert into employee_project_team values('3251','2056',to_date('1-1-2008','dd-mm-yyyy'),to_
 
1 row created.

SQL> /
Enter value for emp_id: 3251
Enter value for project_id: 2056
Enter value for date_start: 12-12-2008
Enter value for date_end:
Enter value for role_id: MGR
old   1: insert into employee_project_team values('&emp_id','&project_id',to_date('&date_start','dd-
new   1: insert into employee_project_team values('3251','2056',to_date('12-12-2008','dd-mm-yyyy'),t
 
1 row created.
 
SQL> /
Enter value for emp_id: 4562
Enter value for project_id: 2056
Enter value for date_start: 12-12-2008
Enter value for date_end:
Enter value for role_id: DEV
old   1: insert into employee_project_team values('&emp_id','&project_id',to_date('&date_start','dd-
new   1: insert into employee_project_team values('4562','2056',to_date('12-12-2008','dd-mm-yyyy'),t
 
1 row created.
 
SQL> /
Enter value for emp_id: 3251
Enter value for project_id: 2078
Enter value for date_start: 1-1-2007
Enter value for date_end: 12-12-2007
Enter value for role_id: DES
old   1: insert into employee_project_team values('&emp_id','&project_id',to_date('&date_start','dd-
new   1: insert into employee_project_team values('3251','2078',to_date('1-1-2007','dd-mm-yyyy'),to_
 
1 row created.
SELECT
Code:
SQL> select * from employee_project_team;

EMP_ID               PROJECT_ID                     DATE_STAR DATE_END  ROLE_ID
-------------------- ------------------------------ --------- --------- --------------------
3251                 2056                           01-JAN-08 11-DEC-08 DES
3251                 2056                           12-DEC-08           MGR
4562                 2056                           12-DEC-08           DEV
3251                 2078                           01-JAN-07 12-DEC-07 DES
Master Tables

Master_Skill
skill_id Name
C C Programming
JAV Java Programming
DES Software Design
TEST Software Testing
OOP Object Oriented Programming
CREATE

Code:
SQL> create table master_skill(skill_id varchar2(20),skill_name varchar2(30),primary key(skill_id));
Table created.
INSERT
Code:
SQL> insert into master_skill values('&skill_id','&skill_name');
Enter value for skill_id: C
Enter value for skill_name: C Programming
old   1: insert into master_skill values('&skill_id','&skill_name')
new   1: insert into master_skill values('C','C Programming')
 
1 row created.
 
SQL> /
Enter value for skill_id: JAV
Enter value for skill_name: Java Programming
old   1: insert into master_skill values('&skill_id','&skill_name')
new   1: insert into master_skill values('JAV','Java Programming')
 
1 row created.
 
SQL> /
Enter value for skill_id: DES
Enter value for skill_name: Software Design
old   1: insert into master_skill values('&skill_id','&skill_name')
new   1: insert into master_skill values('DES','Software Design')
 
1 row created.
 
SQL> /
Enter value for skill_id: Test
Enter value for skill_name: Software Testing
old   1: insert into master_skill values('&skill_id','&skill_name')
new   1: insert into master_skill values('Test','Software Testing')
 
1 row created.
 
SQL> /
Enter value for skill_id: OOP
Enter value for skill_name: Object Oriented Programming
old   1: insert into master_skill values('&skill_id','&skill_name')
new   1: insert into master_skill values('OOP','Object Oriented Programming')
 
1 row created.
SELECT
Code:
SQL> select * from master_skill;

SKILL_ID             SKILL_NAME
-------------------- ------------------------------
C                    C Programming
JAV                  Java Programming
DES                  Software Design
Test                 Software Testing
OOP                  Object Oriented Programming
Master_Designation_details
Designation Name
C1 Engineer
C2 Analyst
C3 Consultant
C4 Director
VP Vice President


CREATE
Code:
SQL> create table master_designation_details(designation varchar2(20),designation_name varchar2(30),
primary key(designation));
 
Table created.
INSERT
Code:
SQL> insert into master_designation_details values('&designation','&designation_name');
Enter value for designation: C1
Enter value for designation_name: Engineer
old   1: insert into master_designation_details values('&designation','&designation_name')
new   1: insert into master_designation_details values('C1','Engineer')
 
1 row created.
 
SQL> /
Enter value for designation: C2
Enter value for designation_name: Analyst
old   1: insert into master_designation_details values('&designation','&designation_name')
new   1: insert into master_designation_details values('C2','Analyst')
 
1 row created.
 
SQL> /
Enter value for designation: C3
Enter value for designation_name: Consultant
old   1: insert into master_designation_details values('&designation','&designation_name')
new   1: insert into master_designation_details values('C3','Consultant')
 
1 row created.
 
SQL> /
Enter value for designation: C4
Enter value for designation_name: Director
old   1: insert into master_designation_details values('&designation','&designation_name')
new   1: insert into master_designation_details values('C4','Director')
 
1 row created.
 
SQL> /
Enter value for designation: VP
Enter value for designation_name: Vice President
old   1: insert into master_designation_details values('&designation','&designation_name')
new   1: insert into master_designation_details values('VP','Vice President')
 
1 row created.

SELECT
Code:
SQL> select * from master_designation_details;

DESIGNATION          DESIGNATION_NAME
-------------------- ------------------------------
C1                   Engineer
C2                   Analyst
C3                   Consultant
C4                   Director
VP                   Vice President
Master_Role
role_id Name
DES Designer
MGR Manager
DEV Developer
SC Site Coordinator
TST Tester

CREATE
Code:
SQL> create table master_role(role_id varchar2(20),role_name varchar2(30),primary key(role_id));
 
Table created.
INSERT
Code:
SQL> insert into master_role values('&role_id','&role_name');
Enter value for role_id: DES
Enter value for role_name: Designer
old   1: insert into master_role values('&role_id','&role_name')
new   1: insert into master_role values('DES','Designer')
 
1 row created.
 
SQL> /
Enter value for role_id: MGR
Enter value for role_name: Manager
old   1: insert into master_role values('&role_id','&role_name')
new   1: insert into master_role values('MGR','Manager')
 
1 row created.
 
SQL> /
Enter value for role_id: DEV
Enter value for role_name: Developer
old   1: insert into master_role values('&role_id','&role_name')
new   1: insert into master_role values('DEV','Developer')
 
1 row created.
 
SQL> /
Enter value for role_id: SC
Enter value for role_name: Site Coordinator
old   1: insert into master_role values('&role_id','&role_name')
new   1: insert into master_role values('SC','Site Coordinator')
 
1 row created.
 
SQL> /
Enter value for role_id: TST
Enter value for role_name: Tester
old   1: insert into master_role values('&role_id','&role_name')
new   1: insert into master_role values('TST','Tester')
 
1 row created.
SELECT
Code:
SQL> select * from master_role;

ROLE_ID              ROLE_NAME
-------------------- ------------------------------
DES                  Designer
MGR                  Manager
DEV                  Developer
SC                   Site Coordinator
TST                  Tester

Answer the following

  • For all questions – following standard procedure starting from defining the problem to showing sample output / dry run.
  • Do not forget to explicitly articulate any assumptions you make
  • Add sufficient sample data to the above data (do not remove any existing data) – so that you can demonstrate good output for all the following SQL and DML that you will design.
  • Inputs are specifically left as generic with examples so that youmay demonstrate results for multiple inputs where needed



1. Find the names of all employees who are currently in projects and are whose designation is as given (say ‘C1’).

STEP 1: Problem Definiton:
o Context
  • a. Employee_portfolio table has employee details – name and designation
  • b. Employee_Project_Team has details of all employees who were / are working on a given project
  • c. If date_end is null, then the project is the currently active project for the given employee

o Input – A designation code
o Output – List of names of employees
  • a. With the given designation
  • b. Who are currently active in at least one project


o Special considerations
  • a. Same employee is active on multiple projects?
  • b. Multiple employees match the requirement

  • c. No employees match the requirement

o Sample scenario - Input ‘C1’ => Output ‘Ryan’


STEP 2: Solution Approach:
• Find employees who are currently active:
  • o Criteria – end_date is null in employee_project_team table
  • o Output required for further matching – emp_id
  • o Intermediate solution:
  •  SELECT emp_id from employee_ project_team
  •  where date_end is null


• Get employee name based on previously got ids
  • o Criteria – designation is as given in input on the employee table
  • o Addl. Criteria – emp_id in output of previous query
  • o Output required: emp_name
  • o Intermediate solution:
  •  select emp_name from employee_portfolio where designation = ?
  •  and emp_id in ?previous_output?



NOTE: Approach can be reversed to filter first by designation as well
STEP 3: Sample Test Cases

1. Input ‘C2’ => Output ‘Mark’
2. Input ‘C1’ => Output ‘Ryan’
3. Input ‘VP’ => Output None

NOTE
Missing test case: multiple output scenario
Need to add data to demonstrate multiple records
This step can also be interchanged with step 2
STEP 4: Dry Run

1. Intermediate result of query irrespective of input is 3251, 4562
2. Mapped names are: Mark, Ryan
3. If input is
  • a. C1 – further filter on employee returns Ryan
  • b. C2 – further filter on employee returns Mark
  • c. VP - further filter on employee returns no result



NOTE: Dry run will vary if approach is reversed
STEP 5: Translation Approach

The translation approach is depicted as sub-bullets (Intermediate Solution) in STEP 2: Solution approach.

NOTE:
Though intermediate solution is inserted above – it is expected that the part is filled only at this point of time. Insertion is done above just to show mapping between these steps
Also note that intermediate solution testing is skipped as this is a solution for a paper test.
STEP 6: Final Query

Code:
SQL> select emp_name from employee_portfolio where designation = 'C1'and emp_id in (SELECT emp_id from employee_project_team where date_end is null);

EMP_NAME
------------------------------
Ryan
OR

Code:
SQL> select distinct emp_name from employee_portfolio e inner join employee_project_team pe  on pe.e
mp_id = e.emp_id where designation = 'C1' and date_end is null;

EMP_NAME
------------------------------
Ryan
OR

Code:
SQL> select emp_name from employee_portfolio e inner join  (SELECT emp_id, date_end from employee_pr
oject_team where date_end is null) pe on pe.emp_id = e.emp_id where designation = 'C1' and date_end 
is null;

EMP_NAME
------------------------------
Ryan


2. For each project, find the number of employees who have a particular skill (say ‘JAV’).


STEP 1: Problem Definiton:
o Context
  • a. Employee_Skills table has details about skills of a given employee
  • b. Project_Team table has details of all employees who were / are working on a given project
  • c. Project table has name of project


o Input – A skill id
o Output – List of names of projects and corresponding number of employees
  • a. with the given skill


o Special considerations
  • a. The project may have no team at all
  • b. None of the team may have the skill
  • c. Multiple employees have skills in the same project
  • d. The same employee may have been on multiple roles in a given project - - in this case he should not be double counted
  • e. The same employee may have worked on multiple projects – in this case double counting is fine.


o Sample scenario - Input ‘JAV’ => Output
  • a. Google Testing 1
  • b. Yahoo Development 1


o Sample scenario - Input ‘DES’ => Output
  • a. Google Testing 1
  • b. Yahoo Development 0


o

STEP 2: Final Query

Code:
SQL> SELECT p.name, count(distinct pe.emp_id) No_of_Employee from employee_project p left outer join
 employee_project_team pe on p.project_id = pe.project_id left outer join employee_skill es  on es.e
mp_id = pe.emp_id where skill_id = 'JAV' or skill_id is null group by p.name
  2  /

NAME                           NO_OF_EMPLOYEE
------------------------------ --------------
Google Testing                              1
Yahoo Development                           1
OR

Code:
SQL> SELECT p.name, count(emp_id) No_of_Employee from employee_project p left outer join  (SELECT di
stinct project_id, emp_id from employee_project_team where emp_id in (select emp_id from employee_sk
ill where skill_id = 'JAV')) pe on p.project_id = pe.project_id group by p.name;

NAME                           NO_OF_EMPLOYEE
------------------------------ --------------
Google Testing                              1
Yahoo Development                           1
OR
Code:
SQL> SELECT p.name, count(pe.emp_id) No_of_Employee from employee_project p left outer join  (SELECT
 distinct project_id, pt.emp_id from employee_project_team pt  INNER JOIN employee_skill es ON es.em
p_id = pt.emp_id where skill_id = 'JAV') pe on p.project_id = pe.project_id group by p.name;

NAME                           NO_OF_EMPLOYEE
------------------------------ --------------
Google Testing                              1
Yahoo Development                           1



Typical Mistakes
o If count(*) is done instead then all places where 0 is expected will return 1
o If outer join not done some records will be skipped
o While doing outer join if OR skill_id is null check is not added some records will be skipped
o If group by not done correctly or if additional fields selected etc, query will not execute
o … and many more





3. Find all people (name and total experience in given skill) who have a particular skill at a particular proficiency (say ‘JAV’ – ‘E2’) who are not currently in any project.


Code:
SQL> SELECT emp_name, exp_years from employee_portfolio e inner join employee_skill es on e.emp_id =
 es.emp_id where skill_id = 'JAV' and prof_level = 'E2' and NOT EXISTS (SELECT 1 FROM employee_proje
ct_team pt where pt.emp_id = e.emp_id and date_end is null);

no rows selected
OR

Code:
SQL> SELECT emp_name, exp_years from employee_portfolio e inner join employee_skill es on e.emp_id =
 es.emp_id where skill_id = 'JAV' and prof_level = 'E2' and e.emp_id not in (SELECT emp_id from empl
oyee_project_team where date_end is null);
 

no rows selected

4. Find all people (name of employee, designation name, project name and total experience in given skill) who have a particular skill at a particular proficiency (say ‘JAV’ – ‘E2’);

Code:
SQL> SELECT emp_name, exp_years, d.designation_name, p.name project_name from employee_portfolio e i
nner join employee_skill es on e.emp_id = es.emp_id left outer join master_designation_details d on 
d.designation = e.designation left outer join employee_project_team pt on pt.emp_id = e.emp_id  left
 outer join employee_project p on pt.project_id = p.project_id where skill_id = 'JAV' and prof_level
 = 'E2' and pt.date_end is null;

EMP_NAME                       EXP_YEARS            DESIGNATION_NAME               PROJECT_NAME
------------------------------ -------------------- ------------------------------ -----------------
Mark                           4                    Analyst                        Google Testing

Typical Mistakes
o If date_end criteria is not used the same employee will appear multiple times for each of the past projects (A valid assumption is that there is a max of one current project in which employee works)
o If inner join done on project_team or project, then people not in any projects will not be listed
o While it may be okay to assume that there are matching designation rows and hence inner join is also okay – inner join on projects is not acceptable


5. Find all employees who report to an employee (say ‘Ryan’) who are not currently in any project

Code:
SQL> select e.emp_name from employee_portfolio e  inner join employee_portfolio sup on e.supervisor_
id = sup.emp_id where sup.emp_name = 'Ryan' and e.emp_id not in (SELECT emp_id from employee_project
_team where date_end is null);

EMP_NAME
------------------------------
Brian

6. Find the employees (name, designation, current project name and role name if any) who are second level reportees of a given employee (say ‘Mark’)

Code:
SQL> select e.emp_name, d.designation_name, p.name project_name, r.role_name  from employee_portfoli
o e inner join employee_portfolio sup on e.supervisor_id = sup.emp_id inner join employee_portfolio 
sup2 on sup.supervisor_id = sup2.emp_id left outer join master_designation_details d on d.designatio
n = e.designation left outer join employee_project_team pt on pt.emp_id = e.emp_id and pt.date_end i
s null left outer join employee_project p on pt.project_id = p.project_id left outer join master_rol
e r on pt.role_id = r.role_id where sup2.emp_name = 'Mark';

EMP_NAME                       DESIGNATION_NAME               PROJECT_NAME                   ROLE_NAME
------------------------------ ------------------------------ ------------------------------ -------
Brian                          Engineer
7. Create report with the following details of all employees (employee id, name, designation, supervisor id, supervisor name, supervisor designation) List them by designations (higher levels first) and within that all names should be alphabetical.

Code:
SQL> select e.emp_id, e.emp_name, e.designation, sup.emp_id supervisor_id, sup.emp_name supervisor_n
ame, sup.designation supervisor_designation from employee_portfolio e left outer join employee_portf
olio sup  on e.supervisor_id = sup.emp_id order by e.designation desc, e.emp_name;

EMP_ID     EMP_NAME                       DESIGNATION          SUPERVISOR SUPERVISOR_NAME                SUPERVISOR_DESIGNATI
---------- ------------------------------ -------------------- ---------- --------------------------
3251       Mark                           C2
9812       Brian                          C1                   4562       Ryan                           C1
4562       Ryan                           C1                   3251       Mark                           C2

8. Provide complete details of all current and previous projects of all employees reporting to a given employee (say ‘Mark’)

Code:
SQL> select e.*, p.*,  pt.* from employee_portfolio e inner join employee_portfolio sup  on e.superv
isor_id = sup.emp_id left outer join employee_project_team pt on pt.emp_id = e.emp_id left outer joi
n employee_project p on pt.project_id = p.project_id where sup.emp_name = 'Mark';

EMP_ID     EMP_NAME                       DESIGNATION          JOIN_DATE SUPERVISOR_ID        PROJECT_ID           DATE_STAR DATE
---------- ------------------------------ -------------------- --------- -------------------- ------
4562       Ryan                           C1                   12-DEC-00 3251                 2056                 01-JAN-08           Google Testing



9. An employee (say ‘Mark’) is handing over charge to a new employee (say ‘Terry’). Write the necessary DML to add the new employee details and change the hierarchy of all reportees of the original employee to the new employee.

INSERT:
Code:
SQL> insert into employee_portfolio values('1234','Terry','C2','12-MAY-2004','');

1 row created.
UPDATE:
Code:
SQL> UPDATE employee_portfolio set supervisor_id='1234' where supervisor_id='3251';

1 row updated.

10. Check for all projects that have been ended in the past (end_date not null) and update the project role end date of all employees who are active (end_date null) to the actual end date of the project

Code:
SQL> update employee_project_team set date_end = (select date_end from employee_project where date_e
nd is not null) where project_id =(select project_id from employee_project where date_end is not nul
l) and date_end is null;

0 rows updated.

11. Find the employee who has the maximum number of direct reportees.


Code:
SQL> select s.emp_id, s.emp_name  from employee_portfolio s inner join employee_portfolio e on e.sup
ervisor_id = s.emp_id group by s.emp_id, s.emp_name having count(*) = ( select max(cnt) from  (selec
t count(*) cnt from employee_portfolio  where supervisor_id is not null group by supervisor_id) emp_
cnt );

EMP_ID     EMP_NAME
---------- ------------------------------
1234       Terry
4562       Ryan


12. Find the employees who have worked on the same project in the most number of roles.

Code:
SQL> select distinct e.emp_id, emp_name from employee_project_team pt inner join employee_portfolio 
 e on e.emp_id = pt.emp_id group by e.emp_id, emp_name, project_id HAVING count(distinct role_id) = 
(SELECT max(num_roles) max_roles FROM (SELECT emp_id, project_id, count(distinct role_id) num_roles 
from employee_project_team  group by emp_id, project_id) emp_roles);

EMP_ID     EMP_NAME
---------- ------------------------------
3251       Mark

Submit "Oracle SQL: How To Solve Complex SQL Queries?" to Digg Submit "Oracle SQL: How To Solve Complex SQL Queries?" to del.icio.us Submit "Oracle SQL: How To Solve Complex SQL Queries?" to StumbleUpon Submit "Oracle SQL: How To Solve Complex SQL Queries?" to Google

Comments

  1. Harsh's Avatar
    Wonderful
  2. yusuf keil's Avatar
    The positive aspect of this blog is the clear examples with simple queries for the complex demands. Although i feel the query could be bit shorter in some.
    But truly, its worth reading and to understand every bit of it. Thanks Angad.
  3. gnawali.shyam's Avatar
    select chooses column but i need rows. is there any way with sql?
  4. Harsh's Avatar
    What do you mean by you need rows? I think you are talking about concept like Cursor or Record composite variable which can deal with table rows as a whole.
  5. Unregistered's Avatar
    This is a nice way to introduce SQL or give practice to beginning users. It seems you took a fair deal of time to create it. I do have a couple of suggestions.

    First:

    Some of the select statements are nightmarish to read on this page. Perhaps, you might format them for those you wish to teach. It could also be an issue that html does not recognize the formatting, but not likely with multiple column names per line. The best example I saw is below. I did not test but assume it executes as expected, since you posted for all to see. Simply looking at the query, I am unable to discern anything. (rhetorical questions) Which tables are queried, how are they joined? It would take a more skilled person to quickly identify answers to those questions, and it may turn beginners away.

    SQL> select e.emp_name, d.designation_name, p.name project_name, r.role_name from employee_portfoli
    o e inner join employee_portfolio sup on e.supervisor_id = sup.emp_id inner join employee_portfolio
    sup2 on sup.supervisor_id = sup2.emp_id left outer join master_designation_details d on d.designatio
    n = e.designation left outer join employee_project_team pt on pt.emp_id = e.emp_id and pt.date_end i
    s null left outer join employee_project p on pt.project_id = p.project_id left outer join master_rol
    e r on pt.role_id = r.role_id where sup2.emp_name = 'Mark';

    Since this was a lesson on learning to query a dataset and not on designing the dataset, I may be too picky here :) The Employee_Skill table has a column exp_years. The values inserted for that column are not reflective of the dates when employees gained those skills. I will assume those are meant to be calculated fields, and thus should not be stored in the table. They should rather be queried. (rhetorical questions) Is there a stored procedure that runs every day to update that column or is a manager supposed to edit them on review? Selecting a calculation based on effective date could also prove a benefit to learning more advanced querying. Something like ROUND((SYSDATE - date_effective) / 365, 2) AS YEARS_OF_EXPERIENCE.



Disclaimer: Users of techforum4u.com are responsible for ensuring that any material they post (article, blog posts, images or other mulitimedia content) does not violate or infringe upon the copyright, patent, trademark, or any personal or proprietary rights of any third party, and is posted with the permission of the owner of such rights.Anyone who violates these rules may have their access privileges removed without warning.