Notes for learing SQL(1)

1. Some Abbreviations

SQL - Sturcture Query Language

DML - Data Manipulation Language (Part of SQL)

SELECT - extracts data from a database table
UPDATE - updates data in a database table
DELETE - deletes data from a database table
INSERT INTO - inserts new data into a database table

DDL - Data Definition Language (Part of SQL)

CREATE TABLE - creates a new database table
ALTER TABLE - alters (changes) a database table
DROP TABLE - deletes a database table
CREATE INDEX - creates an index (search key) DROP INDEX - deletes an index

2. Using Quotes

SQL uses single quotes around text values (most database systems will also accept double quotes). Numeric values should not be enclosed in quotes.

3. The LIKE condition

A "%" sign can be used to define wildcards (missing letters in the pattern) both before and after the pattern.

SELECT * FROM Persons WHERE FirstName LIKE 'O%'
//return persons with first names that start with an 'O'

SELECT * FROM Persons WHERE FirstName LIKE '%a'
//return persons with first names that end with an 'a'

SELECT * FROM Persons WHERE FirstName LIKE '%la%'
//return persons with first names that contain the pattern 'la'

4. IN

The IN operator may be used if you know the exact value you want to return for at least one of the columns.

SELECT column_name
FROM table_name
WHERE column_name IN (value1,value2,..)

5. BETWEEN ... AND

The BETWEEN ... AND operator selects a range of data between two values. These values can be numbers, text, or dates.

SELECT column_name
FROM table_name
WHERE column_name BETWEEN value1 AND value2

To display values outside the range, use the NOT operator:

SELECT column_name FROM table_name WHERE column_name NOT BETWEEN value1 AND value2

IMPORTANT! The BETWEEN...AND operator is treated differently in different databases.Whether the return set include or exclude value1 or value2, you need to check your database.

6. SQL alias

With SQL, aliases can be used for column names and table names.

SELECT column AS column_alias FROM table SELECT column FROM table AS table_alias

7. SQL JOIN

INNER JOIN

SELECT field1, field2, field3
FROM first_table
INNER JOIN second_table
ON first_table.keyfield = second_table.foreign_keyfield

The INNER JOIN returns all rows from both tables where there is a match. If there are rows in first_table that do not have matches in second_table, those rows will not be listed.

LEFT JOIN

SELECT field1, field2, field3
FROM first_table
LEFT JOIN second_table
ON first_table.keyfield = second_table.foreign_keyfield

The LEFT JOIN returns all the rows from the first_table, even if there are no matches in the second_table. If there are rows in first_table that do not have matches in second_table, those rows also will be listed.

RIGHT JOIN

SELECT field1, field2, field3
FROM first_table
RIGHT JOIN second_table
ON first_table.keyfield = second_table.foreign_keyfield

The RIGHT JOIN returns all the rows from the second_table, even if there are no matches in the first_table. If there had been any rows in second_table that did not have matches in first_table, those rows also would have been listed

8. SQL UNION and UNION ALL

SQL Statement 1
UNION
SQL Statement 2

The UNION command is used to select related information from two tables, much like the JOIN command. However, when using the UNION command all selected columns need to be of the same data type.
Note: With UNION, only distinct values are selected.

SQL Statement 1
UNION ALL
SQL Statement 2

The UNION ALL command is equal to the UNION command, except that UNION ALL selects all values(include the same values).