TRIGGER FOR INSERT ERROR IN BUSINESS HOURS

TRIGGER FOR INSERT ERROR  :

CREATE OR REPLACE TRIGGER secure_emp2
BEFORE INSERT ON EMP66
BEGIN
IF (TO_CHAR(SYSDATE,'DY') IN ('TUE','SUN')) OR
(TO_CHAR(SYSDATE,'HH24:MI')
NOT BETWEEN '08:00' AND '9:00')
THEN RAISE_APPLICATION_ERROR (-20500,'You may
insert into EMPLOYEES table only
during business hours.');
END IF;
END;
/






THEN TRY INSERT INTO .........  THUMS UP ☺

JOINS PRACTICE SAMPLES

TABLE 1 : NAME -- EMP66: 


CREATE TABLE EMP66
(
EMP_ID NUMBER(2) CONSTRAINT EMP_COLL_PK PRIMARY KEY,
EMP_NAME VARCHAR(20),
EMP_SALARY NUMBER(5)
);

--------------------------------------------------------------------------------------------------------------------------


TABLE 2 : NAME -- DEPT66: 

CREATE TABLE DEPT66
(
DEPT_ID NUMBER(2) CONSTRAINT DEPT_COL1_PK PRIMARY KEY,
DEPT_NAME VARCHAR(20),
EMP_ID CONSTRAINT DEPT_COL2_FK REFERENCES EMP66(EMP_ID)
);

_________________________________________________________________________________

INSERT VALUES  INTO EMP66:


INSERT INTO EMP66( EMP_ID,EMP_NAME,EMP_SALARY) VALUES(1,'STEVE',20000);
INSERT INTO EMP66( EMP_ID,EMP_NAME,EMP_SALARY) VALUES(2,'NANCY',30000);
INSERT INTO EMP66( EMP_ID,EMP_NAME,EMP_SALARY) VALUES(3,'GURU',40000);
INSERT INTO EMP66( EMP_ID,EMP_NAME,EMP_SALARY) VALUES(4,'ELLEN',55000);
INSERT INTO EMP66( EMP_ID,EMP_NAME,EMP_SALARY) VALUES(5,'JULIA',60000);



_________________________________________________________________________________

INSERT VALUES  INTO DEPT66:

INSERT INTO DEPT66( DEPT_ID,DEPT_NAME,EMP_ID) VALUES(1,'SALES',1);
INSERT INTO DEPT66( DEPT_ID,DEPT_NAME,EMP_ID) VALUES(2,'ACCOUNTS',2);
INSERT INTO DEPT66( DEPT_ID,DEPT_NAME,EMP_ID) VALUES(3,'FINANCE',3);
INSERT INTO DEPT66( DEPT_ID,DEPT_NAME) VALUES(4,'IT');
INSERT INTO DEPT66( DEPT_ID,DEPT_NAME) VALUES(5,'MARKETING');

_________________________________________________________________________________

SELECT * FROM EMP66;
_________________________________________________________________________________

SELECT * FROM DEPT66;
_________________________________________________________________________________

RIGHT OUTER JOIN :

QUERY 1: RIGHT OUTER JOIN WITH ‘ON’ CLAUSE

SELECT EMP_NAME,DEPT_NAME FROM DEPT66 RIGHT OUTER JOIN EMP66 ON(EMP66.EMP_ID=DEPT66.EMP_ID);

------------------------------------------------------------------------------------------------------------

QUERY 2: RIGHT OUTER JOIN WITH WHERE CLAUSE

SELECT EMP_NAME,DEPT_NAME FROM DEPT66 RIGHT OUTER JOIN EMP66 ON(EMP66.EMP_ID = DEPT66.EMP_ID) WHERE EMP_SALARY< 50000;

------------------------------------------------------------------------------------------------------------

QUERY 3: RIGHT OUTER JOIN WITH USING CLAUSE

SELECT EMP_NAME,DEPT_NAME FROM DEPT66 RIGHT OUTER JOIN EMP66 USING(EMP_ID);

________________________________________________________________________

LEFT OUTER JOIN :

SELECT EMP_NAME,DEPT_NAME FROM DEPT66 LEFT OUTER JOIN EMP66 ON(EMP66.EMP_ID=DEPT66.EMP_ID);

________________________________________________________________________

FULL OUTER JOIN:

QUERY 1 : FULL OUTER JOIN WITH ON CLAUSE

SELECT EMP_NAME,DEPT_NAME FROM EMP66 FULL OUTER JOIN DEPT66 ON(EMP66.EMP_ID=DEPT66.EMP_ID);

..................................................................................................................................

QUERY 2 : FULL OUTER JOIN WITH USING CLAUSE

SELECT EMP_NAME,DEPT_NAME FROM EMP66 FULL OUTER JOIN DEPT66 USING(EMP_ID);

..................................................................................................................................

QUERY 3 : FULL OUTER JOIN WITH WHERE CLAUSE

SELECT EMP_NAME,DEPT_NAME, EMP_SALARY FROM EMP66 FULL OUTER JOIN DEPT66 USING (EMP_ID) WHERE EMP66.EMP_SALARY < 50000;

..................................................................................................................................

QUERY 4 : FULL OUTER JOIN WITH ORDER BY CLAUSE

SELECT EMP_NAME,DEPT_NAME FROM EMP66 FULL OUTER JOIN DEPT66 USING(EMP_ID) WHERE EMP66.EMP_SALARY<50000 ORDER BY EMP_NAME DESC;

Full Outer Join In SQL.

Today’s SQL article is all about Full Outer Join.

Before moving ahead just want to say that in order to better understand the concept of full outer Join please read my last two SQL tutorial article on right and left outer join.

Here once again we will be using same table which we have used so far in this Outer Join series. These are the emp and the dept tables.

Let’s have a quick look of the structure and the data of these two tables. 

   Desc emp;

Our table emp has 3 columns emp id, emp name and emp salary. Here column emp id is a primary key.

   Desc dept;

Table dept also has 3 columns dept id, dept name and emp id. Here in this table column dept id is a primary key where column emp id is a foreign key reference from the emp table.

Now let’s have a look at the data of these two tables.

  SELECT * FROM emp;

Emp table has 5 rows where column one has emp id and column 2 has emp name and column 3 consist the salary of these employees.

Now data of dept table

  SELECT * FROM dept;

Table Dept also has 5 columns. Column one hold dept id where column two has dept name and column three emp id which is the foreign key has only 3 records corresponding to department name sales account and finance. 

We can interpret this data easily. For example the employee with employee id 1 is named Steve in our emp table and works in department of Sales. Similarly employee with employee id 2 is Nancy who works in Accounts department and employee with employee id 3 is Guru who works in finance. Also we have not assigned any employee id for department of IT and Marketing which signifies that no one works in these departments.

Let’s jump over to full outer join. 
Full outer join is kind of a combination of both right outer join and left outer join because it returns all the rows from left as well as the right side table.

Let’s have look of FULL OUTER JOIN syntax.

Syntax
  SELECT column names FROM 

  table1 FULL OUTER JOIN / FULL JOIN table 2

  ON(expression) or USING(column_name) 

  WHERE(expression) ORDER BY column names;

Syntax is pretty similar to our left or right join as you can see. We have our SELECT statement where you can specify the name of the columns from both the participating tables followed by FROM clause. And our JOIN clause which is full outer join. Here you can either write full outer join or just outer join since both are permissible and perform the same task. And then we have our Join condition ON and USING followed by WHERE and ORDER BY clause.

Query 1 : Full Outer Join With ON clause

SELECT   emp_name, dept_name   FROM   emp   FULL OUTER JOIN   dept   ON  (emp.emp_id  =  dept.emp_id);

Here in this query we are selecting emp name column from emp table and dept name column from dept table. In our full outer join clause we have emp table on left side and dept table on right side and then we have Our ON clause where we are comparing the values of emp id columns from the both the tables.

On executing this query the result will be.



If you will observe minutely then you can see that the result till row 5 is similar to that of right outer join, As all the records from right side table is here and only those records from left side table which satisfy the join condition are here in the result. Followed by all the remaining records from the tables thus the last row 6 and 7 contains the remaining emp names.

Here in this ON join clause we used columns which have same name and data type. Now let’s use columns which have different name and same data type for example column
dept id.

SELECT   emp_name, dept_name   FROM   emp   FULL OUTER JOIN   dept   ON  (emp.emp_id  =  dept.dept_id);

and the result of this query is

Query 2 : Full Outer Join With USING clause

We use USING join condition when

The column in join condition share the same name and same data type and are compared only using = comparison operator and no other comparison operator such as greater than, less than etc. 

As you can see, in the join condition (ON clause) of Query 1 we have used emp id column of both the tables. This column shares the same name and same data type hence we can easily replace this ON clause with USING clause. Let’s do it. 

SELECT   emp_name, dept_name   FROM   emp   FULL OUTER JOIN   dept   USING  (emp_id);

The result of this query is 


If you compare this result with the result of our first query then you will find them as exactly the same. 

Query 3 : Full Outer Join With WHERE clause

You can also limit the result by using WHERE clause. Say you want to see the name of only those employees and their departments who have a salary of less than 50000

For that we can modify our query 2 and add a where clause to it.

SELECT   emp_name, dept_name, emp_salary   FROM   emp   FULL OUTER JOIN   dept   USING  (emp_id) 
WHERE emp.emp_salary < 50000;

On executing this query you will get only those employee and their departments who has salary less than 50,000 

Query 4 : Full Outer Join With ORDER BY clause

Similarly you can sort the result using ORDER BY clause. Say you want to sort the result of query 3 in ascending order according to the employee name (emp_name column) For that just add the ORDER BY clause followed by the column name which is emp_name in our case. 

SELECT   emp_name, dept_name   FROM   emp   FULL OUTER JOIN   dept   USING  (emp_id)  
WHERE emp.emp_salary < 50000  ORDER BY emp_name;

If you execute this query then by default the result will be sorted in ascending order. 


If you write DESC right after the column, your result will be sorted in descending order. For Example 

SELECT   emp_name, dept_name   FROM   emp   FULL OUTER JOIN   dept   USING  (emp_id)  
WHERE emp.emp_salary < 50000   ORDER BY emp_name DESC;

That’s it on Full Outer Joins. If you found it useful then please share it with your friends on social networking. You can also write to me for any queries. Keep reading for more such concepts. 

Thanks and take care.

Right Outer Join In SQL.

As the name suggests Right Outer Join is a form of Outer Join that returns each and every record from the source table and returns only those values from the target table that fulfil the Join condition.

*Note: The Source table is the one situated on the right side of the Right Outer Join Clause whereas The Target table is the one on the left side of this clause.

Syntax :
   SELECT 
    columns 
  FROM 
  target_table RIGHT OUTER JOIN source_table 
  ON(source_table.column = target_table.column)
  WHERE condition
  ORDER BY column_names;

Examples:
To demonstrate the working of Right Outer Join, I have created two tables by the name of emp and dept and have also inserted some data in them. 

Structure of emp table

Structure of dept table

Data In emp table

Data In dept table

If you have noticed that in dept table IT and marketing department don’t have any employees. 

Query 1: Right Outer Join with ‘ON’ clause

Now when should we use ON Join Condition in SQL Joins you ask?

1. When columns which are participating in ON join condition have Different name and Same Data type or
2. When columns which are participating in ON join condition have SAME NAME and SAME Data-type.

Let’s write a very simple Right outer join query.

SELECT 
  emp_name, dept_name
FROM  dept  RIGHT OUTER JOIN  emp
ON (emp.EMP_ID = dept.EMP_ID);

In the above query we are selecting emp_name from emp table and dept_name from dept table. Emp table is the source table as it’s on the right hand side of join clause thus automatically making dept table as the target table.

Column emp_id is a primary key in emp table and foreign key in dept table thus column emp_id is the column which is establishing a relationship in between these two tables. As column emp_id is common in both tables thus it’s a best fit for Join condition (ON clause).

If you execute this query you will get all the rows of emp_name column of emp table as emp is the source table and only those rows of dept_name column of dept table which satisfy the join condition (condition in ON clause).

Query 2: Right Outer Join with WHERE clause

You can use WHERE clause with any type of JOIN and limit or Filter the result. In case of JOINS, WHERE clause always comes after Join Condition (ON or USING) Say you want to see employees name and department name whose salary is less than 50,000 

SELECT   emp_name, dept_name 
FROM  dept  RIGHT OUTER JOIN  emp 
ON  (emp.EMP_ID = dept.EMP_ID) WHERE  emp_salary < 50000; 

Query 3: Right Outer Join with USING clause

Using clause can be used on the place of ON clause when

1. We are performing Equi Join.
2. We want to put join on that column of source and target table which are common i.e. have same name and data type.

Most of the joins you’ll perform will be equijoins, and if you always use the same name as the primary key for your foreign keys, then:

SELECT  emp_name,dept_name  FROM  dept  RIGHT OUTER JOIN  emp  USING (emp_id);

That’s all on Right Outer Joins. You can also watch my tutorial for understanding its practical usage. Kindly please share this article on your social networking and spread the knowledge. 
Thanks & Have A Great Day!

Introduction of Joins In SQL.

Joins In SQL

Whenever we want to fetch data from two or more database tables based on common field (column) we use Joins. 

DEF:
  Technically Joins are SQL operations which help us in retrieving data from two or more tables that share a common
  field.

Types of Joins

There several Types of Joins such as

1. Inner Join
2. Outer Join
3. Cross Join
4. Self-Join

Outer Join can further be categorize into 3 more categories

1. Right Outer Join
2. Left Outer Join
3. Full Outer Join.

Apart from types of join we also have two join conditions these are

1. Equi Joins
2. Non-Equi Joins

Equi Join: this type of Join looks for common records in two tables on the basis of equality condition and then combines them. Equi-Join is constructed with the help of equality operator (=) where the values of Primary key and Foreign key are compared. Hence the common or matching records from the two tables are presented in the result.

Non Equi Join: Here equality operator is not used, instead operators such as <, >, BETWEEN etc. are employed. Therefore Non Equi-Join is the opposite of Equi-Join, and uses joining conditions excluding equal operator. For example, in a non Equi-Join condition you can use !=, <=,>=,<,> or BETWEEN etc. operators can be used for joining. For implementation you can see Inner-Join.

Outer Join: Outer joins are the SQL operation which definitely return all the rows from Source table no matter whether there is a matching join condition hit or not. At the same time it returns only those rows of the target table that fulfils the matching join condition otherwise just shows ‘null’ in the rows

Natural Joins.

Definition:

A NATURAL JOIN is a JOIN operation that creates an implicit join clause for you based on the common columns in the two tables being joined. Common columns are columns that have the same name in both tables.

A NATURAL JOIN can be an INNER join, a LEFT OUTER join, or a RIGHT OUTER join. The default is INNER join.
Source: Oracle

We will start with Natural Join.
But before jumping over to natural join let me tell you a few terminologies

Source table: Table which comes after FROM clause in the select statement 

Target table: All the tables which come after JOIN clause in the query.

When the join query is executed then oracle starts matching data from the source table to the target table. If there is a hit for a matching source table data in the target table then the value is returned. 

Scenario 1: When there is only one identical name column between source and target tables. 

Let’s use Departments table and locations table for the demonstration. 

Department table has 4 columns DEPARTMENT_ID, DEPARTMENT_NAME, MANAGER_ID, LOCATION_ID

And Locations table has 6 columns 
Location id, street address, postal code, city, state province and country id

Upon minutely observing you will notice that location id is a common column between our source and target tables. It is the foreign key and thus has same data type and column width. Let’s write the query for natural join. 

Say you want to find the City for all your Departments 

SELECT   department_name, city   FROM   departments   NATURAL JOIN   locations;

In this query we are selecting department name from departments table and city from locations table. You can select other columns too

Departments is our source table as its coming right after FROM keyword and locations is our Target table as its coming after JOIN clause. 

The best part of using Natural join is that you do not need to specify the join column because column with the same name in source and target table are automatically associated with each other. 

On executing this query oracle returns all the rows from department column of departments table and from city column of locations table, where location id of departments table is equal to the location id of location table. 

Scenario 2: What if when our source and target tables have more than one identical name column.

To demonstrate this scenario we will use employees and departments table these two tables shares two common columns which are department id and manager id.

Here if we put Natural join on these two tables then oracle engine will use these two common columns to return the result.

Say we want to see the name of employee and the name of department in which he or she is working. For that we will select first name from employee table which is our source table and department name from departments table.

SELECT   first_name, department_name   FROM   employees   NATURAL JOIN   departments;

As I have already mentioned that when we use natural join, there is no need to specify join columns explicitly. All the columns which are common in both source and target table get associated automatically.

The queries in which all the common columns of source and target table get associated automatically are known as Pure Natural Join.

Which means in this query, oracle has automatically associated our common columns which are department id and manager id.

To help you understand more clearly let’s write same query but this time using ON clause.

SELECT   first_name,department_name   FROM   employees  JOIN    departments   ON   (employees.manager_id = departments.manager_id   AND   employees.department_id = departments. department_id);

Here is a slight change in the natural join syntax as we are using ON clause this time, instead of writing natural join we will only write JOIN.

Both these queries produce the same results as both are same query written in different ways.

In this query we emphasize natural join using ON clause. As we are using ON clause thus it becomes mandatory to specify the columns over which we want to perform the join.

Scenario 3: Natural join with USING clause

Here Once again we will use our natural join query.

SELECT   first_name, department_name   FROM   employees    NATURAL JOIN   departments;

As we can see here in this query that when we have more than one common column oracle engine uses all of them to produce the result

Now, here in this query we are using employees and department tables which have 2 common columns department id and manager id. 

What if you want to use only department id in your natural join instead of both manager and department id? 

In this situation we use natural join with USING clause. 

Say we want to select all the first name and department name from source table employees and target table departments from where we have equal values of manager ids in both the employees table and the departments table. 

Let’s write the query

SELECT   first_name, department_name   FROM   employees   JOIN   departments   USING(manager_id);

I hope you enjoyed reading.