ROC Week Two QC's

What are some SQL data types?

Bit, Tinyint, Smallint, Int, Bigint, varchar, char, Float, decimal, Smallmoney, Money, numeric, text, binary, varbinary, image, rowversion, xml.

What is the difference between scalar and aggregate functions? Give examples of each

Scalar Functions take one or more input values as arguments directly and return a value. Examples of Scalar Functions are LCASE(), UCASE(), LEN(), MID(), ROUND(). Aggregate Functions take a complete set of data as input and return a value that is computed from all the values in the set. Examples of Aggregate Functions are SUM(), COUNT(), AVG(), MIN(), MAX(), FIRST().

How would you setup a primary key that automatically increments with every INSERT statement?

You would create a table with a primary key and use the key word AUTO_INCREMENT on the value and use this in all related tables.

What is the purpose of a view? What about an index?

Views can join and simplify multiple tables into a single virtual table. Views can hide the complexity of data. Indexes can be used to retrieve data from a database more quickly than otherwise.

What conditions lead to orphan records?

If a row in a related table references a non-existent row in the primary table it is said to be an orphaned row. A record is deleted in a primary table but there is still a related table with the value of the primary table.

What are the properties a transaction(update delete insert) must follow?

1. Atomicity - This property states that a transaction must be treated as an atomic unit, either all of its operators are executed or none. 2. Consistency - The database must remain in a consistent state after any transaction. 3. Isolation - All transactions will be carried out as if it is the only transaction in the system. 4. Durability - The database should be durable enough to hold all its latest updates even if the system fails or restarts.

What are the 5 sublanguages of SQL? Which commands correspond to them?

1. DDL -> CREATE, DROP, ALTER, TRUNCATE, COMMENT, RENAME. 2. DQL -> SELECT. 3. DML -> INSERT, UPDATE, DELETE. 4. DCL -> GRANT, REVOKE. 5. TCL -> COMMIT, ROLLBACK, SAVEPOINT, SET TRANSACTION.

What are the types of joins? Explain the differences.

1. INNER JOIN - Returns records that have matching values in both tables. 2. LEFT JOIN - Return all records from the left table, and the matched records from the right table. 3. RIGHT JOIN - Returns all records from the right table, and the matched records from the left table. 4. FULL JOIN: Returns all records when there is a match in either left or right table.

Explain the different isolation levels. What read phenomena do each prevent?

1. Read Committed - Prevents the dirty read issue. 2. Read Uncommitted - Prevents other transactions from modifying data read by the current transaction. 3. Repeatable Read - Prevents other transactions from modifying any rows that have been read by the current transaction. 4. Snapshot - Prevents most locking and blocking by using row versioning. 5. Serializable - Prevents all three consistency anomalies.

Explain what SQL is. What are some SQL databases?

1. SQL is a domain-specific language used in programming and designed for managing data held in a relational database management system, or for stream processing in a relational data stream management system. 2. Oracle Database, MySQL, Microsoft SQL Server

Explain the difference between UNION, UNION ALL, and INTERSECT.

1. UNION combines the result set of two or more SELECT statements but no duplicate values. 2. UNION ALL combines the result set of two or more SELECT statements but allows duplicate values. 3. INTERSECT takes the data from both result sets which are in common except takes the data from the first result set, but not in the second result set.

What is a stored procedure and how would you call it in Java?

1. User defined piece of code written in SQL which may return a value that is invoked by calling it explicitly. 2. Invoke the connection.preparecall method to create a callable statement object, Invoke callable statement object, Invoke callablestatement.registeroutparameter, invoke callablestatement.get, invoke callablestatement.close

What is a candidate key? What about a surrogate key?

CANDIDATE KEY is a set of attributes that uniquely identify tuples in a table. It is a super key with no repeated attributes. SURROGATE KEY is created by assigning an identity property to a column that has a number data type. It is a value generated right before the record is inserted into a table. It is unique and not visible to the user.

What is a cascade delete?

Cascade delete means if a record in the parent table is deleted then the corresponding records in the child table will automatically be deleted.

What's the difference between a clustered and non-clustered index?

Clustered index describes the order in which records are physically stored on the disk and you can only have one. Non-Clustered index defines a logical order that does not match the physical order on the disk and you can have multiple of these.

Define the word "schema"

Collection of database objects associated with a database. The username of a database is called schema owner. Schema always belong to a single database whereas a database can have a single or multiple schemas.

Which interface is responsible for transaction management?

Connection Interface.

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

DELETE deletes all records from a database table. DROP removes one or more table definitions and all data, indexes, triggers, constraints, and permission specifications for those tables. TRUNCATE removes all rows from a table without logging the individual row deletions.

Transaction Problems (I added this one just in case)

Dirty Read: Occurs when a transaction reads data that has been added by a different transaction but has not been committed Non-Repeatable Read: Transaction re-reads data that it has previously read and finds that another committed transaction has modified or deleted data Phantom Read: Transaction re-runs a query to find that the number of records has changed

What are the core interfaces/classes in JDBC

DriverManager, Driver, Statement, PreparedStatement, CallableStatement, Connectionm ResultSet, ResultSetMetaData.

Steps to executing a SQL query using JDBC?

Establish a connection. Create a statement. Execute a query. Process the ResultSet Object. Close the connection.

What are some SQL clauses you can use with SELECT statements?

FROM, WHERE, GROUP BY, ORDER BY, HAVING, OFFSET, FETCH FIRST, UNION, INTERSECT, EXCEPT, WITH.

What's the difference between implicit and explicit cursors?

Implicit Cursors are automatically created when select statements are executed. Explicit Cursors need to be defined explicitly by the user by providing a name.

What is JDBC

JDBC is a set of Java API for executing SQL statements. This API consists of a set of classes and interfaces to enable programs to write pure Java Database applications.

List the integrity constraints

NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK, DEFAULT, INDEX.

What is normalization? What are the levels?

Normalization is a database design technique that reduces data redundancy and eliminates undesirable characteristics like Insertion, Update, and Deletion Anomalies. The purpose of Normalization is to eliminate redundant data and ensure data is stored logically. *First Normal Form.* *Second Normal Form.* *Third Normal Form.* *Boyce Codd Normal Form or Fourth Normal Form.* *Fifth Normal Form.* *Sixth Normal Form.*

How to execute stored procedures in JDBC?

Prepare the callable statement, Register the output parameters, Set the input parameters, Execute the callable statement.

Explain the concept of relational integrity

Relational integrity constraint is used to ensure accuracy and consistency of data in a relational database. The term relational stems from the fact that each table in the database contains information related to a single subject and only that subject.

What is the difference between Statement and PreparedStatement?

Statement will be used for executing static SQL statements and can't accept parameters. PreparedStatement will be used for executing SQL statements many times dynamically. It will accept input parameters.

Explain what the ORDER BY and GROUP BY clauses do?

The ORDER BY clause is used to sort the data in ascending or descending order, based on one or more columns. The GROUP BY clause groups rows that have the same values into summary rows. The GROUP BY clause is often used with aggregate functions (COUNT, MAX, MIN, SUM, AVG) to group the result-set by one or more columns.

What is the difference between WHERE and HAVING?

The WHERE clause is used to filter records from a result. The filter occurs before any groupings are made. The HAVING clause is used to filter variables from a group.

What is the difference between joins and set operators?

The set operator combines the results of two component queries into a single result The join operator is used to combine data from many tables based on a matched condition between them.

What is a trigger? Give the syntax for creating a trigger.

Trigger is a special type of stored procedure that automatically runs when an event occurs in the database server. *create trigger [trigger_name] * *[before | after] * *{insert | update | delete} * *on [table_name] * *[for each row] * *[trigger_body]*