Database Summative 1 (M1, M2, M3) Flashcards
Types of SQL Statements
DML (Data Manipulation Language)
DDL (Data Definition Language)
DCL (Data Control Language)
TCL (Transaction Control Language)
Used to manipulate and manage data in the database
DML (Data Manipulation Language
INSERT
UPDATE
DELETE
MERGE
DML (Data Manipulation Language) statements:
Adds new rows to a table
INSERT
Modifies existing data
UPDATE
Removes data from a table
DELETE
Combines insert and update operations based on conditions
MERGE
CREATE
ALTER
DROP
DDL (Data Definition Language)
Defines or alters the structure of the database
DDL (Data Definition Language)
Creates new tables or database objects
CREATE
Modifies existing database structures
ALTER
Deletes tables or other database objects
DROP
GRANT
REVOKE
DCL (Data Control Language)
Manages permissions in the database
DCL (Data Control Language)
Gives users access rights to the database
GRANT
Removes access rights from users
REVOKE
COMMIT
ROLLBACK
SAVEPOINT
TCL (Transaction Control Language)
Controls transaction processing in the database
TCL (Transaction Control Language)
Saves changes made in a transaction
COMMIT
Reverts changes made in a transaction
ROLLBACK
Sets a point in a transaction to roll back to
SAVEPOINT
Manipulates data within tables
DML (Data Manipulation Language)
Adds new records to a table
INSERT
______ INTO table_name (column1, column2)
VALUES (value1, value2);
INSERT
Modifies existing records in a table
UPDATE
_______ table_name
SET column1 = value1 //example, SET firstname = ‘Alec’, lastname = ‘guo’ WHERE GuoID = 1;
WHERE condition; //condition usually is set using ID
UPDATE
Removes records from a table
DELETE
______ FROM table_name
WHERE condition; //condition example, object name = ;
// example condition, WHERE firstname = ‘Juan’;
DELETE
Defines and modifies database structure
DDL (Data Definition Language)
Creates new database objects (tables, views, etc.).
CREATE
_____ TABLE table_name (
column1 datatype,
column2 datatype
);
CREATE
Modifies existing database structures
ALTER
____ TABLE table_name
ADD column_name datatype;
ALTER
Deletes database objects
DROP
_____ TABLE table_name;
DROP
Manages access to the database
DCL (Data Control Language)
Gives privileges to users
GRANT
Removes privileges from users
REVOKE
Manages transactions in the database
TCL (Transaction Control Language)
Saves changes made in the transaction.
COMMIT
COMMIT;
COMMIT
Reverts changes made in the transaction.
ROLLBACK
ROLLBACK;
ROLLBACK
Sets a save point within a transaction to rollback to
SAVEPOINT
_____ savepoint_name;
SAVEPOINT
Changes data in the database but does not affect the structure.
DML (Data Manipulation Language) Effect
Changes can be committed or rolled back (part of TCL).
DML (Data Manipulation Language) Transaction Control
Changes the schema or structure of the database.
DDL (Data Definition Language) Effect
Automatically commits changes (cannot be rolled back).
DDL (Data Definition Language) Transaction Control
Controls who can access or modify data and objects within the database.
DCL (Data Control Language) Effect
Automatically commits changes (cannot be rolled back).
DCL (Data Control Language) Transaction Control
Controls the permanence of changes made using DML commands.
TCL (Transaction Control Language) Effect
Used with DML to manage changes to data
TCL (Transaction Control Language) Usage
DML modifies ____
data
DDL modifies ____
structure
DCL manages ____
permissions
TCL manages _____
transactions
allow us to execute different blocks of code based on certain conditions.
Conditional statements
In PL/SQL, ______ help you manage how your database operations are performed depending on whether specific conditions are met.
conditional statements
Executes a block of code only if a condition is TRUE
IF-THEN Statement
__ ______ _____
– statements to execute if condition is true
END __;
IF condition THEN
IF
DECLARE
v_salary NUMBER := 3000;
BEGIN
IF v_salary > 2500 THEN
DBMS_OUTPUT.PUT_LINE(‘Salary is above the threshold.’);
END IF;
IF-THEN State,emnt
Executes one block of code if a condition is TRUE and another block if the condition is FALSE.
IF-THEN-ELSE Statement
IF condition THEN
– statements to execute if condition is true
ELSE
– statements to execute if condition is false
END IF;
IF-THEN-ELSE Statement Syntax
v_salary NUMBER := 2000;
IF v_salary > 2500 THEN
DBMS_OUTPUT.PUT_LINE(‘Salary is above the threshold.’);
ELSE
DBMS_OUTPUT.PUT_LINE(‘Salary is below or equal to the threshold.’);
END IF;
END;
IF-THEN-ELSE Statement
Handles multiple conditions by evaluating them in sequence.
IF-THEN-ELSIF-ELSE Statement
The first condition that evaluates to TRUE triggers its associated block of code.
IF-THEN-ELSIF-ELSE Statement
IF condition1 THEN
– statements if condition1 is true
ELSIF condition2 THEN
– statements if condition2 is true
ELSE
– statements if none of the conditions are true
END IF;
IF-THEN-ELSIF-ELSE Statement Syntax
DECLARE
v_grade CHAR(1) := ‘B’;
BEGIN
IF v_grade = ‘A’ THEN
DBMS_OUTPUT.PUT_LINE(‘Excellent’);
ELSIF v_grade = ‘B’ THEN
DBMS_OUTPUT.PUT_LINE(‘Very Good’);
ELSE
DBMS_OUTPUT.PUT_LINE(‘Needs Improvement’);
END IF;
END;
IF-THEN-ELSIF-ELSE Statement
Simplifies complex IF-THEN-ELSE statements by consolidating the logic into a cleaner and more readable structure.
CASE Statement
CASE
WHEN condition1 THEN
– statements if condition1 is true
WHEN condition2 THEN
– statements if condition2 is true
ELSE
– statements if none of the conditions are true
END CASE;
CASE Statement Syntax
Keyword to remember in m1
CASE, WHEN, THEN, ELSE, END CASE
allow you to group related data items into a single composite data type.
Introduction to PL/SQL Records
These fields can have different data types, and records can be used to store an entire row of data fetched from a table.
PL/SQL records
Store a row from a table in a record variable with multiple fields.
Creating a Record Using %ROWTYPE
DECLARE
record_name table_name%ROWTYPE;
BEGIN
SELECT * INTO record_name
FROM table_name
WHERE condition;
– Accessing fields
DBMS_OUTPUT.PUT_LINE(‘Field value: ‘ || record_name.field_name);
END;
%ROWTYPE syntax
record_name is declared using %ROWTYPE, which automatically assigns the structure of the table.
DECLARE
record_name table_name%ROWTYPE;
BEGIN
SELECT * INTO record_name
FROM table_name
WHERE condition;
– Accessing fields
DBMS_OUTPUT.PUT_LINE(‘Field value: ‘ || record_name.field_name);
END;
Create custom record types with fields from different tables or with customized structures.
User-Defined Records
DECLARE
TYPE record_type IS RECORD (
field1 data_type,
field2 data_type
);
record_var record_type;
BEGIN
– Assign values to the fields
record_var.field1 := value1;
record_var.field2 := value2;
DBMS_OUTPUT.PUT_LINE(‘Field1: ‘ || record_var.field1 || ‘, Field2: ‘ || record_var.field2);
END;
User-Defined Records Syntax
is used to define a custom record structure with fields of specific data types.
TYPE
record_var is the record variable that holds the values assigned to its fields.
DECLARE
TYPE record_type IS RECORD (
field1 data_type,
field2 data_type
);
record_var record_type;
BEGIN
– Assign values to the fields
record_var.field1 := value1;
record_var.field2 := value2;
DBMS_OUTPUT.PUT_LINE(‘Field1: ‘ || record_var.field1 || ‘, Field2: ‘ || record_var.field2);
END;
Use _____ to fetch all columns of a row from a table and display specific fields.
%ROWTYPE
DECLARE
v_record table_name%ROWTYPE;
BEGIN
SELECT * INTO v_record
FROM table_name
WHERE condition;
– Output specific fields from the record
DBMS_OUTPUT.PUT_LINE(‘Field1: ‘ || v_record.field1 || ‘, Field2: ‘ || v_record.field2);
END;
Example of %ROWTYPE for Fetching Data
A record variable v_record is used to fetch all columns of a row.
The DBMS_OUTPUT.PUT_LINE command displays the values of specific fields in the record.
DECLARE
v_record table_name%ROWTYPE;
BEGIN
SELECT * INTO v_record
FROM table_name
WHERE condition;
– Output specific fields from the record
DBMS_OUTPUT.PUT_LINE(‘Field1: ‘ || v_record.field1 || ‘, Field2: ‘ || v_record.field2);
END;
Define a record structure for storing data from multiple sources and access the fields.
Example of User-Defined Records
DECLARE
TYPE my_record IS RECORD (
field1 data_type,
field2 data_type
);
v_my_record my_record;
BEGIN
– Assigning values to the user-defined record
v_my_record.field1 := value1;
v_my_record.field2 := value2;
– Output field values
DBMS_OUTPUT.PUT_LINE(‘Field1: ‘ || v_my_record.field1 || ‘, Field2: ‘ || v_my_record.field2);
END;
Example of User-Defined Records
my_record is a user-defined record type that stores values for field1 and field2.
The record variable v_my_record holds the values and is used to output them.
DECLARE
TYPE my_record IS RECORD (
field1 data_type,
field2 data_type
);
v_my_record my_record;
BEGIN
– Assigning values to the user-defined record
v_my_record.field1 := value1;
v_my_record.field2 := value2;
– Output field values
DBMS_OUTPUT.PUT_LINE(‘Field1: ‘ || v_my_record.field1 || ‘, Field2: ‘ || v_my_record.field2);
END;
DECLARE
CURSOR cursor_name IS
SELECT field1, field2 FROM table_name WHERE condition;
var1 data_type;
var2 data_type;
BEGIN
OPEN cursor_name;
LOOP
FETCH cursor_name INTO var1, var2; – Fetch rows
EXIT WHEN cursor_name%NOTFOUND; – Exit loop when no more rows
DBMS_OUTPUT.PUT_LINE(var1 || ‘ ‘ || var2);
END LOOP;
CLOSE cursor_name;
END;
Explicit Cursor Declaration and Usage
DECLARE
CURSOR cursor_name IS
SELECT field1, field2 FROM table_name;
BEGIN
FOR record_var IN cursor_name LOOP
– Access fields using record_var.field1, record_var.field2
DBMS_OUTPUT.PUT_LINE(record_var.field1 || ‘ ‘ || record_var.field2);
END LOOP;
END;
Cursor FOR Loop
DECLARE
CURSOR cursor_name (param1 data_type) IS
SELECT field1, field2 FROM table_name WHERE column = param1;
BEGIN
OPEN cursor_name(value); – Pass parameter to cursor
LOOP
FETCH cursor_name INTO var1, var2;
EXIT WHEN cursor_name%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(var1 || ‘ ‘ || var2);
END LOOP;
CLOSE cursor_name;
END;
Parameterized Cursor
DECLARE
CURSOR cursor_name IS
SELECT field1, field2 FROM table_name WHERE condition FOR UPDATE;
BEGIN
OPEN cursor_name;
LOOP
FETCH cursor_name INTO var1, var2;
EXIT WHEN cursor_name%NOTFOUND;
UPDATE table_name
SET column = new_value
WHERE CURRENT OF cursor_name; – Update the current row
END LOOP;
CLOSE cursor_name;
END;
Cursor with FOR UPDATE and WHERE CURRENT OF Clause
An ______ occurs when an error is discovered during the execution of a program that disrupts its normal operation.
exception
Examples of common errors:
Incorrect username or password.
Missing @ in an email address.
Entering an expired credit card number.
Selecting more than one row into a single variable.
Automatically raised by the Oracle server when errors occur (e.g., NO_DATA_FOUND, TOO_MANY_ROWS).
Predefined Exceptions:
Examples of Predefined Exceptions:
(e.g., NO_DATA_FOUND, TOO_MANY_ROWS).
Programmers can define exceptions specific to their application using the EXCEPTION declaration and the RAISE statement.
User-Defined Exceptions
Programmers can define exceptions specific to their application using the _____ declaration and the ____ statement.
EXCEPTION, RAISE
When an exception is raised, PL/SQL can stop abruptly. By using an ______, we can prevent this and define how the program should respond to the error.
exception handler
DECLARE
– Variable and record declarations
BEGIN
– Executable statements
EXCEPTION
WHEN exception_name THEN
– Statements to handle the exception
END;
Handling Exceptions in PL/SQL Syntax
DECLARE
v_value NUMBER;
BEGIN
v_value := 10 / 0; – This will raise the ZERO_DIVIDE exception
EXCEPTION
WHEN ZERO_DIVIDE THEN
DBMS_OUTPUT.PUT_LINE(‘Division by zero is not allowed.’);
END;
Handling Exceptions in PL/SQL Example
Common Predefined Exceptions
NO_DATA_FOUND
TOO_MANY_ROWS
ZERO_DIVIDE
DUP_VAL_ON_INDEX
Raised when a SELECT statement returns no rows.
NO_DATA_FOUND
Raised when a SELECT statement returns more than one row.
TOO_MANY_ROWS
Raised when an attempt to divide by zero is made.
ZERO_DIVIDE
Raised when there is a violation of a unique index.
DUP_VAL_ON_INDEX
DECLARE
v_value NUMBER;
BEGIN
v_value := 10 / 0;
EXCEPTION
WHEN ZERO_DIVIDE THEN
DBMS_OUTPUT.PUT_LINE(‘Cannot divide by zero.’);
END;
Example of Trapping a Predefined Exception:
Allows you to define custom error messages for specific conditions that are not covered by predefined exceptions.
User-Defined Exceptions
DECLARE
exception_name EXCEPTION; – Declare the exception
BEGIN
– Raise the exception when a condition is met
IF condition THEN
RAISE exception_name;
END IF;
EXCEPTION
WHEN exception_name THEN
– Handle the exception
END;
User-Defined Exceptions Syntax
DECLARE
e_invalid_input EXCEPTION;
v_input NUMBER := -5;
BEGIN
IF v_input < 0 THEN
RAISE e_invalid_input;
END IF;
EXCEPTION
WHEN e_invalid_input THEN
DBMS_OUTPUT.PUT_LINE(‘Invalid input: The value cannot be negative.’);
END;
User-Defined Exceptions Example
Catches any exceptions that are not explicitly trapped by other exception handlers.
The OTHERS Exception Handler
EXCEPTION
WHEN OTHERS THEN
– Handle all other exceptions
END;
The OTHERS Exception Handler Syntax
BEGIN
– Some code that may raise an exception
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(‘An unknown error occurred.’);
END;
The OTHERS Exception Handler Example
Allows you to associate a user-defined error message and code with an exception.
Using the RAISE_APPLICATION_ERROR Procedure
RAISE_APPLICATION_ERROR(error_number, message[, {TRUE | FALSE}]);
error_number: Must be between -20000 and -20999.
message: Custom error message.
TRUE | FALSE: Optional Boolean parameter to stack or replace previous errors.
RAISE_APPLICATION_ERROR Syntax
BEGIN
IF v_value = 0 THEN
RAISE_APPLICATION_ERROR(-20001, ‘Value cannot be zero.’);
END IF;
END;
RAISE_APPLICATION_ERROR Example
_____ allow for more robust error handling in PL/SQL
Exceptions
are automatically raised for common errors (e.g., NO_DATA_FOUND).
Predefined Exceptions:
can be created to handle specific application errors.
User-Defined Exceptions
Use the ____ handler to catch any unhandled exceptions.
OTHERS
allows for custom error messages and codes, improving error reporting.
RAISE_APPLICATION_ERROR
