Structured Query Language - PowerPoint PPT Presentation

1 / 32
About This Presentation
Title:

Structured Query Language

Description:

To obtain only unique records DISTINCT clause is employed. Marina G. Erechtchoukova ... Find the year of birth of students who have second character 'o' in their names ... – PowerPoint PPT presentation

Number of Views:41
Avg rating:3.0/5.0
Slides: 33
Provided by: Mari630
Category:

less

Transcript and Presenter's Notes

Title: Structured Query Language


1
Structured Query Language
2
Database Language Requirements
  • Allows to create database schema
  • Performs basic manipulation tasks
  • Performs simple and complex queries
  • Command structure and syntax are relatively easy
    to learn
  • Portability allowing to use the same language on
    different DBMSs

3
SQL
  • Non-procedural language
  • Interactive
  • Embedded
  • Free-format
  • Standard English words

4
SQL Aspects
  • DDL
  • DML
  • Embedded SQL
  • Dynamic SQL
  • Triggers and active database
  • Transaction management

5
SQL Command
  • Reserved words
  • Fixed meaning
  • Cannot be split across lines
  • User-defined words
  • Character literals are case sensitive

6
Create Database
  • CREATE SCHEMA Database_name
  • AUTHORIZAION Owner_name
  • DROP SCHEMA Database_name
  • RESTRICT/CASCADE

7
Create Table
  • CREATE TABLE Table_name (Field_1_name Data-type
    Constraints, , Field_n_name Data-type
    Constraints,
  • PRIMARY KEY (list of fields),
  • FOREIGN KEY (list_1 of fields) REFERENCES
    table_1_name ON DELETE action, ON UPDATE
    action,,
  • FOREIGN KEY (list_m of fields) REFERENCES
    table_m_name ON DELETE action, ON UPDATE
    action)
  • ON UPDATE is not supported by our DBMS

8
Domain Constraint
  • Data types
  • CHECK clause
  • CREATE TABLE Employee (EID CHAR(9),
  • NAME CHAR(20), Title CHAR(4) CHECK(Title IN
    (Mr., Mrs., Ms.)),)
  • CREATE DOMAIN is not supported by our DBMS

9
Modify or Remove Tables
  • ALTER TABLE Table_name
  • ADD COl_name Constraints
  • DROP COLUMN COl_name
  • MODIFY Col_name Constraints
  • ADD CONSTRAINT Const_name Const_def
  • DROP CONSTRAINT Const_name
  • DISABLE CONSTRAINT Const_name
  • ENABLE CONSTRAINT Const_name
  • DROP TABLE Table_name

10
Insert Data into Tables
  • INSERT INTO Table_name (list_of_fields)
    VALUES (list_of_values)
  • INSERT INTO Table_name
  • SELECT statement

11
Modify or Delete Data
  • UPDATE Table_name
  • SET Field_name1 value1,
  • Field_nameN valueN
  • WHERE qualification
  • DELETE FROM Table_name
  • WHERE qualification

12
Data Manipulation (Examples)
  • INSERT INTO Book (bid, title, author,
  • price, av_q) VALUES (b11, Franklin
    forgets, P. Bourgeois, 7.00, 5)
  • UPDATE Book B
  • SET B.price B.price0.9, B.av_qav_q 50
  • WHERE B.bid b11
  • DELETE FROM Book B
  • WHERE B.bid b22

13
Basic SQL Query
  • SELECT DISTINCT select-list
  • FROM from-list
  • WHERE qualification
  • from-list a list of tables and aliases (range
    variables)
  • select-list a list of fields

14
SELECT vs. Relational Algebra
  • SELECT select-list ? ?select-list
  • FROM from-list ? Cross Product
  • WHERE qualification ? ?qualification

15
Qualification
  • Logical expression with AND, OR, NOT and
    comparison operations
  • Rules for expression evaluation
  • An expression is evaluated from left to right
  • Subexpressions in brackets are evaluated first
  • NOTs are evaluated before ANDs and ORs
  • ANDs are evaluated before ORs

16
Basic Search Conditions
  • Comparison
  • Range (tests if a value falls into specified
    range)
  • BETWEEN AND
  • Set membership (tests if a value belongs to a
    specified set)
  • IN ()

17
Basic Search Conditions (cont)
  • Pattern match (tests whether a string matches a
    specified pattern)
  • LIKE
  • Null (tests whether a field contains Null value)
  • IS NULL
  • IS NOT NULL

18
Null Values
  • Comparison
  • NULL and fixed value returns NULL
  • NULL and NULL is unknown
  • IS NULL and IS NOT NULL functions
  • Arithmetic operation
  • Returns NULL if at least one of the operands is
    NULL

19
Logical Operators with Null Values
X Y X OR Y
T T T
T F T
F T T
F F F
N T T
T N T
F N N
N F N
N N N
X Y X AND Y
T T T
T F F
F T F
F F F
N T N
T N N
F N F
N F F
N N N
20
Multisets
  • Unordered collection of elements, which may
    contain several copies of the same element
  • The result of SELECT command is a multiset
  • To obtain only unique records DISTINCT clause is
    employed

21
Conceptual Evaluation Strategy for SELECT
statement
  • Compute cross-product of tables in from-list
  • Delete those rows which fail the qualification
    condition
  • Delete all columns that do not appear in the
    select-list
  • If DISTINCT clause is specified, eliminate
    duplicate rows.

22
ANSI/ISO SQL99 Join Syntax
  • Theta joins
  • SELECT DISTINCT select-list FROM Table_name1
    INNER JOIN Table_name2 ON join-condition
  • WHERE qualification
  • Natural joins
  • SELECT DISTINCT select-list FROM Table_name1
    NATURAL JOIN Table_name2
  • WHERE qualification

23
Examples of Queries
  1. Find all students who were born in or before
    1980.
  2. Find the IDs of students who have been registered
    in ITEC program
  3. Find the names and IDs of students who have been
    registered in ITEC program
  4. Find the program which student Brown is
    registered in.
  5. Find the names and IDs of students who had been
    registered for at least 1 course

24
Expressions
  • Contain arithmetic or string operations,
    constants and aggregate functions
  • In select-list may be followed by
  • AS new_name clause
  • In qualification
  • String operations are based on the same
    comparison operators
  • A Collation concept

25
Examples of Queries
  1. Find student names and compute students marks
    for IT1011 by increasing them on 2 points
  2. Find student pairs where the first student is at
    least 5 years younger than the second

26
Operator LIKE
  • is used mainly along with two pattern-matching
    symbols
  • - represents any sequence of zero or more
    characters
  • _ (underscore, underline) represents any single
    character.
  • Bobby LIKE Bob is False
  • Bobby LIKE Bob is True

27
Operator LIKE (examples)
  • LIKE H
  • LIKE H_ _ _
  • LIKE e
  • LIKE Moscow
  • NOT LIKE H
  • Find the year of birth of students who have
    second character o in their names

28
Combining Result Tables
  • Union-compatible tables
  • UNION
  • Duplicate rows are always eliminated
  • Clause ALL
  • INTERSECT
  • MINUS
  • CORRESPONDING BY field1,fieldk

29
Outer Jions
  • Left outer join
  • SELECT from Student S, Transcript T WHERE
  • S.sid T.sid ()
  • Right outer join
  • SELECT from Transcript T, Course C WHERE
  • T.cid () C.cid

30
New Syntax for Outer Joins
  • SELECT DISTINCT select-list FROM Table_name1
    LEFTRIGHTFULL OUTER JOIN Table_name2 ON
    join-condition
  • WHERE qualification

31
Query Examples
  1. Find the IDs and names of students who have been
    registered for ITEC or COSC program
  2. Find the IDs and names of students who have been
    registered in both ITEC and COSC courses
  3. Find the sid of students who have been registered
    for ITEC courses, but not COSC courses

32
Query Examples
  • Find IDs of students who are younger than 24 and
    are enrolled in COSC courses
  • Find the IDs and names of students who have been
    enrolled in at least two courses
Write a Comment
User Comments (0)
About PowerShow.com