Sql for mere mortals grabber. SQL queries for mere mortals
SQL FOR MERE MORTALS is a complete introduction to a structured query language, written specifically for beginners. If you have no experience managing databases, this book will teach you how to work with SQL easily and fluently, using simple queries and complex operations. To master SQL:
- Understand the concepts associated with database management with a short, simple introduction to relational databases.
- Follow these instructions to use basic SQL commands to find and manipulate information in data tables. Learn to select, summarize, and manage data skillfully.
- Work effectively 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 tables for trading business applications. Learn important principles of effective database design and techniques for ensuring data integrity and security.
- Learn to use SQL with programming languages using a special chapter for programmers.
SQL FOR MERE MORTALS is an essential guide to any implementation of a structured query language, including a quick reference to standard SQL and a description of the general properties of non-standard SQL.
1. Introduction to relational databases
2. Introduction to SQL
3. Using SQL to retrieve 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. Join operation, the operands of which are represented by one table
10. Nesting queries
11. Related 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. Maintain data integrity
20. Introduction to Views
21. Changing Values Using Views
22. Determining data access rights
23. Global Aspects of SQL
24. How to Maintain Order in a SQL Database
25. Using SQL with other programming languages (embedded SQL)
Applications
A. Answers to exercises
B. SQL Data Types
C. Some Common Deviations from the SQL Standard
D. Syntax and Command Help
E. Tables used in examples
F. SQL Today
Introduction to Relational Databases
... We will also use both terms as synonyms 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 to show some additional uses. Now we're ready to dive straight into SQL. Next chapter, to
which you will have to return to from time to time, gives a general idea of the language and orients you in the material presented in the book.
Introduction to SQL
This chapter covers a lot of basic information to give you an overview of SQL. You learned how it is structured, how it is used, how it expresses data, how and by whom it is defined (and the tensions that arise), and some of the conventions and terminology used to describe it. The next chapter explains in detail the formation and operation of commands. You'll learn about a command that allows you to retrieve information from tables and is one of the most commonly used commands in SQL. You will be able to retrieve certain information from the database yourself.
Global Aspects of SQL
- Synonyms (how to create new names for data objects)
- Database space (dbspace) (how to partition the space available in a database)
- Transaction processing (how to save or discard changes to the database)
- Concurrency control (how SQL allows you to prevent commands from interfering with each other)
Synonyms are objects, have names and (sometimes) owners, but do not exist independently of the table whose name they replace. They can be shared and therefore accessible to everyone with access to the object, or they can be owned only by a specific user. Dbspaces are database subsections allocated to users. Related tables that frequently undergo a join operation are best stored in the same database space. COMMIT and ROLLBACK are commands used to save as a group all changes to the database since the previous COMMIT or ROLLBACK command or the start of a session, or to abandon them. Concurrency control determines the extent to which concurrent commands affect each other. This is where operational differences emerge in how databases operate and how commands are isolated.
How to Maintain Order in a SQL Database
So, SQL systems use a set of tables called the system directory of the database structure. These tables can be queried, but cannot be updated. You can also add (or delete) comment columns to the SYSTEMCATALOG and SYSTEMCOLUMNS tables. Creating views on these tables is an excellent way to define exactly what information users are authorized to access. This concludes our discussion of SQL in interactive mode. The next chapter will look at how to use SQL directly in programs written in programming languages; This use allows you to take advantage of the program's interaction with the database.
Using SQL with other programming languages (embedded SQL)
SQL commands are included in procedural programming languages to combine the strengths of the two approaches. Implementing this feature requires some SQL extensions. Embedded SQL commands are translated by a program called a precompiler (preprocessor) to produce a program that can be understood by a high-level language compiler. Embedded SQL commands are replaced by calls to routines that are created using the embedded preprocessor; these routines are called access modules. Using this approach, ANSI supports embedded SQL for the Pascal, FORTRAN, COBOL, PL/1 programming languages. Other languages are also used by developers. The most important of these is C. When describing embedded SQL, special attention should be paid to the following:
- All built-in SQL commands begin and end with EXEC SQL depending on the high-level language used.
- All high-level language variables used in SQL commands must be included in the SQL declarations section before they are used.
- If high-level language variables are used in SQL commands, their names must be preceded by a colon.
- The output of queries can be stored directly in high-level language variables using INTO if and only if the query selects a single row.
- Cursors can be used to store query output and access it one row at a time. Cursors are declared (along with a definition of the query whose output the cursor contains), opened (which corresponds to the execution of the query), and closed (which corresponds to the removal of the output from the cursor, breaking the connection 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 row for each execution of the FETCH command.
- Cursors can be updatable or read-only. To be updatable, a cursor must meet all the same criteria as a view. It must not use the ORDER VU and UNION clauses, which are prohibited from being used in the view. A cursor that is not updated is a read-only cursor.
- If the cursor is updatable, it can be used to manipulate rows that are used by the embedded SQL UPDATE and DELETE commands in the WHERE CURRENT OF clause. The DELETE or UPDATE must belong to the table being accessed through the query cursor.
- SQLCODE can be declared as a numeric variable for every program that uses embedded SQL. The values of this variable are set automatically after each SQL command is executed.
- If the SQL command runs normally but does not produce output or does not make the expected changes to the database, SQLCODE is set to 100. If the command fails, SQLCODE is set to some negative value that describes the cause of the error, depending on the specific SQL system. Otherwise, SQLCODE is zero.
- The WHENEVER clause can be used to determine what action to take if SQLCODE is 100 (NOT FOUND) or negative (SQLERROR). This action consists of going to some specific point in the program (GOTO<метка>) or to perform an “empty action” (CONTINUE, equivalent to the concept of “doing nothing”). The default is “empty action”.
- Only numerical variables can be used as indicators. Indicator variables follow other variable names in the SQL command without any separating characters except the word INDICATOR.
- Typically, the value of an indicator variable is O. If a SQL command attempts to place a NULL value in a high-level language variable that uses this indicator, it is assigned a negative value. This property can be used for error protection and as a flag to mark NULL values in SQL that will be specially interpreted in the main program.
- Indicator variables can be used to insert NULL values into SQL INSERT or UPDATE commands. They take positive values when a string truncation situation occurs.
Download the book SQL Queries for Mere Mortals
SQL Books - SQL Queries for Mere 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-compatible databases shipped each year now numbers in the millions. If you access corporate information on the Internet or over an intranet, then you probably use SQL. SQL Queries for Mere Mortals helps beginners learn the basics of SQL queries and is an essential reference guide for experienced and advanced users.
SQL queries for mere mortals. Description of the book on SQLThe book is an excellent introduction to SQL queries and complements well the previous book, Database Design for Mere Mortals, published by Addison-Wesley. One might say that this is an even better introduction than the first book. A real programmer (that is, a mere mortal) spends more time writing SQL queries and less time designing the database itself. The schemes are created by highly paid database administrators who own private offices and sports cars. What most programmers do is try to make SQL work within a rigidly defined schema.
I configure databases and teach advanced SQL in my profession, so I can confirm that most SQL texts are just as
attractive to read, just like Egyptian hieroglyphs. Once a program starts working, the programmer who wrote it moves on to the next task, never looking back at what has been done. And when something goes wrong with this program, another person sends desperate messages to Internet discussion groups, where John and Mike will save him with a few words of wisdom and a rewrite of the code. They have been helping people solve their problems related to SQL. It's finally time to put it all into a book that anyone can use!
It is not at all difficult and takes no other time to write a good program. If you understand what you are doing, then most problems can be solved quite simply. First of all, you need to learn the basics. This book gives you the chance to learn these basics in an understandable and well-written manner. Then you need to understand when and how to tailor a simple solution to a specific DBMS and a specific SQL implementation. When the basics are reliably mastered, contact me and I will teach you truly non-trivial things.