SQL

1
SQL
2
Tutorials

• To understand some of the topics please analyze
  the following tutorials
• The following tutorials will help
• http//www.sqlcourse.com/intro.html
• http//www.w3schools.com/sql/default.asp

3
What can we do with SQL

• Querying Data
• Single Table
• Multiple Tables
• Inserting Data
• Updating Data
• Deleting Data

4
Querying Data

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

5
Querying Single Tables

• Basic Format
• SELECT column(s)
• FROM table
• WHERE condition(s)
• Rules
• SELECT must be first clause
• FROM must be second clause
• table/column names must be spelled as in the
  database

6
Selecting All Rows

• SELECT column(s)
• FROM table
• Example
• SELECT Customer_ID FROM Order_t

7
Selecting Unique Rows

• SELECT DISTINCT column(s)
• FROM table
• Example
• SELECT DISTINCT Customer_ID FROM Order_t

8
Search Conditions

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

9
Comparison Operators

• SELECT Order_ID, Order_Date
• FROM Order_t
• WHERE Customer_ID 1

10
Comparison Operators, cont...

• 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

11
Boolean Operators

• Boolean Operators
• AND all search conditions must be met
• OR any search condition must be met
• NOT a search condition must not be met
• SELECT Customer_ID, Order_ID, Order_Date FROM
  Order_t
• WHERE (Customer_ID 1)
• AND (Order_ID gt 1005)

12
Other Operators

• BETWEEN
• The BETWEEN ... AND operator selects an inclusive
  range of data between two values. These values
  can be numbers, text, or dates.
• SELECT Customer_ID, Order_Date FROM Order_t
• WHERE Customer_ID BETWEEN 1 AND 5

13
Wildcards

• SELECT
• FROM Order_t
• WHERE Customer_ID IN (1, 3, 5)
• You can use a "" to select all columns.
• LIKE
• Like is a very powerful operator that allows you
  to select only rows that are "like" what you
  specify. The percent sign "" can be used as a
  wild card to match any possible character that
  might appear before or after the characters
  specified.
• SELECT Customer_Name, Customer_ID
• FROM Customer_t
• WHERE Customer_Name LIKE (furn)
• SELECT Customer_Name, Customer_ID
• FROM Customer_t
• WHERE Customer_Name LIKE (furn_____)

14
Min and Max

• Finds minimum/maximum value of attribute
• SELECT MAX(Order_Date) FROM Order_t
• SELECT MIN(Order_Date)
• FROM Order_t
• WHERE Customer_ID gt 8

15
Nulls

• Means
• unknown
• not applicable
• SELECT Customer_Name
• FROM Customer_t
• WHERE Customer_address IS NULL

16
Querying Multiple Tables

• Joins
• Brings data together from multiple tables
• Same column in multiple tables
• Use table_name.column_name to distinguish columns
• Use WHERE clause to join tables
• Example
• SELECT Customer_t.Customer_ID,
• Customer_t.Customer_Name, Order_date
• FROM Customer_t, Order_t
• WHERE Order_date gt (11/01/98,MM/DD/YY)
• AND Customer_t.Customer_ID Order_t.Customer_ID
• Notice that the Customer_ID is the primary
  Key for Customer_t and Foreign key for order_t
  table
• This is the only way you can connect two tables
  !!!

17
Inserting Data

• INSERT INTO table_name
• (column1_name, column2_name, )
• VALUES
• (column1_value, column2_value, )
• Examples
• INSERT INTO Order_t (order_id, customer_id)
• VALUES
• (9000,8)

18
Updating Data

• UPDATE table_name
• SET column1_name new_value ,column2_name
  new value,
• WHERE condition(s)
• Example
• UPDATE Order_t
• SET Customer_ID 2
• WHERE Customer_ID 1

19
Deleting Data

• DELETE FROM table_name
• WHERE condition(s)
• Examples
• DELETE FROM Order_t
• WHERE Customer_ID 2