Download the book SQL queries for mere mortals
SQL Books - SQL Queries for Mortals
Over the past few years, SQL has evolved from a language known only to computer scientists to a widely used international standard. The total number of SQL compliant databases shipped each year is now in the millions. If you are accessing corporate information on the Internet or over an intranet, then you are probably using SQL. The SQL Queries for Mortals book will help novice users learn the basics of SQL queries and will be an essential reference guide for experienced and advanced users.
SQL queries for mere mortals. SQL Book DescriptionThis book is an excellent introduction to SQL queries and complements the previous book Database Design for Mortals by Addison-Wesley. It can be said that this is an even better introduction than the first book. A real programmer (i.e. a mere mortal) spends more time writing SQL queries and less time designing the database itself. The schemes are run by high-paid DBAs with private offices and sports cars. Most programmers are trying to make SQL work under a hard-coded schema.
By virtue of my profession, I set up databases and teach advanced SQL, so I can confirm that most of the texts in SQL are the same
attractive to read, like the Egyptian hieroglyphs. Once the program starts running, the programmer who wrote it moves on to the next problem, never looking back at what was done. And when there is something wrong with this program, the other person sends desperate messages to discussion groups on the Internet, where John and Mike will save him with a few wise words and rewriting the code. For years they have helped people solve their problems related to SQL... Finally, it's time to put it all in a book that anyone can use!
It is not at all difficult and does not take any other time to write good program... If you understand what you are doing, then most problems can be solved quite simply. First you need to learn the basics. This book provides you with the chance to learn these basics in an understandable and well-written manner. Then you need to understand when and how to adapt a simple solution to a specific DBMS and specific SQL implementation. When the basics are firmly grasped, contact me and I will teach you really non-trivial things.
SQL FOR PERSONAL MORTALS is a complete introduction to Structured Query Language written especially for beginners. If you are new to database management, this book will teach you how to work with SQL easily and fluently, using simple queries and complex operations. To master SQL:
- Grasp the meaning of database management concepts with a concise and simple introduction to relational databases.
- Follow these instructions for using basic SQL commands to find and work with information in data tables. Learn to select and summarize data, and manage it skillfully.
- Work efficiently with compound data tables by applying advanced query techniques to more than one table at a time, constructing complex queries and subqueries.
- Create new data sheets for retail business applications. Learn important principles for effective database design and data integrity and protection techniques.
- Learn to apply SQL with programming languages \u200b\u200busing the dedicated programmer chapter.
SQL FOR SIMPLE MORTAL is a must-have guide to any structured query language implementation, including a quick reference to standard SQL and a description of common properties of non-standard SQL.
1. Introduction to relational databases
2. Introduction to SQL
3. Using SQL to fetch data from tables
4. Using relational and boolean operators to create more complex predicates
5. Using special operators in “conditions”
6. Summarizing data using aggregation functions
7. Formatting query results
8. Using multiple tables in one query
9. Operation of joining, the operands of which are represented by one table
10. Nesting requests
11. Linked subqueries
12. Using the EXISTS operator
13. Using the ANY, ALL and SOME Operators
14. Using the UNION clause
15. Entering, deleting and changing field values
16. Using subqueries with update commands
17. Creating tables
18. Restrictions on the set of valid data values
19. Maintaining data integrity
20. Introduction to Views
21. Changing values \u200b\u200busing views
22. Determining data access rights
23. Global Aspects of SQL
24. How order is maintained in the database sQL data
25. Using SQL with other programming languages \u200b\u200b(embedded SQL)
Applications
A. Answers to exercises
B. SQL data types
C. Some general deviations from the SQL standard
D. Syntax and Command Reference
E. Tables used in examples
F. SQL today
An introduction to relational databases
... We will also use both terms interchangeably when discussing SQL. You are already familiar with simple tables. For all their brevity and simplicity, they are quite suitable for demonstrating the most important features of the language, as you will see for yourself later. Sometimes we will introduce other tables or look at other data in one of these tables in order to show some additional features their application. We are now ready to dive into SQL directly. Next chapter, to
which you will have to return from time to time, gives a general idea of \u200b\u200bthe language and guides you in the material presented in the book.
Introduction to SQL
This chapter covers a large number of basic information that gives an overview of SQL. You learned how it is structured, how it is used, how data is expressed in it, how and by whom it is defined (and what conflicts arise), as well as some of the conventions and terminology used to describe it. The next chapter explains in detail the formation and operation of commands. You will become familiar with the command that allows you to retrieve information from tables, which is one of the most commonly used in SQL. You will be able to retrieve certain information yourself from the database.
Global aspects of SQL
- Synonyms (how to create new names for data objects)
- Database space (dbspace) (how to split the space available in the database)
- Transaction processing (how to save or discard changes in the database)
- Concurrency control (how SQL allows you to eliminate the influence of commands on each other)
Synonyms are objects, have names and (sometimes) owners, but do not exist independently and independently of the table whose name they replace. They can be shared and therefore available to everyone with access to the object, or they can only be owned by a specific user. Dbspaces are database subsections allocated to users. It is best to store linked tables that are frequently joined in the same database space. COMMIT and ROLLBACK are commands used to save as a group all changes to the database, starting from the previous COMMIT or ROLLBACK command or from the beginning of the session, or to discard them. Concurrency control determines the extent to which concurrent commands affect each other. This is where the "working" differences in how databases work and how to isolate command results emerge.
How order is maintained in a SQL database
So, SQL systems use a lot of tables, which is called the system catalog of the database structure. These tables can be queried, but they cannot be updated. In addition, comment columns can be added (and deleted) to the SYSTEMCATALOG and SYSTEMCOLUMNS tables. Creating views for these tables is an excellent way to pinpoint the information that users have access to. This concludes our discussion of SQL in interactive mode. In the next chapter, we will consider the issues of using SQL directly in programs written in programming languages; such use allows you to take advantage of the interaction of the program with the database.
Using SQL with other programming languages \u200b\u200b(embedded SQL)
SQL commands are included in procedural programming languages \u200b\u200bto combine the strengths of the two approaches. Implementing this feature requires some SQL extensions. Embedded SQL commands are translated using a program called a precompiler (preprocessor) to create a program that the high-level compiler understands. Embedded SQL commands are replaced by calls to subroutines that are created using the embedded preprocessor; these routines are called accessors. With this approach, ANSI supports embedded SQL for languages pascal programming, FORTRAN, COBOL, PL / 1. Other languages \u200b\u200bare also used by developers. The most important of these is C. When describing embedded SQL, pay particular attention to the following:
- All embedded SQL commands begin with EXEC SQL and end depending on the high-level language used.
- All high-level language variables used in SQL commands must be included in the SQL declaration section before they are used.
- When high-level language variables are used in SQL commands, a colon must be used before their names.
- Output for queries can be stored directly in high-level language variables using INTO if and only if the query fetches a single row.
- Cursors can be used to store the output of a query and access it one row at a time. Cursors are declared (along with the definition of a query whose output contains the cursor), opened (which corresponds to the execution of the query), and closed (which corresponds to removing the output from the cursor, breaking the link between the output and the cursor). While the cursor is open, you can use the FETCH command to access the output of the query: one line for each execution of the FETCH command.
- Cursors can be updatable or read-only. To be updatable, a cursor must meet all of the same criteria as a view. It should not use the clauses ORDER VU and UNION, which are not allowed in the view. The non-updateable cursor is a read-only cursor.
- If the cursor is updatable, it can be used to manipulate the rows that are used by the embedded SQL UPDATE and DELETE commands from the WHERE CURRENT OF clause. DELETE or UPDATE must belong to the table accessed through the query cursor.
- SQLCODE can be declared as a numeric variable for every embedded SQL program. The values \u200b\u200bof this variable are set automatically after each SQL command is executed.
- If the SQL command executes normally, but does not generate output or does not perform the expected changes in the database, SQLCODE is set to 100. If the command fails, SQLCODE takes some negative value describing the cause of the error, depending on the specific SQL system. Otherwise, SQLCODE is zero.
- The WHENEVER clause can be used to determine the action to take if the SQLCODE is 100 (NOT FOUND) or negative (SQLERROR - SQL execution error). This action is to go to a certain point in the program (GOTO<метка>) or to perform an “empty action” (CONTINUE, equivalent to “do nothing”). The default is “empty action”.
- Only numeric variables can be used as indicators. Indicator variables follow other variable names in an SQL command without any delimiter characters except for the word INDICATOR.
- Typically, the value of the indicator variable is O. If a SQL command tries to place a NULL value in a high-level language variable that uses this indicator, it is negative. This property can be used for error protection and as a flag to flag NULL values \u200b\u200bin SQL that will be specially interpreted in the main program.
- Indicator variables can be used to insert NULL values \u200b\u200binto SQL INSERT or UPDATE commands. They take positive values \u200b\u200bwhen a string truncation situation occurs.
Michael J. Hernandez, John L. Viescas - SQL queries for mere mortals
03.09.2009
The SQL Queries for Mortals book is an excellent introduction to SQL queries. A real programmer (mere mortal) spends a lot of time writing SQL queries and less time designing the database itself. The schemes are run by high-paid DBAs with private offices and sports cars. Most programmers are trying to get SQL to work under a hard-coded schema.
About the authors
Michael J. Hernandez is a well-known relational database consultant and owner of the DataTex Consulting Group in Seattle. He often speaks at various conferences. He also penned the book Database Design for Mere Mortals (Designing databases for mere mortals).
John L. Viescas - President of Viescas Consulting, Inc., a renowned database consulting company. He is also the author of the bestselling book Running Microsoft Access 2000 and The Programmer's Quick Reference Guide to SQL.