Lecture 5 SQL Continued

1
Lecture 5SQL Continued
2
BUILT-IN-FUNCTIONS

• AVG(value)
• MAX(value)
• MIN(value)
• SUM(value)
• STDDEV(value)
• VARIANCE(value)
• COUNT(value)
• Etc

3
Nested functions

• Select max (avg(grades)) etc..is allowed

4
String functions

• Several strings can be concatenated
• Use string1string 2
• implies
• select custname , cust_street as address
• From customer

5
Substrings

• Returns substrings
• Format
• Substr(stringvalue,m,n)
• Where m is the starting value and n is the length
  of characters

6
Assume orders have the format

• Abc1234
• cdf2345etc..
• Get the first and last part of the order
• Select substr (order_numb, 1,3),substr
  (order_numb,4,4)
• From order

7
Product (p_code, P_price) table

• P_CODE P_PRICE
• -------- ----------
• 11QER/31 109.99
• 13-Q2/P2 14.99
• 14-Q1/L3 17.49
• 1546-QQ2 39.95
• 1558-QW1 43.99
• 2232/QTY 109.92
• 2232/QWE 99.87
• 2238/QPD 38.95
• 23109-HB 9.95
• 23114-AA 14.4
• 54778-2T 4.99
• P_CODE P_PRICE
• -------- ----------
• 89-WRE-Q 256.99
• PVC23DRT 5.87
• SM-18277 6.99

8
Select three characters of price

• SQLgt select substr(p_price,1,3) from product
• SUB
• ---
• 109
• 14.
• 17.
• 39.
• 43.
• 109
• 99.
• 38.
• 9.9
• 14.
• 4.9
• SUB
• ---
• 256

9
Padding characters

• Rpad (string, length,set)
• Lpad (string,length,set)
• 1 select rpad (p_code,15,'.') as
  rightpad,p_price from product
• SQLgt /
• RIGHTPAD P_PRICE
• --------------- ----------
• 11QER/31....... 109.99
• 13-Q2/P2....... 14.99
• 14-Q1/L3....... 17.49
• 1546-QQ2....... 39.95
• 1558-QW1....... 43.99
• 2232/QTY....... 109.92
• 2232/QWE....... 99.87
• 2238/QPD....... 38.95
• 23109-HB....... 9.95
• 23114-AA....... 14.4
• 54778-2T....... 4.99

10
Length of string

• formatlength (string)
• Returns length of the string

11
SQLgt select P_code, length(P_code) from product

• P_CODE LENGTH(P_CODE)
• -------- --------------
• 11QER/31 8
• 13-Q2/P2 8
• 14-Q1/L3 8
• 1546-QQ2 8
• 1558-QW1 8
• 2232/QTY 8
• 2232/QWE 8
• 2238/QPD 8
• 23109-HB 8
• 23114-AA 8
• 54778-2T 8
• P_CODE LENGTH(P_CODE)
• -------- --------------
• 89-WRE-Q 8
• PVC23DRT 8
• SM-18277 8

12
Trimming data

• LTrim, Rtrim..remove unwanted characters
• Format
• RTRIM (string, set)
• Ltrim (string, set)
• Set is the collection of characters you want to
  trim

13
SQLgt select P_code, length(rtrim (P_code,' '))
from product

• P_CODE LENGTH(RTRIM(P_CODE,''))
• -------- ------------------------
• 11QER/31 8
• 13-Q2/P2 8
• 14-Q1/L3 8
• 1546-QQ2 8
• 1558-QW1 8
• 2232/QTY 8
• 2232/QWE 8
• 2238/QPD 8
• 23109-HB 8
• 23114-AA 8
• 54778-2T 8
• P_CODE LENGTH(RTRIM(P_CODE,''))
• -------- ------------------------
• 89-WRE-Q 8
• PVC23DRT 8

14
Remove . From price

• SQLgt select ltrim(p_price,'.'), p_code from
  product
• LTRIM(P_PRICE,'.') P_CODE
• ---------------------------------------- --------
• 109.99 11QER/31
• 14.99 13-Q2/P2
• 17.49 14-Q1/L3
• 39.95 1546-QQ2
• 43.99 1558-QW1
• 109.92 2232/QTY
• 99.87 2232/QWE
• 38.95 2238/QPD
• 9.95 23109-HB
• 14.4 23114-AA
• 4.99 54778-2T
• LTRIM(P_PRICE,'.') P_CODE
• ---------------------------------------- --------
• 256.99 89-WRE-Q

15
INSTR function

• Allows searching for a string of characters,
  gives the position of the string but does Not cut
  off anything
• Format
• Instr(string, start,occurrence)
• Start is the start of the string
• Occurrence is the position of occurrence that you
  want to search

16
Search for first / in p_codeSQLgt select
p_code, instr(p_code,'/') from product

• P_CODE INSTR(P_CODE,'/')
• -------- -----------------
• 11QER/31 6
• 13-Q2/P2 6
• 14-Q1/L3 6
• 1546-QQ2 0
• 1558-QW1 0
• 2232/QTY 5
• 2232/QWE 5
• 2238/QPD 5
• 23109-HB 0
• 23114-AA 0
• 54778-2T 0
• P_CODE INSTR(P_CODE,'/')
• -------- -----------------
• 89-WRE-Q 0
• PVC23DRT 0

17
Separate P_code in two parts before _ and after
_ for names that contain -

• SQLgt select p_code, substr(P_code, 1,
  instr(p_code,'/')) part1
• from product where p_code like '/'
• 2 3
• P_CODE PART1
• -------- --------
• 11QER/31 11QER/
• 13-Q2/P2 13-Q2/
• 14-Q1/L3 14-Q1/
• 2232/QTY 2232/
• 2232/QWE 2232/
• 2238/QPD 2238/
• WR3/TT3 WR3/
• 7 rows selected.

18

• SQLgt select p_code, substr(P_code, 1,
  instr(p_code,'/')-1) part1
• from product
• where p_code like '/'
• 2 3
• P_CODE PART1
• -------- --------
• 11QER/31 11QER
• 13-Q2/P2 13-Q2
• 14-Q1/L3 14-Q1
• 2232/QTY 2232
• 2232/QWE 2232
• 2238/QPD 2238
• WR3/TT3 WR3
• 7 rows selected.

19
Get the right part
20
Remove the period from price
21
GROUP BY (think of grouping as categorizing)

• Group by .
• Having condition

22
SUBQUERIES.

• Queries inside queryThere are times when you
  need information from a table to answer query
  related to the same table or another table

23

• Format
• SQLgtSELECT .
• FROM
• WHERE colname1
• condition(SELECT col2
• .
• .
• (SELECT
• .
• .
• 
  ))

24
List customers whose balance is greater than the
average balance of all customers

• Logic
• To answer this query we need to know average
  balance of all customers
• We will have to put this in a sub query

25
List customers whose balance are greater than the
average balance of all customers

• SQLgt select
• from customer
• where cust_balance gt
• (Select avg(cust_balance)
• From customer)

26
give the customer balance of customer whose order
is 123

• Logic
• Before we can find balance of customer whose
  order is 123 we need to find the cust_numb first

27
JOINING TABLES

• when information needed is in more than one
  table, we need to join tables
• WHERE clause in the select SQL statement creates
  a join. Note some queries can also be answered
  using sub query

28
Rules FOR joining

• WHERE attribute1 condition attribute2
• Ex where employee.ssnstudent.ssn
• Value(s) from one table are matched value(s) from
  other tables all matching values are attached
• allows joining of tables based on common
  attribute domains
• without the WHERE clause it will produce a
  Cartesian product also

29
Give the names of salesperson and their customers
in maryland

• SQLgtSelect cust_name, Sales_name
• from Customer C, salesperson S
• where
• c.sales_numb s. sales_numb
• And
• Upper(c.cust_st) MD
• C S are aliases for tables Customer and
  Salesperson respectively