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.

 

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s