CIT 111: Introduction to Databases

Prepare 06: Subqueries and Indexes

This week you will...

Instructions

This material will help you build a foundation for this week's topic. Test your understanding by taking this week's prepare quiz in Canvas. The quiz is open book and open note. You may retake the quiz up to 3 times to improve your score.

Terms to Know

Subquery:
 A query inside another query. The nested query will execute first.
Index:
A data structure that provides a way to retrieve data more quickly from a table based on values in a specific column.
View:
A virtual table based on results from a query from one or more base tables.

SUBQUERY

Watch:

(22:20 mins, "MySQL Subqueries" Transcript)

A subquery is a query inside another query. The subquery executes first and then the result of that query is used as a value in the main query, which then runs. A subquery can be nested inside a WHERE, HAVING, FROM or SELECT clause. They can also be placed inside other statements such as an INSERT statement.

Subquery in an INSERT statement:
INSERT INTO product
VALUES (999, "Haro Mountain Madhem - 2020", 2020, 450.00,
    (SELECT brand_id
    FROM brand
    WHERE brand_name = "Haro"),
    (SELECT category_id
    FROM category
    WHERE category_name = "Mountain Bikes"));

The two subqueries run first returning the value 2 for brand_id of Haro and the value 6 for the category_id of Montain Bikes. Then with those two values the INSERT runs and a new bike is placed in the product table with the proper brand_id and category_id foreign keys entered.

Subquery in a WHERE clause
SELECT product_name, list_price
FROM product
WHERE list_price > (SELECT AVG(list_price) FROM product);

The subquery runs first which finds the average of all the list prices in the product table. Then that single value result becomes the value in the WHERE clause and only those product names and list prices show up that are above the average of all the bikes—the value that the subquery returned.

Subquery in a SELECT clause
SELECT product_name, list_price,
  (SELECT SUM(quantity)
  FROM cust_order_item
  WHERE product_id = 20) AS Total_Quantity_Sold
FROM product
WHERE product_id = 20;

The subquery runs returning all quantities added up of the product with product_id of 20 from all the order items. Then the main query runs using that result as it’s 3rd column of the result set. It returns one row with the product name, the price and then the total quantity of how many of the product have been sold.

OPTIONAL: To learn more about Subqueries read Working with Subqueries

INDEX

An index provides a way to retrieve data more quickly from a table based on values in specific columns. The database management system can go directly to a specific row with an index rather than having to go row by row until it is found without an index. Indexes do use extra resources and time when updating or deleting rows with indexes, so don’t create an index for a column unless you know you will be searching on that column often. Indexes are automatically created for primary keys and unique keys.

To create an index:
CREATE INDEX index_name ON table_name (column)
CREATE INDEX bike ON product(product_name);

This would create an index called bike on product_name.

OPTIONAL: To learn more about indexes read: SQL Indexes Explained

VIEW

A view is a virtual table based on the results from a query from one or more base tables. The view table will always refer back to the base table(s) for the most current data, so it’s not a point in time copy and doesn’t store any data itself. So it always shows up-to-date data. The data is recreated every time the view is referenced; or in other words, the query used to define it is rerun.

Views come in handy when you have a complex query that you know you will need again and again so you create view from that query and then you have access to the view table without having to run the query again and again. You can also protect your base tables by having this extra layer of security between the user and the base table(s) because of the views. Because the view only contains selected data and are not permanent, users cannot mess up the data and views are often used in applications where the user only gets access to data through these views. You can also make sure only certain data is access by certain users with views.

CREATE VIEW view_name AS SELECT column, … FROM table 
CREATE VIEW employees_limited AS
SELECT emp_no, first_name, last_name
FROM employees;

This creates a view called employees_limited using the three columns from the employees table. If there were other columns like salary or social security number in the employees table then the view limits it to just those three columns.

Then you can use the view just like you use tables in the FROM clause.

SELECT emp_no, last_name
FROM employees_limited
WHERE last_name = "Simmel";

OPTIONAL: To learn more about SQL views read SQL Views

Submission

No prepare quiz this week! Spend your time on the review project.

Useful Links: