Introduction to SQL

1
Introduction to SQL

• G. Green

2
Querying Data

• Querying Single Tables
• Basic Query Format
• Search Conditions
• Sorting Results
• Querying Multiple Tables

3
Querying Single Tables

• Basic Format
• SELECT column(s)
• FROM table
• WHERE condition(s)
• Rules
• SELECT must be first clause
• FROM must be second clause
• WHERE, if used, must be the third clause
• ORDER BY, if used, must be the last clause
• Table/column names must be spelled as in the
  database
• Semi-colon must be at the end of the query

4
Selecting All Records

• SELECT column(s)
• FROM table
• List all order numbers.
• SELECT order_id FROM orders

5
Selecting All Columns

• SELECT
• FROM table
• List all information about all products.
• SELECT FROM product

6
Search Conditions

• For retrieving specific rows
• Comparison Operators
• Boolean Operators
• Special Operators
• Expressions
• SELECT column(s)
• FROM table
• WHERE ltsearch condition(s)gt

7
Comparison Operators

• Comparison Operators
• equal to
• gt greater than
• lt less than
• gt greater than or equal to
• lt less than or equal to
• ltgt not equal to

8
Comparison Operators, cont

• Show order IDs and dates of all orders placed by
  customer 1.
• SELECT Order_ID, Order_Date FROM Orders WHERE
  Customer_ID 1

9
Boolean Operators

• Boolean Operators
• AND all search conditions must be met
• OR any search condition must be met
• Show the customer ID, order ID, and order date
  for orders placed by customer 1 with an order ID
  higher than 1005.
• SELECT Customer_ID, Order_ID, Order_Date FROM
  Orders WHERE Customer_ID 1 AND Order_ID gt 1005

10
Boolean Operators, cont

• Show descriptions, finish, and price of products
  that either have a natural ash finish or are
  priced under 300.
• SELECT product_description, product_finish,
  standard_price FROM product
  WHERE product_finish natural ash
  OR standard_price lt 300

11
Special Operators

• Null means
• Value unknown for this record
• Value not applicable for this record
• List customers who do not have an owner.
• SELECT Customer_Name
  FROM Customer WHERE
  owner_id IS NULL
• Beware
• SELECT Customer_Name
  FROM Customer
  WHERE owner_id NULL

12
Expressions

• Manipulating columns in query
• List all natural ash products that would exceed
  400 if their prices were increased by 10.
• SELECT Product_ID, Product_finish,
  Standard_Price FROM Product WHERE
  Standard_Price1.1 gt 400 AND product_finish
  natural ash

13
Sorting Query Results

• ORDER BY column_name(s) ASCDESC
• Sorts results in ascending or descending order of
  column(s)
• List customer names and addresses
• in descending order of customer name.
• SELECT Customer_name, Customer_address
  FROM Customer
  
  ORDER BY Customer_name
  DESC

14
Sorting Query Results, cont

• List all natural ash products that would exceed
  400 if their prices were increased by 10. Show
  results from lowest to highest quantity on hand.
• SELECT Product_ID, Product_finish,
  Standard_Price FROM Product WHERE
  standard_Price1.1 gt 400 AND product_finish
  natural ash ORDER BY qty_on_hand

15
Querying Multiple Tables

• Joins
• Brings data together from multiple tables
• Use common column in both rows/tables
• Use table_name.column_name to distinguish columns
  with same name
• Use FROM clause to reference each table
• Use WHERE clause to join/connect tables
• Retrieve Customer Names, Customer IDs, and Order
  Dates of orders placed after November 1, 2000.
• SELECT Customer.Customer_ID, Customer_Name,
  Order_date
• FROM Customer, Orders
• WHERE Order_date gt 11/1/2000
• AND Customer.Customer_ID
  Orders.Customer_ID

16
Querying Multiple Tables

• Retrieve Customer Names, Customer IDs, and Order
  Dates of orders placed after November 1, 2000.
• SELECT Customer.Customer_ID,
• Customer_Name, Order_date
• FROM Customer, Orders
• WHERE Order_date gt 11/1/2000
• AND Customer.Customer_ID
  Orders.Customer_ID

17
Querying Multiple Tables, cont

• List order number, product number, and
  descriptions of products ordered by customer 1.
  Show only those products with an order number
  less than 1010.
• SELECT Orders.Order_ID, Product.Product_ID,
  Product_description
• FROM Order_Line, Orders, Product
• WHERE Orders.Customer_ID 1
• AND Orders.Order_ID Order_Line.Order_ID
• AND Order_Line.Product_ID Product.Product_ID
• AND Orders.Order_ID lt 1010