Chapter 4, Problem 16TD

Program Plan Intro

“SELECT” command:

The “SELECT” command is used to retrieve data in a database.

Syntax for selecting values from the table is as follows:

SELECT STUDENT_ID FROM STUDENT;

• The given query is used to display each student ID from “STUDENT” table.

Subquery:

• Subquery means one query is placed inside with another query.
  • The inner query is called a subquery.
  • The subquery is calculated first.
    • After the subquery has been determined and the outer query can apply the results of the subquery.

Example:

The example for subquery is given below:

SELECT ORDER_NUMBER FROM ORDER_LINE WHERE BOOK_NUMBER IN(SELECT BOOK_NUMBER FROM BOOKS WHERE CATEGORY = 'CE');

The above query is used to list the order number using subquery.

• From the given query, the subquery is “(SELECT BOOK_NUMBER FROM BOOKS WHERE CATEGORY = 'CE')”.
• The subquery is computed first and then outer query is computed.
  • The outer query retrieves the order number on every row in the “ORDER_LINE” table for which the book number is in the results of the subquery.
    • Because that table contains only the book numbers in category “CE”, the results display the preferred list of order numbers.

“AVG” function:

• It is the one function of aggregate function.
• The “AVG” function is used to compute the average value in a column.

Example:

The example for “AVG” function is given below:

SELECT AVG(MARK_CREDIT) FROM STUDENT;

The above query is used to display the average of mark credit from “STUDENT” table using “AVG” function.