CodeRunner: SQL question type help

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

Re: SQL question type help

de către Richard Lobb- marți, 17 martie 2020, 11:54

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.

Re: SQL question type help

de către Pierre FACON- marți, 17 martie 2020, 23:13

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

Re: SQL question type help

de către Richard Lobb- miercuri, 18 martie 2020, 10:13

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.

Re: SQL question type help

de către Jean-Christophe Carré- sâmbătă, 2 martie 2024, 21:55

Hello Richard,

As a programming teacher, I would like to have access to the java question repository, as well as the Python question repository. But all I have is the presentation page of the course, with a long list of all the teachers, and at the bottom, a message saying "You cannot enrol yourself in this course."
How can I do, to have access to it ?
I think it might be a good idea to explain the enrollment process on the home page, so that those repositories get more active. The more we share, the better our classes !

Thanks and have a good day !

Re: SQL question type help

de către Richard Lobb- marți, 5 martie 2024, 21:21

Sorry you had trouble figuring out how to get access. But it does say at the top of the QuestionRepositories section:

I have to admit that the question repositories don't work well. There is certainly a lot of interest in sharing questions but experience suggests that, with a few notable exceptions, most people are more interested in sharing other people's questions than their own.

If someone wishes to set up a server to manage the sharing of CodeRunner questions in a more effective manner, I shall be delighted.

I have given you access to the SQL and Python question repositories.

Richard

Re: SQL question type help

de către Pierre FACON- marți, 17 martie 2020, 23:53

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

Re: SQL question type help

de către Richard Lobb- miercuri, 18 martie 2020, 10:19

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.]

Re: SQL question type help

de către Pierre FACON- miercuri, 18 martie 2020, 22:17

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

Re: SQL question type help

de către Pierre FACON- vineri, 20 martie 2020, 01:58

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

Re: SQL question type help

de către Richard Lobb- vineri, 20 martie 2020, 09:47

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.