• SELECT
• FROM
• WHERE
• ORDER BY
If we want to retrieve all of the information about all of the customers in the Employees table, we could use the asterisk (*) as a shortcut for all of the columns, and our query looks like
SELECT * FROM Employees
-------------------------------
Get only specific columns data for all of the rows in the table:
SELECT EmployeeID, FirstName, LastName, HireDate, City FROM Employees
-------------------------------
The WHERE Clause
--Filter Data for city "London"
SELECT * FROM Employees WHERE City = 'London'
-------------------------------
---The employees who do not live in London
SELECT Employee,* FROM Employees WHERE City <> 'London'
-------------------------------
SELECT EmployeeID, FirstName, LastName, HireDate, City FROM EmployeesWHERE HireDate >= '1-july-1993'-------------------------------
SELECT EmployeeID, FirstName, LastName, HireDate, CityFROM EmployeesWHERE (HireDate >= '1-june-1992') AND (HireDate <= '15-december-1993')
-------------------------------
Note that SQL also has a special BETWEEN operator that checks to see if a value is between two values (including equality on both ends). This allows us to rewrite the previous query as
SELECT EmployeeID, FirstName, LastName, HireDate, CityFROM EmployeesWHERE HireDate BETWEEN '1-june-1992' AND '15-december-1993'
-------------------------------
We could also use the NOT operator, to fetch those rows that are not between the specified dates:
SELECT EmployeeID, FirstName, LastName, HireDate, CityFROM EmployeesWHERE HireDate NOT BETWEEN '1-june-1992' AND '15-december-1993'
-------------------------------
What if we want to check if a column value is equal to more than one value? If it is only 2 values, then it is easy enough to test for each of those values, combining them with the OR operator and writing something like
SELECT EmployeeID, FirstName, LastName, HireDate, City FROM EmployeesWHERE City = 'London' OR City = 'Seattle'
-------------------------------
However, if there are three, four, or more values that we want to compare against, the above approach quickly becomes messy. In such cases, we can use the IN operator to test against a set of values. If we wanted to see if the City was either Seattle, Tacoma, or Redmond, we would write
SELECT EmployeeID, FirstName, LastName, HireDate, City FROM EmployeesWHERE City IN ('Seattle', 'Tacoma', 'Redmond')
-------------------------------
As with the BETWEEN operator, here too we can reverse the results obtained and query for those rows where City is not in the specified list:
SELECT EmployeeID, FirstName, LastName, HireDate, City FROM Employees
WHERE City NOT IN ('Seattle', 'Tacoma', 'Redmond')
-------------------------------
Finally, the LIKE operator allows us to perform basic pattern-matching using wildcard characters. For Microsoft SQL Server, the wildcard characters are defined as follows:
Wildcard Description
_ (underscore) = matches any single character
% = matches a string of one or more characters
[ ] = matches any single character within the specified range (e.g. [a-f]) or set (e.g. [abcdef]).
[^] = matches any single character not within the specified range (e.g. [^a-f]) or set (e.g. [^abcdef]).
A few examples should help clarify these rules.
WHERE FirstName LIKE '_im' finds all three-letter first names that end with 'im' (e.g. Jim, Tim).
WHERE LastName LIKE '%stein' finds all employees whose last name ends with 'stein'
WHERE LastName LIKE '%stein%' finds all employees whose last name includes 'stein' anywhere in the name.
WHERE FirstName LIKE '[JT]im' finds three-letter first names that end with 'im' and begin with either 'J' or 'T' (that is, only Jim and Tim)
WHERE LastName LIKE 'm[^c]%' finds all last names beginning with 'm' where the following (second) letter is not 'c'.
Here too, we can opt to use the NOT operator: to find all of the employees whose first name does not start with 'M' or 'A', we would write
SELECT EmployeeID, FirstName, LastName, HireDate, City FROM EmployeesWHERE (FirstName NOT LIKE 'M%') AND (FirstName NOT LIKE 'A%')
-------------------------------
The ORDER BY Clause
By default, the sort order for a column is ascending (from lowest value to highest value), as shown below for the previous query:
SELECT EmployeeID, FirstName, LastName, HireDate, City FROM Employees ORDER BY City
-------------------------------
--Order by 2 columns
SELECT EmployeeID, FirstName, LastName, HireDate, Country, City FROM Employees ORDER BY Country, City DESC
-------------------------------
--Order by 2 columns but in different order
SELECT EmployeeID, FirstName, LastName, HireDate, Country, City FROM EmployeesORDER BY Country ASC, City DESC
-------------------------------