SQL QC

Explain 1st, 2nd, and 3rd normal form

1 NF: Every value in a database must be atomic. 2 NF: Every non key columns are dependent on the tables primary key 3 NF: Table contains only columns that are non transitively dependent on the primary key. transitive means a relationship is the same across a whole. Dependence is when a value from one column can be derived from another column. Transitive Dependence: means that a column's value relies on another column through a second intermediate column. EX: Consider three columns: AuthorNationality, Author, and Book. Column values for AuthorNationality and Author rely on the Book; once the book is known, you can find out the Author or AuthorNationality. But also notice that the AuthorNationality relies upon Author. That is, once you know the Author, you can determine their nationality. In this sense then, the AuthorNationality relies upon Book, via Author. This is a transitive dependence. This can be generalized as being three columns: A, B and PK. If the value of A relies on PK, and B relies on PK, and A also relies on B, then you can say that A relies on PK though B. That is A is transitively dependent on PK.

explain what a natural join is

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, or a RIGHT OUTER join

Explain triggers

A database trigger is procedural code that is automatically executed in response to certain events. A trigger is mainly used for maintaining the integrity of the info on the database. EX 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

Explain 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 procedures can have input and output parameters A function can be called from a procedure A procedure cannot be called from a function

explain how you return data from JDBC

A result set is returned to the java application when a sql query is executed by a statement object. The result set object provides methods for iterating through the results of the query.

give an example of a scalar function

A scalar valued function is a function that takes one or more values but returns a single value. f(x,y,z) = x2+2yz5 is an example of a scalar valued function

Explain sequences

A sequence is a user defined, schema bound, object that generates unique integers. You can use a sequence to automatically generate primary key values ex: CREATE SEQUENCE customers_seq START WITH 1000 INCREMENT BY 1 NOCACHE // values are not preallocated NOCYCLE; // sequence cannot generate more values after reaching its max value

Explain Log4J

Apache Log4j is a logging library for Java. Apache log4j helps inserting log statements into code as a low tech method for debugging it. log4j makes it possible to enable logging at runtime without modifying the application binary.

Explain auto commit transactions

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

Explain the difference of Prepared Statement and Statement

Both are interfaces that define the methods and properties that enable you to send pl/sql commands and receive data from your database. Statement: is general access to your database and cannot accept parameters. PreparedStatement: is used when you use a sql statement many times. It can access input parameters at runtime. CallableStatement: is used when you want to access the database stored procedures. It can accept run time input parameters.

explain some differences between Oracle sql and mysql

Both: relational database management systems. Mysql uses t-sql, transact sql.

Explain dcl

DCL: Data Control Language. DCL Commands: are used to enforce database security when there are multiple users. Commands: GRANT, REVOKE GRANT: is used to provide access privileges on the db object to the user. REVOKE: removes user access rights. EX: GRANT SELECT ON employee TO user1; This command grants a SELECT permission on employee table to user1 REVOKE SELECT ON employee FROM user1; This command will REVOKE a SELECT privilege on employee table from user1

explain ddl

DDL: "Data Definition Language" DDL Commands: change the structure of the database schema in some way, typically by creating, deleting, or modifying schema objects such as databases, tables, and views. Most DDL statements start with the keywords CREATE , DROP , or ALTER

explain dml

DML: Data Manipulation Language. DML is used to retrieve, store, modify, delete, insert and update data in database. SELECT - retrieve data from the a database. INSERT - insert data into a table. UPDATE - updates existing data within a table. DELETE - Delete all records from a database table. MERGE - UPSERT operation (insert or update) CALL - call a PL/SQL or Java subprogram. EXPLAIN PLAN - interpretation of the data access path

Explain normalization

Database normalization is the process used to organize a database into tables and columns. The core idea is that a table should be about a specific topic and that only those columns which support that topic are included. Benefits: Normalization eliminates data redundancy. Normalization eliminates undesirable characteristics, such as Insertion, Update, Deletion Anamolies. Normalization ensures data is logically stored.

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. Ex: 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

Explain transaction isolation levels

Isolation levels that your transaction runs in determines how sensitive your application is to changes other users' transactions make. You can change the level of isolation using the command: SET TRANSACTION ISOLATION LEVEL 4 levels: Uncommitted Read Committed Read Repeatable Read Serializable

Explain JDBC

Java DataBase Connectivity an application programming interface (API) which defines how a client may access a database. It is part of the Java Standard Edition platform, from Oracle Corporation.

Explain how SELECT is different from INSERT, DELETE, and the other dml commands

SELECT is different than the other data manipulation lang commands because it does not manipulate data. SELECT asks to get, or query's, data back. Some put it in the category of DQL, data query language

What is the object you get back from a statement

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

DELETE VS TRUNCATE

TRUNCATE is a DDL command. DELETE is a DML command. They both delete records in a table. DELETE operations can be rolled back (undone), TRUNCATE operations cannot be rolled back.

Explain why you cant rollback TRUNCATE

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

Explain the main interfaces of JDBC

The DriverManager class (java.sql.DriverManager) is one of main components of JDBC. DriverManager manages database drivers, load database specific drivers and select the most appropriate database specific driver from the previously loaded drivers when a new connection is established ------------------------------------------------ Connection interface provides a standard abstraction to access the session established with a database server. JDBC driver provider should implement the connection interface. We can obtain a Connection object using the getConnection() method of the DriverManager as: Connection con = DriverManager.getConnection(url, username, password); ---------------------------------------------------- The Statement interface provides a standard abstraction to execute SQL statements and return the results using the ResultSet objects ---------------------------------------------------- PreparedStatement is a sub interface of the Statement interface. PreparedStatements are pre-compiled and hence their execution is much faster than that of Statements. You get a PreparedStatement object from a Connection object using the prepareStatement() method: PreparedStatement ps1 = con.prepareStatement("insert into employeeList values ('Heartin',2)"); ps1.executeUpdate(); -------------------------------------------- CallableStatement extends the capabilities of a PreparedStatement to include methods that are only appropriate for stored procedure calls; and hence CallableStatement is used to execute SQL stored procedures. Whereas PreparedStatement gives methods for dealing with IN parameters, CallableStatement provides methods to deal with OUT parameters as well ---------------------------------------------------- ResultSet This interface represents a table of data representing a database result set, which is usually generated by executing a statement that queries the database ---------------------------------------------------- SQLException This class is an exception class that provides information on a database access error or other errors. JDBC 4.0 introduced following refined subclasses of SQLException: java.sql.SQLClientInfoException java.sql.SQLDataException java.sql.SQLFeatureNotSupportedException java.sql.SQLIntegrityConstraintViolationException java.sql.SQLInvalidAuthorizationSpecException java.sql.SQLSyntaxErrorException java.sql.SQLTransactionRollbackException java.sql.SQLTransientConnectionException

explain the HAVING clause

The HAVING clause was added to SQL because the WHERE keyword could not be used with aggregate functions. 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;

explain the INSERT statement

The INSERT INTO statement is used to insert new records in a table SYNTAX: INSERT INTO table_name (column1, column2, column3, ...) VALUES (value1, value2, value3, ...); Columns dont have to be defined if you insert a value for every column

Explain how columns are indexed in JDBC

The JDBC api indexes their columns starting at 1. The ResultSet returned also starts at 1.

Explain the SELECT statement

The SELECT statement is DQL SELECT returns a result set of records from one or more tables. The data returned is stored in a result table, called the result-set SYNTAX: SELECT column1, column2, ... FROM table_name

Give an example of SQL injection

This sql injection creates a SELECT statement by adding a variable (txtUserId) to a select string. The variable is fetched from user input (getRequestString): Example txtUserId = getRequestString("UserId"); txtSQL = "SELECT * FROM Users WHERE UserId = " + txtUserId;

Explain views

a view is a virtual saved query. A view is the result set of a stored query on the data. The user can query this result set.

List the classes of JDBC

java.sql.BatchUpdateException Class java.sql.Connection Interface java.sql.DatabaseMetaData Interface java.sql.Driver Interface java.sql.DriverManager.getConnection Method java.sql.PreparedStatement Interface java.sql.ResultSet Interface java.sql.SavePoint Class java.sql.SQLException Class java.sql.Statement Class javax.sql.XADataSource

Explain how a prepared Statement prevents SQL injection

sql injection is when a user's input is used as part of the sql statement. 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.

Explain what scalar functions do

takes in one or more parameters and returns a single value