Accessing Data in SQL Server

Expressions used in query

The expression used for defining a query consists of values, and different functions. The data can be easily calculated using the queries. The expressions represent formulas which are created in form of a query.

The general representation of expression is as mentioned below:

Code:

SELECT column1, column2, columnN 
FROM table_name
WHERE [ CONDITION | EXPRESSION ]

Where,

• column1, column2,…column are the list of columns used in the query
• table_name: Is the name of the table used in the query
• CONDITION : The particular query condition is defined
• EXPRESSION: The expression to be used in the query are expressed

Boolean Expression

Using the Boolean expression, the data matching a single value is accessed.

Syntax:

Code:

SELECT column1, column2, columnN FROM table_name 
WHERE SINGLE VALUE MATHCING EXPRESSION;

Example:

Code:

select empid, empname, salary from employee where salary > 50000

Output:


Date Expression

The date and time values from the system can be accessed using the date expression.

Example:

Code:

SELECT GETUTCDATE();

Output:

Code:

SELECT CURRENT_TIMESTAMP;

Output:


Numeric Expression

The mathematical calculations are performed using the numeric expression.

Syntax:

Code:

SELECT numeric_expression as OPERATION_NAME
[ FROM table_name WHERE CONDITION ];

Example:

Code:

Select AVG ( SALARY ) FROM employee

Accessing specific attributes

The data accessed from the tables can be displayed in more than one columns. User can retrieve the data from the tables available in the database. The SELECT statement is used for accessing the data from the tables in the database.

Code:

SELECT [ ALL | DISTINCT ] select column [ INTO [ new table_name ] ] 
FROM [ tablename ] where [ condition ] 

Where,

• ALL : The asterisk symbol defines the columns in the table
• DISTINCT: The unique rows from table are shown
• column: The column or aggregate columns are listed
• INTO: A new table is created and values are added
• new table_name: The name of the new table
• FROM tablename: The table name from where the data is accessed
• WHERE: The condition for accessing rows is defined
• condition: The condition is fulfilled for returning rows

Example:

Code:

select * from employee

Output:

Calculating column values

Sometimes, there is a need for calculating the values of a specific column. Consider a student table containing marks of the student. You need to calculate the percentage of the respective student. The arithmetic operators helps user to calculate the percentage.

The arithmetic operators which are used for performing the operations are addition, subtraction, multiplication, division and modulo. The SELECT statement is used along with these arithmetic operators.

The following query is used for calculating the percentage of the student.

Code:

select studid, studname, Percentage = ( marks * 100 ) * 0.01 from student 

Output:

Retrieving data by selecting the rows

Comparison operators for defining condition

Comparison operators are used for accessing the data using the SELECT statement along with the operators. The condition for accessing the specific data is defined using WHERE clause. The comparison operators are not used on image, text, ntext data type.

Syntax:

Code:

SELECT column(s) FROM table WHERE expression1 compare_operator expression2

Where,

expression1, expression2 are some constant value containing variable, function or column expression. The various comparison operators supported by SQL are as mentioned below:

1. > : Greater Than
2. = : Equal To
3. < : Less Than
4. <> , != : Not Equal
5. <= : Less Than or equal To
6. >= : Greater Than
7. !>: Not greater than
8. !< : Not less than

Example:

Code:

select * from student where marks > 70

Output:



Accessing values that satisfy more than one condition

The logical operators are used for accessing records dependent on more than one condition. The SELECT statement is used for retrieving values. The WHERE clause is used for defining the condition used for connecting them.

Syntax:

Code:

SELECT columns FROM table WHERE condition1 ( AND/OR ) [ NOT ] condition2

The different logical operators used in the query are:

1. AND: It is used for combining the two different conditions and providing a value.

Example:

Code:

select * from employee where empid = 112 and location = ‘London’

Output:


2. OR: If at least one of the two condition is satisfied, the result is a true value.

Code:

select * from employee where empid = 114 or location = ‘London’

Output:


3. NOT: It is used for reversing the condition.

Example:

Code:

select * from employee where NOT empid = 112

Output:


Retrieving data from specific range

Using the range operators, the values can be accessed for a particular range.
Syntax:

Code:

SELECT column(s) FROM table WHERE expression1 range_operator expression 2 AND expression 3

The various range operators used in SQL are as mentioned below:

1. BETWEEN: The values in the particular range are defined.

Example:

Code:

select * from student where marks between 60 and 85

Output:


2. NOT BETWEEN: The values from the particular range are not selected.

Example:

Code:

select * from student where marks not between 60 and 85

Output:


Accessing data that matches pattern

There is a need for selecting data which match a specific pattern. The LIKE keyword is used for searching the string using the wildcards.

The different wildcards used in the query are as mentioned below:

1. [ ] : Used for defining a single character in the particular range.
2. %: Used for representing any string containing zero or more characters
3. _: Defines a single character in the string
4. [ ^ ] : The single character not in a particular range is defined

Example:

Code:

select * from student where studname LIKE ‘J%’

Output:


Representing data in a sequential pattern

The ORDER BY clause is used along with the SELECT statement for showing the data in a sequential manner.

Syntax:

Code:

SELECT list FROM table [ ORDER BY expression [ ASC | DESC ] 

Example:

Code:

select studid, marks from student ORDER BY marks ASC

Output:


Accessing data containing NULL values

The NULL value for a column defines the data value for a specific column which is not present. The unknown_value_operator is used in the queries for defining the data.

Syntax:

Code:

SELECT column(s) FROM table WHERE column unknown_value_operator

Example:

Code:

select * from employee where dateofjoining IS NULL

Retrieving data from top of the table

The top values from the table are accessed using the TOP keyword. You can assign the number of records or a percent rows which are returned from the result.
Syntax:

Code:

SELECT [ TOP n [ PERCENT ] ] column(s) FROM table 
WHERE condition [ ORDER BY [ column1, column2,…] ] 

Example:

Code:

select TOP 3 * from employee

Output: