Sample Exam Questions

From the objective of OSS-DB Exam Silver
S3.1 SQL commands (SELECT statement)

Sample Question


The following is a part of the sales table that records the sales date (sales_date), sales person (sales_person), client (client), item name (item), sales amount (amount), and so on.

select * from sales;
 sales_id |  sales_date  | sales_person|  client  |   item        | amount
          1 | 2021-04-21 | Maeda           |  A Store | Computer | 100000
          2 | 2021-04-22 | Ohtani           |  B Shop | Printer     |   20000
          3 | 2021-04-23 | Arihara          |  C Plant | Router      |   10000
          4 | 2021-04-24 | Maeda           |  D Store | Mouse      |     2000  

You want to list the sales persons whose total sales amount is greater than 100000. In the following SELECT statement, what keywords should be filled in the blanks x, y?

select sales_person from sales [x] sales_person [y] sum(amount) > 100000;



  3. JOIN


  5. WHERE

※This sample exam is different from those that appear in the actual OSS-DB Exam.

Answer and Explanation

The SELECT statement can not only search for data that meets specified conditions, but also classify and aggregate the found data. There can be various axes for classification, such as by month, by sales person, by client, by item name, etc. The key column name for classification (in the example, sales_person) is specified by GROUP BY.

In the example, there is also a condition on the total sales amount. When thinking of a conditional expression in a SELECT statement, the WHERE clause might come to mind first. However, there is another clause called HAVING, and understanding the difference between them is crucial. When executing a SELECT statement, it first checks whether each row in the table matches the condition specified in WHERE, and retrieves all matching rows. Then, it classifies and aggregates the retrieved data based on the column (or expression) specified in GROUP BY. Finally, it checks whether each aggregated row matches the condition specified in HAVING, and outputs only those rows that do. The total sales amount is a value that is only known after aggregating the data, so this condition must be specified in HAVING.

Therefore, the correct answer is [x]=A, [y]=B.

In the example, we only set conditions for the aggregated results. However, for the condition such as the total sales amount in April being greater than 100000, you would add a condition in the HAVING clause for sum(amount), and specify a condition in the WHERE clause that restricts sales_date to April.