Microsoft Windows [Versión 10.0.26100.3775] (c) Microsoft Corporation. Todos los derechos reservados. C:\Users\Yeison>cd/xampp/mysql/bin C:\xampp\mysql\bin>mysql -uroot -p Enter password: Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 11 Server version: 10.4.32-MariaDB mariadb.org binary distribution Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> CREATE DATABASE CURSOS; Query OK, 1 row affected (0.013 sec) MariaDB [(none)]> USE CURSOS; Database changed MariaDB [CURSOS]> CREATE TABLE estudiante ( -> identificacion VARCHAR(10) PRIMARY KEY, -> nombre VARCHAR(30), -> estadocivil ENUM('soltero', 'casado', 'unión libre', 'divorciado', 'viudo'), -> fechamatricula DATE -> ); Query OK, 0 rows affected (0.052 sec) MariaDB [CURSOS]> DESCRIBE ESTUDIANTE; +----------------+-------------------------------------------------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------------+-------------------------------------------------------------+------+-----+---------+-------+ | identificacion | varchar(10) | NO | PRI | NULL | | | nombre | varchar(30) | YES | | NULL | | | estadocivil | enum('soltero','casado','unión libre','divorciado','viudo') | YES | | NULL | | | fechamatricula | date | YES | | NULL | | +----------------+-------------------------------------------------------------+------+-----+---------+-------+ 4 rows in set (0.037 sec) MariaDB [CURSOS]> INSERT INTO estudiante (identificacion, nombre, estadocivil, fechamatricula) VALUES -> ('2040', 'Elizabeth Cano', 'casado', '2013-01-01'), -> ('2140', 'Denis Rico', 'divorciado', '2013-02-18'), -> ('2341', 'Alfredo Lara', 'casado', '2014-06-20'), -> ('1840', 'Armando Casas', 'viudo', '2014-01-28'), -> ('2044', 'Eliodoro Puerta', 'casado', '2015-07-20'), -> ('2314', 'Mariana Salinas', 'casado', '2016-06-06'), -> ('2318', 'Benito Céspedes', 'casado', '2016-06-30'), -> ('2045', 'Roberto Jiménez', 'soltero', '2017-01-30'); Query OK, 8 rows affected (0.005 sec) Records: 8 Duplicates: 0 Warnings: 0 MariaDB [CURSOS]> SELECT * FROM ESTUDIANTE; +----------------+-----------------+-------------+----------------+ | identificacion | nombre | estadocivil | fechamatricula | +----------------+-----------------+-------------+----------------+ | 1840 | Armando Casas | viudo | 2014-01-28 | | 2040 | Elizabeth Cano | casado | 2013-01-01 | | 2044 | Eliodoro Puerta | casado | 2015-07-20 | | 2045 | Roberto Jiménez | soltero | 2017-01-30 | | 2140 | Denis Rico | divorciado | 2013-02-18 | | 2314 | Mariana Salinas | casado | 2016-06-06 | | 2318 | Benito Céspedes | casado | 2016-06-30 | | 2341 | Alfredo Lara | casado | 2014-06-20 | +----------------+-----------------+-------------+----------------+ 8 rows in set (0.003 sec) MariaDB [CURSOS]> SELECT YEAR(fechamatricula) AS anio_ingreso, COUNT(*) AS cantidad_alumnos -> FROM estudiante -> GROUP BY anio_ingreso; +--------------+------------------+ | anio_ingreso | cantidad_alumnos | +--------------+------------------+ | 2013 | 2 | | 2014 | 2 | | 2015 | 1 | | 2016 | 2 | | 2017 | 1 | +--------------+------------------+ 5 rows in set (0.004 sec) MariaDB [CURSOS]> CREATE TABLE registrocursos AS -> SELECT YEAR(fechamatricula) AS anio_ingreso, COUNT(*) AS cantidad_alumnos -> FROM estudiante -> GROUP BY anio_ingreso; Query OK, 5 rows affected (0.022 sec) Records: 5 Duplicates: 0 Warnings: 0 MariaDB [CURSOS]> SELECT * FROM registrocursos; +--------------+------------------+ | anio_ingreso | cantidad_alumnos | +--------------+------------------+ | 2013 | 2 | | 2014 | 2 | | 2015 | 1 | | 2016 | 2 | | 2017 | 1 | +--------------+------------------+ 5 rows in set (0.001 sec) MariaDB [CURSOS]> SHOW TABLES; +------------------+ | Tables_in_cursos | +------------------+ | estudiante | | registrocursos | +------------------+ 2 rows in set (0.002 sec) MariaDB [CURSOS]> CREATE TABLE planilla ( -> id INT AUTO_INCREMENT PRIMARY KEY, -> carnet VARCHAR(12), -> nombre VARCHAR(30), -> nota DECIMAL(4,2) UNSIGNED -> ); Query OK, 0 rows affected (0.017 sec) MariaDB [CURSOS]> describe planilla; +--------+-----------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------+-----------------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | carnet | varchar(12) | YES | | NULL | | | nombre | varchar(30) | YES | | NULL | | | nota | decimal(4,2) unsigned | YES | | NULL | | +--------+-----------------------+------+-----+---------+----------------+ 4 rows in set (0.023 sec) MariaDB [CURSOS]> INSERT INTO planilla (carnet, nombre, nota) VALUES -> ('010', 'Soledad Ospina', 4.0), -> ('011', 'Marta Salazar', 1.5), -> ('012', 'Margarita Sol', 1.5), -> ('013', 'Fabian Juda', 4.0), -> ('010', 'Soledad Ospina', 2.5), -> ('011', 'Marta Salazar', 1.0), -> ('012', 'Margarita Sol', 5.0), -> ('013', 'Fabian Juda', 4.5), -> ('010', 'Soledad Ospina', 2.0), -> ('010', 'Soledad Ospina', 3.8), -> ('011', 'Marta Salazar', 3.8), -> ('013', 'Fabian Juda', 5.0); Query OK, 12 rows affected (0.005 sec) Records: 12 Duplicates: 0 Warnings: 0 MariaDB [CURSOS]> select * from planilla; +----+--------+----------------+------+ | id | carnet | nombre | nota | +----+--------+----------------+------+ | 1 | 010 | Soledad Ospina | 4.00 | | 2 | 011 | Marta Salazar | 1.50 | | 3 | 012 | Margarita Sol | 1.50 | | 4 | 013 | Fabian Juda | 4.00 | | 5 | 010 | Soledad Ospina | 2.50 | | 6 | 011 | Marta Salazar | 1.00 | | 7 | 012 | Margarita Sol | 5.00 | | 8 | 013 | Fabian Juda | 4.50 | | 9 | 010 | Soledad Ospina | 2.00 | | 10 | 010 | Soledad Ospina | 3.80 | | 11 | 011 | Marta Salazar | 3.80 | | 12 | 013 | Fabian Juda | 5.00 | +----+--------+----------------+------+ 12 rows in set (0.001 sec) MariaDB [CURSOS]> CREATE TABLE promedio AS -> SELECT carnet, AVG(nota) AS promedio -> FROM planilla -> GROUP BY carnet; Query OK, 4 rows affected (0.018 sec) Records: 4 Duplicates: 0 Warnings: 0 MariaDB [CURSOS]> select * frome promedio; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'frome promedio' at line 1 MariaDB [CURSOS]> SELECT p.carnet, pl.nombre -> FROM promedio p -> JOIN planilla pl ON p.carnet = pl.carnet -> WHERE p.promedio >= 4.0 -> GROUP BY p.carnet, pl.nombre; +--------+-------------+ | carnet | nombre | +--------+-------------+ | 013 | Fabian Juda | +--------+-------------+ 1 row in set (0.006 sec) MariaDB [CURSOS]> SELECT p.carnet, pl.nombre -> FROM promedio p -> JOIN planilla pl ON p.carnet = pl.carnet -> WHERE p.promedio >= 4.0 -> GROUP BY p.carnet, pl.nombre; +--------+-------------+ | carnet | nombre | +--------+-------------+ | 013 | Fabian Juda | +--------+-------------+ 1 row in set (0.001 sec) MariaDB [CURSOS]> CREATE TABLE alumno_aprobado AS -> SELECT p.carnet, pl.nombre -> FROM promedio p -> JOIN planilla pl ON p.carnet = pl.carnet -> WHERE p.promedio >= 4.0 -> GROUP BY p.carnet, pl.nombre; Query OK, 1 row affected (0.017 sec) Records: 1 Duplicates: 0 Warnings: 0 MariaDB [CURSOS]> select * from promedio; +--------+----------+ | carnet | promedio | +--------+----------+ | 010 | 3.075000 | | 011 | 2.100000 | | 012 | 3.250000 | | 013 | 4.500000 | +--------+----------+ 4 rows in set (0.001 sec) MariaDB [CURSOS]> SELECT * FROM alumno_aprobado; +--------+-------------+ | carnet | nombre | +--------+-------------+ | 013 | Fabian Juda | +--------+-------------+ 1 row in set (0.001 sec) MariaDB [CURSOS]> CREATE TABLE porcentaje_calificacion ( -> codpor VARCHAR(5) NOT NULL PRIMARY KEY, -> descrip VARCHAR(30) NOT NULL -> ); Query OK, 0 rows affected (0.015 sec) MariaDB [CURSOS]> describe porcentaje_calificacion; +---------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+-------------+------+-----+---------+-------+ | codpor | varchar(5) | NO | PRI | NULL | | | descrip | varchar(30) | NO | | NULL | | +---------+-------------+------+-----+---------+-------+ 2 rows in set (0.022 sec) MariaDB [CURSOS]> INSERT INTO porcentaje_calificacion (codpor, descrip) VALUES -> ('01', 'Parcial 1'), -> ('02', 'Parcial 2'), -> ('03', 'Seguimiento'), -> ('04', 'Parcial final'); Query OK, 4 rows affected (0.004 sec) Records: 4 Duplicates: 0 Warnings: 0 MariaDB [CURSOS]> select porcentaje_calificacion; ERROR 1054 (42S22): Unknown column 'porcentaje_calificacion' in 'field list' MariaDB [CURSOS]> select * from porcentaje_calificacion; +--------+---------------+ | codpor | descrip | +--------+---------------+ | 01 | Parcial 1 | | 02 | Parcial 2 | | 03 | Seguimiento | | 04 | Parcial final | +--------+---------------+ 4 rows in set (0.001 sec) MariaDB [CURSOS]> CREATE TABLE planilla1 ( -> carnet VARCHAR(12), -> nombre VARCHAR(30), -> notapromedio DECIMAL(4,2) UNSIGNED, -> codpor VARCHAR(5), -> FOREIGN KEY (codpor) REFERENCES porcentaje_calificacion(codpor) -> ); Query OK, 0 rows affected (0.028 sec) MariaDB [CURSOS]> INSERT INTO planilla1 (carnet, nombre, notapromedio, codpor) VALUES -> ('010', 'Soledad Ospina', 4.0, '01'), -- Parcial 1 -> ('010', 'Soledad Ospina', 3.0, '02'), -- Parcial 2 -> ('010', 'Soledad Ospina', 3.5, '03'), -- Seguimiento -> ('010', 'Soledad Ospina', 4.2, '04'); -- Parcial final Query OK, 4 rows affected (0.004 sec) Records: 4 Duplicates: 0 Warnings: 0 MariaDB [CURSOS]> select * from platilla1; ERROR 1146 (42S02): Table 'cursos.platilla1' doesn't exist MariaDB [CURSOS]> select * from planilla1; +--------+----------------+--------------+--------+ | carnet | nombre | notapromedio | codpor | +--------+----------------+--------------+--------+ | 010 | Soledad Ospina | 4.00 | 01 | | 010 | Soledad Ospina | 3.00 | 02 | | 010 | Soledad Ospina | 3.50 | 03 | | 010 | Soledad Ospina | 4.20 | 04 | +--------+----------------+--------------+--------+ 4 rows in set (0.001 sec) MariaDB [CURSOS]> INSERT INTO planilla1 (carnet, nombre, notapromedio, codpor) -> SELECT '005', 'Edilberto Parra', 3.8, codpor -> FROM porcentaje_calificacion -> WHERE descrip = 'Parcial final'; Query OK, 1 row affected (0.003 sec) Records: 1 Duplicates: 0 Warnings: 0 MariaDB [CURSOS]> SELECT * FROM planilla1 -> WHERE carnet = '005'; +--------+-----------------+--------------+--------+ | carnet | nombre | notapromedio | codpor | +--------+-----------------+--------------+--------+ | 005 | Edilberto Parra | 3.80 | 04 | +--------+-----------------+--------------+--------+ 1 row in set (0.001 sec) MariaDB [CURSOS]>