Chapter 4, Problem 8CAT

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.

“COUNT” function:

• It is the one function of aggregate function.
• The “COUNT” function is used to compute the number of rows in a table.

Example:

The example for “COUNT” function is given below:

SELECT COUNT(*) FROM STUDENT WHERE MARK_CREDIT >= 90;

The above query is used to display the number of students whose mark credit is greater or equal to “90” by using “COUNT” function.

• From the given query, the asterisk (*) represent any column.
• User can also count the number of rows in a query by selecting a particular column instead of using the asterisk.
  • The below example is as follows

    SELECT COUNT(STUDENT_ID) FROM STUDENT WHERE MARK_CREDIT >= 90;

“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”.

“GROUP BY” Clause:

• User can group the data using “GROUP BY” clause.
• This clause allows the user to group data on a specific column and then computes statistics when user preferred.

Example:

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

SELECT CUSTOMER_NAME, SUM(AMOUNT) FROM CUSTOMERS GROUP BY CUSTOMER_NAME;

The above query is used to list the customer name and the sum of amount using “GROUP BY” clause.