1
Q
What are subtypes/sublanguages of SQL?
A
- DDL, DML, DQL, DCL TCL
2
Q
What is DDL? What are the keywords?
A
- It stands for Data Definition Language
- The keywords are CREATE, ALTER, DROP, TRUNCATE
3
Q
What is DML? What are the keywords?
A
- DML stands for Data Manipulation Language
- The keywords are INSERT, UPDATE, DELETE
4
Q
What is DCL? What are the keywords?
A
- DCL stands for Data Control Language
- The keywords are GRANT and REVOKE
5
Q
What is DQL? What are the keywords?
A
- DQL stands for Data Query Language
- The keyword with DQL is SELECT
6
Q
What is TCL? What are the keywords?
A
- TCL stands for Transaction Control Language.
- The keywords are COMMIT, ROLLBACK, SAVEPOINT
7
Q
What is a Constraint? Give a few examples.
A
- Constraints are rules used to limit the type of data that can go into a table.
Most used constraints include:
- NOT NULL
- UNIQUE
- PRIMARY KEY
- FOREIGN KEY
- CHECK
- DEFAULT
8
Q
What are the different relationships in SQL?
A
- One-to-one - both tables can have only one record on either side of the relationship.
- One-to-many - one table can only have one record on its side of the relationship while
others can have many records of the relationship - Many-to-many - both sides can have many records on either side of the relationship.
9
Q
What are the different types of Joins?
A
- Inner Join - Returns data that occurs in both tables, only where there is a match in both tables.
- Left Join - Returns the data that occurs in the left table and the values that matched records in the right table.
- Right Join - Returns the data from the right table and the matching data from the left table.
- Full Outer Join - Returns all records from both tables and matches them when
possible. - Self Join - Regular join where you join a table with itself.
- Cross Join - Produces a result set in which the number of rows in the first table multiplied by the number of rows in the second table if no WHERE clause is used
along with CROSS JOIN. This kind of result is called a Cartesian Product.
10
Q
What are the set operators?
A
Set operators combine the results of two component queries into a single result.
Queries containing set operators are called compound queries.
- UNION ALL
- INTERSECT
- MINUS
11
Q
What are the Transaction Properties? (ACID)
A
- Atomicity.
- Consistency.
- Isolation.
- Durability.
12
Q
What is Atomicity?
A
- Atomicity - each transaction is all or nothing, if any part of the transaction fails the
whole of it fails, and then the database is rolled back to its last consistent state.
13
Q
What is Consistency?
A
- Consistency - ensures that any transaction will bring the database from one valid state to another.
14
Q
What is Isolation?
A
One transaction has
nothing to do with any of the others.
15
Q
What is Durability?
A
- Durability - ensures that once a transaction has been committed, it will remain committed even in the event of power loss, crashes, or errors.
16
Q
What are the different Isolation Levels? What anomalies does each allow?
A
- Read Uncommitted - allows all anomalies.
- Read Committed - prevents dirty reads, suffers non-repeatable and phantom reads.
- Repeatable Read - prevents non-repeatable reads, suffers phantom reads.
- Serialized - prevents all anomalies including phantom reads
17
Q
What is a Dirty Read?
A
A transaction anomaly that occurs when a transaction is allowed to read data from a row that has been modified by another running transaction that has not been committed yet.
18
Q
What is a Non-Repeatable Read?
A
- A transaction anomaly that occurs when data is read twice in the same transaction
and returns different values because another transaction has modified the data between
the reads.
19
Q
What is a Phantom Read?
A
- Data getting changed in a transaction by other transactions in which rows are added or removed resulting in different result sets.
20
Q
What is 1NF?
A
- To be in 1NF a relation of the database is in first normal form if the domain of each attribute contains atomic values.
21
Q
What is 2NF?
A
To be in 2NF a relation must have all of it is attributes dependent on the relations
primary key.
22
Q
What is 3NF?
A
- To be in 3NF the relation’s attributes must not be determined by non-prime
attributes - Remove Transitive Dependencies or anything that can be defined by another column
23
Q
What is Transitive Dependency?
A
- A transitive dependency in a database is an indirect relationship between values in the same table where one value is determined by another value.
24
Q
What is an Aggregate Function?
A
- An aggregate function is a function where the values of multiple rows are grouped together to form a single value of more significant meaning or measurement.
EX: count()
25
What is a Scalar Function?
- A scalar function is a function that takes data and modifies it, it does not do any calculations with it, just altering the look or returning metadata of the
query.
EX: len(), uppercase(), round()
26
Functions vs Stored Procedures
- Stored Procedures are just a series of steps that are taken at a given point.
- Procedures can have input and output parameters.
- Functions are defined by and must return some value.
- Functions can only have input parameters with predefined output parameters.
27
What is JDBC?
- JDBC - Java Database Connectivity.
| - An API specification for connecting java programs to popular databases
28
What is DAO?
- Stands for Data Access Object
| - The interface for your database
29
Inner Join vs Intersect
- Intersect is used to retrieve the common records from both the left and the right query
of the intersect operator.
- Intersect does all columns
- inner join does only the specified columns.
30
What are the C.R.U.D operations?
- Stands for Create, Read, Update, and Delete.
| - They are the four basic functions of persistent storage.
31
Truncate vs Delete.
- Delete removes the contents of a column.
| - Truncate marks the columns for deallocation.
32
LEFT JOIN vs MINUS.
- MINUS would return everything in set A that are not found in BOTH A and B.
- LEFT JOIN = (A MINUS B) + (A INNER JOIN B).
33
Statement vs PreparedStatement.
- Statement is vulnerable to SQL injection in which an attacker could finish a query
logically and end it with a semicolon and start another query that could potentially harm
the database (Bobby Tables).
- Prepared statements are not vulnerable to SQL injection
34
DROP vs DELETE.
- DROP is a DDL command that removes its arguments from the table or removes the whole table from the database.
- DELETE is a DML command that removes the values from the columns matching it is
arguments.
35
What is a SELF JOIN?
- Self joins are used to compare values in a column with other values in the same column in the same table.
- A practical use for self joins is obtaining running counts and running totals in a SQL query.
36
Inner Join vs Outer Join
- Inner join returns the matching data from two tables.
- Outer join returns the inner join along with the rows in the table which could not be
matched.
37
What is a theta join?
- Theta joins allow for arbitrary comparison relationships (>, =, <=, =).
- Theta joins using an equality operator are called equijoin.
38
What is a natural join?
- an equijoin on attributes that have the same name in each relationship.
- A join operation that creates an implicit join clause for you based on the common
columns in the two tables being joined.
39
All types of joins. You can get asked differences between any of them.
- Inner
- Outer
- Left
- Right
- Natural
- Theta
panel study java2004usf
flashcards
Decks in class (6)
# Cards
