Database Summative 1 (M1, M2, M3) Flashcards by Ralph Gamboa

Types of SQL Statements

DML (Data Manipulation Language)
DDL (Data Definition Language)
DCL (Data Control Language)
TCL (Transaction Control Language)

How well did you know this?

Not at all

Perfectly

Used to manipulate and manage data in the database

DML (Data Manipulation Language

How well did you know this?

Not at all

Perfectly

INSERT
UPDATE
DELETE
MERGE

DML (Data Manipulation Language) statements:

How well did you know this?

Not at all

Perfectly

Adds new rows to a table

INSERT

How well did you know this?

Not at all

Perfectly

Modifies existing data

UPDATE

How well did you know this?

Not at all

Perfectly

Removes data from a table

DELETE

How well did you know this?

Not at all

Perfectly

Combines insert and update operations based on conditions

MERGE

How well did you know this?

Not at all

Perfectly

CREATE
ALTER
DROP

DDL (Data Definition Language)

How well did you know this?

Not at all

Perfectly

Defines or alters the structure of the database

DDL (Data Definition Language)

How well did you know this?

Not at all

Perfectly

Creates new tables or database objects

CREATE

How well did you know this?

Not at all

Perfectly

Modifies existing database structures

ALTER

How well did you know this?

Not at all

Perfectly

Deletes tables or other database objects

DROP

How well did you know this?

Not at all

Perfectly

GRANT
REVOKE

DCL (Data Control Language)

How well did you know this?

Not at all

Perfectly

Manages permissions in the database

DCL (Data Control Language)

How well did you know this?

Not at all

Perfectly

Gives users access rights to the database

GRANT

How well did you know this?

Not at all

Perfectly

Removes access rights from users

REVOKE

How well did you know this?

Not at all

Perfectly

COMMIT
ROLLBACK
SAVEPOINT

TCL (Transaction Control Language)

How well did you know this?

Not at all

Perfectly

Controls transaction processing in the database

TCL (Transaction Control Language)

How well did you know this?

Not at all

Perfectly

Saves changes made in a transaction

COMMIT

How well did you know this?

Not at all

Perfectly

Reverts changes made in a transaction

ROLLBACK

How well did you know this?

Not at all

Perfectly

Sets a point in a transaction to roll back to

SAVEPOINT

How well did you know this?

Not at all

Perfectly

Manipulates data within tables

DML (Data Manipulation Language)

How well did you know this?

Not at all

Perfectly

Adds new records to a table

INSERT

How well did you know this?

Not at all

Perfectly

______ INTO table_name (column1, column2)
VALUES (value1, value2);

INSERT

How well did you know this?

Not at all

Perfectly

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.

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.

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 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