This post is heavily based on Sams Teach Yourself SQL in 10 Minutes (Fourth Edition). Please consider to buy the book if you find this post useful.
Each SQL statement is made up of one or more keywords.
A reserved word that is part of the SQL language. Never name a table or column using a keyword.
SELECT to retrieve table data you must, at a minimum, specify two arguments - what you want to select, and from where you want to select it.
Retrieving Individual Columns
The following is using the data provided by the author of the book in the book Webpage. I am keeping a copy at my github page, named as tysql but I do not own it. I am also using Microsoft Access throughout. Download the data, open it with Access, and find a way to get to SQL View before executing SQL statements. There may be some differences brought by different versions of Microsoft Access.
You can type the following statements in the SQL View and Run it.
SELECT prod_name FROM Products;
The above retrieves a single column called prod_name from the Products table in the dataset. The following is how it appears in the Microsoft Access.
Multiple SQL statements must be separated by semicolons (the ; character).
SELECTis the same as
select, which is the same as
Select. The convention is to use UPPERCASE for the KEYWORDS and lowercase for column and table names. While the SQL language is case-insensitive, the names of tables, columns, and values may not be.
In addition, all extra white space within a SQL statement is ignored when that statement is processed. So, the followings are the same:
SELECT prod_name FROM Products; SELECT prod_name FROM Products; SELECT prod_name FROM Products;
Retrieving Multiple Columns
When selecting multiple columns be sure to specify a comma between each column name, but not after the last column name.
SELECT prod_id, prod_name, prod_price FROM Products;
Retrieving All Columns
Use the asterisk (*) wildcard character in lieu of actual column names to retrieve all columns.
SELECT * FROM Products;
As a rule, you are better off not using the * wildcard unless you really do need every column in the table. Retrieving unnecessary columns usually slows down the performance of your retrieval.
Retrieving Distinct Rows
Suppose you want the
DISTINCT vendor ID of all vendors with products in the products table. Doing the following gives everey occurences of the the vendor ID.
SELECT vend_id FROM Products;
DISTINCT keyword instructs the database to only return distinct values. The
DISTINCT keyword must be placed directly in front of the column names.
SELECT DISTINCT vend_id FROM Products;
Can’t Be Partially
DISTINCT keyword applies to all columns, not just the one it precedes. The following returns all rows retrieved because the combination of the specified columns are not distinct.
SELECT DISTINCT vend_id, prod_price FROM Products;
To select top number of entries, use the
TOP keyword. The following uses the
SELECT TOP 5 statement to retrieve just the first five rows. Please note that this applies to Microsoft Access and Microsoft SQL Server but not to other DBMs.
SELECT TOP 5 prod_name FROM Products;
Commenting in not allowed in Access at this point of time. The following is applicable to other DBMs.
A # at the start of a line makes the entire line a comment.
# This is a comment SELECT prod_name FROM Products;
/* */ for multi-lines comments.
/* SELECT prod_name, vend_id FROM Products; */ SELECT prod_name FROM Products;
/* and */ is comment text. This type of comment is often used to comment out code, as seen in this example.