SQL: Understanding the Structured Query Language, Slides of Database Management Systems (DBMS)

Download SQL: Understanding the Structured Query Language and more Slides Database Management Systems (DBMS) in PDF only on Docsity!

SQL

Outline

• Background
• Data Definition
• Basic Structure
• Set Operation

Parts of SQL

• Data Definition Language (DDL)
• Data manipulation Language (DML)
• Integrity
• View Definition
• Transaction control
• Embedded SQL and Dynamic SQL
• Authorization

Basic Domain Types

• char(n): A fixed length character string with user specifed length n. character can be used instead.
• varchar(n): A variable length character string with user specified max length n. character varying is equivalent.
• int: An Integer, the full form integer is equivalent.
• smallint: A small integer, a subset of integer domain type
• numeric(p,d): A fixed point number with user specified precision. E.g: numeric(3,1) allows 31.5 to be defined precisely
• real, double precision: Floating-point and double precision floating-point numbers with machine-dependent precision.
• float(n): A floating point number, with precision of at least n digits.

Examples create table

Example 1:

create table customer

(customer_name char ( 20),

customer_street char (30),

customer_city char (30),

primary key (customer_name))

Example 2

create table account

(account_number char (10),

branch_name char (15),

balance numeric (12, 2),

primary key (account_number))

Example select Clause

• select branch_name

from loan

• In relational Algebra, the query would be

branch_name ( loan )

• SQL allows duplicates in query result
  • use distinct if no duplicates in result
  • use all if duplicates required in result
• select distinct branch_name

from loan (result has distinct branch names)

• select all branch_name

from loan (result may have duplicates)

Where Clause

• Corresponds to the selection predicate of relational algebra
• To find loan numbers for loans made at San Jose

branch with loan amounts greater than $ select loan_number from loan where branch_name = “San Jose” and amount > $

• Comparison result can be combined with logical connectives and , or , and not
• SQL includes between comparison operator
• To find loan numbers between amt. 900 and 10, select loan_number from loan where amount between 900 and 10000

Rename

• SQL allows renaming relations and attributes using

as clause

• Example:

To find name, loan_number, amount

of all customers and rename column

loan_number as loan_id.

select customer_name , loan_number as loan_id , amount from borrower , loan where borrower.loan_number = loan. loan_number

Set Operation

• The set operations union, intersect and except

corresponds to U,, - respectively of

relational algebra.

• Each of the above operation automatically

eliminates duplicates

• To retain all duplicates use union all,

intersect all, except all