Reminder

1
Reminder

• We have covered
• Creating tables
• Converting ER diagrams to table definitions
• Today well talk about
• Altering tables
• Inserting and deleting data from tables
• Querying tables

2
Table Alteration
3
Altering Tables

• Table definitions can be altered after their
  creation
• Adding columns
• Changing columns definition
• Dropping columns
• Adding or dropping constraints
• And more
• Use the reserved word ALTER

4
Altering Tables (cont.)

• Adding a column
• ALTER TABLE Employee ADD (
• Mname VARCHAR2(20),
• Birthday DATE
• )
• Changing columns definition
• ALTER TABLE Emplyee Modify (
• Mname VARCHAR2(10)
• )

Cannot be NOT NULL unless the table is empty
5
Altering Tables (cont.)

• Dropping columns
• ALTER TABLE Employee DROP COLUMN Mname
• Dropping multiple columns
• ALTER TABLE Employee DROP
• (Mname, Birthday)
• Adding constraints
• ALTER TABLE Department ADD(
• FOREIGN KEY (ManagerId)
• REFERENCES Employee(SSN))

6
Inserting, deleting, and updating data in a table
7
The Employee Table

• gt Describe Employee
• Name Null? Type
• -------- -------- ------------
• SSN NUMBER
• FNAME VARCHAR2(20)
• LNAME VARCHAR2(20)
• GENDER CHAR(1)
• SALARY NOT NULL NUMBER(5)

8
Inserting a Row

• To insert a row into the Employee table
• INSERT INTO
• Employee(SSN, Fname, Lname, Salary)
• VALUES(121, Sara, Cohen,10000)
• The remaining columns get default values (or
  NULL)
• Q When will this fail?
• A Incompatible data-types, number of variables,
  etc.

9
Some More Details

• The fields dont have to be specified if values
  are specified for all columns and in the order
  defined by the table
• Example
• INSERT INTO Employee
• VALUES(121, Sara, Cohen, F, 1000)

10
Deleting Rows

• General format
• DELETE FROM Table WHERE Cond
• Deletes all rows satisfying Cond from Table
• For example, to remove the employee with SSN 121
  from the Employee table
• DELETE FROM Employee
• WHERE SSN 121

11
Deleting Rows (cont.)

• To remove all employees having a salary greater
  than 100,000
• DELETE FROM Employee
• WHERE Salary gt 100000

12
Updating Rows

• We can update rows in a table
• General format
• UPDATE Table
• SET Field1value1,,,FieldNvalueN
• WHERE Cond
• Now we can reduce salaries instead of firing
  employees
• UPDATE Employee SET Salary 100000
• WHERE Salary gt 100000

13
The ORACLE Bulk Loader

• A tool that provides easy insertion of large
  amounts of rows into tables.
• The idea
• The inserted data is kept in a compact file (Data
  File)
• In the Control File we define loading parameters

myData.dat
myCtrl.ctl
Here we enter the data we want to insert
(employees)
Here we specify loading parameters
14
Loading

• For example, the data file may contain
• ShaquileOneil121
• MagicJohnson134
• LarryBird156
• The control file has the following format
• LOAD DATA
• INFILE ltdataFilegt
• APPEND INTO TABLE lttableNamegt
• FIELDS TERMINATED BY 'ltseparatorgt
• (ltlist of all attribute names to loadgt)

15
The Control File (cont.)

• ltdataFilegt
• The name of the data file
• lttableNamegt
• The name of the table into which the data will be
  loaded (appended if APPEND is specified, or else
  the table must be empty)
• ltseparatorgt
• A string that separates two field values of a row
• The attributes are separated by commas and
  enclosed in parentheses

16
The Control File (cont.)

• Example
• When the control file is run, this will insert
  the 3 employees into the employees table
• The attributes that are unspecified will be set
  to NULL (or default values if they are specified)

myEmployees.dat
myControl.ctl
LOAD DATA INFILE myEmployees.dat INTO TABLE
Employees FIELDS TERMINATED BY '' (Fname,
Lname, SSN)
MosheCohen334 MiriLevinson998 EladKeren998
17
The Data File

• The Bulk Loader considers every single line to
  represent one row in the table
• Even an empty line! (which will usually result in
  an error)
• Spaces are not ignored in the data file, so
• sara cohen121 and
• saracohen121 are different!
• The NULL value is implied by the NULL keyword or
  the empty string

18
The Data File (continued)

• The control and the data files can be combined
  into one .ctl file using the following format
• LOAD DATA
• INFILE
• INTO TABLE Employees
• FIELDS TERMINATED BY ''
• (Fname, Lname, SSN)
• BEGINDATA
• SaraCohen121
• BennyKimelfeld134
• YaronKanza156

19
The Bulk Invocation

• To invoke the bulk loader, issue the following
  command directly from the Unix shell
• Sqlldr ltctrlFileNamegt
• Erroneous lines in the data file are ignored and
  written into ltctrlFileNamegt.bad, and any other
  relevant information is written into
  ltctrlFileNamegt.log.
• The tables you fill using the Bulk Loader should
  be created prior to the loader invocation
• Before invoking the Bulk Loader you have to make
  sure that no SqlPlus sessions are open.

20
SQL Queries
21
Basic SQL query structure
SELECT Attributes FROM relations WHERE
condition
For example
SELECT sid,sname FROM students WHERE sid1122
22
Query Components

• A query can contain the following clauses
• select
• from
• where
• group by
• having
• order by
• Only select and from are obligatory
• Order of clauses is always as above

23
Very Basic SQL Query
SELECT Distinct Attributes FROM relations

• Attributes The attributes which will appear in
  the query result (For example Eid, Ename).
• Relations Relations to perform the query on. If
  more than one relation is mentioned, the
  operation is on the cartesian product between the
  relations
• DISTINCT Optional keyword to delete duplicates

Example Select studentID, studentName From
students
24
Select studentID, studentName From
students Result
25
Basic SQL Query
SELECT Distinct Attributes FROM relations
WHERE condition

• condition A Boolean condition (For example
  Eidgt21, or EnameYuval ). Only tuples which
  return true for this condition will appear in
  the result

26
Select studentID, studentName From students Where
StudentDeptMath Result
27
Basic SQL Query
SELECT Distinct attributes FROM relations
WHERE condition

• Notice! The "SELECT" clause defines the operation
  of projection from the relational model.
  Selection is defined by the WHERE clause.

28
SQL and relational algebra
SELECT Distinct A1,,An FROM R1,,Rm WHERE C
?A1,,An (?C(R1 xx Rm))
29
Basic SQL Query
SELECT Distinct attributes FROM relations
WHERE condition

• Important! The evaluation order is
• Compute the cross product of the tables in
  relations.
• Delete all rows that do not satisfy condition.
• Delete all columns that do not appear in
  attributes.
• If Distinct is specified eliminate duplicate rows.

30
Example Tables Used
31
What does this compute?
Select sname from sailors, reserves Where
sailors.sidreserves.sid
All sailors who have reserved a boat
32
Stage 1 Sailors x Reserves
33
Stage 2 where sailors.sidreserves.sid
34
Stage 2 where sailors.sidreserves.sid
35
Stage 3 select sname
36
Stage 3 select sname
Final answer
37
Example Query
SELECT DISTINCT sname, age FROM Sailors WHERE
ratinggt7
Q What does this compute? A Distinct names and
ages of sailors with rating gt7. Q Write it in
algebra A ?sname, age (?ratinggt7(Sailors))
38
Example Query
SELECT DISTINCT sname FROM Sailors, Reserves
WHERE Sailors.sid Reserves.sid and bid
103
Q What does this compute? A names of sailors
who reserved boat 103 Q Write it in relational
algebra ?sname(?Sailors.sid Reserves.sid ? bid
103 (Sailors x Reserves))
39
(No Transcript)
40
Sailors x Reserves
41
A Few SELECT Options

• Select all columns
• SELECT
• FROM Sailors
• Rename selected columns
• SELECT S.sname AS Sailors_Name
• FROM Sailors S
• Applying functions (e.g., Mathematical
  manipulations)
• SELECT (age-5)2
• FROM Sailors S

42
Select operators

• The aggregate operators available in SQL are
• COUNT()
• COUNT(DISTINCT A)
• SUM(DISTINCT A)
• AVG(DISTINCT A)
• MAX(A)
• MIN(A)
• NULL values are ignored

43
Examples
SELECT Max(S.age) FROM Sailors S
SELECT count(distinct S.sid) FROM Sailors S,
Reserves R WHERE S.sid R.sid

• SELECT Avg(S.age)
• FROM Sailors S, Reserves R
• WHERE S.sid R.sid and R.bid112

44
The WHERE Clause

• Numerical and string comparison
• !,ltgt,, lt, gt, gt, lt, between(val1 AND val2)
• Logical components AND, OR
• Null verification IS NULL, IS NOT NULL
• Checking against a list with IN, NOT IN.

45
Examples

• SELECT sname
• FROM Sailors
• WHERE agegt40 AND rating IS NOT NULL
• SELECT sid, sname
• FROM sailors
• WHERE sid IN (1223, 2334, 3344) or
• sname between(George and Paul)

46
The LIKE Operator

• A pattern matching operator (regular expression)
• Basic format colname LIKE pattern
• Example
• _ is a single character
• is 0 or more characters

SELECT sid FROM Sailors WHERE
sname LIKE R_y
47
Relation naming
SELECT S.sname FROM Sailors S, Reserves R
WHERE S.sid R.sid and R.bid 103

• Naming relations is good style
• It is necessary if the same relation appears
  twice in the FROM clause
• Similar to Renaming in Relational Algebra

48
Example Query
SELECT S.sname FROM Sailors S, Reserves R
WHERE S.sid R.sid and R.bid ! 103
Q Does this return the names of sailors who did
not reserve boat 103?
A No! it returns the names of sailors who
reserved a boat other than boat 103
49
SQL query
SELECT S.sid FROM Sailors S, Reserves R WHERE
S.sid R.sid
When there is a sailor who reserved more than a
single boat
50
Are any of these the same?
SELECT S.sid FROM Sailors S, Reserves R WHERE
S.sid R.sid
SELECT DISTINCT R.sid FROM Sailors S, Reserves
R WHERE S.sid R.sid
SELECT R.sid FROM Reserves R
51
Example Query
How would you query sailors who have reserved
more than one boat?
SELECT S.sname FROM Sailors S, Reserves R1,
Reserves R2. WHERE S.sid R1.sid and
R1.sidR2.sid and R1.bid!R2.bid
52
SQL query
SELECT S.sname FROM Sailors S, Reserves R,
Boats B WHERE S.sid R.sid and R.bid
B.bid and B.color 'red'
Q What does this return? A Names of sailors who
have reserved a red boat.
53
SQL query
Q How would you query the colors of boats
reserved by Bob? A
SELECT distinct B.color FROM Sailors S,
Reserves R, Boats B WHERE S.sname Bob and
S.sid R.sid and R.bid B.bid
54
Order Of the Result

• The ORDER BY clause can be used to sort results
  by one or more columns
• The default sorting is in ascending order
• Can specify ASC or DESC

55
Example
SELECT sname, rating, age FROM Sailors S
WHERE age gt 50 ORDER BY rating ASC, age DESC
56
Other Relational Algebra Operators

• So far, we have seen selection, projection and
  Cartesian product
• How do we do operators UNION and MINUS?

57
Three SET Operators

• Query UNION Query
• Query MINUS Query
• Query INTERSECT QUERY
• Note The operators remove duplicates by default!

58
What does this return?
SELECT DISTINCT S.sname FROM Sailors S,
Reserves R, Boats B WHERE S.sid R.sid and
R.bid B.bid and (B.color 'red' or
B.color'green')
We would get no results! Then how can we query
sailors who have reserved both a green and a red
boat? (in a few slides)
59
Sailors whove reserved red or green boat
SELECT S.sname FROM Sailors S, Boats B,
Reserves R WHERE S.sid R.sid and R.bid B.bid
and B.color red UNION SELECT S.sname
FROM Sailors S, Boats B, Reserves R WHERE S.sid
R.sid and R.bid B.bid and B.color
green
Would INTERSECT here give us sailors who reserved
both red and green boats?
Almost, but not quite because sname is not unique
60
Sailors whove reserved red and green boats
SELECT S.sname FROM Sailors S, Reserves R1,
Reserves R2 Boats B1, Boats B2 WHERE
S.sid R1.sid and R1.bid B1.bid and
B1.color red and S.sid R2.sid and R2.bid
B2.bid and B2.color green
61
Multiset (Bag) Operators

• Union without removing duplicates
• UNION ALL

SELECT DISTINCT sname FROM Sailors
S UNION ALL SELECT DISTINCT sname FROM
Sailors S
62
Nested Queries
63
Nested queries in WHERE

• Equality nested query
• Select R.bid
• From Sailors S, Reserves R
• Where sid (select sid from S where
  snameGeorge)
• When would this work?
• When the subquery returns exactly one tuple

64
Nested queries in WHERE
Subqueries with multiple results
SELECT S.sname FROM Sailors S WHERE S.sid IN
(SELECT R.sid FROM Reserves R WHERE
R.bid 103)
Names of sailors who reserved boat 103 What
would happen if we wrote NOT IN? We would get
names of sailors who did not reserve boat 103 !
65
What does this produce?
SELECT S.sname FROM Sailors S WHERE S.sid NOT
IN (SELECT R.sid FROM Reserves R
WHERE R.bid IN (SELECT B.bid FROM
Boats B WHERE B.color'red'))
Names of sailors who did not reserve a red boat
66
Set-Comparison Queries
SELECT FROM Sailors S1 WHERE S1.age gt ANY
(SELECT S2.age FROM Sailors S2)
Sailors who are not the youngest
We can also use op ALL (op is gt, lt, , gt, lt, or
ltgt).
67
Correlated Nested Queries
SELECT S.sid FROM Sailors S WHERE EXISTS
(SELECT FROM Reserves R WHERE R.bid
103 and S.sid R.sid)
S not in subquery, refers to outer loop
Sid of sailors who reserved boat 103 Q What if
we wrote NOT EXISTS? A We would get sid of
sailors who did not reserve boat 103
68
Exists and Not Exists

• Differs from In and Not In by not matching
  attributes.
• Exists
• For every tuple in the outer loop, the inner
  loop is tested. If the inner loop produces a
  result, the outer tuple is added to the result.

69
Users Table List

• ORACLE holds tables with some general information
  about the tables in your database
• Such Tables are
• Cat, user_objects
• To see the list of all your tables print
• SELECT FROM Cat
• To see the list of all your objects print
• SELECT object_name, timestamp, object_type FROM
  user_objects