Use LEFT and RIGHT arrow keys to navigate between flashcards;
Use UP and DOWN arrow keys to flip the card;
H to show hint;
A reads text to speech;
70 Cards in this Set
- Front
- Back
|
What is DDL?
|
Data Definition Language [Define Database Schema: CREATING ALTERING, and DROPPING TABLES and CONSTRARINTS -Used by DB developers |
|
|
What is DML?
|
Data Manipulation Language - Query and maintain a database: SELECTING, INSERTING, UPDATING, & DELETING data - Used by DB users |
|
|
What is DCL?
|
Data Control Language - Control database security and access: administrating privileges and committing transactions. -Used by DB Admins |
|
|
What commands are used in DDL?
|
CREATE ALTER DROP CREATE INDEX DROP INDEX CREATE VIEW |
|
|
What Operations are used in DML?
|
SELECT ORDER BY GROUP BY WHERE INSERT UPDATE DELETE AVG HAVING COMMIT ROLLBACK TRUNCATE |
|
|
What are the General SQL Rules
|
Case Insensitive Queries Multi-Line Queries Ending statements with a Semicolon (most of the time). |
|
|
What does the GROUP BY operation do?
|
Used for aggregate functions. GROUP BY returns a single row for each unique combination of the GROUP BY fields |
|
|
How do you create a table?
|
CREATE TABLE tableName ( col1 TYPE(type arguments) [NOT NULL] <- optional col2 TYPE(type arguments) |
|
|
How do you ALTER a table
|
ALTER TABLE tableName ADD CONSTRAINT FK_foreignKey FOREIGN KEY (foreign key column) REFERENCES EMPLOYEE (EMPLOYEE_NUMBER); |
|
|
How do you DROP a TABLE
|
DROP TABLE tableName[CASCADE CONSTRAINTS] <--Optional
|
|
|
What does PURGE do?
|
Completely drops a table/index with no chance of recovery.
|
|
|
How do you Create an INDEX?
|
Example: CREATE INDEX emp_ename ON emp(ename) |
|
|
What does a SELECT statement do?
|
The SQL SELECT statement returns a result set of records from one or more tables. A SELECT statement retrieves zero or more rows from one or more database tables or database views.
|
|
|
What is an UPDATE statement?
|
The UPDATE statement is used to update existing records in a table.
|
|
|
What is an INSERT statement?
|
The INSERT statement adds one or more new rows of data to a database table.
|
|
|
What is a DELETE statement?
|
The DELETE statement removes entire rows of data from a specified table or view.
|
|
|
What is a HAVING clause?
|
A HAVING clause restricts the results of a GROUP BY in a SelectExpression. The HAVING clause is applied to each group of the grouped table, much as a WHERE clause is applied to a select list.
|
|
|
How do you use WHERE with an UPDATE?
|
Example: INSERT INTO bonus SELECT ename, job, sal, comm FROM emp WHERE comm > sal * 0.25; |
|
|
What does the DELETE statement do?
|
The DELETE statement removes entire rows of data from a specified table or view
|
|
|
How do you use a DELETE with a WHERE?
|
Example: DELETE FROM product_descriptions WHERE language_id = 'AR'; |
|
|
How do you DELETE when a row references another table?
|
You must either delete the values in the other row that are being referenced or add a CASCADE ON DELETE to the end of the entity being deleted.
|
|
|
What is a VIEW?
|
It is a "query" behind the scenes. It 'looks like a table and acts like one, but it is not an actual table'.
|
|
|
Explain how % and _ are used in String Comparisons
|
% will match any number of characters, where _ will only match exactly one character
|
|
|
How do you "ALIAS" a column? A table?
|
Column = column_name AS alias_name Table = table_name alias_name |
|
|
Examples of aliasing tables and columns examples
|
SELECT contact_id, first_name || last_name AS "CONTACT NAME"FROM contactsWHERE last_name = 'Anderson'; SELECT p.product_id, p.product_name, categories.category_nameFROM products pINNER JOIN categoriesON p.category_id = categories.category_idORDER BY p.product_name ASC, categories.category_name ASC; |
|
|
What is a cross join?
|
A CROSS JOIN is a JOIN operation that produces the Cartesian product of two tables.
|
|
|
Give a visual representation of an INNER JOIN
|
|
|
|
Give a visual representation of a LEFT OUTER JOIN
|
|
|
|
Give a visual representation of a RIGHT OUTER JOIN
|
|
|
|
Give a visual representation of a FULL OUTER JOIN
|
|
|
|
Give an example of a SELF-JOIN
|
SELECT e1.ename||' works for '||e2.ename "Employees and their Managers" FROM emp e1, emp e2 WHERE e1.mgr = e2.empno
|
|
|
What is the difference between a Normal Subquery and a Correlated Subquery?
|
The correlated subquery references a value in it’s WHERE clause (in this case, it uses a column belonging to Emp1) that is used in the outer query. Normal subqueries create the alias/value within the subquery itself.
|
|
|
What are application performance roadblocks?
|
Poorly written or inefficient code/DB
|
|
|
What are hardware performance roadblocks?
|
Memory shortage, performance shortage, etc. One can fix by splitting operations among multiple disks(RAID), increasing memory for a larger cache, improving processor, dedicated server, etc. |
|
|
What are some Database reading and writing performance roadblocks?
|
Excessive normalization, improper indexing, etc.
|
|
|
How do you set up a trigger? What is the basic structure?
|
CREATE OR REPLACE TRIGGER STUDENT_EXAMPLE_LOGGER AFTER INSERT OR UPDATE ON Student_Example FOR EACH ROW BEGIN INSERT INTO STUDENT_EXAMPLE_LOG VALUES(:NEW.STUDENT_ID, :NEW.STUDENT_FIRST_NAME, :NEW.STUDENT_LAST_NAME, :NEW.STUDENT_CLASSIFICATION, SYSDATE, USER); END;
|
|
|
How do you set up a Procedure?
|
CREATE OR REPLACE PROCEDURE Trigger_TesterISBEGININSERT INTO STUDENT_EXAMPLE (STUDENT_FIRST_NAME, STUDENT_LAST_NAME, STUDENT_CLASSIFICATION) VALUES('Brandon', 'Campbell', 'SR');INSERT INTO STUDENT_EXAMPLE (STUDENT_FIRST_NAME, STUDENT_LAST_NAME, STUDENT_CLASSIFICATION) VALUES('Anna', 'Campbell', 'FR');INSERT INTO STUDENT_EXAMPLE (STUDENT_FIRST_NAME, STUDENT_LAST_NAME, STUDENT_CLASSIFICATION) VALUES('Rainbow', 'Dash', 'JR');
|
|
|
How do you set up a function?
|
CREATE OR REPLACE FUNCTION CALC_PERCENT (NumOne NUMBER, NumTwo NUMBER)RETURN NUMBERISBEGIN RETURN (NumOne/NumTwo)* 100; END;
|
|
|
What is an Active Data Dictionary?
|
It functions in real-time; automatically updates as database structure changes.
|
|
|
What is a Passive Data Dictionary?
|
Not real-time, but can be updated as needed. It is useful for databases whose structure does not change frequently.
|
|
|
What are the characteristics of Data Management/Administration?
|
Data Planning/Coordination Set Data Standards Set Data Usage Policies Liason to logical db designers Data usage/displute resolution (custodians) Data's competitive advantage |
|
|
What are the responsibilities of Database Administration?
|
DB Performance tuning DB User Management DB Security Monitoring Data backup and recovery Data dictionary operations Data and DB maintenance Test/Evaluate DBS, Applications, and utilities Assist with DB design User Training (maybe not) Documentation |
|
|
What are Physical Security concerns?
|
Securing tangible devices such as disk drives. Servers in basements - good for tornados, bad for floods Store servers in an interior space away from windows. Use biometrics/rfid cards, etc. |
|
|
What are Logical Security concerns?
|
Securing non-physical assets such as data |
|
|
What are the 5 areas of security?
|
Access control Authentication Eavesdropping Data Integrity Viruses/Worms |
|
|
What is a Data Dictionary?
|
Stores metadata about database components such as tables, attributes, views, indexes, users, etc.
|
|
|
What is a Transaction?
|
An executing program that forms a logical unit of database processing. |
|
|
What does ACID stand for?
|
A - Atomic, a transaction is a complete unit of work - either performed entirely or not at all. C- Consistent, a transaction's correct execution takes the database from one consistent state to another I - Isolation, Each transaction must appear to execute by itself without interference from other concurrent transactions. D - Durable, changes made to the database must be permanent. |
|
|
What are the Transaction States?
|
START TRANSACTION COMMIT |
|
|
What is Transaction Flow?
|
|
|
|
What is Autocommit?
|
Commits every transaction that runs without errors.
|
|
|
What is a Lost Update?
|
Lost updates occur when two or more transactions select the same row and then update the row based on the value originally selected. Each transaction is unaware of other transactions. The last update overwrites updates made by the other transactions, which results in lost data.
|
|
|
What is a Dirty Read?
|
Dirty Reads occur when a second transaction selects a row that is being updated by another transaction. The second transaction is reading data that has not been committed yet and may be changed by the transaction updating the row.
|
|
|
What is a Non-Repeatable Read?
|
A Non-repeatable read occurs when a second transaction accesses the same row several times and reads different data each time. Inconsistent analysis is similar to uncommitted dependency in that another transaction is changing the data that a second transaction is reading. However, in inconsistent analysis, the data read by the second transaction was committed by the transaction that made the change. Also, inconsistent analysis involves multiple reads (two or more) of the same row and each time the information is changed by another transaction; thus, the term nonrepeatable read.
|
|
|
What is a Phantom Read?
|
Phantom reads occur when an insert or delete action is performed against a row that belongs to a range of rows being read by a transaction. The transaction's first read of the range of rows shows a row that no longer exists in the second or succeeding read, as a result of a deletion by a different transaction. Similarly, as the result of an insert by a different transaction, the transaction's second or succeeding read shows a row that did not exist in the original read.
|
|
|
What is a Schedule?
|
A sequential ordering of operations within multiple transactions.
|
|
|
What is a Read Lock?
|
Data item can be read but not changed by the locking and other transactions.
|
|
|
What is a Write Lock?
|
Data items can be read and changed by the locking transaction, but not by other transactions.
|
|
|
What is a Deadlock?
|
Occurs when two or more transactions tries to update the same data and wait for the other to release its lock.
|
|
|
What is the benefit and drawback of Two-Phase Locking Protocol?
|
It guarantees serializablility, but it can still have deadlocks.
|
|
|
What are the two phases of Two Phase Locking?
|
Growing Phase - Transaction obtains but not release locks, can acquire lock-R, lock-W, or upgrade lock-R to lock-W Shrinking Phase - Transaction releases locks but cannot obtain new locks, can release lock-R, lock-W, or downgrade lock-W to a lock-R |
|
|
What is Timestamp Protocol?
|
A timestamp-based concurrency control algorithm is a non-lock concurrency control method. It is used in some databases to safely handle transactions, using timestamps.
|
|
|
What is Data Mining?
|
Discovery of New InformationThe discovery of new information in terms of patterns or rules from vast amounts of data. Discovery of Interesting StructuresThe process of finding interesting structure in data. Discovery by Computer LearningThe process of employing one or more computer learning techniques to automatically analyze and extract knowledge from data. |
|
|
What is the difference between Data Mining and Data Warehousing?
|
Data Mining versus Data WarehousingA data warehouse is a historical database designed for decision support.Data mining can be applied to the data in a warehouse to help with certain types of decisions.Proper construction of a data warehouse is fundamental to the successful use of data mining.Data mining can be applied to data that is not in a data warehouse, e.g., text mining.
|
|
|
What are the components of Knowledege Discovery in Databases? |
Knowledge Discovery in Databases (KDD) Data Selection – Choosing what data is important Data Cleansing – Correcting or eliminating bad data Enrichment – Enhancing the data with additional information Data Transformation or Encoding – Categorizing or grouping data to reduce the amount of data Data Mining – Mining rules or patterns from the dataReporting and displaying discovered knowledge |
|
|
What are the types of Knowledge discovered during Data Mining?
|
Association RulesA correlation between two sets of values Classification HierarchiesCreating a hierarchical structure from existing data Sequential PatternsDetermining a sequence of steps, actions, or events Patterns within Time SeriesFinding similarities based on timing sequences (snapshots taken a regular intervals) ClusteringSegmenting or partitioning data based on similarities |
|
|
What is Classification and Supervised Learning?
|
ClassificationThe process of learning a model that is able to describe different classes of data.Requires supervised learning because the classes are predetermined.Learning is accomplished by using a training set of pre-classified data.The output is usually in the form ofA decision tree or A set of rules.
|
|
|
What is Clustering and Unsupervised Learning?
|
ClusteringUnsupervised learning or clustering builds models from data without predefined classes.The goal is to place records into groups where the records in a group are highly similar to each other and dissimilar to records in other groups.The k-Means algorithm is a simple yet effective clustering technique.
|
|
|
What are Neural Networks?
|
Neural NetworksA neural network is a set of interconnected nodes designed to imitate the functioning of the brain.Node connections have weights which are modified during the learning process.Neural networks can be used for supervised learning and unsupervised clustering.The output of a neural network is quantitative and not easily understood.
|
|
|
What are Genetic Algorithms?
|
Genetic AlgorithmsGenetic learning is based on the theory of evolution.An initial population of several candidate solutions is provided to the learning model.A fitness function defines which solutions survive from one generation to the next.Crossover, mutation and selection are used to create new population elements.
|
