Brainscape's Knowledge GenomeTM

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


See full index

panel study java2004usf > SQL > Flashcards

SQL Flashcards

1
Q

What are subtypes/sublanguages of SQL?

A
  • DDL, DML, DQL, DCL TCL
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

What is DDL? What are the keywords?

A
  • It stands for Data Definition Language

- The keywords are CREATE, ALTER, DROP, TRUNCATE

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

What is DML? What are the keywords?

A
  • DML stands for Data Manipulation Language

- The keywords are INSERT, UPDATE, DELETE

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

What is DCL? What are the keywords?

A
  • DCL stands for Data Control Language

- The keywords are GRANT and REVOKE

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

What is DQL? What are the keywords?

A
  • DQL stands for Data Query Language

- The keyword with DQL is SELECT

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

What is TCL? What are the keywords?

A
  • TCL stands for Transaction Control Language.

- The keywords are COMMIT, ROLLBACK, SAVEPOINT

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
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
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
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.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
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.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
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
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q

What are the Transaction Properties? (ACID)

A
  • Atomicity.
  • Consistency.
  • Isolation.
  • Durability.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
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.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

What is Consistency?

A
  • Consistency - ensures that any transaction will bring the database from one valid state to another.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q

What is Isolation?

A

One transaction has

nothing to do with any of the others.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
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.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
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
Q

What is a Scalar Function?

A
  • 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
Q

Functions vs Stored Procedures

A
  • 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
Q

What is JDBC?

A
  • JDBC - Java Database Connectivity.

- An API specification for connecting java programs to popular databases

28
Q

What is DAO?

A
  • Stands for Data Access Object

- The interface for your database

29
Q

Inner Join vs Intersect

A
  • 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
Q

What are the C.R.U.D operations?

A
  • Stands for Create, Read, Update, and Delete.

- They are the four basic functions of persistent storage.

31
Q

Truncate vs Delete.

A
  • Delete removes the contents of a column.

- Truncate marks the columns for deallocation.

32
Q

LEFT JOIN vs MINUS.

A
  • 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
Q

Statement vs PreparedStatement.

A
  • 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
Q

DROP vs DELETE.

A
  • 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
Q

What is a SELF JOIN?

A
  • 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
Q

Inner Join vs Outer Join

A
  • 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
Q

What is a theta join?

A
  • Theta joins allow for arbitrary comparison relationships (>, =, <=, =).
  • Theta joins using an equality operator are called equijoin.
38
Q

What is a natural join?

A
  • 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
Q

All types of joins. You can get asked differences between any of them.

A
  • Inner
  • Outer
  • Left
  • Right
  • Natural
  • Theta

panel study java2004usf (6 decks)

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