SQL

1
SQL

• Retrieve Data
• Efficiently and Accurately

2
Contents

• Vocabulary
• RDBMS SQL
• Selecting Rows
• Limiting selected data
• Retrieving data from multiple tables
• Single row functions
• Group functions
• Database Objects
• JDBC

3
Vocabulary

• RDBMS Relational Database Management System
• Table
• Basic storage structure of an RDBMS
• Consists of columns and rows (records)
• EMP table

Column
Row
Field
4
RDBMS SQL

• RDBMS
• Basic functions in a RDBMS include
• Storage of data
• Access control to data
• Mechanisms for retrieval and modification of data
• A database is a collection of individual, named
  objects, such as tables, indexes.
• Some commercial and open source RDBMS are
  Oracle,DB2, MySQL, SQL Server, Sybase, PostgreSQL
  etc.

5
RDBMS SQL ..

• SQL (Structured Query Language)
• SQL commands used to access the database
• SQL can be used by range of users
• Non-procedural language (Procedural extension ex.
  Oracle PLSQL)
• English-like language

6
RDBMS SQL ..

• History of SQL
• Mid 1970s, developed under the name SEQUEL at
  IBM San Jose research facility.
• Renamed in 1980 to SQL
• ANSI committees in 1986 1987 produced a
  standards document which lends for the
  portability of SQL.

7
RDBMS SQL ..

• Types of SQL commands
• DDL (Data definition language)
• CREATE Create objects in the database
• ALTER Alters the structure of database
• DROP Drop objects
• TRUNCATE Removes all records from the table.
• COMMENT Add comments to the objects
• RENAME Rename objects

8
RDBMS SQL ..

• Types of SQL commands
• DML (Data manipulation language)
• SELECT Retrieve data from database
• INSERT Insert records in a table
• UPDATE Update records in a table
• DELETE Delete records from the table.
• MERGE(UPSERT) Insert or Update

9
RDBMS SQL ..

• Types of SQL commands
• TCL (Transaction control language)
• COMMIT Commit a transaction
• ROLLBACK Rollback a transaction
• SAVEPOINT Identify a point in transaction to
  which can later rollback.

10
SQLPlus

• An Oracle tool that recognizes and executes SQL
  statements
• iSQLPlus is a web-based utility similar to the
  SQLPlus command line utility for executing SQL
  and PL/SQL commands.

11
iSQLPlus

• Login to iSQLPlus

12
Selecting Rows

• SELECT statement retrieves information from the
  database
• Implements all algebraic operators
• Example
• SELECT ename
• FROM emp
• WHERE ename Smith

13
Selecting Rows..

• Writing SQL commands.
• Commands can be on one or more lines.
• Tabs and indents can be used for readability.
• Abbreviation and splitting of words are not
  allowed.
• Commands are not case sensitive.
• In its simplest form, SELECT must contain the
  following
• SELECT clause, which specifies columns to be
  displayed
• FROM clause, which specifies tables containing
  columns in SELECT clause.
• The asterisk() selects all columns from the
  table.
• Eg. SELECT
• FROM emp

14
Selecting Rows..

• To select specific columns, you must
• List the To select specific columns, you must
• List the columns in the SELECT clause
• Separate the columns by a comma
• Specify columns in order you want them to appear
• Eg. SELECT empno,ename,job FROM emp
• To restrict the result set, WHERE clause is used.
• SQL allows use of arithmetic expressions on
  numbers and dates
• Multiplications and division take priority over
  subtraction and addition
• Operators of same priority are evaluated from
  left to right
• Parenthesis used to force prioritized evaluation
  and clarity
• SELECT 55-55 from dual gt 10
• SELECT 55-(55) from dual gt 0.

15
Selecting Rows..

• Column alias immediately follows the SELECTed
  column.
• Double quote required if alias contains spaces.
• Eg. SELECT ename Employee Name
• FROM emp
• WHERE deptno 10
• Concatenation operator used to concatenate
  character strings
• Eg. SELECT ename is a job
• FROM emp
• WHERE deptno 20

16
Selecting Rows..

• NULL value is a value that is unavailable or
  unassigned.
• NULL is not the same as zero or space.
• Arithmetic expressions containing NULL values
  evaluate to NULL
• NVL (null value locator) is used to convert null
  to any value
• NVL can be used for datatypes like
  Number,Date,Characters
• Eg. SELECT nvl(comm,0)
• FROM emp
• WHERE deptno 10

17
Selecting Rows..

• Unless specified otherwise, results of a query
  will be displayed without eliminating duplicates.
• DISTINCT keyword is used to eliminate the
  duplicates.
• DISTINCT applies to all the SELECTed columns. The
  result set represents distinct combination of the
  SELECTed columns.
• Eg. SELECT DISTINCT ename,job
• FROM emp
• WHERE deptno 10

18
Selecting Rows Summary

• Data is retrieved from a database using the
  SELECT statement.
• The asterisk () selects all columns.
• Aliases are used to change columns headings.
• There are arithmetic, concatenation, and NULL
  value operators.
• DISTINCT keyword is used to eliminate duplicates.

19
Limiting Selected rows

• Order of rows returned by a query is undefined.
• ORDER BY clause may be used to sort the rows.
• SELECT ename, sal
• FROM emp
• WHERE deptno 10
• ORDER BY sal DESC
• ORDER BY clause options
• ASC Ascending order (Default)
• DESC Descending order.

20
Limiting Selected rows..

• WHERE clause used to restrict result set.
• WHERE clause contains conditions that must be
  met.
• Directly follows the FROM clause.
• Characters and dates must be enclosed in single
  quotes, numbers need not.
• Ex. SELECT ename,sal
• FROM emp
• WHERE sal gt 100
• AND ename like Smith

21
Limiting Selected rows..

• Comparison operators are used in WHERE clause to
  compare expressions.
• Two types of comparison operators
• Logical SQL
• Comparison operators
• Equal To
• gt Greater than
• gt Greater than or equal to
• lt Less than
• lt Less than or equal to

22
Limiting Selected rows..

• SQL operators
• BETWEEN Between two values inclusive
• WHERE hiredate between sysdate and 01-oct-99
• IN (list) Match any of a list of values.
• WHERE ename in (Smith,John,King)
• Like Match a character pattern.
• WHERE ename like Sm
• IS NULL Is a null value.
• WHERE comm is null

23
Limiting Selected rows..

• SQL operators
• EXISTS Condition exists in subquery
• WHERE exists (select 1 from emp where mgr is
  null)
• ANY/ ALL Combines with arithmetic operator

24
Limiting Selected rows..

• Logical operators
• AND If both the component conditions are true
  then result is true.
• WHERE comm is null
• AND deptno 10
• OR If either component condition is true, then
  result is true.
• WHERE comm is null
• OR sal lt 1000
• NOT Returns the opposite condition.
• WHERE deptno NOT IN (10,20,30)

25
Limiting Selected rows..

• Combining results from multiple SQLs
• UNION ALL- Combines results, keeps duplicates
• SELECT ename FROM emp WHERE deptno 10
• UNION ALL
• SELECT ename FROM emp WHERE deptno 20
• UNION - Combines results, eliminates duplicates
• SELECT ename FROM emp WHERE deptno 10
• UNION
• SELECT ename FROM emp WHERE deptno 20

26
Limiting Selected rows..

• Combining results from multiple SQLs
• MINUS Takes Result set of one SQL and removes
  those rows which are also returned by second SQL.
• SELECT location FROM dept WHERE deptno 10
• MINUS
• SELECT location FROM dept WHERE deptno 20
• INTERSECT Returns only those rows which are
  common in each of the SQLs
• SELECT ename FROM emp WHERE deptno 10
• INTERSECT
• SELECT ename FROM emp WHERE deptno 20

27
Limiting Selected rows summary

• Order by clause is used to override default
  ordering of a result set.
• WHERE clause is used to restrict result set.
• Logical and SQL operators are used in WHERE
  clause to restrict the result.
• SET operations can be used to combine results of
  multiple SQLs.

28
Retrieving data from multiple tables

• Join is used to retrieve data from multiple
  tables.
• Rows are joined using common values existing in
  corresponding columns
• Ex. emp.deptno dept.dno.
• Join Methods
• EquiJoin
• Non-Equijoin
• Outer join
• Self join

29
Retrieving data from multiple tables..

• When a join condition is invalid or omitted, then
  result is a cross product (cartesian join).
• Result set contains all rows from first table
  joined with all rows from second table.
• Ex. SELECT ename, dname
• FROM emp,dept

30
Retrieving data from multiple tables..

• An equijoin is created when relationship between
  two table values are equal
• SELECT e.ename,d.dname
• FROM emp e, dept d
• WHERE e.deptno d.no
• AND e.sal lt 1000
• Column name needs to be qualified in WHERE clause
  to avoid ambiguity.

31
Retrieving data from multiple tables..

• A Non-equijoin is created when no column value in
  one table corresponds directly to other table
• SELECT e.ename,d.dname
• FROM emp e, dept d, location l
• WHERE e.deptno d.no
• AND d.location_id l.id
• AND e.sal lt 1000
• AND l.location_name in (A,B)

32
Retrieving data from multiple tables..

• Rows not retrieved by equi/non-equi join
  conditions can be retrieved using Outer join
  operator (). Outer join operator is applied on
  the deficient side of the condition.
• Ex. Display all employees belonging to deptno 10
  as well as all other department names.
• SELECT e.ename,d.dname
• FROM emp e, dept d
• WHERE e.deptno() d.no
• AND e.deptno() 10
• Types of outer join
• Left outer join
• Right outer join
• Full outer join

33
Retrieving data from multiple tables..

• Self joins link rows in a table to rows in the
  same table.
• Same table is included more than once in the FROM
  clause.
• Ex. Display employee name along with his
  managers name.
• SELECT e1.ename nvl(e2.ename,'No
  manager')
• FROM emp e1, emp e2
• WHERE e1.mgr e2.empno()

34
Retrieving data from multiple tables Summary

• Multiple methods can be used to join more than
  one table
• Equijoin
• Non-equijoin
• Outer join
• Self join
• Cartesian join is created if join condition is
  missed between the tables.
• Table aliases should be used to avoid ambiguity.

35
Single row functions

• Manipulate data items
• Accept arguments and return one value
• Act on each row returned
• Return one result per row
• Modify the datatype
• Can be nested

36
Single row functions..

• Character functions
• Lower() Converts to lowercase
• Upper() Converts to uppercase
• Substr Returns substring
• Length returns length of the string
• NVL Converts a null.
• Number functions
• Round Rounds value to the specified decimal
• Trunc Truncates the value to the specified
  decimal
• Mod Returns reminder of a division
• Date functions
• Months_between
• Add_months
• Next_day
• Round
• Trunc
• Conversion functions
• To_char
• To_date

37
Single row functions summary

• Single row functions work on character, number
  and date datatype.
• Return one result per row
• Can be nested

38
Group functions

• Operate on sets or rows
• Give one result per group
• By default, all the rows in a table are treated
  as one group.
• Group by used to divide rows into smaller groups
  as needed.

39
Group functions..

• Various group functions
• Count
• Sum
• Avg
• Max
• Min
• Stddev
• Variance

40
Group functions..

• Group by guidelines
• If group function is included in SELECT,
  individual columns cant be selected, unless
  individual column appears in Group By.
• WHERE clause used to pre-exclude rows before
  dividing them into groups.
• Column alias cant be used in Group By clause.
• By default, result set is sorted in Ascending
  order. Order by should be used to change the
  ordering.
• HAVING clause used to specify which groups are to
  be displayed. This is used to further restrict
  the groups on the basis of aggregate information.

41
Group functions..

• Order of execution
• Rows are grouped
• Group functions applied
• HAVING clause is applied
• Order By applied
• Examples
• select deptno,count()
• from (select sal,deptno from emp where sal gt 100)
• group by deptno
• having count() gt 3

42
Group functions summary

• Group By is used to create subgroups
• Groups can be excluded using HAVING clause
• Result set can be sorted by Order By.
• Order of execution
• Rows are grouped
• Group function applied
• HAVING clause applied
• Order By applied.

43
Database Objects

• Tables
• Constraints
• Indexes
• Views
• Stored procedures/functions
• Sequences etc.

44
Database Objects..

• Tables
• Basic unit of data storage
• Constraints
• Primary key
• Foreign key
• Unique key
• Check
• Indexes
• Unique index
• Composite index
• Bitmap index
• Function based index

45
Database Objects..

• Views
• Tailored presentation of data in one or more
  tables
• Avoids complexity of joins by acting as a virtual
  table
• Read only or modifiable
• Can provide additional security

46

• SQL Assignments

47
JDBC

• Contents
• JDBC Introduction
• Architecture
• Driver Types
• Result Set
• Transactions
• Prepared statements
• Stored Procedures

48
Introduction

• JDBC- Java DataBase Connectivity
• JDBC Product components
• JDBC API
• JDBC Driver Manager
• JDBC Test suite
• JDBC-ODBC Bridge

49
JDBC API

• JDBC API
• Java API to access RDBMS
• Connect to database
• Send queries and updates to db
• Retrieve and process the results

50
JDBC API

• Connection Example
• Class.forName(sun.jdbc.odbc.JdbcOdbcDriver)
• Connection con DriverManager.getConnection (
  "jdbcmyDriverwombat", "myLogin","myPassword")
• Statement stmt con.createStatement()
• ResultSet rs stmt.executeQuery("SELECT a, b, c
  FROM Table1")
• while (rs.next())
• int x rs.getInt("a") String s
  rs.getString("b") float f rs.getFloat("c")

51
JDBC API

• Class.forName(sun.jdbc.odbc.JdbcOdbcDriver)
• Loads the driver class to memory.
• Connection con DriverManager.getConnection (
  "jdbcmyDriverwombat", "myLogin","myPassword")
• instantiates a DriverManager object to connect to
  a database driver
• logs into the database
• Statement stmt con.createStatement()
• instantiates a Statement object that carries your
  SQL language query to the database
• Connection interface has a method to create
  statements.

52
JDBC API

• ResultSet rs stmt.executeQuery("SELECT a, b, c
  FROM Table1")
• Statement object sends SQL query with
  executeQuery method
• instantiates a ResultSet object that retrieves
  the results of your query.
• while (rs.next())
• int x rs.getInt("a")
• String s rs.getString("b")
• float f rs.getFloat("c")
• executes a simple while loop
• retrieves those results

53
Architecture

• Two tier model

54
Architecture..

• Three tier model

55
JDBC Driver

• Implements JDBC API interfaces and classes for a
  particular db
• Specified JDBC driver is loaded before connection
  is made to db
• JDBC DriverManager class then sends all JDBC API
  calls to the loaded driver

56
JDBC Driver..

• Four types
• Type 1 (JDBC-ODBC bridge, plus ODBC driver)
• Translates JDBC API calls into Microsoft Open
  Database Connectivity (ODBC) calls which are
  passed to ODBC driver
• ODBC binary code must be loaded on every client
  computer
• Type 2 (Native-API, partly java driver)
• Converts JDBC API calls into db-specific client
  API calls
• Requires some binary code loaded on each client
  computer

57
JDBC Driver

• Type 3 (JDBC-Net, pure Java driver)
• Sends JDBC API calls to middle-tier net server,
  that translates calls into db-specific network
  protocol.
• Translated calls are then sent to a particular
  DB.
• Type 4 (Native-protocol, pure java driver)
• Converts JDBC API calls directly into db-specific
  network protocol without middle-tier.
• Client application directly connect to the DB
  server.

58
Transactions

• What is a transaction?
• Commit
• Rollback
• Savepoint
• Locks

59
Transactions..

• Committing a transaction
• Autocommit (can be set to false)
• Explicit commit (can use multiple statements in a
  transaction)
• Locks
• What are locks?
• Isolation level
• Rollback
• Savepoint

60
ResultSet

• What is a ResultSet Interface?
• Cursors
• Retrieving and manipulating query results
• ResultSet type and concurrency
• Ex.
• Statement stmt con.createStatement
• (ResultSet.TYPE_SCROLL_SENSITIVE,
  ResultSet.CONCUR_READ_ONLY)
• ResultSet srs stmt.executeQuery
• ("SELECT COF_NAME, PRICE FROM COFFEES")

61
ResultSet..

• ResultSet types
• TYPE_FORWARD_ONLY
• TYPE_SCROLL_INSENSITIVE
• TYPE_SCROLL_SENSITIVE
• ResultSet concurrency
• CONCUR_READ_ONLY
• CONCUR_UPDATABLE

62
ResultSet..

• ResultSet methods
• next() Moves cursor forward.
• previous() Moves the cursor backwards.
• first() Moves the cursor to first row in result
  set.
• last() Moves the cursor to the last row in
  result set.
• beforefirst() Positions cursor before first row
  of set.
• afterlast() Positions cursor after last row of
  set.
• relative(int rows) Moves cursor relative to
  current position.
• absolute(int n) Positions the cursor on nth row
  of the object.

63
ResultSet..

• Getter methods
• getBoolean, getLong, getFloat, getString etc.
• Can pass column index or column name to getters
• Ex.
• Statement stmt con.createStatement(ResultSet.TYP
  E_SCROLL_INSENSITIVE,
• ResultSet.CONCUR_READ_ONLY)
• ResultSet srs stmt.executeQuery(
• SELECT COF_NAME, PRICE FROM COFFEES")
• srs.afterLast()
• while (srs.previous())
• String name srs.getString("COF_NAME")
• float price srs.getFloat("PRICE")
• System.out.println(name " " price)

64
Making updates

• Statement objects executeUpdate(sql) method used
  for insertions,deletions,updates etc.
• Creating tables
• s.executeUpdate(CREATE TABLE Persons (FirstName
  VARCHAR(15), LastName VARCHAR(15)))
• Creating a table named Persons with two columns
  which have 15 characters FirstName and LastName.

65
Making updates

• Insert, Update, Delete examples
• s.executeUpdate(INSERT INTO Persons VALUES
  (ABC, XYZ))
• s.executeUpdate(DELETE FROM Persons WHERE
  FirstName ABC)
• s.executeUpdate(UPDATE Persons SET LastName
  PQR WHERE FirstName APC)

66
JDBC Datatypes

• BIGINT,VARCHAR,FLOAT,DOUBLE,BINARY,DATE,TIME,TIMES
  TAMP etc.

67
Prepared statements

• What is a prepared statement?
• Advantages of precompiled SQL
• Ex.
• String updateString "UPDATE COFFEES SET SALES
  75 " "WHERE COF_NAME LIKE 'Colombian'"
  stmt.executeUpdate(updateString)
• PreparedStatement updateSales
  con.prepareStatement( "UPDATE COFFEES SET SALES
  ? WHERE COF_NAME LIKE ? ")
• updateSales.setInt(1, 75)
• updateSales.setString(2, "Colombian")
• updateSales.executeUpdate()

68
Stored procedures

• Logical group of SQL statements
• Input, Output and Inout parameters
• Callable statement
• Ex.
• CallableStatement cs con.prepareCall("call
  SHOW_SUPPLIERS")
• ResultSet rs cs.executeQuery()

69
More information

• Java s documentation page
• http//java.sun.com/j2se/1.4/docs/guide/jdbc/index
  .html
• JDC tutorials
• http//developer.java.sun.com/developer/Books/JDBC
  Tutorial/
• jGuru tutorials
• http//developer.java.sun.com/developer/onlineTrai
  ning/Database/JDBC20Intro/JDBC20.htmlJDBC208
• Other
• http//www.techtutorials.info/javajdbc.html

70
Example
71

• Thank You