Latest News

Oracle ships emergency Java 7 update 17 to patch two vulnerabilities

15 Feb , 2015  

Oracle ships emergency Java 7 update 17 to patch two vulnerabilities

Latest News

US court questions Google’s claim that Oracle does not enjoy copyright protection over parts of Java in Android case

15 Feb , 2015  

US court questions Google’s claim that Oracle does not enjoy copyright protection over parts of Java in Android case

Latest News

US Court of Appeals grants Oracle the right to protect its Java software via copyright, a blow to Google

15 Feb , 2015  

US Court of Appeals grants Oracle the right to protect its Java software via copyright, a blow to Google

PL/SQL

SQL Where Clause

5 Feb , 2015  

The WHERE clause is used to extract only those records that fulfill a specified criterion.

Where Clause Syntax

SELECT column1,column2, … FROM tablename WHERE column1=’filter text on column1′;

Operator in Where clause

  • = Equal
  • <> Not equal. Note: In some versions of SQL this operator may be written as !=
  • > Greater than
  • < Less than
  • >= Greater than or equal
  • <= Less than or equal
  • BETWEEN Between an inclusive range
  • LIKE Search for a pattern
  • IN To specify multiple possible values for a column

 

Why SQL Where Clause

Scenario: We have a table EMPLOYEE that have employee record of an organisation with different department.

Requirement 1: Need a list of employee name and address of specific department i.e. SALE.

Solution:

SELECT EMPLOYEE_NAME,EMPLOYEE_ADDRESS

FROM EMPLOYEE

WHERE EMPLOYEE_DEPARTMENT = ‘SALE’;

 

Requirement 2: Need a list of employee name and address of specific department i.e. SALE and technical, which means only two or more department.

Solution:

SELECT EMPLOYEE_NAME,EMPLOYEE_ADDRESS

FROM EMPLOYEE

WHERE EMPLOYEE_DEPARTMENT in (‘SALE’,’TECHNICAL’);

 

Requirement 3: Need a list of employee name and address of excluding some department i.e. exclude SALE.

Solution:

SELECT EMPLOYEE_NAME,EMPLOYEE_ADDRESS

FROM EMPLOYEE

WHERE EMPLOYEE_DEPARTMENT <> ‘SALE’;

 

Requirement 4: Need a list of employee name and address where employee age is greater than 20 years.

Solution:

SELECT EMPLOYEE_NAME,EMPLOYEE_ADDRESS

FROM EMPLOYEE

WHERE EMPLOYEE_AGE > 20;

 

Requirement 5: Need a list of employee name and address where employee age is greater equals to 20 years, which means resulset will also include of employee’s whose age is equals to 20 years.

Solution:

SELECT EMPLOYEE_NAME,EMPLOYEE_ADDRESS

FROM EMPLOYEE

WHERE EMPLOYEE_AGE >= 20;

 

Requirement 6: Need a list of employee name and address where employee age is less than 20 years.

Solution:

SELECT EMPLOYEE_NAME,EMPLOYEE_ADDRESS

FROM EMPLOYEE

WHERE EMPLOYEE_AGE < 20;

 

Requirement 7: Need a list of employee name and address where employee age is less than equals to 20 years, which means resulset will also include of employee’s whose age is equals to 20 years.

Solution:

SELECT EMPLOYEE_NAME,EMPLOYEE_ADDRESS

FROM EMPLOYEE

WHERE EMPLOYEE_AGE <= 20;

 

Requirement 8: Need a list of employee name and address where employee name contains ‘SINGH’.

Solution:

SELECT EMPLOYEE_NAME,EMPLOYEE_ADDRESS

FROM EMPLOYEE

WHERE EMPLOYEE_NAME LIKE ‘%SINGH%’;

Here % is used with like opertor which is an wild card search which is usually used in search type of functionality.

Find below How wildcard search % works:

  1. WHERE EMPLOYEE_NAME LIKE ‘%SINGH%’; – It will give employee list whose name contains SINGH anywhere.
  2. WHERE EMPLOYEE_NAME LIKE ‘SINGH%’; – It will provide employee list whose name start with SINGH.
  3. WHERE EMPLOYEE_NAME LIKE ‘%SINGH’; – It will provide employee list whose name end with SINGH.
  4. WHERE EMPLOYEE_NAME LIKE ‘SINGH’; – It will provide employee list whose name is SINGH.

Requirement 9: Need a list of employee name and address where employee age is between 20 to 40 years.

Solution:

SELECT EMPLOYEE_NAME,EMPLOYEE_ADDRESS

FROM EMPLOYEE

WHERE EMPLOYEE_AGE BETWEEN (20,40);

This resultset will exclude the 20 years age employee whose age is equals to 20 years.

Text Fields vs. Numeric Fields

In case EMPLOYEE table consists EMPLOYEE_DEPARTMENT as text, what if we have foreign key DEPARTMENT table, and we have DEPARTMENT_ID as numeric i.e. 1 for SALE department, then query will be look like as below:

SELECT EMPLOYEE_NAME,EMPLOYEE_ADDRESS

FROM EMPLOYEE

WHERE EMPLOYEE_DEPARTMENT=1;

 

Lets learn more about SQL Statement in next chapter.

PL/SQL

Distinct SQL Keyword

5 Feb , 2015  

The SELECT DISTINCT statement is used to return distinct (different) values in column(s) of table.

 

Basic distinct keyword example:

SELECT DISTINCT COLUMN1,COLUMN2 FROM TABLENAME;

 

Why to use Distinct keyword?

Scenario: We have a table say DEPARTMENT wherein we have different rows with same department.

Requirement: We need list of department(NAME) without any duplication of department name.

Solution: SELECT DISTINCT DEPARTMENT_NAME FROM DEPARTMENT;

Description:

Here in above SQL statement, unique department name list will be populate, But if we have used distinct with the primary key whose combination is not duplicated then name can again be duplicated with primary key like wise below query:

SELECT DISTINCT DEPARTMENT_NAME,DEPARTMENT_ID FROM DEPARTMENT;

This will be better if you need unique department name, then not to use department id, since department id which is a primary key and generate new at each time and doesn’t matter whether name is same or not.

Lets learn more about SQL statement and lets go to next tutorial.

 

PL/SQL

Select SQL Statement

5 Feb , 2015  

SELECT is the keyword used in SQL Statement to retrieve data from table of database. SQL SELECT statement is used to fetch the data from a database table which returns data in the form of result table. These result tables are called result-set.

Basic SELECT query example:

SELECT * FROM EMPLOYEE;

 

Scenario: We have a table say EMPLOYEE, which consist of 20 column have lots of other data like EMPLOYEE_NAME, EMPLOYEE_ID, EMPLOYEE_ADDRESS, EMPLOYEE_DEPARTMENT and many more.

Requirement 1: Get the list of employee’s with EMPLOYEE_ID, EMPLOYEE_NAME.

Now to met the above requirement we have below two queries, which will suffice the requirement but may differ in performance:

  1. SELECT * FROM EMPLOYEE; , it will surely take more time since here we are getting all 20 columns of EMPLOYEE table.
  2. SELECT EMPLOYEE_ID, EMPLOYEE_NAME FROM EMPLOYEE, It will be taking less time since resultset is lesser than above case.

Requirement 2: Now if we need to get the EMPLOYEE’ who belong to a department:

Now we will use the where keyword or we say where clause in SQL technical language, so query will looks like below:

SELECT EMPLOYEE_ID, EMPLOYEE_NAME FROM EMPLOYEE WHERE EMPLOYEE_DEPARTMENT=’SALE';

 

As requirement change SQL Statement will also change, and to solve the complex requirement keep reading the next tutorial.

 

PL/SQL

Syntactics SQL

5 Feb , 2015  

As other languages SQL also has some rules while coding in SQL Language, Please find below some basic good to know before direct jumping into SQL Language:

  • SQL is NOT case sensitive like select is the same as SELECT
  • As Java and other language, Some SQL versions expect semicolon at the end of each SQL statement, Semicolon is the standard way to separate each SQL statement in database systems which is used to execute more than one SQL statement to be executed in the same call to the server.

SQL Statement

As other languages SQL development is built upon no of instructions written in a sequence which is called SQL Statement, Please find below a example of SQL Statement:

SELECT * FROM EMPLOYEE;

Where select is the keyword used in SQL syntax to retrieve data of EMPLOYEE i.e. SQL Table.

Some of The Most Important SQL Commands

  • SELECT – Recieve data from database
  • UPDATE – Updates the data in a database
  • DELETE – Deletes the row from table of database
  • INSERT INTO – Inserts new data into a database
  • CREATE DATABASE – Used to create a brand new database
  • ALTER DATABASE – Modifies a database
  • CREATE TABLE – Create a new table
  • ALTER TABLE – Modifiy table
  • DROP TABLE – Deletes table rows
  • CREATE INDEX – Creates an index
  • DROP INDEX – Remove index

 

Some of the most popular functions used in SQL Language

SQL has many built-in functions for performing calculations on data.

  • AVG() – Returns the average value of column
  • COUNT() – Returns the number of rows
  • FIRST() – Returns the first value
  • LAST() – Returns the last value
  • MAX() – Returns the largest value
  • MIN() – Returns the smallest value
  • SUM() – Returns the sum of column values
  • UCASE() – Converts a field to upper case
  • LCASE() – Converts a field to lower case
  • MID() – Extract characters from a text field
  • LEN() – Returns the length of a text field
  • ROUND() – Rounds a numeric field to the number of decimals specified
  • NOW() – Returns the current system date and time
  • FORMAT() – Formats how a field is to be displayed

 

PL/SQL

SQL Introduction

5 Feb , 2015  

SQL a shortform of Structured Query Language is a standard language for accessing and manipulating databases which provides immediate results. SQL is a language to send & receive data to-from database, it includes database creation, deletion, fetching rows and modifying rows.

SQL is an ANSI (American National Standards Institute) standard but there are many different versions of the SQL language.

 

What is SQL?

  • SQL stands for Structured Query Language
  • SQL lets you access and manipulate database(s) content
  • SQL is an ANSI (American National Standards Institute) standard

 

SQL Capabilities

  • SQL can execute queries against a database
  • SQL can retrieve data from a database
  • SQL can insert records in a database
  • SQL can update records in a database
  • SQL can delete records from a database
  • SQL can create new databases
  • SQL can create new tables in a database
  • SQL can create stored procedures in a database
  • SQL can create views in a database
  • SQL can set permissions on tables, procedures, and views

Keep in View SQL is a Standard – BUT….

SQL is an ANSI (American National Standards Institute) standard but there are different versions of the SQL language in market. However, to be compliant with the ANSI standard, they all support at least the major commands (such as SELECT, UPDATE, DELETE, INSERT, WHERE) in a same ways.

Using SQL in Your Web Site

To build a web site that shows data from a database, you require:

  • An RDBMS database program (i.e. MS Access, SQL Server, MySQL)
  • To access RDBMS, a server-side scripting language, like JAVA,PHP or ASP
  • To use HTML / CSS

 

RDBMS

RDBMS stands for Relational Database Management System.

RDBMS is the basis for SQL, and for all modern database systems such as Oracle, IBM DB2, MS SQL Server, MySQL, and Microsoft Access. The data in RDBMS is stored in database objects called tables.

A table is a collection of related data entries and it consists of columns and rows.

 

PL/SQL

SQL Startup

5 Feb , 2015  

SQL is a standard language for accessing databases.

Our SQL tutorial will teach you how to use SQL to access and manipulate data in: MySQL, SQL Server, Access, Oracle, Sybase, DB2, and other database systems.

As we have IDE for other coding language like eclipse or JDeveloper, TOAD is the IDE for SQL and lost of DBAs are very familiar with this IDE, so we will use it in our examples where neccessary.

Download the TOAD at http://www.quest.com/toad-for-sql-server/software-downloads.aspx

HTML5

Offline Web Application Development

5 Feb , 2015  

Before jumping into development it is necessary to learn the concept of OFFLINE WEB APPLICATION:

 

What is offline web application?

In simple words Offline web application means running the web applicatin even when there is no internet connection. Surprised, Yes no internet connection. Web pages are things which first download and then render. Downloading implies a network connection. How can you download when you’re offline? Of course, you can’t. But you can download when you’re online. And that’s how HTML5offline applications work.

 

How does it works?

At its simplest, an offline web application is list of URLs i.e. HTMLCSS, JavaScript, images, or any other kind of resource. The home page of the offline web application points to this list, called a manifest file, which is just a text file located elsewhere on the web server. A web browser that implements HTML5 offline applications will read the list of URLs from the manifest file, download the resources, cache them locally, and automatically keep the local copies up to date as they change. When the time comes that you try to access the web application without a network connection, your web browser will automatically switch over to the local copies instead.

HTML5 require to have one manifest file which will have all sorts of url you require to be available offline, now lets see how to configure a offline html file:

Declaring the browser to that cache the following html:

<!DOCTYPE html>
<html manifest="/teamerge.manifest">
<body>
...
</body>
</html>

Declaring the manifest means this html will be cached through browser.

Below is the basic syntax to include files in manifest file:

AddType text/cache-manifest .manifest

Manifest has below 3 headers which decide the behavior of Offline application:

  1. CACHE MANIFEST: In this section we define the all static files(i.e. HTML, CSS, Js or any image) require at the time of loading the Offline request.
  2. NETWORK: This will define the behaviour what sort of files will be accessible when user comes online.
  3. FALLBACK: As name suggesting fallback will decide what to call in absence.

 

Below is one sample cache manifest file:

CACHE MANIFEST
# 2012-02-21 v1.0.0
/css/bootstrap.css
/css/style.css
/css/circle.css
/js/modernizr.js
/js/respond.min.js
/js/jquery.autocomplete.js
/css/jquery.autocomplete.css

NETWORK:

*

FALLBACK:
/html/ /offline.html