SQL Tutorial - PowerPoint PPT Presentation

Why and how: I created this presentation as a college assignment. It contains sql basics.

Transcript and Presenter's Notes

Title: SQL Tutorial

• By Hitesh Sahni
• www.hiteshsahni.com

• Overview of SQL (This may be review for some of
  you)
• Data Definition Language
• Creating tables (well just talk about this)
• Data Manipulation Language
• Inserting/Updating/Deleting data
• Retrieving data
• Single table queries
• Where
• Joins
• Grouping

• SQL is a data manipulation language.
• SQL is not a programming language.
• SQL commands are interpreted by the DBMS engine.
• SQL commands can be used interactively as a query
  language within the DBMS.
• SQL commands can be embedded within programming
  languages.

• Data Definition Language (DDL)
• Commands that define a database - Create, Alter,
  Drop
• Data Manipulation Language (DML)
• Commands that maintain and query a database.
• Data Control Language (DCL)
• Commands that control a database, including
  administering privileges and committing data.

• Four basic commands
• INSERT
• UPDATE
• DELETE
• SELECT

• SQLgtROLLBACK
• Rollback complete.
• REVERSES ALL CHANGES TO DATA MADE DURING YOUR
  SESSION
• SQLgtCOMMIT
• MAKES ALL CHANGES TO THIS POINT PERMANENT
• POINTS AT WHICH COMMIT IS ISSUED, DEFINE EXTENT
  OF ROLLBACK
• ROLLBACK REVERSES EVERY CHANGE SINCE THE LAST
  COMMIT
• EXITING SQLPLUS ISSUES A COMMIT

• SELECT column_name, column_name,
• FROM table_name
• WHERE condition/criteria
• This statement will retrieve the specified field
  values for all rows in the specified table that
  meet the specified conditions.
• Every SELECT statement returns a recordset.

• Semantics of an SQL query defined in terms of
  the following conceptual evaluation strategy
• Compute the cross-product of relation-list.
• Discard resulting tuples if they fail
  qualifications.
• Delete attributes that are not in target-list.
• If DISTINCT is specified, eliminate duplicate
  rows.
• This strategy is probably the least efficient way
  to compute a query! An optimizer will find more
  efficient strategies to compute the same answers.

• - All columns in a table
• Aliases
• SELECT EmployeeID, LastName, FirstName, BirthDate
  AS DOB FROM Employee
• SELECT EmployeeID, LastName, FirstName, FROM
  Employee AS E
• Dot Notation - ambiguous attribute names
• SELECT Customer.LName, E.Lname
• FROM Customer, Employee AS E
• WHERE .

• DISTINCT
• Arithmetic operators , -, , /
• Comparison operators , gt, gt, lt, lt, ltgt
• Concatenation operator
• Substring comparisons , _
• BETWEEN
• AND, OR

• ORDER BY Clause
• UNION, EXCEPT, INTERSECT
• IN

• SQL provides two ways to retrieve data from
  related tables
• Join - When two or more tables are joined by a
  common field.
• Subqueries - When one Select command is nested
  within another command.

• Joins
• The WHERE clause is used to specify the common
  field.
• For every relationship among the tables in the
  FROM clause, you need one WHERE condition (2
  tables - 1 join, 3 tables - 2 joins)

• Inner Join - records from two tables are
  selected only when the records have the same
  value in the common field that links the tables
  (the default join).
• Outer Join - A join between two tables that
  returns all the records from one table and, from
  the second table, only those records in which
  there is a matching value in the field on which
  the tables are joined.

• Plan your joins
• Draw a mini-ERD to show what tables are involved.
• Count the number of tables involved in the SELECT
  query.
• The number of joins is always one less than the
  number of tables in the query.
• Watch out for ambiguous column names.

• These functions are applied to a set(s) of
  records/rows and return one value for each set.
• Count ()
• Min ()
• Max ()
• Sum ()
• Avg ()
• These functions thus aggregate the rows to which
  they are applied.

• If one field in a Select clause is aggregated,
  all fields in the clause must be aggregated.
• Aggregation The process of transforming data
  from a detail to a summary level.
• You can aggregate a field by including it after
  the GROUP BY clause or by making it the argument
  of an aggregating function.

• When you use GROUP BY, every field in your
  recordset must be aggregated in some manner.
• The same rule applies when you use an aggregating
  function such as SUM, COUNT, AVERAGE . If one
  field in the Select clause is aggregated, then
  every other field in the Select clause must be
  aggregated in some manner.

• Additional SQL Clause - HAVING
• The HAVING clause is only used after the GROUP BY
  clause.
• The HAVING clause specifies criteria for a GROUP,
  similar to how the WHERE clause specifies
  criteria for individual rows.

• SELECT - list of attributes and functions
• FROM - list of tables
• WHERE - conditions / join conditions
• GROUP BY - attributes not aggregated in select
  clause
• HAVING - group condition
• ORDER BY - list of attributes

• ISM6217 - Advanced Database

• Subqueries (Nested queries)
• Example
• Correlated subquery
• Join types
• Inner/outer
• Integrity constraints
• Triggers
• Functions

• Scripts to create and populate the database are
  available on the 6217 Web site.

• A subquery is a query that is used in the WHERE
  condition of another query
• AKA Nested query
• Can be multiple levels of nesting
• Can be used with SELECT, INSERT, UPDATE

• List all suppliers who can deliver at least one
  product in less than 10 days

• List all suppliers who can deliver a product in
  less than the average delivery time.

• LIST SUP_NO, PART, DEL FOR QUOTES WHERE DEL gt ANY
  SUPPLIED BY 71

• LIST SUP_NO, PART, DEL FOR QUOTES WHERE DEL gt ALL
  SUPPLIED BY 71

• Who are alternate suppliers for parts supplied by
  71?

• List all suppliers who have not provided a quote

• A correlated subquery is a subquery that is
  evaluated once for each row processed by the
  parent statement. The parent statement can be a
  SELECT, UPDATE, or DELETE statement. These
  examples show the general syntax of a correlated
  subquery

• List all suppliers, parts and prices where quoted
  price is less than the average quote for that
  part.

• Natural join/inner join
• This is what youre used to.
• Returns only rows where PK and FK values match.
• Does not repeat PK/FK columns
• Equi-Join
• Similar to natural join, but includes both PK and
  FK values in record set.

• Outer join
• Includes columns with null FK values
• Problem Inner join will not return a row that
  does not have a matching value.
• Sometimes this prevents you from getting the
  output you want.
• Example List all parts (including description)
  and any quotes that exist for each part. We want
  to include all parts even if there are no quotes
  for some of them.

• SELECT I.PART_NO, DESCRIPTION, SUPPLIER_NO, PRICE
• FROM INVENTORY I, QUOTATIONS Q
• WHERE I.PART_NO Q.PART_NO ()
• ORDER BY I.PART_NO

• SELECT I.PART_NO, DESCRIPTION, SUPPLIER_NO, PRICE
• FROM INVENTORY I, QUOTATIONS Q
• WHERE I.PART_NO () Q.PART_NO
• ORDER BY I.PART_NO

• Field values in a tuple are sometimes unknown
  (e.g., a rating has not been assigned) or
  inapplicable (e.g., no spouses name).
• SQL provides a special value null for such
  situations.
• The presence of null complicates many issues.
  E.g.
• Special operators needed to check if value is/is
  not null.
• Is ratinggt8 true or false when rating is equal to
  null? What about AND, OR and NOT connectives?
• We need a 3-valued logic (true, false and
  unknown).
• Meaning of constructs must be defined carefully.
  (e.g., WHERE clause eliminates rows that dont
  evaluate to true.)
• New operators (in particular, outer joins)
  possible/needed.

• An IC describes conditions that every legal
  instance of a relation must satisfy.
• Inserts/deletes/updates that violate ICs are
  disallowed.
• Can be used to ensure application semantics
  (e.g., sid is a key), or prevent inconsistencies
  (e.g., sname has to be a string, age must be lt
  200)
• Types of ICs Domain constraints, primary key
  constraints, foreign key constraints, general
  constraints.
• Domain constraints Field values must be of
  right type. Always enforced.

• Useful when more general ICs than keys are
  involved.
• Can use queries to express constraint.
• Constraints can be named.

• Awkward and wrong!
• If Sailors is empty, the number of Boats tuples
  can be anything!
• ASSERTION is the right solution not associated
  with either table.

• Trigger procedure that starts automatically if
  specified changes occur to the DBMS
• Three parts
• Event (activates the trigger)
• Condition (tests whether the triggers should run)
• Action (what happens if the trigger runs)

• CREATE TRIGGER youngSailorUpdate
• AFTER INSERT ON SAILORS
• REFERENCING NEW TABLE NewSailors
• FOR EACH STATEMENT
• INSERT
• INTO YoungSailors(sid, name, age, rating)
• SELECT sid, name, age, rating
• FROM NewSailors N
• WHERE N.age lt 18

• ABS (n)
• MOD (m,n)
• Returns the remainder of m/n
• POWER (m,n)
• ROUND(n,m)
• SIGN(n)
• SQRT
• TRUNC(15.79,1)

• CONCAT(char1, char2)
• LOWER/UPPER
• LTRIM(char ,set) RTRIM(char ,set
• SUBSTR(char, m ,n)
• LENGTH(char)

• ADD_MONTHS(d,n)
• LAST_DAY(d)
• MONTHS_BETWEEN(d1, d2)
• ROUND(d,fmt)
• SYSDATE
• TO_CHAR(d , fmt , 'nlsparams' )
• TO_DATE(char , fmt , 'nlsparams' )

• DICTIONARY
• All tables and views that are available to the
  user
• This table contains several hundred rows
• Useful Data Dictionary Views
• Use just like a table
• More useful (generally) than full tables
• Use DESCRIBE to see the columns in the view
• USER_TABLES
• USER_VIEWS
• USER_CONSTRAINTS
• USER_OBJECTS

• www.hiteshsahni.com