{"id":542,"date":"2021-02-22T17:34:13","date_gmt":"2021-02-22T17:34:13","guid":{"rendered":"https:\/\/bhiggs.x10hosting.com\/HighOctaneJava\/?page_id=542"},"modified":"2021-02-22T19:36:21","modified_gmt":"2021-02-22T19:36:21","slug":"database-support-jdbc","status":"publish","type":"page","link":"https:\/\/bhiggs.x10hosting.com\/HighOctaneJava\/course-topics\/database-support-jdbc\/","title":{"rendered":"Database Support (JDBC)"},"content":{"rendered":"<div id=\"ez-toc-container\" class=\"ez-toc-v2_0_85 counter-hierarchy ez-toc-counter ez-toc-grey ez-toc-container-direction\">\n<p class=\"ez-toc-title\" style=\"cursor:inherit\">Table of Contents<\/p>\n<label for=\"ez-toc-cssicon-toggle-item-6a6d4ad52cb8c\" class=\"ez-toc-cssicon-toggle-label\"><span class=\"\"><span class=\"eztoc-hide\" style=\"display:none;\">Toggle<\/span><span class=\"ez-toc-icon-toggle-span\"><svg style=\"fill: #999;color:#999\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" class=\"list-377408\" width=\"20px\" height=\"20px\" viewBox=\"0 0 24 24\" fill=\"none\"><path d=\"M6 6H4v2h2V6zm14 0H8v2h12V6zM4 11h2v2H4v-2zm16 0H8v2h12v-2zM4 16h2v2H4v-2zm16 0H8v2h12v-2z\" fill=\"currentColor\"><\/path><\/svg><svg style=\"fill: #999;color:#999\" class=\"arrow-unsorted-368013\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"10px\" height=\"10px\" viewBox=\"0 0 24 24\" version=\"1.2\" baseProfile=\"tiny\"><path d=\"M18.2 9.3l-6.2-6.3-6.2 6.3c-.2.2-.3.4-.3.7s.1.5.3.7c.2.2.4.3.7.3h11c.3 0 .5-.1.7-.3.2-.2.3-.5.3-.7s-.1-.5-.3-.7zM5.8 14.7l6.2 6.3 6.2-6.3c.2-.2.3-.5.3-.7s-.1-.5-.3-.7c-.2-.2-.4-.3-.7-.3h-11c-.3 0-.5.1-.7.3-.2.2-.3.5-.3.7s.1.5.3.7z\"\/><\/svg><\/span><\/span><\/label><input type=\"checkbox\"  id=\"ez-toc-cssicon-toggle-item-6a6d4ad52cb8c\"  aria-label=\"Toggle\" \/><nav><ul class='ez-toc-list ez-toc-list-level-1 ' ><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-1\" href=\"https:\/\/bhiggs.x10hosting.com\/HighOctaneJava\/course-topics\/database-support-jdbc\/#Relational_Databases\" >Relational Databases<\/a><ul class='ez-toc-list-level-4' ><li class='ez-toc-heading-level-4'><a class=\"ez-toc-link ez-toc-heading-2\" href=\"https:\/\/bhiggs.x10hosting.com\/HighOctaneJava\/course-topics\/database-support-jdbc\/#The_Relational_Model\" >The Relational Model<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-4'><a class=\"ez-toc-link ez-toc-heading-3\" href=\"https:\/\/bhiggs.x10hosting.com\/HighOctaneJava\/course-topics\/database-support-jdbc\/#Relational_Terminology\" >Relational Terminology<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-4'><a class=\"ez-toc-link ez-toc-heading-4\" href=\"https:\/\/bhiggs.x10hosting.com\/HighOctaneJava\/course-topics\/database-support-jdbc\/#Relational_Data_Types\" >Relational Data Types<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-4'><a class=\"ez-toc-link ez-toc-heading-5\" href=\"https:\/\/bhiggs.x10hosting.com\/HighOctaneJava\/course-topics\/database-support-jdbc\/#Relational_Operations\" >Relational Operations<\/a><ul class='ez-toc-list-level-5' ><li class='ez-toc-heading-level-5'><a class=\"ez-toc-link ez-toc-heading-6\" href=\"https:\/\/bhiggs.x10hosting.com\/HighOctaneJava\/course-topics\/database-support-jdbc\/#Selection\" >Selection<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-5'><a class=\"ez-toc-link ez-toc-heading-7\" href=\"https:\/\/bhiggs.x10hosting.com\/HighOctaneJava\/course-topics\/database-support-jdbc\/#Join\" >Join<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-5'><a class=\"ez-toc-link ez-toc-heading-8\" href=\"https:\/\/bhiggs.x10hosting.com\/HighOctaneJava\/course-topics\/database-support-jdbc\/#Union\" >Union<\/a><\/li><\/ul><\/li><li class='ez-toc-page-1 ez-toc-heading-level-4'><a class=\"ez-toc-link ez-toc-heading-9\" href=\"https:\/\/bhiggs.x10hosting.com\/HighOctaneJava\/course-topics\/database-support-jdbc\/#Closure\" >Closure<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-4'><a class=\"ez-toc-link ez-toc-heading-10\" href=\"https:\/\/bhiggs.x10hosting.com\/HighOctaneJava\/course-topics\/database-support-jdbc\/#The_Transaction_Model\" >The Transaction Model<\/a><ul class='ez-toc-list-level-5' ><li class='ez-toc-heading-level-5'><a class=\"ez-toc-link ez-toc-heading-11\" href=\"https:\/\/bhiggs.x10hosting.com\/HighOctaneJava\/course-topics\/database-support-jdbc\/#Commit_vs_Rollback\" >Commit vs Rollback<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-5'><a class=\"ez-toc-link ez-toc-heading-12\" href=\"https:\/\/bhiggs.x10hosting.com\/HighOctaneJava\/course-topics\/database-support-jdbc\/#The_ACID_Properties\" >The ACID Properties<\/a><\/li><\/ul><\/li><\/ul><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-13\" href=\"https:\/\/bhiggs.x10hosting.com\/HighOctaneJava\/course-topics\/database-support-jdbc\/#Structured_Query_Language_SQL\" >Structured Query Language (SQL)<\/a><ul class='ez-toc-list-level-4' ><li class='ez-toc-heading-level-4'><a class=\"ez-toc-link ez-toc-heading-14\" href=\"https:\/\/bhiggs.x10hosting.com\/HighOctaneJava\/course-topics\/database-support-jdbc\/#SQL_is_a_Set-Oriented_Language\" >SQL is a Set-Oriented Language<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-4'><a class=\"ez-toc-link ez-toc-heading-15\" href=\"https:\/\/bhiggs.x10hosting.com\/HighOctaneJava\/course-topics\/database-support-jdbc\/#Categories_of_SQL_Statements\" >Categories of SQL Statements<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-4'><a class=\"ez-toc-link ez-toc-heading-16\" href=\"https:\/\/bhiggs.x10hosting.com\/HighOctaneJava\/course-topics\/database-support-jdbc\/#References\" >References<\/a><\/li><\/ul><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-17\" href=\"https:\/\/bhiggs.x10hosting.com\/HighOctaneJava\/course-topics\/database-support-jdbc\/#DML_Statements\" >DML Statements<\/a><ul class='ez-toc-list-level-4' ><li class='ez-toc-heading-level-4'><a class=\"ez-toc-link ez-toc-heading-18\" href=\"https:\/\/bhiggs.x10hosting.com\/HighOctaneJava\/course-topics\/database-support-jdbc\/#Categories_of_DML_Statements\" >Categories of DML Statements<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-4'><a class=\"ez-toc-link ez-toc-heading-19\" href=\"https:\/\/bhiggs.x10hosting.com\/HighOctaneJava\/course-topics\/database-support-jdbc\/#SELECT_Statements\" >SELECT&nbsp;Statements<\/a><ul class='ez-toc-list-level-5' ><li class='ez-toc-heading-level-5'><a class=\"ez-toc-link ez-toc-heading-20\" href=\"https:\/\/bhiggs.x10hosting.com\/HighOctaneJava\/course-topics\/database-support-jdbc\/#SELECT_Statements_vs_Query_Expressions\" >SELECT&nbsp;Statements vs Query Expressions<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-5'><a class=\"ez-toc-link ez-toc-heading-21\" href=\"https:\/\/bhiggs.x10hosting.com\/HighOctaneJava\/course-topics\/database-support-jdbc\/#Simple_SELECT_Statements\" >Simple&nbsp;SELECT&nbsp;Statements<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-5'><a class=\"ez-toc-link ez-toc-heading-22\" href=\"https:\/\/bhiggs.x10hosting.com\/HighOctaneJava\/course-topics\/database-support-jdbc\/#Specifying_Multiple_Columns\" >Specifying Multiple Columns<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-5'><a class=\"ez-toc-link ez-toc-heading-23\" href=\"https:\/\/bhiggs.x10hosting.com\/HighOctaneJava\/course-topics\/database-support-jdbc\/#Specifying_the_Order_of_Rows_Returned_by_a_SELECT_Statement\" >Specifying the Order of Rows Returned by a&nbsp;SELECT&nbsp;Statement<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-5'><a class=\"ez-toc-link ez-toc-heading-24\" href=\"https:\/\/bhiggs.x10hosting.com\/HighOctaneJava\/course-topics\/database-support-jdbc\/#The_WHERE_clause\" >The&nbsp;WHERE&nbsp;clause<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-5'><a class=\"ez-toc-link ez-toc-heading-25\" href=\"https:\/\/bhiggs.x10hosting.com\/HighOctaneJava\/course-topics\/database-support-jdbc\/#Using_Multiple_Predicates\" >Using Multiple Predicates<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-5'><a class=\"ez-toc-link ez-toc-heading-26\" href=\"https:\/\/bhiggs.x10hosting.com\/HighOctaneJava\/course-topics\/database-support-jdbc\/#Performing_Joins\" >Performing Joins<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-5'><a class=\"ez-toc-link ez-toc-heading-27\" href=\"https:\/\/bhiggs.x10hosting.com\/HighOctaneJava\/course-topics\/database-support-jdbc\/#Other_Kinds_of_Joins\" >Other Kinds of Joins<\/a><\/li><\/ul><\/li><li class='ez-toc-page-1 ez-toc-heading-level-4'><a class=\"ez-toc-link ez-toc-heading-28\" href=\"https:\/\/bhiggs.x10hosting.com\/HighOctaneJava\/course-topics\/database-support-jdbc\/#INSERT_Statements\" >INSERT&nbsp;Statements<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-4'><a class=\"ez-toc-link ez-toc-heading-29\" href=\"https:\/\/bhiggs.x10hosting.com\/HighOctaneJava\/course-topics\/database-support-jdbc\/#DELETE_Statements\" >DELETE&nbsp;Statements<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-4'><a class=\"ez-toc-link ez-toc-heading-30\" href=\"https:\/\/bhiggs.x10hosting.com\/HighOctaneJava\/course-topics\/database-support-jdbc\/#UPDATE_Statements\" >UPDATE&nbsp;Statements<\/a><\/li><\/ul><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-31\" href=\"https:\/\/bhiggs.x10hosting.com\/HighOctaneJava\/course-topics\/database-support-jdbc\/#DDL_Statements\" >DDL Statements<\/a><ul class='ez-toc-list-level-4' ><li class='ez-toc-heading-level-4'><a class=\"ez-toc-link ez-toc-heading-32\" href=\"https:\/\/bhiggs.x10hosting.com\/HighOctaneJava\/course-topics\/database-support-jdbc\/#Schemas_and_Schema_Objects\" >Schemas and Schema Objects<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-4'><a class=\"ez-toc-link ez-toc-heading-33\" href=\"https:\/\/bhiggs.x10hosting.com\/HighOctaneJava\/course-topics\/database-support-jdbc\/#Creating_Schema_Objects\" >Creating Schema Objects<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-4'><a class=\"ez-toc-link ez-toc-heading-34\" href=\"https:\/\/bhiggs.x10hosting.com\/HighOctaneJava\/course-topics\/database-support-jdbc\/#Creating_a_Table\" >Creating a Table<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-4'><a class=\"ez-toc-link ez-toc-heading-35\" href=\"https:\/\/bhiggs.x10hosting.com\/HighOctaneJava\/course-topics\/database-support-jdbc\/#SQL_Data_Types\" >SQL Data Types<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-4'><a class=\"ez-toc-link ez-toc-heading-36\" href=\"https:\/\/bhiggs.x10hosting.com\/HighOctaneJava\/course-topics\/database-support-jdbc\/#Changing_Schema_Objects\" >Changing Schema Objects<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-4'><a class=\"ez-toc-link ez-toc-heading-37\" href=\"https:\/\/bhiggs.x10hosting.com\/HighOctaneJava\/course-topics\/database-support-jdbc\/#Changing_the_Attributes_of_a_Table\" >Changing the Attributes of a Table<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-4'><a class=\"ez-toc-link ez-toc-heading-38\" href=\"https:\/\/bhiggs.x10hosting.com\/HighOctaneJava\/course-topics\/database-support-jdbc\/#Removing_Schema_Objects\" >Removing Schema Objects<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-4'><a class=\"ez-toc-link ez-toc-heading-39\" href=\"https:\/\/bhiggs.x10hosting.com\/HighOctaneJava\/course-topics\/database-support-jdbc\/#Removing_a_Table\" >Removing a Table<\/a><\/li><\/ul><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-40\" href=\"https:\/\/bhiggs.x10hosting.com\/HighOctaneJava\/course-topics\/database-support-jdbc\/#Transaction_Statements\" >Transaction Statements<\/a><ul class='ez-toc-list-level-4' ><li class='ez-toc-heading-level-4'><a class=\"ez-toc-link ez-toc-heading-41\" href=\"https:\/\/bhiggs.x10hosting.com\/HighOctaneJava\/course-topics\/database-support-jdbc\/#Starting_a_Transaction\" >Starting a Transaction<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-4'><a class=\"ez-toc-link ez-toc-heading-42\" href=\"https:\/\/bhiggs.x10hosting.com\/HighOctaneJava\/course-topics\/database-support-jdbc\/#Committing_a_Transaction\" >Committing a Transaction<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-4'><a class=\"ez-toc-link ez-toc-heading-43\" href=\"https:\/\/bhiggs.x10hosting.com\/HighOctaneJava\/course-topics\/database-support-jdbc\/#Rolling_Back_a_Transaction\" >Rolling Back a Transaction<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-4'><a class=\"ez-toc-link ez-toc-heading-44\" href=\"https:\/\/bhiggs.x10hosting.com\/HighOctaneJava\/course-topics\/database-support-jdbc\/#Mixing_DML_and_DDL_in_a_Transaction\" >Mixing DML and DDL in a Transaction<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-4'><a class=\"ez-toc-link ez-toc-heading-45\" href=\"https:\/\/bhiggs.x10hosting.com\/HighOctaneJava\/course-topics\/database-support-jdbc\/#AutoCommit_Mode\" >AutoCommit Mode<\/a><\/li><\/ul><\/li><\/ul><\/nav><\/div>\n\n<p class=\"wp-block-paragraph\">Database access is a very important feature for almost any application in these times.\u00a0 Java provides database access in a very portable way, using JDBC (Java DataBase Connectivity).<\/p>\n\n\n\n<h3 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Relational_Databases\"><\/span>Relational Databases<span class=\"ez-toc-section-end\"><\/span><\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">Relational databases implement the&nbsp;<em><strong>Relational Model<\/strong><\/em>&nbsp;formulated by E.F. Codd in his seminal paper&nbsp;<em>&#8220;A Relational Model of Data for Large Shared Data Banks&#8221;<\/em>, Communications of the ACM, 1970.<\/p>\n\n\n\n<h4 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"The_Relational_Model\"><\/span>The Relational Model<span class=\"ez-toc-section-end\"><\/span><\/h4>\n\n\n\n<p class=\"wp-block-paragraph\">The major features of the Relational Model are:<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li>Data is represented as a set of&nbsp;<em><strong>relations<\/strong><\/em>, or&nbsp;<em><strong>tables<\/strong><\/em>,<\/li><li>How data is stored in a database is independent of the relationships between the data. (the logical schema and the storage schema are independent of each other)<\/li><li>The model provides a mathematical basis for operations on the data.<\/li><\/ul>\n\n\n\n<h4 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Relational_Terminology\"><\/span>Relational Terminology<span class=\"ez-toc-section-end\"><\/span><\/h4>\n\n\n\n<p class=\"wp-block-paragraph\">Here is some terminology commonly used in relational databases:<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li><strong>Table<\/strong>&nbsp;(<em>relation<\/em>) : The basic logical organizational unit for data.<br>Data in a table is organized in:<ul><li><strong>Rows<\/strong>&nbsp;(sometimes known as&nbsp;<em>tuple<\/em>s)<\/li><li><strong>Columns<\/strong>&nbsp;(sometimes known as&nbsp;<em>attributes<\/em>), which have names.<\/li><\/ul><\/li><\/ul>\n\n\n\n<p class=\"wp-block-paragraph\">For example, imagine a database which stores information about employees for a company. <\/p>\n\n\n\n<p class=\"wp-block-paragraph\">An&nbsp;<strong>Employees<\/strong>&nbsp;table might look like the following:<\/p>\n\n\n\n<figure class=\"wp-block-table\"><table><tbody><tr><th><strong>EmployeeID<\/strong><\/th><th><strong>Name<\/strong><\/th><th><strong>Department<\/strong><\/th><th><strong>Age<\/strong><\/th><th><strong>Salary<\/strong><\/th><\/tr><tr><td>10063<\/td><td>Alfred J Prufrock<\/td><td>10<\/td><td>45<\/td><td>120000<\/td><\/tr><tr><td>204567<\/td><td>Angela Parfitt<\/td><td>30<\/td><td>36<\/td><td>78000<\/td><\/tr><tr><td>345890<\/td><td>Michael Solistes<\/td><td>20<\/td><td>28<\/td><td>90000<\/td><\/tr><tr><td>89567<\/td><td>Charles Schultz<\/td><td>20<\/td><td>56<\/td><td>230000<\/td><\/tr><tr><td>56435<\/td><td>Barbara Smith<\/td><td>30<\/td><td>48<\/td><td>134000<\/td><\/tr><tr><td><strong>&#8230;<\/strong><\/td><td>&nbsp;<\/td><td>&nbsp;<\/td><td>&nbsp;<\/td><td>&nbsp;<\/td><\/tr><tr><td><strong>&#8230;<\/strong><\/td><td>&nbsp;<\/td><td>&nbsp;<\/td><td>&nbsp;<\/td><td>&nbsp;<\/td><\/tr><tr><td><strong>&#8230;<\/strong><\/td><td>&nbsp;<\/td><td>&nbsp;<\/td><td>&nbsp;<\/td><td>&nbsp;<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\">and a&nbsp;<strong>Departments<\/strong>&nbsp;table might look like:<\/p>\n\n\n\n<figure class=\"wp-block-table\"><table><tbody><tr><th><strong>DepartmentID<\/strong><\/th><th><strong>Name<\/strong><\/th><th><strong>Manager<\/strong><\/th><\/tr><tr><td>10<\/td><td>Accounting<\/td><td>10063<\/td><\/tr><tr><td>20<\/td><td>Sales<\/td><td>89567<\/td><\/tr><tr><td>30<\/td><td>Information Technology<\/td><td>56435<\/td><\/tr><tr><td><strong>&#8230;<\/strong><\/td><td>&nbsp;<\/td><td>&nbsp;<\/td><\/tr><tr><td><strong>&#8230;<\/strong><\/td><td>&nbsp;<\/td><td>&nbsp;<\/td><\/tr><tr><td><strong>&#8230;<\/strong><\/td><td>&nbsp;<\/td><td>&nbsp;<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\">Note that, in the above example, there are two columns which refer to rows in the other table:<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li>The&nbsp;<strong>Department<\/strong>&nbsp;column in the&nbsp;<strong>Employees<\/strong>&nbsp;table contains DepartmentIDs to refer to the appropriate row in the&nbsp;<strong>Departments<\/strong>&nbsp;table.<\/li><li>The&nbsp;<strong>Manager<\/strong>&nbsp;column in the&nbsp;<strong>Departments<\/strong>&nbsp;table contains EmployeeIDs to refer to the appropriate row in the&nbsp;<strong>Employees<\/strong>&nbsp;table.<\/li><\/ul>\n\n\n\n<p class=\"wp-block-paragraph\">This is done in the relational model to avoid duplication of data, and to improve the data structure\/organization.<\/p>\n\n\n\n<h4 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Relational_Data_Types\"><\/span>Relational Data Types<span class=\"ez-toc-section-end\"><\/span><\/h4>\n\n\n\n<p class=\"wp-block-paragraph\">Each column in a relational database table has a&nbsp;<em><strong>data type<\/strong><\/em>. The Relational Model emphasizes that each column&#8217;s data type must be atomic; that is, a data type is&nbsp;<em>indivisible<\/em>&nbsp;and&nbsp;<em>must be treated as a unit<\/em>. Some common datatypes are:<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li>INTEGER<\/li><li>REAL<\/li><li>CHARACTER(n)<\/li><li>CHARACTER VARYING(n)<\/li><\/ul>\n\n\n\n<p class=\"wp-block-paragraph\">Most relational databases today provide more complex types such as Binary Large Objects (BLOBs) and Character Large Objects (CLOBs), which may in fact be considered divisble by the user (although perhaps not by the database itself).<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">The value of a particular data type can:<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li>Have a value of that data type, or:<\/li><li>Be&nbsp;<em><strong>NULL<\/strong><\/em>, which indicates no value, or unknown value.<\/li><\/ul>\n\n\n\n<h4 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Relational_Operations\"><\/span>Relational Operations<span class=\"ez-toc-section-end\"><\/span><\/h4>\n\n\n\n<p class=\"wp-block-paragraph\">The Relational Model defines a number of operations that may be performed on tables, on rows, and on individual data elements.<\/p>\n\n\n\n<h5 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Selection\"><\/span>Selection<span class=\"ez-toc-section-end\"><\/span><\/h5>\n\n\n\n<p class=\"wp-block-paragraph\">The most important operation is&nbsp;<em><strong>selection<\/strong><\/em>. This is the operation of selecting one or more rows from a table which satisfy one or more&nbsp;<em><strong>predicates<\/strong><\/em>. A predicate is a statement about the truth of something (for example, whether the employee ID is equal to 1234, or whether his\/her salary is higher than $20000.) The rows in the table are filtered on the basis of whether they satisfy the predicate(s).<\/p>\n\n\n\n<h5 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Join\"><\/span>Join<span class=\"ez-toc-section-end\"><\/span><\/h5>\n\n\n\n<p class=\"wp-block-paragraph\">A common relational operation is that of a&nbsp;<em><strong>join<\/strong><\/em>. A join involves one or more tables, and involves combining rows and columns from those tables into a new set of data which also forms a (temporary) table with a new set of columns and rows. For example, if you wished to list all the departments and their managers in the above employees example, you would have to perform a combination of a&nbsp;<em>join<\/em>&nbsp;operation and a&nbsp;<em>selection<\/em>&nbsp;operation to come up with a table that looks something like:<\/p>\n\n\n\n<figure class=\"wp-block-table\"><table><tbody><tr><th><strong>Department<\/strong><\/th><th><strong>Manager<\/strong><\/th><\/tr><tr><td>Accounting<\/td><td>Alfred J Prufrock<\/td><\/tr><tr><td>Sales<\/td><td>Charles Schultz<\/td><\/tr><tr><td>Information Technology<\/td><td>Barbara Smith<\/td><\/tr><tr><td><strong>&#8230;<\/strong><\/td><td>&nbsp;<\/td><\/tr><tr><td><strong>&#8230;<\/strong><\/td><td>&nbsp;<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<h5 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Union\"><\/span>Union<span class=\"ez-toc-section-end\"><\/span><\/h5>\n\n\n\n<p class=\"wp-block-paragraph\">It sometimes happens that two or more tables have the same structure of columns and data types. If you wished to produce a report that combined the data from the two tables, you would perform a&nbsp;<em><strong>union<\/strong><\/em>&nbsp;operation to accomplish this. For example, you might have a&nbsp;<strong>Customers<\/strong>&nbsp;table which could contain similar information to the&nbsp;<strong>Employees<\/strong>&nbsp;table (except of course for the&nbsp;<strong>Employee ID<\/strong>&nbsp;and&nbsp;<strong>Department<\/strong>&nbsp;columns). You could use a combination of selection, join and union to come up with a table that contains rows of data from both the&nbsp;<strong>Employees<\/strong>&nbsp;and the&nbsp;<strong>Customers<\/strong>&nbsp;tables.<\/p>\n\n\n\n<h4 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Closure\"><\/span>Closure<span class=\"ez-toc-section-end\"><\/span><\/h4>\n\n\n\n<p class=\"wp-block-paragraph\">A very important aspect of the relational model is the concept of&nbsp;<em><strong>closure<\/strong><\/em>, which says that&nbsp;<em><strong>the result of any operation on one or more relations is also a relation.<\/strong><\/em><\/p>\n\n\n\n<h4 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"The_Transaction_Model\"><\/span>The Transaction Model<span class=\"ez-toc-section-end\"><\/span><\/h4>\n\n\n\n<p class=\"wp-block-paragraph\">Most relational databases support the concept of a&nbsp;<em><strong>transaction<\/strong><\/em>. A transaction is a unit of work performed against a database which must either completely succeed or completely not succeed. For example, imagine that you work for a bank, and you wish to transfer money from one account to another account. To accomplish this, you would have to perform the following steps in the following sequence:<\/p>\n\n\n\n<ol class=\"wp-block-list\"><li>Withdraw the desired amount from the first account<\/li><li>Deposit that amount into the second account<\/li><\/ol>\n\n\n\n<p class=\"wp-block-paragraph\">But what if something went wrong after step 1) that prevented you from completing step 2)? Without the concept of a transaction, you would likely lose track of the money withdrawn, and the banks books would no longer balance. If you perform the two steps within a transaction, the database takes care to ensure that one of the following occurs:<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li>(Eventually) both actions are completed,&nbsp;<\/li><\/ul>\n\n\n\n<p class=\"wp-block-paragraph\">or:<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li>(Eventually) neither action is completed.<\/li><\/ul>\n\n\n\n<h5 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Commit_vs_Rollback\"><\/span>Commit vs Rollback<span class=\"ez-toc-section-end\"><\/span><\/h5>\n\n\n\n<p class=\"wp-block-paragraph\">When a transaction is ready to complete all its operations, it asks the system to&nbsp;<em><strong>commit<\/strong><\/em>&nbsp;those actions.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">When a transaction discovers difficulty with performing its actions, and decides that all those actions cannot be performed, then it asks the system to&nbsp;<em><strong>rollback<\/strong><\/em>&nbsp;any actions that it has (provisionally) performed.<\/p>\n\n\n\n<h5 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"The_ACID_Properties\"><\/span>The ACID Properties<span class=\"ez-toc-section-end\"><\/span><\/h5>\n\n\n\n<p class=\"wp-block-paragraph\">It is generally agreed that a database transaction must have the following &#8220;<em><strong>ACID<\/strong><\/em>&#8221; properties:<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li><strong>Atomic<\/strong>: A transaction should either complete all its actions, or none of them should be performed. (For example, a money transfer is guaranteed either to completely succeed, or to have no effects on the system.)<\/li><li><strong>Consistent<\/strong>: A transaction should only perform correct transformations between valid system states (For example, An employee should always have an employee ID and an associated department; A department should always have a Manager assigned, etc.)<\/li><li><strong>Isolated<\/strong>: While a transaction is performing changes in a system&#8217;s state, the data may at various points be inconsistent. Such inconsistency should not be visible to other transactions. The system must give a transation the illusion that it is running in isolation.<\/li><li><strong>Durable<\/strong>: Once a transaction has committed, the changes it has made to the database must be preserved, even if the system crashes (due to either hardware or software failure).<\/li><\/ul>\n\n\n\n<h3 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Structured_Query_Language_SQL\"><\/span>Structured Query Language (SQL)<span class=\"ez-toc-section-end\"><\/span><\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">The most common language used with relational databases is&nbsp;<em><strong>Structured Query Language<\/strong><\/em>&nbsp;(<em><strong>SQL<\/strong><\/em>, often pronounced &#8220;Sequel&#8221;), which was invented by IBM in the 1970s. It became an ANSI (Americal National Standards Institute) standard in 1986 (SQL-86), and this was followed by an updated standard in 1989 (SQL-89). In 1992, ANSI and ISO (International Standards Organization) published an updated standard for SQL, called SQL-92.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">SQL is rather an unwieldy language, and it is (especially as specified in more recent standards) an extremely large language. Unfortunately, despite the standardization efforts, and because of the dominance of a small number of database vendors who have proprietary implementations of the SQL language, with their own extensions and quirks, a true, practical, SQL standard is still not a reality.<\/p>\n\n\n\n<h4 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"SQL_is_a_Set-Oriented_Language\"><\/span>SQL is a Set-Oriented Language<span class=\"ez-toc-section-end\"><\/span><\/h4>\n\n\n\n<p class=\"wp-block-paragraph\">It is important to realize that SQL is a&nbsp;<em><strong>set-oriented language<\/strong><\/em>. This means that most operations performed using SQL statements do not operate on a single row in a table, but instead operate on multiple rows in a table.<\/p>\n\n\n\n<h4 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Categories_of_SQL_Statements\"><\/span>Categories of SQL Statements<span class=\"ez-toc-section-end\"><\/span><\/h4>\n\n\n\n<p class=\"wp-block-paragraph\">SQL statements may be categorized into the following groups:<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li>Data Manipulation Language (DML) statements:<ul><li>SELECT statements<\/li><li>INSERT, DELETE and UPDATE statements<\/li><\/ul><\/li><li>Data Definition Language (DDL) statements:<ul><li>CREATE &#8230; statements<\/li><li>ALTER&#8230; statements<\/li><\/ul><\/li><li>GRANT statements<\/li><li>Transaction Management statements<ul><li>SET TRANSACTION statements<\/li><li>COMMIT statements<\/li><li>ROLLBACK statements<\/li><\/ul><\/li><\/ul>\n\n\n\n<h4 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"References\"><\/span>References<span class=\"ez-toc-section-end\"><\/span><\/h4>\n\n\n\n<p class=\"wp-block-paragraph\">SQL is a very complex language, and I cannot hope to give it more than introductory coverage here. There are quite a few books that do a much more comprehensive job than I can of describing the SQL language, including the following:<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li><em><strong>Understanding the New SQL : A Complete Guide (The Morgan Kaufmann Series in Data Management Systems)<\/strong><\/em>&nbsp;by Jim Melton &amp; Alan R. Simon (March 1993) Morgan Kaufmann Publishers; ISBN: 1558602453<\/li><li><em><strong>Understanding the New SQL : A Complete Guid<\/strong><\/em><strong>e<\/strong>&nbsp;by Jim Melton &amp; Alan Simon 2nd edition (<em><strong>to be published December 2000<\/strong><\/em>) Morgan Kaufmann Publishers; ISBN: 1558604561<\/li><li><em><strong>A Guide to the SQL Standard : A User&#8217;s Guide to the Standard Database Language SQL<\/strong><\/em>&nbsp;by Hugh Darwen &amp; C. J. Date, 4th edition (April 1997) Addison-Wesley Pub Co; ISBN: 0201964260<\/li><\/ul>\n\n\n\n<p class=\"wp-block-paragraph\">For the classic book on databases, take a look at:<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li><em><strong>An Introduction to Database Systems<\/strong><\/em>&nbsp;by C. J. Date 7th edition (October 1999) Addison-Wesley Pub Co; ISBN: 0201385902<\/li><\/ul>\n\n\n\n<p class=\"wp-block-paragraph\">(but note that it&#8217;s not exactly bedtime reading&#8230;)<\/p>\n\n\n\n<h3 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"DML_Statements\"><\/span>DML Statements<span class=\"ez-toc-section-end\"><\/span><\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">DML stands for <strong>Data Manipulation Language<\/strong><\/p>\n\n\n\n<h4 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Categories_of_DML_Statements\"><\/span>Categories of DML Statements<span class=\"ez-toc-section-end\"><\/span><\/h4>\n\n\n\n<p class=\"wp-block-paragraph\">Here is a summary of the categories of SQL statements covered in this section:<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li>If you wish to&nbsp;<em><strong>read<\/strong><\/em>&nbsp;data from tables, you use a&nbsp;<strong>SELECT<\/strong>&nbsp;statement<\/li><li>To&nbsp;<em><strong>enter<\/strong><\/em>&nbsp;one or more new rows of data into a table, you use an&nbsp;<strong>INSERT<\/strong>&nbsp;statement<\/li><li>To&nbsp;<em><strong>remove<\/strong><\/em>&nbsp;one or more rows of data from a table, you use a&nbsp;<strong>DELETE<\/strong>&nbsp;statement<\/li><li>To&nbsp;<em><strong>change<\/strong><\/em>&nbsp;one or more rows of data in a table, you use an&nbsp;<strong>UPDATE<\/strong>&nbsp;statement<\/li><\/ul>\n\n\n\n<h4 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"SELECT_Statements\"><\/span>SELECT&nbsp;Statements<span class=\"ez-toc-section-end\"><\/span><\/h4>\n\n\n\n<p class=\"wp-block-paragraph\">The most commonly used statement in SQL is the&nbsp;<em><strong>SELECT statement<\/strong><\/em>. It is used to read data from one or more tables.<\/p>\n\n\n\n<h5 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"SELECT_Statements_vs_Query_Expressions\"><\/span>SELECT&nbsp;Statements vs Query Expressions<span class=\"ez-toc-section-end\"><\/span><\/h5>\n\n\n\n<p class=\"wp-block-paragraph\">There are actually two contexts in which a SELECT may be executed:<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li>To&nbsp;<em><strong>initiate<\/strong><\/em>&nbsp;a SELECT statement<\/li><li>To define or return a&nbsp;<em><strong>table-valued expression<\/strong><\/em>&nbsp;(often called a&nbsp;<em><strong>query expression<\/strong><\/em>)<\/li><\/ul>\n\n\n\n<p class=\"wp-block-paragraph\">The difference is that a table-valued expression has no concept of ordering, while a SELECT statement may return rows in a specified order (specified in an&nbsp;<em><strong>ORDER BY clause<\/strong><\/em>). If no ORDER BY clause is specified, then the results of a SELECT statement may be returned in any order.<\/p>\n\n\n\n<h5 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Simple_SELECT_Statements\"><\/span>Simple&nbsp;SELECT&nbsp;Statements<span class=\"ez-toc-section-end\"><\/span><\/h5>\n\n\n\n<p class=\"wp-block-paragraph\">Here is an example of the simplest kind of SELECT statement:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\"><strong>SELECT name \nFROM employees;<\/strong><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">which, when given the table <strong>Employees<\/strong>:<\/p>\n\n\n\n<figure class=\"wp-block-table\"><table><tbody><tr><th><strong>Employee ID<\/strong><\/th><th><strong>Name<\/strong><\/th><th><strong>Department<\/strong><\/th><th><strong>Age<\/strong><\/th><th><strong>Salary<\/strong><\/th><\/tr><tr><td>10063<\/td><td>Alfred J Prufrock<\/td><td>10<\/td><td>45<\/td><td>120000<\/td><\/tr><tr><td>204567<\/td><td>Angela Parfitt<\/td><td>30<\/td><td>36<\/td><td>78000<\/td><\/tr><tr><td>345890<\/td><td>Michael Solistes<\/td><td>20<\/td><td>28<\/td><td>90000<\/td><\/tr><tr><td>89567<\/td><td>Charles Schultz<\/td><td>20<\/td><td>56<\/td><td>230000<\/td><\/tr><tr><td>56435<\/td><td>Barbara Smith<\/td><td>30<\/td><td>48<\/td><td>134000<\/td><\/tr><tr><td><strong>&#8230;<\/strong><\/td><td>&nbsp;<\/td><td>&nbsp;<\/td><td>&nbsp;<\/td><td>&nbsp;<\/td><\/tr><tr><td><strong>&#8230;<\/strong><\/td><td>&nbsp;<\/td><td>&nbsp;<\/td><td>&nbsp;<\/td><td>&nbsp;<\/td><\/tr><tr><td><strong>&#8230;<\/strong><\/td><td>&nbsp;<\/td><td>&nbsp;<\/td><td>&nbsp;<\/td><td>&nbsp;<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\">would give the results:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\"><strong>Alfred J Prufrock\nAngela Parfitt\nMichael Solistes\nCharles Schultz\nBarbara Smith\n...<\/strong><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">Note that the result of this SELECT statement is a table-valued expression, which conforms to the same rules as a table, so I could represent this as a table.<\/p>\n\n\n\n<blockquote class=\"wp-block-quote is-layout-flow wp-block-quote-is-layout-flow\"><p><strong><em><u>Note:<\/u><\/em><\/strong>&nbsp;SQL is a language case insensitive language. Thus,&nbsp;SELECT,&nbsp;select,&nbsp;Select, and&nbsp;seLect&nbsp;are all equivalent. However, for the purposes of distinguishing between SQL keywords and user-defined names, it is often useful to use a convention. Here, I use the convention that SQL keywords are uppercased, while user-defined names are lowercased.<\/p><\/blockquote>\n\n\n\n<h5 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Specifying_Multiple_Columns\"><\/span>Specifying Multiple Columns<span class=\"ez-toc-section-end\"><\/span><\/h5>\n\n\n\n<p class=\"wp-block-paragraph\">We could form a slightly more complex SELECT statement by returning more than one column from the table:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\"><strong>SELECT name, age, salary \nFROM employees;<\/strong><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">which produces the following table-valued expression:<\/p>\n\n\n\n<figure class=\"wp-block-table\"><table><tbody><tr><th><strong>Name<\/strong><\/th><th><strong>Age<\/strong><\/th><th><strong>Salary<\/strong><\/th><\/tr><tr><td>Alfred J Prufrock<\/td><td>45<\/td><td>120000<\/td><\/tr><tr><td>Angela Parfitt<\/td><td>36<\/td><td>78000<\/td><\/tr><tr><td>Michael Solistes<\/td><td>28<\/td><td>90000<\/td><\/tr><tr><td>Charles Schultz<\/td><td>56<\/td><td>230000<\/td><\/tr><tr><td>Barbara Smith<\/td><td>48<\/td><td>134000<\/td><\/tr><tr><td><strong>&#8230;<\/strong><\/td><td>&nbsp;<\/td><td>&nbsp;<\/td><\/tr><tr><td><strong>&#8230;<\/strong><\/td><td>&nbsp;<\/td><td>&nbsp;<\/td><\/tr><tr><td><strong>&#8230;<\/strong><\/td><td>&nbsp;<\/td><td>&nbsp;<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<h5 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Specifying_the_Order_of_Rows_Returned_by_a_SELECT_Statement\"><\/span>Specifying the Order of Rows Returned by a&nbsp;SELECT&nbsp;Statement<span class=\"ez-toc-section-end\"><\/span><\/h5>\n\n\n\n<p class=\"wp-block-paragraph\">If we are executing a SELECT statement (as opposed to a query expression), we may specify the order in which the rows are returned:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\"><strong>SELECT name, age, salary \nFROM employees\nORDER BY salary;<\/strong><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">this will result in the same set of rows being returned as in the above example, except that the order in which those rows are returned is based on the values in the row&#8217;s Salary column.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Note that it is possible to specify the order even if the ordered column is not returned:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\"><strong>SELECT name, age \nFROM employees\nORDER BY salary;<\/strong><\/pre>\n\n\n\n<h5 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"The_WHERE_clause\"><\/span>The&nbsp;WHERE&nbsp;clause<span class=\"ez-toc-section-end\"><\/span><\/h5>\n\n\n\n<p class=\"wp-block-paragraph\">If you wish to filter the results of a SELECT statement by some criteria (called&nbsp;<em><strong>predicates<\/strong><\/em>), you can specify those predicates in a WHERE clause:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\"><strong>SELECT name, age, salary \nFROM employees\nWHERE salary &gt; 100000;<\/strong><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">You may use a WHERE clause in a SELECT statement and also in a query expression.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">In a SELECT statement, you can combine the WHERE and ORDER BY clauses:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\"><strong>SELECT name, age, salary \nFROM employees\nWHERE salary &gt; 100000\nORDER BY salary;<\/strong><\/pre>\n\n\n\n<h5 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Using_Multiple_Predicates\"><\/span>Using Multiple Predicates<span class=\"ez-toc-section-end\"><\/span><\/h5>\n\n\n\n<p class=\"wp-block-paragraph\">You may combine predicates in a WHERE clause using AND, OR and NOT operators:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\"><strong>SELECT name, age, salary \nFROM employees\nWHERE salary &gt; 100000\n  AND age &lt; 50;<\/strong><\/pre>\n\n\n\n<h5 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Performing_Joins\"><\/span>Performing Joins<span class=\"ez-toc-section-end\"><\/span><\/h5>\n\n\n\n<p class=\"wp-block-paragraph\">If you wish to perform a join across one or more tables, you can list more than one table in the FROM clause:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\"><strong>SELECT employees.name, departments.name, age, salary\nFROM employees, departments;<\/strong><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">(Note that because both the Employees table and Departments table have a column called Name, we have to explicitly specify which one we want by explicit qualification.)<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">However, if you do not use a WHERE clause, this produces a table-valued expression with a very large&nbsp;<em><strong>cardinality<\/strong><\/em>&nbsp;(row count), because it matches every row in the employees table with every row in the departments table. So, if you have, say, 1000 employees and a 100 departments, this SELECT would return 1000 * 100 = 100,000 rows. (This is called the&nbsp;<em><strong>cartesian product<\/strong><\/em>&nbsp;of the two tables.) Rarely is this useful, and it consumes large amounts of resources to compute the result!<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Instead, when you use multiple tables in a SELECT, you almost always should also use a WHERE clause to restrict the number of rows returned.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">For example, if you want to list all employees names, followed by the name of their department, followed by their age and salary, you would use the following SELECT statement:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\"><strong>SELECT employees.name, departments.name, age, salary \nFROM employees, departments\nWHERE department = departmentID;<\/strong><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">This kind of join is called a&nbsp;<em><strong>natural equi-join<\/strong><\/em>, because it selects rows from the two tables that have equal values in the relevant columns.<\/p>\n\n\n\n<h5 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Other_Kinds_of_Joins\"><\/span>Other Kinds of Joins<span class=\"ez-toc-section-end\"><\/span><\/h5>\n\n\n\n<p class=\"wp-block-paragraph\">There are various other kinds of joins that can be accomplished using a SELECT statement, such as&nbsp;<em><strong>outer joins<\/strong><\/em>&nbsp;and&nbsp;<em><strong>unions<\/strong><\/em>, but these are more advanced features that we won&#8217;t cover in this introduction.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Take a look at the references noted earlier for more details.<\/p>\n\n\n\n<h4 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"INSERT_Statements\"><\/span>INSERT&nbsp;Statements<span class=\"ez-toc-section-end\"><\/span><\/h4>\n\n\n\n<p class=\"wp-block-paragraph\">If you wish to enter new rows of data into a table, you use an <em><strong>INSERT <\/strong><\/em><em><strong>statement<\/strong><\/em>. For example:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\"><strong>INSERT INTO employees\n(employeeID, name, department, age, salary)\nVALUES (234134, 'Frodo Benitas', 20, 23, 34000);<\/strong><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">will enter a new employee into the <strong>Employees<\/strong> table. In this example, each column name is specified explicitly by name in the first list. However, if you wish to enter a value for every column, and rely on the &#8220;natural order&#8221; of the columns in the table, you may omit the first list entirely:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\"><strong>INSERT INTO employees\nVALUES (234134, 'Frodo Benitas', 20, 23, 34000);<\/strong><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">However, this requires you to know the order of the column names, and to supply a value for every column in the row. Sometimes, you don&#8217;t know the value for a column, and would prefer to leave it blank. If you do this:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\"><strong>INSERT INTO employees\n(employeeID, name, department, salary)\nVALUES (234134, 'Frodo Benitas', 20, 34000);<\/strong><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">where the age was omitted, then a&nbsp;<em><strong>null value<\/strong><\/em>&nbsp;will be placed in the age column for that row. However, it would be clearer if you were explicit about the assignment of a null value:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\"><strong>INSERT INTO employees\n(employeeID, name, department, age, salary)\nVALUES (234134, 'Frodo Benitas', 20, NULL, 34000);<\/strong><\/pre>\n\n\n\n<h4 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"DELETE_Statements\"><\/span>DELETE&nbsp;Statements<span class=\"ez-toc-section-end\"><\/span><\/h4>\n\n\n\n<p class=\"wp-block-paragraph\">To remove one or more rows of data from a table, you use the&nbsp;<em><strong>DELETE<\/strong><\/em><em><strong>&nbsp;statement<\/strong><\/em>. For example:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\"><strong>DELETE FROM employees\nWHERE name = 'Frodo Benitas';<\/strong><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">will remove the row for Frodo Benitas from the Employees table. If there is more than one employee called Frodo Benitas, it will delete the others, also.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Beware! The statement:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\"><strong>DELETE FROM employees;<\/strong><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">will cause&nbsp;<em><strong>all rows<\/strong><\/em>&nbsp;in the Employees table to be deleted!<\/p>\n\n\n\n<h4 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"UPDATE_Statements\"><\/span>UPDATE&nbsp;Statements<span class=\"ez-toc-section-end\"><\/span><\/h4>\n\n\n\n<p class=\"wp-block-paragraph\">To change the data in one or more rows of a table, use the&nbsp;<em><strong>UPDATE<\/strong><\/em><em><strong>&nbsp;statement<\/strong><\/em>. For example:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\"><strong>UPDATE employees\nSET name = 'Fred Benitas',\n    age = 34\nWHERE employeeID = 234134;<\/strong><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">will change the name and age of an existing row in the Employees table. Presumably, the employeeID is unique; if it is not, then all employees with employeeID 234134 will have their name and age changed.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"DDL_Statements\"><\/span>DDL Statements<span class=\"ez-toc-section-end\"><\/span><\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">DDL stands for <strong>Data Definition Language<\/strong>.<\/p>\n\n\n\n<h4 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Schemas_and_Schema_Objects\"><\/span>Schemas and Schema Objects<span class=\"ez-toc-section-end\"><\/span><\/h4>\n\n\n\n<p class=\"wp-block-paragraph\">A database consists of a set of&nbsp;<em><strong>schemas<\/strong><\/em>, each of which contains, or owns, a set of&nbsp;<em><strong>schema objects<\/strong><\/em>, such as tables, etc.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Each schema object is created using a&nbsp;<em><strong>CREATE statement<\/strong><\/em>.<\/p>\n\n\n\n<h4 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Creating_Schema_Objects\"><\/span>Creating Schema Objects<span class=\"ez-toc-section-end\"><\/span><\/h4>\n\n\n\n<p class=\"wp-block-paragraph\">Each schema object typically has an associated&nbsp;<em><strong>CREATE statement<\/strong><\/em>&nbsp;which is used to create an instance of that object.<\/p>\n\n\n\n<h4 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Creating_a_Table\"><\/span>Creating a Table<span class=\"ez-toc-section-end\"><\/span><\/h4>\n\n\n\n<p class=\"wp-block-paragraph\">To create a table, you use the&nbsp;<em><strong>CREATE TABLE statement<\/strong><\/em>. For example:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\"><strong>CREATE TABLE employees\n{\n  employeeID   INTEGER,\n  name         CHARACTER(40),\n  department   INTEGER,\n  age          INTEGER,\n  salary       DECIMAL(7,2)\n};<\/strong><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">which creates the Employees table specified earlier. Each column is specified using a name and a&nbsp;<em><strong>data type<\/strong><\/em>.<\/p>\n\n\n\n<h4 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"SQL_Data_Types\"><\/span>SQL Data Types<span class=\"ez-toc-section-end\"><\/span><\/h4>\n\n\n\n<p class=\"wp-block-paragraph\">Here are some of the valid SQL data types:<\/p>\n\n\n\n<figure class=\"wp-block-table\"><table><tbody><tr><th><strong>Data Type<\/strong><\/th><th><strong>Description<\/strong><\/th><\/tr><tr><td>CHARACTER(n)<\/td><td>A character field containing exactly n characters<\/td><\/tr><tr><td>CHARACTER VARYING(n)<\/td><td>A character field containing up to n characters<\/td><\/tr><tr><td>INTEGER<\/td><td>An integer field<\/td><\/tr><tr><td>SMALLINT<\/td><td>An integer field of smaller size than INTEGER<\/td><\/tr><tr><td>NUMERIC(p, s)<\/td><td>A numeric field specifying precision p, and scale s<\/td><\/tr><tr><td>DECIMAL(p, s)<\/td><td>A decimal field specifying precision p, and scale s<\/td><\/tr><tr><td>REAL<\/td><td>A single precision floating point value<\/td><\/tr><tr><td>DOUBLE PRECISION<\/td><td>A double precision floating point value<\/td><\/tr><tr><td>FLOAT(p)<\/td><td>A floating point value with precision p<\/td><\/tr><tr><td>DATE<\/td><td>Year, month and day values of a date.<\/td><\/tr><tr><td>TIME<\/td><td>Hour, minute, second values of a time<\/td><\/tr><tr><td>TIMESTAMP<\/td><td>Year, month, day, hour, minute and second values<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<h4 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Changing_Schema_Objects\"><\/span>Changing Schema Objects<span class=\"ez-toc-section-end\"><\/span><\/h4>\n\n\n\n<p class=\"wp-block-paragraph\">Once a schema object has been created, it may be possible to change its attributes (depending on the type of object, and the support from the database vendor), using an&nbsp;<em><strong>ALTER statement.<\/strong><\/em><\/p>\n\n\n\n<h4 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Changing_the_Attributes_of_a_Table\"><\/span>Changing the Attributes of a Table<span class=\"ez-toc-section-end\"><\/span><\/h4>\n\n\n\n<p class=\"wp-block-paragraph\">You can change some (usually not all) of the attributes of a table using the&nbsp;<em><strong>ALTER TABLE statement<\/strong><\/em>:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\"><strong>ALTER TABLE employees\nADD COLUMN last_review DATE;<\/strong><\/pre>\n\n\n\n<pre class=\"wp-block-preformatted\"><strong>ALTER TABLE departments\nDROP COLUMN manager;<\/strong><\/pre>\n\n\n\n<h4 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Removing_Schema_Objects\"><\/span>Removing Schema Objects<span class=\"ez-toc-section-end\"><\/span><\/h4>\n\n\n\n<p class=\"wp-block-paragraph\">You can use a&nbsp;<em><strong>DROP statement<\/strong><\/em>&nbsp;to remove an existing schema object.<\/p>\n\n\n\n<h4 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Removing_a_Table\"><\/span>Removing a Table<span class=\"ez-toc-section-end\"><\/span><\/h4>\n\n\n\n<p class=\"wp-block-paragraph\">You use the&nbsp;<em><strong>DROP TABLE statement<\/strong><\/em>&nbsp;to remove an existing table:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\"><strong>DROP TABLE employees;<\/strong><\/pre>\n\n\n\n<blockquote class=\"wp-block-quote is-layout-flow wp-block-quote-is-layout-flow\"><p><strong><em><u>Note:<\/u><\/em><\/strong>\u00a0Do not confuse the\u00a0<strong>DROP<\/strong>\u00a0statement with the\u00a0<strong>DELETE<\/strong>\u00a0statement!<\/p><\/blockquote>\n\n\n\n<h3 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Transaction_Statements\"><\/span>Transaction Statements<span class=\"ez-toc-section-end\"><\/span><\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">Transaction statements have to do with <strong>Transaction Management<\/strong>.<\/p>\n\n\n\n<h4 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Starting_a_Transaction\"><\/span>Starting a Transaction<span class=\"ez-toc-section-end\"><\/span><\/h4>\n\n\n\n<p class=\"wp-block-paragraph\">In SQL, there is no explicit statement that starts a transaction. A transaction will be started implicitly (if one isn&#8217;t already started) whenever you execute a SQL statement that requires a transaction context. If you don&#8217;t tell the system differently, the transaction will have the following default characteristics:<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li>It will permit both&nbsp;<em><strong>read<\/strong><\/em>&nbsp;and&nbsp;<em><strong>write (update)<\/strong><\/em>&nbsp;operations<\/li><li>It will have the&nbsp;<em><strong>maximum possible isolation<\/strong><\/em>&nbsp;from other concurrent transactions<\/li><\/ul>\n\n\n\n<p class=\"wp-block-paragraph\">If you want to change these characteristics, you must use the SET TRANSACTION statement to explicitly set them:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\"><strong>SET TRANSACTION\nREAD ONLY,\nISOLATION LEVEL READ UNCOMMITTED<\/strong><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">The SET TRANSACTION statement must be the&nbsp;<em><strong>first<\/strong><\/em>&nbsp;statement executed in a transaction.<\/p>\n\n\n\n<h4 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Committing_a_Transaction\"><\/span>Committing a Transaction<span class=\"ez-toc-section-end\"><\/span><\/h4>\n\n\n\n<p class=\"wp-block-paragraph\">Once all the operations in a transaction have successfully completed, you can&nbsp;<em><strong>commit<\/strong><\/em>&nbsp;those changes by using the COMMIT statement:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\"><strong>COMMIT;<\/strong><\/pre>\n\n\n\n<h4 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Rolling_Back_a_Transaction\"><\/span>Rolling Back a Transaction<span class=\"ez-toc-section-end\"><\/span><\/h4>\n\n\n\n<p class=\"wp-block-paragraph\">If you decide that a transaction&#8217;s actions should not be (or cannot be) completed, you can roll those actions back by using the ROLLBACK statement:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\"><strong>ROLLBACK;<\/strong><\/pre>\n\n\n\n<h4 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Mixing_DML_and_DDL_in_a_Transaction\"><\/span>Mixing DML and DDL in a Transaction<span class=\"ez-toc-section-end\"><\/span><\/h4>\n\n\n\n<p class=\"wp-block-paragraph\">Some database systems allow you to mix DML and DDL in a single transaction. For example:<\/p>\n\n\n\n<ol class=\"wp-block-list\"><li>Update a row in the Employees table, then<\/li><li>Create a new table, Customers, then<\/li><li>Delete a row from the Departments table<\/li><li>etc.<\/li><\/ol>\n\n\n\n<p class=\"wp-block-paragraph\">Other database systems to not allow you do mix the two. If you attempt to mix them, you may encounter errors, or you may find that a single DDL statement execution will cause the transaction to commit implicitly.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">If you are writing code that should run against several database systems, you would be well advised to avoid such mixing of DML and DDL.<\/p>\n\n\n\n<h4 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"AutoCommit_Mode\"><\/span>AutoCommit Mode<span class=\"ez-toc-section-end\"><\/span><\/h4>\n\n\n\n<p class=\"wp-block-paragraph\">Some systems (such as ODBC and JDBC) have a mode called&nbsp;<em><strong>autocommit mode<\/strong><\/em>, which causes an implicit and automatic commit to occur after every SQL statement execution.&nbsp; For anything other than a trivial transaction, this is not an appropriate choice.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Database access is a very important feature for almost any application in these times.\u00a0 Java provides database access in a very portable way, using JDBC (Java DataBase Connectivity). Relational Databases Relational databases implement the&nbsp;Relational Model&nbsp;formulated by E.F. Codd in his seminal paper&nbsp;&#8220;A Relational Model of Data for Large Shared Data Banks&#8221;, Communications of the ACM, [&hellip;]<\/p>\n","protected":false},"author":2,"featured_media":0,"parent":19,"menu_order":0,"comment_status":"closed","ping_status":"closed","template":"","meta":{"_eb_attr":"","ocean_front_end_style_editor":"no","ocean_post_layout":"left-sidebar","ocean_both_sidebars_style":"","ocean_both_sidebars_content_width":0,"ocean_both_sidebars_sidebars_width":0,"ocean_sidebar":"ocs-course-topics-sidebar","ocean_second_sidebar":"0","ocean_disable_margins":"enable","ocean_add_body_class":"","ocean_shortcode_before_top_bar":"","ocean_shortcode_after_top_bar":"","ocean_shortcode_before_header":"","ocean_shortcode_after_header":"","ocean_has_shortcode":"","ocean_shortcode_after_title":"","ocean_shortcode_before_footer_widgets":"","ocean_shortcode_after_footer_widgets":"","ocean_shortcode_before_footer_bottom":"","ocean_shortcode_after_footer_bottom":"","ocean_display_top_bar":"default","ocean_display_header":"default","ocean_header_style":"","ocean_center_header_left_menu":"0","ocean_custom_header_template":"0","ocean_custom_logo":0,"ocean_custom_retina_logo":0,"ocean_custom_logo_max_width":0,"ocean_custom_logo_tablet_max_width":0,"ocean_custom_logo_mobile_max_width":0,"ocean_custom_logo_max_height":0,"ocean_custom_logo_tablet_max_height":0,"ocean_custom_logo_mobile_max_height":0,"ocean_header_custom_menu":"0","ocean_menu_typo_font_family":"0","ocean_menu_typo_font_subset":"","ocean_menu_typo_font_size":0,"ocean_menu_typo_font_size_tablet":0,"ocean_menu_typo_font_size_mobile":0,"ocean_menu_typo_font_size_unit":"px","ocean_menu_typo_font_weight":"","ocean_menu_typo_font_weight_tablet":"","ocean_menu_typo_font_weight_mobile":"","ocean_menu_typo_transform":"","ocean_menu_typo_transform_tablet":"","ocean_menu_typo_transform_mobile":"","ocean_menu_typo_line_height":0,"ocean_menu_typo_line_height_tablet":0,"ocean_menu_typo_line_height_mobile":0,"ocean_menu_typo_line_height_unit":"","ocean_menu_typo_spacing":0,"ocean_menu_typo_spacing_tablet":0,"ocean_menu_typo_spacing_mobile":0,"ocean_menu_typo_spacing_unit":"","ocean_menu_link_color":"","ocean_menu_link_color_hover":"","ocean_menu_link_color_active":"","ocean_menu_link_background":"","ocean_menu_link_hover_background":"","ocean_menu_link_active_background":"","ocean_menu_social_links_bg":"","ocean_menu_social_hover_links_bg":"","ocean_menu_social_links_color":"","ocean_menu_social_hover_links_color":"","ocean_disable_title":"default","ocean_disable_heading":"default","ocean_post_title":"","ocean_post_subheading":"","ocean_post_title_style":"","ocean_post_title_background_color":"","ocean_post_title_background":0,"ocean_post_title_bg_image_position":"","ocean_post_title_bg_image_attachment":"","ocean_post_title_bg_image_repeat":"","ocean_post_title_bg_image_size":"","ocean_post_title_height":0,"ocean_post_title_bg_overlay":0.5,"ocean_post_title_bg_overlay_color":"","ocean_disable_breadcrumbs":"default","ocean_breadcrumbs_color":"","ocean_breadcrumbs_separator_color":"","ocean_breadcrumbs_links_color":"","ocean_breadcrumbs_links_hover_color":"","ocean_display_footer_widgets":"default","ocean_display_footer_bottom":"default","ocean_custom_footer_template":"0","footnotes":""},"class_list":["post-542","page","type-page","status-publish","hentry","entry"],"_links":{"self":[{"href":"https:\/\/bhiggs.x10hosting.com\/HighOctaneJava\/wp-json\/wp\/v2\/pages\/542","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/bhiggs.x10hosting.com\/HighOctaneJava\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/bhiggs.x10hosting.com\/HighOctaneJava\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/bhiggs.x10hosting.com\/HighOctaneJava\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/bhiggs.x10hosting.com\/HighOctaneJava\/wp-json\/wp\/v2\/comments?post=542"}],"version-history":[{"count":5,"href":"https:\/\/bhiggs.x10hosting.com\/HighOctaneJava\/wp-json\/wp\/v2\/pages\/542\/revisions"}],"predecessor-version":[{"id":552,"href":"https:\/\/bhiggs.x10hosting.com\/HighOctaneJava\/wp-json\/wp\/v2\/pages\/542\/revisions\/552"}],"up":[{"embeddable":true,"href":"https:\/\/bhiggs.x10hosting.com\/HighOctaneJava\/wp-json\/wp\/v2\/pages\/19"}],"wp:attachment":[{"href":"https:\/\/bhiggs.x10hosting.com\/HighOctaneJava\/wp-json\/wp\/v2\/media?parent=542"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}