Database Query Questions
Mettl Platform provides a structure of creating Database Query type questions. You can create your own tables, and then provide appropriate queries to retrieve desired data from the created tables.
A DB Query type question is created in two steps -
Create database table.
Then creating the DB Query question.
CREATE DATABASE TABLE
1. Click on Account settings and then click on Global Settings
2. In the following screen that opens, click on the 'Manage DB Schemas' button on the extreme right position on the screen.
3. Click on Add Schema button.
4. Provide the name of the schema in Schema Title blank, and in the Add Database blank, click on the drop-down scroll and select the TYPE of database you want to create the table in. Mettl platform supports three types of DB Servers - MSSQL 2008 R2, Oracle 11g and MYSQL 5.5.
5. Once you select the DB server, provide the table structure in the designated blank that appears.
6. Click on Add DataSet to correspond to the contents of the database table that you created. Then, click on Save Schema to upload your table into the database.
Once the Database table has been created, create the relevant queries to extract data from the created table, as seen in the following step.
CREATE DB QUERY QUESTION:
1. Click on Add Question tab, and select the question type Database query question. To select the question type first click on Application based questions and then select Database query question and after that select the topic name in which you want to add the question.
2. In the consecutive screen that opens up, provide the question title, question details (the query question to extract data from the table) and specify the difficulty level of the questions.
3. From the Select Schema scroll-down option, select the table name that you created.
4. In the Expected Query space, provide the correct query that will extract the relevant/desired details from the table.
5. In the Order By option (optional fill-in), provide the order in which you wish the table contents to be displayed (e.g. - ASC, DESC, etc). The View Schema link (in blue) displays the table that you had created.
6. Then, click on Add test case button and fill in the relevant details, i.e - provide a brief heading about the asked query in the Testcase Title, click on the isDefault check-box, in-case you want the Test case to be marked 'Default', select the Dataset that you had provided during the creation of the data-table in the Testcase Dataset space, and specify the marks you wish to award to this test-case in Testcase Marks space. You can add more test-cases as per your requirement.
7. Finally, click on the 'Save' button.
Errors while creating a DB question?
If you face an error like the one shown below, then it usually means that -
you have not created your table correctly, or, you have not called the table correctly, i.e - have't provided the correct query to extract data from the table.
You have provided a query to create a 'View' table, apart from the original table that you created.