The basics are firmly inside my head. I can find data, manipulate it, massage it, modify it, and put it back either as it was, or in a new state. But there was still a lot to go over.

Views make the world appear differently. The concept was familiar to me, although under a different concept. Virtual tables. Creating something you can query and use as if it were a table, even though it’s not. Great tool for smoothing things out, and easing the work load on the DBA. They also comes with warnings like everything else. Take a view (which is a named query) and you can manipulate the data used to create it, or manipulate data you read from the view itself. Use the view to help make another view. Layer the power on top of itself a hundred fold. Wait, no, that’s not  a good idea. Since they are queries, they also have to be run when called, so use too many of them, or use them inefficiently, and your performance drops hard.

This also touches on the next thing covered, tuning your SQL. Like a racing team, getting every last cycle of CPU, every last bit of RAM, and last millisecond you can out of the hardware to drive your database. The mundane first level of tuning was focusing on hardware actually, noting that a simple increase of RAM or CPU, or separation of data table locations could increase performance of the system as a whole. Then you look at the statements themselves, either manually running through them, or using the built in tools to analyze the performance of a given query (the tools are not however included in the freely downloaded versions) and let it try and tell you where it thinks you can improve it. Sometimes it’s as simple as a minor re-wording of the query, other times it’s a complete rewrite of the query. They also covered the concept that for the most part, only a small percentage of the data is used most often, and the rest is mainly legacy support or such for long term reports. Just an interesting tidbit that makes sense, and was used to show that you don’t have to tune the entire system, but rather focus on the parts that get the most load.

Protecting your Data

A recurring concept that was brought out finally was data security and integrity. Protecting the data not only from itself, but from malicious users, and those who just don’t know what they are doing. The concept of granularity and scope was brought up, defining the level of security, from column level all the way to entire database protecting. Locking the data, and how the locks functioned, and how they are optimized and ways to consider optimizing them for better performance. The ability to grant and revoke permissions and privileges to users, and the problems of cascading permissions and how to avoid those issues was looked at. Assigning permissions to groups and then users to those groups to make for a more efficient and easier to manage security setup was discussed.

They also discussed the concept of transactions, committing changes and rollbacks. Discussing the problems that crop up if the data isn’t being monitored or watched properly in a multi user setup. Where one user makes a change to data that another user also is looking at, whether the changed data is propagated to all users properly and how to avoid problems with this situation. Rolling back changes in the data, and how to avoid problems with that process as well. Using a transaction control system to streamline and protect all of this and thus protect the users and the data itself from potential problems.

Conclusions

SQL is extremely versatile and powerful even at the basic to intermediate level of use. The more one works with it the better they become at harnessing that power for the work they perform. My current knowledge is not enough. I want to know more, and be able to use more. While I am somewhere between novice and moderate user (by my own estimation) it doesn’t mean I cannot go further and enhance my own knowledge of the language. It is a tool, a tool of the business world. Couple it with other tools and it becomes a better tool. Tools help to build the world, and those who are willing to learn the tools can help build that world. I look forward to honing my understanding of this tool, and adding to it, with things like PL/SQL, APEX, even tools like Discoverer to use the data to further myself and those I work for.