ITIS411-416 (MID-TERM)

✓ SQL command categories:

1- DDL: object
1. CREATE
2. DROP (delete)
3. ALITER (modify)

2- DML:
1. Insert
2. Update
3. Delete
4. Select

3- Transaction control :
1- Save Change:
COMMIT;

2- Discard Change:
ROLLBACK;

3- SavePoint:
SAVEPOINT name_Savepoint;

Normlization:
1NF > No repeating Group + PK is identified
2NF > No partial Dependency (Composite Key)
3NF > 1NF , 2NF and No transitive Dependency
4- Create:

➢ CREATE TABLE FOR PK + FK:

CREATE TABLE table_name (
column1 datatype,
column2 datatype,
column3 datatype,
CONSTRAINT nameTable_ nameColumn_PK PRIMARY KEY (nameColumn)
CONSTRAINT nameTable_ nameColumn_FK FOREIGN KEY (nameColumn)
REFERENCES nameTable (PK nameColumn)
);

➢ CREATE TABLE FOR COMPOSITE:

CREATE TABLE table_name (
column1 datatype,
column2 datatype,
column3 datatype,

CONSTRAINT nameTable_nameColumn1_FK REFERENCES nameTable

(nameColumn)

CONSTRAINT nameTable_nameColumn2_FK REFERENCES nameTable

(nameColumn)

CONSTRAINT nameTable_nameColumn1_nameColumn2_PK PRIMARY KEY

(nameColumn1, nameColumn2);

➢ CREATE TABLE FOR CHECK:

CREATE TABLE table_name (
column1 datatype,
column2 datatype,
column3 datatype,

CONSTRAINT nameTable_nameColumn_CC check (nameColumn=’’)

);
5- Alter:

▪ ADD:
➢ Add new Column: ALTER TABLE nameTable ADD( nameColumn datatype (size));

➢ Add new Column FK: ALTER TABLE nameTable ADD Column nameColumn1 datatype
(size) REFERENCES nameTable (nameColumn2));

➢ Make Column required: ALTER TABLE nameTable ( Alter Column nameColumn NOT
NULL);

➢ Add new Column with some condition: ALTER TABLE nameTable ADD( nameColumn
datatype (size) CONSTRAINT nameTable_ nameColumn_CC check (nameColumn=’’));

➢ Add constraint for Column: ALTER TABLE nameTable ADD CONSTRAINT nameTable_
nameColumn_CC check (nameColumn=’’));

▪ Modify:
➢ Modify Column: ALTER TABLE nameTable MODIFY ( Column nameColumn datatype
(size));
➢ Modify Column NOT SURE: ALTER nameTable MODIFY (nameColumn Newdatatype
(size));

▪ Drop:
➢ Delete Column: ALTER TABLE nameTable DROP COLUMN nameColumn;

➢ Rrmove existing constraint: ALTER TABLE nameTable DROP CONSTRAINT

constrain_name;

▪ Rename:
➢ Rename Column: ALTER TABLE nameTable RENAME COLUMN OldnameColumn TO
NewnameColumn;
▪ Disable:
➢ Disable constraint to FK: ALTER TABLE nameTable DISABLE CONSTRAINT nameTable_
nameColumn_fk;
➢ Disable constraint to UK: ALTER TABLE nameTable DISABLE CONSTRAINT nameTable_
nameColumn_Uk;

▪ Enable:
➢ Disable constraint to FK: ALTER TABLE nameTable ENABLE CONSTRAINT nameTable_
nameColumn_fk;
➢ Disable constraint to UK: ALTER TABLE nameTable ENABLE CONSTRAINT nameTable_
nameColumn_Uk;

6- Insert:
➢ Insert row: INSERT INTO nameTable VALUES (‘….’ , ’….’);

➢ Insert row for BLOB data: INSERT INTO nameTable ( nameColumn1 , nameColumn2 ,
nameColumn3 ) VALUES (‘’ , ’’, EMPTY_BLOB() );

➢ Insert row for NEXTVAL: INSERT INTO nameTable VALUES (valuecolumn1. NEXTVAL,
’’,’’);

➢ Insert row for CURRVAL: INSERT INTO nameTable VALUES (valuecolumn1. CURRVAL,
’’,’’);

➢ Insert value, into date column: TO_Date (‘Date String ’ , ‘Date Format model’);

LIKE > TO_Date (’24-AUG-2005 ’ , ‘DD-MON-YYYY);

OR > TO_Date (’24/AUG/2005 ’ , ‘DD-MON-YYYY);

➢ interval:
➢ Interval year to month : TO_YMINTERVAL(‘year - month’);
➢ Interval Day to Second : TO_DSINTERVAL(‘day, HH:MI:SS.99’);
➢ Date format: TO_CHAR (nameColumn , ‘format model’)
7- Rename:
➢ Rename for old Table: RENAME OldnameTable TO NewnameTable;

8- Update:
➢ Update for some Colum: UPDATE nameTable SET nameColumn = NewnameColumn
WHERE nameColumn=’’ ;

9- Delete:
➢ Delete for some Row: DELETE FROM nameTable WHERE nameColumn=’’ ;

➢ Delete for sequence: DROP SEQUENCE nameColumn _SEQ;

➢ Delete for some Table: DROP TABLE nameTable;

➢ Delete for some Table With FK: DROP TABLE nameTable CASCADE CONSTRAINTS ;

10- sequence:
➢ CREATE New sequence with increment: CREATE SEQUENCE nameColumn _SEQ (
INCREMENT by 10 START WITH 101);

➢ CREATE New sequence: CREATE SEQUENCE nameColumn _SEQ START WITH 101;

11- Truncating:
➢ Truncating Tables: TRUNCATE TABLE nameTable;

12- Viewing:
➢ Info about Tables: DESCRIBE nameTable;
➢ Info about Tables for USER: SELECT nameTable FROM user_tables;
➢ Info about Tables for ALL: SELECT nameTable FROM all_tables;
➢
➢ Seq info: SELECT SEQ_name FROM user_sequences ;

13- Select:

▪ CURRVAL and NEXTVAL:

➢ Retrieve for CURRVAL : SELECT nameColumn _SEQ. CURRVAL FROM nameTable;

➢ Retrieve for NEXTVAL: SELECT nameColumn _SEQ. NEXTVAL FROM nameTable;

▪ Distinct:

➢ Distinct: SELECT DISTINCT nameColumn FROM nameTable;

▪ Like:
➢ LIKE for last characters: SELECT * FROM nameTable WHERE nameColumn LIKE ‘ %fatima
’;

➢ LIKE for first characters: SELECT * FROM nameTable WHERE nameColumn LIKE ‘ fatima%
’;

➢ LIKE for specific characters: SELECT * FROM nameTable WHERE nameColumn LIKE ‘
%fatima% ’ ;

▪ Date:

➢ Date Calculations: SELECT SYSDATE FROM nameTable ;

➢ Date Calculations for day: SELECT nameColumn1 , nameColumn2 ,SYSDATE-

nameColumn3 FROM nameTable ;

➢ Date Calculations for year: SELECT nameColumn1 , nameColumn2 , (SYSDATE-

nameColumn3)/365 FROM nameTable ;

➢ Interval Calculations to year and month: SELECT nameColumn1 + TO_YMINTERVAL

(‘year - month’) FROM nameTable ;

➢ Interval Calculations to day and second: SELECT nameColumn1 + TO_DSINTERVAL (‘day,

HH:MI:SS.99’) FROM nameTable ;
▪ HAVING AND GROUP:
➢ HAVING: SELECT nameColumn1 , SUM(nameColumn2) FROM nameTable HAVING
SUM(nameColumn2) >=100 GROUP BY nameColumn1;

▪ NULL:

➢ Not Null for row: SELECT * FROM nameTable WHERE nameColumn IS NOT NULL;

▪ IN:

➢ Not IN for row: SELECT * FROM nameTable WHERE nameColumn NOT IN (value1 ,
value2);

▪ ORDER:

➢ ORDER for row: SELECT * FROM nameTable WHERE nameColumn =’’ ORDER BY
nameColumn;

▪ Arithmetic:

➢ Arithmetic for row: SELECT nameColumn1 , nameColumn2, nameColumn3 *

nameColumn4 FROM nameTable;

▪ Heading:

➢ Arithmetic column: SELECT nameColumn as aliasname , nameColumn2 FROM

nameTable;

OR
➢ Arithmetic column: SELECT nameColumn “aliasname” , nameColumn2 FROM
nameTable;

14- GRANT And REVOKE:

▪ For Object:
➢ GRANT FOR Specific user: GRANT SELECT, INSERT, DELETE,UPDATE ON nameTable TO
user_name/ public;

➢ REVOKE FOR Specific user: REVOKE SELECT, INSERT, DELETE,UPDATE ON nameTable TO

user_name/ public ;
➢ REVOKE FOR Specific Table: REVOKE SELECT, INSERT, DELETE,UPDATE ON nameTable
FROM nameTable ;

▪ For System:
➢ GRANT FOR Specific user: GRANT GREATE, ALTER ,DROP ON nameTable TO user_name/
public;

➢ REVOKE FOR Specific user: REVOKE GREATE, ALTER ,DROP ON nameTable TO

user_name/ public ;

Following Oracle query returns the current date and time:

SELECT SYSDATE FROM DUAL;

• _ -> for single character

Done By: ALFATMI

Instagram: Fatema4sm
Coding of final IS416
CH3C
• To join multiple tables we use:
SELECT col1, col2, …
FROM table1, table2
WHERE table1.col1 = table2.col2
AND search_condition(s);

• Inner joins also called -> equality join + quijoin + natural join

• We can join multiple tables using “an intermediary table” -> table whose columns are not
display or search column, but whose columns are joined columns that serve to join the
two tables.

• To specify the inner and outer tables “to only retrieve all of thr rows in the tables” :
Inner_table.join_column = outer_table.join_column(+)

• When you create a query that joins a table to itself, you create a self-join.

• Subqueries that returns a single value :

“Main query” SELECT fieldname1, fieldname2, …
FROM table1, table2, …
WHERE join conditions
AND search field1 = (SELECT fieldname1
FROM table1, table2, … “sub-query”
WHERE search and join conditions)

• Subqueries could be used in “SEARCH CONDITIONS” to update + delete

commands.

• If you want to combine 2 separate queries but at the same these queries don’t
include foreign key, you should use UNION set operator for it.
• There are 2 types of union -> union (display duplication only once) + union
all (display all the duplication.)

• If you want to create a view, use “SOURCE QUERY” as it specify a

collection of single table column or rows or even it could join multiple
tables.

• Updatable views or simple views = to insert, update, and delete data in the
underlying tables.

• view derives its data from tables called = source tables or underlying base
tables.

• DBAs use views > to enforce DB security by allowing certain users to view
only selected table columns or rows. (like giving the user a specific
privileges to manipulate few columns only)

• To create a view:
CREATE VIEW view_name
AS source_query;

• To use a view for a specific name:

CREATE OR REPLACE VIEW view_name
AS source_query;

• TO insert row into a table :

INSERT INTO table_view VALUES(6, ‘M’);

• TO delete row into a table :

DELETE FROM table_view WHERE f_last= ‘M’;

• To remove a view from DB schema:

DROP VIEW view_name
CH4A

• To declare a PL/SQL program :

Variable_name data_type_declaration.

• For “single field” reference variables, you should use.

Variable_name tableName.fieldname%TYPE;

• For reference composite variables, you should use;

Variable_name tableName.fieldname%ROWTYPE;

• EXPONENT > by code (2**3) > that mean (2)^3 =8

• Negation> by code (-5) > = (-5)

• For an assignment statements, use :

Variable_name := value;
• Buffer -> a memory area that is used to increase the size of the program
output variable before displaying it to the user.
SET SERVEROUTPUT ON SIZE buffer_size; buffer_size, for e.g= 2000

• To display an output:
DBMS_OUTPUT.PUT_LINE (‘display_text’).
• To concatenate two strings:
New_ string := string1 || string2;
e.g : DBMS_OUTPUT.PUT_LINE (‘display_text’ ||
TO_CHAR(todays_date));
The results: display text 05-FEB-06
• To remove blank leading spaces (LTRIM function) :
string := LTRIM (string_variable_name);

• To remove blank leading spaces (RTRIM function) :

String := RTRIM (string_variable_name);

• The LENGTH function has the following syntax:

string_length := LENGTH( string_variable_name);

• To convert the lowercase into UPPERCASE:

string := UPPER(string_variable_name);

• To convert the upper into LOWERCASE:

string := LOWER(string_variable_name);

• To convert uppercase,lowercase but only the first letter with uppercase:

string := INTICAP(string_variable_name);

• To search a string for a specific substring:

start_position := INSTR(original_string, substring);

• To extracts a specific number of characters from a character string:

Extracted_string := SUBSTR(string_variable, starting_point,
number_of_characters);

• There are 2 types of error: syntax and logic errors.

Syntax error -> occurs when the command doesn’t follow the guidelines of
the programming languages.
Logic error -> it doesn’t stop the program from running, and the results will
be in an incorrect result.
CH4B

• Coding > Decision control structures (IF/THEN/ELSE)

IF condition THEN
Commands that execute if condition is TRUE;

ELSE
Commands that execute if condition is FALSE;
END IF;

• Coding > Decision control structures (IF/THEN/ELSE *MANY

DIFFERENT CONDITIONS*)
IF condition1 THEN
Commands that execute if condition1 is TRUE;

ELSIF condition2 THEN

Commands that execute if condition2 is TRUE;

ELSIF condition3 THEN

Commands that execute if condition3 is TRUE;

ELSE
Commands that execute if condition is FALSE;

END IF;

• There are 3 type of loops:

1) LOOP -> its used when there are multiple condition to be executed.
2) PRETEST -> its used in case the program isn’t execute. (LOOP … EXIT
“ IF/THEN- will be the FIRST code in the loop”)
3) POSTTEST -> to test the program only at least once. (LOOP … EXIT
“IF/THEN- will be the LAST code in the loop ”)
• There are 5 loops structures:
1- LOOP … EXIT,
the syntax :
LOOP
[program statements]
IF CONDITION THEN
EXIT;
ENDIF;
[additional program statements]
END LOOP;

2- LOOP … EXIT WHEN,

the syntax :
LOOP
[program statements]
EXIT WHEN condition
END LOOP;

3- WHILE … LOOP,
the syntax :
WHILE condition LOOP
[program statements]
END LOOP;

4- NUMERIC FOR LOOPS

the syntax :

FOR counter_variable IN start_value .. end value

LOOP
[program statements]
END LOOP;
5- CURSOR FOR LOOPS (Implicit and explicit cursors)
▪ Implicit the syntax :
SELECT col1, col2, ….
INTO variable1, variable2, …..
FROM table1, table2, …
WHERE join condition …..
AND search_condition_to_retrieve_1_record;

▪ Explicit the syntax :

CURSOR cursor_name IS select.query;

❖ 1- To open an explicit cursor: OPEN cursor_name;

❖ 2- Fetching the data rows :
LOOP
FETCH cursor_name INTO variable_name(s)
EXIT WHEN cursor_name%NOTFOUND;

❖ 3- To close an explicit cursor: CLOSE cursor_name;

❖ 4- cursor FOR LOOP:

FOR variable_name(s) IN cursor_name LOOP
Processing command
END LOOP;

• Syntax for undefined exception :

DECLARE
e_exception_name EXCEPTION
PRAGMA EXCEPTION_INIT(e_exception_name, -Oracle_error_code);

• General syntax for declaring, raising and handling a user-defined exception:

DECLARE
e_exception_name EXCEPTION;
other variable declaration;
BEGIN
Other program commands
IF exception_condition THEN
RAISE e_exception_name;
END IF;
Other program commands
EXCEPTION
WHEN e_exception_name THEN
Exception handler commands;
END;
CH4X

• Local variables: declared in a inner block + cannot be referenced by outside

Blocks.

• Global variables: declared in a outer block + can be referenced by itself and

by its inner blocks.

• Parameters are passed to procedures in three ways:

✓ IN-parameters
✓ OUT-parameters
✓ IN OUT-parameters

• Syntax for creating procedure:

CREATE OR REPLACE PROCEDURE proc_name
IS
Declaration section
BEGIN
Execution section
EXCEPTION
Exception section
END;

• How to execute a Stored Procedure ?

EXECUTE [or EXEC] procedure_name ;

• To find out what procedures you have created, use the following SQL query:
SELECT object_type, object_name
FROM user_objects
WHERE object_type = ‘procedure’;

• To drop a stored procedure:

DROP procedure <procedure_name>;
• Methods of passing parameters:

Positional notation + named notation + mixed notation.

• Syntax for functions:
CREATE OR REPLACE PROCEDURE function _name [parameter]
RETURN return_datatype;
IS
Declaration section
BEGIN
Execution_ section
Return return_variable;
EXCEPTION
Exception section
Return return_variable;
END;
• To find out what functions you have created, use the following SQL query:
SELECT object_type, object_name
FROM user_objects
WHERE object_type = ‘function’;

• To drop a stored procedure:

DROP function <function_name>;

• To create an index:

CREATE INDEX index_name

ON tablename (index_fieldname);

• To create a composite index:

CREATE INDEX index_name

ON tablename (index_fieldname1, index fieldname2, …);

• Rename an index:
ALTER INDEX index_name
RENAME TO new_index_name;

• To drop a stored procedure:

DROP INDEX index_name;
BUILT_IN_FUNCTIONS

• ASCII Function :
Ascii( single_character )

• ASCIISTR Function :
Asciistr( string )

• Chr function :
Chr ( number_code )

• Compose function :
Compose ( string )

• Concat function :
Concat ( string1, string2)

• decompose function :
decompose ( string )

• dump function :
dump( expression, [return_format],
[start_position], [length] )

• Inticap
initcap( string1 )

• Length
length( string1 )
• Lower
lower( string1 )

• lpad:
lpad( string1, padded_length, [
pad_string ] )

• Rpad:
lpad( string1, padded_length, [
pad_string ] )

• Ltrim:
ltrim( string1, [ trim_string ] )

• Rtrim:
ltrim( string1, [ trim_string ] )

• Replace
replace( string1, string_to_replace, [
replacement_string ] )

• Instr
instr( string1, string2 [,
start_position [,
nth_appearance ] ] )

• Soundex
soundex( string1 )

• Substr
substr( string, start_position, [ length ] )

• Translate:
translate( string1, string_to_replace,
replacement_string )

• Ttrim:
trim( [ leading | trailing | both [ trim_character ] ] string1 )
• Upper
upper( string1 )

• Vsize
vsize( expression )

• Bin_to_num
bin_to_num( expr1, expr2, ... expr_n)

• Cast
cast ( { expr | ( subquery ) | MULTISET (
subquery ) } AS type_name )

• Chartorowid:
chartorowid( value1 )

• from_tz function:
from_tz( timestamp_value, time_zone_value )

• hextoraw function:
hextoraw( char )

• numtodsinterval function:
numtodsinterval( number, expression )

• numtoyminterval
numtoyminterval( number, expression )

• rawtohex function
rawtohex( raw )

• to_char function
to_char( value, [ format_mask ], [ nls_language ] )
CH4Z
• For simple case statements -> it allows you to choose which of several
sequences to execute(single expression):
CASE expression
WHEN result1 THEN
Statements1
WHEN result2 THEN
Statement2
…
ELSE
Statements_else
END CASE;

• If you don’t have an explicit ELSE clause use:

ELSE
RAISE CASE_NOT_FOUND;

• To evaluate a list of Boolean expressions “searched CASE statements”:

CASE
WHEN expression1 THEN
Statements1
WHEN expression2 THEN
Statement2
…
ELSE
Statements_else
END CASE;

• The third type that is difficult to implement “nested case statement”.

CH4Z2

• Collections: data structure that acts like a list or a single-dimensional array.

Collections are, in fact, the closest you can get in the PL/SQL language to
traditional arrays.

Types of Collections
- Associative arrays
- Nested tables
- VARRAYs

Done By: ALFATMI + Maryam.

Instagram: Fatema4sm