SQL Database Role in processing Data

SQL has been adopted by nearly every vendor of relational database systems. Because of that, it is an important tool for managing data. JDBC is oriented toward supporting this type of DBMS. JDBC was designed to package and deliver SQL statements to relational database systems.
With SQL, we can write commands to:

1. Add rows to our tables with the INSERT command
2. Remove rows from our tables with the DELETE command
3. Change the data in a column with the UPDATE command
4. Add new tables with the CREATE command
5. Choose data from a table or combination of tables with the SELECT command

While the specific APIs used by database management systems vary, a relational DBMS typically uses SQL commands. Having a standardized way to talk to a DBMS is a major step forward for developer productivity. Previously, each DBMS had its own unique API for communicating with the outside world, which made things much more difficult. Developers had to learn different sets of commands when they needed to use different databases. SQL is fairly easy to learn because its commands are very much like statements in English. (In fact, the original name for the language was "Structured English Query Language" with an acronym of SEQUEL.) Below is an example of a SQL query that finds the cardiac specialists from the list of all the doctors in the in the Brazilian Hospital Project database.

• Select from SQL Example:
  

  SELECT doctor id 
  FROM specialty 
  where specialty = 'Cardiology'
  

• SQL keyword is case-insensitive:
  A SQL keyword is case-insensitive, but commonly they are placed in all uppercase so that you can easily identify them. We will follow that convention in this course. This insensitivity to case does not necessarily carry over to the DBMS-specific parts of a command. The database table and column names may be case sensitive, for example. You will have to consult your database documentation for help determining this.

The basic SQL commands used by Database Management Systems are:

1. CREATE,
2. INSERT,
3. UPDATE,
4. DELETE, and
5. SELECT.

• SQL Syntax:
  You will combine SQL with Java programming when you use JDBC. The following tips will help you while you are learning SQL commands.
  Though SQL is not case sensitive, use all capital letters to easily locate the SQL commands in your code. The space between words in a SQL statement is not important. You can use quotation marks to define strings. A single quotation mark (') indicates a string constant. A double quotation mark (") may be used to delimit database entities (such as table columns) that contain spaces. However, check your database documentation because these two characters are sometimes treated differently. If you hear the terms "JDBC SQL type," "SQL type," and "JDBC type," don't be confused. These are all terms that programmers use to refer to the generic SQL type identifiers.
  When you begin working with JDBC, read the SQL manual published by your database vendor. The documentation should provide a reference for specific information you may need (Open Database Connectivity) in a solution.

package java.sql
Provides the API for accessing and processing data stored in a data source (usually a relational database) using the JavaTM programming language.

public interface ResultSet extends Wrapper

A table of data representing a database result set, which is usually generated by executing a statement that queries the database.
A ResultSet object maintains a cursor pointing to its current row of data. Initially the cursor is positioned before the first row. The next method moves the cursor to the next row, and because it returns false when there are no more rows in the ResultSet object, it can be used in a while loop to iterate through the result set. A default ResultSet object is not updatable and has a cursor that moves forward only. Thus, you can iterate through it only once and only from the first row to the last row. It is possible to produce ResultSet objects that are scrollable and/or updatable. The following code fragment, in which con is a valid Connection object, illustrates how to make a result set that is scrollable and insensitive to updates by others, and that is updatable. See ResultSet fields for other options.

CREATE TABLE table_name (
    column_name1 column_type1 column_modifiers1,
    column_name2 column_type2 column_modifiers2,
    column_name3 column_type3 column_modifiers3,
    ...
);

1) Create is the SQL statement to handle the creation of database entities.
The syntax is database-dependent. Most database systems usually provide (GUI) utilities to create tables without issuing a SQL statement.
if your project requires database tables to be created apart from such a tool you would use the Create Table statement this might be handy where tables need to be created in the automated way such as from a command script or a batch file.

UPDATE <table name>
SET < column list>
WHERE <filter criteria>

Question: What will the following code fragment print when compiled and run?

Connection c = DriverManager.getConnection("jdbc:derby://localhost:1527/sample", "app", "app");
try(Statement stmt = c.createStatement();){
  ResultSet rs = stmt.executeQuery("select * from STUDENT");
  while(rs.next()){
    System.out.println(rs.getString(1));
  }
}
catch(SQLException e){
  System.out.println("Exception ");
}

(Assume that items not specified such as import statements and try/catch block are all valid.)

1. It will throw an exception if the first column of the result is not a String.
2. It will throw an exception every time it is run irrespective of what the query returns.
3. It will print the value for the first column of the result and if there is no row in STUDENT table, it will not print anything.
4. It will not compile.

Answer: c
Explanation:
a. Since Object class has a toString() method, any type can be converted to a String.
So this cannot be a cause of the exception. If you use getInt() or getDouble and if the column value cannot be converted to an int or double, then that will cause an Exception.

There are several variations of SQL in use. The most widely used is SQL-92, and it is the specification our course follows. The most recently defined specification is commonly referred to as SQL3. Support for SQL3 features is not universal, and not all databases conform to the syntax or semantics for the advanced functionality. JDBC does support SQL3, but databases are not required to support it.
In the next lesson, you will learn about the design considerations for JDBC and ODBC (Open Database Connectivity) in a solution.