Wiley SQL Fundamentals
Developed in the 1970s at IBM, SQL has grown and advanced over the years to become the industry standard language specifically designed with databases in mind.
In this session we explore data storage, how the relational model compares with other major models, and provide a look at the important features of relational databases.
SQL is a flexible language that you can use in a variety of ways. It’s the most widely used tool for communicating with a relational database.
In this session you will learn what SQL is and isn’t
In this session you will be introduced to the three languages within SQL that offer everything you need to create, modify, maintain, and provide security for a relational database:
♦ Data Definition Language (DDL)
♦ Data Manipulation Language (DML)
♦ Data Control Language (DCL)
In this session, you will go through the process of using a Rapid Application Development (RAD) tool to build, alter, and drop a simple table, and then learn how to build, alter, and drop the same table using SQL.
The first step to designing any database is to identify what to include and what not to include. The next steps involve deciding how the included items relate to each other and then setting up tables
This session also discusses how to use keys, which enables access ito ndividual records and indexes quickly.
At any given time, you probably want to do one of four things with data: add it to tables, retrieve and display it, change it, or delete it from tables.
In principle, database manipulation is quite simple. In this session we will understanding that to add data to a table isn’t difficult — you can add your data either one row at a time or in a batch.
You will also learn than changing, deleting, or retrieving one or more table rows is also easy in practice.
In this session, we introduce the concept of a period of time, defining it in a very specific way. We will look at various kinds of time and the effect that temporal data has on the definition of primary keys and referential integrity constraints.
We then discuss the way that very complex data can be stored and operated on in bitemporal tables. Finally, I give the standard template placeholder arguments for date and time data.
In this session we explore how SQL recognizes several kinds of values:
♦ Row values
♦ Literal values
♦ Variables
♦ Special variables
♦ Column references
The performance penalty exacted by SQL’s limitations prompted the addition of new features to SQL
Some of those added features:♦ CASE expression, provides a long-sought conditional structure.
♦ CAST expression, facilitates data conversion in a table from one type of data to another.
♦ ROW VALUE expression, enables you to operate on a list of values where previously you could operate only on a single value.
SQL enables you to use some characteristics of the data to determine whether a specific table row is of interest to you. The ♦ SELECT, ♦ DELETE, and ♦ UPDATE
statements convey to the database engine (the part of the DBMS that interacts directly with the data), which rows to select, delete, or update. You add modifying clauses to the SELECT, DELETE, and UPDATE statements to refine the search to your specifications.
Because the data in a relational database is distributed across multiple tables, a query usually draws data from more than one table. SQL has operators that combine data from multiple sources into a single result set. These are the ♦ UNION, ♦ INTERSECTION, and ♦ EXCEPT
operators, as well as a family of ♦ JOIN operators. Each operator combines data from multiple tables in a different way.
f you normalize a database properly, the data is scattered across multiple tables. Most queries that you want to make need to pull data from two or more tables. One way to do this is to use a join operator or one of the other relational operators.
The relational operators take information from multiple tables and combine it all into a single result set. Different operators combine the data in different ways.
Recursion is a feature that’s been around for years in programming languages such as Logo, LISP, and C++. In these languages, you can define a function (a set of one or more commands) that performs a specific operation.
The main program invokes the function by issuing a command called a function call. If the function calls itself as a part of its operation, you have the simplest form of recursion.
The person in charge of a database can determine who has access to the database — and can set users’ access levels, granting or revoking access to aspects of the system.
The system administrator can even grant or revoke the right to grant and revoke access privileges. If you use them correctly, the security tools that SQL provides are powerful protectors of important data. Used incorrectly, these same tools can tie up the efforts of legitimate users in a big knot of red tape when they’re just trying to do their jobs.
A nontrivial, multiuser DBMS application is a large, complex structure. In the course of operation, many things can go wrong.
Methods have been developed for minimizing the impact of these problems, but the problems can never be eliminated completely. This is good news for professional people who do database maintenance and repair — automating them out of a job will probably never be possible.
This sessiom discusses the major things that can go wrong with a database and the tools that SQL provides for you to deal with the problems that arise.
Joe User probably won’t sit down at his terminal and enter an SQL SELECT statement to find the answer.
Systems analysts and application developers are the people who are likely to be comfortable with SQL, and they typically don’t make a career out of entering ad hoc queries into databases.
Instead, they develop applications to make those queries
Unfortunately, “standard” SQL is not all that standard. Even DBMS vendors who claim to comply with the international SQL standard have included proprietary extensions in their SQL implementations — which make them incompatible with the proprietary extensions in other vendors’ implementations.
User organizations, particularly large ones, need a way to make cross-DBMS communication possible — a tool that doesn’t require vendors to dumb down their implementations to the lowest common denominator. This other way is ODBC (Open DataBase Connectivity).
SQL and XML provide two different ways of structuring data so that you can save it and retrieve selected information from it:
SQL is an excellent tool for dealing with numeric and text data that can be categorized by data type and have a well-defined size.
SQL was created as a standard way to maintain and operate on data kept in relational databases.
XML is better at dealing with free-form data that cannot be easily categorized.
The strengths and goals of SQL and XML are complementary. Each reigns supreme in its own domain and forms alliances with the other to give users the information they want, when they want it, and where they want it.
As NoSQL databases have become increasingly popular, the value of exchanging data with relational databases has become clear.
There are decades worth of data stored in relational databases that could be of value to applications designed to operate on NoSQL databases. The reverse is also true. Huge quantities of data are being stored in NoSQL databases that could be used by applications designed to operate on relational databases.
To meet this need, functionality has been added to the SQL specification to enable the translation of JSON data into a form that can be handled by SQL,
Lorem ipsum dolor sit amet, consectetur adipiscing elit. Ut elit tellus, luctus nec ullamcorper mattis, pulvinar dapibus leo.
Lorem ipsum dolor sit amet, consectetur adipiscing elit. Ut elit tellus, luctus nec ullamcorper mattis, pulvinar dapibus leo.
Wouldn’t it be great if every application you wrote worked perfectly every time? Yeah, and it would also be really cool to win $314.9 million playing Powerball. Unfortunately, both possibilities are equally unlikely to happen.
Error conditions of one sort or another are inevitable, so it’s helpful to know what causes them. SQL’s mechanism for returning error information to you is the status parameter (or host variable)
♦ SQLSTATE
Based on the contents of SQLSTATE, you can take different actions to remedy the error condition.
Lorem ipsum dolor sit amet, consectetur adipiscing elit. Ut elit tellus, luctus nec ullamcorper mattis, pulvinar dapibus leo.
A database can be a virtual treasure trove of information, but like the treasure of the Caribbean pirates of long ago, the stuff that you really want is probably buried and hidden from view.
The SQL SELECT statement is your tool for digging up this hidden information. Even if you have a clear idea of what you want to retrieve, translating that idea into SQL can be a challenge. If your formulation is just a little off, you may end up with the wrong results — but results that are so close to what you expected that they mislead you. To reduce your chances of being misled, use the following ten principles.