A basic SQL SELECT statement allows users to return rows and columns of data from a database table. In the previous section, we focused solely on returning literal values and results of basic mathematical expressions. Now, we will be retrieving data that is stored in the tables. SELECT statements that only retrieve data with no filtering, grouping, or sorting have the standard form:
SELECT [Column 1], [Column 2], …, [Column N]
FROM [Schema Name].[Table Name]
Every SELECT statement includes specifying one or more columns and a table from which to retrieve the columns. Each table is contained within a database schema. Unless the schema is the default “dbo” schema, you must specify the schema name with the table name.
Remember that all of our examples will use the AdventureWorks sample database provided by Microsoft. Take a look at our introduction to SQL post to learn how to get started.
To return the Name and ProductNumber columns from the Production.Product table:
SELECT Name, ProductNumber
Notice that the column names are separated by a comma. Your results contain two columns for each row of data – one for the Name column and another for the ProductNumber column.
It is possible to return all columns and all rows from a table using the asterisk (*) symbol. To return all rows and all columns from the Production.Product table, execute the query:
With larger tables, it is not a good practice to return every row of data. You can limit the number of rows returned by your SQL SELECT statement with the “TOP” expression. To return only the top one hundred rows from the Production.Product table:
SELECT TOP 100 *
Instead of specifying a number of rows to limit, you can limit by a percent of the total number of rows in a table. For example, to limit our query to return only the top ten percent of rows from the Production.Product table:
SELECT TOP 10 PERCENT *
Given some of you will work on large databases, it is a good practice to use the TOP expression. You run the risk of sapping much of the server’s resources by not limiting the number of rows retrieved.
This gives you the basic information you need to complete a SQL SELECT statement. You should now be able to retrieve data from a table. You can either return all columns or specify a select few. You also have the ability to limit the number of rows returned to the results.