SQL Tutorial - Basic Commands

SQL Tutorial - Basic Commands

• 1.
  SQL Tutorial Basic SQL Commands © 2013 1keydata.com All Rights Reserved
• 2.
  Agenda • Database Basics • SQL Commands – SELECT … FROM – WHERE – ORDER BY – GROUP BY – HAVING © 2013 1keydata.com All Rights Reserved
• 3.
  Database Basics In a relational database, data is stored in tables. Tables Database © 2013 1keydata.com All Rights Reserved
• 4.
  Database Basics Each table consists of columns and rows. Each column is a field in a record, and there is a column name associated with each column. Columns Tables Database © 2013 1keydata.com All Rights Reserved
• 5.
  Database Basics Each row represents one record. When we say how many records we have, we are referring to the number of rows. Columns Tables Rows Database © 2013 1keydata.com All Rights Reserved
• 6.
  SELECT … FROM SQL is structured similar to the English language. The basic command for retrieving data from a database table is to SELECT data FROM a table. Not surprisingly, the keywords "SELECT" and "FROM" make up the core of a SQL statement. The syntax for “SELECT… FROM” is: SELECT “COLUMN_NAME” FROM “TABLE_NAME” © 2013 1keydata.com All Rights Reserved
• 7.
  SELECT … FROM Different ways of selecting data: Select more than 1 column: SELECT “COLUMN_NAME_1”, “COLUMN_NAME_2” FROM “TABLE_NAME” Select all columns: Select unique values: SELECT * SELECT DISTINCT “Column_Name” FROM “TABLE_NAME” FROM “TABLE_NAME” © 2013 1keydata.com All Rights Reserved
• 8.
  WHERE Sometimes we want to retrieve only a subset of the data. In those cases, we use the “WHERE” keyword. The syntax for “WHERE” is: SELECT “COLUMN_NAME” FROM “TABLE_NAME” WHERE “CONDITION” CONDITION represents how we want the data to be filtered. © 2013 1keydata.com All Rights Reserved
• 9.
  ORDER BY When we want to list the results in a particular order (ascending or descending), we use the ORDER BY keyword at the end of the SQL statement. The syntax for “ORDER BY” is: SELECT “COLUMN_NAME” FROM “TABLE_NAME” WHERE “CONDITION” ORDER BY “COLUMN_NAME” [ASC | DESC] © 2013 1keydata.com All Rights Reserved
• 10.
  MATHEMATICAL FUNCTIONS SQL has built-in mathematical functions to allow us to perform mathematical operations on the data. Common mathematical functions include: • SUM • AVG • COUNT • MAX • MIN © 2013 1keydata.com All Rights Reserved
• 11.
  GROUP BY To find the highest Sales_Amount across all stores, we use the MAX( ) function in the following SQL: SALES_HISTORY Date Store Sales_Amount SELECT MAX(Sales_Amount) FROM SALES_HISTORY; © 2013 1keydata.com All Rights Reserved
• 12.
  GROUP BY To find the highest Sales_Amount for each store, we change the SELECT portion to include “Store”: SALES_HISTORY Date Store Sales_Amount SELECT Store, MAX(Sales_Amount) FROM SALES_HISTORY; © 2013 1keydata.com All Rights Reserved
• 13.
  GROUP BY However, this SELECT statement by itself is not enough. To allow SQL to correctly calculate what we want, we need to use the GROUP BY keyword. In the following example, the Store column after GROUP BY tells SQL to apply the MAX function for each Store. SALES_HISTORY Date Store Sales_Amount SELECT Store, MAX(Sales_Amount) FROM SALES_HISTORY GROUP BY Store; © 2013 1keydata.com All Rights Reserved
• 14.
  GROUP BY To summarize, the syntax for GROUP BY is as follows: SELECT “COLUMN_NAME_1”, FUNCTION(“COLUMN_NAME_2”) FROM “TABLE_NAME” WHERE “CONDITION” GROUP BY “COLUMN_NAME_1” © 2013 1keydata.com All Rights Reserved
• 15.
  HAVING Previously we had talked about using the WHERE keyword to filter results. We cannot use WHERE to filter based on the result of a function, because we need to specify the filtering condition after SQL has calculated the function, and consequently any filtering condition based on the function needs to be specified after the GROUP BY phrase. So we cannot use the WHERE keyword because it is always used before GROUP BY. HAVING is used to filter based on the result of a function. © 2013 1keydata.com All Rights Reserved
• 16.
  HAVING The syntax for HAVING is as follows: SELECT “COLUMN_NAME_1”, FUNCTION(“COLUMN_NAME_2”) FROM “TABLE_NAME” GROUP BY “COLUMN_NAME_1” HAVING (CONDITION based on FUNCTION) © 2013 1keydata.com All Rights Reserved
• 17.
  HAVING Using the SALES_HISTORY table we had earlier. If we want to sum the sales amount for each store, but only want to see results for stores with total sales amount greater than 100, we use the following SQL: SALES_HISTORY Date Store Sales_Amount SELECT Store, SUM(Sales_Amount) FROM SALES_HISTORY GROUP BY Store HAVING SUM(Sales_Amount) > 100; © 2013 1keydata.com All Rights Reserved
• 18.
  Order of SQLCommands A SELECT statement has the following order: • SELECT … FROM • WHERE • GROUP BY • HAVING • ORDER BY © 2013 1keydata.com All Rights Reserved
• 19.
  1Keydata SQL Tutorial http://www.1keydata.com/sql/sql.html © 2013 1keydata.com All Rights Reserved