Lecture 5-6

Question 1

What is the cartesian product?

Answer 1

creates a new relation by combining every tuple in 1st with every tuple in the second. Is meaningless unless used by other operators.

Question 2

What is JOIN? What is a natural join?

Answer 2

produces a new relation that contains combinations of tuples in 1st and 2nd which satisfy join condition.
A natural join is an equijoin(uses equality) over all common attributes, only one copy of each common attribute is kept.

Question 3

What is division?

Answer 3

Produce a relation in which each tuple must appear in the 1st in combination with every tuple in the second.

Question 4

What is the CREATE statement?

Answer 4

can be used to create many things, e.g Table, schema, view, etc.

Question 5

What are the main oracle built in data types?

Answer 5

Number(p, s): where p is the precision(1-38) and s is the scale(-84, 127) NUMBER(7,2) would be a number with 5 digits before the decimal and two after.
Char(size): fixed length character data
VARCHAR2(size): variable-length character string
DATE
CLOB: A large object containing characters
BLOB: a binary large object
BFILE: Contains a locator to a large binary file.

Question 6

What is the CREATE TABLE command?

Answer 6

CREATE TABLE tablename ( column1 datatype (constraint stuff),…
Final constraints
);
Creates a table.

Question 7

What are the main attributes constraints?

Answer 7

NOT NULL, DEFAULT , CHECK (checks value of attribute).

Question 8

What are the main integrity constraints?

Answer 8

PRIMARY KEY, either at the end of single or attribute or at the end in the form PRIMARY KEY(A1, A2).
Unique clause specifies candidate keys.

for single attribute
FOREIGN KEY REFERENCES table1(A1),
For multiple attributes or in general:
FOREIGN KEY (A1) REFERENCES table1(A2)
Actions can be specified if integrity is broken e.g SET NULL, CASCADE, SET DEFAULT.

Question 9

How can we name constraints?

Answer 9

Constraints must have unique names and can be named after calling the CONSTRAINT keyword.

Question 10

What is DATE?

Answer 10

Oracle uses DATE for both the date and time:
DD for day, MM for numeric month, MON (abbreviated month name), MONTH full month name, YY, YYYY, HH (12 hour format), HH24 24 hour, MI minutes of hour, SS seconds of minute.
SQL has DATE: YYYY-MM-DD, TIME: HH:MM:SS,
TIMESTAMP: YYYY-MM-DD HH:MI:SS[.ssssss]

TO_DATE can be used to specify the format for oracle in the form (date, format).
TO_CHAR can be used to format the output of a date:
TO_CHAR(dateattribute, format).

Question 11

What is the DROP TABLE command?

Answer 11

Drops a table, DROP TABLE table [CASCADE CONSTRAINTS];

The CASCADE CONSTRAINTS drops all referential integrity constraints referring to the dropped table.

Question 12

What is the ALTER TABLE command?

Answer 12

Can be used to ADD:
ALTER TABLE table ADD attribute VARCHAR(12);
Change:
ALTER TABLE table MODIFY attribute ;
ADD a constraint:
ALTER TABLE table ADD CONSTRAINT cnst etc,
ENABLE CONSTRAINT etc.

Question 13

What is the INSERT command?

Answer 13

Inserts a tuple, can be used as
INSERT INTO table VALUES (value1, value2);
in order.
or
INSERT INTO table (A1, A2) VALUES (value1, value2);
If used in the second form all NOT NULL attributes without default values must be mentioned.

Question 14

What do the DELETE and UPDATE commands do?

Answer 14

DELETE: Deletes a tuple from table based on criteria
DELETE FROM table WHERE A1 = ‘example’
UPDATE command modifies attribute values:
UPDATE table SET A1 = ‘example’ WHERE A2 = 42

Question 15

What does the SELECT command do?

Answer 15

SELECT FROM table WHERE condition;

Selects attributes from tuples which satisfy selection criteria.

Question 16

What does the WHERE clause do?

Answer 16

Details the selection criteria, can be very complex and use special operators like IN, BETWEEN, LIKE, IS NULL, or less complicted like =, >, >=, .

Question 17

How does the LIKE operator work?

Answer 17

LIKE .
Underscore matches any single character.
% matches a sequence of any number of characters.
These can be used literally using the \ escape character.

Question 18

What does the DISTINCT option do?

Answer 18

Removes duplicates from set of tuples.

Question 19

What does GROUP BY do? What does HAVING do?

Answer 19

defines a subset of the values of a particular field and to apply an aggregate function to the subsets. e.g SELECT A1, MAX(A2) FROM table GROUP BY A1;
HAVING may follow the GROUP BY and can restrict the groups.
e.g
SELECT A1, MAX(A2) FROM table GROUP BY A1 HAVING MAX(A2) > 21;

Question 20

What does ORDER BY do?

Answer 20

Orders output according to values in one or more selected columns. Can be ascending ASC, or DESC descending. Defaults to ascending.
E.g
SELECT * FROM table ORDER BY A1, A2, DESC;

Question 21

What does the UNION operator do?

Answer 21

Combines queries and results. There are no column headings in output, duplicate are elminated except in UNION ALL, the queries must have the same number of columns and be data type compatible. Can be Followed by ORDER BY.