Important database terms
Database: A container to store the organized data.
Table: A structured list of data of a specific type.
Schema: Information about database, table layout and properties.
Column: A single field in a table. Table made up of one or more columns.
Datatype: Type of allowed data.
Row: A record in a table.
Primary key: A column whose values uniquely identify every row in a table.
What is SQL ?
It is a language designed to interact with databases.
Example table used in this post: inventory table
Retrieving individual column
> SELECT product from inventory;
The SELECT statement: Used to retrieve the information from one or more table. The above uses the SELECT statement to retrieve a single column call product from inventory table. This will return all the rows in the table, Data is not filtered nor sorted.
Retrieving multiple columns
> SELECT product, price from inventory;
Column name must specified after the SELECT statement and each column must be separated by comma. The above statement retrieves two columns from the inventory table.
Retrieving all columns
This is done using the “*” wildcard character instead of specifying all column names in the query.
> SELECT * from inventory;
When a wildcard “*” is specified all the columns in the tables are returned. The column order will typically, but not always, be the physical order in which the columns appear in the table definition.
Sort by column
> SELECT product from inventory ORDER BY product;
To sort the data the ORDER BY clause is used. This clause takes one or more columns by which to sort the output. When specifying an ORDER BY clause be sure that it is the last clause in your select statement.
Sort by multiple columns
> SELECT id, quantity, product from inventory ORDER BY quantity, product;
To sort multiple columns simply specify the column name separated by commas. The above statement retrieves three columns and sort the results first by quantity and then by product. Very important to understand that when you are sorting by multiple columns, the sort sequence is exactly as specified.
Sort by column position
> SELECT id, product, price from inventory ORDER BY 2,3;
ORDER BY will also support ordering specify by relative column position. The above statement ORDER BY 2,3 means first sort by product and then by price. The advantage is no need to retype the column again but it is too easy to make mistake
Sort by order
> SELECT product from inventory ORDER BY product DESC;
Default sort order is ascending (from A to Z). The ORDER BY clause can also used to sort DESCENDING order (from Z to A). To sort by descending order DESC keyword must be specified.
Note: If you want to sort descending on multiple columns , be sure each column has its own DESC keyword.
Retrieving the data you want involves specifying search criteria also known as filter condition. Within the SELECT statement, data is filtered by specifying the search criteria in the WHERE clause. The WHERE clause is specified right after the table name.
> SELECT product, price from inventory WHERE price = 12.85;
The above statement retrieves two columns from the inventory table, but instead of returning all rows, only rows with a price value of 12.85 are returned.
WHERE clause operators
=, != , <, <=, >, >=, BETWEEN, IS NULL
Checking against single value
> SELECT product, price from inventory WHERE price < 15;
The above statement retrieves all the product costing less than Rs 15.
Checking for non-matches
> SELECT id, product, price from inventory WHERE vendorid <> “CR001″;
The above statement will list all the product not made by vendorid “CR001″
Checking for range of values
> SELECT vendorid, product, price from inventory WHERE price BETWEEN 2 AND 15;
BETWEEN operator is used to check for a range of values. It required 2 values: the beginning and end of the range. The value must be separated by the AND keyword. It matches all the values in the range, including the specified start and end. The above statement will list all the product with the price between Rs2 and Rs5.
Checking for no values
> SELECT product from inventory where price IS NULL;
The above statement returns a list of all products that have no price(an empty or not a price of 0). In this case there are no data is returned.
NULL: No value, an empty string or just spaces.
More on filtering
Using the AND operator
Filter more than one column use AND operator to append condition at WHERE clause. A special keyword used to join or change clauses within a WHERE clause. Also known as logical operators.
> SELECT id, product, quantity, price from inventory WHERE vendorid =”CR001″ AND price <=7;
The above statement retrieves the id, product name, quantity and price for all the products made by vendor CR001 as long as the price is Rs 7 or less.
Using the OR operator
OR operator is exactly opposite to AND, it retrieves the rows match either of the specified condition. If the first condition was met, the row would be retrieved regardless of the second condition.
> SELECT id, product, quantity, price from inventory WHERE vendorid =”AR001″ OR vendorid = “CR001″;
The above statement retrieves the id, product name, quantity and price for all the products made by either of two specified vendors. If an AND operator is used here, no data is returned.
Order of evaluation
When you write a WHERE clause that use both AND and OR operators, use parentheses to explicitly group orders. Never relay on default evaluation order even if it is exactly what you want.
> SELECT id, product, quantity, price from inventory WHERE (vendorid =”AR001″ OR vendorid = “CR001″) AND price >= 10;
Using the IN operator
Used to specify a range of condition, any of which can be matched. IN takes comma delimited list of valid values all are enclose with in parentheses.
> SELECT id, product, quantity, price from inventory WHERE vendorid IN(“AR001″,”CR001″);
The biggest advantages of IN operator can contain another SELECT statement, enabling you yo build highly dynamic WHERE clause. The IN operator almost always execute more quickly than lists or OR operator. You may think IN operator accomplishes the same goal as OR, yes you are right.
Using the NOT operator
NOT negates what ever condition comes next.
> SELECT id, product, quantity, price from inventory WHERE NOT vendorid = “CR001″;
The above statement NOT negates the condition that follows it; so instead of matching vendorid, it matches vendorid to anything that is NOT CR001.
The LIKE operator
Special character used to match part of a value. A search condition made up of literal text, wildcard characters or any combination of two. Technically LIKE is a predicate not an operator.
The (Percentage)% sign wildcard
> SELECT id, product, quantity, price from inventory WHERE product LIKE “Ha%”;
The most frequent used wildcard is percent sign (%). Within a search string, % means match any number of occurrences of any character. In the above statement it find all the products that starts with “Ha”.
The (Underscore)_ sign wildcard
The underscore is just like %, but instead of matching multiple character it matches a single character.
> SELECT id, product, quantity, price from inventory WHERE product LIKE “_en”;
NOTE: In some RDBMS you might need to use ? instead of _.
Alias is a alternative name for a field or value. Aliases are assigned with AS keyword.
> SELECT id AS Product_ID, product AS Product, quantity AS QTY, price AS Price, vendorid AS Vendor from inventory;
Performing mathematical calculations on retrieved data
> SELECT id, vendorid, product, quantity, price, price*quantity AS total from inventory;
The above statement quantity is multiplied by price.
It is often necessary to summarize the data without actually retrieving it all and SQL provides special function for this purpose.
Following are the aggregate functions.
AVG() – Average value of the column
COUNT() – Number of rows in a column
MAX() – Highest value in a column
MIN() – Lowest value in a column
SUM() – Sum of the column value
> > SELECT count(*) AS Count, AVG(price) AS Average_Price, MAX(price) AS Max_Price, MIN(price) AS Min_Price from inventory;
> SELECT vendorid AS Vendor, SUM(quantity) AS Total_quantity from inventory where vendorid = “CR001″;
Aggregate function can be used to summarize data. This enables you to count rows, calculate sums, averages etc. All the calculation performed on the data or on the data that matched a specific WHERE clause. But if you want to return the number of products offered by each vendor?
> SELECT vendorid, count(*) AS Number_Of_Products from inventory GROUP BY vendorid;
The above statement specifies two columns, vendorid and Number_Of_Products, which is a calculated field(created using COUNT(*) function). The GROUP BY clause will sort the data and group it by vendorid. Thus causes Number_Of_Products be calculated once per vendorid rather than once for the entire table.
> SELECT vendorid AS Vendor, count(*) AS Number_Of_Products from inventory GROUP BY vendorid HAVING count(*) >= 2;
You may ask what is the difference between WHERE and HAVING ?
Here it is: WHERE filters before data is grouped, and HAVING filters after data is grouped. Rows that are eliminated by WHERE clause will not be included in the group.
3,876 total views, 1 views today