Question Authors' Forum

ask question on "create database" SQL statement

ask question on "create database" SQL statement

by Uthpalani Kavisekara -
Number of replies: 9

I'm using CodeRunner for some time now and I must say that it is a very useful one. I was able to create various SQL questions. (by changing the Python code and using the "extra template data" field as needed) 

Now I need to check students' knowledge in "create database" statement. Since in the normal process we need to upload SQLite database to create a question, it is not straightforward to come up with a question to evaluate the "create database" statement. According to my understanding, an SQLite database should be created whenever a student correctly writes the "create database" statement and hit the check button. 

So I would like to know whether this is possible with CodeRunner.

In reply to Uthpalani Kavisekara

Re: ask question on "create database" SQL statement

by Richard Lobb -
In my understanding of sqlite3, there is no create database statement. I thought you had to create a database at the shell level, by starting sqlite3 with a command like

    sqlite3 mynewdatabase.db

If there's no existing file mynewdatabase.db, it's created, and you have your database.

That makes it a bit problematic to check, as the student's answer would have to begin with the shell command to start sqlite3 (with a new database) and would then, presumably, follow with the commands that were entered into the now-running sqlite3.

I attach a question that allows this. You'll see I've had to hack at the template quite a lot. I've added a template parameter dbfilerequired that defaults to true if not supplied. If it is supplied but is false the normal code to locate the database file in the support files is bypassed and instead sqlite3 is launched using the first non-empty line of the student's answer. I've written it that way so you could convert it into a new SQL question type, if you know how to do that.

Please realise this is a proof of concept only and has had negligible testing. Also, it's a bit silly that if the student submits a wrong answer the test case has a .schema line that tells the student exactly what create table statement to use. But I leave you to turn it into a working question.
In reply to Richard Lobb

Re: ask question on "create database" SQL statement

by Uthpalani Kavisekara -

Thanks Richard, I import the question you attached here in order to have an understanding.

I did a small change in the expected output column and I got the results as follows.

 

As you have mentioned, students have to provide a sqlite3 command to create a database according to this question template. In SQL it is "CREATE DATABASE Fred" which I need to evaluate. 

I will give it a try and see. 

In reply to Uthpalani Kavisekara

Re: ask question on "create database" SQL statement

by Richard Lobb -
I'm not sure you're fully understanding how the CodeRunner SQL question type works. It uses sqlite3 - see https://www.sqlite.org/docs.html. This is not a database server and it has its own version of SQL. Database servers like MS SQL Server, mysql, postreSQL require users to authenticate and then offer a a command like CREATE DATABASE to allow users to create their own databases. But sqlite3 uses a file as a database and the filename is supplied to the sqlite3 program when you run it. So there is no CREATE DATABASE statement in the sqlite3 variant of SQL, at least as far as I can see. Indeed there are lots of differences between sqlite3's SQL and others - see here for example.

Using database servers for class exercises is difficult because each user generally needs their own database so as not to interfere with other class members, so you need to set up authentication for each student. Furthermore because database servers are fundamentally stateful, you would need to re-intialise the database for each student test run. Using sqlite3 removes all such complication and is an excellent way to introduce relational databases and (a variant of) SQL. However there are major differences in the sqlite3 language from the SQL used by database servers (though there is no true standard SQL and all servers implement their own variants of the language).

In reply to Richard Lobb

Re: ask question on "create database" SQL statement

by Uthpalani Kavisekara -
yes Richard I understand that. Since my course is about MySQL, I was able to somehow create questions with CodeRunner (select, insert, update, etc... syntax are same in both SQLite and MySQL) except for this particular question for the "create database" statement. Thank you for your explanation on this.
In reply to Richard Lobb

Re: ask question on "create database" SQL statement

by Uthpalani Kavisekara -
By the way, there is another thing I would like to know. Is there a way I can load a stylesheet that has some custom CSS for a particular question in CodeRunner? That is, when the student clicks on the attempt quiz and when the question page loads, I need my custom stylesheet to be loaded as well (but only for that question).
In reply to Uthpalani Kavisekara

Re: ask question on "create database" SQL statement

by Richard Lobb -
The short answer here is just No. All CSS should be within the document element according to the HTML standard and there's no way that I know of to dynamically add CSS to that. However, most browsers do seem to process <style> elements within the <body> element. So you might be able to switch to editing in HTML mode and inserting a <style> element at the start of your question text. 
In reply to Richard Lobb

Re: ask question on "create database" SQL statement

by Uthpalani Kavisekara -
I switched to HTML mode and tried to insert CSS with "style" element as you have mentioned here, but when I save the question the "style" tags and everything within has removed. I think this has something to do with the rich text editor.
Anyway thanks Richard for your answer.
In reply to Uthpalani Kavisekara

Re: ask question on "create database" SQL statement

by Richard Lobb -
Yes, you're right, sorry: the Atto editor strips out <style> elements. But I just managed to enter such elements by switching my personal Editor Preferences to Plain Text area. In that mode your HTML doesn't get censored. But of course if you switch back to using Atto, and then edit the question again, your <style>  element will get stripped out. Caveat emptor.
In reply to Richard Lobb

Re: ask question on "create database" SQL statement

by Uthpalani Kavisekara -
This workaround saves me a lot of time. I switch to plain text editor and it worked. Thanks a lot Richard!