LIKE , BETWEEN, IN, NOT

All keywords are used with WHERE.

LIKE

LIKE is used to map values with String including some characters. There are two special symbols: percent %, replaced for many characters and underscore _, replaced for only one character.

Examples Statement:

SELECT JOB_TITLE FROM JOBS

WHERE JOB_TITLE LIKE ‘S%’;

The results are job titles starting with S.

Examples Statement:

SELECT JOB_TITLE FROM JOBS

WHERE JOB_TITLE LIKE ‘S_l%’;

The results are job titles starting with S, any character at the second index, l at the third index.

BETWEEN

BETWEEN is used to check the condition when the values are in a range or not.

Examples Statement:

SELECT JOB_TITLE, MIN_SALARY FROM JOBS

WHERE MIN_SALARY >= 4000 AND MIN_SALARY <= 9000;

We could use BETWEEN … AND… for this case:

SELECT JOB_TITLE, MIN_SALARY FROM JOBS

WHERE MIN_SALARY BETWEEN 4000 AND 9000;

IN

Examples Statement:

SELECT JOB_TITLE, MIN_SALARY FROM JOBS

WHERE MIN_SALARY = 4000 OR MIN_SALARY = 9000 OR MIN_SALARY = 6000;

When we want to check the value that is on one set or not, we could use IN:

SELECT JOB_TITLE, MIN_SALARY FROM JOBS

WHERE MIN_SALARY IN (4000, 6000, 9000);

NOT

We could use NOT with BETWEEN, LIKE or IN:

SELECT JOB_TITLE, MIN_SALARY FROM JOBS

WHERE MIN_SALARY NOTE BETWEEN 4000 AND 9000;

WHERE

Structure:

SELECT COLUMNS FROM TABLE_NAME WHERE CONDITION;

CONDITION is a Boolean condition.

Examples Statement:

SELECT FIRST_NAME, SALARY FROM EMPLOYEES WHERE SALARY > 10000;

The statement returns records which salary is large than 10000. We could use other operators: 0, <, >, >=, <=, !=.

CONDITION could be many boolean conditions combined by AND or OR. The result of CONDITION follow Boolean rules, from left to right.

Examples Statement:

SELECT FIRST_NAME, SALARY

FROM EMPLOYEES

WHERE (SALARY < 10000 OR SALARY > 10000) AND MANAGER_ID = 101;

And we have the result:

Quotes

To add quotes to Oracle String, we could not write directly as other characters.

Normal characters:

SELECT ‘Study and Share’ AS “Blog” FROM DUAL;

However, when we do the same statement with quotes, there is an error:

SELECT ‘Blog’s Study and Share’ AS “Blog” FROM DUAL;

To avoid this error, we could add one more quote:

SELECT ‘Blog’’s Study and Share’ AS “Blog” FROM DUAL;

Or we could use special signs < and >, ( and ), { and }, [ and ] (mean that one open and one close); or normal charater:

SELECT q’<‘Blog’s Study and Share>’ AS “Blog” FROM DUAL;

Combine STRING on ORACLE

We could append String on ORACLE by ‘||’.

Examples Statement:

SELECT ‘Vietnam’ || ‘ ‘ || ‘ is beautiful’ AS “My country” FROM DUAL;

Examples Statement:

SELECT FIRST_NAME || ‘ ‘ || LAST_NAME AS “NAME” FROM EMPLOYEES;

We could use CONCAT(String1, String2) to append String. CONCAT function has two parameter, so when you want to combine more than 2 String, we need to use nest statements. For statements above, we could write them again:

SELECT CONCAT(‘Vietnam’, CONCAT(‘ ’, ‘is beautiful’))  FROM DUAL;

SELECT CONCAT(FIRST_NAME, CONCAT(‘ ’, LAST_NAME))  AS “NAME” FROM EMPLOYEES;

DISTINCT and COUNT

Examples Statement:

SELECT JOB_ID FROM EMPLOYEES;

This statement returns 107 rows (number of records of EMPLOYEES).

However, the result has duplicate JOB_IDs. When we want to display distinct JOB_ID, we use keyword DISTINCT on the SELECT statement.
Examples Statement:

SELECT DISTINCT JOB_ID FROM EMPLOYEES;

The result displays 19 different JOB_ID.

When we want to know number of records of a table, we use the COUNT keyword. We could use an asterisk or a name of column with the COUNT keyword.
Examples Statement:

SELECT COUNT(EMPLOYEE_ID) FROM EMPLOYEES;

Examples Statement:

SELECT COUNT(*) FROM EMPLOYEES;

We could use DISTINCT for COUNT when we want to know number of different values on the table.

Examples Statement:

SELECT COUNT(MANAGER_ID) FROM EMPLOYEES;

This statement returns 106 while the statement COUNT(*) returns 107. The reason is that COUNT(*) will 
count NULL values also.

Examples Statement:

SELECT COUNT(DISTINCT MANAGER_ID) FROM EMPLOYEES;

The result is 18 – the number of different MANAGER_ID on EMPLOYEES (including NULL values).

Select

Basic SELECT statement:

SELECT COLUMNS FROM TABLE_NAME;

COLUMNS may be:

·         *: all of fields of the table

·         A field name of the table

·         Many fields are separated by a comma ‘,’

Examples Statement:

SELECT 1 FROM DUAL;

Result:

Examples Statement:

SELECT 1 AS “DEMO” FROM DUAL;

Result:

Note: Dual is a special table existing in memory, means that data on this table will be lost after finish sessions.
Examples Statement:

SELECT TABLE_NAME FROM USER_TABLES

Note: USER_TABLES is the table storing information of user’s tables.
Examples Statement:

SELECT * FROM REGIONS;

The REGIONS table has 4 records and this statement returns all 4 records.
Examples Statement:

SELECT REGION_ID AS “ID”, REGION_NAME AS “NAME” FROM REGIONS;

This statement selects two columns: REGION_ID and REGION_NAME. We also could use alias to change the display names for columns.

Configure and Deploy Applications with Tomcat 7

This article will show us how to use Apache Tomcat as beginners. I illustrate on Window 7 OS.

Download

You browse the home address of Apache Tomcat http://tomcat.apache.org/ and easy to see download links on the left side.

When I write this article, the current version is 7.0. After you download the zip file, you extract to a location on your computer. Assume that we extract it to C:\Tomcat. You also should set environment variables for Tomcat, for example: TOMCAT_HOME = C:\Tomcat and add TOMCAT_HOME \bin to the PATH variable.

Configuration Users and Roles

First of all, we have to declare users details for Tomcat. We also need to configure the access roles for users. Tomcat has various roles for users. To add or modify users’ information, we open TOMCAT_HOME\conf\tomcat-users.xml to do it. From the Tomcat zip file, this file is empty with no user.

Users are declared with a <user> tag which have 3 attributes: username, password, and roles (could have many roles separated by a comma). We put <user> tags inside the <tomcat-users> tag.

<?xml version='1.0' encoding='utf-8'?>

<tomcat-users>

  <user username="admin" password="admin" roles="admin,manager,manager-gui"/>

  <user username="ducky" password="2013" roles="manager-gui"/>

</tomcat-users>

As examples above, user admin has password admin and 3 roles: admin, manager, and manager-gui. User ducky has password 2013 and two roles: manage-gui and admin-gui.

Note that, by default no user is included in the ‘manager-gui’ role required to operate the "/manager/html" web application (TOMCAT description). The ‘manager-gui’ allows users using the web application. The ‘admin-gui’ allows users checking and changing hosting (or the host manager role).

Configuration Server Ports and Host

To configure these information, we open file .

The default port of Tomcat is 8080. If you install it on your computer, you could connect by address: http://localhost:8080. To change connection port of Tomcat on web browsers (HTTP protocol), for instance, to 8081, we change as below:

 <Connector port="8081" protocol="HTTP/1.1" connectionTimeout="20000" redirectPort="8443" />

 Start and Stop Tomcat

To start Tomcat, you could run TOMCAT_HOME\bin\startup.bat. If you open Tomcat by browsers, you could see the GUI like this and configure and see Server status, applications, hosting.

To shutdow Tomcat, you could run TOMCAT_HOME\bin\shutdow.bat

Deploy applications on Tomcat

To deploy applications by war files, you start up Tomcat, then browse the manage applications by address http://localhost:8080/manager/html. Scroll down to the Deploy part, you could choose a war file and deploy it. After deployed, the application will appear on the Application list.

To deploy applications by html files, for instance, you could add the application by copy and past directly to the folder: TOMCAT_HOME\webapps. I create a folder Test (it is also the Test application), and put a simple index.html inside Test:

<html>

<body>

This is demo for applications

</body>

<html>

Then, we start Tomcat, and we could see from the Application list on application manager, the test application appears as others. Click on the link, the content of index.html of test application will be displayed.

Note: After we deploy applications (by wars or directly), we must restart Tomcat if it is running. Tomcat just recognizes applications on the webapps folder since the last time it starts.