Chapter 4, Problem 16CAT

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.

“IN” Clause:

• The “IN” clause consists of an “IN” operator followed by a set of values.
• Using an “IN” clause, user can determine whether a column contains one of a particular group of values.

Example:

The example for “IN” clause is given below:

SELECT STUDENT_ID, STUDENT_NAME, STUDENT_CREDIT FROM STUDENT WHERE STUDENT_CREDIT IN(50, 70, 100);

 The above query is used to list student ID, name, and credit for each student with a credit limit of “50”, “70” or “100”.

• From the given query, user can determine whether a student credit limit is “50”, “70” or “100” using an “IN” operator.

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.