SqlTutorial-1 : Lesson-3 : Class-2
Microsoft SQL Server Training:
Free Online-Learning Classes (OLC) for T-SQL
Basic SELECT- Operations:

Microsoft SQL Server Training Online Learning Classes Basic SELECT Operations
In this article, you will find all SELECT-Operations of T-SQL at one-place. It contains huge combinations of examples.

INDEX
  • Syntax
  • Examples
    1. Simple-SELECT (Without any Clause even no FROM)
    2. SELECT-FROM 
    3. SELECT-FROM-WHERE with OPERTORS
      • Relational Operators
      • Logical Operators
      • Pattern Matching Operators
      • List Operators li>
      • NULL-CHECKING Operators
      • SET Operators
    4. SELECT-FROM-WHERE-GROUP By-HAVING
    5. SELECT-FROM-ORDER By


Syntax

SELECT [DISTINCT] { * | column1[,column2..] | expressions }
   [INTO new_destTable ]
   [FROM table_source]
   [WHERE search_condition]
   [GROUP BY expression1[,..n] [WITH {CUBE | ROLLUP} ]
   [HAVING search_condition]
   [ORDER BY col1[ASC/DESC] [,col2…]]

The order of SELECT statement is as follows:
  1. FROM
  2. ON (joining)
  3. WHERE
  4. GROUP BY
  5. CUBE | ROLLUP
  6. HAVING
  7. SELECT
  8. DISTINCT
  9. ORDER BY
  10. TOP | ROW NUMBER | RANK | COMPUTE etc.


1. Simple-SELECT (Without any clause even no FROM)

  1. Displaying text message.

    SELECT 'Welcome ' AS message
  2. Displaying a result of expression.

    SELECT 1 + 6 / 2 AS RESULT => 4
  3. Displaying a result based on condition.

    SELECT CASE 2
    WHEN 1 THEN 'ONE '
    WHEN 2 THEN 'TWO '
    END
  4. Scripting through SELECT.

    SELECT 'THE SQUARE OF 2 IS ' + CONVERT(CHAR, square(2))
    -- THE SQUARE OF 2 IS 4
  5. SELECT with ALL BUILT-IN functions.

    SELECT SQRT(4)   => 2
    SELECT POWER(2,4) =>16
    SELECT SIN(90)    => 1

2. SELECT-FROM 

  1. Selecting all columns of a table.

    SELECT * FROM employee
  2. Selecting particular column of a table.

    SELECT Id, name FROM employee
  3. T-SQL is case insensitive.

    SELECT Id, name FROM employee
    = SELECT ID,NAME FROM EMPLOYEE
  4. Using ALIAS with tables

    SELECT t1.*, t2.Id
    FROM Table1 AS t1, Table2 AS t2;
  5. Arithmetic operations on columns (AS clause / ALIASE with columns)

    SELECT col1+col2 AS ADDITION
    FROM Tablen 1

    col1–col2, col1/col2, and col1 * col2 is also possible.
  6. Complex ALIASE (integer, multiple words etc): significance of [ ]

    SELECT name AS [MY NAME]
    FROM Table1

    Giving Numbers as alias also requires “[ ]” as [10], otherwise it is compilation error.
  7. Eliminating duplicate values from results.

    SELECT DISTINCT name, salary
    FROM employee

    • You cannot SELECT DISTRINCT * . It is compilation error.
    • You can select MULTIPLE DISTRINCT as SELECT DISTINCT col1,DISTINCT col2
  8. Implementing MULTIPLE DISTINCT (directly not possible)

    SELECT name, salary
    FROM employee
    GROUP BY name, salary
  9. Sorting of data.(ORDER BY clause)

    SELECT DISTINCT name, salary FROM employee
    ORDER BY name DESC          -- Default is ASC
  10.  SELECT with CASE clause(Condition based result)

    SELECT CASE 
        WHEN (salary BETWEEN 1000 AND 5000) THEN 'low salary'
        WHEN (salary BETWEEN 5000 AND 10000) THEN 'avg salary'
        WHEN (salary BETWEEN 10000 AND 15000) THEN 'above avg'
        WHEN (salary BETWEEN 150000 AND 20000) THEN 'Good'
      END
    FROM employee

    In SELECT statement, CASE clause cannot be in FROM, WHERE clauses.
  11. Defining a new table using existing table. (SELECT INTO clause)

    SELECT * INTO NewTemp FROM Employee

    Note: It automatically creates a new table (here name NewTemp ) with all columns of Employee.
  12. Defining a new table with additional column of type “date”.

    SELECT *, CAST(NULL AS datetime) AS col1 INTO temp2 FROM Table1


3. SELECT-FROM-WHERE with OPERATORS

Examples SELECT with WHERE etc.


In WHERE clause almost all operators such as relational, Logical, Range, LIKE. But some operators such as SET operators, CUBE, ROLL UP, etc are not allowed in WHERE clause.

Operator
Description
Example
1: RELATIONAL OPERATORS
=
Equals to
WHERE ename =’abc’
!= or <>
Not equals to
WHERE salary != 5000
Grater than
WHERE salary > 5000
Less than
WHERE salary < 1000
>=
Greater than or equals to
WHERE salary >= 6000
<=
Less than or equals to
WHERE salary <= 6000
!>
Not greater than
WHERE salary !> 6000
!<
Not less than
WHERE salary !> 6000
2: LOGICAL OPERATORS
AND
Logical AND
WHERE salary>1000 AND salary<2000
OR
Logical OR
WHERE salary=1000 OR salary=2000
NOT
Logical NOT
WHERE salary NOT= 2000
WHERE salary NOT NULL
WHERE salary NOT BETWEEN 1000 AND 2000
3: PATTERN MATCHING

Symbol  Description
%               Matches any string of zero or more characters.
_                Matches any single character.
[ ]               Matches a single character listed within the brackets.
[ - ]             Matches a single character within the given range.
[ ^ ]            Matches a single character not listed after the caret.
ESCAPE   Ignores specified character.
[NOT] LIKE
WHERE name LIKE 'J%'
WHERE name LIKE '_a%' รจ 2nd char “a”
WHERE name LIKE '[C-E]%'starts C,D,E
WHERE name LIKE '[^C-E]%' not starts C,D,E
WHERE BankerCity LIKE 'SAN%'
WHERE BankerName LIKE 'DAMI[EO]N'
WHERE BankerState LIKE 'M[A-J]'
WHERE BankerState LIKE 'M[^K-Y]'
WHERE BankerZipCode NOT LIKE '[1-9]%'รจ000
WHERE name NOT LIKE '[^J-O]%'           =
WHERE  NOT ( first_name LIKE '[^J-O]%') =
WHERE  name LIKE '[^J-O]%'

//Any of the wildcard characters (%, _' [ ], or ?) enclosed in square brackets stand for themselves.
WHERE first_name LIKE '%[_]%' รจ Jon_Wiliam

//Find the names begins with letter A-Z except B
WHERE(name LIKE '[ABCDEFGHIJKLMNOPQRSTUVWXYZ]%' ESCAPE 'B')
Note: EXCAPE not works as WHERE(name LIKE '[A-Z]%' ESCAPE 'B')
4: LIST  OPERATORS
BETWEEN AND
Between a range of values
(both inclusive)

Syntax:
WHERE  expression  [NOT] BETWEEN begin AND end
WHERE salary BETWEEN 1000 AND 2000
WHERE salary NOT BETWEEN 100 AND 200

WHERE BillingDueDate 
BETWEEN GetDate() AND GetDate() + 30

WHERE BillingDate 
BETWEEN '2002-05-01' AND '2002-05-31'

WHERE M1+M2 BETWEEN 200 AND 500
IN(list)
Matches any one value of list based on equality.

An IN operator is equivalent to a series of conditions, connected with one or more OR operators. But it does not work with NULL value.

WHERE eid in (1000, 2000, 3000)
= WHERE eid=1000 OR eid=2000 OR  eid=3000

WHERE BankerID IN
      (  SELECT BankerID
         FROM Billings
          WHERE BillingDate = '2002-05-01')

SOME(list)
Matches more than 1 value.
WHERE salary  =SOME(1000,2000,3000)
WHERE salary > SOME (1000,2000,3000)
ANY(list)
Same as IN but works with non-equality (<, >, != etc)

Condition                   Equivalent expression
---------------------------------------------------------
x > ANY (12)                x > 1
x < ANY (12)                x < 2
x = ANY (12)                (x = 1) OR (x = 2)
x <> ANY (12)              (x <> 1) OR (x <> 2)

WHERE salary = ANY(100,200,300)
= WHERE salary IN(100,200,300)
Note: Difference: ANY allows arithmetic operators.

WHERE salary >=(select salary from employee)

ALL(list)
Matches all values of list.

Table: How ALL works:

Condition        Equivalent expression
x > ALL(12)         x > 2
x < ALL(12)         x < 1
x = ALL(12)         (x = 1) AND (x = 2)
x <>ALL(12)        (x<>1) AND (x<>2)

Finding Maximum of salary
SELECT   salary FROM   employee
WHERE m1>=ALL(select salary from employee)
= SELECT MAX(salary) FROM employee

Note: use <= for minimum. But it would not work with NULL values.
EXISTS(list)
Matches rows.
(similar to IN but matches entire rows
not a single value).

WHERE [NOT] EXISTS (subquery)

Use the EXISTS operator to test  that one or more rows are returned by the 
Subquery .When you use the EXISTS operator 
with a subquery, the subquery doesn't 
actually return any rows.Instead, it returns an 
indication of whether any rows meet the specified condition



SELECT ID, First_Name, Last_Name
FROM Employee
WHERE EXISTS
 (SELECT FROM Title
  WHERE ID = 1).


Note: For EXISTS, no need of same type of columns in main query and sub-query as it is compulsory for set operations.
NOTE:  1) IN, SOME, ALL,ANY, EXISTS operators does not works with NULL values.

               2) Except IN operator, other operators such as SOME, ALL, ANY, EXISTS operators needs
                   sub-query not direct value for execution. Example:
   WHERE col1 =>ALL(1000,200) ; // Error but    WHERE col1 =>ALL(Select col1 from Table1); // O.K.
5: NULL CHECKING OPERATOR
IS [NOT] NULL
Instead of “=”, use “IS” operator to check NULL values.
WHERE name IS NULL
WHERE name IS NOT NULL
6: SET OPERATORS

UNION
Set UNION operation.

Returns records of both queries eliminating duplicates.

Note:
(1)     For all set operations such as UNION, UNIONALL, INTERSET,  & EXCEPT, both queries must be select identical columns, otherwise operation cannot be performed.

(2)     Syntax for all set operations is
(Query1) OPERATOR(Query2).
Example1:  UNION same type of columns from different tables

SELECT CompanyName, Address,  
       City,  Region,Country
FROM Customers
UNION
SELECT CompanyName, Address,
       City, Region,Country
FROM Supplier

Example2: OR operator can be used instead of the UNION operator

SELECT first_name
FROM employee WHERE id = '1'
UNION
SELECT first_name
FROM employee WHERE id = '2'
=
SELECT first_name
FROM employee
WHERE id ='1' OR id = '2'


UNION ALL
Returns records of both the queries without eliminating duplicates.

Note: ORDER BY clause is not allowed with in query prior to UNION ALL
E.g., inserting multiple records simultaneously

INSERT INTO table1(id, name)
SELECT 101,’aaa’
UNION ALL
SELECT 102,’bbbb’.
INTERSECT
It returns common records available in both the result sets.

Used for Returning Distinct or Matching Rows.
SELECT ID,first_Name
FROM employee where id > 3
INTERSECT
SELECT 
ID,first_Name
FROM employee where id > 4;
=
SELECT ID,first_name
FROM employee
Where id>3 AND id>4
EXCEPT
(Like Minus)
Returns un-common records from first results.

SELECT id FROM employee
EXCEPT
SELECT id FROM title

SELECT ID,first_Name
FROM employee where id > 2
EXCEPT
SELECT ID,first_Name
FROM employee where id > 4;
=
SELECT ID, first_Name
FROM employee
Where id>2 AND id<=4.

Note: You can use “NOT” operator with many other operators. E.g., NOT IN, NOT EXISTS,
           NOT BETWEEN, and NOT LIKE, NOT = etc.





4. SELECT-FROM-WHERE-GROUP By-HAVING

  1. GROUP BY clause groups the records according to given criteria.
    Example: If you do grouping based on primary key then No. of groups = No. of records.

    SELECT Id
    FROM Employee
    GROUP BY id
  2. Displaying unique names (without using DISTINCT )

    SELECT Name
    FROM student
    GROUP BY name
  3. To display the on duplicate names in a table.

    SELECT Name
    FROM Student
    GROUP BY name HAVING (COUNT(name) > 1)
  4. You can do grouping with JOIN operations

    SELECT BankerState,
              BankerCity,
              COUNT(*) AS BillingQty,
              AVG(BillingTotal) AS BillingAvg
    FROM Billings
              JOIN Bankers ON Billings.BankerID = Bankers.BankerID
    GROUP BY BankerState, BankerCity
  5. GROUPING IS DONE after the filtering rows in WHERE clause.

    SELECT Name
    FROM student
    WHERE (RNo > 4)
    GROUP BY name
  6. Use aggregate functions by GROUPING.

    SELECT City, SUM(Salary)
    FROM Employee
    GROUP BY city
  7. You can GROUPING based on multiple columns.

    SELECT CustomerID, EmployeeID, COUNT(*)
    FROM Orders
    GROUP BY CustomerID, EmployeeID

5. SELECT-FROM-ORDER By

Syntax:

ORDER BY expression [ASC|DESC] [, expression [ASC|DESC]] ...
  • An ORDER BY clause sorts a column in ascending sequence
  • The default order is ASC.

Examples

  1. Simple ORDER BY

  2. SELECT Ename
    FROM Employee  
    ORDER BY Ename DESC
  3. Complex ORDER BY (ORDERING MULTIPLE COLUMNS).

    If ordering is as ORDER BY col1, col2, then result is based on first col1, and then col2 for duplicate values in col1. Example:
    Col1       Col2                 
    65           50
    66           40
    67           20
    67           30
    86           10                  


    SELECT *
    FROM Table1 
    ORDER BY Col1, Col2;
  4. Alias in ORDER BY

    Select M1+M2+M3 as Total
    FROM Student 
    ORDER BY Total
  5. Inline View Alias in ORDER BY

    Select *
    FROM (Select ename, id From Employee) AS t1 
    ORDER BY t1.id
  6. Expression in ORDER BY clause.

    SELECT *
     FROM Student  
    ORDER BY M1+M2+M3
  7. ORDERING aggregate results.

    SELECT region
    FROM Employee
    GROUP BY region  
    ORDER BY region

1 comment: