{"id":3343,"date":"2019-03-25T17:43:50","date_gmt":"2019-03-25T20:43:50","guid":{"rendered":"http:\/\/xexeu.elipse.com.br\/pt\/structure-query-language-sql-chapter-6-sql-commands\/"},"modified":"2020-02-12T15:49:55","modified_gmt":"2020-02-12T18:49:55","slug":"structure-query-language-sql-chapter-6-sql-commands","status":"publish","type":"post","link":"https:\/\/kb.elipse.com.br\/en\/structure-query-language-sql-chapter-6-sql-commands\/","title":{"rendered":"Structure Query Language (SQL): Chapter 6 – SQL Commands."},"content":{"rendered":"
\n

This article illustrates a few SQL commands. To illustrate how they are used, the following tables will work as reference:<\/p>\n

Store_Information<\/b> table:<\/p>\n

Store_Name\u00a0\u00a0\u00a0 Sales\u00a0\u00a0\u00a0 Date_Time<\/b>
\nLos Angeles\u00a0\u00a0\u00a0 1500 \u00a0\u00a0\u00a0 05-Jan-1999
\nSan Diego\u00a0\u00a0 \u00a0 \u00a0 \u00a0 250 \u00a0\u00a0\u00a0 07-Jan-1999
\nLos Angeles\u00a0\u00a0 \u00a0\u00a0 300 \u00a0\u00a0\u00a0 08-Jan-1999
\nBoston\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 700 \u00a0\u00a0\u00a0 08-Jan-1999<\/p>\n

Internet_Sales<\/b> table:<\/p>\n

Date_Time\u00a0\u00a0\u00a0 Sales<\/b>
\n07-Jan-1999\u00a0\u00a0\u00a0 250
\n10-Jan-1999\u00a0\u00a0\u00a0 535
\n11-Jan-1999\u00a0\u00a0\u00a0 320
\n12-Jan-1999\u00a0\u00a0\u00a0 750<\/p>\n

Commands used for creating and inserting data in tables: <\/b>
\n
\nStore_Information<\/b> table:<\/p>\n

CREATE TABLE<\/span> Store_Information<\/span>(
\nStore_Name<\/span> varchar<\/span>(20) not null,
\nSales <\/span>int <\/span>not null,
\nDate_Time<\/span> date <\/span>not null
\n)<\/span><\/p>\n

INSERT <\/span>Store_Information<\/span>
\nVALUES<\/span>
\n(‘Los Angeles’<\/span>, 1500, ’05-Jan-1999′<\/span>),
\n(‘San Diego’<\/span>, 250 , ’07-Jan-1999′<\/span>),
\n(‘Los Angeles’<\/span>, 300, ’08-Jan-1999′<\/span>),
\n(‘Boston’<\/span>, 700, ’08-Jan-1999′<\/span>)
\nGO<\/span><\/p>\n

Internet_Sales<\/b> table:<\/p>\n

CREATE TABLE<\/span> Internet_Sales<\/span>(
\nDate_Time<\/span> date <\/span>not null,
\nSales <\/span>int <\/span>not null
\n)<\/span><\/p>\n

INSERT <\/span>Internet_Sales<\/span>
\nVALUES<\/span>
\n(’07-Jan-1999′<\/span>, 250),
\n(’10-Jan-1999′<\/span>, 535),
\n(’11-Jan-1999′<\/span>, 320),
\n(’12-Jan-1999′<\/span>, 750)
\nGO<\/span><\/p>\n

1. WHERE<\/b><\/p>\n

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:<\/p>\n

SELECT “column_name”
\nFROM “table_name”
\nWHERE “condition”<\/b><\/span><\/p>\n

Example:<\/p>\n

SELECT <\/span>Store_Name<\/span>
\nFROM <\/span>Store_Information<\/span>
\nWHERE <\/span>Sales > 1000<\/span><\/span><\/p>\n

Result:<\/p>\n

Store_Name<\/u>
\nLos Angeles<\/span><\/p>\n

2. HAVING<\/b><\/p>\n

This clause limits the result\u00a0 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 <\/span>and HAVING\u00a0 <\/span>clauses is that the WHERE <\/span>clause condition is tested against each and every row of data, while the HAVING <\/span>clause is tested against the groups.
\nSyntax:<\/p>\n

SELECT “column1”, SUM(“column2”)
\nFROM “table_name”
\nGROUP BY “column1”
\nHAVING (arithmetic function’s condition) <\/span><\/b><\/p>\n

Example:<\/p>\n

SELECT <\/span>Store_Name<\/span>, SUM(sales)<\/span>
\nFROM <\/span>Store_Information<\/span>
\nGROUP BY<\/span> store_name<\/span>
\nHAVING SUM(<\/span>sales<\/span>) ><\/span> 1500<\/span><\/p>\n

Result:
\n
\n<\/u>Store_name\u00a0\u00a0\u00a0 SUM(Sales)<\/u>
\nLos Angeles\u00a0\u00a0\u00a0 1800<\/span><\/p>\n

3. DISTINCT<\/b><\/p>\n

The SELECT <\/span>clause works in conjunction with the SELECT <\/span>clause and selects only distinct (unique) data from a database table(s). The syntax is:<\/p>\n

SELECT DISTINCT “column_name”
\nFROM “table_name”<\/span><\/b><\/p>\n

Example:<\/p>\n

SELECT DISTINCT<\/span> Store_Name<\/span> FROM <\/span>Store_Information<\/span><\/span>
\n<\/span>
\nResult:<\/p>\n

Store_Name<\/u>
\nLos Angeles
\nSan Diego
\nBoston<\/span><\/p>\n

4. FUNCTIONS <\/b><\/p>\n

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<\/span>, COUNT<\/span>, MAX<\/span>, MIN<\/span>, and SUM<\/span>.<\/p>\n

For functions, use the following syntax:
\n
\nSELECT “function type”(“column_name”)
\nFROM “table_name”<\/span><\/b><\/p>\n

Example:<\/p>\n

SELECT <\/span>SUM<\/span>(<\/span>Sales<\/span>) FROM <\/span>Store_Information<\/span><\/span><\/p>\n

Result:
\n
\nSUM(Sales)<\/u>
\n2750 <\/span><\/p>\n

5. JOIN<\/b><\/p>\n

The JOIN <\/span>clause selects data from two or more tables tied together by matching table columns. Example:
\n
\nSELECT * FROM Table1
\nJOIN Table2
\nON Table1.Field = Table2.Field<\/span><\/b><\/p>\n

6. UNION<\/b><\/p>\n

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:
\n
\n[Instruction SQL 1]
\nUNION
\n[Instruction SQL 2]<\/span><\/b><\/p>\n

The example below merges all data recorded in both tables:<\/p>\n

SELECT <\/span>Date_Time<\/span> FROM <\/span>Store_Information<\/span>
\nUNION<\/span>
\nSELECT <\/span>Date_Time<\/span> FROM <\/span>Internet_Sales<\/span><\/span><\/p>\n

Result:<\/p>\n

Date_Time<\/u>
\n05-Jan-1999
\n07-Jan-1999
\n08-Jan-1999
\n10-Jan-1999
\n11-Jan-1999
\n12-Jan-1999<\/span><\/p>\n


\n7. UNION ALL<\/b><\/p>\n

The UNION ALL <\/span>clause merges the results of two or more queries into one result set. It works similarly to UNION <\/span>clause; however, when using UNION<\/span>, the duplicated rows are removed from the result set, and when using UNION ALL<\/span>, all rows are returned, including the duplicated ones.<\/p>\n

[Instruction SQL 1]
\nUNION ALL
\n[Instruction SQL 2]<\/span><\/b><\/p>\n

Example:<\/p>\n

SELECT <\/span>Date_Time<\/span> FROM <\/span>Store_Information<\/span>
\nUNION ALL <\/span>
\nSELECT <\/span>Date_Time<\/span> FROM <\/span>Internet_Sales<\/span>
\n<\/span>
\nResult:<\/p>\n

Date_Time<\/u>
\n05-Jan-1999
\n07-Jan-1999
\n08-Jan-1999
\n08-Jan-1999
\n07-Jan-1999
\n10-Jan-1999
\n11-Jan-1999
\n12-Jan-1999<\/span><\/p>\n<\/div>\n

<\/div>\n
\n

Related Articles<\/h3>\n
\n