Week 4 Flashcards

1
Q

What is Amazon RDS?

A

Amazon Relational Database is a web service that makes it easier to set up, operate, and scale a relational database within AWS cloud.

It provides cost efficient, resizable capacity for an industry standard relational database and manages common database administration tasks.

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

Why use RDS?

A

automates expensive and time consuming tasks such as managing backups, software patching, automatic failure detection, and recovery

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

What is SQL?

A

Structured Query Language.

A domain-specific language for working with certain databases called relational databases.

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

What is a relational database?

A

a type of database that stores information in tables - that is, the data is stored in rows and columns.

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

What is DDL?

A

Data Definition Language. These statements are utilized to define the database schema or skeleton. It is how we implement the design structure.

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

Main keywords in DDL?

A

CREATE.
ALTER
DROP
TRUNCATE

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

What does the CREATE keyword do? Which sub language is it used in?

A

It creates new objects or tables.

DDL

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

What does the ALTER keyword do? Which sub language is it used in?

A

It modifies existing objects or tables.

DDL

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

What does the DROP keyword do? Which sub language is it used in?

A

It deletes existing objects or tables.

DDL

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

What does the TRUNCATE keyword do? Which sub language is it used in?

A

It deletes all the data existing within a table leaving the skeleton of the table only.

DDL

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

Can changes be made to DDL operations?

A

No. DDL operations are permanent.

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

What is DML?

A

Data Manipulation Language statements are used to perform CRUD operations on the actual data. Operations are normally performed by row in a relational database.

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

What are the DML keywords?

A

INSERT.
UPDATE.
DELETE.
SELECT.

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

What does the INSERT keyword do? Which sub language is it used in?

A

It inserts a new row into a table.

DML

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

What does the UPDATE keyword do? Which sub language is it used in?

A

It updates one or more rows column values of a table that match a specific WHERE clause.

DML

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

What does the DELETE keyword do? Which sub language is it used in?

A

It deleetes one or more rows of a table that match a specific WHERE clause.

DML

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

What does the SELECT keyword do? Which sub language is it used in?

A

It obtains one or more rows of a table that match a specific WHERE clause. In ORACLE databases this is considered DML. This is how we perform queries in a database.

DML

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

What is DQL?

A

Data Query Language. A sub language where only the SELECT statement exists.

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

Four different DQL Clauses?

A

GROUP BY
HAVING
WHERE
ORDER BY

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

Explain the GROUP BY clause. Which sub language is it used in?

A

This will combine all rows by a column specified in a query and perform any aggregate functions which are stated.

DQL

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

Explain the HAVING clause. Which sub language is it used in?

A

This clause will pass another filter similar to the WHERE clause after everything has been filtered and grouped.

DQL

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

A SQL error will be thrown if you try to perform the ______ clause in a WHERE clause.

A

HAVING

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

What are Scalar Functions?

A

They operate on individual values and will perform some operation per row, and can be used in the SELECT or WHERE clause.

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

What are Aggregate Functions?

A

Aggregate functions operate on multiple values (multiple rows). These functions are used to combine (aggregate) the values existing in one column.

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

What does MAX(COLUMN) return? Which sub language is it used in?

A

It returns the max value on a column.

DQL

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

What does MIN(COLUMN) return? Which sub language is it used in?

A

It returns the minimum value on a column.

DQL

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

What does AVG(COLUMN) return? Which sub language is it used in?

A

It returns the average value of the column.

DQL

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

What does SUM(COLUMN) return? Which sub language is it used in?

A

It returns the sum of the column.

DQL

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

What does COUNT(COLUMN) return? Which sub language is it used in?

A

It returns the count of elements in a column.

DQL

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

Which clause can Aggregate functions be used within?

A

The SELECT clause.

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

What is DCL?

A

Data Control Language statements are used to manage the security and control of database systems.

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

What does the GRANT keyword do? Which sub language is it used in?

A

Grants any permission to an existing user.

DCL

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

What does the REVOKE keyword do? Which sub language is it used in?

A

Revokes any permissions of an existing user.

DCL

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

What is the TCL?

A

Transaction Control Language. These statements are utilized to manage transactions within a relational database.

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

What does the COMMIT keyword do? Which sub language is it used in?

A

Any DML operations that were executed before the statements will be persisted permanently.

TCL

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

What does the ROLLBACK keyword do? Which sub language is it used in?

A

Any DML operations between two COMMIT statments will be completely erased.

TCL

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

What does the SAVEPOINT keyword do? Which sub language is it used in?

A

Utilized to ROLLBACK to a specific point in time.

TCL

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

What is OWASP?

A

The Open Web Application Security Project is an international non-profit organization dedicated to Web Application Security.

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

What is insufficient logging and monitoring?

A

OWASP critical security issue.
Insufficient logging and monitoring is when you are missing security critical information logs or have a lack of proper log format, context, storage, security, and timely response to detect an incident or breach.

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

Define SELECT SQL Command.

A

Extracts data from a database.

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

Define UPDATE SQL Command.

A

Updates data in a database.

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

Define DELETE SQL Command.

A

Deletes data from a database.

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

Define INSERT INTO SQL Command.

A

Inserts new data into a database.

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

Define CREATE DATABASE SQL Command.

A

Creates new database.

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

Define ALTER DATABASE SQL Command.

A

Modifies a database.

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

Define CREATE TABLE SQL Command.

A

Creates a new table.

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

Define ALTER TABLE SQL Command.

A

Modifies a table.

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

Define DROP TABLE SQL Command.

A

Deletes a table.

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

Define CREATE INDEX SQL Command.

A

Creates an index (search key)

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

Define DROP INDEX SQL Command.

A

Deletes an index.

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

When should the producer produce data?

A

When the queue is not full.

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

When should the consumer consume data?

A

When the queue is not empty.

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

How can we solve the producer-consumer problem?

A

We can use the wait() and notify() methods to communicate between producer and consumer threads.

The wait() method to pause the producer or consumer thread depending on the queue size.

The notify() method sends a notification to the waiting thread.

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

What does I/O refer to?

A

The nature of how data is accessed, either by reading it form a resource (input) or writing it to a resource (output).

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

In Java, what are the two categories file I/O can fall into?

A

Streams.

Reader/Writer.

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

Explain Streams category.

A

Streams category is one of two categories relating to file I/O. Streams are for reading or writing bytes.

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

Explain Reader/Writer categories.

A

Reader/Writer category is one of two categories relating to file I/O. Reader/Writer are for reading or writing characters.

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

What does FileInputStream file I/O class do?

A

Reads raw bytes from a file.

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

What does FileOutputStream file I/O class do?

A

Writes raw bytes to a file.

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

What does FileReader file I/O class do?

A

Reads characters from a file.

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

What does FileWriter file I/O class do?

A

Writes characters to a file.

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

What does BufferedReader file I/O class do? What does it need?

A

Reads a file line by line, needs an instance of a FileReader with a path to the resource to be read in the constructor.

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

What does BufferedWriter file I/O class do? What does it need?

A

Writes to a file line by line, needs an instance of a FileWriter with a path to the destination file in the contructor.

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

What does Scanner file I/O class do?

A

Reads from an InputStream, useful methods for character reading.

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

What should you always close with when using I/O classes to read and write? Why?

A

.close()

This prevents exceptions from being thrown later, memory leaks, and system over utilization of unused resources.

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

What is Serialization?

A

The process of writing the state of an object to a byte stream; the reverse is called de-serialization.

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

What must an object do to be serialized?

A

It must implement the Serializable interface.

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

What is the point of a Marker interface?

A

The point is to provide metadata to the compiler.

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

How do you serialize an object?

A

To serialize an Object, you need a FileOutput Stream instance inside the constructor of an ObjectOutputStream, passing in the file path of where oyou want the Object to be serialized call the ObjectOutputStream.writeObject(yourObject) method.

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

How do you De-serialize an object?

A

To deserialize an Object, you need a FileInputStream instance inside the constructor of an ObjectInputStream, passing in the file path of where the serialized object is Call the ObjectInputStream.readObject() method, casting it to a bean of your type

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

What is a Singleton pattern ? Why is it useful?

A

A design pattern which allows the creation of an object in memory only once in an application and can be shared across multiple classes. It can be useful for services in an application, or other resources like a connection or thread pool.

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

The _______ is not created until it is used, often referred to as lazy instantiation.

A

Singleton

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

Which pattern gives the programmer complete control over instantiation?

A

Singleton Pattern.

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

How do you fetch the correct instance in a Singleton Pattern.

A

getInstance()

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

What allows coordination across a system due to the use of only 1 instance?

A

Singleton Pattern.

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

Two drawbacks to a Singleton Pattern?

A

Harder to work with in mulithreading.

Different components can be given too much control over other components.

77
Q

To make a class into a Singleton, use:

A

private static variable of the class type.

private constructor - to prevent arbitrary object creation.

public static getInstance() method, which will either instantiate the object or return the instance in memory.

78
Q

What is a factory pattern?

A

A design pattern which creates objects in which the precise type may not be known until runtime.

79
Q

Which would pattern would you run if you don’t know the exact types needed before running a code?

A

Factory pattern.

80
Q

Which pattern would you run if you want to hide the creational logic, which prevents end user creating things that they shouldn’t.

A

Factory pattern.

81
Q

Which pattern would you use if you need an easy way to extend internal components.

A

Factory pattern.

82
Q

Single responsibility is upheld by putting all of the construction code in a single function is a benefit of what?

A

Factory pattern.

83
Q

To make a factory pattern…

A

Create the abstract data type

Create several classes that inherit the abstract data type (the objects whose instantiation details may not be known until runtime)

Set up a static method whose return type is the abstract data type in (1), which will return the concrete instance

84
Q

What is JDBC?

A

Java Database Connectivity is a relatively low-level API used to write java code that interacts with relational databases via SQL.

85
Q

Where are the JDBC classes and interfaces located?

A

java. sql package

javax. sql package

86
Q

What is the JDBC DriverManager class used for?

A

To make a connection a database driver.

87
Q

What is the JDBC DataSource interface used for?

A

For retrieving connections. It’s an alternative to DriverManager.

88
Q

What is the JDBC Connection interface used for?

A

It represents a physical connection with a database.

89
Q

What is the JDBC SQLException class?

A

A general exception thrown when something goes wrong when accessing the database.

90
Q

What is the JDBC Statement interface?

A

Used for executing static SQL statements.

91
Q

What is the JDBC PreparedStatement interface?

A

Represents pre-compiled SQL statements.

92
Q

What is the JDBC CallableStatement interface?

A

Used to execute stored procedures.

93
Q

What is the JDBC ResultSet interface?

A

Represents data returned from the interface.

94
Q

What are the steps to interact with the database through Java?

A
  1. Register the JDBC Driver.
  2. Open a connection using: Database URL, Username, and Password.
  3. Execute some SQL statement using either: Statement, PreparedStatement, or CallableStatement.
  4. Retrieve the results that are returned in a ResultSet object.
95
Q

An interface that represents a session between java application and database.

A

java.sql.Connection

96
Q

Which class can we use to get a Connection to the database, given that we have the JDBC URL, username, and password?

A

DriverManager class.

97
Q

By default, when a connection is created it is in __________.

A

auto-commit mode.

98
Q

What is the JDBC String?

A

It is the database URL which is an address pointing to the database to be used.

99
Q

What is the Postgres JDBC String URL?

A

org.postresql.Driver

100
Q

What is DAO Design Pattern?

A

A way to logically separate the code that accesses the database into Data Access Objects.

101
Q

To use the DAO Design Pattern, you must….

A

define an interface which declares methods through which the database will be queried.

102
Q

The Statement and PreparedStatement interfaces have additional methods for sending SQL, including:

A

.execute()

.executeUpdate()

103
Q

What does .execute() do ?

A

for any kind of SQL statement, returns a Boolean.

104
Q

What does .executeUpdate() do?

A

for DML statements, returns an int which is the number of rows affected.

105
Q

Results from a SQL query are returned as a _________.

A

ResultSet

106
Q

How do you create an instance of the CallableStatement interface?

A

By using the prepareCall() method on a connection object.

107
Q

What is a database schema?

A

A former structure of data defined in a relational database.

108
Q

How can we visualize the schema of relational databases?

A

through Entity Relationship Diagrams, or ERD’s.

109
Q

What must you do when defining the properties of an entity in the database (i.e the columns)?

A

You must specify the data type to store.

110
Q

What is the function of integrity constraints on specific columns in our database when defining tables?

A

They allow us to enforce the schema by ensuring consistency and integrity of the data in the table.

111
Q

List all of the integrity constraints.

A
PRIMARY KEY
FOREIGN KEY
NOT NULL
UNIQUE 
CHECK
DEFAULT
AUTO INCREMENT
112
Q

What is a primary key constraint?

A

A constraint that uniquely identifies a record in a table.

113
Q

A primary key is inherently composed of which two constraints?

A

UNIQUE

NULL

114
Q

What must be provided when inserting a record into a table, unless the RDBMS system is generating it automatically behind the scenes?

A

A primary key.

115
Q

What is a foreign key constraint?

A

A constraint that signifies that a column represents a reference to the primary key of another table.

116
Q

What is a not null constraint?

A

A constraint that enforces that all records must have a field for the column on which this constraint is applied.

117
Q

We know that every person has a social security number. If we want to store the SSN, what would we consider placing on that field in our users table? Why?

A

not null constraint.

It prevents users of the database from leaving the table in an inconsistent or invalid state. ( similar to unique constraint)

118
Q

What is a check constraint?

A

Provides a way of performing validation on values before records are entered into the table.

119
Q

If I wanted to ensure that a bank account can never have a negative balance, which constraint would we set? Write out code example.

A

check.

CHECK (balance >= 0)

120
Q

What is a default constraint?

A

A constraint that allows setting default values on columns for records that are inserted into the table.

121
Q

What does Auto-increment constraint allow?

A

It allows a unique number to be generated automatically when a new record is inserted into a table.

122
Q

What are candidate keys?

A

A scenario in which there could be multiple columns that together create a primary key to uniquely identify rows.

123
Q

What do we call it once we identify the actual combination of columns to use as the primary key?

A

Composite key.

124
Q

What is referential integrity?

A

This is making sure that our data remains in a consistent state throughout the database when we create table relationships.

125
Q

What are orphan records? When does it happen? How can we prevent this?

A

These are records whose foreign keys do not point to an existing record in the other table.

It happens when we break referential integrity.

One way is by using a setting called CASCADE DELETE.

126
Q

What happens when we enable CASCADE DELETE?

A

When we enable this, deleting a record in the table will also cascade that operation and delete any records in tables that reference the that record via foreign keys.

127
Q

What is a one-to-one relationship?

A

This means that each entity in the table only relates to a single entity in the other table.

128
Q

If we are modeling a school, where each classroom has a single projector in it, which table relationship would we want to have between Classroom and Projector? Which key would we provide the Classroom and Projector tables? Call the keys projector_id and classroom_id. Last, what should we add to enforce this? Why?

A

one-to-one relationship.

We would provide the foreign key projector_id to the Classroom table.

We would provide the foreign key classroom_id to the projector table.

To enforce the one to one aspect, we should apply a unique constraint on the foreign key columns. Otherwise, a use could add another projector record with the same classroom_id as an existing record which would break our one-to-one relationship.

129
Q

What is a one to many, or many to one, relationship?

A

This is where one entity can belong to, own, or otherwise relate to multiple other entities.

130
Q

A student has many books. Which table relationship best represents this? Where do we add the foreign key?

A

One to many or many to one.

You would add a foreign key to the “many” side of the relationship (books).

131
Q

What is a many to many relationship?

A

This implies a one-to-many relationship in both directions on the entities.

132
Q

If we are creating a connection between Students and Teachers in a database, which relationship would this likely represent? Explain how you would do this (use table called Class)

A

Many-to-many.

You would use a junction table. We would create a Class table which contains two foreign keys - one referring to Teacher table’s primary key and one referring to the Student table’s primary key.

133
Q

What do we need to create to connect two tables with a many-to-many relationship.

A

junction table (aka bridge table)

134
Q

What is PL/SQL?

A

This stands for the Procedural Language extension to SQL and it is a complete programming language which also allows SQL statements.

135
Q

What is a sequence?

A

An object which holds a numeric number that starts from a certain point and it also contains a max. It increments by a specific amount every time NEXTVAL is called.

136
Q

What can be combined with Triggers to auto increment primary key columns?

A

Sequence.

137
Q

What is a Trigger?

A

A block of code that executes when a specific event happens. These events can be INSERT, UPDATE, or DELETE statements and they can happen AFTER or BEFORE.

138
Q

What can be used to loop programmatically on the output of a SELECT statement (similar to iterators in Java)?

A

Cursor.

139
Q

What is a stored procedure?

A

PL/SQL code that can be executed in certain ways and has some properties.

140
Q

Stored Procedures don’t return anything. T/F?

A

True.

141
Q

What may or may not contain IN (by value) and OUT (by reference) parameters?

A

Stored Procedure.

142
Q

What allows any DML statements within it? What does it mean can be created?

A

Stored Procedures.

This means transactions can be created in a stored procedure.

143
Q

What can call other procedures and functions?

A

Stored procedures.

144
Q

What is a User Defined Function?

A

These are like stored procedures but have some other restrictions or abilities.

145
Q

What are the restrictions and abilities of User Defined Functions?

A
  • They must return something.
  • Cursors are allowed.
  • It should be a single value.
  • They may or may not contain IN parameters (by default)
  • Only SELECT statements are allowed.
  • Functions can only call other functions.
  • ## They can be used in any DML statement.
146
Q

To call a function, you have to use a ________.

A

DML statement.

147
Q

Use _______ if you are not selecting from a specific table.

A

FROM DUAL

148
Q

_______ is a dummy table which returns anything you throw at it.

A

DUAL

149
Q

What are Types?

A

Similar to classes, they can be used as your own data types for columns. This is a high level technique.

150
Q

What is a VARRAY?

A

Arrays of any Oracle data type or your own type, they can be used for columns.

151
Q

What is a Nested Table?

A

Infinite arrays, they can be seen as tables in columns. They have to be of a specific data type.

152
Q

Two main types of Indexes?

A

Clustered - index that alters the order in which the records are physically stored on a disk. Only one clustered index can be created on a given table.

NonClustered - index that specify a logical ordering of rows but do not affect the physical ordering, so there may be more than one non-clustered index in a table.

153
Q

Explain the WHERE clause. Which sub language is it used in?

A

The WHERE clause is used to filter records. It is used to extract only those records that fulfill a specified condition.

DQL

154
Q

Explain the ORDER BY clause. What is the default for this clause? Which sub language is it used in?

A

The ORDER BY clause is used to sort the result-set in ascending or descending order. The default is ascending. If you want to sort descending, use the DESC keyword.

DQL.

155
Q

What are Joins?

A

Used to combine two or more tables. Joins are a database technique used in SELECT statements. Normally performed comparing primary keys to foreign keys.

156
Q

What is an INNER JOIN?

A

Most common used type of join, returns rows only if the columns specified in the join clause match.

157
Q

What is FULL OUTER JOIN?

A

Returns all records when there is a match in either left or right table.

158
Q

What is LEFT OUTER JOIN?

A

Returns all records from the left table, and the matched records from the right table.

159
Q

What is RIGHT OUTER JOIN?

A

Returns all records from the right table, and the matched records from the left table.

160
Q

What is a CallableStatement Interface?

A

used to execute stored procedures. You can create by calling the prepareCall() method on a connection object.

161
Q

What is Normalization?

A

An optimization process of structuring a relational database in a way that reduces redundancy of data and improves data integrity and consistency.

162
Q

Name and explain the first three normal forms.

A

1NF - must have a primary key, no repeating groups, and atomic columns
2NF - must already be in 1NF, plus have no partial dependencies
3NF - must already be in 2NF, plus have no transitive dependencies

163
Q

The first normal form enforces that a table must:

A

Have a primary key.

Have each column be as granular as possible.

164
Q

To be in second normal form, a table must also:

A

Not have columns that are dependent on only one part of the key. If there are no composite primary keys, you are automatically in 2NF.

165
Q

To get to the third normal form, a table must also:

A

Not have transitive dependencies. This mean if column C relates to column B which relates to column A which is the primary key, this is not in 3NF because C is related to the primary key but indirectly (transitive dependency).

166
Q

What is a sub-query?

A

A nested query. It is a query within another SQL query and embedded within the WHERE clause. It is used to return data that will be used in the main query as a condition to further restrict the data to be retrieved.

167
Q

What does a properties file do?

A

Stores information as key value pairs, each on their own line; the file has a .properties extension.

168
Q

What is Mockito?

A

A Java framework used for mocking and unit testing Java applications. Internally, it uses the Reflection API.
Mockito simplifies the process of testing by creating mock objects and avoiding external dependencies. It is used with other testing frameworks like JUnit or TestNG.

169
Q

What is Mockito Spy?

A

A spy is like a partial mock, which will track the interactions with the object like a mock. Additionally, it allows us to call all the normal methods of the object. Whenever we call a method of the spy object, the real method will be invoked.

170
Q

What are the 4 properties of ACID?

A

Atomicity.
Consistency.
Isolation.
Durability.

171
Q

What is Atomicity?

A

Either all of the transactions will execute successfully or none of them will.

172
Q

What is Consistency?

A

Constraints are enforced for every committed transaction. That indicates that all Keys, Data types, Checks, and Triggers are successful and no constraint violation is triggered.

173
Q

What is Isolation?

A

Guarantees that concurrently running transactions should not affect each other.

174
Q

What is Durability?

A

Guarantees all changes are recorded in the database once complete. This protects against a system crash or power outage.

175
Q

What are the 4 Isolation levels?

A

Serializable.
Repeatable Reads.
Read Committed.
Read Uncommitted.

176
Q

List some attributes of the Serializable Iso level.

A

Allowed in Oracle.
Read/Write locks.
Applies range locks even in the WHERE clauses of a select statement.
Phantom reads can’t happen because of this.
Table that is being read can’t be modified until the reading is done (no INSERTS, no UPDATES, no DELETES).

177
Q

List some attributes of the Repeatable Reads Iso level.

A

Not used often.
Read/Write locks.
Doesn’t provide range locks, that means phantom reads can happen.
Doesn’t lock the whole SELECT statement, nor INSERTS, nor UPDATES, nor DELETS.

178
Q

List some attributes of the Read Committed Iso level.

A

Oracle default.
Write only locks.
Only data that is committed will be seen by other transactions.
Dirty reads can’t happen, but Phantom reads can.
This is why it is recommended to not perform very long transactions.

179
Q

List some attributes of the Read Uncommitted Iso level.

A

A disaster.
Dirty reads are normal, any transaction can see any uncommitted data.
Very inconsistent.

180
Q

What is a Dirty Read?

A

Reading data that is uncommitted.

181
Q

What is a non-repeatable read?

A

when a row is read twice in a transaction and the values are different.

182
Q

What is a Phantom Read?

A

Reading data that is being added or modified by a running transaction.

183
Q

What are Set Operators? How are they different from Joins?

A

Set operators combine the rows of different result sets, and perform some kind of (set) operation on two different queries.

Joins combine columns of two tables.

184
Q

Explain UNION and UNION [ALL] set operators.

A

UNION does not keep duplicates.

UNION ALL does keep duplicates.

185
Q

What does the INTERSECT Set Operator do?

A

Returns records in common between the queries.

186
Q

What does the MINUS Set Operator do?

A

Removes from the first result set any rows that appear in the second result set and returns what remains.

187
Q

What does the EXCEPT Set Operator do?

A

Same as MINUS, but for SQLServer instead of Oracle.

188
Q

What is AWS S3?

A

A program that’s built to store, protect, and retrieve data from “buckets” at any time from anywhere on an y device.

189
Q

How does AWS S3 work?

A

Focuses on two key components. Buckets and objects that work together to create the storage system.
The S3 environment is a flat structure — a user creates a bucket; the bucket stores objects in the cloud.