Question Authors' Forum

SQL question type help

SQL question type help

by Pierre FACON -
Number of replies: 8

Hello everyone,

I used CodeRunner to create 40 Java problems thanks to guides found on that site. Thank you very much for that very good product. 

Now, we would like to create SQL questions (sqlite), in order to assess SQL knowledge of our student. But i don't find the way to create a database, load it, query it, etc.. as we can find for Java programs. Is there any resource i can use to help us in creating  SQL questions ? a kind of tutorial for example ? 

I don't code in Python, is Python necessary to create SQL questions ?

Thanks for help !

Pierre, from Paris, France

In reply to Pierre FACON

Re: SQL question type help

by Richard Lobb -

There is some discussion of SQL questions in this thread. There are a couple of demo questions here. I just edited the demo questions so they include an XML export of the questions, which should give you a basis for writing your own.

Note that the SQL questions use sqlite3 rather than a standard shared SQL server like mysql. So you have to be prepared to compromise and teach the sqlite3 dialect.

In reply to Richard Lobb

Re: SQL question type help

by Pierre FACON -

Hello Richard, 

Very kind of you for that help.

As i told you in this post, i created 38 questions concerning Java introduction course. If that can help someone, where can i put those resources to help others ?

Thanks 

Pierre from Paris

In reply to Pierre FACON

Re: SQL question type help

by Richard Lobb -

Thanks for the offer, Pierre. There is a Java question repository "course" on this site here. These repositories are just standard Moodle courses in which teachers can use the question bank to download or upload questions. The Java repository is in a woeful state and as I don't teach Java myself I can't contribute. Some uploads would be much appreciated. Feel free to create a question category for your own questions (give it any name you like) and you can then just export them from your site and import them into that course.

In reply to Richard Lobb

Re: SQL question type help

by Pierre FACON -

Hello Richard, 

I have 2 questions concerning .db file 

1 Is it better to deliver it with already existing tables inside it, or is it better to write DDL  and DML (create table and inserts). In the case where i write my own DDL, in which XML entry do i put those verbs ?

2 Which tool can i use to take a sqlite3 db file and get the 'encoding="base64"' format file ?


Thanks a lot

Pierre from Paris

In reply to Pierre FACON

Re: SQL question type help

by Richard Lobb -

Sorry, I should have explained that you shouldn't attempt to deal directly with the exported xml files. Just import them into your course and then open them with the question editor. You'll see that the db file is just a standard CodeRunner support file. You create it by running sqlite3 on your usual Windows/Mac/Linux work machine and use standard sqlite3 commands to populate it with the required tables and data. Once you've done that you can copy the db file straight into an SQL question as its support file.

As with all CodeRunner question types, when you're in the question editor you can get some help on how to use a question type by opening the Question Type Details section. [I perhaps should rename this to Question Type Help.]


In reply to Richard Lobb

Re: SQL question type help

by Pierre FACON -

So kind of you for this reply.

Here in Paris, we are confined, then the best way to kill time is to learn CodeRunner !!

Thanks for all

Pierre, Paris, Fraance

In reply to Pierre FACON

Re: SQL question type help

by Pierre FACON -

Hello everyone,

Very happy to tell you that everything is working well with SQL Question  type and a sqlite3 db of my own.

Now that everything in working fine, is it possible de install either mysql ou pgsql on the jobee server, modify the question  type, to that i can test the present SQL langage ? 

Is there anywhere any help to implement that ? 

I thank you very much

Pierre from Paris

In reply to Pierre FACON

Re: SQL question type help

by Richard Lobb -

The question of whether a server like mysql or pgsql could be used instead of sqlite3 is discussed in this thread. It's a hard problem.