Oracle DB for system administrators, part5

On previous parts I use only sample selects from single table. But this is just the begin, so lets create another table and link the tables
22. create new table
CREATE TABLE "ROMEO"."DEPT"
  (
    "DEPTNO" NUMBER(2,0),
    "DNAME"  VARCHAR2(14 BYTE),
    "LOC"    VARCHAR2(13 BYTE)
  )
  TABLESPACE "TEST01" ;

and fill it with data so will have name and location of departments
SQL> select * from dept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

SQL>
23. So lets select first name, last name of employee and name of department
SQL> SELECT hr.fname, hr.lname, dept.dname FROM hr, dept;

FNAME           LNAME           DNAME
--------------- --------------- --------------
Donald          OConnell        ACCOUNTING
Douglas         Grant           ACCOUNTING
Jennifer        Whalen          ACCOUNTING
...snip...
Kevin           Feeney          OPERATIONS
424 rows selected.

Hm, somethings wrong, we want to see only names of the person and name of department where he or she work. But see too many lines and very strange output. In this case we create so named Cartesian product or search. This is full combination of all rows from both tables. Lets imagine we have two tables, 1st with 100000 lines and second with 1000 lines. Cartesian search will produce 100 million of lines and we will overload the server. So be careful :)
Here I demonstrate how to use the exact column from table. The format is [Name of table].[Name of column] This help when you have columns with the same name in different tables.

24, So lets restrict the search and join both tables, mapping number of the department from table HR to the department number from table DEPT
SQL> SELECT hr.fname, hr.lname, dept.dname FROM hr, dept WHERE hr.deptno = dept.deptno;

FNAME           LNAME           DNAME
--------------- --------------- --------------
Jennifer        Whalen          ACCOUNTING
Michael         Hartstein       RESEARCH
Pat             Fay             RESEARCH
Susan           Mavris          OPERATIONS
Den             Raphaely        SALES
Alexander       Khoo            SALES
Shelli          Baida           SALES
Sigal           Tobias          SALES
Guy             Himuro          SALES
Karen           Colmenares      SALES

10 rows selected.

Hm, strange again, I see only 10 employees, but I have much more records in HR table
SQL> select count(*) from hr;

  COUNT(*)
----------
       106

SQL>

So lets check the DEPTNO form table HR and the same from table DEPT
SQL> select distinct deptno from hr;

    DEPTNO
----------
       100
        30
        20
        70
        90
       110
        50
        40
        80
        10
        60

11 rows selected.
SQL> select distinct deptno from dept;

    DEPTNO
----------
        30
        20
        40
        10

SQL>
Aha, in table DEPT I have information only for few departments, not all. Here as you see I use distinct keyword to get only unique department ID.

25. And at the end complicate the select, searching for employee with salary > 10000 (from known departments) and get name of employee, name of department and location
SQL> SELECT hr.fname || ' ' || hr.lname "Employee", dept.dname "Department", dept.loc "Location" FROM hr, dept WHERE hr.deptno = dept.deptno AND sal > 10000;

Employee                        Department     Location
------------------------------- -------------- -------------
Michael Hartstein               RESEARCH       DALLAS
Den Raphaely                    SALES          CHICAGO

Here I "rename" the names of columns to make output a little bit pretty and make it easy readable