[sql/en] More examples (#4898)

1 files changed, 48 insertions, 1 deletions

diff --git a/sql.html.markdown b/sql.html.markdown
index 5c933465..19eae60f 100644
--- a/sql.html.markdown
+++ b/sql.html.markdown
@@ -3,6 +3,7 @@ language: SQL
 filename: learnsql.sql
 contributors:
   - ["Bob DuCharme", "http://bobdc.com/"]
+  - ["Th3G33k", "https://github.com/Th3G33k"]
 ---
 
 Structured Query Language (SQL) is an [ISO/IEC 9075](https://www.iso.org/standard/63555.html) standard language for creating and working with databases stored in a set of tables. Implementations usually add their own extensions to the language; [Comparison of different SQL implementations](http://troels.arvin.dk/db/rdbms/) is a good reference on product differences.
@@ -15,6 +16,10 @@ Several of these sample commands assume that the [MySQL employee sample database
 ```sql
 -- Comments start with two hyphens. End each command with a semicolon.
 
+/*
+Multi-line comments
+*/
+
 -- SQL is not case-sensitive about keywords. The sample commands here
 -- follow the convention of spelling them in upper-case because it makes
 -- it easier to distinguish them from database, table, and column names.
@@ -54,7 +59,22 @@ SELECT * FROM departments WHERE dept_name LIKE 'S____';
 SELECT DISTINCT title FROM titles;
 
 -- Same as above, but sorted (case-sensitive) by the title values.
-SELECT DISTINCT title FROM titles ORDER BY title;
+-- The order can be specified by adding ASC (ascending) or DESC (descending).
+-- If omitted, it will sort in ascending order by default.
+SELECT DISTINCT title FROM titles ORDER BY title ASC;
+
+-- Use the comparison operators (=, >, <, >=, <=, <>) and
+-- the conditional keywords (AND, OR) to refine your queries.
+SELECT * FROM departments WHERE dept_no = 'd001' OR dept_no = 'd002'
+
+-- Same as above.
+SELECT * FROM departments WHERE dept_no IN ('d001', 'd0002')
+
+-- Opposite of the above.
+SELECT * FROM departments WHERE dept_no NOT IN ('d001', 'd0002')
+
+-- Select in a given range.
+SELECT * from departments WHERE dept_no BETWEEN 'd001' AND 'd0002'
 
 -- Show the number of rows in the departments table.
 SELECT COUNT(*) FROM departments;
@@ -63,6 +83,24 @@ SELECT COUNT(*) FROM departments;
 -- have 'en' as a substring of the dept_name value.
 SELECT COUNT(*) FROM departments WHERE dept_name LIKE '%en%';
 
+-- Aggregate functions can be used, with GROUP BY, to compute a value
+-- from a set of values. Most commonly used functions are:
+-- MIN(), MAX(), COUNT(), SUM(), AVG().
+-- Use HAVING to filter rows by aggregated values.
+
+-- Retrieve the total number of employees, by department number,
+-- with the condition of having more than 100 employees.
+SELECT dept_no, COUNT(dept_no) FROM dept_emp GROUP BY dept_no
+HAVING COUNT(dept_no) > 100
+
+-- Aliases, using the optional keyword AS, can be used for column/table names.
+SELECT COUNT(A.*) AS total_employees, COUNT(B.*) total_departments
+FROM employees AS A, departments B
+
+-- Common date format is "yyyy-mm-dd".
+-- However, it can vary according to the implementation, the operating system, and the session's locale.
+SELECT * FROM dept_manager WHERE from_date >= '1990-01-01'
+
 -- A JOIN of information from multiple tables: the titles table shows
 -- who had what job titles, by their employee numbers, from what
 -- date to what date. Retrieve this information, but instead of the
@@ -75,6 +113,15 @@ SELECT employees.first_name, employees.last_name,
 FROM titles INNER JOIN employees ON
        employees.emp_no = titles.emp_no LIMIT 10;
 
+-- Combine the result of multiple SELECT.
+-- UNION selects distinct rows, UNION ALL selects all rows.
+SELECT * FROM departments WHERE dept_no = 'd001'
+UNION
+SELECT * FROM departments WHERE dept_no = 'd002'
+
+-- SQL syntax order is:
+-- SELECT _ FROM _ JOIN _ ON _ WHERE _ GROUP BY _ HAVING _ ORDER BY _ UNION
+
 -- List all the tables in all the databases. Implementations typically provide
 -- their own shortcut command to do this with the database currently in use.
 SELECT * FROM INFORMATION_SCHEMA.TABLES