Thursday, November 10, 2011

Simple Select Statements (Part 1)

This post was tested on an Oracle 9 database. First create a small table and insert a few rows:
  
SQL> create table emp
  2  (name  varchar2(10),
  3  dob    date,
  4  salary number)
  5  /

Table created.

SQL> insert into emp values
  2  ('Andrew','01-JAN-1960',10000)
  3  /

1 row created.

SQL> insert into emp values
  2  ('Brian','02-FEB-1965',20000)
  3  /

1 row created.

SQL> insert into emp values
  2  ('Colin','03-MAR-1970',30000)
  3  /

1 row created.

SQL> insert into emp values
  2  ('David','04-APR-1975',40000)
  3  /

1 row created.

SQL> insert into emp values
  2  ('Edward','05-MAY-1980',50000)
  3  /

1 row created.


SQL>

You can count the number of rows in a table as follows:

SQL> select count(*) from emp
  2  /

  COUNT(*)
----------
        5


SQL>

  
This is how you select all the columns in every row:

SQL> select * from emp
  2  /

NAME       DOB           SALARY
---------- --------- ----------
Andrew     01-JAN-60      10000
Brian      02-FEB-65      20000
Colin      03-MAR-70      30000
David      04-APR-75      40000
Edward     05-MAY-80      50000


SQL>

The next query shows some of the columns from all of the rows:

SQL> select name, dob from emp
  2  /

NAME       DOB
---------- ---------
Andrew     01-JAN-60
Brian      02-FEB-65
Colin      03-MAR-70
David      04-APR-75
Edward     05-MAY-80


SQL>

Finally, to see all of the columns from some of the rows, you need to use a where clause:

SQL> select * from emp where name = 'Andrew'
  2  /

NAME       DOB           SALARY
---------- --------- ----------
Andrew     01-JAN-60      10000

SQL>

No comments: