part0104

Since the replacement of paper files stored in a physical file cabinet, relational databases have given way to new ground.

Relational database management systems or RDBMS, for short, are used anywhere information is stored or retrieved, like a login account for a website or articles on a blog.

Speaking of which, this also gave a new platform and helped leverage web sites like Wikipedia, Facebook, Amazon, and eBay.

Wikipedia, for instance, contains articles, links, and images, all of which are stored in a database behind-the-scenes.

Facebook holds much of the same type of information, and Amazon holds product information, payment methods, and even handles payment transactions.

With that in mind, banks also use databases for payment transactions and to manage the funds within someone’s bank account.

Other industries, like retail, use databases to store product information, inventory, sales transactions, price, and so much more.

Medical offices use databases to store patient information, prescription medication, appointments, etc.

To expand further upon databases, using the medical office, for example, allows for numerous users to be able to connect to the database at one time and interact with its information.

Since it uses a network to manage connections, virtually anyone with access to the database can access it from just about anywhere in the world.

Since the age of the digital database, it helps leverage mobile applications and provides new opportunities for software, or any other platforms that use databases on a daily basis, to be developed.

One app that comes to mind would be an email app, as it’s storing the emails on a server somewhere in a data center and allowing you to view and send emails.

These types of databases have also given way to new jobs and even expanded the tasks and responsibilities of current jobs.

Those who are in finance, for instance, now have the ability to run reports on financial data; those in sales can run a report for a sales forecast and so much more!

In practical situations, databases are often used by multiple users at the same time.

A database that can support many users at once has a high level of concurrency.

In some situations, concurrency can lead to loss of data or the reading of non-existent data.

SQL manages these situations using transactions to control atomicity, consistency, isolation, and durability.

These elements comprise the properties of transactions.

A transaction is a sequence of T-SQL statements that combine logically and complete an operation that would otherwise introduce inconsistency to a database.

Atomicity is a property that acts as a container for transaction statements.

If the statement is successful, then the total transaction completes.

If any part of a transaction is unable to process fully, then the entire operation fails, and all partial changes roll back to a prior state.

Transactions take place once a row or page-wide lock is in place.

Locking prevents modification of data from other users taking effect on the locked object.

It is akin to reserving a spot within the database to make changes.

If another user attempts to change data under lock, their process will fail, and an alert communicates that the object in question is barred and unavailable for modification.

Transforming data using transactions allows a database to move from one consistent state to a new consistent state.

It's critical to understand that transactions can modify more than one database at a time.

Changing data in a primary key or foreign key field without simultaneously updating the other location, creates inconsistent data that SQL does not accept.

Transactions are a big part of changing related data from multiple table sources all at once.

Transactional transformation reinforces isolation, a property that prevents concurrent transactions from interfering with each other.

If two simultaneous transactions take place at the same time, only one of them will be successful.

Transactions are invisible until they are complete.

Whichever transaction completes first will be accepted.

The new information displays upon completion of the failed transaction, and at that point, the user must decide if the updated information still requires modification.

If there happened to be a power outage and the stability of the system fails, data durability would ensure that the effects of incomplete transactions “rollback.”

If one transaction completes and another concurrent transaction fails to finish, the completed transaction is retained.

Rollbacks are accomplished by the database engine using the transaction log to identify the previous state of data and match the data to an earlier point in time.

There are a few variations of a database lock, and various properties of locks as well.

Lock properties include mode, granularity, and duration.

The easiest to define is duration, which specifies a time interval where the lock is applied.

Lock modes define different types of locking, and these modes are determined based on the type of resource being locked.

A shared lock allows the data reads while the row or page lock is in effect.

Exclusive locks are for performing data manipulation (DML), and they provide exclusive use of a row or page for the execution of data modification.

Exclusive locks do not take place concurrently, as data is being actively modified; the page is then inaccessible to all other users regardless of permissions.

Update locks are placed on a single object and allow for the data reads while the update lock is in place.

It also allows the database engine to determine if an exclusive lock is necessary once a transaction that modifies an object is committed, this is only true if no other locks are active on the object in question at the time of the update lock.

The update lock is the best of both worlds, allowing the reading of data and DML transactions to take place at the same time until the actual update is committed to the row or table.

These lock types describe page-level locking, but there are other types beyond the scope of this text.

The final property of a lock, the granularity specifies to what degree a resource is unavailable.

Rows are the smallest object available for locking, leaving the rest of the database available for manipulations.

Pages, indices, tables, extents, or the entire database are candidates for locking.

An extent is a physical allocation of data, and the database engine will employ this lock if a table or index grows, and more disk space is needed.

Problems can arise from locks, such as lock escalation or deadlock, and we highly encourage readers to pursue a deeper understanding of how these function.

It is useful to mention that Oracle developed an extension for SQL that allows for procedural instruction using SQL syntax.

This is called PL/SQL, and as we discussed at the beginning of the book, SQL, on its own, is unable to provide procedural instruction because it is a non-procedural language.

The extension changes this and expands the capabilities of SQL. PLSQL code is used to create and modify advanced SQL concepts such as functions, stored procedures, and triggers.

Triggers allow SQL to perform specific operations when conditional instructions are defined.

It is an advanced functionality of SQL and often works in conjunction with logging or alerts to notify principles or administrators when errors occur.

SQL lacks control structures, the for looping, branching, and decision making, which are available in programming languages such as Java.

The Oracle corporation developed PL/SQL to meet the needs of its database product, which includes similar functionality to other database management systems but is not limited to non-procedural operations.

Previously, user-defined functions were mentioned but not defined.

T-SQL does not adequately cover the creation of user-defined functions, but using programming, it is possible to create functions that fit neatly within the same scope as system-defined functions.

A user-defined function (UDF) is a programming construct that accepts parameters, performs tasks capable of making use of system-defined parameters, and returns results successfully.

UDF's are tricky because Microsoft SQL allows for stored procedures that often can accomplish the same task as a user-defined function.

Stored procedures are a batch of SQL statements that are executed in multiple ways and contain centralized data access logic.

Both of these features are important when working with SQL in production environments.

Although it can be much to learn, SQL can be a very simple language to use in a database.

By taking advantage of the necessary tools in this book, you can successfully maneuver your way throughout any database.

It is important to keep in mind that not all formulas work the same in every database, and there are different versions listed in the book.

There is plenty to learn when it comes to SQL, but with the use of practice and good knowledge, you can be as successful as you decide to be in any database.

Just how the English language has many rules to be followed, the same applies to SQL.

By taking the time to learn the language thoroughly, many things are achievable with the use of a database.

Refer back to any of the information in this book any time you are stumped on something, you are working on.

Although it can be a complex challenge, patience and practice will help you successfully learn SQL.

By remembering the basic commands and rules to SQL, you will avoid any issues that can come across most individuals that practice the use of it.

It is a lot of information to take in, but instead, take it as it comes.

Go to the practical tools that you may need for whatever you are trying to achieve through the database.

When presented with an obstacle or complex assignment, refer to the tools that will clear up what you need.

Take time to fully analyze what is before you while also trying to focus on one thing at a time.

Keep an open and simple mind when moving forward, and you will keep any issues from becoming more complicated than what they need to be.

You just need to take the time to understand what everything fully means in-depth.

If something doesn’t turn out as expected, retrace your tracks to find where you might have inappropriately added formula and some of the information.

By building and maintaining successful problem-solving skills, you will not limit your success.

If you have made it this far, you have seen how we can use Structured Query Language to turn lifeless entries in a database into meaningful information that you or your company can use to make the tough decisions that define every business venture.

Throughout this book, I have tried to demonstrate how to write efficient but intelligent queries using real-world scenarios that showcase the methods that I have found useful and still use today.

I have tried my best to avoid the philosophical debates, technicalities, and academic jargon that plague everybody of technical knowledge in order to bring you by the purest and most expedient path to your own unique mastery of SQL.

To the beginner: I hope you have enjoyed this journey we have taken together and that I have built a bridge for you to continue on your path to data mastery.

To those with previous experience: I hope this book has highlighted a few insights and given you a sandbox to test your ever-growing SQL toolbox.

Although we have focused mainly on the role of the database analyst (using your skills in query composition, composing statements, and answering everyday questions), there is plenty of demand for database designers as well.

If you have ever wondered who decides what fields will be contained in any given table, or how the tables will relate to each other, that is the job of a database designer/modeler.