DATABASE ADMINISTRATION

1. DATABASE ADMINISTRATION Pertemuanke-12

2. In business, databases are dynamic. They are constantly in the process of having data inserted, updated, and deleted. • Think how many times the school's student database changes from day to day and year to year. Unless changes are made, the database would quickly lose its usefulness. • Updating, inserting, deleting, and managing data is a Database Administrator’s (DBA’s) job. In this lesson, you will become the DBA of your own schema and learn to manage your database. • In this lesson, you will begin to use data manipulation language (DML) statements to make changes to a database.

3. INSERT • The INSERT statement is used to add new rows to a table. The statement requires three values: • the name of the table • the names of the columns in the table to populate • corresponding values for each column • How can we INSERT the data below to create a new customer in the copy_f_customers table?

4. INSERT INTO copy_f_customers (id, first_name, last_name, address, city, state, zip, phone_number) VALUES (145, 'Katie', 'Hernandez', '92 Chico Way', 'Los Angeles', 'CA', 98008, 8586667641); • This statement explicitly lists each column as it appears in the table. The values for each column are listed in the same order. Note that number values are not enclosed in single quotation marks.

5. Another way to insert values in a table is to implicitly add them by omitting the column names. • One precaution: the values for each column must match exactly the default order in which they appear in the table (as shown in a DESCRIBE statement), and a value must be provided for each column

6. INSERT INTO copy_f_customers VALUES (475, 'Angelina', 'Wright', '7425 Redwood St', 'San Francisco', 'CA', 94162, '4159982010'); • The INSERT statement in this example was written without explicitly naming the columns. For clarity, however, it is best to use the column names in an INSERT clause.

7. Before inserting data into a table, you must check several table details. The DESCRIBE tablename statement will return a description of the table structure in the table summary chart

8. Inserting Rows With Null Values • In example, the SALARY column is defined as a NOT NULL column. An implicit attempt to add values to the table as shown would generate an error INSERT INTO copy_f_staffs ( id, first_name, last_name, birthdate, overtime_rate) VALUES (15, ‘Gregorz’, ‘Polanski’, ‘25-SEP-1988’, 17.50);

9. An implicit insert will automatically insert a null value in columns that allow nulls. To explicitly add a null value to a column that allows nulls, use the NULL keyword in the VALUES list. • To specify empty strings and/or missing dates, use empty single quotation marks (with no spaces between them like this ‘’) for the missing data INSERT INTO copy_f_staffs ( id, first_name, last_name, birthdate, salary, overtime_rate) VALUES (15, ‘Gregorz’, ‘Polanski’, ‘25-SEP-1988’, 224.25, null);

10. Inserting Special Values • Special values such as SYSDATE and USER can be entered in the VALUES list of an INSERT statement. • SYSDATE will put the current date and time in a column. • USER will insert the current session’s username, which is OAE_PUBLIC_USER in Oracle Application Express INSERT INTO copy_employees (employee_id, last_name, email, hire_date, job_id) VALUES (1001, USER, 'Test', SYSDATE, 'IT_PROG');

11. Using A Subquery To Copy Rows • Each INSERT statement we have seen so far adds only one row to the table. But suppose we want to copy 100 rows from one table to another. • We do not want to have to write and execute 100 separate INSERT statements, one after the other. That would be very time-consuming • Fortunately, SQL allows us to use a subquery within an INSERT statement. All the results from the subquery are inserted into the table. So we can copy 100 rows – or 1000 rows – with one multiple-row subquery within the INSERT. • As you would expect, you don’t need a VALUES clause when using a subquery to copy rows because the inserted values will be exactly the values returned by the subquery. • INSERT Statements

12. In the example shown, a new table called SALES_REPS is being populated with copies of some of the rows and columns from the EMPLOYEES table. • The WHERE clause is selecting those employees that have job IDs like '%REP%'. INSERT INTO sales_reps(id, name, salary, commission_pct) SELECT employee_id, last_name, salary, commission_pct FROM employees WHERE job_id LIKE '%REP%';

13. If we want to copy all the data – all rows and all columns – the syntax is even simpler. • To select all rows from the EMPLOYEES table and insert them into the SALES_REPS table, the statement would be written as shown: INSERT INTO sales_reps SELECT * FROM employees;

14. UPDATE • The UPDATE statement is used to modify existing rows in a table. It requires four values: • the name of the table • the name of the column(s) whose values will be modified • a new value for each of the column(s) being modified • a condition that identifies which rows in the table will be modified. • The new value for a column can be the result of a single-row subquery.

15. The example shown uses an UPDATE statement to change the phone number of one customer in the Global Fast Foods database UPDATE copy_f_customers SET phone_number='4475582344' WHERE id=123; • Which rows would be updated in the following transaction? UPDATE copy_f_customers SET phone_number='9876543210';

16. Updating a Column with a value from a Subquery • We can use the result of a single-row subquery to provide the new value for an updated column UPDATE copy_f_staffs SET salary = (SELECT salary FROM copy_f_staffs WHERE id = 9) WHERE id = 12

17. DELETE • The DELETE statement is used to remove existing rows in a table. The statement requires two values: • the name of the table • the condition that identifies the rows to be deleted DELETE FROM copy_f_customers WHERE ID= 123;

18. Subquery DELETE DELETE FROM employees WHERE department_id = (SELECT department_id FROM departments WHERE department_name = “Shipping”);

19. Transactions • Transactions are a fundamental concept of all database systems. Transactions allow users to make changes to data and then decide whether to save or discard the work. • Database transactions bundle multiple steps into one logical unit of work. A transaction consists of one of the following: • DML statements which constitute one consistent change to the data. The DML statements include INSERT, UPDATE, DELETE, and MERGE • One DDL statement such as CREATE, ALTER, DROP, RENAME, or TRUNCATE • One DCL statement such as GRANT or REVOKE

20. Example • A bank database contains balances for various customer accounts, as well as total deposit balances for other branches. Suppose a customer wants to withdraw and transfer money from his account and deposit it into another customer’s account at a different branch • There are several separate steps involved to accomplish this rather simple operation. Both bank branches want to be assured that either all steps in the transaction happen, or none of them happen, and if the system crashes, the transaction is not left partially complete. Grouping the withdrawal and deposit steps into one transaction provides this guarantee. • A transaction either happens completely or not at all

21. Controlling Transactions • Transactions are controlled using the following statements: • COMMIT: Represents the point in time where the user has made all the changes he wants to have logically grouped together, and because no mistakes have been made, the user is ready to save the work. When a COMMIT statement is issued, the current transaction ends making all pending changes permanent.

22. ROLLBACK: Enables the user to discard changes made to the database. When a ROLLBACK statement is issued, all pending changes are discarded. • SAVEPOINT: Creates a marker in a transaction, which divides the transaction into smaller pieces • ROLLBACK TO SAVEPOINT: Allows the user to roll back the current transaction to a specified savepoint. If an error was made, the user can issue a ROLLBACK TO SAVEPOINT statement discarding only those changes made after the SAVEPOINT was established.

23. In the example shown, the user has issued an UPDATE statement and immediately created SAVEPOINT one. UPDATE d_cds SET cd_number = 96 WHERE title = 'Graduation Songbook'; SAVEPOINT one;

24. After an INSERT statement and an UPDATE statement, the user realized that a WHERE clause was not included in the last UPDATE. To remedy the mistake, the user issued a ROLLBACK TO SAVEPOINT one. The data is now restored to its state at SAVEPOINT one UPDATE d_cds SET cd_number = 96 WHERE title = 'Graduation Songbook'; SAVEPOINT one; INSERT INTO d_cds(cd_number, title, producer, year) VALUES(100, 'Go For It', 'The Music Man', 2004) ); UPDATE d_cds SET cd_number = 101; ROLLBACK TO SAVEPOINT one; COMMIT;

25. When Does a Transaction Start or End? • A transaction begins with the first DML (INSERT, • UPDATE, DELETE or MERGE) statement. • A transaction ends when one of the following occurs: • A COMMIT or ROLLBACK statement is issued • A DDL(CREATE, ALTER, DROP, RENAME or TRUNCATE) statement is issued • A DCL(GRANT or REVOKE) statement is issued • The user exits iSQL*Plus or SQL*Plus • A machine fails or the system crashes

26. After one transaction ends, the next executable SQL statement automatically starts the next transaction. A DDL statement or a DCL statement is automatically committed and therefore implicitly ends a transaction. Every data change made during a transaction is temporary until the transaction is committed.

27. When a DML statement is committed, the change made to the database becomes visible to anyone executing a SELECT statement. • If the transaction is rolled back, the changes are undone: • The original, older version of the data in the undo segment is written back to the table. • All users see the database as it existed before the transaction began.

28. COMMIT, ROLLBACK, and SAVEPOINT • In the transaction shown in the graphic, a DELETE statement was issued and then SAVEPOINT A was established. This SAVEPOINT acts like a marker that will allow the user to rollback any subsequent changes made in the data back to the state of the data as it existed at this point.

29. COMMIT, ROLLBACK, and SAVEPOINT • In the example, following SAVEPOINT A, the user issues an INSERT and UPDATE statements, then establishes another rollback marker at SAVEPOINT B • If for some reason the user does not want these INSERT and/or UPDATE statements to occur, the user can issue a ROLLBACK TO SAVEPOINT A statement. This will rollback to the state of the data as it was at the SAVEPOINT A marker.

30. COMMIT, ROLLBACK, and SAVEPOINT • Adding other SAVEPOINTS creates additional markers for rollback points. If a user issues a ROLLBACK without a ROLLBACK TO SAVEPOINT statement, the entire transaction is ended and all pending data changes are discarded.

31. Automatic commit of data changes occurs under the following circumstances: • a DDL statement is issued • a DCL statement is issued • a normal exit from iSQL*Plus or SQL*Plus without explicitly issuing COMMIT or ROLLBACK statements • When the Autocommit box is checked in Oracle Application Express

32. Automatic rollback occurs under an abnormal termination of either iSQL*Plus or SQL*Plus, or when a system failure occurs. • This prevents any errors in the data from causing unwanted changes to the underlying tables. The integrity of the data is therefore protected.