SELECT Query:





The SELECT statement is used to retrieve record from one or more tables. The select query determines that which of the columns should be included into the result of the query result. The SELECT statement is also used to form intricate queries that will return the data that you want to retrieve. The SELECT statement or query is the most common query used in the SQL and is also most flexible and extensive statement in the SQL standards.




The select statement is the query expression that uses a SELECT keyword. This statement begins with the SELECT keyword and also has a number of elements that will be used to form the expression.

SYNTAX

We can split up the syntax of the SLECT statement into various clauses. These clauses are further used to define or refine the queries so that the required data or the refined data can be returned. The syntax of SELECT statement is as follows:

SELECT colum_name

FROM table_reference

WHERE condition;

The FROM statement is used to give reference of the table through which the data will be extracted. The “WHERE” clause is used to set the condition that which type of data the user wants.

EXAMPLE

Consider the following example that uses the SELECT command:

Suppose you have a table that is named STUDENT as follows:

STUD_ID NAME DEPT MONFEE CRSNO CDTE
100 Tim Marketing 1000 SPSS 19/06/96
100 Faddy Marketing 1000 SURVEYS 10/07/96
140 Jones Accounting 1200 TAXACCT 12/08/97
110 John Info. System 1100 SPSS 14/07/96
110 James Info. System 1100 COBOL 22/07/96
190 Abraham Finance 1200 INVESTMENT 20/06/97
150 Louis Marketing 1000 SPSS 19/06/97
150 Stuart Marketing 1000 SYSANAL 20/07/97

Suppose you want to display the name, department, monthly fee, CRSNO and CDTE of the student whose student id is 140, then the SQL statements for this are as follows in which we used the SELECT statement, the FROM statement and the WHERE clause:

SELECT STUD_ID,

NAME,

DEPT,

MONFEE,

CRSNO,

CDTE

From Student WHERE STUD_ID = 140;

Then the result will be as follows:

STUD_ID NAME DEPT MONFEE CRSNO CDTE
140 Jones Accounting 1200 TAXACCT 12/08/97

If we omit the WHERE clause and do not specify the condition and also reduce one of the columns for example CRSNO then the result will be as follows:

STUD_ID NAME DEPT MONFEE CDTE
100 Tim Marketing 1000 19/06/96
100 Faddy Marketing 1000 10/07/96
140 Jones Accounting 1200 12/08/97
110 John Info. System 1100 14/07/96
110 James Info. System 1100 22/07/96
190 Abraham Finance 1200 20/06/97
150 Louis Marketing 1000 19/06/97
150 Stuart Marketing 1000 20/07/97

From the above example, we can conclude that the column CRSNO is deleted as we did not specified it into the list of columns when defining the SELECT statement.



EXAMPLE to select all the records

If the user wants to retrieve all the data from a table then he should use a asterisk with the SELECT statement. All data in all columns will be selected when using the asterisk with the select statement. Consider the following example:

SELECT * FROM Student;

The above statement will display the whole table. The result is as follows:

STUD_ID NAME DEPT MONFEE CRSNO CDTE
100 Tim Marketing 1000 SPSS 19/06/96
100 Faddy Marketing 1000 SURVEYS 10/07/96
140 Jones Accounting 1200 TAXACCT 12/08/97
110 John Info. System 1100 SPSS 14/07/96
110 James Info. System 1100 COBOL 22/07/96
190 Abraham Finance 1200 INVESTMENT 20/06/97
150 Louis Marketing 1000 SPSS 19/06/97
150 Stuart Marketing 1000 SYSANAL 20/07/97

 

EXAMPLE to show results based on some conditions

SELECT * FROM Student WHERE name = ‘Faddy’;

The result generated from the above statement will be as follows:

STUD_ID NAME DEPT MONFEE CRSNO CDTE
100 Faddy Marketing 1000 SURVEYS 10/07/96

 

EXAMPLE to perform simple calculations

Suppose you want to add 500 fee to the monthly fee of all the students then consider than you have a following table:

STUD_ID NAME DEPT MONFEE CRSNO CDTE
100 Tim Marketing 1000 SPSS 19/06/96
100 Faddy Marketing 1000 SURVEYS 10/07/96
140 Jones Accounting 1200 TAXACCT 12/08/97
110 John Info. System 1100 SPSS 14/07/96
110 James Info. System 1100 COBOL 22/07/96
190 Abraham Finance 1200 INVESTMENT 20/06/97
150 Louis Marketing 1000 SPSS 19/06/97
150 Stuart Marketing 1000 SYSANAL 20/07/97

Use the following statement to add 500 to the monthly fee of the students:

SELECT STUD_ID, name, DEPT, MONFEE, CRSNO, CDTE, MONFEE + 500 from Student;

In the above statement MONFEE is the column that is specifying the monthly fee, STUDENT is the name of the table and STUD_ID is the id of the student and 500 is added to MONFEE. The result for this statement will be as follows:

STUD_ID NAME DEPT MONFEE CRSNO CDTE MONFEE+ 500
100 Tim Marketing 1000 SPSS 19/06/96 1500
100 Faddy Marketing 1000 SURVEYS 10/07/96 1500
140 Jones Accounting 1200 TAXACCT 12/08/97 1700
110 John Info. System 1100 SPSS 14/07/96 1600
110 James Info. System 1100 COBOL 22/07/96 1600
190 Abraham Finance 1200 INVESTMENT 20/06/97 1700
150 Louis Marketing 1000 SPSS 19/06/97 1500
150 Stuart Marketing 1000 SYSANAL 20/07/97 1500





It can be seen that a new column is inserted to the right side of the table that displays the result when 500 is added to MONFEE that is monthly fee.