SQL

View Syntax:

"CREATE VIEW view_name AS SELECT column_name(s) FROM table_name WHERE condition"

How do you prevent SQL injection?

"Option #1: Use of Prepared Statements (Parameterized Queries) Option #2: Use of Stored Procedures Option #3: Escaping all User Supplied Input Also Enforce: Least Privilege Also Perform: White List Input Validation"

How do you add a column to the table?

"The ALTER TABLE statement is used to add, delete, or modify columns in an existing table. ALTER TABLE table_name ADD column_name datatype"

What is a composite key?

A primary or foreign key built out of two or more columns.

What is a sequence?

A sequence is a user defined schema that will create a counter, unique integers. Syntax: CREATE SEQUENCE seq_name START WITH val1 INCREMENT BY val2

What is a transaction?

A transaction is an atomic unit of work that is a sql statement, which is committed at the end of the transaction if it does not fail. If it fails, it should rollback to the start of the transaction.

What is ACID?

Atomic: all statements execute or none do. Consistent: After a transaction, the database is in a valid state by following the constraints of the table Isolated: concurrent transactions will result in the same data as if the transactions were back to back. Durability: Data is persisted, even in the event of power loss.

What language contains GRANT and REVOKE?

DCL Data Control Language

What language contains DROP and TRUNCATE?

DDL: Data Definition Language

What's an ERD?

Entity Relationship Diagram: A Data modeling technique that illustrates the relationship between tables and represent the entity framework infrastructure.

Explain the syntax of a for loop.

For statements iterate over rows in a defined read only result set. A cursor is implicitly declared. For statements make it easy to retrieve a set of column values for a set of rows. Example: CREATE PROCEDURE P() LANGUAGE SQL BEGIN ATOMIC DECLARE fullname CHAR(40); FOR v AS cur1 CURSOR FOR SELECT firstnme, midinit, lastname FROM employee DO SET fullname = v.lastname || ',' || v.firstnme ||' ' || v.midinit; INSERT INTO tnames VALUES (fullname); END FOR; END

Why would you want to do a self join?

I would want to do a self join if there is a relationship between rows in a table. ' For example, I could use a self join to get a list of employees and their immediate managers.

How do you add a record to the table?

INSERT INTO table_name VALUES (val1, val2, val3)

What is the purpose of an index?

Indexes allow the database to find data without reading the whole table.

What is the difference between JOINS and UNIONS?

Joins act on tables. Unions act on result sets

Can a row taht is being referenced be deleted before the referencing row is?

No.

Is NULL a constraint?

No. NOT NULL is a constraint.

What sql statement will return the number of records in a table emp

SELECT COUNT(*) FROM emp

What are some of the String manipulation methods in SQL?

SUBSTRING, TRIM, LTRIM, RTRIM, REVERSE, REPLACE, LENGTH, CONCAT, UCASE, LCASE, etc.

What does the function TO_DATE do?

TO_DATE converts a character string or numbers into a date data type.

Why can't you rollback a TRUNCATE command?

TRUNCATE is a ddl command. The commit already happened. It is not logged.

Let's say we have two tables and they have columns 1, 2, 3, 4, and 5 in common. How to select these columns from both tables without losing data?

Union All

How do you add a record to a table?

insert into table_name(col1, col2, ...)

What is the difference between order by and group by?

order by is used for sorting results. group by is used with aggregate functions to group results.

what are the transaction isolation levels?

read uncommitted read committed repeatable reads serializable

What are some oracle datatypes?

varchar2 number float long date timestamp blob clob raw

What is a transitive dependency?

when column c depends on column b that depends on the pk column a

What is the difference between a global table and a local table in SQL?

"A local temp table is visible to the creator that is connected only, disbands after disconnect. A global temp table is saved in the database and visible by all users, even after the initial creating user disconnects."

How do you change the column order of an existing table in SQL that has data in it?

"Assuming columns: a, b, c, d (As they appears in a 'select *'), You would have to indicate specifically which columns you want, in the order you want: SELECT d,a,b,c FROM TABLE; (You could also recreate the table)"

"What does REPLACE do? Use an example"

"Replace a subset in a column with a replacement, for all elements that apply. SELECT REPLACE( COLUMN_A, 'existing', 'replaced') from TABLENAME."

How do you add a record to a table?

"The INSERT statement. The basic syntax to insert a value for each and every column is: INSERT INTO TABLE VALUES( ___ , ___ , ___ , ___ ) To insert only specific columns and leave others null: INSERT INTO TABLE(col1, col2) VALUES( ___ , ___ )"

What is a nested query (or subquery)?

A Subquery or Inner query or Nested query is a query within another SQL query and embedded within the WHERE clause. A subquery is used to return data that will be used in the main query as a condition to further restrict the data to be retrieved. Subqueries can be used with the SELECT, INSERT, UPDATE, and DELETE statements along with the operators like =, <, >, >=, <=, IN, BETWEEN etc.

We have a table without primary key. How do you add a column that is a primary key and auto increamenting?

ALTER table tablename ADD ColumnID INTEGER PK AUTOINCREAMENT

What is the purpose of AND and OR?

AND and OR connect multiple WHERE clauses together to restrict results

What is an ERD?

An entity relationship diagram is an illustration of entities and their relationships.

What's an index?

An index can be created in a table to find data more quickly and efficiently. Indexes allow the database application to find data fast; without reading the whole table. The users cannot see the indexes, they are just used to speed up searches/queries. Updating a table with indexes takes more time than updating a table without (because the indexes also need an update). So you should only create indexes on columns that will be frequently searched against. You can use indexes or unique indexes (which require a unique value).

What languages make up SQL?

DDL, DML, DCL, TCL, DQL - data definition language, data manipulation language, and data control language, transaction control language, data query language

What are the sql sub language commands?

DDL: CREATE, DROP, ALTER, TRUNCATE DCL: GRANT, REVOKE TCL: COMMIT, ROLLBACK DQL: SELECT DML: CRUD, INSERT, SELECT

What is the difference between WHERE and HAVING?

HAVING specifies a search condition for a group or an aggregate function used in SELECT statement. WHERE cannot be used to apply restrictions to an aggregate function. SYNTAX: SELECT column_name(s) FROM table_name WHERE condition GROUP BY column_name(s) HAVING condition ORDER BY column_name(s); EXAMPLE: The following SQL statement lists the number of customers in each country. Only include countries with more than 5 customers: Example SELECT COUNT(CustomerID), Country FROM Customers GROUP BY Country HAVING COUNT(CustomerID) > 5;

How would use IN and EXIST? What is the difference?

IN works best for a small finite set of data, whereas EXISTS is better for subqueries. In most cases, EXISTS will be much more efficient (and faster) than an IN statement. When using an IN combined with a subquery, the database must process the entire subquery first, then process the overall query as a whole, matching up based on the relationship specified for the IN. With an EXISTS or a JOIN, the database will return true/false while checking the relationship specified. Unless the table in the subquery is very small, EXISTS or JOIN will perform much better than IN.

What is an implicit cursor?

Implicit cursors are automatically created by Oracle whenever an SQL statement is executed, when there is no explicit cursor for the statement. Programmers cannot control the implicit cursors and the information in it. Whenever a DML statement (INSERT, UPDATE and DELETE) is issued, an implicit cursor is associated with this statement. For INSERT operations, the cursor holds the data that needs to be inserted. For UPDATE and DELETE operations, the cursor identifies the rows that would be affected.

What is a view?

In SQL, a view is a virtual table based on the result-set of an SQL statement. A view contains rows and columns, just like a real table. The fields in a view are fields from one or more real tables in the database. You can add SQL functions, WHERE, and JOIN statements to a view and present the data as if the data were coming from one single table.

What is clustered vs non clustered indexing?

Indexing is used to speed up searching. An index contains keys built from one or more columns. clustered indexes sort and store the data rows in the table or view based on their key values. Non clustered indexes: have a structure separate from the data rows. It contains the key values and each key value entry has a pointer to the row that contains the key value.

What is the difference between INNER JOIN and OUTER JOIN?

Inner join will return only the rows that actually match based on the join predicate. An outer join will also return rows that are not common between both tables.

Can you have more than one primary key in one table? Can a primary key contain more than one column?

No. A table can have one and only one primary key constraint. However, a primary key can contain more than one column. A composite key is a combination of two or more columns in a table that can be used to uniquely identify each row in the table. Uniqueness is only guaranteed when the columns are combined; when taken individually the columns do not guarantee uniqueness.

How to go about unifying two table that have a similar structure, except one column is different in data type?

One method is to cast the int into a string. Or if the string represents a number, you could cast the string into an int

What is an orphan?

Orphaned records are records that reference a key which no longer exists in the foreign table. If referential integrity is enforced by using a foreign key constraint, this cannot happen.

What is referential integrity?

Referential integrity means that the foreign key in any referencing table must always refer to a valid row in the referenced table. Referential integrity ensures that the relationship between two tables remains synchronized during updates and deletes.

How would you get the minimum salary from emp of all departments?

SELECT MIN(sal) FROM emp GROUP BY dept

How would you select the top 10% of rows from a table in SQL?

SELECT TOP 10% * FROM TABLENAME;

How would you write a query to grab the first 'X' amount of records?

SELECT TOP X FROM TABLENAME;

How is SELECT different from the other DML commands?

SELECT does not manipulate data. SELECT asks to query data. INSERT, DELETE, etc, manipulate the data in the table. SELECT can be put in its own category, DQL, Data Query Language.

sql in a Statement Object is compiled by what? sql in a prepared Statement Object is compiled by what?

Statement objects are compiled by the database. Prepared statement objects are compiled by java.

What object do you get back from a statement?

Statement objects generate ResultSet objects. A Result Set is a table of data representing a database result set.

What does the INSERT statement do?

The INSERT INTO statement is used to insert new records in a table.

What is UNION, UNION ALL, MINUS, INTERSECT? In these statements, what conditions must be met?

The UNION operator returns only distinct rows that appear in either result, while the UNION ALL operator returns all rows. The UNION ALL operator does not eliminate duplicate selected rows. The INTERSECT operator returns only those rows returned by both queries.The MINUS operator returns only unique rows returned by the first query but not by the second. The corresponding expressions in the select lists of the component queries of a compound query must match in number and must be in the same datatype group (such as numeric or character).

What are triggers?

Triggers are stored programs in pl/sql and are fired when some event occurs. Syntax: CREATE OR REPLACE TRIGGER triggername BEFORE INSERT on tablename FOR EACH ROW BEGIN ... END

How can you retrieve rows from a database table?

Using a SELECT statement (aka query). The basic syntax is: "SELECT [ column_list | * ] [FROM table] [JOIN table ON join_predicate] ... [WHERE where_condition] [GROUP BY {col_name} [HAVING condition] [ORDER BY {col_name} [ASC | DESC]]"

What is the difference between WHERE and HAVING

WHERE cannot apply restrictions to an aggregate function. HAVING: specifics a condition for a group or an aggregate function used in SELECT.

What is GRANT and REVOKE?

You can grant users various privileges to tables and revoke those privileges. These privileges can be any combination of SELECT, INSERT, UPDATE, DELETE, REFERENCES, ALTER, INDEX, or ALL. GRANT privileges ON object TO user; REVOKE privileges ON object FROM user;

When would you want to use case statement in SQL?

You would want to use the case statement to give meaningful descriptions of cryptic values and/or specify the translation of the column

Aggregate vs scalar functions

aggregate: return a value calculated from values in a column. example: avg, count, first, list, max, min, sum scalar: returns a value based on input example: ucase, lcase, mid, len, round

Difference between Procedure and Functions

procedure can return multiple values. function only returns one value

What is the purpose of SELECT INTO?

"The SELECT INTO statement selects data from one table and inserts it into a new table. SELECT * INTO new_table FROM table1"

How would you change a table's column name?

ALTER TABLE table_name RENAME COLUMN old_name TO new_name

Explain the differences between DELETE and TRUNCATE

DELETE operations can be rolled back. TRUNCATE operations cannot be rolled back. DELETE is a DML command. TRUNCATE is a DDL command. They both delete records from a table, but DELETE using a WHERE can specify which records to delete.

What is a cascade delete?

"A foreign key with cascade delete means that if a record in the parent table is deleted, then the corresponding records in the child table will automatically be deleted. This is called a cascade delete in SQL Server. A foreign key with cascade delete can be created using either a CREATE TABLE statement or an ALTER TABLE statement."

What is an entity relational diagram (ERD)?

"An entity-relationship diagram—otherwise known as an ERD—is a data modeling technique that creates an illustration of an information system's entities and the relationships between those entities. There are 3 ingredients in a standard entity-relationship diagram: •Entities, which represent people, places, items, events, or concepts. •Attributes, which represent properties or descriptive qualities of an entity. These are also known as data elements. •Relationships, which represent the link between different entities. Entities, attributes, and relationships can be represented in one of three ways: with a conceptual model, logical model, or physical model. These models increase in complexity as you move from conceptual to logical to physical. It's usually best to start with a conceptual ERD model, so you can understand—at the highest level—the entities in your data and how they relate to each other. As you transform a conceptual ERD to a physical model, you'll learn exactly how to implement modeled information into the database of your choice. There are two main types of ERD notations: Crow's Foot notation and Chen notation."

What is an explicit cursor?

"Explicit cursors are programmer defined cursors for gaining more control over the context area. An explicit cursor should be defined in the declaration section of the PL/SQL Block. It is created on a SELECT Statement which returns more than one row. CURSOR cursor_name IS select_statement; Working with an explicit cursor involves four steps: Declaring the cursor for initializing in the memory Opening the cursor for allocating memory Fetching the cursor for retrieving data Closing the cursor to release allocated memory"

What is the difference between LEFT JOIN, RIGHT JOIN, and FULL JOIN?

"INNER JOIN: Returns all rows when there is at least one match in BOTH tables LEFT JOIN: Return all rows from the left table, and the matched rows from the right table RIGHT JOIN: Return all rows from the right table, and the matched rows from the left table FULL JOIN: Return all rows when there is a match in ONE of the tables"

How would you go about deleting duplicate rows in a table in SQL?

"Identify duplicates via a query that utilizes a count(*) aggregate, where the count is greater than 1. Set row count to amount of total specific duplicates, then delete the records individually."

Difference between JDBC and ODBC

"JDBC provides a connection between front end java applications to backend databases. ODBC provides the same services, except for other applications other than java."

What is a cross join? Difference between cross join and cartesan ?

"Joins two tables via a cartesian product. (All possible combinations for each table's entities). Cross join and certesian are the same."

What are various constraints in SQL?

"NOT NULL - Indicates that a column cannot store NULL value UNIQUE - Ensures that each row for a column must have a unique value PRIMARY KEY - A combination of a NOT NULL and UNIQUE. Ensures that a column (or combination of two or more columns) have a unique identity which helps to find a particular record in a table more easily and quickly FOREIGN KEY - Ensure the referential integrity of the data in one table to match values in another table CHECK - Ensures that the value in a column meets a specific condition DEFAULT - Specifies a default value for a column"

What is normalization?

"Normalization is the process of organizing data in a database. This includes creating tables and establishing relationships between those tables according to rules designed both to protect the data and to make the database more flexible by eliminating redundancy and inconsistent dependency. There are six normal forms, though many organizations only use the first three. (1NF) Eliminate repeating groups in individual tables. Create a separate table for each set of related data. Identify each set of related data with a primary key. (2NF) A table is in 2NF if it is in 1NF and every non-key attribute of the table is dependent on the primary key as a whole. Therefore, a table is automatically in 2NF if the table does not use a composite key. (3NF) The table is in third normal form if it is in 2NF and every non-key attribute is not dependent upon any non-key attribute (aka transitive dependency)"

What are one-to-one, one-to-many and many-to-many relationships, and how do you implement?

"One-to-One (1:1) relationship is defined as the relationship between two tables where both the tables should be associated with each other based on only one matching row. This relationship can be created using Primary key-Unique foreign key constraints. With One-to-One Relationship in SQL, for example, a person can have only one passport. The One-to-Many (1:M) relationship is defined as a relationship between two tables where a row from one table can have multiple matching rows in another table. This relationship can be created using Primary key-Foreign key relationship. In the One-to-Many Relationship in SQL, for example, a book can have multiple authors. A Many-to-Many (M:N) relationship is defined as a relationship between two tables where many rows from one table can have multiple matching rows in another table. Neither table can support a foreign key to relate the tables, so a junction table (aka join table or associative entity) is created. A junction table is a database table that contains foreign key references to two or more other database tables. It is the standard way of creating a many-to-many relationship between tables."

What is a cursor?

"Oracle creates a memory area, known as context area, for processing an SQL statement, which contains all information needed for processing the statement, for example, number of rows processed, etc. A cursor is a pointer to this context area. PL/SQL controls the context area through a cursor. A cursor holds the rows (one or more) returned by a SQL statement. The set of rows the cursor holds is referred to as the active set." You can name a cursor so that it could be referred to in a program to fetch and process the rows returned by the SQL statement, one at a time. There are two types of cursors: Implicit cursors and Explicit cursors

What does the DISTINCT keyword do. Use it in an example.

"Returns the unique records from a query. Select distinct * from tablename."

Give me an example of a case statement that would write 'A' for numbers less than 1000, 'B' for numbers greater than 1000, and 'C' otherwise.

"SELECT A,B, CASE WHEN C < 1000 Then 'A' WHEN C > 1000 THEN 'B' ELSE 'C' as 'C' FROM TABLE "

JOIN vs UNION

"SQL JOIN allows us to "lookup" records on other table based on the given conditions between two tables. For example, if we have the department ID of each employee, then we can use this department ID of the employee table to join with the department ID of department table to lookup department names. UNION operation allows us to add 2 similar data sets to create resulting data set that contains all the data from the source data sets. Union does not require any condition for joining. For example, if you have 2 employee tables with same structure, you can UNION them to create one result set that will contain all the employees from both of the tables. "

What is an aggregate function?

"SQL aggregate functions return a single value, calculated from values in a column. •AVG() - Returns the average value •COUNT() - Returns the number of rows •FIRST() - Returns the first value •LAST() - Returns the last value •MAX() - Returns the largest value •MIN() - Returns the smallest value •SUM() - Returns the sum"

What is a scalar function?

"SQL scalar functions return a single value, based on the input value. •UCASE() - Converts a field to upper case •LCASE() - Converts a field to lower case •MID() - Extract characters from a text field •LEN() - Returns the length of a text field •ROUND() - Rounds a numeric field to the number of decimals specified •NOW() - Returns the current system date and time •FORMAT() - Formats how a field is to be displayed"

Why do we use stored procedures?

"Security: Provides advanced database functionality for users who wouldn't normally have access to these tasks. Faster execution: Stored procedures are parsed and optimized as soon as they are created and then stored in the database memory. This means that they execute a lot faster than sending many lines of SQL code from your application. Reduced network traffic: If you send many lines of SQL code over the network to your database server, this will have an impact on network performance. Reusable: You can write a stored procedure once, then call it from multiple places in your application."

"Compare flags If category is 'AAA' then flag is 'F' If category is 'AA' and value is <= 2000 then flag is 'G' If category is 'AA' and value is > 2000 then flag is 'H' If not any of these then flag is 'J' Compare to flag to expected flag Use a case statement to generate different flag values as expected_flag and compare to actual flag column"

"Select Category, CValue, CASE WHEN Category = 'AAA' THEN 'F' WHEN Category = 'AA' AND Cvalue <= 2000 THEN 'G' WHEN Category = 'AA' AND Cvalue > 2000 THEN 'H' ELSE 'J' END as ""Flag"" FROM TABLENAME"

What is the difference between DELETE, TRUNCATE, and DROP?

"The DELETE command is used to remove rows from a table. A WHERE clause can be used to only remove some rows. If no WHERE condition is specified, all rows will be removed. After performing a DELETE operation you need to COMMIT or ROLLBACK the transaction to make the change permanent or to undo it. Note that this operation will cause all DELETE triggers on the table to fire. TRUNCATE removes all rows from a table. The operation cannot be rolled back and no triggers will be fired. As such, TRUCATE is faster and doesn't use as much undo space as a DELETE. " The DROP command removes a table from the database. All the tables' rows, indexes and privileges will also be removed. No DML triggers will be fired. The operation cannot be rolled back.

What is the difference between Order By and Group By?

"The ORDER BY keyword is used to sort the result-set by one or more columns. The ORDER BY keyword sorts the records in ascending order by default (ASC). To sort the records in a descending order, you can use the DESC keyword. The GROUP BY statement is used in conjunction with the aggregate functions to group the result-set by one or more columns."

What is the WHERE clause? What is the purpose of AND and OR?

"The SQL WHERE clause is used to specify a condition while fetching the data from single table or joining with multiple tables. If the given condition is satisfied then only it returns specific rows from the table. You would use WHERE clause to filter the records and fetching only necessary records. The WHERE clause is not only used in SELECT statement, but it is also used in UPDATE, DELETE statement, etc." "The SQL AND and OR operators are used to combine multiple conditions to narrow data in an SQL statement. These two operators are called conjunctive operators. These operators provide a means to make multiple comparisons with different operators in the same SQL statement." "SELECT column1, column2, columnN FROM table_name WHERE [condition1] AND [condition2]...OR [conditionN];"

What is a trigger?

"Triggers are stored programs in PL/SQL, which are automatically executed or fired when some events occur. Triggers are, in fact, written to be executed in response to any of the following events: A database manipulation (DML) statement (DELETE, INSERT, or UPDATE). A database definition (DDL) statement (CREATE, ALTER, or DROP). A database operation (SERVERERROR, LOGON, LOGOFF, STARTUP, or SHUTDOWN). Triggers could be defined on the table, view, schema, or database with which the event is associated. " Example: USE AdventureWorks2008R2; GO IF OBJECT_ID ('Sales.reminder1', 'TR') IS NOT NULL DROP TRIGGER Sales.reminder1; GO CREATE TRIGGER reminder1 ON Sales.Customer AFTER INSERT, UPDATE AS RAISERROR ('Notify Customer Relations', 16, 10); GO

"How would you combine two columns into two column? Use it in an example."

"Use the CONCAT command. Select CONCAT(A, B) as ""Combination"" from TABLENAME"

How do you drop a table?

"Using the DROP TABLE statement. It removes one or more table definitions and all data, indexes, triggers, constraints, and permission specifications for those tables. Any view or stored procedure that references the dropped table must be explicitly dropped by using DROP VIEW or DROP PROCEDURE. DROP TABLE database_name . table_name "

"What statement would you use to grab all data from a string column, where the value includes the string: ""red""? What would the query look like?"

"You would use the LIKE command. SELECT * FROM TABLE WHERE ColumnName like '%red%';"

Consider tables a and b with loan number. There are 5 rows in common, 10 total rows in a, 20 total rows in b. If a left outer join is performed what would be the output

10 rows that all belong to a

How about a right outer join

20 rows that all belong to b

How about a full outer join?

25 rows all together

What is a cross join?

A cross join does not have a WHERE or ON clause, therefore it produces the Cartesian product of the tables involved in the join. The size of a Cartesian product result set is the number of rows in the first table multiplied by the number of rows in the second table.

What is a foreign key?

A foreign key is a column (or columns) that references a column (most often the primary key) of another table. This is to establish a relationship between records in separate tables.

What are the differences between a function and a stored procedure?

A function must return a value. A stored procedure returns 0 or more values. A function can have only input parameters. A stored procedure can have input and output parameters. A function can be called from a procedure. A stored procedure cannot be called from a function.

What is a sql function?

A function returns one value. A function cannot perform database operations. A function can call other functions A function can not call stored procedures.

What is a natural join?

A natural join is a join operation that creates an implicit join clause for you based on the common columns in the two tables being joined. Common columns are columns that have the same name in both tables. A natural join can be an INNER join, a LEFT OUTER join, RIGHT OUTER join.

What is a primary key?

A primary key is a column or set of columns that uniquely identify each row of a table. A driver's license number, a VIN number, or UPC (universal product code) are example of a primary key. A primary key must be unique and not null.

What is the difference between a primary key and a unique key?

A primary key specifies unique rows and cannot have a null value. A unique constraint specifies unique elements in that column and can have a single null value. For example, a user can have emails and all should be unique but they also have a username, which should also be unique and can act as a pk.

what is a sql query?

A query is when you ask the database to return information using the select command. example: select colname from tablename where colname = value;

What is recursive query and how do you write recursive sql?

A. A query that somewhat calls itself, B. You start with the keyword "with", which lets you define a CTE, a common table expression. This a temp. named result set that's derived from a single select, insert, delete, or update statement. Afterwards, you call the CTE you defined, and establish a base case, or anchor query , and union all the result with the recursive query, which is a query that inner joins the CTE and the table it was derived from.

What is auto commit transactions?

Auto commit mode is the default transaction management mode. Every statement is either committed or rolled back when completed.

What is BASE

Basic Availability Soft state Eventual consistency These are rules a microservice architecture with multiple databases should follow. Basic Availability means that there is access to the databases, even if all the databases aren't synced together. Soft state means that the databases can function without being in synch. Eventual consistency means that the databases will be synched, eventually.

what is the difference between cardinality and multiplicity?

Cardinality is the relationship between sets example: one to many Multiplicity sets values on the relationship. example: 0 to 3

Explain the transaction phenomenas and how they are prevented.

dirty reads are when a transaction reads uncommited data. read commited prevents this by locking down commits. nonrepeatable reads are when a transaction reads the same row twice but gets different results. It is prevented by locking down...__________ Phantom reads are when a transaction doesn't initially see a row that matches a search criteria. This is prevented by locking down the entire table.

How does a prepared statement prevent sql injection?

prepared statements force the user's input to be handled as the content of a parameter, and not as a part of the sql command. sql injection is when a user's input is used as part of the sql statement.

What issues goes along with each level?

read uncommitted doesn't prevent anything. Read committed prevents dirty reads. Repeatable reads prevents dirty reads and nonrepeatable reads. Serializable prevents dirty reads, nonrepeatable reads, as well as phantom reads.

In order to perform a sum of loan amounts with respect to loan number, write a simple example of SQL statement

select loan_number, sum(loan_amount) from table_a group by loan_number