introduction to structured query language sql n.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
Introduction to Structured Query Language (SQL) PowerPoint Presentation
Download Presentation
Introduction to Structured Query Language (SQL)

Loading in 2 Seconds...

play fullscreen
1 / 43

Introduction to Structured Query Language (SQL) - PowerPoint PPT Presentation


  • 60 Views
  • Uploaded on

Introduction to Structured Query Language (SQL). RA Exercise. SALESPERSON( SSN , Name, Start_Year, Dept_No) TRIP(SSN, From_City, Departure_Date, Return_Date, Trip_ID ) EXPENSE( Trip_ID, Account# , Amount)

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'Introduction to Structured Query Language (SQL)' - twila


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
ra exercise
RA Exercise
  • SALESPERSON(SSN, Name, Start_Year, Dept_No)
  • TRIP(SSN, From_City, Departure_Date, Return_Date, Trip_ID)
  • EXPENSE(Trip_ID, Account#, Amount)
    • Give the details (all attributes of TRIP relation) for trips that exceeded $2000 in expense
    • Print the SSN of salesman who took trips to Honolulu
    • Search the Name & Dept_No of salesman who took trips from ‘Jakarta’ and expensed no more than $1000
join example

BarInfo( bar beer price addr )

Joe’s Bud 2.50 Maple St.

Joe’s Milller 2.75 Maple St.

Sue’s Bud 2.50 River Rd.

Sue’s Coors 3.00 River Rd.

Join Example

Sells( bar beer price ) Bars( bar addr )

Joe’s Bud 2.50 Joe’s Maple St.

Joe’s Miller 2.75 Sue’s River Rd.

Sue’s Bud 2.50

Sue’s Coors 3.00

BarInfo := Sells JOIN Bars

why sql
Why SQL?
  • SQL is a very-high-level language, in which the programmer is able to avoid specifying a lot of data-manipulation details that would be necessary in languages like C++.
  • What makes SQL viable is that its queries are “optimized” quite well, yielding efficient query executions.
sql components
SQL Components
  • DDL – Data definition language
    • Defining the relational schema – relations, attributes, domains (the meta-data)
    • Dropping/altering the relational schema
  • DML – Data manipulation language
    • Defining the queries against the schema
    • Inserting, deleting and modifying data
  • Others
    • Integrity (allowable values/referential)
    • Catalog and dictionary facilities
    • Transaction control (long-duration and batch)
    • Authorization (who can do what when)
our running example
Our Running Example
  • All our SQL queries will be based on the following database schema.
    • Underline indicates key attributes.

SCHEMA BeerWorld

Beers(name, manf)

Bars(name, addr, license)

Drinkers(name, addr, phone)

Likes(drinker, beer)

Sells(bar, beer, price)

Frequents(drinker, bar)

data definition language ddl
Data Definition Language - DDL
  • A Pre-Defined set of Primitive Types
    • Numeric
    • Character-string
    • Bit-string
    • Additional Types
  • Defining Domains
  • Defining Schema
  • Defining Tables
  • Defining Views
  • Note: Each DBMS May have their Own DBMS Specific Data Types
primitive types
Primitive Types
  • Numeric
    • INTEGER (or INT), SMALLINT
    • REAL, DOUBLE PRECISION
    • FLOAT(N) Floating Point with at Least N Digits
    • DECIMAL(P,D) (DEC(P,D) or NUMERIC(P,D)) have P Total Digits with D to Right of Decimal
  • Note that INTs and REALs are Machine Dependent (Based on Hardware/OS Platform)
primitive types1
Primitive Types
  • Character-String
    • CHAR(N) or CHARACTER(N) - Fixed
    • VARCHAR(N), CHAR VARYING(N), or CHARACTER VARYING(N) Variable with at Most N Characters
  • Bit-Strings
    • BIT(N) Fixed
    • VARBIT(N) or BIT VARYING(N) Variable with at Most N Bits
primitive types2
Primitive Types
  • These Specialized Primitive Types are Used to:
    • Simplify Modeling Process
    • Include “Popular” Types
    • Reduce Composite Attributes/Programming
  • DATE : YYYY-MM-DD
  • TIME: HH-MM-SS
what are domains
What are Domains?
  • Domains are Similar in Concepts to Programming Language Type Definitions
  • A Domain can be Defined as Follows:

CREATE DOMAIN DrinkerName CHAR(25) DEFAULT ‘Joe’;

CREATE DOMAIN BeerName CHAR(20);

  • Advantage of Using Domains
    • Changing a Domain Definition in One Place Changes it Consistently Everywhere it is Used
    • Default Values Can Be Defined for Domains
    • Constraints Can Be Defined for Domains
dropping a domain
Dropping a Domain
  • A Domain is Dropped As Follows:

DROP DOMAIN DrinkerName RESTRICT;

DROP DOMAIN BeerName CASCADE;

  • Restrict:
    • Drop Operation Fails If the Domain is Used in Column Definitions
  • Cascade:
    • Drop Operation Causes Columns to be Defined Directly on the Underlying Data Type
what is a sql schema
What is a SQL Schema?
  • A Schema in SQL is the Major Meta-Data Construct
  • Supports the Definition of:
    • Relation - Table with Name
    • Attributes - Columns and their Types
    • Identification - Primary Key
    • Constraints - Referential Integrity (FK)
  • Two Part Definition
    • CREATE Schema - Named Database or Conceptually Related Tables
    • CREATE Table - Individual Tables of the Schema
create drop schema
Create/Drop Schema
  • For schema BeerWorld

SQL:

CREATE SCHEMA BeerWorld Authorization Indra;

  • Drop schema BeerWorld

SQL:

DROP SCHEMA BeerWorld RESTRICT;

DROP SCHEMA BeerWorld CASCADE;

    • Restrict:
      • Drop Operation Fails If Schema is Not Empty
    • Cascade:
      • Drop Operation Removes Everything in the Schema
create table
Create Table
  • Create table Drinkers

Drinkers(name, addr, phone)

CREATE TABLE Drinkers(

name VARCHAR(25),

addr VARCHAR(40),

phone CHAR(10),

PRIMARY KEY(name)

);

create table1
Create Table
  • Default value: gives the default value for a column/attribute

Example: name VARCHAR(25) DEFAULT ‘Joe’

  • Constraint: gives the constraint to the column/attribute. It can be used for:
    • Specifying the primary key/foreign key
    • Specifying column that can’t have the null value
    • Preserving the integrity of the database
      • Deleting the record
      • Modifying the value of

primary key

SET DEFAULT, SET NULL, CASCADE

create table2
Create Table

Constraint name

  • Create table Drinkers(name, addr, phone)

CREATE TABLE Drinkers(

name VARCHAR(25) NOT NULL,

…,

CONSTRAINT pkDrinkers PRIMARY KEY(name)

);

  • Create table Sells(bar, beer, price)

CREATE TABLE Sells(

bar VARCHAR(25) NOT NULL,

beer VARCHAR(25) NOT NULL,

price DECIMAL(10,2) NOT NULL,

CONSTRAINT pkBar FOREIGN KEY(bar) REFERENCES Bars(name) ON UPDATE CASCADE,

CONSTRAINT pkBeer FOREIGN KEY(beer) REFERENCES Beers(name) ON UPDATE CASCADE

);

drop tables
Drop Tables
  • Command:

DROP TABLE Drinkers RESTRICT;

DROP TABLE Sells CASCADE;

  • Restrict:
    • Drop Operation fails if the Table is Referenced by View and/or Constraint Definitions
  • Cascade:
    • Drop Operation Removes Referencing View and Constraint Definitions
alter table
Alter Table
  • Change the table structures:
    • Adding or dropping column
    • Changing column definition
    • Adding or dropping table constraint
  • Adding column “job” in table “Drinkers”:

ALTER TABLE Drinkers ADD job VARCHAR(20);

  • Dropping column “job” in table “Drinkers”:

ALTER TABLE Drinkers DROP job CASCADE;

ALTER TABLE Drinkers DROP job RESTRICT;

    • Restrict: Drop Operation Fails if Column is Referenced
    • Cascade: Drop Operation Removes Referencing View and Constraint Definitions
basic query in sql1
Basic Query in SQL
  • The principal form of a query is:

SELECT desired attributes

FROM one or more tables

WHERE condition about tuples of the tables

GROUP BY attribute list

HAVING condition

ORDER BY attribute list

Basic Query

example
Example
  • Using Beers(name, manf), what beers are made by Anheuser-Busch?

SELECT name

FROM Beers

WHERE manf = ‘Anheuser-Busch’;

result of query
Result of Query

name

‘Bud’

‘Bud Lite’

‘Michelob’

The answer is a relation with a single attribute,

name, and tuples with the name of each beer

by Anheuser-Busch, such as Bud.

meaning of single relation query
Meaning of Single-Relation Query
  • Begin with the relation in the FROM clause.
  • Apply the selection indicated by the WHERE clause.
  • Apply the extended projection indicated by the SELECT clause.
operational semantics
Operational Semantics
  • To implement this algorithm think of a tuple variable ranging over each tuple of the relation mentioned in FROM.
  • Check if the “current” tuple satisfies the WHERE clause.
  • If so, compute the attributes or expressions of the SELECT clause using the components of this tuple.
in select clauses
* In SELECT clauses
  • When there is one relation in the FROM clause, * in the SELECT clause stands for “all attributes of this relation.”
  • Example using Beers(name, manf):

SELECT *

FROM Beers

WHERE manf = ‘Anheuser-Busch’;

result of query1
Result of Query:

name manf

‘Bud’ ‘Anheuser-Busch’

‘Bud Lite’ ‘Anheuser-Busch’

‘Michelob’ ‘Anheuser-Busch’

Now, the result has each of the attributes

of Beers.

renaming attributes
Renaming Attributes
  • If you want the result to have different attribute names, use “AS <new name>” to rename an attribute.
  • Example based on Beers(name, manf):

SELECT name AS beer, manf

FROM Beers

WHERE manf = ‘Anheuser-Busch’

result of query2
Result of Query:

beer manf

‘Bud’ ‘Anheuser-Busch’

‘Bud Lite’ ‘Anheuser-Busch’

‘Michelob’ ‘Anheuser-Busch’

expressions in select clauses
Expressions in SELECT Clauses
  • Any expression that makes sense can appear as an element of a SELECT clause.
  • Example: from Sells(bar, beer, price):

SELECT bar, beer,

price*8000 AS prcInRupiah

FROM Sells;

result of query3
Result of Query

bar beer prcInRupiah

Joe’s Bud 40000

Sue’s Miller 56000

… … …

another example constant expressions
Another Example: Constant Expressions
  • From Likes(drinker, beer):

SELECT drinker,

‘likes Bud’ AS whoLikesBud

FROM Likes

WHERE beer = ‘Bud’;

result of query4
Result of Query

drinker whoLikesBud

‘Sally’ ‘likes Bud’

‘Fred’ ‘likes Bud’

… …

complex conditions in where clause
Complex Conditions in WHERE Clause
  • From Sells(bar, beer, price), find the price Joe’s Bar charges for Bud:

SELECT price

FROM Sells

WHERE bar = ‘Joe’’s Bar’ AND

beer = ‘Bud’;

important points
Important Points
  • Two single quotes inside a string represent the single-quote (apostrophe).
  • Conditions in the WHERE clause can use AND, OR, NOT, and parentheses in the usual way boolean conditions are built.
  • SQL is case-insensitive. In general, upper and lower case characters are the same, except inside quoted strings.
patterns
Patterns
  • WHERE clauses can have conditions in which a string is compared with a pattern, to see if it matches.
  • General form: <Attribute> LIKE <pattern> or <Attribute> NOT LIKE <pattern>
  • Pattern is a quoted string with % = “any string”; _ = “any character.”
example1
Example
  • From Drinkers(name, addr, phone) find the drinkers with exchange 555:

SELECT name

FROM Drinkers

WHERE phone LIKE ‘%555-_ _ _ _’;

null values
NULL Values
  • Tuples in SQL relations can have NULL as a value for one or more components.
  • Meaning depends on context. Two common cases:
    • Missing value : e.g., we know Joe’s Bar has some address, but we don’t know what it is.
    • Inapplicable : e.g., the value of attribute spouse for an unmarried person.
comparing null s to values
Comparing NULL’s to Values
  • The logic of conditions in SQL is really 3-valued logic: TRUE, FALSE, UNKNOWN.
  • When any value is compared with NULL, the truth value is UNKNOWN.
  • But a query only produces a tuple in the answer if its truth value for the WHERE clause is TRUE (not FALSE or UNKNOWN).
surprising example

UNKNOWN UNKNOWN

UNKNOWN

Surprising Example
  • From the following Sells relation:

bar beer price

Joe’s Bar Bud NULL

SELECT bar

FROM Sells

WHERE price < 2.00 OR price >= 2.00;

exercise
Exercise

Select name

From Drinkers

  • List the name of all drinkers
  • List the name of beers started by ‘Bin’

Select name

From Beers

Where name Like ‘Bin%’

  • Is the syntax of this query correct or not?

Select 1 as tag, drinker

From Likes

Where beer <> ‘Anheuser-Busch’

exercise1
Exercise
  • Find the name of bars selling ‘Anheuser-Busch’ with price less than $5

Select bar

From Sells

Where beer = ‘Anheuser-Busch’ and price < 5

  • Find the name of bars started with ‘Zanz’ selling ‘Bir Bintang’

Select bar

From Sells

Where bar like ‘Zanz%’ and beer = ‘Bir Bintang’