logging in or signing up SimpleSQLTips tlhastin Download Post to : URL : Related Presentations : Share Add to Flag Embed Email Send to Blogs and Networks Add to Channel Uploaded from authorPOINT lite Insert YouTube videos in PowerPont slides with aS Desktop Copy embed code: Embed: Flash iPad Dynamic Copy Does not support media & animations Automatically changes to Flash or non-Flash embed WordPress Embed Customize Embed URL: Copy Thumbnail: Copy The presentation is successfully added In Your Favorites. Views: 4 Category: Science & Tech.. License: All Rights Reserved Like it (0) Dislike it (0) Added: September 20, 2012 This Presentation is Public Favorites: 0 Presentation Description No description available. Comments Posting comment... Premium member Presentation Transcript Basic SQL: Basic SQLPurpose 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 SQLTerms: 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 TermsBasic 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 DISTINCTSelect 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 fieldsConditional 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 ExpressionsPower 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 aGrouping: 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. GroupingGrouping 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(* )>= 3Union 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. JoinNatural 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 % ModulusMultiplication Calculation: SELECT OrderID, Freight, Freight * 1.1 AS NewFreight FROM Orders WHERE Freight >= 500; Multiplication CalculationAge 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 ” You do not have the permission to view this presentation. In order to view it, please contact the author of the presentation.