(Previous) < 1 2 3 4 5 > (Next)

Manipulating Data

Describe each DML statement

INSERT, UPDATE, DELETE, (INSERT, SET TRANSACTION, EXPLAIN PLAN)

Insert a row into a table

 insert into parientes(miembro,nombre) values (12,'Alexia');
 insert into relatives(member,name) values(12,'Alexia');

Update a row in a table

 update parientes set apellido='Rubio' where miembro=12; 
 update relatives set lastname='Rubio' where member=12;

Delete a row in a table

 delete parientes where miembro=12; 

Control Transactions

commit and rollback savepoint . When a savepoint is defined within a statement, it can be rolledback up to that particular point. Example:

 insert into nomina(sueldo) values(1000000);
 savepoint excelente;
 insert into nomina(sueldo) values(100000);
 savepoint bueno;
 insert into nomina(sueldo) values(10000);
 savepoint salario_mexicano;
 commit;
              

Even though the commit was already issued, we can still rollaback up to a savepoint location with:

 ROLLBACK to bueno 

That would rollback the insert with a value of 10000

Creating and Managing Tables

Describe the main database objects

INDEX, SEQUENCE, TABLE ,VIEW,CLUSTER, PROCEDURE,SYNONYM,TRIGGER, CONSUMER GROUP,RESOURCE PLAN ,FUNCTION,INDEXTYPE, JAVA CLASS, JAVA RESOURCE, LIBRARY, OPERATOR, PACKAGE, PACKAGE BODY, QUEUE.

Create Tables

create table alumnos (
 nombre varchar2(25), 
 apellido varchar2(25),
 matricula integer primary key,
 salon number );   

 create tables pupils (
 name varchar2(25),
 lastname varchar2(25),
 id integers primary key,
 classroom number);
 

Describe the Datatypes that can be used when specifying column definitions

1. char(n): A fixed length character string,will take up 200 bytes regardless of how long the string actually is. The maximum length of char is 2000 bytes.
2. varchar(n): A variable length character string, that will take up space accordingly
3. Other Datatype: CHAR,NCHAR,VARCHAR2, NVARCHAR,DATE,NUMBER,LONG,RAW,LONG RAW, MISLABEL, BLOB,CLOB,NCLOB,BFILE,ROWID y UROWID

Alter Table Definitions

To drop a column :

                 alter table mifamilia drop column no_sirve;
                 alter table myfamily drop column irrelevant;

To add a column :

                 alter table mifamilia add (telefono number(15));
		 alter table myfamily add (telephone number(15));

To rename a column (two steps):

                 alter table mifamilia set unused parientes;(This deactivates the column)
		 alter table mifamilia add (familiares varchar2(40));(Created a new column)

When the column is marked unused, the information is not deleted it still exists. So you can then copy the data from the unused column (parientes) to the new column (familiares).

To change variable type in column :

 
                alter table mifamily modify(nombre varchar2(43));

		NOTE: To modify the Datatype the column must be all null.

Alter storage parameter for the table:

                ALTER TABLE branch MODIFY PARTITION branch_mex
		STORAGE (MAXEXTENTS 75) LOGGING;


		ALTER TABLE emp
		PCTFREE 30 PCTUSED 60;

Drop,rename and truncate tables.

To Drop a table:

              drop table esta_no_sirve;
	      drop table useless;

To Rename a table:

              alter table mal_nombre rename to buen_nombre;
	      alter table bad_name rename to good_name;

or the rename command can also be used: rename mal_nombre to buen_nombre

To Truncate:

              truncate table se_perdera_todo;
	      truncate table all_is_lost;

NOTE: A truncate statement cannot be rollbacked. Truncate is used to RESET the STORAGE parameters to the values when the table or cluster was created. Deleting rows with the TRUNCATE statement can be more efficient than dropping or re-creating a table. Dropping and re-creating a table invalidates the tables dependent objects , the priviliges on the table have to be regranted , and also requires re-creating the tables indexes, integrity constraints, triggers, and of course respecify its storage parameteres. TRUNCATING has none of these efffcts. thus its usefullness.

Including Constraints

Describe constraints

A constraint is used to implement certain rules on columns in the table that will be created, these are normally business or operational rule.

Describe constraints

A constraint is used to implement certain rules on columns in the table that will be created, these are normally business or operational rule.

Create and maintain constriants

1. not null : Requires a value for this column.
2. unique : Two rows cant have the same value in this column ( side effect, Oracle creates an index on this column )
3. primary key :Same as unique except that no row can have a null value, also it is possible to refer from other tables to this key.
4. check : Limit the range of the column. Example: rango integer check ( rango > 6 and rango <= 9)
5. references : This column can only contain values present in another tables primary key . Example:usuario not null references empleado

Constraints can also apply to multiple columns:

                     create table los_mejores (
		     identificacion integer primary key,
		     nombre_titulo  varchar2(45),
		     edad           integer,
		     unique(edad,identificacion) 
		     ); 

		     create table los_mejores (
		     identificacion integer primary key,
		     nombre_titulo  varchar2(45),
		     edad           integer,
		     check (edad <=35 or identificacion <=1000 )
		     ); 

In case you want a more friendly message, and not a cryptic constraint violated you can name the constraint like so:

                     create table los_mejores (
		     identificacion integer primary key,
		     nombre_titulo  varchar2(45),
		     edad           integer,
		     constraint los_primeros  
		     check (edad <=35 or identificacion <=1000 )
		     ); 

If the table already existed:

                    alter table mifamilia add(constraint los_primeros check(edad < 35 or id >1000));
		    alter table myfamily add(constraint the_first check(age < 35 or id > 1000));

To delete a constraint from an existing table:

                    alter table mifamilia drop primary key;
		    alter table myfamily drop primary key;

Or if you check the constraint name of the table in USER_CONSTRAINTS or ALL_CONSTRAINTS then you can drop the constraint by constraint_name

                   alter table mifamilia drop constraint los_primeros;
		   alter table myfamily drop constraint the_first;

In case the constraint is referenced by another table then the previous commands will fail unless the cascade parameter is specified.

(Previous) < 1 2 3 4 5 > (Next)