SQL - Select
Suppose we have a table of student looking like this. The table name is "tblStudent".
1. Select all cases and all columns
Select * from tblStudent
SQL is case-insensitive, which means that "select", "Select" and "SELECT" are all equivalent.
The "*" in the above SQL statement means selecting all columns. The result is below.
2. Select specific columns
Select StudentID, FirstName, LastName from tblStudent
The result is below.
3. Generate new fields in the displayed result
Select
StudentID,
FirstName+ ' '+ LastName as FullName,
TotalScore/3 as AvgScore
from tblStudent
The result is below.
4. Select with a criteria
Here we want to select all cases with TotalScore is at or above 200.
Select * from tblStudent where TotalScore>=200
The result is below.
The criteria can contain multiple conditions, such as:
Select * from tblStudent where TotalScore>=200 and Math>70
5. Select with ordered result
Select * from tblStudent order by TotalScore
If you want the values be ordered from large to small, use keyword "desc". Keyword "desc" means "descending".
Select * from tblStudent order by TotalScore desc
6. Select unique values
Suppose there are 2 students from NY, like below. How to get a list of unique home states?
Select distinct HomeState from tblStudent
The result is below:
Select distinct HomeState from tblStudent
The result is below:
7. Select ... Group by
Suppose we want to get the number of student from each state, we can use the following COUNT and GROUP BY statement. The result is ordered by the number of students from large to small.
Select HomeState, count(HomeState) as myCount
from tblStudent
group by HomeState
order by MyCount desc
8. Select ... NULL
Note most values of the "Note" column are "NULL". The "NULL" are not entered by us. The system automatically marks any empty cells as "NULL" so that you won't mistake them as white spaces. We can also operate on the "Null" values, like below:
Select * from tblStudent where note is not null
The result is below:
Comments
Post a Comment