Brainscape's Knowledge GenomeTM

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


See full index

QCWEEK4 > SQL > Flashcards

SQL Flashcards

1
Q

What is an RDBMS?

A
  • It stands for Relational Database Management System which is a program that allows
    you to create, update, and save data in a relational database.
  • Some examples are Oracle, PostgreSQL, MySQL, SQLite.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

What is SQL?

A
  • SQL stands for Structured Query Language and it is a programming language used to
    communicate with the data stored in relational database management system.
  • SQL has sublanguages that are used specifically to either create/alter/drop tables,
    add/update/delete row entries in those tables, control what data you are retrieving from
    the tables, control user permissions, and handle transactions.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

What are subtypes/sublanguages of SQL?

A
  • Sublanguages are a group of commands in SQL used to work with specific parts of a
    database table.
  • DDL, DML, DQL, DCL TCL
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

What is DDL? What are the keywords?

A
  • It stands for Data Definition Language and it is used to define tables and set their
    properties. With it, you can create a table, alter the properties of the table and drop the
    table.
  • The keywords are CREATE, ALTER, DROP, TRUNCATE
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
5
Q

What is DML? What are the keywords?

A
  • DML stands for Data Manipulation Language, The commands of SQL that are used to
    insert data into the database, modify the data of the database and to delete data from
    the database are collectively called as DML.
  • You can insert rows into the table, update rows and delete rows
  • The keywords are INSERT, UPDATE, DELETE
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

What is DCL? What are the keywords?

A
  • DCL stands for Data Control Language, and it is the sub language that is used to grant
    and revoke users of their rights and permissions to a database
  • The keywords are GRANT and REVOKE
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q

What is DQL? What are the keywords?

A
  • DQL stands for Data Query Language. The commands of SQL that are used to retrieve
    data from the database are collectively called as DQL. So, all Select statements comes
    under DQL.
  • The keyword with DQL is SELECT
  • DQL is sometimes included in DML
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

What is TCL? What are the keywords?

A
  • TCL stands for Transaction Control Language.
  • TCL commands are used to manage transactions in database. These are used to
    manage the changes made by DML-statements.
  • The keywords are COMMIT, ROLLBACK, SAVEPOINT
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

What is a Database?

A
  • It is a structured collection of data that is stored in a computer or server and can be
    accessed and manipulated in various ways.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

What are Objects in SQL?

A
  • Objects in SQL are any defined object that can store or reference data.
  • Some objects may encompass other SQL objects.
  • The database itself is a SQL object itself that encompasses all other data objects.
  • The database contains Schema objects that contain table objects and views.
  • Columns are the smallest data object.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q

What are Triggers? When can they execute?

A
  • Triggers are a database object/ block of code that executes one or more SQL
    statements when a specified operation/ event is executed.
  • You can define when a trigger fires when you define the trigger
  • Events are any DML (Delete, Update, Insert)
  • Can specify a chronological relationship to these events (whether they occur before or
    after their execution)
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
12
Q

What is a Schema?

A
  • A schema is a collection of database objects associated with one particular
    database username.
  • The username is called the schema owner, or the owner of the related group of objects.
  • You can have multiple schemas in a database.
    Schema types
  • Snowflake - normalized; different relationships between tables.
  • Star - not normalized; all tables point back to one fact table.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

What is an Index?

A
  • An index is used to speed up the performance of queries.
  • It does so by reducing the number of database data pages that have to be
    scanned/visited.
  • In SQL Server, a clustered index determines the physical order of data in a table.
  • There can only be one clustered index per table (the clustered index is the table).
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q

What is a Sequence?

A
  • A sequence is a user-defined, schema-bound object that generates a sequence of
    numeric values according to the specification with which the sequence was
    created.
  • The sequence of numeric values is generated in an ascending order or descending order
    at a defined interval and can be configured to restart when exhausted.
  • You can use a sequence to define a row by a unique value.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
15
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
16
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
17
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.
- Include:
-
- UNION ALL
- INTERSECT
- MINUS

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

What are the Transaction Properties?

A

ACID

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

What is Consistency?

A
  • Consistency - ensures that any transaction will bring the database from one valid
    state to another. (does not protect for correctness of transaction)
21
Q

What is Isolation?

A
  • Isolation - ensures that the concurrent execution of transactions results in a system state
    that would be obtained if transactions were used sequentially. One transaction has
    nothing to do with any of the others.
22
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. Once a sequence of
    statements execute, the results need to be stored permanently in non-volatile memory
    to defend against these faults.
23
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
24
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.

25
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. (more about update problems)
26
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. (more about insert/delete problems)
27
Q

What is Normalization? What is it used for?

A
  • Normalization is the process of organizing the database into different forms each with
    their own sets of rules.
  • Makes the database more consistent and safe.
  • There are several normal forms.
  • Minimize data redundancy, reduce duplicate data
28
Q

What is 1NF?

A
  • First Normal Form
  • The lowest level of normalization.
  • To be in 1NF a relation of the database is in first normal form if the domain of each
    attribute contains atomic values.
  • This means that fields in the rows of the database cannot be broken into smaller more
    logical parts and contains only one of these parts.
  • Example could be street addresses :
  • Use Street, city, state.
  • Instead of address.
29
Q

What is 2NF?

A
  • Second Normal Form, one level of normalization higher than 1NF.
    To be in 2NF a relation of the database must first be in 1NF.
    To be in 2NF a relation must have all of it is attributes dependent on the relations
    primary key.
30
Q

What is 3NF?

A
  • Third Normal Form, the next step form 2NF.
  • To be in 3NF the relation must first be in 2NF.
  • To be in 3NF the relation’s attributes must not be determined by non-prime
    attributes
  • “All columns must depend on the whole key if they are not part of the key.”
  • Remove Transitive Dependencies or anything that can be defined by another column
31
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.
32
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.
  • An aggregate function takes data and returns an interpolation from that dataset.
  • Examples:
    average()
    count()
33
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.
  • Good examples are:
    Upper case()
    Lower case()
34
Q

WHERE vs HAVING

A
  • A where clause is used to filter records form a result, the filter happens before any
    query is done.
  • Having clause is used to filter an already existing group, having filters results from a
    completed query.
35
Q

Functions vs Stored Procedures

A
  • Functions are defined by and must return some value.
  • Functions can only have input parameters with predefined output parameters.
  • Stored Procedures are just a series of steps that are taken at a given point.
  • Procedures can have input and output parameters.
36
Q

What is JDBC?

A
  • JDBC - Java Database Connectivity.
  • An API specification for connecting java programs to popular databases
  • Done using a driver for the particular dialect.
37
Q

What do you need to get a JDBC Connection?

A
  • First you must have a configured driver.
  • DriverManager loads the specified driver and gets a connection to the database.
  • DataSource is an interface that is preferred to DriverManager because it allows some
    transparency for some parts of the DataSource.
38
Q

What is DAO?

A
  • Stands for Data Access Object
  • The interface for your database
  • Establishes connections and executes queries sent to it.
  • Passes result set.
39
Q

What are the different types of Indexes?

A

Unique
- Guarantees unique values for the column(s) included in the index.
Covering
- includes all of the columns that are used in a particular query(set of queries),
allowing the database to use only the index and not actually have to look at table data to
retrieve the results.
Clustered
- this is a way in which the actual data is ordered on the disk, which means if a query
uses the clustered index for looking up the values, it does not have to take the
additional step of looking up the actual table row for any data not included in the index.

40
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.
  • the intersect operator returns almost the same result as an inner join a lot of the time.
41
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.

42
Q

Truncate vs Delete.

A
  • Truncate is not transactional because it cannot be rolled back.
  • Truncate can modify the object storage attributes so it is not ordinary DML statement.
  • Delete removes the contents of a column.
  • Truncate marks the columns for deallocation.
43
Q

Can I do a subquery in an INSERT statement?

A
  • Yes
44
Q

Which are the main interfaces of JDBC?

A
  • Connection
  • Statement
  • Prepared statement
  • Callable statement
  • Result Set
45
Q

ALTER vs UPDATE.

A
  • Alter is a table scope command (DDL)
  • Update is row scoped (DML)
  • Alter table is altering the number or attributes of the columns and the functions in the
    table.
  • Update is altering the data in the rows of the table.
46
Q

FULL JOIN vs UNION.

A
  • Union combines the results of two or more queries into a single result set that
    includes the rows that belong to all queries in the union.
  • Full Join - Combines the results of both left and right outer joins. The joined table
    will contain all records from both tables and fill in NULLs for missing matches on either
    side. Needs at least one match to join.
47
Q

CLOB vs BLOB.

A

BLOB:

  • Variable-length binary large object string that can be up to 2GB long - Primarily
    intended to hold non-traditional data such as voice or mixed media.
  • BLOB strings are not associated with a character set.

CLOB:

  • Variable-length character large object string that can be up to 2GB long.
  • Can store single-byte character strings or multibyte, character-based data.
  • Considered a character string.
48
Q

FULL JOIN vs UNION.

A
  • Union combines the results of two or more queries into a single result set that
    includes the rows that belong to all queries in the union.
  • Full Join - Combines the results of both left and right outer joins. The joined table
    will contain all records from both tables and fill in NULLs for missing matches on either
    side. Needs at least one match to join.

QCWEEK4 (2 decks)

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