Brainscape's Knowledge GenomeTM

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


See full index

Revature Questions Week 2 > SQL > Flashcards

SQL Flashcards

1
Q

What is SQL?

A

SQL stands for structured query language and it lets you access and manage databases.

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

What us a unique constraint?

A

The UNIQUE constraint ensures that all values in a column are different.

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

What is a view? Materialized view?

A

A view is a virtual table based on the result set from a SQL statement. Views are not stored in the database and are generated every time. Materialized views are views that are actually stored in the database system.

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

What are primary keys and foreign keys?

A

Primary keys are keys in which uniquely identifies a record in a table. Primary keys must contain unique values and cannot be NULL. A table can only have one primary key.
Foreign keys are just keys used to establish relations between tables. They refer to primary key that exists in another table.

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

What is the order of operations is a SQL statement?

A

The order is FROM, WHERE, GROUP BY, HAVING, ORDER BY, and SELECT

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

How can I create an Alias?

A

The AS keyword just gives a column or table an alias that exits for the duration of the query.

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

What is a RDBMS?

A

.An RDBMS is a type of database management system (DBMS) that stores data in a row-based table structure which connects related data elements.

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

What is a database?

A

A database is a set of data stored in a computer. This data is usually structured in a way that makes the data easily accessible.

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

What are the sublanguages of SQL?

A 
DQL, DDL, DCL, DML, TCL, SCL
DQL - data query language
DDL - data definition language
DCL - data control language
DML - data manipulation language
TCL - transaction control language
SCL - Session control
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

What is cardinality?

A

It is the uniqueness of values in a column. High cardinality refers to a column being unique meaning no duplicate values, low cardinality means there are some duplicates.

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

What is a candidate key?

A

Candidate key is also a unique key to identify a record uniquely in a table but a table can have multiple candidate keys

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

What are some different constraints on columns?

A

Not null, unique, primary key, foreign key, default, check and create index.

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

What is an entity relation diagram?

A

It is a model or graphical representation that shows relationships between different entities in a system.

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

WHERE HAVING

A

So the where clause is used to filter records before grouping is made while the Having clause filters values from a group.

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

ORDER BY GROUP BY

A

Order by sorts the result in either ascending or descending order while group by is used to group rows that have the same values.

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

LIKE

A

A logical operator that checks whether a string matches a patter.

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

SUB QUERY

A

A subquery is a SQL query nested inside a larger query. To use a subquery you can nest it inside another select or update statement but usually they are nested after a WHERE clause.

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

How does BETWEEN work?

A

Between just selects values within a range. This could be numbers, text or dates, and it is inclusive.

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

What is the order of operations is a SQL statement.

A

From, Where, group by, having, order by, select

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

What is difference between aggregate and scalar functions? Examples?

A

Aggregate functions operate on a collection of values and return a single value while scalar functions return a single value based on input. Examples of aggregate are
count(), min(), max(), sum(), avg().
Examples of scalar are
ucase(), lcase(), round(), len()

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

JOINS

A

join, left, right, full,

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

SET OPERATIONS

A

union, union all, intersect, minus
union combines results of 2 or more select statements but doesnt include duplicates
union all same thing just includes duplicates
intersect only returns common
minus returns those only belonging to first set

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

JOIN VS SET

A

Joins combine columns from seperate tables while sets combine rows.

24
Q

TRANSACTION

A

A sequence of operations performed on a database as a single unit of work. If a transaction is successful, all of the data modifications made during the transaction are committed and become a permanent part of the database. If a transaction encounters errors and must be canceled or rolled back, then all of the data modifications are erased.

25
Q

ACID

A

Properties are ACID.
A stands for Atomicity: All changes are performed as a single unit. Either they all are performed or none of them are.
C stands for consistency meaning data is in a consistent state when it starts and ends.
I stands for isolation. Transactions are separate from other transactions until they are finished.
D stands for durability. Means that once a transaction has completed it will remain completed even if there is a system failure.

26
Q

ISOLATION LEVELS

A

Transaction isolation levels are a measure of the extent to which transaction isolation succeeds.
Transaction isolation levels are defined by the presence or absence of the following phenomena: dirty reads, nonrepeatable reads, and phantoms. The four transaction isolation levels are: read uncommitted, read committed, repeatable read, and serializable.

Read uncommited - lowest isolation level in which one transaction may read not yet comitted changes made by another transaction also known as dirty reads.
Read committed - guarantees that any data read is committed the moment it is read. holds a read or write lock on the current row.
Repeatable read - most restrictive isolation level that holds read and write locks on all rows it references to and updates, inserts, deletes to.
Serializable - highest isolation level that ensures all transactions are executed one after another.

27
Q

What are dirty reads, non repeatable reads, and phantom reads?

A

Dirty reads are where one transaction can read uncommitted data of another transaction.

Non repeatable reads occurs when in the same transaction we are retrieving the same row more than once, but the values for that row can be different.

Phantom reads occurs when two same queries are executed, but the rows retrieved by the two, are different.

28
Q

What is normalization?

A

Normalization is a database design technique that reduces data redundancy and eliminates undesirable characteristics like Insertion, Update and Deletion Anomalies. Normalization rules divides larger tables into smaller tables and links them using relationships.

29
Q

1NF, 2NF, 3NF

A

1NF - each table cell should contain a single value, and each record needs to be unique
2NF - Be in 1NF, single column primary key that does not functionally dependant on any subset of candidate key relation.
3NF - Be in 2NF, has no transitive functional dependencies ( when changing a non key column might cause any of the other non key columns to change)

30
Q

What is a dao?

A

Its like an interface that provides us access to a database. It can provide us data operations without exposing details of a database.

31
Q

What is the danger of putting values directly into our queries?

A

A danger is SQL injection attacks. If you have values directly in your queries, it can allow hackers to inject their own values directly to your database and retrieve or alter your data.

32
Q

How do we specify dependencies using sbt?

A

Library dependencies can be added in two ways:
unmanaged dependencies are jars dropped into the lib directory. Most people use managed dependencies instead of unmanaged. But unmanaged can be simpler when starting out. Unmanaged dependencies work like this: add jars to lib and they will be placed on the project classpath.
managed dependencies are configured in the build definition and downloaded automatically from repositories. Most of the time, you can simply list your dependencies in the setting libraryDependencies.

33
Q

What is a port number?

A

An address of an application or process that uses a network or internet to communicate. Used to identify a specific process to which an internet or other network message is to be forwarded when it arrives at a server

34
Q

What is Multiplicity? Examples of 1-1, 1-N, N-N?

A

Multiplicity refers to the number of instances of an entity type that can be associated with the instances of another type.

35
Q

What is CRUD?

A

create, read, update, delete operations.

36
Q

BASE

A

Basically Available: The system is guaranteed to be available in event of failure.
Soft State: The state of the data could change without application interactions due to eventual consistency.
Eventual Consistency: The system will be eventually consistent after the application input. The data will be replicated to different nodes and will eventually reach a consistent state. But the consistency is not guaranteed at a transaction level.

37
Q

What is a database in Mongo?

A

document oriented nosql database that utilizes documents and collections instead of table and rows

38
Q

What is a collection?

A

A collection is a grouping of MongoDB documents. Documents within a collection can have different fields. A collection is the equivalent of a table in a relational database system. A collection exists within a single database

39
Q

What is a document?

A

MongoDB stores data records as documents (specifically BSON documents) which are gathered together in collections.

40
Q

What rules does mongo enforce about the structure of documents inside a collection?

A

The field name _id is reserved for use as a primary key; its value must be unique in the collection, is immutable, and may be of any type other than an array.
Field names cannot contain the null character.

41
Q

What is a distributed application? Distributed date store?

A

A distributed application is a program that runs on more than one computer at the same time and communicates through a network. A distributed data store is a system that stores and processes data on multiple machines.

42
Q

What is high availability? How is it achieved?

A

High Availability means that the SQL Server instances or databases will be available and reachable, with the least possible downtime, in case of any server crash or failure.

43
Q

What kind of nosql database is mongodb?

A

document based where each key is paired with a data structure known as document.

44
Q

What is a namespace in mongodb?

A

The namespace is a combination of the database name and the name of the collection or index

45
Q

What are languages that can be used with mongodb?

A

Java, C#, Python, C/C++, Perl, PHP, Ruby, Scala, Javascript

46
Q

Compare SQL and MongoDB at high level?

A

SQL databases are used to store structured data while NoSQL databases like MongoDB are used to save unstructured data. MongoDB is used to save unstructured data in JSON format. MongoDB does not support advanced analytics and joins like SQL databases support.

47
Q

Does mongodb support foreign key constraints?

A

No

48
Q

Does mongo support Acid?

A

No

49
Q

How can you achieve primary - foreign key relationship in mongdb?

A

No

50
Q

Sharding

A

The procedure of storing records across multiple machines.

51
Q

Replication

A

It is the process of synchronizing data across multiple servers to provide redundancy and increase data availability with multiple copies of data on different database server. Helps in protecting the database from loss of a single server.

52
Q

What is NoSQL Database?

A

NoSQL databases (aka “not only SQL”) are non-tabular databases and store data differently than relational tables. NoSQL databases come in a variety of types based on their data model. The main types are document, key-value, wide-column, and graph. They provide flexible schemas and scale easily with large amounts of data and high user loads.

53
Q

What is referential integrity?

A

It is a database concept that is used to build and maintain logical relationships between tables to avoid logical corruption of data. Usually, referential integrity is made up of the combination of a primary key and a foreign key.

The main concept of REFERENTIAL INTEGRITY is that it does not allow to add any record in a table that contains the foreign key unless the reference table containing a corresponding primary key.

54
Q

Agile

A

Agile is a type of incremental approach to software development based on principles that focuses more on people, results, collaboration, and flexible responses to change. Instead of planning for the whole project, it breaks down the development process in small increments completed in iterations, or short time frames.

55
Q

CAP

A

any distributed data store can only provide two of the following three guarantees:

Consistency
Every read receives the most recent write or an error.
Availability
Every request receives a (non-error) response, without the guarantee that it contains the most recent write.
Partition tolerance
The system continues to operate despite an arbitrary number of messages being dropped (or delayed) by the network between nodes.

Revature Questions Week 2 (1 decks)

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