Chapter 4, Problem 6CAT

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.

“ORDER BY” Clause:

• User can sort the data in specific order using “ORDER BY” clause.
• The column on which to sort data is called a sort key or a simple key.
• To sort the output, use an “ORDER BY” clause followed by the sort key.
• If the user does not indicate a sort order, the output displayed in default order that is ascending order.

Example:

The example for “ORDER BY” clause is given below:

SELECT STUDENT_ID, STUDENT_NAME, STUDENT_CREDIT FROM STUDENT ORDER BY STUDENT_CREDIT;

 The above query is used to list student ID, name and credit for each student with ascending order of student credit using an “ORDER BY” clause.

• From the given query, the sort key is “STUDENT_CREDIT”. So, the rows are sorted in ascending order by “STUDENT_CREDIT”.

“DESC” Operator:

• User can sort the data in descending order using a “DESC” operator.
• To sort in descending order, user follows the name of the sort key with the “DESC” operator.

Example:

The example for “DESC” operator is given below:

SELECT STUDENT_ID, STUDENT_NAME, STUDENT_CREDIT FROM STUDENT ORDER BY STUDENT_CREDIT DESC;

The above query is used to list student ID, name and credit for each student with descending order of student credit using an “ORDER BY” clause with “DESC” operator.

• From the given query, the sort key is “STUDENT_CREDIT”.