Basic SQL Introduction

1. Basic SQL Introduction Presented by: Madhuri Bhogadi

2. Overview

3. What is SQL? SQL is Structured Query Language, is a computer language for storing, manipulating and retrieving data stored in relational database.

4. SQL Commands The SQL commands to interact with databases are classified into 4 groups: • DDL - Data Definition Language CREATE, ALTER, DROP • DML - Data Manipulation Language INSERT, UPDATE, DELETE • DCL - Data Control Language GRANT, REVOKE • DQL - Data Query Language SELECT

5. DQL - Data Query Language SELECT Syntax: SELECT column1, column2,………, columnNFROM table_name WHERE [ CONDITION | EXPRESSION ]; Example: SELECT * from Customer SELECT [cust-no],[name],[address],[city],[st], telephone from customer where active='a'

6. WHERE Clause The SQL WHERE clause is used to specify a condition while fetching the data from single table or joining with multiple tables. The WHERE clause is not only used in SELECT statement, but it is also used in UPDATE, DELETE statement, etc. We can specify condition using >, <, =,LIKE , ODER BY,GROUP BY,HAVING Etc. Syntax : SELECT column1, column2, columnN FROM table_name WHERE[condition] Example : Select * from customer WHERE [cust-no]='davis'

7. SQL Operators An operator is a reserved word or character used in SQL statement's WHERE clause to perform operation(s). SELECT column1, column2,………. columnNFROM table_name WHERE [condition] They are : • Arithmetic operators • Comparison operators • Logical operators

8. SQL Arithmetic Operators Operators : + , - , * , / , % Examples: select * from [invoice] where [gross-amt]+[add-charges] >1000 select * from [invoice] where [gross-amt]-[add-charges]>0 select * from [invoice] where [tax-amt__1]*0.5>0

9. SQL Comparison Operators Operators : = , != , <> , > , < ,>= ,<= ,!< ,!> Examples: Select * from customer where [cust-no]='davis' Select * from customer where [cust-no]!='davis' select * from [invoice] where [gross-amt]>100 select * from [invoice] where [gross-amt]<0

10. SQL Logical Operators Operators: AND , BETWEEN ,EXISTS,IN,LIKE,NOT, OR,IS NULL Examples: select * from [invoice] where [gross-amt]>0 and [add-charges]>0 select * from [invoice] where [gross-amt] between 0 and 10000 select * from [sa] where [cancel-date] is null

11. Sorting the Data The SQL ORDER BY clause is used to sort the data in ascending or descending order, based on one or more columns. Some database sorts query results in ascending order by default. We can use more than one column in the ORDER BY clause. Syntax: SELECT column(s) FROM table_name [WHERE condition] [ORDER BY column1, .. columnN] [ASC | DESC] Examples: Select * from customer order by [cust-no] desc Select * from invoice order by [cust-no],[document] ASC

12. Group By Clause The SQL GROUP BY clause is used in collaboration with the SELECT statement to arrange identical data into groups . Group by can be used with WHERE ,ORDER BY clause as an optional. Syntax: SELECT column1, column2 FROM table_name WHERE [ conditions ] GROUP BY column1, column2 ORDER BY column1, column2 SELECT SUM(column_name) FROM table_name WHERE CONDITION GROUP BY column_name HAVING (arithematic function condition) Examples: Select [cust-no] from [invoice] group by [cust-no] Select [cust-no] from invoice group by [cust-no] having count(*)>1

13. Like Clause The SQL LIKE clause is used to compare a value to similar values using wildcard operators. There are two wildcards used in conjunction with the LIKE operator: The percent sign (%) : The percent sign represents zero, one, or multiple characters. The underscore (_) : The underscore represents a single number or character. Examples: Select * from [invoice] WHERE document like '111%' Select * from [invoice] WHERE document like '%111%' Select * from [invoice] WHERE document like '%111%' Select * from [invoice] WHERE document like '5__6%' Select * from [invoice] WHERE document like '5__000'

14. Functions SQL has many built-in functions for performing processing on string or numeric data. Following is the list of all useful built-in functions: • Aggregate • Date • String • Numeric

15. Aggregate Functions They summarize the results of a particular column of selected numeric data. They are COUNT ,MAX,MIN,AVG and SUM. These can be used with Group By as well. Examples: select count(*) from customer select max([ar-balance]) from [customer-d] select min([ar-balance]) from [customer-d] select avg([ar-balance]) from [customer-d] select sum([ar-balance]) from [customer-d] select [cust-no],sum([ar-balance]) from [customer-d] group by [cust-no]

16. Date Functions They are quite a few in-built date functions like GETDATE, DAY , MONTH, YEAR ,DATENAME, DATEADD , DATEDIFF etc. GETDATE - Gives current date time stamp DAY - Returns an integer representing the day (day of the month) of the specified date MONTH - Returns an integer that represents the month of the specified date YEAR - Returns an integer that represents the year of the specified date DATENAME - Syntax: DATENAME ( datepart , date ) where the datepart is DAY,MONTH,YEAR, WEEK, WEEKDAY Etc. DATEADD- Returns a specified date with the integer added to a specified datepart of that date. Syntax: DATEADD (datepart , number , date ) DATEDIFF - Returns the count of the specified datepart between the specified start date and end date. Syntax:DATEDIFF (datepart,startdate,enddate) Examples: select GETDATE () select * from invoice where DAY([invoice-date])=1 select * from invoice where DAY([invoice-date])=1 and MONTH([invoice-date])=10 select * from invoice where DAY([invoice-date])=1 and MONTH([invoice-date])=10 and YEAR([invoice-date])=2013 select * from invoice where DATENAME(week,[invoice-date])=4 and MONTH([invoice-date])=1 select * from invoice where DATENAME(weekday,[invoice-date])='sunday' and MONTH([invoice-date])=1 select * from invoice where DATEADD(YEAR,1,[invoice-date])=2014 select * from invoice where DATEDIFF(day,[invoice-date],[due-date])>10

17. String Functions SQL string functions are used primarily for string manipulation. The following are the important string functions: CHARINDEX Returns the starting position of a character string. LEFT Returns the leftmost number of characters as specified RIGHT Returns the specified rightmost number of characters SUBSTRING Returns the substring as specified LTRIM/RTRIM Removes leading and trailing spaces LOWER Returns the argument in lowercase UPPER Converts to uppercase LEN Returns the length of a string in bytes REVERSE Returns reverse a string. REPLACE Replaces occurrences of a specified string Examples: Select * from customer where CHARINDEX('Davis', Name)>0 Select * from customer where SUBSTRING( Name , 1,4)='david' Select * from customer where LEN( City )=15 Select UPPER(name) from customer Select LOWER(name) from customer Select LTRIM(name) from customer Select LEFT(name,5),* from customer Select RIGHT(name,10),* from customer Select REVERSE(name) from customer

18. Numeric Functions SQL numeric functions are used for numeric manipulation or mathematical calculation. CEILING Returns the smallest integer value that is not less than passed numeric expression. FLOOR Returns the largest integer value that is not greater than passed numeric expression. ROUND Returns numeric expression rounded to an integer. Can be used to round an expression to a number of decimal points Examples: select CEILING([gross-amt]) 'Gross Amt',* from [sa-inv] select FLOOR([gross-amt]) 'Gross Amt',* from [sa-inv] select ROUND([gross-amt],3) 'Gross Amt',* from [sa-inv]

19. Joins The SQL Joinclause is used to combine records from multiple tables in a database. We can join with join two, three or n number tables. There are different types of joins available in SQL: INNER JOIN : Returns rows when there is a 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 rows from the right table, even if there are no matches in the left table.

20. Inner Join The most frequently used and important of the joins is the INNER JOIN. They are also referred to as an EQUIJOIN.TheINNER JOIN creates a new result table by combining column values of two tables. Syntax: SELECT table1.column1, table2.column2... FROM table1 INNER JOIN table2 ON table1.common_filed = table2.common_field Example: SELECT customer.[cust-no],[customer-d].[ar-balance] from customer INNER JOIN [customer-d] on customer.[cust-no]=[customer-d].[cust-no] WHERE active='a' and [customer-d].[ar-balance]>1000 SELECT c.[cust-no],d.[ar-balance] from customer c with(nolock) INNER JOIN [customer-d] d with(nolock) on c.[cust-no]=d.[cust-no] WHERE c.active='a' and d.[ar-balance]>1000 SELECT [job].[cust-no],[job].[job-no],[jc-inv].[document],[ar-term].[Term-desc], [jc-inv].[gross-amt]+[jc-inv].[add-charges] 'InvAmt' from [job] with(nolock) INNER JOIN [jc-inv] with(nolock) on [job].[job-no]=[jc-inv].[job-no] INNER JOIN [ar-term] with(nolock) on [job].[term-code]=[ar-term].[term-code] WHERE [job].[statuscode]='open'

21. SQL - Sub Queries A Subquery or Nested query is a query within another SQL query and with WHERE clause. A subquery is used to return data that will be used in the main query as a condition to further filter the data to be retrieved. Sub queries are used with SELECT, INSERT, UPDATE, and DELETE statements with the operators like =, <, >, >=, <=, IN, BETWEEN etc. Here are a few rules that subqueries must follow: • Subqueries must be enclosed within parentheses. • A subquery can have only one column in the SELECT clause, unless multiple columns are in the main query for the subquery to compare its selected columns. • An ORDER BY cannot be used in a subquery, although the main query can use an ORDER BY. The GROUP BY can be used to perform the same function as the ORDER BY in a subquery. • Subqueries that return more than one row can only be used with multiple value operators, such as the IN operator. Syntax: SELECT column_name [, column_name ] FROM table1 WHERE column_name OPERATOR (SELECT column_name FROM table2 [WHERE]) Example: select * from "item" where "item-no" in (select "whs-code" from warehouse) select * from "item" where "item-no" not in (select "whs-code" from warehouse)

22. DML - Data Manipulation Language DML is used to modify, delete, insert data in database.DML Commands are INSERT, UPDATE, and DELETE. UPDATE : The SQL UPDATE Query is used to modify the existing records in a table. You have to use WHERE clause with UPDATE query to update selected rows otherwise all the rows will be updated. Syntax: UPDATE table_name SET column1 = value1, column2 = value2...., columnN= valueNWHERE [condition] Example: update [sctype] set [price]=100 where [sctype]='Test' update [sctype] set [price]=100 where [sctype] in ('Test','Test1','Test2')

23. SQL Server Management Studio How to access SQL Management Studio ? Click on the Shortcut on your desktop if you have one. OR 2. Go to Start / All Programs / Microsoft SQL Server 2008 ( or your version) / SQL Server Management Studio. 3. Once you click on that it will ask you for the server name Authentication ( User/Password ) .

24. New Query From SQL Editor Click on the New Query as you screen in the screen shot. You will have the editor for writing the queries. By default it will be master database you have to choose the database where you would like to. In Global Edge we choose either Service or ServiceTR.

25. Query Output How to Execute your query? Once you are done writing you select statement or Query you can either press F5 or use Execute Button on the editor to get the output. What else you can do with the Results other than seeing on the screen? Click on top of the query results or left side to see the options that are available. We can copy the results (with header columns or with out header columns) to any other file like excel. OR You can save to file directly OR Print.

26. Summary Query with : Select Where Group By Order By Joining multiple tables

27. Questions?

28. Thank You