Table creation with subquery

In this section we will disscuss about another methord of creating  a table. Let see how to create a table with the use of subquery but before this  we will see the meaning of subquery

Subquery:

“SQL statement embedded inside a another SQL statement is called a subquery “

Syntax:

CREATE TABLE Table_name (column1,column2,…..,column N) 
AS subquery

    In this syntax create table specify that creation of new table,Table_name specify the name of the table,column1, ….,column N etc specify the columns name of the table, AS is a keyword which states that we are going to create a new table from a exiting table,In subquery we can write the SQL select statement to select the rows from the other table.

Example:
 create a table employees2 from the exiting employees

CREATE TABLE EMPLOYEES2 (ID,FIRST_NAME,LAST_NAME,SALARY,DEPT_ID)
AS
SELECT EMPLOYEE_ID,FIRST_NAME,LAST_NAME,SALARY,DEPARTMENT_ID
FROM EMPLOYEES

Note : The above statement create a employees2 with the same structure and along with data of employees table.

I am interested in hearing your feedback, so that I can improve my articles and learning resources for you.connect with us on facebook, twitter

Share

Did you enjoy reading this and found it useful? If so, please share it with your friends:

sql developer enter full pathname for java.exe

  In this section we are going to solve the problem which i encoutered the following message repeatedly when trying to start SQL Developer  after installtion of Oracle Database 11g Enterprise: Enter the full pathname for java.exe.

and the solution is very easy just enter the path :

C:\app\abhishek\product\11.1.0\db_1\jdk\bin

after enter the correct path

Step 1: select the file type ...

Step 2: Connect to database . befor connect test the connection and once the connection successfull then save the connetion and connect to database .

Step 3: after successfully connection try to execute some sql Query.

 

I am interested in hearing your feedback, so that I can improve my articles and learning resources for you.connect with us on facebook, twitter

Share

Did you enjoy reading this and found it useful? If so, please share it with your friends:

Outer Join

In this lesson we are going to study about Outer Join in a database.

OUTER JOIN:  
      In Outer join operation ,all records from the source table are included in the result even though they did't satisfy the join condition .

TYPE OF OUTER JOIN:

• Left outer join .
• Right outer join .
• Full outer join .

SYNTEX:

SELECT Columns Names from both Tables FROM tablenameA
LEFT | RIGHT | FULLOUTER JOIN tablenameB ON condition


Left outer join : 

 

• Left outer join return the unmatched rows from the left table with matched rows from the right table.
• It selects all the matched rows from the tableB and unmatched rows from tableA.
• Specify the outer join operator in the left side table.

Right outer join :
 

• Return the unmatched rows from the right tableB plus matched rows from the left tableA.
• Specify the outer join operator in the right side table.

 Full outer join :

• It is a combination of left and right outer join .
• Join operation combines the results of both left and right outer join .
• The joined table will contain all the records from both tables.
• Fill the values NULL for missing matches on either side .

Example:

SQL :

•  Left outer join 

          select emp.employee_id,emp.last_name,dept.department_id
          from employees emp
          left outer join departments dept
          on
          emp.department_id=dept.department_id;

• Right outer join

       select emp.employee_id,emp.last_name,dept.department_id
        from employees emp
        right outer join departments dept
        on
        emp.department_id=dept.department_id;

• Full outer join 

        select emp.employee_id,emp.last_name,dept.department_id
        from employees emp
        full outer join departments dept
        on
        emp.department_id=dept.department_id;

Related Article

• Joins
• Cross Join 
• Equi Join
• Non  Equi Join
• Self Join 
• Natural Join 
• Join with Using clause.
• Join with ON clause

I am interested in hearing your feedback, so that I can improve my articles and learning resources for you.connect with us on facebook, twitter

Share

Did you enjoy reading this and found it useful? If so, please share it with your friends:

Join with ON clause

In this lesson we are going to study about Join with ON clause in a database.

Join with ON clause:

• The join condition for the natural join is basically an equijoin of all columns with the same name.
• To specfify arbitary conditions or specfify columns to join,the ON clause is used.
• The join condition is separated from other search conditions.
  The ON clause makes code easy to understand.

Example:

we are having two table DEPARTMENTS and EMPLOYEES with comman column department_id .So, we can join this two table with ON  clause.

SQL:

Select e.employee_id,e.last_name,e.department_id,d.location_id
From employees e join departments d
On (e.department_id=d.department_id);




OUTPUT:

 Same result with equijoin :

Select e.employee_id,e.last_name,e.department_id,d.location_id
From employees e join departments d
where  e.department_id=d.department_id;


Related Article

• Join
• Cross Join
• Equi Join
• Non Equi Join 
• Self Join
• Natural Join
• Join with Using clause 
• Outer Join.  

I am interested in hearing your feedback, so that I can improve my articles and learning resources for you.connect with us on facebook, twitter

Share

Did you enjoy reading this and found it useful? If so, please share it with your friends:

Join with Using clause

In this lesson we are going to study about Join with Using clause in a database.

Join with Using clause:

• Using clause is used to join the tables where the column in those tables shares a same name .
• Join operation carried out in these two table according to the values in these two columns.
• The columns listed the using clause cannot have any qualifier in the
  statement and it should not include the where clause .

     It means we can’t select the value of these two columns in output and we
can’t use these columns in where clause.

Example:
we are having two table DEPARTMENTS and EMPLOYEES with comman column department_id .So, we can join this two table with Using clause.

SQL:

select e.employee_id,e.last_name,d.location_id
from employees e join departments d
using (department_id)

OUTPUT:

 same result with equi join:

select  e.employee_id,
e.last_name,d.location_id
from employees e , departments d
where e.employee_id =d.department_id






NOTE:

• Do not use a table name or alias in referenced columns.
• The NATURAL JOIN and USING clause are mutually exclusive.

Related Article

• Join
• Cross Join
• Equi Join
• Non Equi Join 
• Self Join
• Natural Join
• Join with ON clause
• Outer Join. 

I am interested in hearing your feedback, so that I can improve my articles and learning resources for you.connect with us on facebook, twitter

Share

Did you enjoy reading this and found it useful? If so, please share it with your friends:

Natural Join

In this lesson we are going to study about Natural Join in a database.

Natural Join:

• This join is having a special features ,with the use of this join no need to specify the join condition explicitly.
• This type of join offer a further specification of equi join .
• We have to specify the keyword Natural join in the join statement.
• Natural Join automatically joins two table based on columns in the two table which have same datatype and names.

Example: EMPLOYEE table and DEPARTMENTtable have a same column DepeartmentID and same datatype.
so we can join this two table using
NATURAL JOIN.



SQL :

SELECT *

FROM employee

NATURAL JOIN department;

Note:

• If we write a where clause of a select statement with two or more table then the order parser will start the join operation from right to left.In this case the table name which is written last will be processed,
•  The  join can happen only on columns having the same names and same data types in both the table .If the columns have the same name ,but different data types,then the NATURAL JOIN syntex causes an error.

Related Article

• Join
• Cross Join
• Equi Join
• Non  Equi Join
• Self Join 
• Join with Using clause.
• Join with ON clause
• Outer Join. 

I am interested in hearing your feedback, so that I can improve my articles and learning resources for you.connect with us on facebook, twitter

Share

Did you enjoy reading this and found it useful? If so, please share it with your friends:

Self Join

In this lesson we are going to study about Self joins in a database.

Self Join: 

• In order to join a table to itself we use the self join.
• This type of join is used to compare values between two columns in the same table .

Example:-To find the name of Abhishek and Aritra manager we need to

1. find in EMPLOYEE table by looking the Emp_name columns.
2. find the manager number from Manager_id.
3. find the name of manager with manager_id.

sql query :-

select

      emp.Emp_Name as Employee_name,

      manager.Emp_Name as manager_name

from  employees emp,

      employees manager

where emp.manager_id=manager.emp_id

OUTPUT: 

In simulate two table in the from clause there are two  aliases ,namely emp and manager for the same table ,EMPLOYEE.



Related Article

• Join
• Cross Join
• Equi Join
• Non  Equi Join
• Natural Join 
• Join with Using clause.
• Join with ON clause 
• Outer Join

I am interested in hearing your feedback, so that I can improve my articles and learning resources for you.connect with us on facebook, twitter

Share

Did you enjoy reading this and found it useful? If so, please share it with your friends: