SQL for mere mortals. Michael J
SQL FOR MORTAL 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:
- Make sense 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 placed in data tables. Learn how to select and summarize data, as well as skillfully manage it.
- Efficiently work with composite data tables by applying advanced query techniques to more than one table at the same time, constructing complex queries and subqueries.
- Create new data tables for retail business applications. Learn the important principles of effective database design and techniques for ensuring data integrity and protection.
- Learn to use SQL with programming languages using the dedicated programmer chapter.
SQL FOR MORTAL MORTALS is an indispensable guide for any implementation of Structured Query Language, which includes 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 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. Join operation whose operands are represented by one table
10. Nesting Requests
11. Related subqueries
12. Using the EXISTS operator
13. Using ANY, ALL and SOME operators
14. Using the UNION clause
15. Entering, deleting and changing field values
16. Using Subqueries with Update Commands
17. Create tables
18. Restrictions on the set of valid data values
19. Data integrity support
20. Introduction to Views
21. Changing Values with Views
22. Determination of data access rights
23. Global aspects of SQL
24. How order is maintained in a SQL database
25. Using SQL with other programming languages (embedded SQL)
Applications
A. Answers to the exercises
B. SQL Data Types
C. Some General Deviations from the SQL Standard
D. Syntax and command help
E. Tables used in examples
f.sql today
Introduction to relational databases
… We, too, will 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 later see for yourself. Sometimes we will introduce other tables or look at other data in one of these tables in order to show some additional features their applications. We are now ready to dive into SQL directly. Next chapter, to
which you will have to come back to from time to time, gives you a general idea of the language and orients you in the material presented in the book.
Introduction to SQL
This chapter covers a large number of basic information that gives a general idea of SQL. You've learned how it's structured, how it's used, how it expresses data, how it's defined and by whom (and the inconsistencies that come with it), and some of the conventions and terminology used to describe it. The next chapter explains in detail the formation and operation of commands. You will get acquainted with the command that allows you to retrieve information from tables and is one of the most commonly used in SQL. You will be able to extract certain information from the database yourself.
Global Aspects of SQL
- Synonyms (how to create new names for data objects)
- Database space (dbspace) (how to divide the space available in the database)
- Transaction processing (how to save or discard changes to 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 of the table whose name they replace. They can be shared and thus accessible to anyone 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 are joined frequently are best stored in the same dbspace. COMMIT and ROLLBACK are commands used to save as a group all database changes since the previous COMMIT or ROLLBACK command or from the beginning of the session, or to discard them. Concurrency control determines the extent to which simultaneous commands affect each other. This is where “working” differences come into play in how databases function and how to isolate the results of command execution.
How order is maintained in a SQL database
So, SQL systems use a set of tables, which is called the system catalog of the database structure. These tables can be queried but cannot be updated. You can also add (and delete) comment columns to the SYSTEMCATALOG and SYSTEMCOLUMNS tables. Creating views for these tables is an excellent way to pinpoint exactly what information users have permission to access. This concludes our discussion of SQL interactively. The next chapter will deal with the use of SQL directly in programs written in programming languages; this use allows you to take advantage of the interaction of the program with the database.
Using SQL with other programming languages (embedded SQL)
SQL commands are included in procedural programming languages to combine the power 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 language compiler understands. high level. Embedded SQL commands are replaced by calling subroutines that are created using the built-in preprocessor; these routines are called access modules. With this approach, ANSI supports embedded SQL for languages Pascal programming, FORTRAN, COBOL, PL/1. Other languages are also used by developers. The most important of these is C. When describing embedded SQL, pay special attention to the following:
- All built-in SQL commands begin with the words EXEC SQL and end depending on the high-level language used.
- All high-level language variables used in SQL commands must be listed in the SQL declarations section before being used.
- If high-level language variables are used in SQL commands, they must be preceded by a colon.
- Query output 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 query output and access it one line at a time. Cursors are declared (along with the definition of the query whose output contains the cursor), opened (corresponding to query execution), and closed (corresponding 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 query output, one row for each execution of the FETCH command.
- Cursors can be updatable or read-only. To be updatable, a cursor must satisfy all the same criteria as a view. It must not use the ORDER and UNION clauses, which are not allowed in a view. A non-updatable cursor is a read-only cursor.
- If the cursor is updatable, it can be used to manipulate rows used by the UPDATE and DELETE embedded SQL commands from the WHERE CURRENT OF clause. DELETE or UPDATE must belong to the same table that is accessed through the query cursor.
- SQLCODE can be declared as a numeric type variable for every program that uses embedded SQL. The values of this variable are set automatically after the execution of each SQL command.
- If the SQL command executes normally but does not generate output or does not make the expected changes to the database, SQLCODE is set to 100. If the command produces an error, then SQLCODE is set to some negative value that describes the reason for the error, depending on the particular SQL system. AT otherwise SQLCODE is zero.
- The WHENEVER clause can be used to specify the action to take if the SQLCODE evaluates to 100 (NOT FOUND - not found) or a negative value (SQLERROR - an error occurred while executing SQL). This action is to go to some specific 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 the SQL statement without any separating characters except for the word INDICATOR.
- Typically, the value of the indicator variable is 0. If the SQL command tries to place null value into a high-level language variable that uses this indicator, then it takes a negative value. This property can be used for error protection and as a flag to mark NULL values in SQL for special interpretation in the main program.
- Indicator variables can be used to insert NULL values into SQL INSERT or UPDATE statements. They take positive values when a string truncation situation occurs.
Michael J. Hernandez, John L. Viescas - SQL queries for mere mortals
03.09.2009
SQL Queries for Mere Mortals is an excellent introduction to SQL queries. A real programmer (mere mortal) spends a lot of time writing SQL queries and less for the design of the database itself. The schemes are made up of highly paid DBAs who own private offices and sports cars. Most programmers are in the business of trying to make SQL work under hard conditions. given scheme.
About authors
Michael J. Hernandez is a well-known relational database consultant and owner of the DataTex consulting group in Seattle. He often makes presentations at various conferences. He also wrote the book Database Design for Mere Mortals (Database Design for Mere Mortals).
John L. Viescas- President of Viescas Consulting, Inc., a well-known database consulting company. He is also the bestselling author of Running Microsoft Access 2000 and Programmer's Quick Reference Guide to SQL.
Over the past few years, SQL has evolved from being a language known only to computer scientists to being widely used. international standard. The total number of SQL compatible 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 book SQL Queries for Mere Mortals will help novice users learn the basics of SQL queries and will be an essential reference guide for experienced and advanced users.
Content.
Part I Relational databases and SQL. one
Chapter 1 What is "relational"?. 3
Database types3
Short story relational model. four
Anatomy of relational databases. 5
Why is all this necessary. fourteen
Results.16
Chapter 2 Ensuring the reliability of the database structure. 17
Why is this chapter placed here. 17
Why you need a well thought out structure. eighteen
Field settings. eighteen
Table setup. 26
Installing and fixing links. 36
And it's all?. 43
Results. 43
Chapter 3 A Brief History of SQL. 45
Origins of SQL. 45
early implementations. 47
".and then there was the Standard." 48
Development of the ANSI/ISO standard. 49
What is preparing the future. 54
Why learn SQL. 56
Results. 57
Part II Basics of SQL. 59
Chapter 4 Creating simple queries. 61
Introduction to SQL. 61
SELECT statement. 62
Brief Digression: Data Versus Information.64
Translation of a query into SQL. 66
Elimination of duplicate lines.72
Sorting information. 74
Saving a job79
Operator examples. 79
Results.87
Tasks for independent solution. 88
Chapter 5 How to get more than just columns. 91
SELECT condition, double two.92
Beyond the basics96
What is an "expression"97
What are you trying to express.97
Expression types. 100
Using expressions in a SELECT clause. 109
Null value. 117
Operator examples. 120
Results.127
Tasks for independent solution. 128
Chapter b Filtering data 131
Refining what is obtained using WHERE.131
Defining search conditions.135
Using multiple conditions.156
Re-encounter with NULL: Warning note.168
Expressing conditions in various ways. 172
Operator examples. 173
Results.179
Tasks for independent solution. 179
Part III Work with multiple tables. 183
Chapter 7 Thinking in sets. 185
What is a set. 186
Operations on sets.186
intersection. 187
Difference. 192
An association. 199
Set operations in SQL. 203
Results.212
Chapter 8 Internal Joins. 214
What is JOIN. 214
INNER JOIN. 215
Applications of INNER JOIN.231 conditions
Operator examples.233
Results.251
Tasks for independent solution.251
Chapter 9 Outer Joins.255
What is OUTER JOIN.255
LEFT/RIGHT OUTER JOIN.257
FULL OUTER JOIN.276
Using OUTER JOIN.281 operations
Operator examples.282
Results.295
Tasks for independent solution296
Chapter 10 UNION Operations 298
What is UNION.298
Writing queries with UNION.300
Application of UNION.311
Operator examples.312
Results.322
Tasks for independent solution.322
Chapter 11* Subqueries325
What is a subquery.326
Subqueries as expressions with columns.327
Subqueries as filters.332
Using subqueries.347
Operator examples349
Results.361
Tasks for independent solution.362
Part IV Summation of data and grouping. 365
Chapter 12* A Simple Sum.367
Aggregate Functions.367
Using Aggregate Functions in Filters.381
Operator examples.384
Results.390
Tasks for independent solution.390
Chapter 13 Grouping data. 393
Why you need to group data.393
GROUP BY clause. 395
Imposing some restrictions.404
Using GROUP BY.408
Operator examples.409
Results.417
Tasks for independent solution.418
Chapter 14 Filtering Grouped Data.420
Narrowing groups.420
Filters: Feel the difference. 425
Using HAVING. 432
Operator examples. 433
Results. 441
Tasks for independent solution. 441
Conclusion. 445
Applications 447
Appendix A Diagrams of the SQL Standard. 449
Appendix B Database structures used
as examples. 455
Appendix C Recommended Readings. 459
Foreword
The book SQL Queries for Mere Mortals is an excellent introduction to SQL queries and complements well the previous book Designing a Database for Mere Mortals by Addison-Wesley. It can be said that this is an even better introduction than the first book. A real programmer (ie, a mere mortal) spends more time writing SQL queries and less time designing the database itself. The schemes are made up of highly paid DBAs who own private offices and sports cars. Most programmers are in the business of 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 SQL texts are as compelling to read as Egyptian hieroglyphs. As soon as the program starts running, the programmer who wrote it moves on to the next problem, never looking back at what has been done. And when something goes 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 words of wisdom and code rewriting. They've been helping people solve their SQL problems for years. Finally, it's time to put it all in a book that everyone can use!
Free download e-book in a convenient format, watch and read:
Download SQL Queries for Mere Mortals, A Practical Guide to Data Manipulation in SQL, Michael J. Hernandez, John L. Viescas., 2003 - fileskachat.com, fast and free download.
Download pdf
You can buy this book below best price at a discount with delivery throughout Russia.
SQL for mere mortals + SQL in 10 minutes
Sams Teach Yourself SQL in 10 Minutes
Author: Ben Forta
Translator: V. Ginzburg
Languages: Russian
Publisher: Williams
Series: Learn in 10 minutes
ISBN 978-5-8459-1858-1, 978-0-672-33607-2; 2014
Pages 288 pages
Format 84x108/32 (130x200 mm)
Circulation 2000 copies.
Binding Soft coverSQL book in 10 minutes offers simple and practical solutions for those who want to get results quickly. After working through all 22 lessons, each of which will take no more than 10 minutes, you will learn everything that is necessary for the practical use of SQL. The examples in the book are suitable for IBM DB2, Microsoft Access, Microsoft SQL Server, MySQL, Oracle, PostgreSQL, SQLite, MariaDB and Apache OpenOffice Base.
access to all links
Illustrative examples will help you understand how SQL statements are structured.
Tips will prompt short cuts to solutions.
Warnings help you avoid common mistakes.
Notes provide further clarification.
What can be learned in 10 minutes:Ben Forta is Director of Development at Adobe Systems. Author of numerous bestselling books, including books on databases, SQL, and ColdFusion. He has extensive experience in database design and application development.
- basic SQL instructions;
- creation of complex SQL queries with many sentences and operators;
- extraction, sorting and formatting of data;
- obtaining specific data using various filtering methods;
- application of summary functions to obtain summary data;
- association of relational tables;
- adding, updating and deleting data;
- creation and modification of tables;
- Working with views, stored procedures and much more.Understanding SQL
Author: Martin Graber
Translator: V. Yastrebov
Languages: Russian
Publisher: Lori
ISBN 978-5-85582-301-1; 2014
additional characteristics
Pages 378 pages
Format 70x100/16 (170x240 mm)
Circulation 200 copies.
Binding Soft coverSQL FOR MORTAL 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:
- Understand 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 placed in data tables. Learn how to select and summarize data, as well as skillfully manage it.
- Efficiently work with composite data tables by applying advanced query techniques to more than one table at the same time, constructing complex queries and subqueries.
- Create new data tables for retail business applications. Learn the important principles of effective database design and techniques for ensuring data integrity and protection.
- Learn to use SQL with programming languages using a dedicated chapter for programmers.SQL FOR MORTAL MORTALS is an indispensable guide for any implementation of Structured Query Language, which includes a quick reference to standard SQL and a description of the general properties of non-standard SQL.
Download 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 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 book SQL Queries for Mere Mortals 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 descriptionThe book is an excellent introduction to SQL queries and complements well the previous book Designing a Database for Mere Mortals by Addison-Wesley. It can be said that this is an even better introduction than the first book. A real programmer (ie a mere mortal) spends more time writing SQL queries and less time designing the database itself. The schemes are made up of highly paid DBAs who own private offices and sports cars. Most programmers are in the business of 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 SQL texts are just as
attractive to read, as are Egyptian hieroglyphs. As soon as the program starts running, the programmer who wrote it moves on to the next problem, never looking back at what has been done. And when something goes 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 words of wisdom and code rewriting. They have been helping people solve their problems for years. SQL. Finally, it's time to put it all in a book that everyone can use!
It is not difficult at all and it 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 of all, you need to learn the basics. This book gives you a chance to learn these basics in an understandable and well-written way. Then you need to understand when and how to tailor a simple solution to a specific DBMS and specific SQL implementation. When the basics are firmly mastered, contact me and I will teach you really non-trivial things.15.11.2020Internet
