SQL 3

What are the non-alphanumeric characters that can be used in a db object name?

$, #, _ Shhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh, UNDERSCORE!

A db transaction begins with the first DML SQL statement executed. What 4 things can it end with?

-A COMMIT or ROLLBACK -A DDL or DCL statement executes (automatic commit) -The user executes SQL Developer or DQL*Plus -The system crashes

What are the 3 implicit ways a commit can occur?

-DDL statement issued -DCL statement issued -A normal exit from the db client WITHOUT explicitly issuing COMMIT or ROLLBACK.

What is the state of data after a commit?

-Data changes are saved in the db -The previous state of the data is overwritten -All sessions can view the results -Lock on the affected rows are released; those rows are available for other sessions to manipulate. -all savepoints are erased

What is the max length of a database object in Oracle?

1-30 chars

What is a transaction?

A collection of DML statements that form a logical unit of work.

What is required to get an AUTOCOMMIT in SQL Developer?

A normal exit from the File menu. Closing the window counts as an abnormal exit, and will be rolled back.

What table can you query to get a list of all tables?

ALL_TABLES

What is the newer syntax for dropping one or more columns?

ALTER TABLE table1 DROP (column1, column2);

What is the old syntax for dropping a column from a table?

ALTER TABLE table1 DROP COLUMN column1;

How do you drop unused columns?

ALTER TABLE table1 DROP UNUSED COLUMNS;

How do you put a table in maintenance mode, and then put it back to read-write mode?

ALTER TABLE table1 READ ONLY; put into maintenance mode. ALTER TABLE table1 READ WRITE; returns to normal function

What is the syntax for a column rename?

ALTER TABLE table1 RENAME oldColumn TO newColumn;

How do you mark columns as unused?

ALTER TABLE table1 SET UNUSED (column1,column2) [ONLINE]; or ALTER TABLE table1 SET UNUSED COLUMN column1 [ONLINE];

What is the table level syntax for a FK?

As a table level constraint: CONSTRAINT constraint1 FOREIGN KEY (localFKColumn) REFERENCES targetTable (targetColumn) [ON DELETE CASCADE/ON DELETE SET NULL]

What is the syntax of UPDATE table?

UPDATE table1 SET column1 = value1, column2 = value2 WHERE condition; IT IS IMPORTANT TO INCLUDE A WHERE IF YOU DON'T WANT TO UPDATE ALL ROWS IN A TABLE!!!

What table can you query to get the USER table names?

USER_TABLES

What are the advantages of COMMIT and ROLLBACK statements?

With COMMIT and ROLLBACK statements you can: -Ensure data consistency -Preview data changes before making changes permanent -Group logicaly related operations

What kind of commands can be issued to a table while it is in READ ONLY mode?

DDL that does not alter the data in the table.

What is the syntax of DELETE?

DELETE [FROM] table WHERE condition1. The FROM is optional. The WHERE is also optional, but it would usually be bad to not include it.

How can you define multi-column PK in a table?

You have to use table level constraints, not column level constraints.

What is the syntax of DROP TABLE?

DROP TABLE table1 [PURGE]; PURGE will release the table's space back to tablespace. Only the creator of a table or a user with the DROP ANY TABLE privilege can drop a table.

If you wish to change column names or add a default from a CREATE TABLE newTable AS how would you go about this?

Default is the only thing that can be set here.

How do you recover a table from the recycle bin?

FLASHBACK TABLE

What is the syntax of INSERT INTO?

INSERT INTO table1 (column1, column2) VALUES (val1,val2); This can only insert one value at a time in Oracle.

If you wish to use one subquery to update 2 or more columns in an UPDATE SET how might you do this?

If you can return more than one column from your subquery that corresponds to the fields you wish to update. See example.

What does a plain old ROLLBACK accomplish?

It discards all pending data changes that have not been committed.

What are the restrictions on a LONG?

It is long CHAR type!

What does this SQL accomplish: SELECT * FROM EMPLOYEES WHERE DEPARTMENT_ID=10 FOR UPDATE;

It locks the rows in EMPLOYEES where DEPARTMENT_ID=10 until commit. No one else can manipulate them. Normal selects will be able to see the old version.

What does this SQL accomplish: SELECT * FROM EMPLOYEES WHERE DEPARTMENT_ID=20 FOR UPDATE nowait;

It will try to access the rows in EMPLOYEES where DEPARTMENT_ID=20. If they are locked it will immediately return an error.

What does this SQL accomplish: SELECT * FROM EMPLOYEES WHERE DEPARTMENT_ID=20 FOR UPDATE WAIT 10;

It will try to access the rows in EMPLOYEES where DEPARTMENT_ID=20. If they are locked it will wait 10 seconds to access and then return an error.

What is a statement-level rollback?

Just a failed DML statement will be rolled-back. In case you were doing a DELETE of a table, row by row will be deleted. But if it finds FK dependencies then it will cause an error and ROLLBACK that statement. HOWEVER it will NOT rollback the other pending DML statements, just the failed one. The others can still be rolled back prior to be committing, but the statement level rollback will not do this automatically.

What is the max size of CHAR? What is the max size of VARCHAR2?

Legacy is 4000 for VARCHAR2. It is 2000 for CHAR.

What is the only constraint to be copied when using a CREATE TABLE table2 AS table1; ?

NOT NULL

Which constraint CANNOT be a table level constraint, and doesn't need to be named?

NOT NULL

What is one exception to the UNIQUE constraint?

NULLs. You can have multiple nulls.

What are the range of values that can be recorded in a DATE?

Nearest second between Jan 1, 4712 BC and DEC 31, 9999 AD.

In NUMBER what is P and S? NUMBER(P,S)

Precision: total number of digits. Can be between 1-38 digits long. Scale: number of digits to the right of the decimal point. Can be between -84 and 127

What is the syntax for renaming objects, including tables?

RENAME object1 TO object2;

How do you ROLLBACK to a specific SAVEPOINT?

ROLLBACK TO SAVEPOINT savepointName1;

How do you set a named SAVEPOINT?

SAVEPOINT savepointName1;

Where can you find unused columns?

SELECT * FROM USER_UNUSED_COL_TABS;

What is required for a user to create tables outside of their own schema?

CREATE TABLE privileges.

What is the syntax for adding a column level constraint?

CREATE TABLE table1 ( Column1 NUMBER CONSTRAINT constraint1 PRIMARY KEY);

What is a CLOB?

Character Large Object. Store of to (4GB-1) * (db block size). National character set.

What is a BLOB?

Binary Large Object. (4GB-1)*(db block size)

How can you limit the number of rows being locked for an UPDATE?

By specifying FOR UPDATE OF columnYouWantLocked. See example.

How do you define a composite PK?

CONSTRAINT contraint1 PRIMARY KEY (keyColumn1, keyColumn2), You can have only one PK constraint per table.

What is the syntax of TRUNCATE?

TRUNCATE TABLE table1; Remember this is NOT DML. This is DDL. No rolling back. TRUNCATE empties the table. It is faster than DELETE.

What is the column level syntax for a FK in Oracle?

Within column definition: (dept_id NUMBER CONSTRAINT constraint2 REFERENCES DEPARTMENTS (department_id) [ON DELETE CASCADE/ON DELETE SET NULL])