What is SQL
SQL – pronounced as the letters S-Q-L or a sequel is an abbreviation for Structured Query Language, is designed specifically for communicating with database.
– is made up of very few words, is designed to do one thing and do it well – provide you with a simple and efficient way to read and write data from a database.
Retrieving Data / Multiple columns:
Syntax:
SELECT columnname1, columnname2,…columnnamen
FROM tablename;
SELECT prod_id
FROM Products;
SELECT prod_id, prod_name, prod_price
FROM Products;
Retrieving all columns:
Syntax:
SELECT * FROM tablename;
Sorting Retrieved Data:
Syntax:
SELECT columnname
FROM tablename
ORDER BY columnname;
Sorting by Multiple Columns:
Syntax:
SELECT columname1,….,columnnamen
FROM tablename
ORDER BY colunmname1,..,columnname2
Filtering Data:
Syntax:
SELECT columnname
FROM tablename
WHERE columnname operator value;
The WHERE Clause Operators
|
Operator |
Description |
|
= <> != < <= !< > >= !> BETWEEN IS NULL |
Equality Non = equality Non – equality Less than Less than or equal to Not less than Greater than Greater than or equal to Not greater than Between two specified values Is a Null Value
|
* Null – no value, as opposed to a field containing 0 or spaces
- Checking against a single value
- Checking for nonmatches
- Checking for a range of values
- Checking for no value
When to use quotes:
Example: SELECT Vend_id, prod_name
FROM products
WHERE vend_id <> ‘DL01’;
Advanced Data Filtering:
Using the AND operator
AND – a keyword used in a WHERE clause to specify that only rows matching all the specified conditions should be retrieved
Example:
SELECT Vend_id, prod_name
FROM products
WHERE vend_id <> ‘DL01’ AND prod_price <=4;
Using the OR operator
OR – a keyword used in a WHERE clause that any rows matching either of the specified conditions should be retrieved
Example:
SELECT Vend_id, prod_name
FROM products
WHERE vend_id <> ‘DL01’ OR vend_id <> ‘DS01’
Using Wildcard Filtering
Wildcards – special characters used to match parts of a value
Search Pattern – a search condition made up of literal text and one or more wildcard characters
Using the LIKE Operator
Like – instructs the DBMS that the following search pattern is to be compared using a wildcard match rather than a straight equality match.
Note: Wildcard searching can only be used with text fields (strings), you can’t use wildcards to search fields of non text datatypes
The Percent Sign (%) Wildcard
The most frequently used wildcard is the percent sign (%). Within a search string, means, match any number of occurrences of any character.
Example:
SELECT prod_id,prod_name
FROM products
WHERE prod_name LIKE ‘fish%’; { ‘%beam bag%’}
The Underscore (_) Wildcard
The underscore is used just like % but instead of matching multiple characters the underscore matches just a single characters the underscore matches just a single character.
Example:
SELECT prod_id,prod_name
FROM products
WHERE prod_name LIKE ‘_inch teddy bear’;
The Brackets ( [ ] ) Wildcard
The brackets ( [ ]) wildcard is used to specify a set of characters, any one of which must match a character in the specified position (the location of the wildcard).
Example:
SELECT cust_contact
FROM Customers
WHERE cust_contact LIKE ‘[ JM]%’;
Tips for Using Wildcards
- Don’t overuse wildcards. If another search operator will do, use it instead
- When you do use wildcards, try not use them at the beginning of the search pattern unless absolutely necessary. Search patterns that begin with wildcards are the slowest to process
- Pay careful attention to the placement of the wildcard symbols. If they are misplaced, you might not return the data you intended
