ECDL Exam ECDL-ADVANCED Topic 8 Question 48 Discussion

In order to enhance the performance of the query, James will use the WHERE clause with the SELECT statement as follows:

SELECT * FROM OrderDetails WHERE custname='customer name';

The WHERE clause is used to filter data given in the table. The filter is based on one or more columns given after the SELECT statement. The columns are separated by commas.

Answer option C is incorrect. The DISTINCT clause is used to prevent rows from getting duplicated in the result set. It can only be used with the SQL SELECT statement.

The syntax for the DISTINCT clause is as follows:

SELECT DISTINCT <columns>

FROM <table_name>

WHERE <conditions>

Answer option B is incorrect. The TOP clause is used to retrieve data for the first set of rows. The example is as follows:

SELECT * TOP 10 FROM OrderDetails;

The statement will retrieve only the top ten rows from the OrderDetails table.

Answer option D is incorrect. The GROUP BY clause groups selected rows on the basis of values of specified column(s) for each row, and returns a single row of summary information for each group. Rows that have the same values in the specified grouping column(s) are grouped together. The GROUP BY clause suppresses duplicate rows for the grouping column(s). It does not guarantee the order of the result set. Therefore, the ORDER BY clause should be used with the GROUP BY clause to sort the result set in the desired order. The syntax of the GROUP BY clause is given below:

SELECT column_name, aggregate_function(column_name) FROM table_name WHERE column_name operator value GROUP BY column_name

Consider the following example, EMPLOYEE_EXPENSES table:

P_ID

LastName

Expenses

1

Harry

1000

2

Steve

400

3

Harry

2000

To group the total sum (total order) of each employee from the EMPLOYEE_EXPENSES table, run the following command:

SELECT LastName, Expenses FROM Orders GROUP BY LastName

The output of the command will appear as:

LastName

Expenses

Harry

3000

Steve

400

Chapter: DATABASE, ADVANCED-LEVEL

Objective: Query Design