Small Examples: tables + SQL

The easy part about the creation of small examples to see how students annotate ERDs with runtime information is that a lot of simple examples can be found on introductions to SQL or other presentations about database management systems. The following examples were extracted from a presentation about SQL.

STUDENT
STUDENT_ID STUDENT_NAME MAJOR CREDITS
S1015 Jones, Mary MATH 42
S1002 Chin, Ann MATH 36
S1013 McCarthy, Owen MATH 9
S1001 Smith, Tom HISTORY 90
S1010 Burns, Edward ART 63
S1020 Rivera, Jane CSC 15
    1. SELECT STUDENT_NAME, STUDENT_ID, CREDITS FROM STUDENT WHERE MAJOR = ‘MATH’;
    2. SELECT STUDENT_NAME FROM STUDENT WHERE MAJOR = ‘MATH’ AND CREDITS > 30;
    3. SELECT STUDENT_ID, ‘NUMBER OF COURSES =’, CREDITS/3 FROM STUDENT;
FACULTY
FACULTY_ID FACULTY_NAME DEPARTMENT RANK
F105 Tanaka CSC Instructor
F221 Smith CSC Professor
F202 Smith HISTORY
F101 Adams ART
F110 Byrne MATH
    4. SELECT * FROM FACULTY WHERE DEPARTMENT = ‘CSC’;
    5. SELECT FACULTY_NAME, FACULTY_ID FROM FACULTY ORDER BY FACULTY_NAME;
    6. SELECT COUNT(DISTINCT DEPARTMENT) FROM FACULTY;
ENROLLMENT
COURSE_NO STUDENT_ID GRADE
ART103A S1001
CSC201A S1020
CSC201A S1002
ART103A S1010
ART103A S1002
MTH101B S1020 A
HST205A S1001
MTH103C S1010
MTH103C S1002 B
    7. SELECT COURSE_NO FROM ENROLLMENT;
    8. SELECT DISTINCT COURSE_NO FROM ENROLLMENT;
    9. SELECT COUNT(DISTINCT STUDENT_ID) FROM ENROLLMENT WHERE COURSE_NO = ‘ART103A’;
    10. SELECT COURSE_NO, COUNT(*) FROM ENROLLMENT GROUP BY COURSE_NO;
    11. SELECT COURSE_NO FROM ENROLLMENT GROUP BY COURSE_NO HAVING COUNT(*) < 3;
    12. SELECT COURSE_NO, STUDENT_ID FROM ENROLLMENT WHERE GRADE IS NULL;
CLASS
COURSE_NO FACULTY_ID SCHEDULE ROOM COURSE_TITLE
MTH101B F110 MTUTH9 H225
MTH103C F110 MWR11 H225
ART103A F101 MWF9 H221
HST205A F202 H221
CSC201A TUTHF10 M110
CSC203A M110
    13. SELECT * FROM CLASS WHERE COURSE_NO LIKE ‘MTH%’;
    14. SELECT COPY1.COURSE_NO, COPY1.SCHEDULE, COPY1.ROOM, COPY2.COURSE_NO, COPY2.SCHEDULE FROM CLASS COPY1, CLASS COPY2 WHERE COPY1.ROOM = COPY2.ROOM AND COPY1.COURSE_NO > COPY2.COURSE_NO;
    15. SELECT ENROLLMENT.STUDENT_ID, STUDENT_NAME FROM STUDENT, ENROLLMENT WHERE COURSE_NO = ‘ART103A’ AND ENROLLMENT.STUDENT_ID = STUDENT.STUDENT_ID;
    16. SELECT STUDENT_ID, GRADE FROM CLASS, ENROLLMENT WHERE FACULTY_ID = ‘F110’ AND CLASS.COURSE_NO = ENROLLMENT.COURSE_NO ORDER BY STUDENT_ID ASC;
    17. SELECT ENROLLMENT.COURSE_NO, STUDENT_NAME, MAJOR FROM CLASS, ENROLLMENT, STUDENT WHERE FACULTY_ID = ‘F110’ AND CLASS.COURSE_NO = ENROLLMENT.COURSE_NO AND ENROLLMENT.STUDENT_ID = STUDENT.STUDENT_ID;
    18. SELECT STUDENT_ID, STUDENT_NAME, MAJOR, FACULTY_ID, FACULTY_NAME, DEPARTMENT FROM STUDENT, FACULTY WHERE STUDENT.MAJOR != FACULTY.DEPARTMENT;
    19. SELECT STUDENT_ID, STUDENT_NAME, MAJOR, FACULTY_ID, FACULTY_NAME, DEPARTMENT FROM STUDENT, FACULTY WHERE STUDENT.MAJOR != FACULTY.DEPARTMENT; –> Note that this one is an unusual request so maybe it will bring more confusion that knowledge in an experiment.
    20. SELECT COURSE_NO FROM CLASS WHERE FACULTY_ID = (SELECT FACULTY_ID FROM FACULTY WHERE FACULTY_NAME = ‘Byrne’ AND DEPARTMENT = ‘MATH’);
    21. SELECT FACULTY_ID, FACULTY_NAME FROM FACULTY WHERE FACULTY_ID IN (SELECT FACULTY_ID FROM CLASS WHERE ROOM = ‘H221’);
    22. SELECT STUDENT_NAME, STUDENT_ID FROM STUDENT WHERE STUDENT_ID IN (SELECT STUDENT_ID FROM ENROLLMENT WHERE COURSE_NO IN (SELECT COURSE_NO FROM CLASS WHERE FACULTY_ID = ‘F110’)) ORDER BY STUDENT_NAME ASC;
    23. SELECT STUDENT_NAME FROM STUDENT WHERE EXISTS (SELECT * FROM ENROLLMENT WHERE STUDENT.STUDENT_ID = ENROLLMENT.STUDENT_ID AND COURSE_NO = ‘CSC201A’);
    24. SELECT STUDENT_NAME FROM STUDENT WHERE NOT EXISTS (SELECT * FROM ENROLLMENT WHERE STUDENT.STUDENT_ID = ENROLLMENT.STUDENT_ID AND COURSE_NO = ‘CSC201A’);

Going back to the Structured Query Language post, lets add a few more SQL queries that are not similar to the previous examples.

    25. DELETE FROM ENROLLMENT WHERE STUDENT_ID = ‘S1002’ AND COURSE_NO = ‘ART103A’;
    26. INSERT INTO FACULTY VALUES (‘F22’, ‘Wilson’, ‘CSC’, ‘Assistant Professor’);
    27. INSERT INTO ENROLLMENT (COURSE_NO, STUDENT_ID) VALUES (‘MTH101B’, ‘S1002’);
    28. UPDATE ENROLLMENT SET GRADE = ‘A+’ WHERE STUDENT_ID = ‘S1001’ AND COURSE_NO = ‘HST205A’;
    29. SELECT STUDENT_NAME, STUDENT_ID, CREDITS FROM STUDENT WHERE CREDIT BETWEEN 0 AND 30;
    30. SELECT TOP 10 PERCENT STUDENT_NAME, STUDENT_ID, CREDITS FROM STUDENT ORDER BY CREDITS DESC;

See also:

3 responses to this post.

  1. 1. You may find some useful material at http://databaseanswers.org/data_models/index.htm.

    2. Purists may want you to use “INNER JOIN” instead of “,” to do joins — we got complaints about that when writing our CS-1 book🙂

    3. Is “SELECT TOP 10 PERCENT” standard SQL?

    Responder

  2. 3. It seams it is not — “Note: Not all database systems support the TOP clause.”

    Responder

  3. […] 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 […]

    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: