Bases de datos

1. SELECT *FROM STUDENTS WHERE UPPER(major)=’History’;

2. SELECT *FROM STUDENTS ORDER BY major ASC, last_name DESC;

3. SELECT major, COUNT(major) FROMSTUDENTS GROUP BY DESC;

4. SELECT description FROM STUDENTS a, REGISTERED_STUDENTS b, CLASSES c WHERE a.id=b.student_id AND b.departament=c.departament ANDb.course=c..course AND UPPER(a.first_name)=’Margaret’ AND UPPER(a.last_name)=’Mason’;

5. SELECT AVG(number_seats) FROM ROOMS GROUP BY building;

6. SELECT a.building,a.description, a.room_id, b.description FROM ROOMS a, CLASSES b WHERE a.room_id=b.room_id;

7. SELECT COUNT(student_id), grade FROM REGISTERED_STUDENTS GROUP BY grade;

8.SELECT COUNT(student_id), departament FROM REGISTERED_STUDENTS GROUP BY departament;

9. SELECT b.departament, b.course, c.description, a.id, a.first_name, a.last_nameFROM STUDENTS a, REGISTERED_STUDENTS b, CLASSES C WHERE a.id=b.student_id AND b.course=c.course AND b.departament=c.departament AND UPPER(a.major)=’Music’;

10. SELECTbuilding, room_number, description FROM ROOMS WHERE number_seats=(SELECT MAX(number_seats) FROM ROOMS);

11. SELECT a.DESCRIPTION FROM CLASSES a, ROOMS b WHEREb.NUMBER_SEATS=(SELECT MIN (b.max_students) FROM ROOMS AND b.DEPARTMENT=(SELECT a.DEPARTMENT FROM CLASSES WHERE a.DEPARTMENT='CS'));

12. SELECT a.description, COUNT(*)max_students FROM CLASSES c, ROOMS a WHERE room_number=1 AND c.room_id=a.room_id GROUP BY a.description ORDER BY max_students DESC;

TALLER DE BASES DE DATOS II

DAVIDALEJANDRO CLAVIJO ECHEVERRI
ANDRES FELIPE ALVAREZ

INSTITUCIÓN UNIVERSITARIA ANTONIO JOSÉ CAMACHO
FACULTAD DE INGENIERÍA
ÁREA TECNOLOGÍA EN SISTEMAS
SANTIAGO DE CALI
2010