Notes for 《Teach Yourself SQL in 10 Minutes(3rd)》

What Is a Database?

a database is a collection of data stored in some organized fashion . The simplest way to think of it is to imagine a database as a filing cabinet. The filing cabinet is simply a physical location to store data, regardless of what that data is or how it is organized.

case-insensitive?

Be aware that while the SQL language is case-in sensitive, the names of tables, columns, and values may not be (that depends on your DBMS and how it is configured).

 

 

ORDER By – must be in the end

Sorting by Multiple Columns

 

 

SELECT prod_id, prod_price, prod_name
FROM Products
ORDER BY prod_price, prod_name;

== 
SELECT prod_id, prod_price, prod_name
FROM Products
ORDER BY 2, 3;

///////////////////////////////////////////////////
ORDER BY 2, 3 means sort by prod_price and then by prod_name.
///////////////////////////////////////////////////


output:

prod_id    prod_price    prod_name
-------    ----------    --------------------
BNBG02     3.4900        Bird bean bag toy
BNBG01     3.4900        Fish bean bag toy
BNBG03     3.4900        Rabbit bean bag toy
RGAN01     4.9900        Raggedy Ann
BR01       5.9900        8 inch teddy bear
BR02       8.9900        12 inch teddy bear
RYL01      9.4900        King doll
RYL02      9.4900        Queen doll
BR03       11.9900       18 inch teddy bear

 

ASC or DESC?

DESC is short for DESC ENDING

ASC   is short for ASCE NDING (ascending order is the default sequence)

 

 

SELECT prod_id, prod_price, prod_name
FROM Products
ORDER BY prod_price DESC, prod_name;
means:
prod_price DESC
prod_name ASC (if the DBMS assume it is default sequence)

 

 

The single quotes are used to delimit a string.

 

Checking for a Range of Values

SELECT prod_name, prod_price
FROM Products
WHERE prod_price BETWEEN 5 AND 10;
//Note include 5 and 10

Checking for No Value

SELECT prod_name
FROM Products
WHERE prod_price IS NULL;

 

 

Understanding Order of Evaluation

SELECT prod_name, prod_price
FROM Products
WHERE vend_id = 'DLL01' OR vend_id = 'BRS01'
    AND prod_price >= 10;

 

 

Resolve ->

         Using () in WHERE Clauses

 

 

WHERE (vend_id = 'DLL01' OR vend_id = 'BRS01')
    AND prod_price >= 10;

 

 

 

Matching

         %   means match any number of occurrences of any character.

         _    matches just a single character. no more and no less

         []   specify a set of characters, any one of which must match a character in the specified position (the location of the wildcard).

Inner Join

 

 

 

SELECT vend_name, prod_name, prod_price
FROM Vendors, Products
WHERE Vendors.vend_id = Products.vend_id;
==
SELECT vend_name, prod_name, prod_price
FROM Vendors INNER JOIN Products
 ON Vendors.vend_id = Products.vend_id;

 

 

Outer Joins

SELECT Customers.cust_id, Orders.order_num
FROM Customers LEFT OUTER JOIN Orders
 ON Customers.cust_id = Orders.cust_id;

The example uses LEFT OUTER JOIN to select 
all the rows from the table on the left in the FROM clause (the Customers table).