SQL Basics

1
SQL Basics

• Session 701

2
Introductions

• Name
• Company
• Database experience
• Data warehouse experience
• SQL experience

3
Session Outline

• Data warehouse concepts
• Relational database basics
• SQL command types
• The basic query
• Using operators
• Summarizing data
• Sorting and grouping data
• Changing the appearance of data
• Joining tables in queries
• Query performance

4
What is a Data Warehouse?

• A complete store of data obtained from a variety
  of sources
• Organized for reporting and analysis purposes
• Some common data warehousing terms
• OLAP (online analytical processing)
• Data mart
• Data mining
• Cubes
• ETL (extract, transform and load)
• Business intelligence
• Often derived from OLTP (online transaction
  processing) systems and relational databases

5
What is a Relational Database?

• Data stored in logical units or tables
• Tables are related to one another based on common
  data items
• Tables are collections of rows and columns.

Producer Table
6
More about rows and columns

• Row an entire record each individual entry in
  a table
• Column contains all the information associated
  with a
  specific field in a table. Each column has a
  data type.
• Common data types

• Decimal (p,s)
• Smallint
• Integer
• Float (p)

• Datetime (y,m,d,h,m,s)
• Char
• Varchar
• And many more

7
Related Tables

• Each table below has a common data element that
  allows the tables to be joined together
• Primary Key column(s) that make(s) the row
  unique
• Foreign Key column(s) in a table that refer(s)
  to a related tables
• primary key

D_OptLocation
D_PrdProducer
8
What is SQL?

• Structured Query Language
• The language used to communicate with a
  relational database.
• Pronounced two ways
• The letters, S-Q-L
• The word, Sequel

9
Types of SQL Commands

• DDL Data Definition Language
• CREATE, DROP, ALTER TABLE / INDEX
• DML - Data Manipulation Language
• INSERT, UPDATE, DELETE
• DCL - Data Control Language
• ALTER PASSWORD, GRANT, REVOKE
• Transactional Control Commands
• COMMIT, ROLLBACK
• DQL- Data Query Language
• SELECT

10
Basic Query Format

• SELECT column1, column2
• FROM tableA
• SELECT COUNT ()
• FROM tableA
• SELECT
• FROM tableA
• SELECT DISTINCT column1, column2
• FROM tableA

11
Example Basic Select

• SELECT COUNT ()
• FROM D_PrdProducer
• SELECT
• FROM D_PrdProducer
• SELECT locationCd
• FROM D_PrdProducer
• SELECT DISTINCT locationCd
• FROM D_PrdProducer

12
Adding Conditions

• SELECT column1, column2
• FROM tableA
• WHERE column1 abc
• AND column2 123

13
Typical Operators

• Comparison
• Equal
• ltgt Not Equal
• gt Greater Than
• gt Greater Than or Equal
• lt Less Than
• lt Less Than or Equal

14
More Operators

• Logical
• IS NULL / IS NOT NULL
• BETWEEN / NOT BETWEEN
• IN / NOT IN
• LIKE / NOT LIKE
• EXISTS / NOT EXISTS
• Wildcards
• _ (underscore)
• Boolean
• AND
• OR

15
Null Values

• A term used to represent a missing value
• A column with no value
• Referenced by the IS NULL
• or IS NOT NULL operators
• Not the same as spaces, zero or empty string ('')
• Example WHERE initialName IS NULL is
  different than
• WHERE initialName ''

16
Examples Conditions

• SELECT producerId, certNum, effectiveDate
• FROM Cred_Policy
• WHERE producerId IN ('FLCA0001','98070100','840735
  01')
• SELECT
• FROM Cred_Policy
• WHERE effectiveDate BETWEEN '01/01/2000' AND
  '12/31/2004'
• SELECT certNum, lastName
• FROM Cred_Insured
• WHERE lastName LIKE 'MA'

17
Aggregate Functions

• Used to summarize data in a SQL statement
• Used in conjunction with the
• GROUP BY clause
• Typical Aggregate Functions
• SUM
• MAX
• MIN
• AVG
• COUNT

18
Using Aggregates

• SELECT column1, SUM(column2)
• FROM tableA
• WHERE column1 'XYZ'
• GROUP BY column1

19
Example Aggregates

• SELECT
• payDt,
• SUM(benAmt) AS total_claims,
• AVG(benAmt) AS avg_claim_amt,
• COUNT(payDt) AS claims_count
• FROM Clm_Payments
• WHERE payDt gt '1/1/2000'
• GROUP BY payDt

20
Sorting

• SELECT column1, SUM(column2)
• FROM tableA
• WHERE column1 ???
• GROUP BY column1
• ORDER BY column1 ASC (DESC)

21
Example - Sorting

• SELECT
• DISTINCT producerId,
• COUNT() AS 'Number of Certs'
• FROM Cred_Policy
• GROUP BY producerId
• ORDER BY producerId

22
String Functions

• Used to manipulate character data in a SQL
  statement
• Examples of String Functions
• Concatenate column 1 and column 2
• SELECT column1 column2
• Select the left, three characters from column 1
• SELECT LEFT(column1, 3)
• Select some of the characters from a column
• SELECT SUBSTRING (column1,3,2)

23
Example String Functions

• SELECT
• LastName ', ' FirstName ' ' InitialName
• AS 'Full Name',
• LEFT(FirstName,1) InitialName
  LEFT(LastName,1) AS 'Initials'
• FROM Card_Address
• WHERE LastName LIKE 'D'
• AND InitialName IS NOT NULL

24
Joins

• Allow you to run a query using columns from more
  than one table
• Tables are joined on common fields
• Types of Joins
• Inner or Equal
• Left Outer
• Right Outer

25
Understanding Joins
Customers
Orders
Left Table
Right Table
Left Outer Join All customers any related
orders
Right Outer Join All orders any related
customers
Equal or Inner Join Customers who placed orders
26
Using Joins

• SELECT
• Customer.column1,
• Customer.column2,
• Order.column3
• FROM Customer
• INNER JOIN Order
• ON Customer.column1 Order.column1

27
Using an Alias

• SELECT
• C.column1,
• C.column2,
• O.column3
• FROM Customer AS C
• INNER JOIN Order AS O
• ON C.column1 O.column1

28
Exercise Joining Two Tables

• SELECT
• A.credPolicyId,
• A.covNum,
• B.refundDate,
• B.cncPremAmt
• FROM Cred_Coverage AS A
• INNER JOIN Cred_Cancel AS B
• ON A.credPolicyId B.credPolicyId AND
• A.covNum B.covNum

29
Example Right Outer Join

• SELECT
• A.covCd,
• B.covCd
• FROM Cred_Coverage A
• RIGHT OUTER JOIN D_OptCoverage B
• ON A.covCd B.covCd

30
What is an Index?

• Similar to an index in a book.
• Kinds of Indexes
• A Single-Column Index is effective when a single
  column is used in the WHERE clause.
• A Composite Index is most effective on table
  columns used together in the WHERE clause.
• A Unique Index does not allow duplicate values to
  be inserted into the table.
• An INDEX is a pointer to data in a table.

31
Query Performance

• Avoid full table scans by using a WHERE clause or
  an INDEX
• Avoid the OR operator use IN if possible
• Avoid large SORT operations
• Use the LIKE operator and wildcards to help
  screen data
• Avoid joining more than 2 or 3 large tables

32
Planning Your Query

• First, understand your data model
• What tables are needed?
• Which columns should be included?
• What items may need to be calculated?
• Is only a particular time period or location
  necessary?
• In what order should the data be displayed?

33
Group SQL Exercise

• Write a SELECT statement that will display
  insurer code, coverage code, and issued premium.
  Sort the results by insurer code and coverage
  code.
• Write a WHERE clause that will limit the query to
  issued premiums greater than 500.00.
• Join the producer and location tables to produce
  a report displaying the producers names and their
  state names. Only producers in Ohio and Illinois
  should be included.

34
Group SQL Exercise
D_PrdProducer
Cred_Coverage

D_OptLocation
35
Thank You!