Small Examples: tables + SQL III

Moving into the horse racing world, here are 10 queries equivalent to the ones in the previous post.

  1. SELECT HORSE_NAME, BREED FROM HORSE WHERE COLOR = ‘Brown’ AND AGE > 6;
  2. SELECT * FROM HORSE;
  3. SELECT RACE_ID, COUNT(*) FROM PARTICIPATION GROUP BY RACE_ID HAVING COUNT(*) BETWEEN 2 AND 3;
  4. SELECT DISTINCT JOCKEY_ID, TIME FROM PARTICIPATION WHERE TIME IS NOT NULL ORDER BY JOCKEY_ID ASC, TIME DESC;
  5. DELETE FROM PARTICIPATION WHERE JOCKEY_ID = ‘J3’ AND RACE_ID = ‘R4’;
  6. INSERT INTO PARTICIPATION (JOCKEY_ID, RACE_ID) VALUES (‘J2’, ‘R4’);
  7. UPDATE PARTICIPATION SET TIME = ‘1:36.20’ WHERE JOCKEY_ID = ‘J1’ AND RACE_ID = ‘R1’;
  8. SELECT JOCKEY_NAME, YEAR, DISTANCE FROM (JOCKEY INNER JOIN PARTICIPATION ON PARTICIPATION.JOCKEY_ID = JOCKEY.JOCKEY_ID) INNER JOIN RACE ON RACE.RACE_ID = PARTICIPATION.RACE_ID;
  9. SELECT JOCKEY_NAME FROM JOCKEY WHERE JOCKEY_ID IN (SELECT JOCKEY_ID FROM PARTICIPATION WHERE HORSE_ID IN (SELECT HORSE_ID FROM HORSE WHERE TRAINER_ID = ‘T2’));
  10. SELECT COPY1.RACE_ID, COPY1.DISTANCE, COPY1.YEAR, COPY2.RACE_ID, COPY2.DISTANCE FROM RACE AS COPY1 INNER JOIN RACE AS COPY2 ON COPY1.YEAR = COPY2.YEAR WHERE COPY1.RACE_ID > COPY2.RACE_ID;

See:

2 responses to this post.

  1. Um… HOCKEY_NAME?

    Responder

  2. Oops it is with J.

    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: