As mentioned on Day 1, the name Query is really a misnomer in this context. An SQL query is not necessarily a question to the database. It can be a command to do one of the following:
SELECT NAME, STARTTERM, ENDTERM FROM PRESIDENTS WHERE NAME = 'LINCOLN';In this example everything is capitalized, but it doesn't have to be. The preceding query would work just as well if it were written like this:
select name, startterm, endterm from presidents where name = 'LINCOLN';Notice that LINCOLN appears in capital letters in both examples. Although actual SQL statements are not case sensitive, references to data in a database are. For instance, many companies store their data in uppercase. In the preceding example, assume that the column name stores its contents in uppercase. Therefore, a query searching for 'Lincoln' in the name column would not find any data to return. Check your implementation and/or company policies for any case requirements.
Take another look at the sample query. Is there something magical in the spacing? Again the answer is no. The following code would work as well:
NOTE: Commands in SQL are not case sensitive.
select name, startterm, endterm from presidents where name = 'LINCOLN';However, some regard for spacing and capitalization makes your statements much easier to read. It also makes your statements much easier to maintain when they become a part of your project.
Another important feature of ; (semicolon)semicolon (;)the sample query is the semicolon at the end of the expression. This punctuation mark tells the command-line SQL program that your query is complete.
If the magic isn't in the capitalization or the format, then just which elements are important? The answer is keywords, or the words in SQL that are reserved as a part of syntax. (Depending on the SQL statement, a keyword can be either a mandatory element of the statement or optional.) The keywords in the current example are
This discussion starts with SELECT because most of your statements will also start with SELECT:
SELECT <COLUMN NAMES>The commands, see also statementsbasic SELECT statement couldn't be simpler. However, SELECT does not work alone. If you typed just SELECT into your system, you might get the following response:
SQL> SELECT;
SELECT * ERROR at line 1: ORA-00936: missing expressionThe asterisk under the offending line indicates where Oracle7 thinks the offense occurred. The error message tells you that something is missing. That something is the FROM clause:
FROM <TABLE>Together, the statements SELECT and FROM begin to unlock the power behind your database.
NOTE: keywordsclausesAt this point you may be wondering what the difference is between a keyword, a statement, and a clause. SQL keywords refer to individual SQL elements, such as SELECT and FROM. A clause is a part of an SQL statement; for example, SELECT column1, column2, ... is a clause. SQL clauses combine to form a complete SQL statement. For example, you can combine a SELECT clause and a FROM clause to write an SQL statement.
NOTE: Each implementation of SQL has a unique way of indicating errors. Microsoft Query, for example, says it can't show the query, leaving you to find the problem. Borland's Interbase pops up a dialog box with the error. Personal Oracle7, the engine used in the preceding example, gives you an error number (so you can look up the detailed explanation in your manuals) and a short explanation of the problem.
The CHECKS table:
CHECK# PAYEE AMOUNT REMARKS --------- -------------------- ------ --------------------- 1 Ma Bell 150 Have sons next time 2 Reading R.R. 245.34 Train to Chicago 3 Ma Bell 200.32 Cellular Phone 4 Local Utilities 98 Gas 5 Joes Stale $ Dent 150 Groceries 6 Cash 25 Wild Night Out 7 Joans Gas 25.1 Gas
SQL> select * from checks;
queriesCHECK# PAYEE AMOUNT REMARKS ------ -------------------- ------- ---------------------
1 Ma Bell 150 Have sons next time 2 Reading R.R. 245.34 Train to Chicago 3 Ma Bell 200.32 Cellular Phone 4 Local Utilities 98 Gas 5 Joes Stale $ Dent 150 Groceries 6 Cash 25 Wild Night Out 7 Joans Gas 25.1 Gas 7 rows selected.
The asterisk (*) in select * tells the database to return all the columns associated with the given table described in the FROM clause. The database determines the order in which to return the columns.
SQL> SELECT payee, remarks, amount, check# from checks;Notice that each column name is listed in the SELECT clause. The order in which the columns are listed is the order in which they will appear in the output. Notice both the commas that separate the column names and the space between the final column name and the subsequent clause (in this case FROM). The output would look like this:
PAYEE REMARKS AMOUNT CHECK# -------------------- ------------------ --------- --------- Ma Bell Have sons next time 150 1 Reading R.R. Train to Chicago 245.34 2 Ma Bell Cellular Phone 200.32 3 Local Utilities Gas 98 4 Joes Stale $ Dent Groceries 150 5 Cash Wild Night Out 25 6 Joans Gas Gas 25.1 7 7 rows selected.Another way to write the same statement follows.
SELECT payee, remarks, amount, check# FROM checks;Notice that the FROM clause has been carried over to the second line. This convention is a matter of personal taste when writing SQL code. The output would look like this:
PAYEE REMARKS AMOUNT CHECK# -------------------- -------------------- --------- -------- Ma Bell Have sons next time 150 1 Reading R.R. Train to Chicago 245.34 2 Ma Bell Cellular Phone 200.32 3 Local Utilities Gas 98 4 Joes Stale $ Dent Groceries 150 5 Cash Wild Night Out 25 6 Joans Gas Gas 25.1 7 7 rows selected.
SQL> SELECT CHECK#, amount from checks;which returns
CHECK# AMOUNT --------- --------- 1 150 2 245.34 3 200.32 4 98 5 150 6 25 7 25.1 7 rows selected.
What if you need information from a different table?
DEPOSIT# WHOPAID AMOUNT REMARKS -------- ---------------------- ------ ------------------- 1 Rich Uncle 200 Take off Xmas list 2 Employer 1000 15 June Payday 3 Credit Union 500 LoanYou would simply change the FROM clause to the desired table and type the following statement:
SQL> select * from depositsThe result is
DEPOSIT# WHOPAID AMOUNT REMARKS -------- ---------------------- ------ ------------------- 1 Rich Uncle 200 Take off Xmas list 2 Employer 1000 15 June Payday 3 Credit Union 500 Loan
SQL> select amount from checks;you would see
AMOUNT --------- 150 245.34 200.32 98 150 25 25.1Notice that the amount 150 is repeated. What if you wanted to see how may different amounts were in this column? Try this:
SQL> select DISTINCT amount from checks;The result would be
AMOUNT --------- 25 25.1 98 150 200.32 245.34 6 rows selected.
Try this example--for the first (and only!) time in your SQL career:
SQL> SELECT ALL AMOUNT 2 FROM CHECKS;
AMOUNT --------- 150 245.34 200.32 98 150 25 25.1 7 rows selected.It is the same as a SELECT <Column>. Who needs the extra keystrokes?
A The data was created using the methods described on Day 8. The database connection depends on how you are using SQL. The method shown is the traditional command-line method used on commercial-quality databases. These databases have traditionally been the domain of the mainframe or the workstation, but recently they have migrated to the PC.
Q OK, but if I don't use one of these databases, how will I use SQL?
A You can also use SQL from within a programming language. Embedded SQLEmbedded SQL is normally a language extension, most commonly seen in COBOL, in which SQL is written inside of and compiled with the program. Microsoft has created an entire Application Programming Interface (API) that enables programmers to use SQL from inside Visual Basic, C, or C++. Libraries available from Sybase and Oracle also enable you to put SQL in your programs. Borland has encapsulated SQL into database objects in Delphi. The concepts in this book apply in all these languages.
SELECT * FROM CHECKS; select * from checks;?
b. Select * from checks
c. Select amount name payee FROM checks;
from checks;
b. select * from checks;
c. select * from checks
/
2. Rewrite the query from exercise 1 so that the remarks will appear as the first column in your query results.
3. Using the CHECKS table, write a query to return all the unique remarks.