| Previous | Table of Contents | Next |
by Ben McEwan
The first week will teach you how to use the SELECT statement to request data from the SQL Server. On Day 1, I’ll start you out with some back-ground information. I’ll talk about why SQL is worth knowing and how it’s used in the corporate world. I’ll tell you how to get hooked up to the server and whether it’s using ISQL or a browser to connect to the metis site.
On Day 2, you will retrieve data with SELECT. Gradually, I’ll add pieces to the SELECT so you can retrieve data in different ways. Day 2 will cover SELECT, FROM, and ORDER BY.
Day 3 will discuss how to restrict rows in the result set with WHERE. Then on Day 4, I’ll talk a little about manipulating columns and different types of data, such as dates and money.
Day 5 will show how to get aggregate information from a table, such as the minimum value in a column or the number of rows in a table. On Day 6, you’ll finish up your work with SELECT by learning about the GROUP BY and HAVING clauses.
By the last day of the week, you’ll know almost all there is to know about SELECT. I’ll talk about joining tables on Day 7, which is one of the most important and powerful skills in SQL.
Don’t worry if a lot of this seems like just meaningless words now—very soon, order will be imposed. Remember to take the time to do all the exercises at the end of each day, and check back with the “Week in Review” after the seventh day.
Welcome to Teach Yourself Transact-SQL in 21 Days. There are lots of SQL books out there, but my coauthor and I are glad you chose this one to get started. We’ll take you from the basics—getting connected to a server and making sure that you can communicate with your server—to advanced topics that even the most veteran user will find useful. In three weeks, you’ll have everything you need to write SQL like an old hand.
This book teaches you the basics of SQL usage. We provide lots of examples, lots of workshops, and plenty of opportunity for you to experiment on your own. There is a Web-based interface to our SQL Server that we’ve made available to people who don’t have a server available to them. This interface, written in Java, is available at http://www.metis.com. You will find details on connecting to it in this chapter.
The first week is all about writing queries to access data on the server. The second week is about SQL programming structures and strategies, manipulating data, and transaction control. The last week covers advanced topics, including stored procedures, triggers, and indexes. The last day or two covers some especially neat things.
Today, I discuss some SQL basics in two sections. The first section is light background reading and covers the following topics:
If you’re anxious to know something, you can skip the first section and jump right into “Getting Connected.” This second section is where we’ll really get started. In the second section I cover these topics:
If you’re an application programmer who knows C, Visual Basic, Delphi, or some other programming language, but you don’t know SQL, this is a great book for you. You’ll find lots of examples along the way.
If you hate learning from syntax statements, as I do, this book is for you. Most texts give you a syntax statement first, some explanations, and then an example at the end. I always found myself reading backwards. In this book, I give a brief description of the problem, an example, and then work through the example. When all is said and done, I’ll show the syntax.
SQL stands for Structured Query Language. It was created in the mid-seventies by IBM. SQL (pronounced sequel) provides a standard method for accessing data in a relational database. Many different database platforms use the SQL language to access the data stored in them. Oracle was the first to use SQL in a commercial environment.
Note: Originally, when IBM invented SQL, it was actually called SEQUEL. This was an acronym for Structured English QUEry Language. Today, SQL is used in many countries around the world in a variety of languages. Somewhere along the way, when people realized that SEQUEL wasn’t just for English speakers anymore, the language changed into SQL. Today, hardly anyone remembers that it really used to be called SEQUEL, and if you write it as SEQUEL in the newsgroups, everyone laughs at you.
SQL specifies keywords, what they should do, and how an ANSI-SQL–compliant product (such as MS SQL Server) should behave in response to these keywords. During the first week, you work with the SELECT keyword. Tomorrow, you start learning all about how to use it.
The American National Standards Institute, commonly known as ANSI, developed and published a standard for the SQL language in 1989; then they published the most recent standard, known as SQL-92 (or SAG). They have published other SQL standards, but the ’89 and ’92 standards are the most relevant. Standards are good, because they allow you, as a professional in the computer industry, to learn a single language and apply it to many different products. Oracle, Access, Informix, Sybase, and MS SQL Server all use SQL to access and manipulate their data.
Therein lies the rub. Once you hand a standard to a group of people in the computer industry, they invariably look at it askance, agree to implement most (but not all) of the standard, and add in some neat things for performance. The result is a group of different dialects.
Transact-SQL is one of the dialects of SQL. As you work through this book and learn how to use T-SQL, we’ll mention where the SQL standard is being followed and where the things you’re learning are extensions of the standard. Some surprisingly ordinary things are actually extensions, such as indexes and all the program flow-of-control statements (IF, WHILE).
The SQL-92 standard does not mention indexes. That’s OK, because SQL is not interested in performance issues. The standard is written to ensure that you can manipulate and access data. The result, though, is that every database platform has a slightly different way to create indexes.
In this book, we’ll be using MS SQL Server 6.5 to run all the examples, to formulate the workshops and quizzes, and to decide what parts of SQL to discuss. If you have 6.0 or 6.5, either will work for you, although 6.5 adds a few extra SQL details.
The server is one-half of a client/server system. The clients can be anything from Mac workstations to DOS machines to DEC Alpha NT Workstations, although most common today are the 32-bit Windows OSs running on Intel processors.
The server is responsible for physical storage of data, the central enforcement of business rules, and ensuring data integrity by making certain two users don’t try to change the same data at the same time.
The client is responsible for getting data from the server, formatting it to make it pretty, and then applying the data in a way that serves the needs of an end user.
If there was a stock price application that retrieved data from a SQL server, the server would store a table full of price information, and the client workstation(s) would request that data and then build a graphical chart to display it. This division of responsibility is good because it lets each machine’s hardware be specific to its task: you don’t need a 21-inch monitor on a SQL Server, and you don’t need a 40GB RAID array on a client workstation.
| Previous | Table of Contents | Next |