Job Ready SQL

Lesson 15Diving into Advanced SQL Topics

You've covered creating, reading, updating, and deleting tables and fields. You've also covered various aspects of selecting and manipulating the data within those databases and tables. There are a number of other actions and features you can tap into when working with SQL.

In this lesson a variety of topics will be touched upon that add to the foundation that you've already established. These topics are considered more advanced, but each is important in its own way.

Learning Objectives

By the end of this lesson, you will be able to:

• Use a simple subquery
• Explain the pros and cons of views
• Explain the requirements of a database transaction
• Use optimization techniques to improve the performance of a MySQL database
• Describe the use of indexes to improve database performance

ADDING SUBQUERIES

SQL is a specialized language that executes only in the context of a relational database. You can't create a video game with it. Despite that, SQL is incredibly powerful, flexible, and expressive. To get a sense of its flexibility, consider this: any value, table, or set of values can be replaced by a second, separate query.

A subquery is a syntactically correct, complete query that is embedded in another query to produce a value or tabular result set. Removed from its parent, a subquery is still valid, though it may use values from its parent to establish context. Three main areas where subqueries are often used include the IN clause, where ...