ID: 6811

Название работы: Data manipulation in SQL Oracle

Категория: Лабораторная работа

Предметная область: Информатика, кибернетика и программирование

Описание: Data manipulation in SQL Oracle Purpose of the lab To study SQL Oracle possibilities in inserting, updating and deleting rows in a tables. To acquire practical skills in inserting, updating and deleting rows in a tables by using SQ...

Язык: Английский

Дата добавления: 2013-01-08

Размер файла: 110 KB

Работу скачали: 10 чел.

Data manipulation in SQL Oracle

1.  Purpose of the lab

•  To study SQL Oracle possibilities in inserting, updating and deleting rows in a tables.
•  To acquire practical skills in inserting, updating and deleting rows in a tables by using SQL*Plus.

1.  Theoretical backgrounds

There are three SQL statements that enable you to manipulate the data within a database's table. These statements are the following:

•  The INSERT statement – allows to add new rows of data into a tables.
•  The UPDATE statement – allows to change column values in existing rows of a table.
•  The DELETE statement – allows to remove rows from a table.
  1.  Inserting rows in a table
     1.  Syntax

1.  Purpose

To add rows to a table.

1.  Prerequisites

For you to insert rows into a table, the table must be in your own schema or you must have INSERT privilege on the table.

If you have the INSERT ANY TABLE system privilege, you can also insert rows into any table.

1.  Keywords and Parameters 

schema	is the schema containing the table. If you omit schema, Oracle assumes the table or view is in your own schema.
table	is the name of the table into which rows are to be inserted.
column	is a column of the table. In the inserted row, each column in this list is assigned a value from the values_clause or the subquery.   If you omit one of the table's columns from this list, the column's value for the inserted row is the column's default value as specified when the table was created. For more information on default column values, see Lab1. If any of these columns has a NOT NULL constraint and it does not have a default value, then Oracle returns an error indicating that the constraint has been violated and rolls back the INSERT statement. If you omit the column list altogether, the values_clause or query must specify values for all columns in the table.
values_clause	specifies a row of values to be inserted into the table or view. You must specify a value in the values_clause for each column in the column list. If you omit the column list, then the values_clause must provide values for every column in the table.
subquery	is a subquery that returns rows that are inserted into the table. If the subquery selects no rows, Oracle inserts no rows into the table. When specified with VALUES, the subquery returns values to be inserted into one row. When specified without VALUES, the subquery can return values to be inserted into more than one row. The subquery can refer to any table, including the target table of the INSERT statement. The select list of this subquery must have the same number of columns as the column list of the INSERT statement. If you omit the column list, then the subquery must values for all columns in a table.
t_alias	provides a correlation name for the table to be referenced elsewhere in the statement.

1.  Examples

VALUES Examples

The following statement inserts a row into the FACULTY table:

INSERT INTO FACULTY   

  VALUES (1, 'informatics', 'Sidorov', '5', 93517);

The following statement inserts a row with six columns into the TEACHER table. One of these columns is assigned NULL: Values of two columns (Tel and Commission) are not defined. So Tel column the value NULL is assigned and Commission column takes the value 0 (default value):

INSERT INTO TEACHER (TchNo, DepNo, name, post, hiredate, salary)

  VALUES (15, 7, 'Ivanov', NULL, NULL, TO_DATE('25.07.2001', 'DD.MM.YYYY'), 350.00);

Subquery Example

The following statement copies professors whose Commission exceeds 25% of salary into the BONUS table:

INSERT INTO bonus

  SELECT *

  FROM TEACHER

  WHERE Commission > 0.25 * salary AND post 'proessor';

1.  Updating rows in a table
   1.  Syntax

1.  Purpose

To change existing values in a table.

1.  Prerequisites 

For you to update values in a table, the table must be in your own schema or you must have UPDATE privilege on the table.

The UPDATE ANY TABLE system privilege also allows you to update values in any table or any view's base table.

1.  Keywords and Parameters 

schema	is the schema containing the table. If you omit schema, Oracle assumes the table is in your own schema.
table	is the name of the table to be updated.
set_clause	column	is the name of a column of the table that is to be updated. If you omit a column of the table from the set_clause, that column's value remains unchanged.
		subquery	is a subquery that returns exactly one row for each row updated. If you specify only one column in the set_clause, the subquery can return only one value. If you specify multiple columns in the set_clause, the subquery must return as many values as you have specified columns. If the subquery returns no rows, then the column is assigned a null.
		expr	is the new value assigned to the corresponding column.
where_clause	restricts the rows updated to those for which the specified condition is TRUE. If you omit this clause, Oracle updates all rows in the table or view. See the syntax description of conditions in Lab2 . The where_clause determines the rows in which values are updated. If you do not specify the where_clause, all rows are updated. For each row that satisfies the where_clause, the columns to the left of the equals (=) operator in the set_clause are set to the values of the corresponding expressions on the right. The expressions are evaluated as the row is updated.

1.  Examples

Simple Examples

The following statement gives null Commission to all teaches with the post assistant:

UPDATE TEACHER

   SET Commission = NULL

   WHERE post = 'assistant';

The following statement moves Jones Department 20 with a 100 salary raise (assuming there is only one Jones):

UPDATE TEACHER

   SET DepNo =20, salary = salary + 100

   WHERE name = 'jones';

Complex Example

This example shows the following syntactic constructs of the UPDATE statement:

•  Both forms of the set_clause together in a single statement .
•  A correlated subquery .
•  A where_clause to limit the updated rows .

UPDATE TEACHER a

 SET DepNo =

 (SELECT DepNo

  FROM deptartment

  WHERE name = 'data bases and information systems'),

    (salary, Commission) =

  (SELECT 1.1*AVG(salary), 1.5*AVG(Commission)

   FROM TEACHER b

   WHERE a.DepNo = b.DepNo)

 WHERE DepNo IN

 (SELECT DepNo

  FROM DEPARTMENT

  WHERE FacNo = 1);

The above UPDATE statement performs the following operations:

•  Updates only those teachers that  work in faculty with DepNo = 1
•  Sets DepNo for these teaches to the DepNo of 'Data bases and information systems'
•  Sets each teachers’s salary to 1.1 times the average salary of their department.
•  Sets each teachers's Commission to 1.5 times the average Commission of their department
  1.  Deleting rows in a table 
     1.  Syntax

1.  Purpose 

 To remove rows from a table.

1.  Prerequisites 

For you to delete rows from a table, the table must be in your own schema or you must have DELETE privilege on the table.

The DELETE ANY TABLE system privilege also allows you to delete rows from any table.

1.  Keywords and Parameters 

schema	is the schema containing the table. If you omit schema, Oracle assumes the table or view is in your own schema.
table	is the name of a table from which the rows are to be deleted.
where_clause	deletes only rows that satisfy the condition. The condition can reference the table and can contain a subquery..   If you omit the where_clause, Oracle deletes all rows of the table or view.
t_alias	provides a correlation name for the table to be referenced elsewhere in the statement. Table aliases are generally used in DELETE statements with correlated queries.

1.  Examples

Basic Examples

The following statement deletes all rows from a table named DEPARTMENT.

DELETE FROM DEPARTMENT;

The following statement deletes from the TEACHER table all professors who have less than 100 Commission:

DELETE FROM TEACHER

   WHERE post = 'professor' AND Commission  < 100;

1.  Lab tasks
   1.  Inserting rows in tables

Insert the following rows in tables.

FACULTY	FacNo	Name	Dean	Building	Fund
	1	informatics	Sidorov	5	57398.00
	2	economy	Petrov	3	NULL
	3	linguistics	Popov	4	NULL

DEPARTMENT	DepNo	FacNo	Name	Head	Building	Fund
	1	1	SE	Sidorov	5	14378.00
	2	1	CAD	Perov	5	15000.00
	3	1	DBMS	Ivanov	4	22000.00
	4	2	Accounts	John	3	NULL

TEACHER	TchNo	DepNo	Name	Post	Tel	Hiredate	Salary	Commission
	1	1	Andrew	assistant	2281319	01.02.2001	250	80
	2	1	John	professor	2281550	01.07.2001	400	150
	3	2	Bill	assistant	NULL	17.11.2002	240	80
	4	2	Albert	assistant	NULL	11.11.2001	260	100

SGROUP	GrpNo	DepNo	Course	Num	Quantity	Curator	Rating
	1	1	1	101	33	4	20
	2	1	1	102	35	4	22
	3	3	2	205	20	1	15
	4	3	3	305	25	NULL	40
	5	3	4	405	25	2	37

SUBJECT	SbjNo	Name
	1	pascal
	2	C
	3	OS
	4	inernet
	5	dbms

ROOM	RomNo	Num	Seate	Floor	Building
	1	101	20	1	5
	2	316	150	3	5
	3	201	150	2	2
	4	202	30	2	5

LECTURE	TchNo	GrpNo	SbjNo	RomNo	Type	Day	Week	Lesson
	1	1	1	1	lecture	mon	1	1
	1	2	2	1	lab	mon	1	1
	2	3	3	1	lecture	tue	1	3
	2	4	4	2	practice	wed	1	3
	4	4	5	2	practice	thu	2	4
	4	4	5	3	lab	fri	2	1

Insert in the FACULTY table information about your faculty/ Because of at that time information about teachers dies not exists, use NULL as a value of foreign key that references to the dean of the faculty.

Insert into DEPARTMENT table information about your department and all departments of teachers that teach you. Because of at that time information about teachers dies not exists, use NULL as a value of foreign key that references to the head of the department

Insert into TEACHER table information about all teaches that teach you, including dean of the faculty and head of the departments. . Teacher subordination is set in such a way:

- Dean of the faculty does not subordinate anybody

- Chief of the department subordinates to the dean

- Teachers that has lectures subordinates to the head of the department

- Teachers that has labs subordinates to the teacher that has lectures in the same subject.

Update FACULTY table by changing NULL in DeanFK column to the references to corresponding teacher

Update DEPARTMENT table by changing NULL in HeadFK column to the references to corresponding teacher.

Insert into SGROUP table information about your group.

Insert into SUBJECT table all disciplines that you study.

Insert into ROOM table information about all room where you have lectures.

Insert into LECTURE table information about all lectures that you have.

1.  Updating rows in tables

Do the following changes in tables data:

•  In economics FACULTY set dean = “Bob” and fund = 3467.00
•  In DEPARTMENT with DepPK =  3 change head to Frank and Building to ‘3’
•  In TEACHER table set Commission of all assistants to 25% of their salary.
•  In SGROUP table set rating = 0 to all groups of the first course.
•  In SUBJECT table change name of the subject “internet” to “html”

1.  Control questions

Please, give answers to the following questions:

1.  Is it necessary that ordering of column names in column list in INSERT statement corresponds to the columns ordering in the table definition?
2.  What value is assigned to the column if it is omitted in column list in INSERT statement?
3.  What does happen if column is absent in column list in INSERT statement and it has NOT NULL constraint and does not have default value?
4.  What does absence of column list in INSERT statement  mean?
5.  Is it possible to define NULL value in values clause?
6.  What is a purpose to use table alias?
7.  What does absence of where clause in UPDATE statement mean?
8.  What does absence of where clause in DELETE statement mean?

PAGE  7