DML - Data Manipulation Language Flashcards
Describe the INSERT statement
Used to add one or more rows to a table or view and can also get these rows to add from elsewhere in the database
INSERT INTO CRUISES (cruise_id, cruise_type_id, cruise_name, captain_id, start_date, end_date, status) VALUES (1,1,’day at sea’, 101, ‘02-JAN-10’, ‘09-JAN-10’, ‘sched’);
No particular order for column names and they are not mandatory as long as all required column values (NOT NULL, PRIMARY KEY) are provided but in that case values must line up with the order of the columns in the table structure as verified by DESC
INSERT INTO TEST_SCORES VALUES (100,85);
The advantage of listing column names is that if you subsequently add columns then the SQL statement will still execute as intended
Series of expressions for values must be in the same order as the columns listed and data types compatible although implicit conversions will be performed whenever possible, you should rely on explicit data type conversions with functions
Steps performed at execution:
- Confirm existence / validity of table
- Confirm existence /validity of columns
- Evaluate expression for values
- Data types of expressions compared with data types of associated columns and evaluated for compatibility
- Values of expressions will be applied to any constraints in the table
The violation of a constraint is a runtime error
You can use a sequence object to dispense individual values that we can use as PRIMARY KEY values for INSERT statements
Assume the table CRUISES has a PRIMARY KEY CONSTRAINT on column cruise_id and the following CONSTRAINT on its cruise_name column
CONSTRAINT Cruise_name_ck CHECK (Cruise_name IN (
‘Hawaii’,’Bahamas’, ‘Mexico’))
Assume that you have created a sequence SEQ_CRUISE_ID
When you execute the following INSERT
INSERT INTO CRUISES(cruise_id, cruise_name)
VALUES(SEQ_CRUISE_ID.NEXTVAL, ‘Hawaii’);
The cruise_id will be valid from the sequence but the cruise_name should also be valid otherwise we get a runtime error for check constraint violated
Describe different types of SQL statements
- DDL - Data Definition Language - statements used to build database objects, add comments associated with objects, issue privileges to users and initiate performance analysis on objects
CREATE - to create objects
ALTER - modify structure, name and other attributes of
existing objects
DROP - remove an object
RENAME - change name of an existing object
TRUNCATE - remove all rows of data from existing table
GRANT - provide privileges / rights to users to perform
tasks
REVOKE - remove privileges
FLASHBACK - restore an earlier version of table /
database
PURGE - remove objects from recycle bin
COMMENT - add comments to data dictionary for db
objects - DML - Data Manipulation Language
SELECT - display data or rows
INSERT - add rows
UPDATE - modify rows
DELETE - delete rows
MERGE - perform combination of insert / update or
or delete statements in a single transaction - TCL - Transaction Control Language - save / cancel changes made to a database with DML within a given session
COMMIT - save and finalize set of DML modifications in
current session
ROLLBACK - restore database to an earlier state by
undoing DML modifications in current
session
SAVEPOINT - makes a point in a session to which
future rollbacks may be issued as
opposed to an all or nothing rollback
Describe the UPDATE statement
Update modifies rows one table at a time. It can modify multiple columns of data, change selected rows or all rows in a table. Also works with views ultimately changing data in underlying tables. The WHERE clause is optional.
PRIMARY KEY constraints help update statements identify single rows
UPDATE CRUISES
SET cruise_name = ‘Bahamas’,
Start_date = ‘01-DEC-11’
WHERE cruise_id = 1;
The SET clause doesn’t need to reference all required columns or refer to columns in any particular order and only mentioned column values are changed. If update violates a constraint a runtime error will occur and the entire update operation is cancelled.
The update statement can use expressions to assign values and these can be literals, column references, mathematical expressions or SQL functions
UPDATE COMPENSATION
SET salary = salary * 1.03,
Last_changed_date = SYSDATE
WHERE employee_number = 83;
Assume a table projects has a check constraint on column cost
CONSTRAINT cost_ck CHECK (cost < 1000000)
Then for certain large values of cost in the table the following statement will result in a check constraint violation runtime error rejecting the entire update
UPDATE PROJECTS SET cost = cost * 1.02;
Describe the DELETE statement
Rows are identified by the WHERE clause and if omitted all rows are deleted
DELETE FROM project_listing WHERE construction_id = 12;
Describe the COMMIT statement
COMMIT is used to save changes made with insert, update or delete so that these changes become permanent and cannot be undone with ROLLBACK which only works before a COMMIT statement.
A series of SQL statements is treated as a single transaction or unit and the entire unit is not made permanent or the transaction not completed without a commit event.
2 kinds of commit events, explicit event with COMMIT statement execution or implicit commit with certain database events.
Syntax: COMMIT; or COMMIT WORK; /work is an optional keyword
Implicit commit events:
Immediately before or after any DDL statement (CREATE, ALTER, DROP, GRANT, REVOKE)
Exit from oracle utilities and tools sqlplus developer etc
Describe the behaviour of COMMIT with multiple users logged in
A set of database tables will be owned by a user for the benefit of other users
Other users must be granted privileges to get access to these tables for reading, modifying, deleting data
Example: assume that one user account owns a schema with several tables. All users have been granted SELECT privileges but only the owner can change the data
If the owner performs a series of DML statements, these changes will be visible to the owner but not to any of the other users with SELECT privileges until a commit event occurs
Only after a commit event do the changes Clemons permanent and are visible to all users. Before a commit event the user is modifying data effectively in draft mode
Describe the ROLLBACK statement
Like an undo function in software systems
Any changes made before a ROLLBACK are removed but only until the last commit, committed changes cannot be rolled back. Other users changes are unaffected, only those changes are rolled back that were issued by the user performing the rollback
Am implicit rollback occurs when a program terminates abnormally, uncommitted changes will not be made permanent
Describe the SAVEPOINT statement
Supports ROLLBACK and COMMIT statements
SAVEPOINT establishes demarcation points within a transaction to empower any COMMIT or ROLLBACK to save or undo changes until a particular named savepoint. Provides a finer detail at which transactions can be controlled.
Without SAVEPOINT, COMMIT and ROLLBACK operate on an all or nothing basis.
COMMIT;
UPDATE Ships SET home_port_id = 21 WHERE ship_id = 12;
SAVEPOINT SP_1;
UPDATE Ships SET home_port_id = 22 WHERE ship_id = 12;
ROLLBACK WORK TO SP_1;
COMMIT;
Home_port_id is first set to 21, then to 22 and then back to 21
Rules for using SAVEPOINT
All SAVEPOINT statements must include a name which becomes associated with a system change number or SCN
Do not duplicate SAVEPOINT names, if you do the new one will override the earlier on erasing it
Once a Commit event occurs all existing SAVEPOINTs are erased from memory
COMMIT; UPDATE Ships SET home_port_id = 21 WHERE ship_id = 12; SAVEPOINT MARK_01; COMMIT; ROLLBACK TO MARK_01;
The second commit saves everything so there is nothing to rollback to and all SAVEPOINTs have been deleted so MARK_01 doesn’t exist, will generate error. Any uncommitted changes at such a point could still be committed or rolled back.
If a single ROLLBACK is executed without a reference to a SAVEPOINT any changes since the time of the last commit will be undone.
SAVEPOINT is useful when managing a large series of transactions requiring validation, so if they fail they can be corrected.
