Sorry, no posts matched your criteria.
The WHERE clause is used to extract only those records that fulfill a specified criterion.
SELECT column1,column2, … FROM tablename WHERE column1=’filter text on column1′;
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.
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.
WHERE EMPLOYEE_DEPARTMENT in (‘SALE’,’TECHNICAL’);
Requirement 3: Need a list of employee name and address of excluding some department i.e. exclude SALE.
WHERE EMPLOYEE_DEPARTMENT <> ‘SALE’;
Requirement 4: Need a list of employee name and address where employee age is greater than 20 years.
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.
WHERE EMPLOYEE_AGE >= 20;
Requirement 6: Need a list of employee name and address where employee age is less than 20 years.
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.
WHERE EMPLOYEE_AGE <= 20;
Requirement 8: Need a list of employee name and address where employee name contains ‘SINGH’.
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:
Requirement 9: Need a list of employee name and address where employee age is between 20 to 40 years.
WHERE EMPLOYEE_AGE BETWEEN (20,40);
This resultset will exclude the 20 years age employee whose age is equals to 20 years.
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:
Lets learn more about SQL Statement in next chapter.
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;
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;
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.
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:
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.
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:
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.
SQL has many built-in functions for performing calculations on data.
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.
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.
To build a web site that shows data from a database, you require:
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.
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
Before jumping into development it is necessary to learn the concept of 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.
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:
Below is one sample cache manifest file:
# 2012-02-21 v1.0.0