Brainscape's Knowledge GenomeTM

Browse over 1 million classes created by top students, professors, publishers, and experts.


See full index

3 TSY 23-24 > Database Summative 1 (M1, M2, M3) > Flashcards

Database Summative 1 (M1, M2, M3) Flashcards

1
Q

Types of SQL Statements

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

Used to manipulate and manage data in the database

A

DML (Data Manipulation Language

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

INSERT
UPDATE
DELETE
MERGE

A

DML (Data Manipulation Language) statements:

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

Adds new rows to a table

A

INSERT

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
5
Q

Modifies existing data

A

UPDATE

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

Removes data from a table

A

DELETE

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q

Combines insert and update operations based on conditions

A

MERGE

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

CREATE
ALTER
DROP

A

DDL (Data Definition Language)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

Defines or alters the structure of the database

A

DDL (Data Definition Language)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

Creates new tables or database objects

A

CREATE

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q

Modifies existing database structures

A

ALTER

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
12
Q

Deletes tables or other database objects

A

DROP

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

GRANT
REVOKE

A

DCL (Data Control Language)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q

Manages permissions in the database

A

DCL (Data Control Language)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
15
Q

Gives users access rights to the database

A

GRANT

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q

Removes access rights from users

A

REVOKE

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
17
Q

COMMIT
ROLLBACK
SAVEPOINT

A

TCL (Transaction Control Language)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
18
Q

Controls transaction processing in the database

A

TCL (Transaction Control Language)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
19
Q

Saves changes made in a transaction

A

COMMIT

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
20
Q

Reverts changes made in a transaction

A

ROLLBACK

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
21
Q

Sets a point in a transaction to roll back to

A

SAVEPOINT

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
22
Q

Manipulates data within tables

A

DML (Data Manipulation Language)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
23
Q

Adds new records to a table

A

INSERT

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
24
Q

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

A

INSERT

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
25
Q

Modifies existing records in a table

A

UPDATE

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
26
Q

_______ table_name
SET column1 = value1 //example, SET firstname = ‘Alec’, lastname = ‘guo’ WHERE GuoID = 1;
WHERE condition; //condition usually is set using ID

A

UPDATE

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
27
Q

Removes records from a table

A

DELETE

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
28
Q

______ FROM table_name
WHERE condition; //condition example, object name = ;
// example condition, WHERE firstname = ‘Juan’;

A

DELETE

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
29
Q

Defines and modifies database structure

A

DDL (Data Definition Language)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
30
Q

Creates new database objects (tables, views, etc.).

A

CREATE

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
31
Q

_____ TABLE table_name (
column1 datatype,
column2 datatype
);

A

CREATE

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
32
Q

Modifies existing database structures

A

ALTER

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
33
Q

____ TABLE table_name
ADD column_name datatype;

A

ALTER

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
34
Q

Deletes database objects

A

DROP

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
35
Q

_____ TABLE table_name;

A

DROP

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
36
Q

Manages access to the database

A

DCL (Data Control Language)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
37
Q

Gives privileges to users

A

GRANT

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
38
Q

Removes privileges from users

A

REVOKE

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
39
Q

Manages transactions in the database

A

TCL (Transaction Control Language)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
40
Q

Saves changes made in the transaction.

A

COMMIT

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
41
Q

COMMIT;

A

COMMIT

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
42
Q

Reverts changes made in the transaction.

A

ROLLBACK

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
43
Q

ROLLBACK;

A

ROLLBACK

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
44
Q

Sets a save point within a transaction to rollback to

A

SAVEPOINT

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
45
Q

_____ savepoint_name;

A

SAVEPOINT

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
46
Q

Changes data in the database but does not affect the structure.

A

DML (Data Manipulation Language) Effect

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
47
Q

Changes can be committed or rolled back (part of TCL).

A

DML (Data Manipulation Language) Transaction Control

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
48
Q

Changes the schema or structure of the database.

A

DDL (Data Definition Language) Effect

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
49
Q

Automatically commits changes (cannot be rolled back).

A

DDL (Data Definition Language) Transaction Control

50
Q

Controls who can access or modify data and objects within the database.

A

DCL (Data Control Language) Effect

51
Q

Automatically commits changes (cannot be rolled back).

A

DCL (Data Control Language) Transaction Control

52
Q

Controls the permanence of changes made using DML commands.

A

TCL (Transaction Control Language) Effect

53
Q

Used with DML to manage changes to data

A

TCL (Transaction Control Language) Usage

54
Q

DML modifies ____

A

data

55
Q

DDL modifies ____

A

structure

56
Q

DCL manages ____

A

permissions

57
Q

TCL manages _____

A

transactions

58
Q

allow us to execute different blocks of code based on certain conditions.

A

Conditional statements

59
Q

In PL/SQL, ______ help you manage how your database operations are performed depending on whether specific conditions are met.

A

conditional statements

60
Q

Executes a block of code only if a condition is TRUE

A

IF-THEN Statement

61
Q

__ ______ _____
– statements to execute if condition is true
END __;

A

IF condition THEN
IF

62
Q

DECLARE
v_salary NUMBER := 3000;
BEGIN
IF v_salary > 2500 THEN
DBMS_OUTPUT.PUT_LINE(‘Salary is above the threshold.’);
END IF;

A

IF-THEN State,emnt

63
Q

Executes one block of code if a condition is TRUE and another block if the condition is FALSE.

A

IF-THEN-ELSE Statement

64
Q

IF condition THEN
– statements to execute if condition is true
ELSE
– statements to execute if condition is false
END IF;

A

IF-THEN-ELSE Statement Syntax

65
Q

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;

A

IF-THEN-ELSE Statement

66
Q

Handles multiple conditions by evaluating them in sequence.

A

IF-THEN-ELSIF-ELSE Statement

67
Q

The first condition that evaluates to TRUE triggers its associated block of code.

A

IF-THEN-ELSIF-ELSE Statement

68
Q

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;

A

IF-THEN-ELSIF-ELSE Statement Syntax

69
Q

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;

A

IF-THEN-ELSIF-ELSE Statement

70
Q

Simplifies complex IF-THEN-ELSE statements by consolidating the logic into a cleaner and more readable structure.

A

CASE Statement

71
Q

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;

A

CASE Statement Syntax

72
Q

Keyword to remember in m1

A

CASE, WHEN, THEN, ELSE, END CASE

73
Q

allow you to group related data items into a single composite data type.

A

Introduction to PL/SQL Records

74
Q

These fields can have different data types, and records can be used to store an entire row of data fetched from a table.

A

PL/SQL records

75
Q

Store a row from a table in a record variable with multiple fields.

A

Creating a Record Using %ROWTYPE

76
Q

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;

A

%ROWTYPE syntax

77
Q

record_name is declared using %ROWTYPE, which automatically assigns the structure of the table.

A

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;

78
Q

Create custom record types with fields from different tables or with customized structures.

A

User-Defined Records

79
Q

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;

A

User-Defined Records Syntax

80
Q

is used to define a custom record structure with fields of specific data types.

A

TYPE

81
Q

record_var is the record variable that holds the values assigned to its fields.

A

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;

82
Q

Use _____ to fetch all columns of a row from a table and display specific fields.

A

%ROWTYPE

83
Q

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;

A

Example of %ROWTYPE for Fetching Data

84
Q

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.

A

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;

85
Q

Define a record structure for storing data from multiple sources and access the fields.

A

Example of User-Defined Records

86
Q

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;

A

Example of User-Defined Records

87
Q

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.

A

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;

88
Q

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;

A

Explicit Cursor Declaration and Usage

89
Q

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;

A

Cursor FOR Loop

90
Q

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;

A

Parameterized Cursor

91
Q

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;

A

Cursor with FOR UPDATE and WHERE CURRENT OF Clause

92
Q

An ______ occurs when an error is discovered during the execution of a program that disrupts its normal operation.

A

exception

93
Q

Examples of common errors:

A

Incorrect username or password.
Missing @ in an email address.
Entering an expired credit card number.
Selecting more than one row into a single variable.

94
Q

Automatically raised by the Oracle server when errors occur (e.g., NO_DATA_FOUND, TOO_MANY_ROWS).

A

Predefined Exceptions:

95
Q

Examples of Predefined Exceptions:

A

(e.g., NO_DATA_FOUND, TOO_MANY_ROWS).

96
Q

Programmers can define exceptions specific to their application using the EXCEPTION declaration and the RAISE statement.

A

User-Defined Exceptions

97
Q

Programmers can define exceptions specific to their application using the _____ declaration and the ____ statement.

A

EXCEPTION, RAISE

98
Q

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.

A

exception handler

99
Q

DECLARE
– Variable and record declarations
BEGIN
– Executable statements
EXCEPTION
WHEN exception_name THEN
– Statements to handle the exception
END;

A

Handling Exceptions in PL/SQL Syntax

100
Q

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;

A

Handling Exceptions in PL/SQL Example

101
Q

Common Predefined Exceptions

A

NO_DATA_FOUND
TOO_MANY_ROWS
ZERO_DIVIDE
DUP_VAL_ON_INDEX

102
Q

Raised when a SELECT statement returns no rows.

A

NO_DATA_FOUND

103
Q

Raised when a SELECT statement returns more than one row.

A

TOO_MANY_ROWS

104
Q

Raised when an attempt to divide by zero is made.

A

ZERO_DIVIDE

105
Q

Raised when there is a violation of a unique index.

A

DUP_VAL_ON_INDEX

106
Q

DECLARE
v_value NUMBER;
BEGIN
v_value := 10 / 0;
EXCEPTION
WHEN ZERO_DIVIDE THEN
DBMS_OUTPUT.PUT_LINE(‘Cannot divide by zero.’);
END;

A

Example of Trapping a Predefined Exception:

107
Q

Allows you to define custom error messages for specific conditions that are not covered by predefined exceptions.

A

User-Defined Exceptions

108
Q

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;

A

User-Defined Exceptions Syntax

109
Q

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;

A

User-Defined Exceptions Example

110
Q

Catches any exceptions that are not explicitly trapped by other exception handlers.

A

The OTHERS Exception Handler

111
Q

EXCEPTION
WHEN OTHERS THEN
– Handle all other exceptions
END;

A

The OTHERS Exception Handler Syntax

112
Q

BEGIN
– Some code that may raise an exception
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(‘An unknown error occurred.’);
END;

A

The OTHERS Exception Handler Example

113
Q

Allows you to associate a user-defined error message and code with an exception.

A

Using the RAISE_APPLICATION_ERROR Procedure

114
Q

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.

A

RAISE_APPLICATION_ERROR Syntax

115
Q

BEGIN
IF v_value = 0 THEN
RAISE_APPLICATION_ERROR(-20001, ‘Value cannot be zero.’);
END IF;
END;

A

RAISE_APPLICATION_ERROR Example

116
Q

_____ allow for more robust error handling in PL/SQL

A

Exceptions

117
Q

are automatically raised for common errors (e.g., NO_DATA_FOUND).

A

Predefined Exceptions:

118
Q

can be created to handle specific application errors.

A

User-Defined Exceptions

119
Q

Use the ____ handler to catch any unhandled exceptions.

A

OTHERS

120
Q

allows for custom error messages and codes, improving error reporting.

A

RAISE_APPLICATION_ERROR

3 TSY 23-24 (14 decks)

Brainscape helps you reach your goals faster, through stronger study habits.
© 2024 Bold Learning Solutions. Terms and Conditions