Lesson05 relational languages sql

6 September 2019 Sikandar 1
Lesson 05
Relational Languages

 Commercial DB systems require more “User Friendly”
languages.
 Query languages (User makes requests to the DB to
obtain info.
 These languages should preserve fundamental
constructs and concepts
 Ones we saw in Relational Algebra.
 Examples
 SQL (Structured Query Language)
 QBE (Query By Example)

 SQL
 MS Access SQL version
 Oracle SQL version
 DB2 SQL version
 Etc
 Very Minor Differences between all the
implementations.

SQL
 SQL consists of 2 key Parts
 Data Definition Language (DDL)
 The commands for defining , deleting, and
modifying relations schemas or structure , we can
change the structure of the table insert columns
etc.
 Data Manipulation Language (DML)
 This is further divided into two parts
 Commands to retrieve information called (Query
Part)
 The commands to insert , delete, and update tuples
called (Modification Part)

SQL
DDL used to create
the table P# PNAME COLOR WEIGHT CITY
P1 Nut Red 12 London
P2 Bolt Green 17 Paris
P3 Screw Blue 17 Rome
P4 Screw Red 14 London
P5 Cam Blue 12 Paris
P6 Cog Red 19 London
DML used to fill the
table with tuples etc
 The languages Used to extract info or retrieve info are
called “Querying Languages”

DML
 Query Part (of DML)
 Basic Structure
(3 clauses)
 Select
 From
 Where
 Select:
 Correspond to the project operation of relational
algebra.
 Lists the attributes desired in the result of the query.

DML
 From:
 Corresponds to the Cartesian product operation in
relational algebra
 Lists the relations to be scanned in the evaluation
process
 Where:
 Corresponds to the selection conditions
 Involves attributes of the relations in the from clause.
 Typical Format:
select A1, A2, A3……..An (attribute names)
From r1,r2,r3………… rn (Relation names)
Where P (conditions)

DML
 Typical Format:
select A1, A2, A3……..An (attribute names)
From r1,r2,r3………… rn (Relation names)
Where P (conditions)
 Similar expression can be written in relational
algebra
 ( ( r1,r2,r3………… rn ) where P ) [A1, A2,
A3……..An]
 If where is omitted the selection condition is true
for all tuples, so we only use 2 clauses
Select s#, city
From suppliers;

DML examples
 If we want the whole table
Select *
From suppliers
 All of the info suppliers located in London
Select *
From suppliers
Where city = ‘London’

Operators for where clause
 Operators for where clause:
 Logical operators
 AND, OR, NOT etc.
 Comparison operators
 < , > , <> , >= , <= etc
 Arithmetic operators
 + , - , / , *
 Between (or NOT between)
 A type of comparison operator.

Operators for where clause
example 1
Select s#
From supplier
Where (status>= 0) AND (status <= 30);
example 2
select s#
From supplier
where (status between 10 and 30)
example 3
select s#
From supplier
where (status not between 10 and 30)

String matching operators
 Oracle gives 2 special characters (wild characters)
 Percent(%) matches any sub string
 Under score (_) matches any character
 For Access
 * matches any sub string
 ? matches any character

String matching operators examples
 “ABC%”
 Matches any string beginning with ABC.
 “%ABC%”
 Matches any string containing ABC as a sub string.
 ‘_ _ _’
 Matches any string with exactly 3 characters.
 ‘_ _ %”
 Matches any string matching at least 2 character
long.

String matching operators
 Patterns are expressed using “LIKE” comparison
operator.
 Example
 Select SNAME
 From supplier
 Where city LIKE ‘%York%’ ;
 Example
 Select city
 From supplier
 Where SNAME LIKE ‘T%’ ;
To Use wild cards
we must use LIKE
operator

Join
 No direct representation of natural join in SQL.
 SQL use Theta join.
 Example
 A list of parts number supplied by TOM
Select P#
From suppliers, shipment
Where (supplier.S# = shipment.S#)
 System creates a Cartesian product of the 2 tables.

Handling Duplicate Tuples
 It is very tough to check duplicate tuples by default.
 there can be duplicate tuples due to the result of a
query.
 SQL allows duplicates by default
 Duplicate elimination is very costly.
 Also we might require an idea or number of tuples
recovered due to some query.
 To force elimination we use DISTINCT after select
clause.

Distinct
 Example
 A list of cities for suppliers.
select city
from suppliers; //we get a lot of duplicate cities
select distinct city
from suppliers; // all duplicates are eliminated.

Set Operators (  ,  , )
 UNION:
 Eliminates duplicates by default, if we want to keep
duplicates , we use : UNION ALL.
 Example
( Select SNAME
From supplier
Where (status > 30) )
UNION
( Select SNAME
From supplier
Where city = ‘London’)

UNION
 Instead of the previous code we can also use
Select SNAME
From supplier
Where (status > 30 ) OR (city = ‘London’)
 If table names are different then we use UNION
 if we want to keep duplicates we use UNION ALL.

Intersection and Minus
 Both intersection and Minus are NOT included in ANSI
standards , majority of SQL vendors do not support
these two.
 Some times even UNION is not supported

Additional Operators
 IN and NOT IN
 Operator tests for set membership where the set is a
collection of values produced by the select clause in a
sub expression (“sub select”).
 E.g
 Get S# for suppliers who supplied ‘P2’ AND are
located in London.
Select S#
From shipment
Where p# = ‘P2’ and S# IN ( select S#
from suppliers
where (city = ‘London’) )

IN and NOT IN
Select S#
From shipment
Where p# = ‘P2’ and S# IN ( select S#
from suppliers
where (city = ‘London’) )
 First check if P# for s# = P2
 If passes then
 Check S# is in London or not.
 OR
Select S#
From shipment
Where p# = ‘P2’ and S# IN ( ‘S1’ , ‘S2 , ‘S5’ )
 We can also use NOT IN

ANY
 > ANY
 < ANY
 <= ANY
 >= ANY
 <> ANY
 This operator is called “set comparison operator”
 Example
 Find SNAME where all suppliers who had greater status
then some supplier located in ‘London’

ANY
Select SNAME
From supplier
Where status > ANY ( select status
from supplier
where city = ‘London’ )

ALL
 > ALL
 < ALL etc
Select SNAME
From supplier
Where status > ALL (select status
from supplier
where ( city = ‘London’ )

Tuple Variable
 SQL uses the notion of tuple variables
 A tuple variable is defined in the FROM clause by
placing it after the name of the relation separated by
space.
 Tuple variables are most useful for comparing 2 tuples
in the same relation.

Tuple Variable
 Example
find all customers who have a deposit account at
some branch at which Tom has an account.
Select copy2.customer
From deposit copy1 , deposit copy2
Where (copy1.customer = ‘Tom’
AND copy1.branchName = copy2.branchName)
 This makes 2 copies , makes an anchor and then
compare the 2 tuples

Tuple Variable
 Example
get supplier’s name SNAME for suppliers who are
located in the same city as Tom.
Select B.SNAME
From supplier A, supplier B
Where A.SNAME = ‘Tom’ AND (A.city = B.city)

Tuple Variable
 Example
Get all pairs of SNAME such the two suppliers
concerned are located in the same city.
Select X.SNAME , Y.SNAME
From supplier X , supplier Y
Where X.city = Y.city;
Now we might get some redundant information
Tom Tom
Tom Bob
Bob Tom
Etc, now how to eliminate duplicates ?

Tuple Variable
Select X.SNAME , Y.SNAME
From supplier X , supplier Y
Where ( X.city = Y.city) AND (X.SNAME >
Y.SNAME)
Now we might get
Tom Tom
Tom Bob
Bob Tom

Extra Clauses And Features
 ORDER BY:
 Used for ordering the display of tuples
 Order of statements is as follows:
 Select
 From
 Where
 Order by

ORDER BY
Example:
select *
from suppliers
where status >= 10
order by status
S# SNAME STATUS CITY
S1 Smith 20 London
S2 Jones 10 Paris
S3 Blake 30 Paris
S4 Clark 20 London
S5 Adams 30 Athens
Supplier S (original table)
S# SNAME STATUS CITY
S2 Jones 10 Paris
S4 Clark 20 London
S1 Smith 20 London
S3 Blake 30 Paris
S5 Adams 30 Athens
Query result

ORDER BY
 Default Order of order by is ascending.
 If we use
 Order by column_name desc
 We get a descending order of the tuples according to
column named column_name
Select *
From shipment
Order by s# , P# desc

ORDER BY
Select account , customer , balance
From deposit
Order by 3 , 1 , 2

Computing Functions
 Computing functions cannot be nested, I.e min(max())
 Examples of computing functions are
 Avg
 Sum
 Count
 Max
 Min etc

Computing Functions
 Examples
Select avg(balance)
From deposit;
Select avg(qty)
From shipment;
Select avg(qty)
From shipment
Where S#=S1

Computing Functions
 Examples
count the number of tuples in shipment
select count(*)
from shipment;
how many suppliers have made shipment
select count(distinct S#)
from shipment;

Modify Part Of DML
 1) Deletion
 Delete whole tuple(s)
 <format>
Delete from table_name
Where condition
e.g.
Delete all of shipments made by S2
delete from shipment
where S# = ‘S2’

Deletion
 E.g
Delete from suppliers
Where city = ‘London’
 E.g.
 Delete all deposit accounts at branches located in Chicago
 Deposit(branch_name, Account, customer, balance)
 Branches(branch_name, asserts , city)
Delete from deposit
Where branch_name IN ( select branch_name
from branches
Where city = ‘Chicago’ ; )

Data Manipulation Language
 A DML statement is executed when you:
 Add new rows to a table
 Modify existing rows in a table
 Remove existing rows from a table
 A transaction consists of a collection of
DML statements that form a logical unit
of work.

Adding a New Row to a Table
DEPT
DEPTNO DNAME LOC
------ ---------- --------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
New row
50 DEVELOPMENT DETROIT
DEPT
DEPTNO DNAME LOC
------ ---------- --------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
“…insert a new row
into DEPT table…”
50 DEVELOPMENT DETROIT

The INSERT Statement
Add new rows to a table by using the
INSERT statement.
Only one row is inserted at a time with
this syntax.
INSERT INTO table [(column [, column...])]
VALUES (value [, value...]);

Inserting New Rows
SQL> INSERT INTO dept (deptno, dname, loc)
2 VALUES (50, 'DEVELOPMENT', 'DETROIT');
1 row created.
 Insert a new row containing values for each
column.
 List values in the default order of the
columns in the table.
 Optionally list the columns in the INSERT
clause.
 Enclose character and date values within
single quotation marks.

Null Values
 A Null value is either unknown or not applicable.
 NULL is not equal to “” , or FI, or empty string
 E.g
 Insert into deposit
 Values(‘NEIU’ , 1006 , NULL , 1200)
 OR
 Values(‘NEIU’ , 1006 , , 1200)

Nulls
 Problems caused by NULL values
 All comparison operations involving NULL are false.
 They simply wont be selected.
 Avoid using NULLs.
Select *
From shipment
Where qty < 200;
 So NULL rows wont be selected.

DDL (Data Defination Language)
 To specify or modify schema definations
 To create tables
 To Update tables

Creating and Managing
Tables

Objectives
 After completing this lesson, you
should be able to do the following:
 Describe the main database objects
 Create tables
 Describe the datatypes that can be used
when specifying column definition
 Alter table definitions
 Drop, rename, and truncate tables

Database Objects
Object Description
Table Basic unit of storage; composed of rows
and columns
View Logically represents subsets of data from
one or more tables
Sequence Generates primary key values
Index Improves the performance of some queries
Synonym Gives alternative names to objects

Naming Conventions
Must begin with a letter
Can be 1–30 characters long
Must contain only A–Z, a–z, 0–9, _, $, and #
Must not duplicate the name of another
object owned by the same user
Must not be an Oracle Server reserved word

The CREATE TABLE Statement
CREATE [GLOBAL TEMPORARY] TABLE [schema.]table
(column datatype [DEFAULT expr][, ...]);
 You must have :
 CREATE TABLE privilege
 A storage area
 You specify:
 Table name
 Column name, column datatype, and
column size

Referencing Another User’s
Tables
 Tables belonging to other users are not
in the user’s schema.
 You should use the owner’s name as a
prefix to the table.

The DEFAULT Option
… hiredate DATE DEFAULT SYSDATE, …
 Specify a default value for a column during an
insert.
• Legal values are literal value, expression, or SQL
function.
• Illegal values are another column’s name or
pseudocolumn.
• The default datatype must match the column
datatype.

Creating Tables
SQL> CREATE TABLE dept
2 (deptno NUMBER(2),
3 dname VARCHAR2(14),
4 loc VARCHAR2(13));
Table created.
• Confirm table creation.
SQL> DESCRIBE dept
Name Null? Type
--------------------------- -------- ---------
DEPTNO NUMBER(2)
DNAME VARCHAR2(14)
LOC VARCHAR2(13)
 Create the table.

Tables in the Oracle Database
 User Tables
 Collection of tables created and maintained
by the user
 Contain user information
 Data Dictionary
 Collection of tables created and maintained
by the Oracle server
 Contain database information

Querying the Data Dictionary
• View distinct object types owned by the
user.
• View tables, views, synonyms, and
sequences owned by the user.
SQL> SELECT *
2 FROM user_tables;
SQL> SELECT DISTINCT object_type
2 FROM user_objects;
SQL> SELECT *
2 FROM user_catalog;
• Describe tables owned by the user.

Datatypes
Datatype Description
VARCHAR2(size) Variable-length character data
CHAR(size) Fixed-length character data
NUMBER(p,s) Variable-length numeric data
DATE Date and time values
LONG Variable-length character data
up to 2 gigabytes
CLOB Single-byte character data up to 4
gigabytes
RAW and LONG RAW Raw binary data
BLOB Binary data up to 4 gigabytes
BFILE Binary data stored in an external
file; up to 4 gigabytes

Creating a Table
by Using a Subquery
CREATE TABLE table
[(column, column...)]
AS subquery;
 Create a table and insert rows by combining the
CREATE TABLE statement and AS subquery option.
 Match the number of specified columns to the
number of subquery columns.
 Define columns with column names and
default values.

SQL> CREATE TABLE dept30
2 AS
3 SELECT empno, ename, sal*12 ANNSAL, hiredate
4 FROM emp
5 WHERE deptno = 30;
Table created.
Creating a Table
by Using a Subquery
Name Null? Type
---------------------------- -------- -----
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
ANNSAL NUMBER
HIREDATE DATE
SQL> DESCRIBE dept30

The ALTER TABLE Statement
ALTER TABLE table
ADD (column datatype [DEFAULT expr]
[, column datatype]...);
ALTER TABLE table
MODIFY (column datatype [DEFAULT expr]
[, column datatype]...);
 Use the ALTER TABLE statement to:
 Add a new column
 Modify an existing column
 Define a default value for the new
column

Adding a Column
DEPT30
EMPNO ENAME ANNSAL HIREDATE
------ ---------- --------
7698 BLAKE 34200 01-MAY-81
7654 MARTIN 15000 28-SEP-81
7499 ALLEN 19200 20-FEB-81
7844 TURNER 18000 08-SEP-81
...
“…add a
new
column
into
DEPT30
table…”
DEPT30
EMPNO ENAME ANNSAL HIREDATE
------ ---------- --------
7698 BLAKE 34200 01-MAY-81
7654 MARTIN 15000 28-SEP-81
7499 ALLEN 19200 20-FEB-81
7844 TURNER 18000 08-SEP-81
...
JOB
JOB
New column

Adding a Column
EMPNO ENAME ANNSAL HIREDATE JOB
--------- ---------- --------- --------- ----
7698 BLAKE 34200 01-MAY-81
7654 MARTIN 15000 28-SEP-81
7499 ALLEN 19200 20-FEB-81
7844 TURNER 18000 08-SEP-81
...
6 rows selected.
SQL> ALTER TABLE dept30
2 ADD (job VARCHAR2(9));
Table altered.
• The new column becomes the last column.
 You use the ADD clause to add
columns.

Modifying a Column
2 MODIFY (ename VARCHAR2(15));
Table altered.
 You can change a column’s datatype, size,
and default value.
 A change to the default value affects only
subsequent insertions to the table.

Dropping a Column
2 DROP COLUMN job ;
Table altered.
You use the DROP COLUMN clause drop
columns you no longer need from the
table.

SET UNUSED Option
ALTER TABLE table
(column);
ALTER TABLE table
COLUMN column;
OR
ALTER TABLE table
DROP UNUSED COLUMNS;
SET UNUSED
SET UNUSED
 You use the SET UNUSED option to mark one
or more columns as unused.
 You use the DROP UNUSED COLUMNS option
to remove the columns that are marked as
UNUSED.

Dropping a Table
SQL> DROP TABLE dept30;
Table dropped.
 All data and structure in the table is
deleted.
 Any pending transactions are committed.
 All indexes are dropped.
 You cannot roll back this statement.

Changing the Name of an
Object
SQL> RENAME dept TO department;
Table renamed.
 To change the name of a table, view,
sequence, or synonym, you execute the
RENAME statement.
 You must be the owner of the object.

Truncating a Table
SQL> TRUNCATE TABLE department;
Table truncated.
 The TRUNCATE TABLE statement:
 Removes all rows from a table
 Releases the storage space used by that
table
 You cannot roll back row removal when
using TRUNCATE.
 Alternatively, you can remove rows by
using the DELETE statement.

Adding Comments to a Table
SQL> COMMENT ON TABLE emp
2 IS 'Employee Information';
Comment created.
 You can add comments to a table or
column by using the COMMENT statement.
 Comments can be viewed through the
data dictionary views.
 ALL_COL_COMMENTS
 USER_COL_COMMENTS
 ALL_TAB_COMMENTS
 USER_TAB_COMMENTS

Summary
Statement Description
CREATE TABLE Creates a table
ALTER TABLE Modifies table structures
DROP TABLE Removes the rows and table structure
RENAME Changes the name of a table, view,
sequence, or synonym
TRUNCATE Removes all rows from a table and
releases the storage space
COMMENT Adds comments to a table or view

End
END OF LESSON 05
(slides adapted from M Ali Shahid’s original slides)

Lesson05 relational languages sql

More Related Content

Similar to Lesson05 relational languages sql

More from Muhammad Sikandar Mustafa

Recently uploaded

Lesson05 relational languages sql