Structure Query Language (SQL): Chapter 6 – SQL Commands.

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

Este artigo foi útil? Was this post helpful?
Yes0
No0

Leave a Reply

Your email address will not be published.Required fields are marked *