Small Examples: tables + SQL II

As a way to create a minimal subset of examples for an experiment lets go through the ones presented on the previous post. This time we will explain what information we expect to obtain with each example.

    1. SELECT STUDENT_ID, CREDITS FROM STUDENT WHERE MAJOR = ‘MATH’ AND CREDITS > 30;

In this query we are accessing only one instance with a SELECT. Not all of the column names of the STUDENT table are included in the query and the ones included play different roles. With this example we will be able to answer the following yes or no questions:

  • a) Does the participant represent the fact that it is a SELECT query? — To analyze the data for this question it will be necessary to alter the order among the SELECT, INSERT, UPDATE and DELETE queries presented to the participants, or mention as part of the introduction that we are going to present SELECT, INSERT, UPDATE and DELETE queries.
  • b) Does the participant represent attributes corresponding to columns names not used in the query? — We will provide the query and small tables with data.
  • c) Does the participant mark the attributes corresponding to the columns that are retrieved?
  • d) Does the participant mark the attributes corresponding to the columns used to filter the data?
  • e) Does the participant mark the attributes corresponding to columns that are retrieved and used to filter the data?
  • f) Are numeric and string literals represented?
  • g) Are comparison operators represented?

More complex questions related to this example are:

  • h) For each of the yes or no questions that had a affirmative answer: How it was represented?
  • i) In which order the different parts of the diagram where painted?
  • j) Which entity-relationship diagram notation was used by the participant?
    2. SELECT * FROM STUDENT;

In this query we are accessing all the attributes of an entity without specifying the names of the columns. With this example we will be able to check if the answer to the questions a) and b) changes when the asterisk is used. For this query questions h), i) and j) also apply.

    3. SELECT COURSE_NO, COUNT(*) FROM ENROLLMENT GROUP BY COURSE_NO HAVING COUNT(*) BETWEEN 2 AND 3;
    4. SELECT DISTINCT STUDENT_ID, GRADE FROM ENROLLMENT WHERE GRADE IS NOT NULL ORDER BY STUDENT_ID ASC, GRADE DESC;

In the previous two queries we are using other sections of a SELECT query that do not appear as frequently as the WHERE clause (and a function). In each case we will be able to answer if each particular clause (or if the function) is represented or not, and how. For this queries questions b), f), g), i) and j) also apply.

    5. DELETE FROM ENROLLMENT WHERE STUDENT_ID = ‘S1002’ AND COURSE_NO = ‘ART103A’;

Wich this query we will be able to check if the participant represents (and how) the fact that it is a DELETE. We can apply questions b), d), f), g), i) and j) to this case.

    6. INSERT INTO ENROLLMENT (COURSE_NO, STUDENT_ID) VALUES (‘MTH101B’, ‘S1002’);

Wich this query we will be able to check if the participant represents (and how) the fact that it is an INSERT. We can apply questions b), d), f), i) and j) to this case.

    7. UPDATE ENROLLMENT SET GRADE = ‘A+’ WHERE STUDENT_ID = ‘S1001’ AND COURSE_NO = ‘HST205A’;

Wich this query we will be able to check if the participant represents (and how) the fact that it is an UPDATE. We can apply questions b), d), f), g), i) and j) to this case.

    8. SELECT STUDENT_NAME, SCHEDULE, ROOM FROM (STUDENT INNER JOIN ENROLLMENT ON ENROLLMENT.STUDENT_ID = STUDENT.STUDENT_ID) INNER JOIN CLASS ON CLASS.COURSE_NO = ENROLLMENT.COURSE_NO;

In this example we will be able to check if the participants mark the attributes involved on the INNER JOINs and how. We can also check if the participants represent the order of the INNER JOINs. Questions a), b), c), i) and j) can be applied to this case as well (replacing attributes by relationships when necesary).

    9. SELECT STUDENT_NAME FROM STUDENT WHERE STUDENT_ID IN (SELECT STUDENT_ID FROM ENROLLMENT WHERE COURSE_NO IN (SELECT COURSE_NO FROM CLASS WHERE FACULTY_ID = ‘F110’));

In this example we will be able to check if the participants represent the order of the SELECTs. Questions a), b), c), d), i) and j) can be applied to this case as well (replacing attributes by relationships when necesary).

    10. SELECT COPY1.COURSE_NO, COPY1.SCHEDULE, COPY1.ROOM, COPY2.COURSE_NO, COPY2.SCHEDULE FROM CLASS AS COPY1 INNER JOIN CLASS AS COPY2 ON COPY1.ROOM = COPY2.ROOM WHERE COPY1.COURSE_NO > COPY2.COURSE_NO;

In this example we will be able to check if the participants mark the attribute involved on the INNER JOINs and how. It will be interesting to check if the notation used changes because the INNER JOIN is between a table and itself. Questions a), b), c), d), e), g), i) and j) can be applied to this case as well (replacing attributes by relationships when necesary).

It is important to note that the general questions that we are traing to answer are:

  • How do programers represent runtime information from SQL queries into ERDs?
  • Which part of the whole ERD is represented?
  • In which order the diagrams are painted?

For this we will need at the end of the experiments to ask the participants: whether the things they didn’t represent were discarted because:

  • the participant didn’t though about it
  • the participant though about it but there was already to many information on the diagram
  • the participant though about it but consider that it was not important
  • the participant though about it but didn’t know how to represent it

In a future post I will try to select other data domains (not similar to this school domain but with a similar structure to reproduce these queries with other column names).

3 responses to this post.

  1. Very good — next question from me is, in what order would you present these examples? #3 is a big jump up from #1 and #2…

    Responder

  2. Maybe 1, 2, 6, 5, 7, 4, 9, 6, 8, 10, but I have to think how to merge these examples with their equivalents (probably the insertion of a few equivalent examples/not the same for everyone will be enough to check if the results do not change when other tables are used).

    Responder

  3. […] About « Small Examples: tables + SQL II […]

    Responder

Responder

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s

A %d blogueros les gusta esto: