|
|
You've covered a fair bit of ground in this chapter, so let's try to round things up by discussing what all this means for the SQL programmer. Mostly, it's good news. As you've seen, SQL statements (including queries) are written in plain text and don't need to be compiled, unlike programming languages (although they'll be parsed in the database to make sure the syntax is correct). The keywords in SQL make it possible for SQL statements to read almost like English, which makes it relatively easy to quickly understand a SQL statement you haven't seen before—when you've grasped the basic syntax.
The fact that you're able to establish sophisticated relationships between your tables means that you can write a single SQL query that extracts and uses data from several tables. You can pull together all of the data you need from the various tables and present it all in a single "report."
In the examples used in this book (based on an InstantUniversity database), you'll see many examples of one-to-many and many-to-many relationships between the tables, and you'll look at example SQL code to extract related data from these and other tables throughout the book. In fact, Chapter 14, "Case Study: Building a Product Catalog," describes a complete product catalog case study. You'll create basic data structures to hold information about products and about their categories and departments, and you'll also see other techniques to gather reports containing related data from your tables.
If you find yourself using the DDL portion of SQL, then, again, you'll find that the syntax is easy to grasp. For example, the following code creates a simple Course table:
CREATE TABLE Course ( CourseID INT NOT NULL PRIMARY KEY, Name VARCHAR(50), Credits INT);
As you can see, you've created three columns: CourseID, Name, and Credits. You've specified that CourseID will hold integer data types and is a PRIMARY KEY column, so each value in this column must be unique. You've specified that the Name column (the name of the course) will hold character data of varying length but with a maximum for 50 characters. Finally, you've specified that the Credits column will hold integer data types.
Of course, if you're charged with making the decisions as to how to actually store the data, what tables must be created, how they're related, what data types to use, and so on, then a lot of careful thought needs to go into this before you start executing the SQL. Your choices can dramatically impact the integrity of your data and the performance of the applications that rely on your data.
If you're responsible for controlling access to the data in your database, then you need to have a good understanding of the DCL portion of the language. We haven't covered the SQL statements that fall in this category in this chapter, but you can learn about them in Chapter 11, "Users and Security," and then see them in action in Chapter 15, "Case Study: Implementing Role-Based Security."
Okay, before you really get your hands dirty with SQL, you need to ensure that you're ready to start entering SQL statements and that you have a sample database from which to work. To this end, we've included two appendixes in this book that you'll find useful for these tasks:
Appendix A, "Executing SQL Statements": This appendix describes how to get each of the RDBMS platforms used in this book (SQL Server, DB2, Oracle, MySQL, and Access) up and running and ready to execute SQL statements.
Appendix B, "Setting Up the InstantUniversity Database": This appendix describes the sample application you'll use throughout the book, the InstantUniversity database. This appendix describes how to create the database structure and populate it with data.
You can download the sample code for each database platform from the Downloads section of the Apress Web site (http://www.apress.com).
In Appendix B, "Setting Up the InstantUniversity Database," you'll see the basic code you need to create and populate the database tables. However, these processes are RDBMS specific in many cases, so what we've done in the code download is provide separate scripts for each database.
A script is simply a document (say, a Notepad document, usually with a .sql identifier) that gathers all of the necessary SQL statements required to perform a certain action. Therefore, you'll have all the SQL statements you need for each platform, gathered into separate scripts.
A useful technique when coding SQL in general, and especially when putting together scripts containing lots of SQL statements, is to clearly comment your SQL code so that the intent of the code is apparent. A comment is simply some text entered by the creator of the script that doesn't get interpreted as a SQL statement; in fact, RDBMSs ignore comments when processing the SQL statements. As with programming languages, different SQL dialects use different comment syntax; that is, they use different character strings to denote that a block of text is a comment and shouldn't be interpreted as a SQL statement. You'll meet these from time to time as you work through the book, so you'll quickly see how to structure them here.
All RDMBSs, except for Access, use a double dash (--) to indicate a comment:
-- This is a comment in RDBMSs other than Access
Note, however, that comments marked with -- must be followed by a space to avoid any possible confusion.
In Microsoft Access, you use a single quote (') at the start of a line of text, and everything else on that line is ignored:
' This is a comment in Access
Some RDBMSs also use other syntaxes; for example, SQL Server allows you to use C++ style /* and */ delimiters, which enable you to write comments that span several lines. Everything between these delimiters is ignored, for example:
/* This is a comment in SQL Server... ...it can span multiple lines! */
Finally, it's worth noting that older versions of MySQL only support # to mark a line as being a comment. Although more modern implementations of MySQL also support this syntax, they also implement the -- method.
|
|