SELECT query

1.     SELECT query

-     We use this when we want to retrieve data from the database that we can view.

1.1  Using Arithmetic Operators

-          SELECT EMPNO, ENAME, SAL+500 AS New_Salary   FROM EMP;

·         SELECT EMPNO, ENAME, JOB, HIREDATE

  FROM EMP

WHERE JOB="ANALYST" OR HIREDATE>=#1/1/1987#;

·         SELECT EMPNO, ENAME, JOB, HIREDATE

  FROM EMP

WHERE Not JOB="ANALYST";

1.2  Concatenating Fields

-          SELECT [ENAME] & " was hired last " & [HIREDATE] AS REMARKS

   FROM EMP;

1.3  Using the WHERE clause

-    SELECT EMPNO, ENAME, JOB, HIREDATE

        FROM EMP

     WHERE JOB="ANALYST";

1.3.1       Using AND, OR and NOT

·         SELECT EMPNO, ENAME, JOB, HIREDATE
           FROM EMP
           WHERE JOB="ANALYST" AND HIREDATE>=#1/1/1987#;

  

·         SELECT EMPNO, ENAME, JOB, HIREDATE

  FROM EMP

WHERE JOB="ANALYST" OR HIREDATE>=#1/1/1987#;

·         SELECT EMPNO, ENAME, JOB, HIREDATE

  FROM EMP
WHERE Not JOB="ANALYST";

1.3.2       Using the BETWEEN Predicate

·         SELECT EMPNO, ENAME, JOB, HIREDATE

   FROM EMP

WHERE HIREDATE Between #1/1/1981# And #12/31/1987#;



1.3.3       Using the LIKE Predicate

·         SELECT EMPNO, ENAME, JOB

   FROM EMP

 WHERE ENAME Like "A*";

1.3.4       Using the IN Predicate

·         SELECT EMPNO, ENAME, JOB

   FROM EMP

 WHERE JOB In ("PRESIDENT","MANAGER");

1.3.5       Using the NULL Predicate

·         SELECT EMPNO, ENAME, COMM

   FROM EMP

WHERE COMM Is Null;

1.4  The ORDER BY Clause

·         SELECT EMPNO, ENAME, HIREDATE

   FROM EMP

 ORDER BY HIREDATE DESC;

1.5  The GROUP BY Clause

1.5.1       The Aggregate Functions

·         SELECT JOB, Sum(SAL) AS SUM_SAL, Count(EMPNO) AS EMP_DEPT

   FROM EMP

 GROUP BY JOB;

1.5.2       The Having Clause

·         SELECT JOB, Sum(SAL) AS SUM_SAL, Count(EMPNO) AS EMP_DEPT

   FROM EMP

  GROUP BY [JOB]

HAVING Count(EMPNO)>3;

1.5.3      The DISTINCT Clause

·         SELECT DISTINCT JOB

   FROM EMP;