Help
Options
Didn't know it?
click below
click below
Knew it?
click below
click below
Don't Know
Remaining cards (0)
Know
retry
shuffle
restart
0:00
Embed Code - If you would like this activity on your web page, copy the script below and paste it into your web page.
Normal Size Small Size show me how
Normal Size Small Size show me how
INFOMAN - Finals
modules 5-8
| Question | Answer |
|---|---|
| MODULE 5 | Introduction to SQL |
| It is the standard for relational database management systems. | SQL (Structured Query Language) |
| A database management system that manages data as a collection of tables in which all relationships are represented by common values in related tables. | RDBMS (Relational Database Management System) |
| A SET OF SCHEMAS that constitute the description of a database. | catalog |
| The structure that contains DESCRIPTIONS of objects created by a user (base tables, views, constraints) | schema |
| Commands that DEFINE a database, including creating, altering, and dropping tables and establishing constraints. | data definition language (DDL) |
| Commands that MAINTAIN and query a database | data manipulation language (DML) |
| Commands that CONTROL a database, including administering privileges and committing data | data control language (DCL) |
| What are the commands in data manipulation language? | select, insert, update, delete, merge |
| What are the commands in data definition language? | create, alter, drop, rename, truncate, comment |
| What are the commands in data control language? | grant, revoke |
| What are the commands in transaction control? | commit, rollback, savepoint |
| DDL is involved in... | physical design, maintenance |
| DML is involved in... | implementation |
| DCL is involved in... | implementation, maintenance |
| MODULE 6 | DATA DEFINITION LANGUAGE |
| It is a basic unit of storage composed of rows and columns. | table |
| table and column names must contain only the following: | A-Z, a-z, 0-9, _, $, # |
| variable-length character data | VARCHAR2 |
| fixed-length character data | CHAR |
| variable-length numeric data | NUMBER |
| These enforce RULES at the table level, and prevent the deletion of a table if there are dependencies. | constraints |
| valid constraint types: | NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK |
| name a constraint using this format | SYS_Cn |
| when to create a constraint? | same time or after creation of table |
| how to define a constraint (at what level)? | column or table level |
| how to view a constraint? | data dictionary |
| constraint that ensures that foreign key values of a table must match primary key values of a related table | referential integrity |
| keyword that defines the column in the child table | FOREIGN KEY |
| keyword that identifies the table and column in the parent table | REFERENCES |
| statement that allows you to change column specifications | ALTER TABLE |
| statement that allows you to rename an existing column | RENAME COLUMN |
| statement that allows you to remove tables from schema | DROP TABLE |
| statement that allows you to recover tables to a specified point in time with a single statement, | FLASHBACK TABLE |
| statement that allows you to rename an existing table | RENAME TABLE |
| statement that removes all rows from a table, leaving the table empty and the table structure intact | TRUNCATE |
| MODULE 7 | DATA MANIPULATION LANGUAGE (DML) AND TRANSACTION CONTROL |
| A DML statement is executed when you: | add new rows, modify existing rows, and remove existing rows |
| It consists of a COLLECTION of DML statements that form a logical unit of work. | transaction |
| statement that add new rows to a table | INSERT INTO |
| example of adding rows to a table | INSERT INTO table_name (attribute1, attribute2) VALUES (value1, value2) |
| example of inserting from another table | INSERT INTO table_name SELECT * FROM table2 WHERE table2attribute = 'X'; |
| use this to prompt for values; a placeholder for the variable value | ampersand (&) |
| statement that modifies existing values in a table | UPDATE |
| example of modifying data in existing rows | UPDATE table SET attribute = value WHERE attribute2 = value2; |
| statement that removes/deletes existing rows from a table | DELETE FROM |
| example of deleting certain rows | DELETE FROM table WHERE attribute = 'hello' |
| database transactions consists of one of the following: | DML statements, one DDL statement, or one DCL statement |
| statement that creates a MARKER in the current transaction | SAVEPOINT |
| statement that rolls back to a marker | ROLLBACK TO SAVEPOINT |
| this guarantees a CONSISTENT view of the data at all times | read consistency |
| statement that LISTS the columns to be RETURNED from the query | SELECT |
| indicates the tables or views FROM which data will be obtained | FROM |
| indicates the conditions under WHICH row will be included in the result | WHERE |
| indicates the categorization of results | GROUP BY |
| indicates the conditions under which a category will be included | HAVING |
| sorts the results according to the specified criteria | ORDER BY |
| use this function to find totals | COUNT |
| this operator allows you to compare strings using wildcards | LIKE '%abc' |
| a virtual data created DYNAMICALLY upon request; data from base table made available to user; no data actually stored | dynamic view |
| copy or replication of data, data actually stored | materialized view |
| MODULE 8 | ADVANCED SQL AND DATA CONTROL LANGUAGE |
| a relational operation that causes two or more tables with a common domain to be COMBINED into a single table or view | JOIN |
| a join in which the joining condition is based on EQUALITY between values in the common columns, which appear redundantly in the result table | equi-join |
| an equi-join in which one of the duplicate columns is ELIMINATED in the result table | natural join |
| a join in which rows that DO NOT HAVE matching values in common columns are nonetheless included in the result table | outer join |
| a join in which rows MUST HAVE matching values in order to appear in the result table; alternative to WHERE clause, and is used to match primary and foreign keys | inner join |
| join that includes ALL columns from each table in the join, and an instance for each row of each table | union join |
| placing an inner query (SELECT statement) inside an outer query | subquery |
| subqueries that are executed once for the entire outer query; do not depend on data from outer query | noncorrelated |
| subqueries that are executed one for EACH ROW returned by the outer query; makes use of data from the outer query | correlated |
| two types of data security | system security and data security |
| type of privileges: PERFORMING a particular action within the database | system privileges |
| type of privileges: MANIPULATING the content of the database OBJECTS | object privileges |
| collection of objects such as tables, views, and sequences | schemas |
| the database administrator creates users with this statement | CREATE USER |
| how to grant a user specific system privileges | GRANT privilege, privilege2 TO username; |
| grant privileges to a role | GRANT create table, create view TO manager; |
| change password using this statement | ALTER USER |
| grant query privileges on the employees table | GRANT select ON employees TO demo; |
| grant privileges to update specific columns to users and roles: | GRANT update (dep_name, loc_id) ON departments TO demo, manager |
| give user to pass along privileges | GRANT select, insert ON departments TO demo WITH GRANT OPTION; |
| allow all users on the system to query data from a department | GRANT select ON name.departments TO PUBLIC; |
| gives other users privileges to access the objects | GRANT |
| creates a collection of privilegs | CREATE ROLE |
| removes privileges on an object from users | REVOKE |
| Which command displays the structure of the table? | describe |
| What command to use when saving changes to the database that is irreversible? | COMMIT |
| returns the transaction back to the marker. | ROLLBACK TO SAVEPOINT |
| users require these to manipulate the contents of the objects in the database | object privileges |
Created by:
samy__