06 - Retrieving Data

6.1 Retrieving Specific attributes

Retrieving data from the tables displays value of one or more columns. The SELECT statement can retrieve the required data from the database tables.

The SELECT statement is used to access and retrieve data. The syntax of the SELECT statement is as shown below:

ALL is represented with the ( * ) asterisk symbol and displays all the columns of the table.

DISTINCT specifies only unique rows should appear in the result set.

select_column_list is used to list the columns or aggregate columns for the listed data.

INTO creates a new table and inserts the resulting rows from the query into it.

new_table_name is the name of the new table to be created.

FROM table_name is the name of the table from which the data is to be retrieved.

WHERE specifies the search condition for the rows returned by the query.

search_condition specifies the condition to be satisfied to return the selected rows.

Consider an example of STUDENT table. To display the details of the STUDENT table execute the following query.

SELECT * from STUDENT

The output for the query is as follows:

The result set displays the records stored in the source table.

Customizing the Display

User needs to change the way the data is displayed. If the name of the column is not descriptive, user needs to change the column headings by adding user defined headings.

Consider an example of the Department table. User wants only the Department Number and Department Name column from the table. The column headings should be different from the table.

SELECT DepartmentID AS ‘Department Number’, NAME as ‘Department Name’ FROM Department

 

Department Number

Department Name

12

Engineering

10

Finance

11

Production

15

Marketing

17

Quality Assurance

 

Concatenating the text values in the Output

Concatenation is the operation where two strings are combined to make a single string. The concatenation operator is used to concatenate the string expressions. It is represented by the + sign. To concatenate the string, use the following query.

SELECT ‘snow’ + ‘ball’

The output for the query is snowball.

 

6.2 Retrieving Selected Rows

A column can contain different values and records. At some time user might need some records to be accessed. To retrieve the selected rows based on the specific condition, use the WHERE clause in the SELECT statement.

Using the comparison operators to specify the condition

User can specify the condition in the SELECT statement to retrieve selected rows by using the comparison operators. They allow the row retrieval based on the condition specified in the WHERE clause. The operator cannot be used on image, text, or ntext data type. The syntax for the comparison operator is as shown below:

Where,

expression1 and expression2 are the combination of constant, a variable, a function or column based expression.

Consider an example of Employee table where the holiday days is less than 6. The Employee table is as mentioned below:

EmpID

Designation

Location

Holidaydays

E101

Developer

Germany

5

E102

Architect

Holland

7

E103

Test Analyst

Belgium

4

E104

Database Manager

Paris

8

E105

Developer

Amsterdam

9

E106

Test Lead

London

2

 

The query for retrieving the appropriate data is as shown below:

SELECT EmpID, Designation, Holidaydays FROM Employee where Holidaydays <6

The output table for the query is as shown below:

EmpID

Designation

Holidaydays

E101

Developer

5

E103

Test Analyst

4

E106

Test Lead

2

 

The following table lists the comparison operators supported by the SQL Server.

Operators

Description

=

Equal to

>

Greater than

<

Less than

>=

Greater than or equal to

<=

Less than or equal to

<>, !=

Not equal to

!<

Not less than

!>

Not greater than

 

Retrieving records that match one or more conditions

Logical operators are used in SELECT statement are used to retrieve the records based on one or more conditions. User can add the logical operator to apply multiple search conditions. The SELECT statement, the conditions are specified in the WHERE clause are connected by using the logical operators. The syntax for the logical operator is as shown below:

Where,

conditional_expression1 and conditional_expression2 are any conditional expressions.

The three types of logical operators are as follows:

1) OR: It returns the value when at least one condition is satisfied. Consider the following SQL query that retrieves the records from the Department table where the designation is either ‘Manager’ or ‘Team Lead’

SELECT * FROM Department WHERE designation = ‘Manager’ OR designation = ‘ Team Lead’

2) AND: It is used to join the two conditions and returns the true value when the conditions are satisfied. Consider the query for retrieving data from the Department table where the designation is ‘Architect’ and Gender is ‘Male’.

SELECT * FROM Department WHERE Designation= ‘Architect’ AND Gender=’M’

3) NOT: It reverses the result of the search conditions. The following query returns the records of the employee where designation is not ‘Team Lead’.

SELECT * FROM Department WHERE NOT Designation= ‘Team Lead’

The above query retrieves rows except the rows that match the condition specified after the NOT conditional expression.

Retrieving Records that contain values in a given range

Range operators retrieve data based on a range. The syntax for the operator is as shown below:

Where,

expression1, expression2 and expression3 are any valid combination of constants, variables, functions or column based expressions.

range_operator is any valid range operator.

They have the following types.

BETWEEN: It specifies an inclusive range to search. The following query where the total vacation hours that an employee can go for a vacation is between 20 and 50.

SELECT EmpID, vacationHours from Employee WHERE vacationHours BETWEEN 20 AND 50

NOT BETWEEN: It excludes the range from the result set. The following query retrieves the records from the Employee table where the number of hours for the vacation is not between 40 AND 60.

SELECT EMPID, vacationHours from Employee WHERE vacationHours NOT BETWEEN 40 AND 60

Retrieving Records that contain value from a given set of values

In some circumstances, user needs to retrieve data after specifying the set of values to check whether the specified value is matched with the table data. The IN and NOT IN keywords are used for performing the operation.

Where,

expression is any valid combination of constants, variables and column based expressions.

list_operator is any valid list operator, IN or NOT IN

value_list is the list of values to be included or excluded in the condition.

The following query selects the records of the employees whose Location is ‘London’ or ‘Germany’.

SELECT EmpID, Location FROM Department WHERE Location IN (‘London’, ‘Germany’)

The NOT IN keyword restricts the selection of values that matches from the list of values. The following query retrieves records whose Location is not ‘London’ or ‘Germany’

SELECT EmpID, Location FROM Department WHERE Location NOT IN (‘London’, ‘Germany’)

Like us on Facebook