SQL Select Part 1 - PowerPoint PPT Presentation

1 / 28
About This Presentation
Title:

SQL Select Part 1

Description:

'F', 'Programmer', 89500, '10/5/1978', '080178080', '2327 Lakeside Drive', 'C-5', '78203', Null ) ... JobTitle = 'Programmer' AnnualSalary = 85000. Longevity ... – PowerPoint PPT presentation

Number of Views:30
Avg rating:3.0/5.0
Slides: 29
Provided by: john217
Category:
Tags: sql | part | programmer | select

less

Transcript and Presenter's Notes

Title: SQL Select Part 1


1
SQL Select (Part 1)
  • Murach Chapters 3 and 8

2
Select Syntax (elementary version)
  • SELECT
  • FROM
  • WHERE
  • ORDER BY
  • SELECT commands can be saved as VIEWs
  • fields
  • tables
  • row conditions
  • fields

3
Column Headings
  • SELECT
  • FROM
  • SELECT
  • FROM
  • field AS heading
  • tables
  • FirstName, LastName,
  • AnnualSalary/12 AS MonthlySalary
  • tblEmployee

4
Column Headings - Special Characters
  • SELECT
  • FROM
  • SELECT
  • FROM
  • field AS heading
  • tables
  • FirstName AS First Name,
  • LastName AS Last Name,
  • AnnualSalary/12 AS Monthly Salary
  • tblEmployee

5
Arithmetic Operators
38.4 / 10 3.84 38 / 10 3 38 10 8
  • -
  • /
  • ( )
  • Addition
  • Subtraction
  • Multiplication
  • Division
  • Integer Division
  • Modulo Remainder
  • Override normal evaluation sequence

6
Arithmetic Functions
  • ABS(x)
  • SQRT(x)
  • SQUARE(x)
  • POWER(x,p)
  • ROUND(x,d)
  • FLOOR(x)
  • CEILING(x)
  • Absolute value
  • Square root
  • Square
  • x P
  • Round to d digits
  • Round down
  • Round up

Dozens of additional functions are available
7
String Functions
  • UPPER(s)
  • LOWER(s)
  • LEN(s)
  • CHARINDEX(c,s)
  • LEFT(s,n)
  • RIGHT(s,n)
  • SUBSTRING(s,p,n)
  • REPLACE(s,x,y)
  • Concatenation
  • Upper case
  • Lower case
  • Length (number of characters)
  • Location of character C in S
  • N characters from the left side of S
  • N characters from the right side of S
  • N characters from position P of S
  • Replace X with Y in S

8
Computed Fields
  • SELECT
  • FROM
  • SELECT
  • FROM
  • FirstName ' ' LastName AS FullName,
  • ROUND( AnnualSalary/12, 2 )
  • AS MonthlySalary
  • tblEmployee
  • FirstName ' ' LastName AS FullName,
  • LEFT(FirstName,1) LEFT(LastName,1)
  • AS Initials
  • tblEmployee

9
Computed Fields
  • SELECT
  • FROM
  • SELECT
  • FROM
  • FirstName ' ' LastName AS FullName,
  • ROUND( AnnualSalary/12, 2 )
  • AS MonthlySalary
  • tblEmployee
  • FirstName ' ' LastName AS FullName,
  • LOWER( LEFT(FirstName,1)
  • LEFT(LastName,1) ) AS Initials
  • tblEmployee

10
DateTime Functions
  • DAY(d)
  • MONTH(d)
  • YEAR(d)
  • d x
  • DATEADD(p,x,d)
  • DATEDIFF(p,d1,d2)
  • GETDATE()
  • GETUTCDATE()
  • Day part of D (1-31)
  • Month part of D (1-12)
  • Year part of D
  • Add X days to D
  • Add X date parts P to D
  • Determine number of date parts P
  • between D1 and D2
  • Current date and time (local)
  • Current date and time (GMT)

11
DateTime Functions
  • SELECT
  • FROM
  • WHERE
  • SELECT
  • FROM
  • WHERE
  • ORDER BY
  • FirstName, LastName, EID
  • tblEmployee
  • MONTH(DOB) 3 AND DAY(DOB) 4
  • EID, PayDate, NetAmount
  • tblPaycheck
  • YEAR(PayDate) 2008
  • EID, MONTH(PayDate)

12
Duplicate Rows
  • SELECT
  • FROM
  • same as
  • SELECT ALL
  • FROM
  • City, State
  • tblEmployee
  • City, State
  • tblEmployee

13
Eliminate Duplicate Rows
  • SELECT DISTINCT
  • FROM
  • SELECT DISTINCT
  • FROM
  • WHERE
  • City, State
  • tblEmployee
  • EID
  • tblPaycheck
  • YEAR(PayDate) 2008

14
Row Level Filtering
  • fields
  • tables
  • row condition
  • SELECT
  • FROM
  • WHERE
  • Comparison Operators , gt, gt, lt, lt, ltgt,
    !
  • Logical Operators AND, OR, NOT

15
Row Level Filtering
  • SELECT
  • FROM
  • WHERE
  • SELECT
  • FROM
  • WHERE
  • OR
  • OR
  • tblEmployee
  • City 'Austin' AND State 'Tx'
  • tblEmployee
  • ( City 'Austin' OR City 'Dallas'
  • City 'Houston' OR City 'San Antonio'
  • City 'Waco' ) AND State 'Tx'

16
IN List Operator
  • SELECT
  • FROM
  • WHERE
  • SELECT
  • FROM
  • WHERE
  • AND
  • tblEmployee
  • FirstName IN ( 'Tom', 'Dick', 'Harry' )
  • tblEmployee
  • City IN ( 'Austin', 'Dallas', 'Houston',
  • 'San Antonio', 'Waco' )
  • State 'Tx'

17
BETWEEN Range Operator
  • SELECT
  • FROM
  • WHERE
  • SELECT
  • FROM
  • WHERE
  • tblEmployee
  • AnnualSalary BETWEEN 35000
  • AND 50000
  • tblEmployee
  • AnnualSalary gt 35000 AND
  • AnnualSalary lt 50000

same as
18
BETWEEN Range Operator
  • SELECT
  • FROM
  • WHERE
  • SELECT
  • FROM
  • WHERE
  • tblEmployee
  • DOB BETWEEN '1/1/1960'
  • AND '12/31/1985'
  • tblEmployee
  • YEAR(DOB) BETWEEN 1960
  • AND 1985

19
BETWEEN Range Operator
  • SELECT
  • FROM
  • WHERE
  • SELECT
  • FROM
  • WHERE
  • tblPaychecks
  • PayDate BETWEEN '7/4/2009'
  • AND '10/12/2009'
  • tblPaychecks
  • PayDate BETWEEN GetDate()-100
  • AND GetDate()

20
LIKE Wildcard Operator
  • SELECT
  • FROM
  • WHERE
  • Wildcards
  • _
  • abc
  • a-k
  • tblEmployee
  • LastName LIKE 'mo'
  • Matches any number of characters (zero, too)
  • Matches one character (underscore, not
    dash)
  • Matches A, B or C
  • Matches A through K (dash, not underscore)

21
LIKE Wildcard Operator
  • SELECT
  • FROM
  • WHERE
  • Wildcards
  • _
  • abc
  • a-k
  • tblEmployee
  • LastName LIKE '_o'
  • Matches any number of characters (zero, too)
  • Matches one character (underscore, not
    dash)
  • Matches A, B or C
  • Matches A through K (dash, not underscore)

22
LIKE Wildcard Operator
  • SELECT
  • FROM
  • WHERE
  • Wildcards
  • _
  • abc
  • a-k
  • tblEmployee
  • LastName LIKE '_aeiou'
  • Matches any number of characters (zero, too)
  • Matches one character (underscore, not
    dash)
  • Matches A, B or C
  • Matches A through K (dash, not underscore)

23
NOT LIKE / NOT IN
  • SELECT
  • FROM
  • WHERE
  • SELECT
  • FROM
  • WHERE
  • tblEmployee
  • LastName NOT LIKE '_o'
  • tblEmployee
  • FirstName NOT IN ( 'Tom', 'Dick', 'Harry' )

24
Missing Data
  • SELECT
  • FROM
  • WHERE
  • SELECT
  • FROM
  • WHERE
  • AND
  • tblEmployee
  • Phone IS NULL
  • tblEmployee
  • Phone IS NULL
  • Email IS NOT NULL

25
Record Sorting
  • SELECT
  • FROM
  • WHERE
  • ORDER BY
  • SELECT
  • FROM
  • ORDER BY
  • fields
  • tables
  • row conditions
  • fields
  • tblEmployee
  • LastName, FirstName

26
Top Records
  • SELECT
  • FROM
  • ORDER BY
  • SELECT TOP 5
  • FROM
  • ORDER BY
  • FirstName, LastName, AnnualSalary
  • tblEmployee
  • AnnualSalary DESC
  • FirstName, LastName, AnnualSalary
  • tblEmployee
  • AnnualSalary DESC

27
Top Records
  • SELECT
  • FROM
  • ORDER BY
  • SELECT TOP 5 PERCENT
  • FROM
  • ORDER BY
  • FirstName, LastName, AnnualSalary
  • tblEmployee
  • AnnualSalary DESC
  • FirstName, LastName,
  • AnnualSalary
  • tblEmployee
  • AnnualSalary DESC

28
Top Records
  • SELECT
  • FROM
  • ORDER BY
  • SELECT TOP 5 WITH TIES
  • FROM
  • ORDER BY
  • FirstName, LastName, AnnualSalary
  • tblEmployee
  • AnnualSalary DESC
  • FirstName, LastName,
  • AnnualSalary
  • tblEmployee
  • AnnualSalary DESC
Write a Comment
User Comments (0)
About PowerShow.com