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