Centro de Enseñanza Técnica
Industrial
1º Parcial
Práctica ORDER GROUP LIMIT IN EXISTS
Elaborado por el alumno:
Real Haro David Kaleb
Registro: 20300663
Grupo: 6C1
Carrera: Tecnólogo en Desarrollo de Software
Materia: Base de Datos II
Asesor:
Mtra. Karla Areli Isaac Rodríguez
2 de marzo de 2023
Real Haro David Kaleb 20300663 6C1
Programación Móvil I
Realiza las siguientes instrucciones SQL, observa el resultado.
Pega en un editor de texto la instrucción junto con el resultado dado.
Realiza el .pdf con las especificaciones de la actividad en teams
1.-SELECT* FROM `alumnos` limit 5 OFFSET 15
MariaDB [ceti_6c1]> SELECT* FROM `alumno` limit 5 OFFSET 15;
+----------+-----------------+-----------+----------+------------+--------------------------------------------------
-+-----------+---------+
| Registro | Nombre | ApeP | ApeM | Celular | Domicilio
| Municipio | Carrera |
+----------+-----------------+-----------+----------+------------+--------------------------------------------------
-+-----------+---------+
| 20300696 | Isaac Daniel | Chavez | Mares | 3313048026 | La Condesa 1790
| 39 | 3|
| 20300697 | Diego | Romero | Corvera | 3338455252 | Paseo del bosque 1398
| 120 | 3|
| 20300705 | Daniel | Atilano | Romero | 3329229030 | Calle Laureles #2693
| 120 | 3|
| 20300799 | Jared Emiliano | Due?as | Flores | 3320301314 | Palermo. Cod.
Belmonte. Calle barol. Interior 226 | 120 | 3|
| 20300827 | Diego Emiliano | Gonzalez | Martinez | 3311925050 | Ca?ada 189, int 13
| 120 | 3|
+----------+-----------------+-----------+----------+------------+--------------------------------------------------
-+-----------+---------+
5 rows in set (0.001 sec)
MariaDB [ceti_6c1]>
2.-SELECTa. registro, a.nombre, avg(c.calificacion) AS PROMEDIO FROM alumnos as a
INNER JOIN calificacion AS c ON a.registro = c.registro
Real Haro David Kaleb 20300663 6C1
Programación Móvil I
MariaDB [ceti_6c1]> SELECT a.registro, a.nombre, avg(c.calificacion) AS PROMEDIO
FROM alumno as a INNER JOIN calificacion AS c ON a.registro = c.alumno;
+----------+--------+----------+
| registro | nombre | PROMEDIO |
+----------+--------+----------+
| 19100143 | Diego | 82.4697 |
+----------+--------+----------+
1 row in set (0.001 sec)
MariaDB [ceti_6c1]>
3.-SELECT a. registro, a.nombre, avg(c.calificacion) AS PROMEDIO FROM alumnos as a
INNER JOIN calificacion AS c ON a.registro = c.registro GROUP BY (a.registro)
MariaDB [ceti_6c1]> SELECT a.registro, a.nombre, avg(c.calificacion) AS PROMEDIO
FROM alumno as a INNER JOIN calificacion AS c ON a.registro = c.registro GROUP BY
(a.registro);
ERROR 1054 (42S22): Unknown column 'c.registro' in 'on clause'
MariaDB [ceti_6c1]> SELECT a.registro, a.nombre, avg(c.calificacion) AS PROMEDIO
FROM alumno as a INNER JOIN calificacion AS c ON a.registro = c.alumno GROUP BY
(a.registro);
+----------+---------------------+----------+
| registro | nombre | PROMEDIO |
+----------+---------------------+----------+
| 19100143 | Diego | 78.7778 |
| 19300107 | Misael | 84.0000 |
| 19300115 | Sergio Yael | 70.9444 |
Real Haro David Kaleb 20300663 6C1
Programación Móvil I
| 20100314 | Denahi | 72.5556 |
| 20100320 | Perla Dayane | 81.0556 |
| 20100338 | Osman Azael | 81.9444 |
| 20100348 | Gabriel Nahum | 57.3889 |
| 20100351 | Victor maximiliano | 75.3333 |
| 20300663 | David Kaleb | 94.1111 |
| 20300668 | Diego Antonio | 94.8889 |
| 20300674 | Fernando | 81.1111 |
| 20300680 | Daniel | 84.3889 |
| 20300685 | Gabriela Alejandra | 87.2222 |
| 20300692 | Francisco Emiliano | 82.8889 |
| 20300694 | Gustavo Miguel | 90.2778 |
| 20300696 | Isaac Daniel | 89.5556 |
| 20300697 | Diego | 92.7222 |
| 20300705 | Daniel | 86.0000 |
| 20300799 | Jared Emiliano | 82.3333 |
| 20300827 | Diego Emiliano | 86.4444 |
| 20300846 | Arely Jazm?n | 72.8889 |
| 20300849 | Melanie Astrid | 87.5000 |
+----------+---------------------+----------+
22 rows in set (0.027 sec)
MariaDB [ceti_6c1]>
Real Haro David Kaleb 20300663 6C1
Programación Móvil I
4.-SELECTa. registro, a.nombre, avg(c.calificacion) AS PROMEDIO FROM alumnos as a
INNER JOIN calificacion AS c ON a.registro = c.registro GROUP BY (a.registro) limit 5
MariaDB [ceti_6c1]> SELECT a.registro, a.nombre, avg(c.calificacion) AS PROMEDIO
FROM alumno as a INNER JOIN calificacion AS c ON a.registro = c.alumno GROUP BY
(a.registro) limit 5;
+----------+---------------+----------+
| registro | nombre | PROMEDIO |
+----------+---------------+----------+
| 19100143 | Diego | 78.7778 |
| 19300107 | Misael | 84.0000 |
| 19300115 | Sergio Yael | 70.9444 |
| 20100314 | Denahi | 72.5556 |
| 20100320 | Perla Dayane | 81.0556 |
+----------+---------------+----------+
5 rows in set (0.001 sec)
MariaDB [ceti_6c1]>
5.-SELECT a.registro, a.nombre, avg(c.calificacion) AS PROMEDIO FROM alumnos as a
INNER JOIN calificacion AS c ON a.registro= c.registro GROUP BY(a.registro) ORDER
BY(a.nombre)
MariaDB [ceti_6c1]> SELECT a.registro, a.nombre, avg(c.calificacion) AS PROMEDIO
FROM alumno as a INNER JOIN calificacion AS c ON a.registro= c.alumno GROUP
BY(a.registro) ORDER BY(a.nombre);
+----------+---------------------+----------+
| registro | nombre | PROMEDIO |
Real Haro David Kaleb 20300663 6C1
Programación Móvil I
+----------+---------------------+----------+
| 20300846 | Arely Jazm?n | 72.8889 |
| 20300705 | Daniel | 86.0000 |
| 20300680 | Daniel | 84.3889 |
| 20300663 | David Kaleb | 94.1111 |
| 20100314 | Denahi | 72.5556 |
| 20300697 | Diego | 92.7222 |
| 19100143 | Diego | 78.7778 |
| 20300668 | Diego Antonio | 94.8889 |
| 20300827 | Diego Emiliano | 86.4444 |
| 20300674 | Fernando | 81.1111 |
| 20300692 | Francisco Emiliano | 82.8889 |
| 20100348 | Gabriel Nahum | 57.3889 |
| 20300685 | Gabriela Alejandra | 87.2222 |
| 20300694 | Gustavo Miguel | 90.2778 |
| 20300696 | Isaac Daniel | 89.5556 |
| 20300799 | Jared Emiliano | 82.3333 |
| 20300849 | Melanie Astrid | 87.5000 |
| 19300107 | Misael | 84.0000 |
| 20100338 | Osman Azael | 81.9444 |
| 20100320 | Perla Dayane | 81.0556 |
| 19300115 | Sergio Yael | 70.9444 |
| 20100351 | Victor maximiliano | 75.3333 |
Real Haro David Kaleb 20300663 6C1
Programación Móvil I
+----------+---------------------+----------+
22 rows in set (0.011 sec)
MariaDB [ceti_6c1]>
6.-SELECT a. registro, a.nombre, avg(c.calificacion) AS PROMEDIO FROM alumnos as a
INNER JOIN calificacion AS c ON a.registro= c.registro GROUP BY(a.registro) ORDER
BY(a.nombre) LIMIT 5
MariaDB [ceti_6c1]> SELECT a.registro, a.nombre, avg(c.calificacion) AS PROMEDIO
FROM alumno as a INNER JOIN calificacion AS c ON a.registro= c.alumno GROUP
BY(a.registro) ORDER BY(a.nombre) LIMIT 5;
+----------+---------------+----------+
| registro | nombre | PROMEDIO |
+----------+---------------+----------+
| 20300846 | Arely Jazm?n | 72.8889 |
| 20300705 | Daniel | 86.0000 |
| 20300680 | Daniel | 84.3889 |
| 20300663 | David Kaleb | 94.1111 |
| 20100314 | Denahi | 72.5556 |
+----------+---------------+----------+
5 rows in set (0.001 sec)
MariaDB [ceti_6c1]>
7.-SELECT a. registro, a.nombre, avg(c.calificacion) AS PROMEDIO FROM alumnos as a
INNER JOIN calificacion AS c ON a.registro= c.registro GROUP BY(a.registro) ORDER
BY(a.nombre) LIMIT 5 OFFSET 3
Real Haro David Kaleb 20300663 6C1
Programación Móvil I
MariaDB [ceti_6c1]> SELECT a. registro, a.nombre, avg(c.calificacion) AS PROMEDIO
FROM alumno as a INNER JOIN calificacion AS c ON a.registro= c.alumno GROUP
BY(a.registro) ORDER BY(a.nombre) LIMIT 5 OFFSET 3;
+----------+---------------+----------+
| registro | nombre | PROMEDIO |
+----------+---------------+----------+
| 20300663 | David Kaleb | 94.1111 |
| 20100314 | Denahi | 72.5556 |
| 20300697 | Diego | 92.7222 |
| 19100143 | Diego | 78.7778 |
| 20300668 | Diego Antonio | 94.8889 |
+----------+---------------+----------+
5 rows in set (0.002 sec)
8.-SELECT nombre, num_mun FROM municipio WHERE nombre IN("ZAPOPAN",
"Guadalajara", "tonala")
MariaDB [ceti_6c1]> SELECT nombre, PK_Municipio FROM municipio WHERE nombre
IN("ZAPOPAN", "Guadalajara", "tonala");
+-------------+--------------+
| nombre | PK_Municipio |
+-------------+--------------+
| Guadalajara | 39 |
| Tonala | 101 |
| Zapopan | 120 |
+-------------+--------------+
Real Haro David Kaleb 20300663 6C1
Programación Móvil I
3 rows in set (0.001 sec)
MariaDB [ceti_6c1]>
9.-SELECT nombre, num_mun FROM municipio WHERE nombre NOT IN("ZAPOPAN",
"Guadalajara", "tonala")
MariaDB [ceti_6c1]> SELECT nombre, PK_Municipio FROM municipio WHERE nombre
NOT IN("ZAPOPAN", "Guadalajara", "tonala");
+-------------------------------+--------------+
| nombre | PK_Municipio |
+-------------------------------+--------------+
| Acatic | 1|
| Acatlan de Juarez | 2|
| Ahualulco de Mercado | 3|
| Amacueca | 4|
| Amatitan | 5|
| Ameca | 6|
| San Juanito de Escobedo | 7|
| Arandas | 8|
| El Arenal | 9|
| Atemajac de Brizuela | 10 |
| Atengo | 11 |
| Atenguillo | 12 |
| Atotonilco el Alto | 13 |
Real Haro David Kaleb 20300663 6C1
Programación Móvil I
| Atoyac | 14 |
| Autlan de Navarro | 15 |
| Ayotlan | 16 |
| Ayutla | 17 |
| La Barca | 18 |
| Bola?os | 19 |
| Cabo Corrientes | 20 |
| Casimiro Castillo | 21 |
| Cihuatlan | 22 |
| Zapotlan el Grande | 23 |
| Cocula | 24 |
| Colotlan | 25 |
| Concepcion de Buenos Aires | 26 |
| Cuautitlan de Garcia Barragan | 27 |
| Cuautla | 28 |
| Cuquio | 29 |
| Chapala | 30 |
| Chimaltitan | 31 |
| Chiquilistlan | 32 |
| Degollado | 33 |
| Ejutla | 34 |
| Encarnacion de Diaz | 35 |
| Etzatlan | 36 |
Real Haro David Kaleb 20300663 6C1
Programación Móvil I
| El Grullo | 37 |
| Guachinango | 38 |
| Hostotipaquillo | 40 |
| Huejucar | 41 |
| Huejuquilla el Alto | 42 |
| La Huerta | 43 |
| Ixtlahuacan de los Membrillos | 44 |
| Ixtlahuacan del Rio | 45 |
| Jalostotitlan | 46 |
| Jamay | 47 |
| Jesus Maria | 48 |
| Jilotlan de los Dolores | 49 |
| Jocotepec | 50 |
| Juanacatlan | 51 |
| Juchitlan | 52 |
| Lagos de Moreno | 53 |
| El Limon | 54 |
| Magdalena | 55 |
| Santa Maria del Oro | 56 |
| La Manzanilla de la Paz | 57 |
| Mascota | 58 |
| Mazamitla | 59 |
| Mexticacan | 60 |
Real Haro David Kaleb 20300663 6C1
Programación Móvil I
| Mezquitic | 61 |
| Mixtlan | 62 |
| Ocotlan | 63 |
| Ojuelos de Jalisco | 64 |
| Pihuamo | 65 |
| Poncitlan | 66 |
| Puerto Vallarta | 67 |
| Villa Purificacion | 68 |
| Quitupan | 69 |
| El Salto | 70 |
| San Cristobal de la Barranca | 71 |
| San Diego de Alejandria | 72 |
| San Juan de los Lagos | 73 |
| San Julian | 74 |
| San Marcos | 75 |
| San Martin de Bola?os | 76 |
| San Martin Hidalgo | 77 |
| San Miguel el Alto | 78 |
| Gomez Farias | 79 |
| San Sebastian del Oeste | 80 |
| Santa Maria de los angeles | 81 |
| Sayula | 82 |
| Tala | 83 |
Real Haro David Kaleb 20300663 6C1
Programación Móvil I
| Talpa de Allende | 84 |
| Tamazula de Gordiano | 85 |
| Tapalpa | 86 |
| Tecalitlan | 87 |
| Techaluta de Montenegro | 88 |
| Tecolotlan | 89 |
| Tenamaxtlan | 90 |
| Teocaltiche | 91 |
| Teocuitatlan de Corona | 92 |
| Tepatitlan de Morelos | 93 |
| Tequila | 94 |
| Teuchitlan | 95 |
| Tizapan el Alto | 96 |
| Tlajomulco de Zu?iga | 97 |
| San Pedro Tlaquepaque | 98 |
| Toliman | 99 |
| Tomatlan | 100 |
| Tonaya | 102 |
| Tonila | 103 |
| Totatiche | 104 |
| Tototlan | 105 |
| Tuxcacuesco | 106 |
| Tuxcueca | 107 |
Real Haro David Kaleb 20300663 6C1
Programación Móvil I
| Tuxpan | 108 |
| Union de San Antonio | 109 |
| Union de Tula | 110 |
| Valle de Guadalupe | 111 |
| Valle de Juarez | 112 |
| San Gabriel | 113 |
| Villa Corona | 114 |
| Villa Guerrero | 115 |
| Villa Hidalgo | 116 |
| Ca?adas de Obregon | 117 |
| Yahualica de Gonzalez Gallo | 118 |
| Zacoalco de Torres | 119 |
| Zapotiltic | 121 |
| Zapotitlan de Vadillo | 122 |
| Zapotlan del Rey | 123 |
| Zapotlanejo | 124 |
| San Ignacio Cerro Gordo | 125 |
+-------------------------------+--------------+
122 rows in set (0.001 sec)
MariaDB [ceti_6c1]>
Real Haro David Kaleb 20300663 6C1
Programación Móvil I
10.-SELECT REGISTRO, NOMBRE FROM alumnos AS A WHERE num_mun IN(SELECT
DISTINCT(num_mun) FROM municipio AS m WHERE a.num_mun=m.num_mun AND
m.nombre='Zapopan');
MariaDB [ceti_6c1]> SELECT REGISTRO, NOMBRE FROM alumno AS A WHERE
Municipio IN(SELECT DISTINCT(PK_Municipio) FROM municipio AS m WHERE
a.Municipio=m.PK_Municipio AND m.nombre='Zapopan');
+----------+---------------------+
| REGISTRO | NOMBRE |
+----------+---------------------+
| 19100143 | Diego |
| 19300107 | Misael |
| 19300115 | Sergio Yael |
| 20100320 | Perla Dayane |
| 20100338 | Osman Azael |
| 20100351 | Victor maximiliano |
| 20300674 | Fernando |
| 20300680 | Daniel |
| 20300692 | Francisco Emiliano |
| 20300694 | Gustavo Miguel |
| 20300697 | Diego |
| 20300705 | Daniel |
| 20300799 | Jared Emiliano |
| 20300827 | Diego Emiliano |
| 20300846 | Arely Jazm?n |
+----------+---------------------+
Real Haro David Kaleb 20300663 6C1
Programación Móvil I
15 rows in set (0.016 sec)
MariaDB [ceti_6c1]>
11.-SELECT REGISTRO, NOMBRE FROM alumnos AS A WHERE num_mun NOT
IN(SELECT DISTINCT(num_mun) FROM municipio AS m WHERE
a.num_mun=m.num_mun AND m.nombre='Zapopan');
MariaDB [ceti_6c1]> SELECT REGISTRO, NOMBRE FROM alumno AS A WHERE
Municipio NOT IN(SELECT DISTINCT(PK_Municipio) FROM municipio AS m WHERE
a.Municipio=PK_Municipio AND m.nombre='Zapopan');
+----------+---------------------+
| REGISTRO | NOMBRE |
+----------+---------------------+
| 20100314 | Denahi |
| 20100348 | Gabriel Nahum |
| 20300663 | David Kaleb |
| 20300668 | Diego Antonio |
| 20300685 | Gabriela Alejandra |
| 20300696 | Isaac Daniel |
| 20300849 | Melanie Astrid |
+----------+---------------------+
7 rows in set (0.041 sec)
MariaDB [ceti_6c1]>
Real Haro David Kaleb 20300663 6C1
Programación Móvil I
12.-SELECT REGISTRO, NOMBRE FROM alumnos AS A WHERE EXISTS (SELECT
*FROM municipio AS m WHERE a.num_mun=m.num_mun AND m.nombre='Zapopan');
MariaDB [ceti_6c1]> SELECT REGISTRO, NOMBRE FROM alumno AS A WHERE
EXISTS (SELECT *FROM municipio AS m WHERE a.Municipio=m.PK_Municipio AND
m.nombre='Zapopan');
+----------+---------------------+
| REGISTRO | NOMBRE |
+----------+---------------------+
| 19100143 | Diego |
| 19300107 | Misael |
| 19300115 | Sergio Yael |
| 20100320 | Perla Dayane |
| 20100338 | Osman Azael |
| 20100351 | Victor maximiliano |
| 20300674 | Fernando |
| 20300680 | Daniel |
| 20300692 | Francisco Emiliano |
| 20300694 | Gustavo Miguel |
| 20300697 | Diego |
| 20300705 | Daniel |
| 20300799 | Jared Emiliano |
| 20300827 | Diego Emiliano |
| 20300846 | Arely Jazm?n |
+----------+---------------------+
Real Haro David Kaleb 20300663 6C1
Programación Móvil I
15 rows in set (0.001 sec)
MariaDB [ceti_6c1]>
13.-SELECT REGISTRO, NOMBRE FROM alumnos AS A WHERE NOT EXISTS
(SELECT *FROM municipio AS m WHERE a.num_mun=m.num_mun AND
m.nombre='Zapopan');
MariaDB [ceti_6c1]> SELECT REGISTRO, NOMBRE FROM alumno AS A WHERE NOT
EXISTS (SELECT *FROM municipio AS m WHERE a.Municipio=m.PK_Municipio AND
m.nombre='Zapopan');
+----------+---------------------+
| REGISTRO | NOMBRE |
+----------+---------------------+
| 20100314 | Denahi |
| 20100348 | Gabriel Nahum |
| 20300663 | David Kaleb |
| 20300668 | Diego Antonio |
| 20300685 | Gabriela Alejandra |
| 20300696 | Isaac Daniel |
| 20300849 | Melanie Astrid |
+----------+---------------------+
7 rows in set (0.001 sec)
MariaDB [ceti_6c1]>
