Structured Query Language (SQL)

SQL Statments

What is a Joint?
Use a join to query data from more than one table.

• Write the join condition in the WHERE clause.
• Prefix the column name with the table name when the same column name appears in more than one table

Guidelines

• When writing a SELECT statement that joins tables, precede the column name with the table name for clarity and to enhance database access.
• If the same column name appears in more than one table, the column name must be prefixed with the table name.
• To join n table together, you need a minimum of (n-1) join conditions. Therefore, to join four tables, a minimum of three joins are required. This rule may not apply if your table has a concatenated primary key, in which case more than one column is required to uniquely identify each row.

Types of Joins

There are two main types of join conditions:

Equijoins:
The relationship between both tables must be equally joined. For example the values in DEPTNO column on both tables must be equal. Frequently, this type of join involves primary and foreign key complements.

SELECT   emp.empno, emp.name, emp.deptno, dept.deptno, dept.loc
FROM     emp, dept
WHERE    emp.deptno = dept.deptno

Using Table Aliases

SELECT e.empno, e.ename, e.deptno, d.deptno, d.loc
FROM   emp e, dept d
WHERE  e.deptno = d.deptno

Non-Equijoins

Emp
+---------------------------+
| EMPNO | ENAME    | SAL    |
+===========================+
| 7839    KING       5000   |
| 7698    BLAKE      2800   |
| 7782    CLARK      2450   |
| 7655    JONES       950   |
+---------------------------+
SALGRADE
+---------------------------+
| GRADE | LOSAL    | HISAL  |
+===========================+
| 1         700      1200   |
| 2        1201      1400   |
| 3        1401      2000   |
| 4        2001      3000   |
+---------------------------+
	 

SELECT   e.name, e.sal, s.grade
FROM     emp e, salgrade s
WHERE    e.sal
BETWEEN  s.losal AND s.hisal

Outer Joins

• You use an outer join to also see rows that do not usually meet the join condition.
• Outer join operator is the plus sign (+).

SELECT   table1.column, table2.colum
FROM     table1, table2
WHERE    table1.column(+) = table2.column;
		   
SELECT   table1.column, table2.colum
FROM     table1, table2
WHERE    table1.column = table2.column(+);
		   
SELECT   e.ename, d.deptno, d.dname
FROM     emp e, dept d
WHERE    e.deptno(+) = d.deptno
ORDER BY e.deptno;

What Are Constraints?

• Constraints enforce rules at the table level.
• Constraints prevent the deletion of a table if there are dependencies.
• The following constraint types are valid in Oracle:
  - NOT NULL
  - UNIQUE
  - PRIMARY KEY
  - FOREIGN KEY
  - CHECK

PRIMARY KEY Constraint
Defined at either the table level or the column level. A UNIQUE index is automatically created for a PRIMARY KEY column.

CREATE TABLE dept(
        deptno   NUMBER(2),
		dname    VARCHAR2(14),
		loc      VARCHAR2(13),
    	CONSTRAINT dept_dname_uk UNIQUE (dname),
		CONSTRAINT dept_deptno_pk PRIMARY KEY (deptno);

FOREIGN KEY Constraint
The FOREIGN KEY, or referential integrity constraint, designates a colum or combination of columns as a foreign key and establishes a relationship between a primary key or a unique key in the same table or a different table. A foreign key value must match an existing value in the parent table or be NULL.

CREATE TABLE emp(
		 empno    NUMBER(4)
		 ....
		 ....
		 deptno   NUMBER(7) NOT NULL,
		 CONSTRAINT emp_deptno_fk FOREIGN KEY (deptno)
		 REFERENCE dept (deptno);