0.3 Query Boxes and Executing SQL
Query Boxes within the Text
It is well known that practicing skills along with understanding the concepts is a powerful way to learn and to develop new skills. To facilitate this process, query boxes have been placed throughout the textbook to demonstrate SQL statement execution and so that you can practice writing SQL statements.
Figure 0.2 illustrates a typical query box. The SQL in the query box is a valid statement illustrating correct syntax and form. Clicking on the “Run Query” button sends the query to the appropriate DBMS, which executes the query and returns the result. Note the DBMS that receives the query is the one that is assigned by the Advanced Options selection, which was explained earlier. The query box includes, behind the scenes, a connection string to connect to the DBMS and then to run the query and capture the result to format and display on the page.
Right above the query box is a link to the database model that is connected to that query box. Clicking on that link opens a new tab that displays the database model. Displaying the database model facilitates writing the SQL statements.
By clicking anywhere within the query box, the query box goes into edit mode, which permits you to edit the SQL statement or write a new SQL statement. Multiple SQL statements may be written in the box but must be separated by semicolons. The semicolon is the standard termination character for SQL statements for all DBMSs.
The reset icon (half circle with arrow), removes the result display and resets the original SQL statement. The wrench icon at the bottom of the query box displays a settings pop-up window. Figure 0.3 displays the settings box. These are the settings that define how the results are displayed. The default parameters within the settings pop-up window work well, but you may change them as desired.
The Open in Advanced Editor... option takes the SQL statement that is extant in the query box and opens up another page with the Advanced SQL Editor. The Advanced Editor is explained next.
Advanced Editor Page
The Advanced Editor page is similar to the query boxes and executes SQL queries in the same way. It does provide more options to control both the DBMS being used to run the queries and the database being accessed. Figure 0.4 illustrates the Advanced Editor with the two drop-down menus displayed.
The query box contains the original query from the query box in the textbook. It is in edit mode so that you can modify and enter your own SQL statements.
The Database drop-down menu is used to select which DBMS will be used to execute the query. The four supported DBMSs, Oracle, SQL Server, MySQL, and PostgreSQL, are listed first. The “Other (please specify)” option is designed to work with another DBMS on the MyEducator server. Selecting that option opens up another text box for the connection string. The “None” option is simply a placeholder.
The Credentials drop-down menu is used to select which database is to be used. The “Your Student Account (…)” option is the important selection for modifying the database. All of the other databases are “read-only” databases and are protected so that students always get correct values on valid queries. However, as a student you are given an empty sandbox where you can create your own database and insert, modify, and delete data. This option connects you to your sandbox database.
RedCat 1 is a denormalized and simplified version of the RedCat shoes database that is for simple queries from Chapter 2 (Figure 2.1). The RedCat 2 database is the full RedCat shoes database and is used primarily in Chapter 6 (Figure 6.1). The Lorenzo database is for Lorenzo Shipping. The Food Inspection is the Chicago Food Inspection database. The data models for both the Lorenzo Shipping database and the Food Inspection database are given in Appendix A1.