Lab 11

From bd_en
Jump to: navigation, search

Substitution variables in SQL*Plus

Substitution variables can be used to store constants, conditions required in the WHERE clause, field names used in the ORDER BY cluase or in the SELECT list.

Substitution variables are preceded by symbol & and when executing the query, SQLPlus will ask the user for the values to be used for the substitution variables.

Example 1:

SELECT *
    FROM students
    WHERE year=&year AND first_name LIKE '&firstName'

Example 2:

SELECT fname, &field_to_show
    FROM students
    WHERE &condition2
    ORDER BY &field_to_sort;

Execute the queries above several times, using various values for the substitution variables. Verify the results.

Substitution variables can be declared and instantiated in advance, case in which SQLPlus will not ask for the value when executing the query:

DEFINE field=lname
SELECT fname, &field
    FROM students
    ORDER BY &field;

The variable is stored during the current session until it is eliminated with the UNDEFINE command or until the session is closed.

UNDEFINE field

DEFINE and UNDEFINE commands are specific to the SQL*Plus tool and not to the SQL language.

For more about substitution variables read https://blogs.oracle.com/opal/entry/sqlplus_101_substitution_varia

DML statements - Modifying data

So far,you worked only with queries - SELECT phrases which search for data in the database. This section is dedicated to DML commands that modify data.

Inserting records

Inserting new records in the database is made with the INSERT statement which has two forms:

INSERT INTO table_name [ (column_list) ]
      VALUES (value_list)
INSERT INTO courses VALUES(30, 'Mach. Learn.', 3, 1, 5);

INSERT INTO courses (id_course, course_title) VALUES(31, 'NoSQL');

INSERT INTO table_name [ (column_list) ]
   select_phrase	
INSERT INTO grades
SELECT id_stud, 30, 10, SYSDATE
  FROM students
  WHERE year=3;

SEQUENCES

Many times, we need to create synthetic attributes to play as keys in the database, storing unique values in the table.Sequences allow us to automatically generate integer unique values when inserting records.

The syntax for creating sequences:

CREATE SEQUENCE sequence_name
  [INCREMENT BY n]               -- step
  [START WITH n1]                -- starting value
  [MAXVALUE n_max | NOMAXVALUE]  -- stop value
  [MINVALUE n_min | NOMINVALUE]  -- minimum value (lists can consist of descending values)
  [CYCLE | NOCYCLE]              -- if the series is restarted when reaching min/max

The NEXTVAL command generates the next number in the series and CURRVAL returns the current value. NEXTVAL must be executed before the list contains any number.

Example:

CREATE SEQUENCE s1
  INCREMENT BY 2
  START WITH 10
  MAXVALUE 15;

SELECT s1.NEXTVAL FROM DUAL; --execute it repeatedly; when does it raise errors?

For more about sequences, read https://docs.oracle.com/cd/B12037_01/server.101/b10759/statements_6014.htm

Exercises

How can be a sequence used for inserting records? Answer by creating a sequence to help you inserting new courses with unique IDs, with consecutive entries starting with value 1. Insert 3 new courses with the id generated by sequence.

Updating records

UPDATE table_name [alias]
  SET column_name1 = value1
     [, column_name2 = value2...]
      [WHERE condition]
UPDATE students

SET scholarship=scholarship*0.15 WHERE scholarship<300;

UPDATE table_name [alias]
  SET (column_name1 [,column_name2]...) = (subquery)
    [WHERE condition]

Exercises

  1. Update the scholarship for the students who have at least one grade equal to 10. These students will earn 500RON.

Deleting records

DELETE FROM table_name 
  [WHERE condition]
DELETE FROM grades WHERE value < 5;

Exercises

Delete all the students who haven't received any grade.

DDL statements - creating/modifying data structures

These commands aim at creating or deleting tables (or other types of objects), and also at modifying the structure of the existing tables (objects). They form the DDL component (Data Definition Language) of the SQL language.

CREATE TABLE AS

The commands for creating tables were explained at the course, detailing the constraints that can be enforced on the data. Here we cover only the CREATE command using subqueries.

CREATE TABLE table_name[(col1, col2,...)] [AS] select_phrase;

The command is similar to the one used to create the view, the difference consisting that when creating a table it is filled with data and the links to the base tables is not preserved.

Exercises

Execute the ROLLBACK command (simply type ROLLBACK). Than create a table to store the first_name, last_name, scholarship and the average grade for each student.

ALTER TABLE

The structure of the existing tables can be modified, even if the table contains data.The changes may imply adding new columns, deleting existing columns, modifying the definition of a column (the type and the constraints), renaming a column, renaming the table, adding or deleting constraints (candidate keys, primary keys, foreign keys, NOT NULL constraints, CHECK).

The commands are the following:

ALTER TABLE table_name ADD (col1 definition_col1 [,col2 definition_col2....])
ALTER TABLE courses ADD (abreviation CHAR(2) NULL, 
                 description VARCHAR(40) DEFAULT 'mandatory course');
ALTER TABLE table_name DROP COLUMN col
ALTER TABLE courses DROP COLUMN description;
ALTER TABLE table_name MODIFY (col1 definitie_col1 [,col2 definitie_col2....])
ALTER TABLE instructors MODIFY (fname VARCHAR(20), 
                            lname VARCHAR(20));
ALTER TABLE table_name RENAME COLUMN old_col TO new_col
ALTER TABLE grades RENAME COLUMN value TO grade;
ALTER TABLE old_table_name RENAME TO new_table_name
ALTER TABLE instructors RENAME TO teachers;
ALTER TABLE table_name ADD CONSTRAINT constraint_name constraint_definition;
ALTER TABLE students ADD CONSTRAINT 
     pk_studs PRIMARY KEY (id_stud);
ALTER TABLE grades ADD CONSTRAINT 
     fk_studs FOREIGN KEY (id_stud) 
           REFERENCES students(id_stud) ON DELETE CASCADE;
ALTER TABLE table_name DROP CONSTRAINT constraint_name;
ALTER TABLE grades DROP CONSTRAINT fk_studs;

Exercices

Execute again the script here: http://profs.info.uaic.ro/~pmihaela/faculty.sql

  1. Add the primary key constraints for tables Students, Instructors, Courses.
  2. Add the referential constraints for tables Grades and Teaching. When deleting an instroctor in table Instructors, the id of the deleted instructor will be set to NULL. When deleting a course in table Courses, in table Teaching the record referencing the deleted course will also be deleted. Write some DELETE statements for the referenced tables and study the behavior.
  3. Impose the constraint that a student will not have more than a grade to each course.
  4. Impose the constraint that the grade values will be in range 1-10.

Transactions

A transaction represents a group of commands for modifying data (DML statements) that must be executed together, in order to guarantee data consistency. Failing to execute one statement must determines the system to restore the initial state from the beginning of the transaction.

A transaction begins with the first DML statement and ends when a COMMIT or ROLLBACK command is met, or when a DDL command is launched, when closing the session or when a system error appears.

The ROLLBACK command ends the transaction restoring the initial state. A system failure also results a ROLLBACK, restoring the state of the database at the moment before starting the transaction; this is how data integrity is ensured. The rest of the situations which end a transaction make the results of the DML permanent in the database without the possibility to restore intermediate states.

Once a transaction is over, another one begins.

During this lab, you executed the ROLLBACK command before experimenting with CREATE TABLE, ie before launching the first DDL statement. The result was the restoration of the state of the database from the beginning of the session. If the ROLLBACK command would not have been executed, the changes made to the data would have become permanent in the moment the DDL command had been executed (as previously specified, any DDL command ends the current transaction marking the previous changes in data as permanent).

During a transaction, several markers can be placed helping us to restore intermediate states. Adding an indicator is made with the command
SAVEPOINT indicator_name
and restoring the state of the database for that moment is made with
ROLLBACK TO SAVEPOINT indicator_name
.

Any DDL command is considered to be a transaction.

Exercises

  1. Ensure that you start a new transaction.
  2. Delete all the records in table Instructors.
  3. Insert an instructor.
  4. Mark its current state as 's1'.
  5. Change the name of the inserted instructor.
  6. Query the data.
  7. Restore s1.
  8. Query the data.
  9. Restore the state at the beginning of the transaction.