Table Alteration

1
Table Alteration
2
Altering Tables

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

3
Altering Tables (continued)

• 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
4
Altering Tables (continues)

• 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))

5
Users Table List

• ORACLE may print tables that hold some general
  information about the tables in your database
• Such Tables are
• Tab, Cat, User_Tables (too detailed...)
• To see the list of all your tables you can print
• SELECT FROM Cat
• SELECT tname FROM Tab
• SELECT table_name from User_Tables

6
Table Data Maintenance
7
The Employee Table

• 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)
• When will this fail?

9
Some More Details

• The fields neednt 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, 10000)

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 (continues)

• To remove all male employees having a salary
  greater than 15000 shekels
• DELETE FROM Employee WHERE
• Gender M AND Salary 15000
• The WHERE clause is basically the same as one in
  a query

Case sensitive
12
Updating Rows (continues)

• We can update fields of rows in a table
• General format
• UPDATE Table SET Field1value1,,,FieldNval
  ueN
• WHERE Cond
• Now we can reduce salaries instead of firing
  employees
• UPDATE Employee SET Salary 15000
• WHERE Gender M AND
• Salary 15000

13
The ORACLE Bulk Loader

• A tool that provides easy insertion of large
  amounts of rows into tables.
• The idea the field values of the rows are kept
  in a file, the format of which is defined by us.
• For example, it can automatically load 3
  employees from the file myEmployees.dat that
  contains the following lines
• SaraCohen121
• BennyKimelfeld134
• YaronKanza156

14
The Control File

• The control file is the direct input of the
  loader
• A simple control file
• LOAD DATA
• INFILE
• APPEND INTO TABLE
• FIELDS TERMINATED BY '
• ()

15
The Control File (continued)

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

16
The Control File (continues)

• As an example, the following control file loads
  the employees from myEmployees.dat
• LOAD DATA
• INFILE myEmployees.dat
• INTO TABLE Employees
• FIELDS TERMINATED BY ''
• (Fname, Lname, SSN)
• The attributes that are unspecified will be set
  to NULL

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!
• thus the rows sara cohen121 and saracohen121
  define different functionalities
• 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 control log
  bad
• All fields are optional
• File names that have no extension are
  automatically extended (by .dat, .log or .bad)
• Erroneous lines in the data file are ignored and
  written into badFile, and any other relevant
  information is written into logFile.

20
Bulk Loader Important Remarks

• Before using the Bulk Loader, make sure your
  personal ORACLE environment is properly set up
• 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!

21
SQL Queries
22
Query Components

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

23
Basic SQL Query
SELECT Distinct Attributes FROM relations
WHERE condition

• Attributes A list of fields onto which the query
  projects (For example Eid, Ename).
• relations A list of relation names (For example
  Employees, Departments, Works).
• condition A Boolean condition (For example
  Eid21, or EnameYuval )
• DISTINCT Optional keyword to delete duplicates

24
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.

25
Basic SQL Query
SELECT Distinct attributes FROM relations
WHERE condition

• Evaluation order
• 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.

26
Basic SQL Query
SELECT Distinct A1,,An FROM R1,,Rm WHERE C
?A1,,An (?C(R1 xx Rm))
27
Query Without WHERE
SELECT Distinct A1,,An FROM R1,,Rm
?A1,,An (R1 xx Rm)
28
Query Without Projection
SELECT Distinct FROM R1,,Rm WHERE C
(?C(R1 xx Rm))
29
Query Without Projection, Without WHERE
SELECT Distinct FROM R1,,Rm
R1 xx Rm
30
Example Tables Used
31
Example Query
SELECT DISTINCT sname, age FROM Sailors WHERE
rating7

• Q What does this compute?
• A Distinct names and ages of sailors with a
  rating 7.
• Q Write it in algebra
• A ?sname, age (?rating7(Sailors))
• When would the result be different if we removed
  distinct?

32
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))
33
Sailors x Reserves
34
(No Transcript)
35
Sailors x Reserves
36
Range Variables
SELECT S.sname FROM Sailors S, Reserves R
WHERE S.sid R.sid and R.bid 103

• Range variables are good style.
• They are necessary if the same relation appears
  twice in the FROM clause
• Similar to Renaming in Relational Algebra

37
Example Query
SELECT S.sname FROM Sailors S, Reserves R
WHERE S.sid R.sid and R.bid ! 103
Q What does this return?A Names of sailors
who reserved a boat other than boat
103Notice sailors who reserved both boat 103
and a different boat will appear!
38
A Few SELECT Options

• Select all columns
• SELECT
• FROM Sailors S
• 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

39
The WHERE Clause

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

40
Examples

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

41
The LIKE Operator

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

SELECT sid FROM Sailors WHERE
sname LIKE R_y
42
SQL query
SELECT S.sid FROM Sailors S, Reserves R WHERE
S.sid R.sid
43
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
44
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
45
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.
46
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
47
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

48
Example
SELECT sname, rating, age FROM Sailors S
WHERE age 50 ORDER BY rating ASC, age DESC
49
Other Relational Algebra Operators

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

50
Three SET Operators

• Query UNION Query
• Query MINUS Query
• Query INTERSECT QUERY
• Note The operators remove duplicates by default!
• How would you express intersect in Relational
  Algebra?

51
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')
52
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?
53
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
54
Multiset (Bag) Operators

• SQL standard includes 3 bag operators
• UNION ALL
• INTERSECT ALL
• MINUS ALL
• Oracle supports only UNION ALL. Does not remove
  duplicates when performing UNION

55
Example
SELECT DISTINCT sname FROM Sailors
S UNION ALL SELECT DISTINCT sname FROM
Sailors S
56
Nested Queries
57
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 wouldnt it?

58
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 whove reserved boat 103 What
would happen if we wrote NOT IN?
59
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'))
60
Set-Comparison Queries
Sailors who are not the youngest
SELECT FROM Sailors S1 WHERE S1.age ANY
(SELECT S2.age FROM Sailors S2)
We can also use op ALL (op is , , ).
61
Correlated Nested Queries
Names of sailors whove reserved boat 103
SELECT S.sid FROM Sailors S WHERE EXISTS
(SELECT FROM Reserves R WHERE R.bid
103 and S.sid R.sid)
What would happen if we wrote NOT EXISTS?
S not in subquery, refers to outer loop
62
Exists and Not Exists

• Differ 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.