Retrieving data by using Select statement
In order to retrieve data from database tables you can use Select satement. In the Select statement you have to specify at least two things:
- The table where you want to retrieve data.
- and the data or selection list you want to retrieve.
Here is the simple Select statement:
SELECT col1,col2,...
FROM tbl
If you want to select more than one column you have to seperate them by a comma. To retrieve all column from a table you can use shorthand artistic (*) like following:
SELECT * FROM tbl
To retrieve all customers from the customer table you can use the following query:
SELECT * FROM customer
Normally when you retrieve data from a database table, the column heading name is the name of column name. You can also assign a new column heading by using keyword AS. In PostgreSQL AS keyword is mandatory.
SELECT col1 AS col1_alias, col2 AS col2_alias
FROM tbl
If you only want to retrieve the first name and last name of the customer and give it to a meaningful heading you can use the following query:
SELECT customer_id AS id,
fname AS firstname,
lname AS lastname
FROM customer
To eliminate the duplicated row in the result set which you retrieve from a datable table you can use keyword DISTINCT as follows:
SELECT DISTINCT col1
FROM tbl
SELECT DISTINCT col1, col2
FROM tbl
- In the first statement PostgreSQL eliminates all duplicated rows which has the same value in col1 column.
- In the second statement PostgreSQL eliminates all duplicated rows which are combined by col1 and col2 column.
The visual explaination of SQL distinct is as follows:
Suppose you want to retrieve unique last name of customer you can use DISTINCT as follows:
SELECT DISTINCT lname
FROM customer
Sometimes you want to restrict the returned result set by your own criteria. In this case you can use WHERE clause as follows:
SELECT col1,col2
FROM tbl
WHERE conditions
Conditions can be any expression which restricts the returned result set. For example if you want to retrieve all customers which has title is Mr. you can use the following query:
SELECT customer_id,title, fname, lname
FROM customer
WHERE title = 'Mr'
Somtimes you want to sort the result base on your own criteria in ascending or desceding fashion, in this case you can use ORDER BY with the keywords ASC (ascending) or DESC (descending) to do so.
SELECT col1,col2,..
FROM tbl
WHERE conditions
ORDER BY col1 ASC, col2 DESC
For example you can retrieve all customers which have title is Mrs and then you sort them by first name in ascesding order and last name in descending order.
SELECT customer_id,title, fname, lname
FROM customer
WHERE title = 'Mrs'
ORDER BY fname ASC, lname DESC
