Foreign Key Constraints

Foreign Key Constraints:

A foreign key always references a primary key. It introduces a Referential Integrity into the table.

 

E.g. (Foreign Key)

 

SQL> CREATE TABLE emp(

empno NUMBER(4),

ename VARCHAR2(10) NOT NULL,

job VARCHAR2 (9)

hiredate DATE,

sal NUMBER(7,2)

comm NUMBER(7,2)

deptno NUMBER(7) NOT NULL,

CONSTRAIN emp_deptno_fk FOREIGN KEY (deptno)

REFERENCES dept (deptno));

 

Output:

Table created.

 

ON DELETE CASCADE:

This keyword can be used to make sure all foreign key references are deleted once the primary key reference is deleted.

Without this option, the primary key in the parent table cannot be deleted if it is referenced as a foreign key in the child table.

 

CHECK Constraint: Condition for row.

 

E.g. (CHECK)

 

SQL> CREATE TABLE emp(

empno NUMBER(4),

ename VARCHAR2(10) NOT NULL,

job VARCHAR2 (9)

hiredate DATE,

sal NUMBER(7,2)

comm NUMBER(7,2)

deptno NUMBER(2)

CONSTRAINT dept_deptno_ck CHECK (deptno BETWEEN 10 AND 99));

 

Output:

Table created.

VIEWS:

  • To restrict database access
  • To make complex queries easy.
  • To present different views of the same data.

 

NOTE:

When views are created by using joins, they cannot be modified.

 

E.g. (VIEWS)

 

SQL> CREATE VIEW empnv

AS (SELECT empno, ename, job

FROM emp

WHERE deptno = 10);

 

Output:

View created.

 

 

Student Exercise1:

 

The CREATE or REPLACE VIEW clause allows us to modify the view if it exists. Modify the previous view by giving the COLUMN ALIASES of employee, name & job title.

Output:

View created.

 

 

Rules For Performing DML on Views:

 

DATA cannot be modified in a view if,

  • It contains group functions.
  • It contains a GROUP BY clause.
  • It contains a DISTINCT keyword.
  • Columns defined by expression.
  • Contains NOT NULL columns in base tables that are not selected by the view.

 

NOTE:

To remove a view, use the drop view command.

 

 

Student Exercise:

DELETE the previous VIEW.

 

SQL> drop view empnv;

 

Output:

View dropped.

 

 

Student Exercise 1:

CREATE a view named dept20 that contains the empno, ename & deptno. or all employees in dept20. Label the view column emp_id, employee & ept_id.

 

 

Student Exercise 2:

Use the dept20 view to display the names of all employees whose names end with an ‘s’.

 

SQL> SELECT * FROM dept20

WHERE employee LIKE ‘S%’;

 

Output:

EMP_ID EMPLOYEE     DEPT_ID

——— ———- ———

7369 SMITH             20

7788 SCOTT             20

 

SEQUENCES:

  • They are number generating objects that are used to generate numbers for primary key.

 

Example: (SEQUENCES)

 

SQL> CREATE SEQUENCE dept_deptno

INCREMENT BY 1

START WITH 91

MAXVALUE 100

NOCACHE

NOCYCLE;

 

Output:

Sequence created.

 

USING SEQUENCES:

 

dept_deptno.NEXTVAL

dept_deptno.CURRVAL

 

These functions are commonly used.

 

Student Exercise 3:

Create a sequence by your name, that it starts with100, has the maximum value 1000 & is incremented by 10. Use the NEXTVAL & CURRVAL functions.

 

 

ALTER SEQUENCE:

 

  • Command is used to change the sequence.

 

 

Student Exercise 4:

ALTER the previous sequence so that it is incremented by 20.

 

 

DROP SEQUENCE:

 

  • Command is used to delete the sequence.

 

 

Student Exercise 5:

DROP the previous sequence.

 

 

 

Advertisements

TRANSACTIONS AND OTHER FUNCTIONS:

Transaction:

 

  • Database transactions consist of the following statements.
  • DML statement that makeup one consistent change to the data.
  • One DDL statement.
  • One DCL statement.

 

  • DDL stands for Data Definition Language.
  • DCL stands for Data Control Language.

 

 

NOTE: –

  • Database transaction start when the first executable statement is executed.
  • Transaction and when one of the following events occurs.
  • COMMIT or ROLLBACK is issued.
  • DDL or DCL statement executed.
  • User exits.
  • System crashes (Worst Scenario).

 

 

 

COMMIT or ROLLBACK:

 

 

Transaction

INSERT

UPDATE

INSERT

DELETE

 

 

LAST          SAVEPOINT A                               SAVEPOINT B

COMMIT

 

ROLLBACK TO B

 

ROLLBACK TO A

 

ROLLBACK

 

COMMIT:

  • Ends the current transaction by making all the pending data changes permanent.

 

 

SAVEPOINT name:

  • Marks the save point in the current transaction.

 

 

ROLLBACK TO SAVEPOINT name:

  • Ends the current transaction by discarding the SAVEPOINT and all subsequent changes.

 

 

State Of Data Before COMMIT OR ROLLBACK:

  • Previous state of the data can be recovered.
  • The current user can review the results of the DML operation by using SELECT.
  • Other users cannot view the results of the DML by the current user.
  • The effected rows are locked, other user cannot change the data within the effected rows or the effected rows are locked.

 

 

State Of Data After COMMIT:

  • Data changes are made permanent in the database.
  • The previous state of the data is permanently lost.
  • All users can view the results.
  • Locks on the effected rows are released.

 

Example: (COMMIT)

 

  • All save points are erased

 

Make Change:

 

SQL> UPDATE emp

SET deptno=10

WHERE empno = 7782;

 

Output:

1 row updated.

 

 

 

Commit Change:

 

SQL> COMMIT;

 

Output:

Commit complete.

 

Example: (Rollback)

 

1) SQL> DELETE from emp;

 

Output:

14 rows deleted.

 

 

2) SQL> ROLLBACK;

 

Output:

Rollback complete.

 

Queries:

 

1) SQL> UPDATE emp

SET deptno=20

WHERE empno = 7782;

 

Output:

1 row updated.

 

 

2) SQL> SAVEPOINT update_done;

 

Output:

Savepoint created.

 

 

3) SQL> INSERT INTO dept (deptno, dname, loc)

VALUES (50, ‘DEVELOPMENT’, ‘DETROIT’);

 

Output:

1 row created.

 

 

4) SQL> ROLLBACK TO update_done;

 

Output:

Rollback complete.

 

Creating & Managing Tables:

 

SQL> CREATE TABLE item       Table Name

(itemno  NUMBER(2),

iname   VARCHAR(14),         Data type

loc     VARCHAR(13));

 

Output:

Table created.

 

 

Creating a Table By Using A Sub-Query:

 

SQL> CREATE TABLE employee_in_dept30

AS       Must be Used!

SELECT empno, ename, hiredate, sal, deptno, mgr

FROM emp

WHERE deptno=30;

 

Output:

Table created.

 

 

Adding A Column:

 

command         Table Name

 

SQL> ALTER TABLE dept

 

ADD (job VARCHAR2(9));

 

 

 

 

Keyword   Column        data type

name

 

Output:

Table altered.

 

  • Default value will be “NULL” in the job column after the execution of the upper query.

 

 

 

 

Modifying Column:

SQL> ALTER TABLE dept

MODIFY (dname VARCHAR2(15));

New data type

Output:

Table altered.

 

 

  • Usual conversation is from similar kind of data type for example char can be converted to VARCHAR2 or empno might be change from 4 to 5 numbers.
  • If a column is modified the new data type would be applied only to subsequent addition and all previous rows would have the old data type.

 

 

 

Dropping A Table:

 

SQL> DROP TABLE dept30;

Object

Output:

Table dropped.

 

  • It simply deletes the table.
  • In scripts design to create full database structure all tables that are to created are first dropped.

 

 

 

Changing The Name Of The Object:

 

SQL> RENAME dept TO department;

 

Output:

Table renamed.

 

 

Example Of Object:

  • View
  • Table
  • Index
  • Cursor

 

 

 

Constraints:

 

dept                                                    emp

           deptno, dname, loc                          empno, deptno, ename

 

 

 

 

Primary                                          Primary  Foreign

Ke y                                                Key        Key

 

  • Unique constraint is used for fields that are not primary key and they must be unique, only a single Primary Key per table but multiple unique columns can exist.

 

 

Types Of Constraints:

 

  • Not Null
  • Unique
  • Primary Key                      Mostly Used
  • Foreign Key

 

 

  • Check                   Condition Based

 

 

Example:

 

SQL> CREATE TABLE item(

itemno NUMBER(2),          Type Of Constraint

iname VARCHAR(14),

loc VARCHAR(13),

CONSTRAINT   item_iname_uk    UNIQUE     (iname),

CONSTRAINT   item_itemno_pk PRIMARY KEY  (itemno));

Keyword      Name of constraint                               Constraint

applied on

Output:

Table created.

 

SE1:

Create a table named Empdept20 that contains the ename , sal of all employees in department 20.

 

SE2:

Rename the above table to Emp_dept_20.

 

SE3:

Write a query the ename from the user and then deletes the record of that employee.

 

SE4:

Create a view containing information about all employees in department 20.

 

SE5:

Display information about employees whose name starts with an ‘S’ from SE4.

 

SE6:

Create a table EMP10 that lists employees of department 10. Perform DML on the table after creating savepoints. Perform rollbacks, observe results and give systematic outputs.

Sql Script (demobld.sql)

This is the best script use by all learners in sql. Copy the below script and paste it on your notepad. Save the file as demobld.sql . Remember to change the file name to .sql and properties to all files while saving the notepad file.
Run the script in oracle sql 10g .

–Script starts here:


— Copyright (c) Oracle Corporation 1988, 2000.  All Rights Reserved.

— NAME
—   demobld.sql

— DESCRIPTION
—   This script creates the SQL*Plus demonstration tables in the
—   current schema.  It should be STARTed by each user wishing to
—   access the tables.  To remove the tables use the demodrop.sql
—   script.

—  USAGE
—    From within SQL*Plus, enter:
—        START demobld.sql

SET TERMOUT ON
PROMPT Building demonstration tables.  Please wait.

DROP TABLE EMP;
DROP TABLE DEPT;
DROP TABLE BONUS;
DROP TABLE SALGRADE;
DROP TABLE DUMMY;

CREATE TABLE EMP
       (EMPNO NUMBER(4) NOT NULL,
        ENAME VARCHAR2(10),
        JOB VARCHAR2(9),
        MGR NUMBER(4),
        HIREDATE DATE,
        SAL NUMBER(7, 2),
        COMM NUMBER(7, 2),
        DEPTNO NUMBER(2));

INSERT INTO EMP VALUES
        (7369, ‘SMITH’,  ‘CLERK’,     7902,
        TO_DATE(’17-DEC-1980′, ‘DD-MON-YYYY’),  800, NULL, 20);
INSERT INTO EMP VALUES
        (7499, ‘ALLEN’,  ‘SALESMAN’,  7698,
        TO_DATE(’20-FEB-1981′, ‘DD-MON-YYYY’), 1600,  300, 30);
INSERT INTO EMP VALUES
        (7521, ‘WARD’,   ‘SALESMAN’,  7698,
        TO_DATE(’22-FEB-1981′, ‘DD-MON-YYYY’), 1250,  500, 30);
INSERT INTO EMP VALUES
        (7566, ‘JONES’,  ‘MANAGER’,   7839,
        TO_DATE(‘2-APR-1981’, ‘DD-MON-YYYY’),  2975, NULL, 20);
INSERT INTO EMP VALUES
        (7654, ‘MARTIN’, ‘SALESMAN’,  7698,
        TO_DATE(’28-SEP-1981′, ‘DD-MON-YYYY’), 1250, 1400, 30);
INSERT INTO EMP VALUES
        (7698, ‘BLAKE’,  ‘MANAGER’,   7839,
        TO_DATE(‘1-MAY-1981’, ‘DD-MON-YYYY’),  2850, NULL, 30);
INSERT INTO EMP VALUES
        (7782, ‘CLARK’,  ‘MANAGER’,   7839,
        TO_DATE(‘9-JUN-1981’, ‘DD-MON-YYYY’),  2450, NULL, 10);
INSERT INTO EMP VALUES
        (7788, ‘SCOTT’,  ‘ANALYST’,   7566,
        TO_DATE(’09-DEC-1982′, ‘DD-MON-YYYY’), 3000, NULL, 20);
INSERT INTO EMP VALUES
        (7839, ‘KING’,   ‘PRESIDENT’, NULL,
        TO_DATE(’17-NOV-1981′, ‘DD-MON-YYYY’), 5000, NULL, 10);
INSERT INTO EMP VALUES
        (7844, ‘TURNER’, ‘SALESMAN’,  7698,
        TO_DATE(‘8-SEP-1981’, ‘DD-MON-YYYY’),  1500,    0, 30);
INSERT INTO EMP VALUES
        (7876, ‘ADAMS’,  ‘CLERK’,     7788,
        TO_DATE(’12-JAN-1983′, ‘DD-MON-YYYY’), 1100, NULL, 20);
INSERT INTO EMP VALUES
        (7900, ‘JAMES’,  ‘CLERK’,     7698,
        TO_DATE(‘3-DEC-1981’, ‘DD-MON-YYYY’),   950, NULL, 30);
INSERT INTO EMP VALUES
        (7902, ‘FORD’,   ‘ANALYST’,   7566,
        TO_DATE(‘3-DEC-1981’, ‘DD-MON-YYYY’),  3000, NULL, 20);
INSERT INTO EMP VALUES
        (7934, ‘MILLER’, ‘CLERK’,     7782,
        TO_DATE(’23-JAN-1982′, ‘DD-MON-YYYY’), 1300, NULL, 10);

CREATE TABLE DEPT
       (DEPTNO NUMBER(2),
        DNAME VARCHAR2(14),
        LOC VARCHAR2(13) );

INSERT INTO DEPT VALUES (10, ‘ACCOUNTING’, ‘NEW YORK’);
INSERT INTO DEPT VALUES (20, ‘RESEARCH’,   ‘DALLAS’);
INSERT INTO DEPT VALUES (30, ‘SALES’,      ‘CHICAGO’);
INSERT INTO DEPT VALUES (40, ‘OPERATIONS’, ‘BOSTON’);

CREATE TABLE BONUS
        (ENAME VARCHAR2(10),
         JOB   VARCHAR2(9),
         SAL   NUMBER,
         COMM  NUMBER);

CREATE TABLE SALGRADE
        (GRADE NUMBER,
         LOSAL NUMBER,
         HISAL NUMBER);

INSERT INTO SALGRADE VALUES (1,  700, 1200);
INSERT INTO SALGRADE VALUES (2, 1201, 1400);
INSERT INTO SALGRADE VALUES (3, 1401, 2000);
INSERT INTO SALGRADE VALUES (4, 2001, 3000);
INSERT INTO SALGRADE VALUES (5, 3001, 9999);

CREATE TABLE DUMMY
        (DUMMY NUMBER);

INSERT INTO DUMMY VALUES (0);

COMMIT;

SET TERMOUT ON
PROMPT Demonstration table build is complete.

INSERT Statement & Other Values

Inserting Special Values:

 

  • We can insert for example the the current date by using “SYSDATE”
  • Insert any date by using TO_DATE function

 

 

Student Exercise:

 

1)    Insert the row into the EMP table with employee number is 9000, name is JD and the system date.

 

 

Output

1 row created.

 

EMPNO ENAME  JOB      MGR  HIREDATE   SAL    COMM    DEPTNO

—– —— ——- —– ——— —–  —— ——–    7369  SMITH  CLERK    7902 17-DEC-80  800                20

7499  ALLEN  SALESMAN 7698 20-FEB-81  1600    300        30

.     .       .       .       .       .       .          .

.     .       .       .       .       .       .          .

.     .       .       .       .       .       .          .

9000  JD                   12-OCT-07

 

 

 

 

 

 

 

 

 

2)    Insert the row into the EMP table using the employee number 9500 and name as RAFID and date 1st april 2011

 

 

 

Output:

1 row created.

 

EMPNO ENAME  JOB      MGR  HIREDATE   SAL    COMM    DEPTNO

—– —— ——- —– ——— —–  —— ——–    7369  SMITH  CLERK    7902 17-DEC-80  800                20

7499  ALLEN  SALESMAN 7698 20-FEB-81  1600    300        30

.     .       .       .       .       .       .          .

.     .       .       .       .       .       .          .

.     .       .       .       .       .       .          .

9500  RAFID                01-APR-11

 

 

 

Copying Rows From Another Table:

 

  • Write INSERT statement with a SUB-QUERY

 

 

Example:

 

SQL> INSERT INTO MANAGERS(empno, ename, sal, hiredate)

2  SELECT empno, ename, sal, hiredate

3  FROM EMP

4  WHERE job=’MANAGER’;

 

SE2:

Create a table SALESMEN  and insert the empno,ename and sal into this table from emp.

 

 

UPDATE:

 

  • Modifies existing data
  • Always use the “WHERE” clause

 

 

 

Example:

 

SQL> UPDATE table

2  SET field=new value

3  WHERE condition.

 

 

SE3:

Update SMITH’s deptno to Research

 

SE4:

Update Employee 7698 job & department to match that of employee 7499

 

 

  • In Upper query first job will be compared and if jobs are same then department number will updated.
  • In Upper query 7789 doesn’t exist so the department number is NULL in 7789 so the department number of 7788 will be updated to NULL.

 

 

 

Constraints:

 

Integrity Constraint:

  • It exist two maintain the integrity of data.

 

 

 

DELETE Statement:

 

SQL> DELETE from DEPARTMENT

2  WHERE dname=’DEVELOPMEN’;

 

 

Output:

1 row deleted.

 

  • Always include the where clause in delete statement otherwise all rows are deleted.

 

 

SE5:

Delete all entries in emp that belong to department SALES.

 

 

  • There is a “Integrity Constraint Error” because if we delete deptno 10 from dept table which is primary key in dept table and there are also foreign keys references available in emp table so we can’t delete its primary key from dept table.

 

 

INSERT Statement & Other Values

Inserting Special Values:

 

  • We can insert for example the the current date by using “SYSDATE”
  • Insert any date by using TO_DATE function

 

 

Student Exercise:

 

1)    Insert the row into the EMP table with employee number is 9000, name is JD and the system date.

 

 

Output

1 row created.

 

EMPNO ENAME  JOB      MGR  HIREDATE   SAL    COMM    DEPTNO

—– —— ——- —– ——— —–  —— ——–    7369  SMITH  CLERK    7902 17-DEC-80  800                20

7499  ALLEN  SALESMAN 7698 20-FEB-81  1600    300        30

.     .       .       .       .       .       .          .

.     .       .       .       .       .       .          .

.     .       .       .       .       .       .          .

9000  JD                   12-OCT-07

 

 

 

 

 

 

 

 

 

2)    Insert the row into the EMP table using the employee number 9500 and name as RAFID and date 1st april 2011

 

 

 

Output:

1 row created.

 

EMPNO ENAME  JOB      MGR  HIREDATE   SAL    COMM    DEPTNO

—– —— ——- —– ——— —–  —— ——–    7369  SMITH  CLERK    7902 17-DEC-80  800                20

7499  ALLEN  SALESMAN 7698 20-FEB-81  1600    300        30

.     .       .       .       .       .       .          .

.     .       .       .       .       .       .          .

.     .       .       .       .       .       .          .

9500  RAFID                01-APR-11

 

 

 

Copying Rows From Another Table:

 

  • Write INSERT statement with a SUB-QUERY

 

 

Example:

 

SQL> INSERT INTO MANAGERS(empno, ename, sal, hiredate)

2  SELECT empno, ename, sal, hiredate

3  FROM EMP

4  WHERE job=’MANAGER’;

 

SE2:

Create a table SALESMEN  and insert the empno,ename and sal into this table from emp.

 

 

UPDATE:

 

  • Modifies existing data
  • Always use the “WHERE” clause

 

 

 

Example:

 

SQL> UPDATE table

2  SET field=new value

3  WHERE condition.

 

 

SE3:

Update SMITH’s deptno to Research

 

SE4:

Update Employee 7698 job & department to match that of employee 7499

 

 

  • In Upper query first job will be compared and if jobs are same then department number will updated.
  • In Upper query 7789 doesn’t exist so the department number is NULL in 7789 so the department number of 7788 will be updated to NULL.

 

 

 

Constraints:

 

Integrity Constraint:

  • It exist two maintain the integrity of data.

 

 

 

DELETE Statement:

 

SQL> DELETE from DEPARTMENT

2  WHERE dname=’DEVELOPMEN’;

 

 

Output:

1 row deleted.

 

  • Always include the where clause in delete statement otherwise all rows are deleted.

 

 

SE5:

Delete all entries in emp that belong to department SALES.

 

 

  • There is a “Integrity Constraint Error” because if we delete deptno 10 from dept table which is primary key in dept table and there are also foreign keys references available in emp table so we can’t delete its primary key from dept table.

 

 

Special Sub-Queries & User Input

Multiple Row Sub-Query:

IN, ANY, ALL

 

IN:

Student Exercise:

Find the employees who earn the same salary as the minimum salary of departments.

 

Output

ENAME         DEPTNO       SAL

———- ——— ———

SMITH             20       800

JAMES             30       950

MILLER            10      1300

 

ANY:

Example:

 

SQL> SELECT empno, ename, job

2  FROM EMP

3  WHERE sal < ANY (SELECT sal

4                   FROM EMP

5                   WHERE job=’CLERK’)

6  AND job <> ‘CLERK’;

 

Output

EMPNO ENAME      JOB

——— ———- ———

7521 WARD       SALESMAN

7654 MARTIN     SALESMAN

 

 

NOTES

  • This example displays employees whose salary is less than any clerk and who are not clerks.
  • For example if the minimum salary is 1300 this statement displays employees who are not clerks but earn less than 1300.

 

RULES

  • “ < ANY “ means less than Maximum value returned.
  • “ > ANY “ means more than Minimum value returned.
  • “ = ANY “ equivalent to “IN”.

 

 

 

ALL:

  • “ > ALL “ means that more than the maximum.
  • “ < ALL “ means that less than the minimum.

 

 

 

User Input:

  • The Oracle SQL accept inputs by the use of substitution variables. The     “ & “ symbol is use for inputting the values from the user.

 

 

Example:

SQL> SELECT empno, ename, sal, deptno

2  FROM EMP

3  WHERE empno = &employee_num;

 

 

 

 

Substitution variables

 

Output

Enter value for employee_num: 7839

old   3: WHERE empno = &employee_num

new   3: WHERE empno = 7839

 

EMPNO ENAME            SAL    DEPTNO

——— ———- ——— ———

7839 KING            5000        10

 

 

 

 

Student Exercise1:

Write SQL that accepts a wildcard ename from the user and displays the employee name and department number of the employee.

 

 

 

Scripts:

  • Scripts run at the command line interface of the Oracle.
  • We can use the get command or “@@” command.
  • Oracle scripts is file of SQL statements that runs in batch mode.
  • Scripts files have an extension of .SQL.

 

&&:

The double & variable accepts only ones and then reuse the value.

 

 

Example:

SQL> SELECT empno, ename, job, &&Col_Nam

2  FROM EMP

3  ORDER BY &Col_Nam;

 

Output

Enter value for col_nam: deptno

old   1: SELECT empno, ename, job, &&Col_Nam

new   1: SELECT empno, ename, job, deptno

old   3: ORDER BY &Col_Nam

new   3: ORDER BY deptno

 

EMPNO ENAME      JOB          DEPTNO

——— ———- ——— ———

7782 CLARK      MANAGER          10

7902 FORD       ANALYST          20

7499 ALLEN      SALESMAN         30

………..

Accept:

  • Accept command is use to get an input and display a user prompt by using the “PROMPT” keyword.
  • Accept only be use within a script.

 

Example:

ACCEPT dept PROMPT Department_name?

SELECT *

FROM DEPT

WHERE dname=UPPER(‘&dept’);

 

  • Save upper SQL as a script, name a.sql.

 

Output

SQL> @ a

Department_name?SALES

old   3: WHERE dname=UPPER(‘&dept’)

new   3: WHERE dname=UPPER(‘SALES’)

 

DEPTNO DNAME          LOC

——— ————– ————-

30 SALES          CHICAGO

 

 

 

DML (DATA MANUPULATION LANGUAGE):

  • A DML statement is executed when you;
  • Add new rows to a table.
  • Modify existing rows in a table
  • Remove existing rows from a table. Eg. DELETE Operator

 

 

Transaction:

  • A transaction is a collection of DML statement that form a logical unit of work. Eg. E-Commerce

 

STOCK                                  CUS_ORDER

                             DEL Operator                     Add or INSERT Operator

 

 

COMMIT:

  • Commit makes the changes permanent which are made in DATABASE.

 

 

 

INSERT:

  • Insert the row of data into a table.

 

 

Example:

SQL> INSERT INTO dept(deptno, dname, loc)

2  VALUES (50,’DEVELOPMENT’,’DETROIT’);

 

Output

1 row created.

 

 

SQL> INSERT INTO dept(deptno,loc)

2  VALUES (60,’KARACHI’);

 

Output

1 row created.

 

  • In upper query case dname will be NULL

 

 

Student Exercise2:

Write a script that takes input from the user the fields are department number, department name, and location and enters into department table.

 

Output

Enter value for deptno: 70

Enter value for dname: RESEARCH

Enter value for loc: DALLAS

old   2: VALUES (&deptno, ‘&dname’, ‘&loc’)

new   2: VALUES (70, ‘RESEARCH’, ‘DALLAS’)

 

1 row created.

Sub Queries

  • It is a query within a query.
  • A sub-query must be written in parenthesis.

 

 

 

Example:

 

List all employees whose salaries are greater than Jones salary

 

 

Query

 

SQL> select ename,sal

2  from emp

3  where sal >

4  (select sal

5  from emp

6  where ename=’JONES’);

 

 

Output

 

ENAME            SAL

———- ———

SCOTT           3000

KING            5000

FORD            3000

 

 

 

SINGLE ROW SUB-QUERY

 

 

  • It is a sub-query that returns one row only

 

 

S.E1

 

List the name and job of employees whose job title is same as that of 7369 and salary is greater than 7876.

 

 

Multiple column sub-queries

 

  • Multiple column sub-queries are used to make decisions based on multiple columns.
  • So multiple columns can be used for decisions making in a simplified manner by using multiple column sub-query.

 

 

SE2

 

Display order number, product number and quantity of any item in  which the product number and quantity match both the product number and quantity of an item in order 605.

 

 

SE3

 

Display order number, product number and quantity of any item in  which the product number and quantity match any of the product number and quantity of an item in order 605.

 

 

ILLEGAL QUERY!!!

 

Query

 

SQL> select empno,ename

2   from emp

3   where sal=(select min(sal)

4   from emp

5  group by deptno)

6  ;

where sal=(select min(sal)

 

Output

*

ERROR at line 3:

ORA-01427: single-row subquery returns more than one row

 

SE 4:

Display salaries of all employees whose salary is greater than average salary.

 

 

 

SE 5:

Display salaries of all employees whose salary is greater than average salary of department 20. DO not include any employee from department 20.

 

SE6:

Write a querry to display the employee number and name for all employees who work in a department with any employee whose name contains a ‘T’.