STRUCTURED QUERY LANGUAGE SQL - PowerPoint PPT Presentation

Loading...

PPT – STRUCTURED QUERY LANGUAGE SQL PowerPoint presentation | free to view - id: 5cb53-ZTg4N



Loading


The Adobe Flash plugin is needed to view this content

Get the plugin now

View by Category
About This Presentation
Title:

STRUCTURED QUERY LANGUAGE SQL

Description:

SQL is desired when communicating between web pages and database ... Oracle: PL/SQL. SQL Server: Transact-SQL. MySQL. Advanced Database. 5. Microsoft Access ... – PowerPoint PPT presentation

Number of Views:350
Avg rating:3.0/5.0
Slides: 35
Provided by: Facu131
Category:

less

Write a Comment
User Comments (0)
Transcript and Presenter's Notes

Title: STRUCTURED QUERY LANGUAGE SQL


1
STRUCTURED QUERY LANGUAGE (SQL)
SELECT FROM … WHERE ….
INSERT INTO frank VALUES (ASSO, Associate)
  • Prepared By Cheryn Mok

2
What is SQL?
  • Originally developed by IBM in 1970s
  • It is the standard query language for
    communicating with relational databases
  • SQL is desired when communicating between web
    pages and database
  • Universal rules established by ANSI (American
    National Standards Institute), accepted by
    database vendors
  • Structure and syntax same across most DBMS
    (Oracle, DB2, SQL Server, Access, Sybase…)

3
Structured Query Language - Intro
  • What does it do?
  • Create new records holding data
  • Read existing data
  • Update existing data
  • Delete data
  • What does it not do?
  • Not a software product but a language
  • No front end does not have user forms
  • No back end no tools that store data. Not a
    procedural language. It is a set-based language,
    which communicates in statements that define an
    outcome.

4
SQL Standards
  • Open standard
  • ANSI-SQL / pure SQL
  • Vendors differentiate with extra features,
    enhancements
  • Oracle PL/SQL
  • SQL Server Transact-SQL
  • MySQL

5
Microsoft Access
  • Serve as both back and and front end
  • Separated to
  • Application environment (user interface to create
    tables database objects, manage data database
    objects
  • Jet database engine (DBMS manages tables and
    processes SQL commands)
  • Open Database Connectivity (ODBC)
  • Standard communication protocol for the database
    application to interact with different DBMS

6
Open Database Connectivity
Database Application
Access ODBC driver
SQL Server ODBC driver
Oracle ODBC driver
Jet Database Engine
SQL Server DBMS
Oracle DBMS
7
Access Queries
  • Query question that can be answered by data
    stored in a database
  • Data retrieved from single or multiple tables
  • Can include conditions, mathematical functions,
    group retrieved records, and perform group
    functions
  • How to create a query
  • Click (gt) on the Queries object
  • gt New on the toolbar
  • gt Design View on the
  • New Query window

8
Access Queries
  • There are three views on the Query window
  • Design view for QBE
  • SQL view to enter SQL queries
  • Datasheet view to view results
  • Query By Example (QBE) is the Access query
    technique that provides pull-down lists for users
    to specify query conditions
  • Jet SQL are unique SQL commands used by the Jet
    database engine

9
The Basic SQL Syntax
  • The basic SQL expression is in this format
  • SELECT column name
  • FROM table name(s)
  • WHERE conditions
  • Users can enter the SQL syntax directly into the
    SQL view. When using QBE, users can see the SQL
    syntax generated clicking on SQL view.

QBE view
SQL view
10
SELECT all columns
  • When a user want to retrieve all columns in a
    query, he can use the asterisk () as a wildcard
    character.
  • Example
  • SELECT FROM Customer

Datasheet view of the results
11
DISTINCT operator
  • The DISTINCT operator takes the result of the
    data and drops any duplicate data
  • SELECT DISTINCT lastName
  • FROM Customer

Without DISTINCT
DISTINCT
12
WHERE and Operators
  • When specifying conditions, use the keyword
    WHERE
  • WHEREexpressioncomparison operatorexpressio
    n
  • Example
  • SELECT CustomerNumber,FirstName, LastName
  • FROM Customer
  • WHERE CreditLimit
  • 1500 AND 2000

Text values are case sensitive and must be in
single quotes
13
Comparison Operators
14
Logical Operators
15
Sorting Results
  • The keyword ORDER BY organizes the data display
    in an order that is important to the user.
  • The user must specify the sort key (column that
    will be used as a basis for ordering data)
  • General format for sorting results
  • ORDER BY column(s)
  • To sort results in ascending order
  • ORDER BY column(s) ASC
  • To sort results in descending order
  • ORDER BY column(s) DESC

16
Sorting Results
  • Example
  • SELECT FirstName, LastName
  • FROM Customer
  • ORDER BY LastName

Results with ORDER BY
Access default
17
Performing Mathematical Calculations
  • Mathematical operators are used to perform basic
    math calculations
  • General Format
  • data fieldoperatordata field AS
    alias
  • Alias name that serves as the column heading
    when the column is a derived value
  • By default, Access assigns a default name, i.e.
    Expr1001
  • Aliases must be between 1 to 64 characters long
    and can be numbers, characters and some special
    characters

18
Mathematical Operators
  • Example
  • SELECT (CreditLimit) (Balance) AS
    AvailableCredit
  • FROM . . .

19
Aliases
Alias
Default derived column name
20
Order of Precedence
  • SQL evaluates mathematical operators in this
    order of precedence
  • Denoting values as positive or negative
  • Evaluating multiplication or division operations
  • Evaluating addition and subtraction operations
  • To change the default order of precedence, the
    user must place an expression within parentheses
    to force it to be evaluated first

21
Data Types
  • If your answer displays a wrong data type, you
    can manipulate the data type in a specific way
    using the number function
  • Data types
  • Character - for data that is not to be used in
    arithmetic expressions
  • Numeric - for data that can be used in
    arithmetic expressions
  • Time - for data that can be used in time math
  • Numeric Data types
  • INT, INTEGER
  • Only whole numbers
  • (no decimals)
  • DECIMAL, DEC, NUMERIC
  • The range depends on
  • scale or precision specified

22
Aggregate Functions
  • Aggregate functions work with groups of values
    and reduce them to a single values

23
Aggregate Functions
  • Example
  • SELECT CreditLimit, COUNT(CustomerNumber)
  • FROM Customer
  • GROUP BY CreditLimit
  • HAVING COUNT(CustomerNumber) gt 1
  • When your query involves a group function, and
    there are other columns in your SELECT statement
    that do not require grouping, you must include
    the GROUP BY clause
  • HAVING acts like the WHERE keyword, except that
    it works with aggregate functions/ groups

24
Joins (Joining Multiple Tables)
  • When data is retrieved from more than one table
    it is known as a JOIN using foreign key
    references
  • To qualify the column, the column names that
    exist in more than one table must be preceded by
    the table name.

PK
FK
SELECT SalesRep.lastName, Customer.lastName FROM
SalesRep, Customer
Table name followed by period
25
Joins
  • From your WHERE clause, you can specify the table
    and column names on which to join the tables.
    This is know as the join condition
  • Join by specifying FK on one side and PK on
    another
  • SELECT OrderLine.OrderNumber, Orders.CustomerNumbe
    r, Orders.OrderDate, NumberOrderedQuotedPrice
  • FROM Orders
  • WHERE Orders.OrderNumber OrderLine.OrderNumbe
    r
  • ORDER BY OrderLine.OrderNumber DESC

26
Joins
  • A Cartesian product occurs if you accidentally
    omit a join condition in a multiple-table query
    every row in one table is joined with every row
    in the other table
  • SELECT
  • SalesRep.LastName,
  • SalesRep.FirstName,
  • Customer.LastName
  • FROM SalesRep, Customer

27
Comprehensive example
  • SELECT customerNumber, Orders.orderNumber,
    orderDate, SUM(numberOrderedquotedPrice) AS
    OrderTotal
  • FROM Orders, OrderLine
  • WHERE Orders.orderNumber OrderLine.orderNumber
  • GROUP BY Orders.ordernumber, customerNumber,
    orderDate
  • HAVING SUM(numberOrderedquotedPrice) gt 100
  • ORDER BY Orders.orderNumber DESC

28
Aggregate Operators
  • UNION combines all the rows that are either in
    the first table, or second table, or both
  • INTERSECT combines all the rows that are only in
    both tables
  • MINUS/EXCEPT combines a set of rows that are in
    the first table, but are not in the second table

Most DBMS do not support INTERSECT MINUS
Oracle does Access supports UNIONS
29
UNION
  • Union requires that the two tables have the same
    structure (union-compatible).
  • Union compatible is defined as two relations that
    have the same number of columns and the
    corresponding attributes have the same data type
  • General format query 1 UNION query 2
  • Example
  • SELECT CustomerNumber,lastName
  • FROM Customer
  • WHERE slsrepNumber 12
  • UNION
  • SELECT Customer.customerNumber, lastName
  • FROM Customer, Orders
  • WHERE Customer.CustomerNumber
    Orders.CustomerNumber

30
Insert Records
  • When you insert, update and delete data records
    using SQL commands, they are action queries

Action queries change the data that are stored in
the database
31
Insert Records
  • General Formats
  • INSERT INTO table
  • VALUES (column1 value, column2 value
    ...)
  • INSERT INTO table(column1, column2, ...)
  • VALUES (column1 value, column2 value
    ...)

32
Update Records
  • General Formats
  • UPDATE table
  • SET column1 new data value1, column2
    new data value2, ...
  • WHERE search condition

33
Delete Records
  • General Formats
  • DELETE FROM table
  • DELETE FROM table
  • WHERE condition

34
Tutorial links
  • SQL tutorials
  • PL/SQL http//www.hot-oracle.com/showtutorial.htm
    l
  • SQL Interpreter and tutorial
  • http//www.sqlcourse.com/
  • http//sqlcourse2.com/
  • Baycon Group SQL tutorial
  • http//baycongroup.com/tocsql.htm
  • About.com tutorials http//databases.about.com/li
    brary/weekly/aa020401a.htm
About PowerShow.com