SimpleSQLTips

Download
Share  Add to  Flag Embed
Views:
 
Category: Science & Tech..
     
 
License:   All Rights Reserved

Presentation Description

No description available.

Comments

Presentation Transcript

Basic SQL: 

Basic SQL

Purpose of SQL: 

SQL is a language aimed at retrieving data from a database. The user specifies conditions for the data they wish to retrieve. The program culls the records in the database that meet the specified condition. The query result is returned in an application. Purpose of SQL

Terms: 

General Structure SELECT, DISTIN CT, *, AS, FROM, WHERE Conditions IN, BETWEEN, LIKE*%_”, = Group GROUP BY, HAVING, COUNT(), SUM(), AVG(), MAX(), MIN() Display Order ORDER BY, ASC, DESC Operators AND, OR, NOT, CASE Output INTO TABLE/CURSOR, TO FILE (ADDITIVE), TO PRINTER, TO SCREEN Combine Statements UNION, UNION ALL, JOIN Terms

Basic Query: 

Every query requires SELECT and FROM statements. Sample: Basic Query SELECT column FROM table WHERE condition ;

Unique vs Distinct: 

UNIQUE UNIQUE is a constraint applied to the data tables that ensures all values are unique. If you try to insert an existing value, you will get an error like “Insert violates unique constraint.” The DISTINCT command returns one instance of each value. Example: SELECT FirstName SELECT DISTINCT FROM Names FirstName FROM Names Results: Jane Jane Jane Jill Jill Joe Jill Joe Joe Unique vs Distinct DISTINCT

Select Expressions: 

Select Expressions SELECT [ALL/DISTICT] expr1 [AS col1], expr2 [AS col2]; FROM table WHERE condition Expressions expr1, expr2 can be: A column (AS Alias) An expression of functions and fields

Conditional Expressions: 

Expression IN (value1, value2, value3) [you cannot use = for more than one value] Expression BETWEEN value1 AND value2 [BETWEEN is inclusive] Expression LIKE “%_” Conditional Expressions

Power of Like: 

Like bypasses all indexes; therefore, it can affect application performance. This function is helpful when you only know partial data for your query. Examples: Power of Like SELECT LastName FROM Table WHERE LastName LIKE “B %” (This find every last name that starts with B.) SELECT LastName, FirstName FROM Table WHERE LastName LIKE “_a%” AND FirstName=“John” (This finds every last name that contains a as the second letter a

Grouping: 

SELECT>FROM>WHERE>GROUP BY>HAVING>ORDER BY HAVING expression can only be used with GROUP GROUP BY functions: COUNT(), SUM(), AVG(), MAX(), MIN() Specifies the related rows to be grouped as one entry: usually a column. WHERE specifies conditions of individual rows before the rows are grouped. HAVING specifies conditions of the group. Grouping

Grouping Examples: 

SELECT Name AVG(dob) FROM Table GROUP BY Name Grouping Examples SELECT AVG(mtest), class FROM Table WHERE sex=“M” GROUP BY class HAVING COUNT(* )>= 3

Union Combines two or more SELECT statements: 

Union Combines two or more SELECT statements SELECT * FROM TableA UNION SELECT * FROM TableB ORDER BY Row1 , Row2 INTO TABLE Row10 By default, duplicates are removed in UNIONs; UNION ALL includes duplicates.

Join: 

INNER JOIN returns rows when there is at least one match in both tables . LEFT JOIN returns all rows from the left table even if there are no matches in the right table. RIGHT JOIN returns all the rows from the right table even if there are no matches in the left table. FULL JOIN return rows when there is a match in one of the tables. Join

Natural Join: 

Natural Join SELECT a.col, a.col1 , b.col2, expr1, expr2 FROM table1 a, table2 b WHERE a.dataset = b.dataset; The FROM clause contains an alias for the table name eliminating input of the full table name for each column in the SELECT and WHERE statements.

Outer Join A join operation that includes rows that have a match, plus rows that do not have a match in the other table.: 

SELECT table1.column, table2.column FROM table1 FULL [OUTER] JOIN table2 ON (table1.column=table2.column) WHERE conditions Outer Join A join operation that includes rows that have a match, plus rows that do not have a match in the other table.

Case (If , Then, Else): 

SELECT CASE column WHEN value THEN return value ELSE return _ value END AS columnname FROM table Case (If , Then, Else)

Basic Calculations: 

Basic Calculations Operator Description + Addition - Subtraction * Multiplication / Division % Modulus

Multiplication Calculation: 

SELECT OrderID, Freight, Freight * 1.1 AS NewFreight FROM Orders WHERE Freight >= 500; Multiplication Calculation

Age Calculation: 

# Days - DATE() – dob # Years - (DATE() – dob)/365 1 Decimal Point - ROUND(_,1) Example: Age Calculation SELECT Name, ROUND((DATE()-dob)/365,1) AS AGE FROM Table WHERE Gender = “F ”