|Lesson 6|| SQL overview |
|Objective||Explain what SQL is and its role in database processing. |
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:
- Add rows to our tables with the INSERT command
- Remove rows from our tables with the DELETE command
- Change the data in a column with the UPDATE command
- Add new tables with the CREATE command
- 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
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:
See the following Slide Show for examples.
- DELETE, and
Basic SQL Commands
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.