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).