Display Data in SQL | SQL SELECT Statement with Example

In the previous post, I already created a database, then a table in it, and finally inserted some data into the table. So it is time to get the data from the table and display it using the SQL query.

To display the data from the table, SQL provides a SELECT statement. Here is the general form of the "SELECT" statement that is used to select the data from the table to display it.

Before writing the general form of a select statement, let me tell you that either you can use a select statement to display data from some particular columns or you can use a select statement to display all the data available in the table. So to display all the data available in a table, Here is the general form:

SELECT * FROM tableName;

For example:

SELECT * FROM customer;

If you execute this SQL query, here is the outcome you will get: Since I already created the table "customer" in my previous post, I used this table to show you the example. I'm showing this snapshot for your understanding.

And to display the data of some particular columns, here is the general form of the SQL SELECT statement.

SELECT column1Name, column2Name, column3Name, ..., columnNName
FROM tableName;

For example:

SELECT firstname, email, city FROM customer;

If you execute this SQL query, here is the outcome you will get:

Display Only Distinct Value with SQL

Sometimes we need to only display the distinct data from some particular columns. So to achieve this task, you need to use the SQL SELECT DISTINCT statement. Here is its general form.

SELECT DISTINCT colum1Name, column2Name, column3Name, ..., columnNName
FROM tableName;

For example, the following SQL query will display the "city" column data or values with only distinct values. No duplicate city will be displayed.

SELECT DISTINCT city FROM customer;

Filter Data before Display in SQL

Sometime, we need to filter the data before display. For example, sometimes we need to display the details of only those customers who belong to the particular city, or something else you need to implement as the condition. To achieve this task, SQL provides the WHERE clause. Here is its general form.

SELECT column1Name, column2Name, ..., columnNName
FROM tableName
WHERE condition;

Or

SELECT * FROM tableName
WHERE condition;

For example:

SELECT * FROM customer
WHERE city='Austin';

The above SQL query will display all the data from the table "customer" with the "city" column value of "Austin."

You can also use the column with primary key as condition to display the data for a particular row. For example, the following SQL query:

SELECT * FROM customer
WHERE id=3;

will only display the row in which the id column's value is 3.

Here is the list of operators that can be used with the WHERE clause in SQL.

• =
• >
• <
• >=
• <=
• <>
• BETWEEN
• IN
• LIKE
• AND
• OR
• NOT

For example, the following SQL query:

SELECT * FROM customer
WHERE id < 4;

will display all the data whose id value is less than 4.

The <> operator is used for "Not equal" to display records that are not equal to a specified value. And the following SQL query:

SELECT * FROM customer
WHERE id BETWEEN 2 AND 17;

will display all the records whose id value is between 2 and 17. Now let me create another example to implement the IN operator.

SELECT * FROM customer
WHERE city IN ('Austin','Elko');

displays all the rows whose city value is either "Austin" or "Elko." Now here is an example of a SQL query with the LIKE operator.

SELECT * FROM customer
WHERE city LIKE 'a%';

Since the LIKE operator is used for pattern matching. Therefore, the above SQL query will be used to display all the records from the table "customer" whose "city" value starts with "a." Here are some other patterns that you might be interested in:

• Use '%m' to match values ending with 'm'.
• Use '%the%' to match values having 'the' at any position.
• Use '_m%' to match values in which the character 'm' is available at the second position.
• Use 'm%e' to match values that start with 'm' and end with 'e'.
• Use 'm%e' to match values that start with 'm' and end with 'e'.

You can use the AND operator to display records only if all the given conditions are true. For example:

SELECT * FROM customer
WHERE name='Mark' AND city='Austin';

Use the OR operator to display records if any of the given conditions are true. For example:

SELECT * FROM customer
WHERE name='Mark' AND city='Roseburg';

And finally, use the NOT operator to only display the records if the specified condition is not true. For example:

SELECT * FROM customer
WHERE NOT city='Roseburg';

Display Data in Ascending or Descending Order in SQL

To display data in ascending or descending order, use the ORDER BY keyword. Here is its general form.

SELECT column1Name, column2Name, ..., columnNName
FROM tableName
ORDER BY column1Name, column2Name, ..., columnNName ASC|DESC;

For example:

SELECT firstname FROM customer
ORDER BY firstname ASC;

The output produced by this SQL query should look like this:

Here is another example:

SELECT * FROM customer ORDER BY firstname ASC;

Now the output produced by this SQL query should look like this:

Display Number of Rows in SQL

To display the total number of rows available in a specific table, use the "SELECT COUNT (*)" statement. For example

SELECT COUNT(*) FROM customer;

The above SQL query will return the total number of rows available in the table named "customer." The output looks like this after executing the above query:

You can also implement the WHERE clause to write some conditions to count the number of rows that satisfy certain conditions.

SQL Online Quiz