[w3schools] SQL Study②

1.INSERT INTO Statement

The INSERT INTO statement is used to insert new records in a table.

The first way specifies both the column names and the values to be inserted:

INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

If you are adding values for all the columns of the table, you do not need to specify the column names in the SQL query. However, make sure the order of the values is in the same order as the columns in the table. The INSERT INTO syntax would be as follows:

INSERT INTO table_name
VALUES (value1, value2, value3, ...);

※ A column of the auto-increment field will be generated automatically when a new record is inserted into the table. ex. CustomerNo

※ Insert Data Only in Specified Columns 

It is also possible to only insert data in specific columns.

The following SQL statement will insert a new record, but only insert data in the "CustomerName", "City", and "Country" columns (CustomerID will be updated automatically):

ex. 

INSERT INTO Customers (CustomerName, City, Country)

VALUES ('Cardinal', 'Stavanger', 'Norway');

(RESULT)

2. NULL Values

A field with a NULL value is a field with no value.

If a field in a table is optional, it is possible to insert a new record or update a record without adding value to this field. Then, the field will be saved with a NULL value.

※ How to Test for Null Values?

> IS NULL or IS NOT NULL

SELECT column_names
FROM table_name
WHERE column_name IS NULL;

SELECT column_names
FROM table_name
WHERE column_name IS NOT NULL;

3. UPDATE Statement

The UPDATE statement is used to modify the existing records in a table.

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

EX.

UPDATE Customers
SET ContactName = 'Alfred Schmidt', City= 'Frankfurt'
WHERE CustomerID = 1;

Note: The WHERE clause specifies which record(s) that should be updated. If you omit the WHERE clause, all records in the table will be updated!

EX.

UPDATE Customers
SET ContactName='Juan';

4. DELETE Statement

The DELETE statement is used to delete existing records in a table.

DELETE FROM table_name 

WHERE condition;

EX.

DELETE FROM Customers WHERE CustomerName='Alfreds Futterkiste';

5. SELECT TOP CLAUSE

The SELECT TOP clause is used to specify the number of records to return.

The SELECT TOP clause is useful on large tables with thousands of records. Returning a large number of records can impact on performance.

※ SQL Server / MS Access Syntax:

SELECT TOP number|percent column_name(s)
FROM table_name
WHERE condition;

ex. 

SELECT TOP 3 * FROM Customers
WHERE Country='Germany';

SELECT TOP 50 PERCENT * FROM Customers;

※ MySQL Syntax:

SELECT column_name(s)
FROM table_name
WHERE condition
LIMIT number;

ex. 

SELECT * FROM Customers
WHERE Country='Germany'
LIMIT 3;

※ Oracle Syntax:

SELECT column_name(s)
FROM table_name
WHERE ROWNUM <= number;

ex. 

SELECT * FROM Customers
WHERE ROWNUM <= 3;

SELECT * FROM Customers
WHERE Country='Germany' AND ROWNUM <= 3;

6. MIN() and MAX() Functions

The MIN() function returns the smallest value of the selected column.

The MAX() function returns the largest value of the selected column.

SELECT MIN(column_name)
FROM table_name
WHERE condition;

ex.

SELECT MIN(Price) AS SmallestPrice
FROM Products;

SELECT MAX(column_name)
FROM table_name
WHERE condition;

ex.

SELECT MAX(Price) AS LargestPrice
FROM Products;

7. COUNT(), AVG(), SUM() Functions

The COUNT() function returns the number of rows that match specified criteria.

The AVG() function returns the average value of a numeric column.

The SUM() function returns the total sum of a numeric column.

SELECT COUNT(column_name)
FROM table_name
WHERE condition;

SELECT AVG(column_name)
FROM table_name
WHERE condition;

SELECT SUM(column_name)
FROM table_name
WHERE condition;

8. SQL LIKE Operator, Wildcards

The LIKE operator is used in a WHERE clause to search for a specified pattern in a column.

There are two wildcards used in conjunction with the LIKE operator:

• % - The percent sign represents zero, one, or multiple characters
• _ - The underscore represents a single character

Note: MS Access uses a question mark (?) instead of the underscore (_).

In MS Access and SQL Server you can also use:

• [charlist] - Defines sets and ranges of characters to match
• [^charlist] or [!charlist] - Defines sets and ranges of characters NOT to match

SELECT column1, column2, ...
FROM table_name
WHERE columnN LIKE pattern;

ex.

SELECT * FROM Customers
WHERE CustomerName NOT LIKE 'a%';

The following SQL statement selects all customers with a City starting with "b", "s", or "p":

SELECT * FROM Customers
WHERE City LIKE '[bsp]%';

The following SQL statement selects all customers with a City starting with "a", "b", or "c":

SELECT * FROM Customers
WHERE City LIKE '[a-c]%';

The two following SQL statements select all customers with a City NOT starting with "b", "s", or "p":

①

SELECT * FROM Customers

WHERE City LIKE '[!bsp]%';

②

SELECT * FROM Customers
WHERE City NOT LIKE '[bsp]%';

9. IN Operator

The IN operator allows you to specify multiple values in a WHERE clause.

The IN operator is a shorthand for multiple OR conditions.

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

SELECT column_name(s)
FROM table_name
WHERE column_name IN (SELECT STATEMENT);

Ex. 

SELECT * FROM Customers
WHERE Country IN (SELECT Country FROM Suppliers);

SELECT * FROM Customers
WHERE Country NOT IN ('Germany', 'France', 'UK');

10. Between Operator

The BETWEEN operator selects values within a given range. 

The values can be numbers, text, or dates.

The BETWEEN operator is inclusive: begin and end values are included. 

SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;

Ex. 

The following SQL statement selects all products with a price BETWEEN 10 and 20. In addition; do not show products with a CategoryID of 1,2, or 3:

SELECT * FROM Products
WHERE (Price BETWEEN 10 AND 20) AND NOT CategoryID IN (1,2,3);

SELECT * FROM Products
WHERE ProductName BETWEEN 'Carnarvon Tigers' AND 'Mozzarella di Giovanni'
ORDER BY ProductName;

SELECT * FROM Products
WHERE ProductName NOT BETWEEN 'Carnarvon Tigers' AND 'Mozzarella di Giovanni'
ORDER BY ProductName;

The following SQL statement selects all orders with an OrderDate BETWEEN '01-July-1996' and '31-July-1996':

①

SELECT * FROM Orders
WHERE OrderDate BETWEEN #01/07/1996# AND #31/07/1996#;

②

SELECT * FROM Orders
WHERE OrderDate BETWEEN '1996-07-01' AND '1996-07-31';

11. SQL Aliases (; AS)

SQL aliases are used to give a table, or a column in a table, a temporary name.

Aliases are often used to make column names more readable.

An alias only exists for the duration of the query.

Aliases can be useful when:

• There are more than one table involved in a query
• Functions are used in the query
• Column names are big or not very readable
• Two or more columns are combined together

[Alias Column Syntax]

SELECT column_name AS alias_name
FROM table_name;

EX.

The following SQL statement creates an alias named "Address" that combine four columns (Address, PostalCode, City and Country):

SELECT CustomerName, Address + ', ' + PostalCode + ' ' + City + ', ' + Country AS Address
FROM Customers;

Note: To get the SQL statement above to work in MySQL use the following:

SELECT CustomerName, CONCAT(Address,', ',PostalCode,', ',City,', ',Country) AS Address
FROM Customers;

Note: It requires double quotation marks or square brackets if the alias name contains spaces:

EX.

SELECT CustomerName AS Customer, ContactName AS [Contact Person]
FROM Customers;

[Alias Table Syntax]

SELECT column_name(s)
FROM table_name AS alias_name;

EX.

SELECT o.OrderID, o.OrderDate, c.CustomerName
FROM Customers AS c, Orders AS o
WHERE c.CustomerName="Around the Horn" AND c.CustomerID=o.CustomerID;