SQL

1
SQL Part II

• Yong Choi
• School of Business
• CSU, Bakersfield

2
SQL Examples Aggregate Functions

• Example 18 Save as example 18
• How many parts (count number of records) are in
  item class HW?
• Use of count command
• Count all records count()
• No idea? Try to figure out manually!

3
Example 18 SQL Query to Count Records
4
Example 18

• SELECT count()
• FROM Part
• WHERE Class________

5
SQL Examples Aggregate Functions

• Example 19 Save as example 19
• Find the number of customers and the total of
  their balances.
• Calculate total sum(field name)

6
Example 19 SQL Query to Count Records and
Calculate a Total
7
Example 19

• SELECT count(), Sum(________)
• FROM Customer

8
SQL Examples Aggregate Functions

• Example 20 Save as example 20
• Find the total number of customers and the total
  of their balances. Change the column names for
  the number of customers and the total of their
  balances to CustomerCount and BalancesTotal.
• Assign column name using AS command

9
Example 20 SQL Query to Perform Calculations and
Rename Fields
10
Example 20

• SELECT count() AS CustomerCount,
• Sum(Balance) AS _____________
• FROM Customer

11
SQL Examples Nested Query

• A query inside another query
• A inside query (sub-query) is evaluated first. It
  is common to enclose sub-query in parentheses for
  readability!!
• Example 21 Save as example 21
• List the order number for each order from the
  order line table for a part located in warehouse
  3. That is, we are looking for list of OrderNum
  that is associated with warehouse 3.
• No idea? Try to figure out manually!

12
Example 21 SQL Query with Subquery
13
Example 21

• SELECT OrderNum
• FROM OrderLine
• WHERE PartNum IN
• (SELECT __________
• FROM _______
• WHERE ___________)

14
SQL Examples - Grouping

• Use GROUP BY clause
• ONLY grouping, NOT sorting (usually associated
  with ORDER BY clause)
• Example 22 Save as example 22
• For each sales rep, list the rep number, the
  number of customers assigned to each rep, and the
  average balance of the reps customers.
• Rename the count of the number of customers and
  the average of the balances to NumOfCustomers and
  AverageBalance

15
Example 22 SQL Query to Group Records
of customers are grouped by each sales rep
16
Example 22

• SELECT RepNum,
• Count() AS NumOfCustomer,
  Avg(Balance) AS AvgBalance
• FROM Customer
• GROUP BY __________

17
SQL Examples Grouping (cont)

• Example 23 Save as example 23
• For each sales rep with fewer than four
  customers, list the rep number, the number of
  customers assigned to the rep, and the average
  balance of the reps customers. Rename the count
  of the number of customers and the average of the
  balances to NumOfCustomers and AverageBalance.
• Use of Having command.
• Count, Rep. with lt 4 customers (group)

18
Example 23 SQL Query to Restrict Groups

1. of customers are grouped by each sales rep
2. Apply the condition to the group

19
Example 23

• SELECT RepNum,
• count() AS NumCustomer,
• Avg(Balance) AS AverageBalance
• FROM Customer
• GROUP BY RepNum
• HAVING ___________

20
SQL Examples Grouping (cont)

• Use of Where and Having clauses together
• Where command must be stated first
• Example 23-1 Save as example 23-1
• Exactly same as example 23. Except, only groups
  with fewer than three records and customers with
  credit limit of less than 10,000 must be
  included.

21
Example 23-1 SQL Query with WHERE and HAVING
Clauses
22
Example 23-1

• SELECT RepNum, count() AS NumCustomer,
  Avg(Balance) AS AverageBalance
• FROM Customer
• ____________________
• GROUP BY RepNum
• HAVING Count()lt3

23
Processing Multiple Tables

• Type of Joins (driven from Set Theory)
• Equi-join
• Natural join
• Outer join Left or Right
• Union join
• Self join
• Each example in the textbook chapter 7

24
Example visualization of different join types
with results returned in shaded area
25
Figure 7-1 Pine Valley Furniture Company Customer
and Order tables with pointers from customers to
their orders
These tables are used in queries that follow
26
Equi-Join Example

• For each customer who placed an order, what is
  the customers name and order number?

The best way to find out match customers with
their orders is Including CustumerID from both
tables
27
SQL Examples Joining Tables

• Example 24 Save as example 24
• List the number and name of each customer
  together with the number, last name, and first
  name of the sales rep who represents the
  customer. If a customer does not have a Rep.,
  then he/she must not be included.
• That is, look for matching RepNum from both
  tables

28
Example 24 SQL Query to Join Tables
29
Example 24

• SELECT CustomerNum, CustomerName, Rep.RepNum,
  LastName, FirstName
• FROM Customer, Rep
• WHERE Rep.RepNumCustomer.RepNum

30
SQL Examples Joining Tables (cont)

• Use of multiple tables with a compound condition
• Example 25 Save as example 25
• List the number and name of each customer whose
  credit limit is 10,000 together with number,
  last name, and first name of the sales rep who
  represents the customer.

31
Example 25 Query to Restrict Records in Join
32
Example 25

• SELECT CustomerNum, CustomerName, Rep.RepNum,
  LastName, FirstName
• FROM Customer, Rep
• WHERE Rep.RepNum____________________
• AND CreditLimit10000

33
SQL Examples Joining Tables (cont)

• Example 26 Save as example
• For every order, list the order number, order
  date, customer number, and customer name. In
  addition, for each order line within the order,
  list the part number, description, number
  ordered, and quoted price. Make sure that
  everything is matched. In other words, any
  unmatched records must not be included.

34
Example 26 Query to Join Multiple Tables
35
Example 26

• SELECT Orders.OrderNum, Orderdate,
  Customer.CustomerNum, CustomerName, Part.PartNum,
  Description, NumOrdered, QuotedPrice
• FROM Orders, Customer, OrderLine, Part
• WHERE Customer.CustomerNum____________ AND
  Orders.OrderNum__________________
• AND OrderLine.PartNum_________________

36
SQL Examples Union

• The union of two tables is a table containing all
  rows that are in either the first table, the
  second table, or both tables.
• Two tables involved in union must have same
  structure.
• Example 27 Save as example 27
• List the number and name of all customers that
  are either represented by sales rep 35 OR that
  currently have orders on file, OR both.
• Any unmatched records must not be included.

37
Example 27 SQL Query to Perform Union Red
Currently have orders on file Blue Represented
by sales rep 35 Green Both
38
Example 27

• SELECT CustomerNum, CustomerName
• FROM Customer
• WHERE RepNum'35'
• UNION
• SELECT Customer.CustomerNum, CustomerName
• FROM Customer, Orders
• WHERE Customer.CustomerNumOrders.CustomerNum

39
Three Basic Functions by SQLAnd Their Basic SQL
Commands

1. Data definition (last topic) through the use of
   CREATE
2. Data manipulation (next topic) through INSERT,
   UPDATE, and DELETE
3. Data querying (we are done with this) through the
   use of SELECT AND MANY OTHERS, which is the basis
   for all SQL queries.

40
SQL - Data Manipulation

• Possible with Access
• UPDATE
• INSERT
• DELETE
• Only possible with enterprise level DBMS
• COMMIT
• ROLLBACK

41
SQL - Data Manipulation (cont)

• UPDATE command makes data entry corrections
• UPDATE Project
• SET PrjtLocat 'Bellaire', DeptNum 5
• WHERE PrjtNum 10
•  
• UPDATE Employee
• SET Salary Salary 1.1
• WHERE Branch 'Lincoln'

42
SQL - Data Manipulation (cont)

• INSERT command add new data to a table
• INSERT INTO Employee (SSN, LastName, FirstName)
• VALUES ('Richard', 'Marini', '43433')
• DELETE command removes table row
• DELETE FROM Employee
• WHERE LastName 'Brown'

43
SQL - Data Manipulation (cont)

• COMMIT command store data on the secondary memory
  permanently
• ROLLBACK command restores database back to
  previous condition if COMMIT hasnt been used

44
SQL Examples - Data Manipulation

• Example 28 Save as example 28
• Update the street address of customer 524 to 1445
  Rivard
• First, review the current street address of
  customer 524 (838 Ridgeland)

45
Example 28

• UPDATE Customer
• SET Street '1445 Rivard'
• WHERE CustomerNum'524'

46
SQL Examples - Data Manipulation

• Example 29 Save as example 29
• Add a new sales rep to the Rep table. Her number
  is 16, her name is Sharon Rands, and her address
  is 826 Raymond, Altonville, FL 32543. She has not
  yet earned any commission, but her commission
  rate is 5(0.05).
• First, review the Rep table

47
Example 29

• INSERT INTO Rep
• VALUES ('16', 'Rands', 'Shron', '826 Raymond',
  'Altonville', 'FL', '32543', 0, 0.05)

48
SQL Examples - Data Manipulation

• Example 30 Save as example 30
• Delete any row in the Orderline table in which
  the part number is BV06
• First, review the part number BV06
  (OrderNum21617)

49
Example 30

• DELETE
• FROM OrderLine
• WHERE PartNum'BV06'

50
SQL Examples Creating a New Table Using a
Existing Table

• Example 31 save as example 31
• Create a new table named SmallCust, consisting of
  all fields from the Customer table and those rows
  in which the credit limit is less than or equal
  to 7,500.
• SELECT
• INTO Name of table to create
• FROM
• WHERE

51
Example 31 SQL Query to Create New Table
52
Example 31

• SELECT INTO SmallCust
• FROM Customer
• WHERE CreditLimitlt7500

53
SQL - Data Definition I

• Create a database structure to hold all the
  database tables MS Access ONLY can create tables
• Usually, only a DBA can create a new database
  structure

SQL syntax for creating a database
structure CREATE SCHEMA AUTHORIZATION
ltcreatorgt ExampleCREATE SCHEMA AUTHORIZATION
JONES
54
SQL - Data Definition II

• Specify a new relation by giving it a name and
  specifying each of its attributes.
• Each attribute is given a name, a data type to
  specify its values, and some constraints on the
  attribute.
• Syntax
• CREATE TABLE lttable namegt

55
SQL Example Data Definition

• Example 32 Save as example 32
• Create a table call CSUB that contains
  following fields
• EmpID Number (vs. Number(9) or Num(9))
• LastName Char(20)
• FirstName Char(20)
• Street Char(30)
• City Char(20)
• State Char(2)
• Phone Number

56
Example 32 (cont)

• Using Access
• Create table CSUB
• (EmpID Number, LastName Char(20), FirstName
  Char(20), Street Char(30), City Char(20), State
  Char(2), Phone Number)
• Insert following values
• EmpID 123456789
• LastName your lastname
• FirstName your firstname
• Street 9001 Stockdale Hgwy
• City Bakersfield
• State CA
• Phone [phone]

57
Example 32

• INSERT INTO Employee
• VALUES ('987654321', 'Choi', 'Yong', '9001
  Stockdale', 'Bakersfield', 'CA', '123456789')

58
Using Oracle CREATE TABLE EMPLOYEE
(FNAME VARCHAR(15) NOT NULL, LNAME
VARCHAR(15) NOT NULL, SSN CHAR(9) NOT
NULL, BDATE DATE, SEX CHAR, SALARY
DECIMAL(10,2), SUPERSSN CHAR(9), DEPTNO
INT NOT NULL, PRIMARY KEY (SSN),
FOREIGN KEY (SUPERSSN) REFERENCES EMPLOYEE(SSN),
FOREIGN KEY (DNO) REFERENCES DEPARTMENT(DNUMBER)
)