1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Accessing Data in SQL Server

Discussion in 'SQL Server' started by MinalS, Apr 9, 2016.

  1. 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:
    [​IMG]

    Date Expression

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

    Example:
    Code:
    SELECT GETUTCDATE();
    
    Output:
    [​IMG]

    Code:
    SELECT CURRENT_TIMESTAMP;
    
    Output:
    [​IMG]

    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
    
    [​IMG]

    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:

    [​IMG]

    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:
    [​IMG]

    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:

    [​IMG]

    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:
    [​IMG]

    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:
    [​IMG]

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

    Example:
    Code:
    select * from employee where NOT empid = 112
    
    Output:
    [​IMG]

    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:
    [​IMG]

    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:
    [​IMG]

    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:
    [​IMG]

    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:
    [​IMG]

    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:
    [​IMG]
     

Share This Page