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


 

А также другие работы, которые могут Вас заинтересовать

64585. Проектирование промышленно-отопительной ТЭЦ 2.01 MB
  В сетевой подогревательной установке ТЭЦ с современными теплофикационными турбинами подогрев сетевой воды может осуществляться по одно-, двух- и трехступенчатой схемам.
64586. Структура современной социальной психологии, основные задачи и проблемы исследований 30.5 KB
  Особенностью социальной психологии является ее широкое включение в жизнь общества. Задачи для исследователей: корректный анализ зарубежной социальной психологии содержания ее теоретических концепций методов и результатов исследований.
64588. Учение Платона об идеях, душе и познании 37.5 KB
  По Платону существуют два мира: мир вещей и мир идей эйдосов. Эйдосы идеальные формы прообразы всех вещей; их мир воплощение гармонии и совершенства. Согласно ей весь наш мир подобен темной пешере в которой люди прикованы цепями к стенам.
64589. ОСТРЫЕ КИШЕЧНЫЕ ИНФЕКЦИИ 88 KB
  В ряде случаев когда ребенок заболевает тяжелой формой дизентерии симптомокомплекс реализуется в нижних отделах и характеризуется острым колитом: тенезмы бескаловый стул с примесью крови...
64592. Историческая основа дипломатической школы Египта 37 KB
  Во время войн на территории северного Египта мы встречаем первый случай предъявления ультиматума вождем гиксосов правителю Фив. Кстати после изгнания гиксосов из Египта между правителями Египта и другими древневосточными государствами установился систематический обмен посольствами.
64593. Критика эмпиризма 16.63 KB
  Причины в опыте не наблюдаются в этом проблема эмпиризма. Проблема непосредственности. Проблема описания факта. Проблема интерпретации факта.