Table of contents:

  1. How to display length of fieald in table?
  2. How to select unique name?
  3. Dipslay all departments - example
  4. Select employee with WHERE clause, checking employee with the name equal to: ‘Jessica’
  5. Check employee with the last name starting from: ‘J’
  6. Check employee with: ‘e’ character in name
  7. Example of filtering on data value
  8. Create a column aliases

Ok, let’s practice basic database queries, SQL Oracle Database. Below is the database schema

Database Schema
Database Schema
  • How to display length of fieald in table?

select first_name, length(first_name) from employees; 

Output:

FIRST_NAME           LENGTH(FIRST_NAME)
-------------------- ------------------
Ellen                                 5
Sundar                                6
Mozhe                                 5
David                                 5
Hermann                               7
Shelli                                6
Amit                                  4
Elizabeth                             9
Sarah                                 5
David                                 5
Laura                                 5

FIRST_NAME           LENGTH(FIRST_NAME)
-------------------- ------------------
Harrison                              8
Alexis                                6
Anthony                               7
Gerald                                6
Nanette                               7
John                                  4
Kelly                                 5
Karen                                 5
Curtis                                6
Lex                                   3
Julia                                 5
  • How to select unique name?

select distinct(first_name) from employees;

Output:

Ellen
Mozhe
Hermann
Alberto
Britney
Jean
Timothy
Ki
Nancy
Peter
Michael
Shelley
Steven
Samuel
Christopher
Lindsey
Sigal
Jose Manuel

...
  • (standard test data)
  • Dipslay all departments

select * from Departments;

Output:

DEPARTMENT_ID DEPARTMENT_NAME                MANAGER_ID LOCATION_ID
------------- ------------------------------ ---------- -----------
           10 Administration                        200        1700
           20 Marketing                             201        1800
           30 Purchasing                            114        1700
           40 Human Resources                       203        2400
           50 Shipping                              121        1500
           60 IT                                    103        1400
           70 Public Relations                      204        2700
           80 Sales                                 145        2500
           90 Executive                             100        1700
          100 Finance                               108        1700
          110 Accounting                            205        1700

...
  • Select employee with WHERE clause, checking employee with the name equal to: ‘Jessica’

select first_name, last_name, salary from employee
where first_name = 'Jessica';
Jessica	Elliott	21000
Jessica	Sullivan	21000
Jessica	Bennett	83000
  • Check employee with the last name starting from: ‘J’

select employee_id, first_name, last_name
from employee
where last_name like 'J%';

Ouptut:

9	Kathleen Jones
44	Philip	Jordan
45	Lori	Jacobs
99	Mark	Jackson
108	Frank	Jones
  • Check employee with: ‘e’ character in name

select employee_id, first_name, last_name
from employee
where last_name like '%e%';
EMPLOYEE_ID FIRST_NAME                                                                                           LAST_NAME                                                                                           
----------- ---------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------
          1 Michelle                                                                                             Foster                                                                                              
          2 Cheryl                                                                                               Turner                                                                                              
          4 Patrick                                                                                              Berry                                                                                               
          5 Doris                                                                                                Powell                                                                                              
          9 Kathleen                                                                                             Jones                                                                                               
         11 Norma                                                                                                Henry                                                                                               
         12 Evelyn                                                                                               Gonzalez                                                                                            
         14 Bruce                                                                                                Mitchell                                                                                            
         17 Barbara                                                                                              Morales                                                                                             
         18 Ralph                                                                                                Anderson                                                                                            
         19 Lois                                                                                                 Butler                                                                                              

EMPLOYEE_ID FIRST_NAME                                                                                           LAST_NAME                                                                                           
----------- ---------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------
         23 Teresa                                                                                               Bell                                                                                                
         24 Anthony                                                                                              Rice                                                                                                
         25 Joseph                                                                                               Berry                                                                                               
         26 Alice                                                                                                Perry                                                                                               
         27 Ralph                                                                                                Montgomery                                                                                          
         28 Roy                                                                                                  Lane                                                                                                
         29 Kenneth                                                                                              Bennett                                                                                             
         33 Donna                                                                                                Martinez                                                                                            
         35 Robert                                                                                               Gilbert                                                                                             
         38 David                                                                                                Bailey                                                                                              
         39 Fred                                                                                                 Wallace                           
  • Example of filtering on data value

select employee_id, first_name, last_name, salary, department_id, hire_date 
from employee
where first_name = 'John' or last_name = 'Diaz';

Output:

EMPLOYEE_ID FIRST_NAME                                                                                           LAST_NAME                                                                                                SALARY DEPARTMENT_ID HIRE_DATE
----------- ---------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------- ---------- ------------- ---------
         81 Joyce                                                                                                Diaz                                                                                                     113000             2 03-NOV-10
        116 John                                                                                                 Diaz                                                                                                      75000             2 25-MAY-16
        150 Heather                                                                                              Diaz                                                                                                      63000             5 03-AUG-12
        173 John                                                                                                 Hayes                                                                                                     85000             8 23-MAR-14
        177 Craig                                                                                                Diaz            
  • Create a column aliases

select e.employee_id as "Employee", e.first_name as "First name", e.last_name as "Last name", e.salary as "Salary", e.salary/12 as "1 month salary",
e.salary/56 as "56/salary", e.salary * 12 as "Salary multiply per 12"
from employee e;

Output:

EMPLOYEE_ID FIRST_NAME                                                                                           LAST_NAME                                                                                                SALARY DEPARTMENT_ID HIRE_DATE
----------- ---------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------- ---------- ------------- ---------
         81 Joyce                                                                                                Diaz                                                                                                     113000             2 03-NOV-10
        116 John                                                                                                 Diaz                                                                                                      75000             2 25-MAY-16
        150 Heather                                                                                              Diaz                                                                                                      63000             5 03-AUG-12
        173 John                                                                                                 Hayes                                                                                                     85000             8 23-MAR-14
        177 Craig                                                                                                Diaz                                                                                                     114000             5 24-DEC-13

no rows selected

  Employee First name                                                                                           Last name                                                                                                Salary 1 month salary  56/salary Salary multiply per 12
---------- ---------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------- ---------- -------------- ---------- ----------------------
         1 Michelle                                                                                             Foster                                                                                                    48000           4000 857.142857                 576000
         2 Cheryl                                                                                               Turner                                                                                                    79000     6583.33333 1410.71429                 948000
         3 Carolyn                                                                                              Hudson                                                                                                    47000     3916.66667 839.285714                 564000
         4 Patrick                                                                                              Berry                                                                                                     51000           4250 910.714286                 612000
         5 Doris                                                                                                Powell                                                                                                   117000           9750 2089.28571                1404000
         6 Jessica                                                                                              Elliott                                                                                                   21000           1750        375                 252000
         7 Sean                                                                                                 Burns                                                                                                     51000           4250 910.714286                 612000
         8 Ann                                                                                                  Bowman                                                                                                    34000     2833.33333 607.142857                 408000
         9 Kathleen                                                                                             Jones                                                                                                     92000     7666.66667 1642.85714                1104000
        10 Scott                                                                                                Cruz                                                                                                      32000     2666.66667 571.428571                 384000
        11 Norma                                                                                                Henry             

Reference:

  1. Oracle SQL Developer
  2. Microsoft SQL documentation