Inserting a New Record - SQL Server

Problem

You want to insert a new record into a table. For example, you want to insert a new record into the DEPT table. The value for DEPTNO should be 50, DNAME should be "PROGRAMMING", and LOC should be "BALTIMORE".

Solution

Use the INSERT statement with the VALUES clause to insert one row at a time:

 insert into dept (deptno,dname,loc)
 values (50,'PROGRAMMING','BALTIMORE')

For DB2 and MySQL you have the option of inserting one row at a time or multiple rows at a time by including multiple VALUES lists:

 /* multi row insert */
 insert into dept (deptno,dname,loc)
 values (1,'A','B'),
        (2,'B','C')

Discussion

The INSERT statement allows you to create new rows in database tables. The syntax for inserting a single row is consistent across all database brands.

As a shortcut, you can omit the column list in an INSERT statement:

 insert into dept
 values (50,'PROGRAMMING','BALTIMORE')

However, if you do not list your target columns, you must insert into all of the columns in the table, and be mindful of the order of the values in the VALUES list; you must supply values in the same order in which the database displays columns in response to a SELECT * query.

** If you want the Full Table detail. Refer the SQL Table in Label List. Or Click here to View the Table

Using NULLs in Operations and Comparisons **SQL**

Problem

NULL is never equal to or not equal to any value, not even itself, but you want to evaluate values returned by a nullable column like you would evaluate real values. For example, you want to find all employees in EMP whose commission (COMM) is less than the commission of employee "WARD". Employees with a NULL commission should be included as well.

Solution

Use a function such as COALESCE to transform the NULL value into a real value that can be used in standard evaluation:

 1 select ename,comm
 2   from emp
 3  where coalesce(comm,0) < ( select comm
 4                                  from emp
 5                                 where ename = 'WARD' )

Discussion

The COALESCE function will return the first non-NULL value from the list of values passed to it. When a NULL value is encountered it is replaced by zero, which is then compared with Ward's commission. This can be seen by putting the COALESCE function in the SELECT list:

 
 select ename,comm,coalesce(comm,0)
   from emp
  where coalesce(comm,0) < ( select comm
                                  from emp
                                 where ename = 'WARD' )

  ENAME            COMM COALESCE(COMM,0)
  ---------- ---------- ----------------
  SMITH                                0
  ALLEN             300              300
  JONES                                0
  BLAKE                                0
  CLARK                                0
  SCOTT                                0
  KING                                 0
  TURNER              0                0
  ADAMS                                0
  JAMES                                0
  FORD                                 0
  MILLER                               0

** If you want the Full Table detail. Refer the SQL Table in Label List. Or Click here to View the Table

Returning Missing Data from Multiple Tables : SQL Server

Problem

You want to return missing data from multiple tables simultaneously. Returning rows from table DEPT that do not exist in table EMP (any departments that have no employees) requires an outer join. Consider the following query, which returns all DEPTNOs and DNAMEs from DEPT along with the names of all the employees in each department (if there is an employee in a particular department):

 
 select d.deptno,d.dname,e.ename
   from dept d left outer join emp e
     on (d.deptno=e.deptno)

    DEPTNO DNAME          ENAME
 --------- -------------- ----------
        20 RESEARCH       SMITH
        30 SALES          ALLEN
        30 SALES          WARD
        20 RESEARCH       JONES
        30 SALES          MARTIN
        30 SALES          BLAKE
        10 ACCOUNTING     CLARK
        20 RESEARCH       SCOTT
        10 ACCOUNTING     KING
        30 SALES          TURNER
        20 RESEARCH       ADAMS
        30 SALES          JAMES
        20 RESEARCH       FORD
        10 ACCOUNTING     MILLER
        40 OPERATIONS

The last row, the OPERATIONS department, is returned despite that department not having any employees, because table EMP was outer joined to table DEPT. Now, suppose there was an employee without a department. How would you return the above result set along with a row for the employee having no department? In other words, you want to outer join to both table EMP and table DEPT, and in the same query. After creating the new employee, a first attempt may look like this:

 insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno)
 select 1111,'YODA','JEDI',null,hiredate,sal,comm,null
   from emp
  where ename = 'KING'

 
 select d.deptno,d.dname,e.ename
   from dept d right outer join emp e
     on (d.deptno=e.deptno)

     DEPTNO DNAME        ENAME
 ---------- ------------ ----------
         10 ACCOUNTING   MILLER
         10 ACCOUNTING   KING
         10 ACCOUNTING   CLARK
         20 RESEARCH     FORD
         20 RESEARCH     ADAMS
         20 RESEARCH     SCOTT
         20 RESEARCH     JONES
         20 RESEARCH     SMITH
         30 SALES        JAMES
         30 SALES        TURNER
         30 SALES        BLAKE
         30 SALES        MARTIN
         30 SALES        WARD
         30 SALES        ALLEN
                         YODA

This outer join manages to return the new employee but lost the OPERATIONS department from the original result set. The final result set should return a row for YODA as well as OPERATIONS, such as the following:

     DEPTNO DNAME        ENAME
 ---------- ------------ --------
         10 ACCOUNTING   CLARK
         10 ACCOUNTING   KING
         10 ACCOUNTING   MILLER
         20 RESEARCH     ADAMS
         20 RESEARCH     FORD
         20 RESEARCH     JONES
         20 RESEARCH     SCOTT
         20 RESEARCH     SMITH
         30 SALES        ALLEN
         30 SALES        BLAKE
         30 SALES        JAMES
         30 SALES        MARTIN
         30 SALES        TURNER
         30 SALES        WARD
         40 OPERATIONS
                          YODA

Solution

Use a full outer join to return missing data from both tables based on a common value.

DB2, MySQL, PostgreSQL, SQL Server

Use the explicit FULL OUTER JOIN command to return missing rows from both tables along with matching rows:

 1 select d.deptno,d.dname,e.ename
 2   from dept d full outer join emp e
 3     on (d.deptno=e.deptno)

Alternatively, union the results of two different outer joins:

 1 select d.deptno,d.dname,e.ename
 2   from dept d right outer join emp e
 3     on (d.deptno=e.deptno)
 4  union
 5 select d.deptno,d.dname,e.ename
 6   from dept d left outer join emp e
 7     on (d.deptno=e.deptno)

Oracle

If you are on Oracle9i Database or later, you can use either of the preceding solutions. Alternatively, you can use Oracle's proprietary outer join syntax, which is the only choice for users on Oracle8i Database and earlier:

 1 select d.deptno,d.dname,e.ename   
 2   from dept d, emp e
 3  where d.deptno = e.deptno(+)
 4  union
 5 select d.deptno,d.dname,e.ename
 6   from dept d, emp e
 7  where d.deptno(+) = e.deptno

Discussion

The full outer join is simply the combination of outer joins on both tables. To see how a full outer join works "under the covers," simply run each outer join, then union the results. The following query returns rows from table DEPT and any matching rows from table EMP (if any).

 
 select d.deptno,d.dname,e.ename
   from dept d left outer join emp e
     on (d.deptno = e.deptno)

   DEPTNO DNAME          ENAME
   ------ -------------- ----------
       20 RESEARCH       SMITH
       30 SALES          ALLEN
       30 SALES          WARD
       20 RESEARCH       JONES
       30 SALES          MARTIN
       30 SALES          BLAKE
       10 ACCOUNTING     CLARK
       20 RESEARCH       SCOTT
       10 ACCOUNTING     KING
       30 SALES          TURNER
       20 RESEARCH       ADAMS
       30 SALES          JAMES
       20 RESEARCH       FORD
       10 ACCOUNTING     MILLER
       40 OPERATIONS

This next query returns rows from table EMP and any matching rows from table DEPT (if any):

 
 select d.deptno,d.dname,e.ename
   from dept d right outer join emp e
     on (d.deptno = e.deptno)

  DEPTNO DNAME          ENAME
  ------ -------------- ----------
      10 ACCOUNTING     MILLER
      10 ACCOUNTING     KING
      10 ACCOUNTING     CLARK
      20 RESEARCH       FORD
      20 RESEARCH       ADAMS
      20 RESEARCH       SCOTT
      20 RESEARCH       JONES
      20 RESEARCH       SMITH
      30 SALES          JAMES
      30 SALES          TURNER
      30 SALES          BLAKE
      30 SALES          MARTIN
      30 SALES          WARD
      30 SALES          ALLEN    
                        YODA

The results from these two queries are unioned to provide the final result set.

** If you want the Full Table detail. Refer the SQL Table in Label List. Or Click here to View the Table