1 / 48

SQL - PowerPoint PPT Presentation

  • Uploaded on

SQL. Credits: Drs. Liu and Raj, Silberschatz , et al . Structured Query Language (SQL). Developed by IBM (system R) in the 1970s Standards (needed due to multiple vendors) SQL-86 SQL-89 (minor revision) SQL-92, aka SQL2 (major revision) Most DBMS in production support this

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

PowerPoint Slideshow about 'SQL' - midori

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


Credits: Drs. Liu and Raj, Silberschatz, et al.

Structured query language sql
Structured Query Language (SQL)

  • Developed by IBM (system R) in the 1970s

  • Standards (needed due to multiple vendors)

    • SQL-86

    • SQL-89 (minor revision)

    • SQL-92, aka SQL2 (major revision)

    • Most DBMS in production support this

    • SQL:1999, aka SQL3 (major extensions/OO)

    • SQL:2003 (more extensions, some XML)

    • SQL:2006 (better integration with XML)

Check standard supported by your DBMS version

Two facets of sql
Two Facets of SQL

  • Data Manipulation Language (DML)

    • C: insert statement

    • R: select statement

    • U: update statement

    • D: delete statement

  • Data Definition Language (DDL)

    • The schema for each relation.

    • The domain of values associated with each attribute.

    • Integrity constraints

    • The set of indices to be maintained for each relations.

    • Security and authorization information for each relation.

Integrity constraints ics
Integrity Constraints (ICs)

  • Checks that prevent manipulations (CUD) that violate constraints set up by the data expert

    • Guard against accidental damage

    • Prevent loss of consistency due to changes

    • Ensure application semantics

  • Constraints supported via SQL features

    • Domain constraints

    • Referential integrity constraints

    • Assertions

    • Triggers (really a general mechanism that can be used for constraint checking)

Domain aka type constraints
Domain (aka Type) Constraints

  • Most elementary form of integrity constraint

    • Ensures that values stored are type safe

    • Prevents bad data from being kept in database

    • Ensure only valid queries are used

    • Avoids nonsensical comparisons

      • My apple is bigger than your orange

Referential integrity
Referential Integrity

  • If a table entry refers to another table, guarantee that the reference exists (no dangling pointers)

    • If Fido is a dog name mentioned in doghouse, then a row for Fido exists in dog

    • If passenger Smith is booked on Flight 100, then 100 is a flight mentioned in the flight table

  • Referential integrity in SQL

    • Specified by keys as part of create table statement

      • Primary keys, unique keys and foreign keys

      • Keys need not be single attributes, could be a set of attributes

Common domain types in sql
Common Domain Types in SQL

  • Strings

    • char(n), varchar(n) (see more on next slide)

  • Numbers

    • int, smallint, numeric(p,d), real, double precision, float(n)

  • Date and time

    • date, time, datetime

  • User-defined domains

    • create domain SSNtype char(11) not null

  • Null values allowed for all domains

    • May be overridden by NOT NULL

Other data types
Other Data Types

  • date: Dates, containing a (4 digit) year, month and date

    • Example: date‘2005-7-27’

  • time: Time of day, in hours, minutes and seconds.

    • Example: time‘09:00:30’ time‘09:00:30.75’

  • timestamp: date plus time of day

    • Example: timestamp‘2005-7-27 09:00:30.75’

  • interval: period of time

    • Example: interval ‘1’ day

    • Subtracting a date/time/timestamp value from another gives an interval value

    • Interval values can be added to date/time/timestamp values

User defined types
User Defined Types

  • create typeconstruct in SQL creates user-defined type

    create type Dollars as numeric (12,2) final

    • create table department(dept_namevarchar(20),building varchar(15),budget Dollars);

B lobs and clobs
blobs and clobs

  • Large objects (photos, videos, CAD files, etc.) are stored as a large object:

    • blob: binary large object -- object is a large collection of uninterpreted binary data (whose interpretation is left to an application outside of the database system)

    • clob: character large object -- object is a large collection of character data

    • When a query returns a large object, a pointer is returned rather than the large object itself.

C har n and varchar n
char(n) and varchar(n)

  • Either used to define a character type

  • Use char(n) for a fixed-length string

  • Use varchar(n) for variable-length string

  • Note that storing “dog” in a char(6) string actually stores “dogXXX” where X is a space. In other words, it pads all strings to make the consistent length. varchar(n) does not pad the string.

  • Why did I mention this?

    • Recommendation is to use varchar

Create table construct
Create Table Construct

  • An SQL relation is defined using thecreate tablecommand:

    create table r(A1D1, A2D2, ..., AnDn,(integrity-constraint1), ..., (integrity-constraintk))

    • r is the name of the relation

    • each Ai is an attribute name in the schema of relation r

    • Di is the data type of values in the domain of attribute Ai

  • Example:

    create table branch (branch_namevarchar(15) not null,branch_cityvarchar(30),assets integer);

Primary keys
Primary Keys

  • Primary keys are denoted in relations by listing them first and underlining them:

    • STUDENT (studentID, name, major)

    • PRIMARY KEY (studentID)

  • For PKs made up of several attributes (composite keys), all of the attributes that are part of the key are underlined:

    • COURSE (departmentID, courseNumber, courseDescription)

    • PRIMARY KEY(departmentID, courseNumber)

  • A PK constraint means that the attribute must be:

    • Unique

    • Not Null

Create table construct key
Create Table Construct & Key

  • Indicate the primary key in the create table command:

    create table branch (branch_namevarchar(15) not null,branch_cityvarchar(30),assets integer),

    primary key (branch_name));

  • Remember that the primary key must be guaranteed to be non-null and unique

Foreign keys
Foreign Keys

  • Foreign keys are added to a relation to show the relationship between two (or more) entities.

  • Example:

    • Department (DeptID,DeptName, Location);

    • Employee (EmpID, EmpName, DeptID);

    • FOREIGN KEY (DeptID) REFERENCES Department(DeptID)

      • or FOREIGN KEY (DeptID) REFERENCES Department

Table name

Basic query structure
Basic Query Structure

  • SQL is based on set and relational operations with certain modifications and enhancements

  • A typical SQL query has the form:select A1, A2, ..., Anfromr1, r2, ..., rmwhere P

    • Ai represents an attribute

    • Rirepresents a relation

    • P is a predicate.

  • The result of an SQL query is a relation.

The select clause
The select Clause

  • The select clause list the attributes desired in the result of a query

    • corresponds to the projection operation of the relational algebra

  • Example: find the names of all branches in the loan relation:select branch_namefrom loan

  • NOTE: SQL names are case insensitive (i.e., you may use upper- or lower-case letters.)

    • E.g. Branch_Name ≡ BRANCH_NAME ≡ branch_name

The select clause cont
The select Clause (Cont.)

  • An asterisk in the select clause denotes “all attributes”

    select *from loan

  • The select clause can contain arithmetic expressions involving the operation, +, –, , and /, and operating on constants or attributes of tuples.

  • The query:

    selectloan_number, branch_name, amount  100from loan

    would return a relation that is the same as the loan relation, except that the value of the attribute amount is multiplied by 100.

Cartesian product
Cartesian Product

  • For each tuple in each relation, concatenate into a single tuple

Cartesian product1
Cartesian Product

  • RRes

Result is a relation consisting of each instructor concatenated with each of the “teaches”

Instances on that relation

Note that this example is provided in the text on page 69 – and the answer provided

Is wrong!

The from clause
The from Clause

  • The fromclause lists the relations involved in the query

    • Corresponds to the Cartesian product operation of the relational algebra.

  • Find the Cartesian product borrower X loan

    select from borrower, loan

  • The Cartesian product is generated if > 1 relation


  • Refer to Figure 2.1 from your text to answer the response to the queries (instructor relation)

Sample query
Sample Query

  • select name from instructor;

Sample query1
Sample Query

  • select dept_name from instructor; (notice the duplicates)

Sample query2
Sample Query

  • Use select all dept_name from instructor; to explicitly indicate that duplicates should not be removed

Sample query3
Sample Query

  • select distinct dept_name from instructor;

  • Use of distinct to eliminate duplicates

  • Duplicateretention is the default

Sample query4
Sample Query

  • What will this query return?

  • select ID, name from instructor;

Sample query5
Sample Query

  • What will this query return?

  • select ID, name from instructor;

  • Returns a relation containing all instructor IDs and names

Sample query6
Sample Query

  • What will this query return?

  • select ID, name, salary * 1.1 from instructor;

Sample query7
Sample Query

  • What will this query return?

  • select ID, name, salary * 1.1 from instructor;

  • Returns a relation containing all instructor IDs, names, and salaries incremented by 10%

  • Note that it returns a separate relation; the original data in the table is unchanged

S elect from where

  • To reference a specific attribute, use the dot notation

  • Find the name, loan number and loan amount of all customers having a loan at the Perryridge branch.

  • select customer_name, borrower.loan_number, amountfrom borrower, loanwhere borrower.loan_number = loan.loan_numberand branch_name = 'Perryridge’;

The where clause
The where Clause

  • The whereclause specifies conditions that the result must satisfy

    • Corresponds to the selection predicate of the relational algebra.

  • To find all loan number for loans made at the Perryridge branch with loan amounts greater than $1200.

    select loan_numberfrom loanwhere branch_name ='Perryridge'and amount > 1200

  • Comparison results can be combined using the logical connectives and, or, and not.

The where clause cont
The where Clause (Cont.)

  • SQL includes a between comparison operator

  • Example: Find the loan number of those loans with loan amounts between $90,000 and $100,000 (that is,  $90,000 and  $100,000)

selectloan_numberfrom loanwhere amountbetween 90000 and 100000

Check your implementation of SQL. “between X and Y” may include X and or Y, or exclude both

Sample query8
Sample Query

  • Back to our instructor relation: What will this query return?

  • Select name from instructor where dept_name = ‘Comp Sci’ and salary > 70000;

Sample query9
Sample Query

  • Back to our instructor relation: What will this query return?

  • Select name from instructor where dept_name = ‘CompSci’ and salary > 70000;

  • Trick question: Returns nothing because the dept_name doesn’t match.

Sample query10
Sample Query

  • Back to our instructor relation: What will this query return?

  • Select name from instructor where dept_name = ‘Comp. Sci.’ and salary > 70000;

  • Returns a relation containing Katz and Brandt

The rename operation
The Rename Operation

  • The SQL allows renaming relations and attributes using the as clause:

    old-name as new-name

  • Find the name, loan number and loan amount of all customers; rename the column name loan_number as loan_id.

select customer_name, borrower. loan_numberas loan_id, amountfrom borrower, loanwhere borrower.loan_number = loan.loan_number

String operations
String Operations

  • SQL standard is that strings are case sensitive

  • Some database systems do not distinguish case – you need to know the specifics for the database you are using

  • SQL supports functions for string concatenation, substrings, length, converting to lower/upper case, etc

  • Different operations supported on various databases

String operations1
String Operations

  • Pattern Matching

  • % matches any substring

  • _ (underscore) matches any character

  • Patterns are case sensitive


  • The operator “like” uses patterns that are described using two special characters:

    • percent (%). The % character matches any substring.

    • underscore (_). The _ character matches any character.

  • Find the names of all customers whose street includes the substring “Main”.

    select customer_namefrom customerwherecustomer_streetlike '% Main%’

    See the book if you want to use a % or _ in the actual pattern

Other string operations
Other String Operations

  • SQL supports a variety of string operations such as

    • concatenation (using “||”)

    • converting from upper to lower case (and vice versa)

    • finding string length, extracting substrings, etc.

Tuple variables
Tuple Variables

  • Tuple variables are defined in the from clause via the use of the as clause.

  • Find the customer names and their loan numbers for all customers having a loan at some branch.

select customer_name, T.loan_number, S.amountfrom borrower as T, loan as Swhere T.loan_number = S.loan_number

Tuple variables1
Tuple Variables

Find the names of all branches that have greater assets than some branch located in Brooklyn.

select distinct T.branch_namefrom branch as T, branch as S  rename the same relation to do 1-1 comparewhere T.assets > S.assetsand S.branch_city = 'Brooklyn‘

Ordering the display of tuples
Ordering the Display of Tuples

  • List in alphabetic order the names of all customers having a loan in Perryridge branch

    select distinct customer_namefrom borrower, loanwhere borrower.loan_number = loan.loan_number and branch_name = 'Perryridge' order by customer_name

  • We may specify desc for descending order or asc for ascending order, for each attribute; ascending order is the default.

    • Example: order bycustomer_namedesc

Modification of the database deletion
Modification of the Database – Deletion

  • Delete all account tuples at the Perryridge branch

    delete from accountwhere branch_name = 'Perryridge'

  • Delete all accounts at every branch located in the city ‘Needham’.

    delete from accountwhere branch_name in (select branch_namefrom branchwhere branch_city = 'Needham')

Modification of the database insertion
Modification of the Database – Insertion

  • Add a new tuple to account

    insert into accountvalues ('A-9732', 'Perryridge', 1200)

    or equivalentlyinsert into account (branch_name, balance, account_number)values ('Perryridge', 1200, 'A-9732')

  • Add a new tuple to account with balance set to null

    insert into accountvalues ('A-777','Perryridge', null )

Modification of the database updates
Modification of the Database – Updates

  • Increase all accounts with balances over $10,000 by 6%

    update accountset balance = balance  1.06where balance > 10000

Case statement for conditional updates
Case Statement for Conditional Updates

  • Same query as before: Increase all accounts with balances over $10,000 by 6%, all other accounts receive 5%.

    updateaccountsetbalance = casewhenbalance <= 10000 thenbalance *1.05elsebalance * 1.06end