This article illustrates a few SQL commands. To illustrate how they are used, the following tables will work as reference:
Store_Information table:
Store_Name Sales Date_Time
Los Angeles 1500 05-Jan-1999
San Diego 250 07-Jan-1999
Los Angeles 300 08-Jan-1999
Boston 700 08-Jan-1999
Internet_Sales table:
Date_Time Sales
07-Jan-1999 250
10-Jan-1999 535
11-Jan-1999 320
12-Jan-1999 750
Commands used for creating and inserting data in tables:
Store_Information table:
CREATE TABLE Store_Information(
Store_Name varchar(20) not null,
Sales int not null,
Date_Time date not null
)
INSERT Store_Information
VALUES
(‘Los Angeles’, 1500, ’05-Jan-1999′),
(‘San Diego’, 250 , ’07-Jan-1999′),
(‘Los Angeles’, 300, ’08-Jan-1999′),
(‘Boston’, 700, ’08-Jan-1999′)
GO
Internet_Sales table:
CREATE TABLE Internet_Sales(
Date_Time date not null,
Sales int not null
)
INSERT Internet_Sales
VALUES
(’07-Jan-1999′, 250),
(’10-Jan-1999′, 535),
(’11-Jan-1999′, 320),
(’12-Jan-1999′, 750)
GO
1. WHERE
This clause works with other SQL clauses to specify a search condition for the statements in a table. The example below selects all stores whose sales are higher than 1000. The syntax is:
SELECT “column_name”
FROM “table_name”
WHERE “condition”
Example:
SELECT Store_Name
FROM Store_Information
WHERE Sales > 1000
Result:
Store_Name
Los Angeles
2. HAVING
This clause limits the result based on the respective sum (or on any other function that has been added). In the example below, a clause will be inserted to return only stores whose sales are higher than 1500. The difference between WHERE and HAVING clauses is that the WHERE clause condition is tested against each and every row of data, while the HAVING clause is tested against the groups.
Syntax:
SELECT “column1”, SUM(“column2”)
FROM “table_name”
GROUP BY “column1”
HAVING (arithmetic function’s condition)
Example:
SELECT Store_Name, SUM(sales)
FROM Store_Information
GROUP BY store_name
HAVING SUM(sales) > 1500
Result:
Store_name SUM(Sales)
Los Angeles 1800
3. DISTINCT
The SELECT clause works in conjunction with the SELECT clause and selects only distinct (unique) data from a database table(s). The syntax is:
SELECT DISTINCT “column_name”
FROM “table_name”
Example:
SELECT DISTINCT Store_Name FROM Store_Information
Result:
Store_Name
Los Angeles
San Diego
Boston
4. FUNCTIONS
SQL aggregate functions are used to um, count, get the average, get the minimum, and get the maximum values from a column or from a sub-set of column values. The arithmetic functions are: AVG, COUNT, MAX, MIN, and SUM.
For functions, use the following syntax:
SELECT “function type”(“column_name”)
FROM “table_name”
Example:
SELECT SUM(Sales) FROM Store_Information
Result:
SUM(Sales)
2750
5. JOIN
The JOIN clause selects data from two or more tables tied together by matching table columns. Example:
SELECT * FROM Table1
JOIN Table2
ON Table1.Field = Table2.Field
6. UNION
The UNION clause merges the results of two or more queries into one result set. It’s similar to JOIN, since both are used to merge information from multiple tables. However, with UNION the corresponding tables must have the same structure (data type). The syntax is:
[Instruction SQL 1]
UNION
[Instruction SQL 2]
The example below merges all data recorded in both tables:
SELECT Date_Time FROM Store_Information
UNION
SELECT Date_Time FROM Internet_Sales
Result:
Date_Time
05-Jan-1999
07-Jan-1999
08-Jan-1999
10-Jan-1999
11-Jan-1999
12-Jan-1999
7. UNION ALL
The UNION ALL clause merges the results of two or more queries into one result set. It works similarly to UNION clause; however, when using UNION, the duplicated rows are removed from the result set, and when using UNION ALL, all rows are returned, including the duplicated ones.
[Instruction SQL 1]
UNION ALL
[Instruction SQL 2]
Example:
SELECT Date_Time FROM Store_Information
UNION ALL
SELECT Date_Time FROM Internet_Sales
Result:
Date_Time
05-Jan-1999
07-Jan-1999
08-Jan-1999
08-Jan-1999
07-Jan-1999
10-Jan-1999
11-Jan-1999
12-Jan-1999
Related Articles
- Structure Query Language (SQL): Chapter 1 – Manipulating information in the DB.
- Structure Query Language (SQL): Chapter 2 – SQL Server Database’s Files and Logs.
- Structure Query Language (SQL): Chapter 3 – Data Discard and DB Limits.
- Structure Query Language (SQL): Chapter 4 – Backup.
- Structure Query Language (SQL): Chapter 5 – Best practices for setting up Historics and Queries.
- Structure Query Language (SQL): Chapter 6 – SQL Commands.
- Structure Query Language (SQL): Chapter 7 – Views.
- Structure Query Language (SQL): Chapter 8 – Triggers.
- Structure Query Language (SQL): Chapter 9 – Stored Procedures.
