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:
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

Popular posts from this blog

Excel - IF function

Image
If function is a logic function. It does two things: Whether the criteria is met Return a value based on whether the criteria is met or not. If the criteria is met, it returns one value; if the criteria is not met, it returns a different value. The reason IF function is confusing for some Excel users is that it does two things: a logical test, AND a return value depending on the logical test result, while the name of "IF" implies it only does a logical test. If this function only returns TRUE or False, it would be much easier to understand. In the following example, there are 5 employees, each having his own budget. Column C records the exact amount of money they spent. If the actual spending is greater than budget, we call it "Over budget", otherwise, we call it "Within budget". The IF function is: =IF(C2>B2,"Over budget","Within budget") The first part, "C2>B2", is a logical test. If this is TRUE, then the IF function
Read more

Excel - Hierarchical chart (Treemap)

Image
If you have hierarchical data, you can create beautiful hierarchical charts. For example, in a university, there are students of different majors in departments, and departments in schools. Suppose we have data below, showing number of students in each school, department and major. In this example, we show you how to make treemap chart. The other type of hierarchical chart is sunburst chart. The data is equivalent if all the cells are filled. I like the following format better. 1. Select the data, then click Insert > Charts > Other Charts > Hierarchical . Select Treemap .  2. A nice-looking treemap chart is shown. The blue area shows all engineering students. The orange area shows all business students. Size of each rectangle is proportional to the number of students in each major.
Read more

Excel - VLOOKUP function

Image
VLOOKUP is one of the most commonly used Excel functions. VLOOKUP function asks for 4 things: The value you want to look up, such as name of a person The data range you want to search If the value is found in the data range, which column contains the value you want the function to return? Whether it is a approximate match or not. The default option is approximate match. This argument is optional and let's ignore it for now. The following table contains first name, last name and age of 4 people. Your task is to find the age of Jan Doe. It can be easily done using VLOOKUP function. The value you want to look up is "Jane", which is at cell "A3" The range of data you want to search, which is "A2:C5" Which column contains the value you want to retrieve? The "Age" is column C, so this argument is 3. The 4th argument is optional and we just ignore it for now. The formula is: =VLOOKUP(A3, A2:C5, 3) Below is the result. For the value you want to look
Read more