SQL Query - Intermediate

Overview:

We saw in the previous blog about the basic SQL queries and in this section I'll cover the important query concept called Joins.  In real life scenario,most of the time we'll pull data from various source by joining the matching conditions.

There are 6 different types of Joins in Oracle (from 10G onwards) and they are

1. Equi and Non-Equi joins
2. Inner Join
3. Outer joins (This consists of 3 different joins)
1. LEFT 
2. RIGHT 
3. FULL
4. Cross joins or Cartesian Product 
5. Self joins
6. Partition outer joins

1) Equi and Non-Equi joins

An equi-join is a join where the join condition uses the equal to (=) operator to match the rows from two  ore more tables. When a join condition uses any other operator other than '=' to relate the rows of two or more tables, the join is called a non-equi-join.

--Bringing data from two tables using Equi-Join
SELECT
  EMPLOYEE_ID,
  FIRST_NAME,
  LAST_NAME,
  SALARY,
  MANAGER_ID,
  DEPARTMENT_ID,
  DEPARTMENT_NAME
FROM
  EMPLOYEES,   DEPARTMENTS
WHERE
  EMPLOYEES.DEPARTMENT_ID=DEPARTMENTS.DEPARTMENT_ID

RESULT: Error
This statment will throw the following error  :ORA-00918: column ambiguously defined

REASON:
DEPARTMENT_ID in the select column doesn't know which table we are referring to as the department id exist in both the tables.


SOLUTION:
in this situation as well as its a best practice to specify the table name in front of the column name like the example below:

SELECT
  EMPLOYEES.EMPLOYEE_ID,
  EMPLOYEES.FIRST_NAME,
  EMPLOYEES.LAST_NAME,
  EMPLOYEES.SALARY,
  EMPLOYEES.MANAGER_ID,
  EMPLOYEES.DEPARTMENT_ID,
  DEPARTMENTS.DEPARTMENT_NAME
FROM
  EMPLOYEES,   DEPARTMENTS
WHERE
  EMPLOYEES.DEPARTMENT_ID=DEPARTMENTS.DEPARTMENT_ID

RESULT:  Successful
All matching records are displayed.

ALTERNATIVE:
The statement above can also be return by supplying an alias name to the table, this way you don't need to type the full table name

SELECT
  E.EMPLOYEE_ID,
  E.FIRST_NAME,
  E.LAST_NAME,
  E.SALARY,
  E.MANAGER_ID,
  E.DEPARTMENT_ID,
  D.DEPARTMENT_NAME
FROM
  EMPLOYEES E,   DEPARTMENTS D
WHERE
  E.DEPARTMENT_ID=D.DEPARTMENT_ID


--Adding additional queries to the join
SELECT
  E.EMPLOYEE_ID,
  E.FIRST_NAME,
  E.LAST_NAME,
  E.SALARY,
  E.MANAGER_ID,
  E.DEPARTMENT_ID,
  D.DEPARTMENT_NAME
FROM
  EMPLOYEES E,   DEPARTMENTS D
WHERE
  E.DEPARTMENT_ID=D.DEPARTMENT_ID
  AND E.SALARY>=8000
ORDER BY E.FIRST_NAME, E.SALARY


2) Inner joins 
An inner join returns the rows that satisfy the join condition.Each row returned by an inner join contains data from all the tables in the join that matches the criteria.

The difference is how Oracle created execution plan.  Inner Joins are consider faster than Equi Join.

SELECT
  E.EMPLOYEE_ID,
  E.FIRST_NAME,
  E.LAST_NAME,
  E.SALARY,
  E.MANAGER_ID,
  E.DEPARTMENT_ID,
  D.DEPARTMENT_NAME
FROM
  EMPLOYEES E
INNER JOIN DEPARTMENTS D ON D.DEPARTMENT_ID=E.DEPARTMENT_ID
  AND E.SALARY>=8000
ORDER BY E.FIRST_NAME, E.SALARY



SELECT 
  D.DEPARTMENT_NAME,
  L.CITY,
  L.STATE_PROVINCE,
  L.COUNTRY_ID
FROM DEPARTMENTS D INNER JOIN LOCATIONS L
ON D.LOCATION_ID = L.LOCATION_ID;


3) Outer joins
An outer join returns the rows that satisfy the join condition and also the rows from one table for which no corresponding rows exist in the other table.


a) LEFT Outer Join

Specifies that the results be generated using all rows from DEPARTMENT. For those rows in DEPARTMENT that don't have corresponding rows in LOCATION table  NULL's are returned in the result set for the LOCATIONS table's columns.

SELECT 
  D.DEPARTMENT_NAME,
  L.CITY,
  L.STATE_PROVINCE,
  L.COUNTRY_ID
FROM DEPARTMENTS D LEFT OUTER JOIN LOCATIONS L
ON D.LOCATION_ID = L.LOCATION_ID;

RESULT:
Returns all matching 27 records

b) RIGHT Outer Join
Specifies that the results be generated using all rows from LOCATIONS table. For those rows in LOCATIONS  table that don't have corresponding rows in DEPARTMENT table, NULL's are returned in the result set for the DEPARTMENT table's columns.

SELECT 
  D.DEPARTMENT_NAME,
  L.CITY,
  L.STATE_PROVINCE,
  L.COUNTRY_ID
FROM DEPARTMENTS D RIGHT OUTER JOIN LOCATIONS L
ON D.LOCATION_ID = L.LOCATION_ID;

RESULT:
Returns all matching s 43 records including non matching record from Department with NULL as value

c) FULL Outer Join
Specifies that the results be generated using all rows from DEPARTMENTS and LOCATIONS. For those rows in DEPARTMENTS that don't have corresponding rows in LOCATION, NULL's are returned in the result set for the LOCATIONS columns. Additionally, for those rows in LOCATIONS that don't have corresponding rows in DEPARTMENTS , NULL's are returned in the result set for the DEPARTMENTS columns.

SELECT 
  D.DEPARTMENT_NAME,
  L.CITY,
  L.STATE_PROVINCE,
  L.COUNTRY_ID
FROM DEPARTMENTS D FULL OUTER JOIN LOCATIONS L
ON D.LOCATION_ID = L.LOCATION_ID;

4) Cross joins or Cartesian Product
Cross joins are joins without a join condition. Each row of one table is combined with each row of another table. The result is referred to as a Cartesian product.

SELECT  
  E.FIRST_NAME, 
  E.LAST_NAME,
  D.DEPARTMENT_NAME
FROM  EMPLOYEES E CROSS JOIN DEPARTMENTS D;

 

SQL Query Basics - Beginners

Overview:

I was helping one of my friend on SQL query and thought I can publish the same  as it might be useful for others who are interested in learning SQL Programming

I create this in two phase approach, one for the beginners and one for the advance  users.

Installing Oracle Express Database 11G

You can download the database from the following link
http://www.oracle.com/technetwork/database/express-edition/downloads/index.html

Once you download the ZIP file,  unzip the file and run the setup.exe, keep clicking next button until you reach the step where it'll ask you to setup a password.  Remember the password as this will be your system admin password for  your database

Once the installation is over Install the client utility to run the query, there are several tools the best and free one to me is Oracle SQL Developer, which is available in the same link above under the links section or choose the link below
http://www.oracle.com/technetwork/developer-tools/sql-developer/downloads/index.html

Once you complete installation, run the Oracel SQL developer and Connect to the database




You'll see empty connection names on the left hand side  ( I already created few accounts)

Give youself a name to the Conenction Name
choose 'system' for Username
enter the password you created during Oracle 11G Express installation on the  Password section and click 'Test' button and once successful, click 'Connect' button.

Now you'll see your connection in the Navigation pane screen.


by default, Oracle ships with a sample database called HR, but for some reason they lock that account, which we'll unlock in the following steps to start using the sample database.




Expand the 'Other Users' from the navigation pane and
right-click the HR database and select 'Edit User'
un-check all the check boxes and set a password
Click 'Apply'








After, you complete the above step, you are good to go with the HR database.

Now, lets connect to 'HR' database and start exploring our SQL skills.

Select the green '+' sign in the navigation pane or choose 'File>>New>>Database Connection' option from the menu
Enter the following information  on the connection window

Connection Name: Choose your own name or 'HR' in this case
User Name: Choose 'HR'
Password:  The password you choose during unlock section above and click 'Connect'.



Once you are connected you should see the following tables




That's it, lets get our SQL query exercise



Select all columns & rows from a tablE

SELECT * FROM <SCEHMA.TABLE NAME>

• select * from HR.Employees
• select * from HR.Departments

Select total records in a table
SELECT COUT(*) FROM  <SCEHMA.TABLE NAME>

• select count(*) from HR.Employees;

Select specific columns from table
SELECT <COLUMN1>, <COLUMN2>.... FROM <SCEHMA.TABLE NAME>

• SELECT
    EMPLOYEE_ID,  FIRST_NAME,  LAST_NAME,  EMAIL,  HIRE_DATE
   FROM
    EMPLOYEES ;

Filtering data based on a column (You can use different filter operators (Arithmetic or String)>
SELECT * FROM  <SCEHMA.TABLE NAME> WHERE <COLUMN NAME> condition <VALUE>

• SELECT * FROM HR.EMPLOYEES where HIRE_DATE >='21-MAR-1993';

Filtering data based on more than one column

• SELECT * FROM HR.EMPLOYEES where HIRE_DATE >='21-MAR-1973' AND SALARY >15000;
•     SELECT * FROM HR.EMPLOYEES where HIRE_DATE >='21-MAR-1973' AND
      SALARY between 10000 and 15000;
• SELECT * FROM HR.EMPLOYEES where DEPARTMENT_ID in (80,100); 

Sorting the data using the 'Order by' clause on a column - Default is Ascending order

• SELECT
    EMPLOYEE_ID,  FIRST_NAME,  LAST_NAME,  EMAIL,  HIRE_DATE,
    JOB_ID,  SALARY,  MANAGER_ID,  DEPARTMENT_ID
  FROM
    EMPLOYEES
  ORDER BY FIRST_NAME;

Order by multiple column - Default is Ascending order

SELECT
  EMPLOYEE_ID,  FIRST_NAME,  LAST_NAME,  EMAIL,  HIRE_DATE,
  JOB_ID,  SALARY,  MANAGER_ID,  DEPARTMENT_ID
FROM
  EMPLOYEES
ORDER BY LAST_NAME, FIRST_NAME;


Order by a column - another way to order without specifying the column name and by specifying the column position in the result

SELECT
  EMPLOYEE_ID,  FIRST_NAME,  LAST_NAME,  EMAIL,  HIRE_DATE,
  JOB_ID,  SALARY,  MANAGER_ID,  DEPARTMENT_ID
FROM
  EMPLOYEES
ORDER BY 2

Order by a column descending

SELECT
  EMPLOYEE_ID,  FIRST_NAME,  LAST_NAME,  EMAIL,  HIRE_DATE,
  JOB_ID,  SALARY,  MANAGER_ID,  DEPARTMENT_ID
FROM
  EMPLOYEES
ORDER BY FIRST_NAME DESC; 

SELECT
  DEPARTMENT_ID,
  DEPARTMENT_NAME,
  MANAGER_ID,
  LOCATION_ID
FROM
  DEPARTMENTS 
ORDER BY DEPARTMENT_NAME DESC; 


Grouping records by a  particular column

SELECT
  MANAGER_ID,  COUNT(*) "NOofEmployees"
FROM
  EMPLOYEES
  GROUP BY MANAGER_ID

* Note:  "NOofEmplyees" is the alias name for the count(*) results.

Group records by title and add a filter on top of the results

SELECT
  MANAGER_ID,  COUNT(*) "NOofEmployees"
FROM
  EMPLOYEES
  GROUP BY MANAGER_ID
  HAVING COUNT(*) >=8

Please see the next post - Intermediate Queries section  to advance your SQL query skills