SqlTutorial-1 : Lesson-3 : Class-2 Microsoft SQL Server Training: Free Online-Learning Classes (OLC) for T-SQL 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 (1, 2)                x > 1 x < ANY (1, 2)                x < 2 x = ANY (1, 2)                (x = 1) OR (x = 2) x <> ANY (1, 2)              (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(1, 2)         x > 2 x < ALL(1, 2)         x < 1 x = ALL(1, 2)         (x = 1) AND (x = 2) x <>ALL(1, 2)        (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

SELECT Ename
FROM Employee  
ORDER BY Ename DESC

2. 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;

3. Alias in ORDER BY

   Select M1+M2+M3 as Total
   FROM Student 
   ORDER BY Total

4. Inline View Alias in ORDER BY

   Select *
   FROM (Select ename, id From Employee) AS t1 
   ORDER BY t1.id

5. Expression in ORDER BY clause.

   SELECT *
    FROM Student  
   ORDER BY M1+M2+M3

6. ORDERING aggregate results.

   SELECT region
   FROM Employee
   GROUP BY region  
   ORDER BY region