No tienes acceso a esta clase

隆Contin煤a aprendiendo! 脷nete y comienza a potenciar tu carrera

Tipos de columnas usando / Creaci贸n de la tabla clientes

10/24
Recursos

Aportes 403

Preguntas 55

Ordenar por:

驴Quieres ver m谩s aportes, preguntas y respuestas de la comunidad?

o inicia sesi贸n.

  1. EL no colocar AUTO_INCREMENT a la columna que es PRIMARY KEY simplemente vuelve el proceso de asignar id a una forma manual o se puede asignar desde otra capa de negocios.
    UNIQUE, la columna que tenga el constraint unique garantiza que el valor que se guarda en esa columna sea 煤nico

  2. -. TIMESTAMP
    Est谩 basado en el n煤mero epoch que es el 1 enero de 1970 hasta la fecha y es donde se determina el inicio de las computadoras y es un n煤mero entero que se guarda en segundos y permite hacer operaciones sobre el.

  3. -. DATETIME
    Este tipo de datos puede guardar cualquier valor de tipo fecha sin restricci贸n. Incluso anterior a nuestra era. es por eso que las fechas de nacimiento de usuarios debe utilizar este valor para garantizar que podemos registrarlos con la fecha adecuada.

  4. TIMESTAMP vs DATETIME: hay que resaltar que un, 1.TIMESTAMP 鈥淣O PUEDE HACER TODO LO DE DATETIME pero DATETIME S脥 PUEDE HACERLO DE UN TIMESTAMP鈥, 2.DATETIME no est谩 guardado en segundos y no es tan eficiente para hacer c谩lculos.

  5. -.Active
    Es buena pr谩ctica no eliminar registros de una bases de datos es por ello que se crea una columna como active que es un valor booleano dicho valor sirve para para decir si el registro est谩 activo o no.

  6. -. created_ad y updated_ad
    Es buena pr谩ctica tener una columna que permite saber el momento exacto en el que se crea un registro o se actualiza. Este tipo de dato se comporta m谩s como una meta-informaci贸n y nos puede ayudar por ejemplo a cu谩ntos usuarios fueron creados en una fecha en espec铆fico, saber cuando una tupla se actualiz贸

  7. created_ad Es una columna de buena pr谩ctica que permite saber cuando se cre贸 un registro. Est谩 utilizar谩 un conjunto de propiedades llamada entre ella se colocar谩 DEFAULT CURRENT_TIMESTAMP . Cuando se realiza un insert s铆 el valor de esta columna viene vac铆o colocar谩 en la tupla el valor de la fecha en que se cre贸 de manera autom谩tica .

 `created_at`    TIMESTAMP  NOT NULL DEFAULT CURRENT_TIMESTAMP,
  1. update_ad Es una columna de buena pr谩ctica que permite saber cuando un registro se actualiza se puede colocar ON UPDATE CURRENT_TIMESTAMP esto permite dejar un registro de la fecha actual cuando ocurre una actualizaci贸n
`updated_at`    TIMESTAMP  NOT NULL DEFAULT CURRENT_TIMESTAMP 
                       ON UPDATE CURRENT_TIMESTAMP, 
/*Tabla clientes*/
CREATE TABLE `clients` (
  `client_id`       int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name`           varchar(50) DEFAULT NULL,
  `email`           varchar(100) NOT NULL,
  `birthdate`       date DEFAULT NULL,
  `gender` enum('M','F') DEFAULT NULL,
  `active`           TINYINT(1) NOT NULL DEFAULT 1,
 `created_at`    TIMESTAMP  NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at`    TIMESTAMP  NOT NULL DEFAULT CURRENT_TIMESTAMP 
                       ON UPDATE CURRENT_TIMESTAMP, 
  PRIMARY KEY (`client_id`),
  UNIQUE KEY `email` (`email`)
) ENGINE=InnoDB AUTO_INCREMENT=101 DEFAULT CHARSET=utf8;
/*Tabla transactions*/
CREATE TABLE `transactions` (
  `transaction_id`      int(10) unsigned NOT NULL AUTO_INCREMENT,
  `book_id`             int(10) unsigned NOT NULL,
  `client_id`           int(10) unsigned NOT NULL,
  `type` enum('lend','sell') NOT NULL,
  `created_at`          timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `modified_at`         timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `finished`            tinyint(1) NOT NULL DEFAULT '0',
  `active`        TINYINT(1) NOT NULL DEFAULT 1,
  PRIMARY KEY (`transaction_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Les recomiendo esta pagina, que sirve para hacer inspeccion del codigo.

https://www.eversql.com/sql-syntax-check-validator/

Hola amigos de platzi aqu铆 les dejo un aporte para que aprendan a como crear un usuario,listarlo y eliminarlo sigan los pasos tal como esta en la imagen espero les sirva.

CREATE TABLE operations(
    operation_id INTEGER UNSIGNED PRIMARY KEY AUTO_INCREMENT NOT NULL,
    book_id INTEGER UNSIGNED NOT NULL, 
    client_id INTEGER UNSIGNED NOT NULL,
    type ENUM('V','P', 'D' ) NOT NULL COMMENT 'V: Vendido -P:Prestado -D: Devuelto',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    finished TINYINT(1) NOT NULL
    
);```

En Colombia se le suele decir a las tuplas -> registros y a las columnas -> campos.

Ac谩 como lo 鈥渟olucion茅鈥, nota de color interesante para la Base de Datos no es lo mismo usar ( ` ) en los nombres que usar ( 鈥 ), lo aprend铆 a las malas.

CREATE TABLE `operation` (
    `operation_id` int(10) unsigned NOT NULL PRIMARY KEY AUTO_INCREMENT,
    `book_id` INT(10) unsigned NOT NULL,
    `client_id` INT(10) unsigned NOT NULL,
    `operation_type` enum('PRESTAMO', 'DEVOLUCI脫N', 'VENTA'),
    `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `finished` TINYINT(1) NOT NULL,
    UNIQUE KEY `operation_id` (`operation_id`)
);```

Ahi les dejo el significado de los tipos de datos con fecha o tiempo
Tipo Fecha
DATE : Fecha con formato AAAA-MM-DD
TIME : Hora con formato hh:mm:ss
DATETIME : Fecha y hora con formato AAAA-MM-DD hh:mm:ss
TIMESTAMP : Lapso de tiempo con formato AAAA-MM-DD-hh-mm-ss.
YEAR : A帽o con formato AAAA

Resumen de la clase:
Tips:
Auto Increment es una buena pr谩ctica
Siempre crear columna created_at cuando se tiene una base de datos catalogo - operaci贸n.
Ninguna tupla se borra jam谩s, se usa columna active con bandera booleana

Tipo de Datos:
UNIQUE: valor de llave donde el valor de la columna es 煤nico.
TIMESTAMP: basado en EPOC, guarda enteros el valor de 1970 a la fecha.
DATETIME: ideal para cunpleanos, cualquier valor, no tan eficiente al calcular.
ENUM: enumeraci贸n de datos, solo se recibe una de las opciones.

C贸digo Reto:

CREATE TABLE IF NOT EXISTS operations (
    operation_id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    book_id INT UNSIGNED,
    client_id INT UNSIGNED,
    `type` ENUM('borrowed', 'sold', 'returned') NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    finished TINYINT(1) NOT NULL
);
CREATE TABLE clients (
    client_id INTEGER UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    `name` VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE,
    birthdate DATETIME,
    gender ENUM('M', 'F', 'ND') NOT NULL,
    active TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP 
        ON UPDATE CURRENT_TIMESTAMP
    
);```

Me encanta este profe!

Algo tan simple como DATETIME y TIMESTAMP puede explicarse con un esquema de forma muy simple, no hay necesidad de tanta palabrer铆a.


CREATE TABLE IF NOT EXISTS authors (
    author_id INTEGER UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    nationality VARCHAR(3)
);

//MOSTRAR LAS COLUMNAS DE LAS tablas
describe authors; 
desc books;
// MOSTRAR COMENTARIOS
show full comuns from books

//nombre de una columa y reservarla con comillas `integer_column `
`year`

CREATE TABLE clients(
    id_client INTEGER UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    `name` VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE,
    birthdate DATETIME,
    gender ENUM('M','F', 'ND') NOT NULL,
    
    active TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
********NOTA*************
DATETIME cualquier fecha como 1800-01-01 00:00:00
TIMESTAMP es un valor desde 1970 hasta ahora, global. Para calculos ('yyyy-mm-dd hh:mm:ss')
The DATETIME type is used when you need values that contain both date and time information. MySQL 
retrieves 
and displays DATETIME values in 'YYYY-MM-DD HH:MM:SS' format. The supported range is 
'1000-01-01 00:00:00' to '9999-12-31 23:59:59'.


The TIMESTAMP data type has a range of '1970-01-01 00:00:01' UTC to '2038-01-09 03:14:07' UTC. 
It has varying properties, depending on the MySQL version and the SQL mode the server is running in.
...
ENUM ( '','','') 
An ENUM is a string object with a value chosen from a list of permitted values 
that are enumerated explicitly in the column specification at table creation time.
********END NOTA**********```

Profe no todos los estudiantes son de despues de los 70

created_ad y updated_ad
Es buena pr谩ctica tener una columna que permite saber el momento exacto en el que se crea un registro o se actualiza. Este tipo de dato se comporta m谩s como una meta-informaci贸n y nos puede ayudar por ejemplo a cu谩ntos usuarios fueron creados en una fecha en espec铆fico, saber cuando una tupla se actualiz贸

CREATE TABLE IF NOT EXISTS operations (
	operation_id INTEGER UNSIGNED PRIMARY KEY AUTO_INCREMENT,
	book_id INTEGER UNSIGNED,
	client_id INTEGER UNSIGNED,
	type ENUM("Vendido", "Prestado", "Devuelto"),
	created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
	updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP 
	ON UPDATE CURRENT_TIMESTAMP,
	finshed TINYINT(1) NOT NULL DEFAULT 1
	);



Buen D铆a

Quisiera saber a que se debe este error鈥

mysql> CREATE TABLE clients (
-> client_id INTEGER UNSIGNED PRIMARY KEY AUTO_INCREMENT,
-> name VARCHAR(50) NOT NULL,
-> email VARCHAR(100) NOT NULL UNIQUE,
-> birthdate DATETIME,
-> gender ENUM(鈥楳鈥, 鈥楩鈥, 鈥楴D鈥) NOT NULL,
-> active TINYINT(1) NOT NULL DEFAULT 1,
-> created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
-> updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
-> ON UPDATE CURRENT_TIMESTAMP
-> );
ERROR 1293 (HY000): Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause
mysql>

Seg煤n veo la sintaxis es la misma que usa el profesor y no creo que el sistema operativo ponga problemas estoy usando un Windows 10, debo hacer alguna conf adicional.

Quedo atento.

Dato curioso: El Timestamp llega hasta 2038-01-19 03:14:07 porque hasta ah铆 llega el contador de ((2^31) - 1) bits que tienen los sistemas de 32 bits.

Todos los programas que usen esta medida de 32 bits de tiempo volver谩n (Inform谩ticamente) al a帽o 1900, cosa que posiblemente cause muchos problemas si no se previene antes de esa fecha.

El nombre que se le dio a este problema es Y2K38 (Year 2038), aunque tambi茅n hay unos muy parecidos llamados Y2K36 y Y2K que siguen la misma l贸gica.

Reto cumplido!

![](

Veo que algunos permiten null en book_id y client_id, lo que no es viable porque a ninguna operaci贸n le puede faltar el libro y cliente correspondientes.

CREATE TABLE IF NOT EXISTS operations (
	operation_id INTEGER UNSIGNED PRIMARY KEY AUTO_INCREMENT,
	book_id INTEGER UNSIGNED NOT NULL,
	client_id INTEGER UNSIGNED NOT NULL,
	`type` ENUM('P', 'D', 'V') NOT NULL, 
	created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
	updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP 
		ON UPDATE CURRENT_TIMESTAMP,
	finished TINYINT(1) NOT NULL
);

este es el c贸digo para la tabla clients:

create table clients (
    `client_id` integer unsigned primary key auto_increment,
    `name` varchar(50) not null,
    `email` varchar(100) not null unique,
    `birthdate` datetime,
    `gender` enum('M', 'F', 'ND') not null,
    `active` tinyint(1) not null default 1,
    `created_at` timestamp not null default current_timestamp,
    `updated_at` timestamp not null default current_timestamp on update current_timestamp
); 

esta es mi propuesta para la tabla operations:

create table if not exists operations (
    `operation_id` integer unsigned primary key auto_increment,
    `book_id` integer unsigned,
    `client_id` integer unsigned,
    `type` enum('s', 'b', 'r') not null,
    `finished` tinyint(1) not null,
    `created_at` timestamp not null default current_timestamp,
    `updated_at` timestamp not null default current_timestamp on update current_timestamp
); 

CREATE TABLE clients (
-> client_id INTEGER UNSIGNED PRIMARY KEY AUTO_INCREMENT,
-> name VARCHAR(50) NOT NULL,
-> email VARCHAR(100) NOT NULL UNIQUE,
-> birthdate DATETIME,
-> gender ENUM( 鈥楳鈥 , 鈥楩鈥 , 鈥楴D鈥) NOT NULL,
-> active TINYINT(1) NOT NULL DEFAULT 1,
-> created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
-> updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);
Query OK, 0 rows affected (0.33 sec)

Aqui ta uwu:

CREATE TABLE IF NOT EXISTS operations(
    
    `operation_id` INTEGER UNSIGNED NOT NULL UNIQUE PRIMARY KEY AUTO_INCREMENT,
    `book_id` INTEGER UNSIGNED NOT NULL,
    `client_id` INTEGER UNSIGNED NOT NULL,
    `type` ENUM('Prestado', 'Vendido', 'Devuelto') NOT NULL,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `update_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `finished` TINYINT NOT NULL,

    FOREIGN KEY(`book_id`) REFERENCES books(`book_id`),
    FOREIGN KEY(`client_id`) REFERENCES clients(`client_id`)
    
);

Como utilizo mariaDB puede ser m铆nimamente diferente pero es mas de los mismo al final c:

<
 CREATE TABLE IF NOT EXISTS operations(
     `operation_id` INTEGER UNSIGNED AUTO_INCREMENT PRIMARY KEY,
     `book_id` INTEGER UNSIGNED,
     `client_id` INTEGER UNSIGNED,
     `type`ENUM('L', 'R', 'S') NOT NULL COMMENT 'L = lent, R = returned, S = Sold',
     `finished` SMALLINT(1), 
     `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP(),
     `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP() ON UPDATE CURRENT_TIMESTAMP()
 );
>

Reto completado 馃槃

CREATE TABLE IF NOT EXISTS operations (
    operation_id INTEGER UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    book_id INTEGER UNSIGNED, 
    client_id INTEGER UNSIGNED,
    type ENUM('SELLED', 'BORROWED', 'RETURNED') NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    update_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 
    finished TINYINT(1) NOT NULL DEFAULT '0'
);
CREATE TABLE IF NOT EXISTS operations(
	operation_id INTEGER UNSIGNED PRIMARY KEY AUTO_INCREMENT,
	book_id INTEGER UNSIGNED NOT NULL,
	client_id INTEGER UNSIGNED NOT NULL
	operation ENUM("V","P","D") UNSIGNED,
	created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
	updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
	finished TINYINT(1) NOT NULL
);

Les dejo un cuadro de los tipos de Datos de fecha y hora

updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
finshed TINYINT(1) NOT NULL

Existe algo as铆 en SQL Servers? Esto es INCRE脥BLE!!

CREATE TABLE IF NOT EXISTS operations (
operation_id INTEGER UNSIGNED PRIMARY KEY AUTO_INCREMENT,
book_id INTEGER UNSIGNED,
client_id INTEGER UNSIGNED,
type ENUM(鈥楶鈥,鈥楧鈥,鈥榁鈥),
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
ON UPDATE CURRENT_TIMESTAMP,
finshed TINYINT(1) NOT NULL

Buen d铆a, esta es mi propuesta para generar la tabla de operations:

CREATE TABLE IF NOT EXISTS operations(
    operation_id INTEGER UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    book_id INTEGER UNSIGNED NOT NULL,
    client_id INTEGER UNSIGNED NOT NULL,
    type ENUM('B','R','S') NOT NULL COMMENT 'Borrowed, Returned, Sold',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    finished TINYINT(1) NOT NULL
);
CREATE TABLE IF NOT EXISTS operations (
  operation_id INTEGER UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  book_id INTEGER UNSIGNED,
  client_id INTEGER UNSIGNED,
  type ENUM('PRESTADO', 'DEVUELTO', 'VENDIDO') NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
    ON UPDATE CURRENT_TIMESTAMP,
  finshed TINYINT(1) NOT NULL
);

Mi tabla qued贸 de la siguiente forma.

<CREATE TABLE IF NOT EXISTS operations (
	operation_id INTEGER UNSIGNED PRIMARY KEY AUTO_INCREMENT,
	book_id INTEGER UNSIGNED,
	client_id INTEGER UNSIGNED,
	type ENUM('PRESTADO', 'DEVUELTO', 'VENDIDO'),
	create_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
	update_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
	finshed TINYINT(1) NOT NULL
	);>
CREATE TABLE IF NOT EXISTS operations (
`operation_id` INTEGER UNSIGNED PRIMARY KEY AUTO_INCREMENT,
`books_id` INTEGER UNSIGNED,
`client_id` INTEGER UNSIGNED,
`type` ENUM ('Disponible', 'Prestamo', 'Vendido', 'Devuelto' ),
`created_at` TIMESTAMP NOT NULL  DEFAULT CURRENT_TIMESTAMP,
`upadte_at`  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE  CURRENT_TIMESTAMP,
`finished` TINYINT (1) NOT NULL
);

Yo lo dej茅 as铆:

CREATE TABLE IF NOT EXISTS operations(
	operation_id INTEGER UNSIGNED PRIMARY KEY AUTO_INCREMENT,
	book_id INTEGER UNSIGNED,
	client_id INTEGER UNSIGNED,
	op_type ENUM('VTA', 'PRE', 'DEV') NOT NULL DEFAULT 'VTA',
	created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
	updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
	finished TINYINT(1) NOT NULL DEFAULT(0)
);

As铆 qued贸 la tabla:

CREATE TABLE IF NOT EXISTS operations (
	operation_id INTEGER UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  	book_id INTEGER UNSIGNED NOT NULL,
  	client_id INTEGER UNSIGNED NOT NULL,
	type ENUM("borrowed","returned","sold") NOT NULL,
  	created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  	updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  	finished TINYINT(1) NOT NULL
); ```

Hay dos tipos de datos de DATE:

  • DATETIME: no tiene restricciones a la hora de indicar cualquier a帽o de cualquier fecha.
  • TIMESTAMP: esta basada en el n煤mero epoch, n煤mero de segundos desde 1 de Enero de 1970.

LO HICE ASI PERO ME DA ERROR, ME CORRIGEN

CREATE TABLA IF NOT EXISTS operations (
operation_id INTEGER UNSIGNED PRIMARY KEY NOT NULL AUTO_INCREMENT,
book_id INTEGER UNSIGNED NOT NULL,
cliente_id INTEGER UNSIGNED NOT NULL,
typy ENUM(鈥榩restado鈥, 鈥榙evuelto鈥, 鈥榲endido鈥),
create_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
update_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
finshed TYNYINT (1) NOT NULL DEFAULT
);

created_ad y updated_ad, Este tipo de dato se comporta m谩s como una meta-informaci贸n y nos puede ayudar por ejemplo a cu谩ntos usuarios fueron creados en una fecha en espec铆fico o en qu茅 fecha los datos de los usuarios fueron actualizados.

CREATE TABLE IF NOT EXISTS operations(
   operation_id INTEGER UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    book_id INTEGER UNSIGNED,
    client_id INTEGER UNSIGNED,
    `type` ENUM('prestado','devuelto','vendido') NOT NULL,
    create_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE 	CURRENT_TIMESTAMP,
    finshed TINYINT(1) NOT NULL
);

Gracias por aceptarme.Yo nac铆 antes de 1970 y antes de los 60.Sesgado el curso? o el conocimiento?

No conoc铆a la diferencia entre TIMESTAMP y DATETIME ahora la tendr茅 en cuenta a la hora de crear tablas.gracias 馃槂

creacion de tabla operations

CREATE TABLE IF NOT EXISTS operations(
  operation_id INTEGER UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  book_id INTEGER UNSIGNED NOT NULL,
  client_id INTEGER UNSIGNED NOT NULL,
  `type` ENUM('Lend','Return','Sell') NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  update_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  finshed TINYINT(1) NOT NULL DEFAULT '0',
);```
CREATE TABLE IF NOT EXISTS operations (
    operation_id INTEGER UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    book_id INTEGER UNSIGNED,
    client_id INTEGER UNSIGNED,
    type ENUM('prestado','devuelto','vendido') NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
    ON UPDATE CURRENT_TIMESTAMP,
    finished TINYINT(1) NOT NULL DEFAULT 0
);```

En active utilice el tipo de dato boolean.

CREATE TABLE `clients` (
  `client_id` INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  `name` VARCHAR(50) NOT NULL,
  `email` VARCHAR(100) NOT NULL UNIQUE,
  `birthdate` DATETIME,
  `gender` ENUM('M', 'F', 'ND') NOT NULL,
  `active` BOOLEAN DEFAULT true,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);```
CREATE TABLE `opeations` (
  `opeartion_id` INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  `book_id` INT UNSIGNED,
  `user_id` INT UNSIGNED,
  `type` ENUM('borrowed', 'sold', 'returned') NOT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `finished` TINYINT(1) NOT NULL DEFAULT 0
);```

el curso va excelente pero tengo un problema yo estoy haciendo el proceso con SQL server pero tengo un problema con el Columna **Update_at ** tabla **operations **no he podido crear esta tabla esta esta parte ya sql creo que es diferente agredesco si alguien sabe como se maneja esta parte en SQL server.

CREATE TABLE operations ( 
operation_id INT(10) unsigned PRIMARY KEY AUTO_INCREMENT,
book_id INT(10) unsigned NOT NULL,
client_id INT(10) unsigned NOT NULL,
type ENUM('prestado','devuelto','vendido') NOT NULL,
created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
finished tinyint(1) NOT NULL DEFAULT 0);```

Me gusta los trucos que d谩 el profe, enserio es un buen masestro, aq铆 dejo el reto, no es complicado todo ya lo vimos y me queda claro

CREATE TABLE IF NOT EXISTS operations(
    operation_id INTEGER UNSIGNED PRIMARY KEY AUTO_INCREMENT, 
    book_id INTEGER UNSIGNED, 
    client_id INTEGER UNSIGNED, 
    `type` ENUM('PRESTADO', 'DEVUELTO', 'VENDIDO') NOT NULL, 
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    update_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    finshed TINYINT(1) NOT NULL DEFAULT 0
);

en mi caso siempre me acostumbre a crear los campos de las tablas con el prefijo del nombre de la tabla ejemplo:
authors_name porque asi me evito siempre el no usar palabras reservadas o que puedan repetirse en otras tablas y cuando haces un select de varias tablas se de que tabla es que campo.

CREATE TABLE IF NOT EXISTS author(
    idAuthor INTEGER UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    national VARCHAR(3)
);

CREATE TABLE IF NOT EXISTS book(
    idBook INTEGER UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    idAuthor INTEGER,
    title VARCHAR(100) NOT NULL,
    year INTEGER UNSIGNED NOT NULL DEFAULT 0,
    language varchar(2) NOT NULL DEFAULT 'es' COMMENT 'ISO 639-1 language',
    cover_url VARCHAR(500),
    price DOUBLE(6,2) NOT NULL DEFAULT 10.0,
    sellable TINYINT(1) DEFAULT 1,
    copies INTEGER NOT NULL DEFAULT 1,
    description TEXT
);

CREATE TABLE IF NOT EXISTS client(
    idClient INTEGER UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(5) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE,
    birthdate DATETIME,
    created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    active TINYINT(1) NOT NULL DEFAULT 1
);

CREATE TABLE IF NOT EXISTS operation(
    idOperation INTEGER UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    idBook INTEGER,
    idClient INTEGER,
    `type` ENUM("S", "L", "R") NOT NULL COMMENT 'S: SALE, L: LEND, R: RETURNED',
    created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    active TINYINT(1) NOT NULL DEFAULT 1
);

Mi tabla quedo as铆

CREATE TABLE IF NOT EXISTS operations (
    operation_id INTEGER PRIMARY KEY NOT NULL AUTO_INCREMENT,
    book_id INTEGER UNSIGNED,
    client_id INTEGER UNSIGNED,
    type ENUM('B','R','S') NOT NULL COMMENT 'Borrow, Return, Sell ',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    finish TINYINT(1) NOT NULL 
);

CREATE TABLE operations (
operation_id BIGINT NOT NULL PRIMARY KEY AUTO_INCREMENT,
book_id int(10) DEFAULT NULL,
client_id int(10) DEFAULT NULL,
type enum(鈥楶鈥,鈥楧鈥,鈥榁鈥) DEFAULT NULL,
created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
update_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

A continuaci贸n dejo mi c贸digo.
Se que lo coloque todo en minusculas, pero bueno鈥

create table if not exists operation (
operation_id int unsigned primary key auto_increment,
book_id int unsigned,
client_id int unsigned,
operation_type enum (鈥楶鈥, 鈥楧鈥, 鈥榁鈥) not null COMMENT 鈥楶:Prestado, D:Devuelto y V:Vendido鈥,
created_at timestamp not null default current_timestamp,
updated_at timestamp not null default current_timestamp on update current_timestamp,
finished tinyint(1) not null
);

Son solamente dos opciones鈥aja

CREATE TABLE IF NOT EXISTS operations(
    `operation_id` INTEGER UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    `book_id` INTEGER UNSIGNED,
    `client_id` INTEGER UNSIGNED,
    `type` ENUM('L', 'R', 'S') NOT NULL COMMENT 'L:LEND -R:RETURNED -S:SOLD',
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, 
    `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `finished` TINYINT(1) NOT NULL
);

As铆 hice la tabla

CREATE TABLE IF NOT EXISTS operations(
	operation_id INTEGER UNSIGNED PRIMARY KEY AUTO_INCREMENT,
	book_id INTEGER UNSIGNED,
	client_id INTEGER UNSIGNED,
	type ENUM('borrowed', 'returned', 'sold') NOT NULL,
	created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
	updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
	finished TINYINT(1) NOT NULL,
	FOREIGN KEY (book_id) REFERENCES books(book_id),
	FOREIGN KEY (client_id) REFERENCES clients(client_id)
);

Reto de la clase

CREATE TABLE IF NOT EXISTS operations (
operations_id INTEGER UNSIGNED PRIMARY KEY AUTO_INCREMENT,
book_id INT UNSIGNED, client_id INT UNSIGNED,
type ENUM ('prestado', 'devuelto', 'vendido') DEFAULT NULL,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
finshed TINYINT NOT NULL DEFAULT 1);

La sentencia sin ningun warnings

CREATE TABLE clients (
  client_id INTEGER UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(50) NOT NULL,
  email varchar(100) NOT NULL UNIQUE,
  birthdate DATETIME,
  gender ENUM('M','F', 'ND') NOT NULL,
  active TINYINT NOT NULL DEFAULT 1,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
-- Clients
CREATE TABLE IF NOT EXISTS clients (
	client_id INTEGER UNSIGNED PRIMARY KEY AUTO_INCREMENT,
	name VARCHAR(50) NOT NULL,
	email VARCHAR(100) NOT NULL UNIQUE,
	birthdate DATETIME,
	gender 	ENUM('M','F','ND'),
	active TINYINT(1) NOT NULL DEFAULT 1,
	created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
	update_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Operations
CREATE TABLE IF NOT EXISTS operations(
	operation_id INTEGER UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    book_id INTEGER UNSIGNED,
    client_id INTEGER UNSIGNED,
    `type` ENUM('borrowed', 'sold', 'returned') NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    finished TINYINT(1) NOT NULL
);```

Esta es mi soluci贸n 馃挌

CREATE TABLE IF NOT EXISTS operations(
  operations_id INTEGER UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  book_id INTEGER UNSIGNED NOT NULL,
  client_id INTEGER UNSIGNED NOT NULL,
  type ENUM('prestado', 'devuelto', 'vendido') NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT ON UPDATE CURRENT_TIMESTAMP,
  finished TINYINT NOT NULL
);

CREATE TABLE IF NOT EXISTS operations (
operation_id INTEGER PRIMARY KEY NOT NULL AUTO_INCREMENT,
book_id INTEGER UNSIGNED NOT NULL,
cliente_id INTEGER UNSIGNED NOT NULL,
operation_type ENUM (鈥榩restado鈥,鈥榙evuelto鈥,鈥榲endido鈥) NOT NULL,
create_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
update_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
finshed TINYINT(1) NOT NULL DEFAULT 鈥0鈥
);

La elecci贸n entre TIMESTAMP y DATETIME depende de los requisitos espec铆ficos de tu aplicaci贸n y del SGBD que est茅s utilizando. Si necesitas una mayor precisi贸n o la capacidad de manejar zonas horarias, TIMESTAMP puede ser m谩s adecuado. Si solo necesitas almacenar fechas y horas hasta el segundo y no tienes requisitos de zona horaria, DATETIME puede ser suficiente.

CREATE TABLE IF NOT EXISTS operations (
  operation_id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  books_id INT UNSIGNED NOT NULL,
  clients VARCHAR(50) NOT NULL UNIQUE,
  `type` ENUM('V', 'C', 'D'),
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  finished TINYINT(1) NOT NULL
);

Mi soluci贸n

CREATE TABLE IF NOT EXISTS operations (
    operation_id INTEGER UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    book_id INTEGER UNSIGNED NOT NULL,
    client_id INTEGER UNSIGNED NOT NULL,
    operation_date DATETIME NOT NULL,
    type ENUM('lend', 'sell') NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    modifed_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    finished TINYINT(1) NOT NULL DEFAULT 0
);

Creo que para una mejor sintaxis en ingles se podr铆a cambiar el nombre de la tabla clients por customers al menos que quieras tener por aparte una tabla clientsque ser铆an los clientes fijos que tienen cierta cantidad de privilegios con nuestra librer铆a.

CREATE TABLE IF NOT EXISTS operations (
operation_id INTEGER UNSIGNED PRIMARY KEY AUTO_INCREMENT NOT NULL,
book_id INTEGER UNSIGNED NOT NULL,
client_id INTEGER UNSIGNED NOT NULL,
type ENUM(鈥榩restado鈥, 鈥榙evuelto鈥, 鈥榲endido鈥) NOT NULL,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
finshed TINYINT(1) NOT NULL
);

Mi tabla

-- TABLA OPERACION
CREATE TABLE IF NOT EXISTS operations(
    `operation_id` INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
    `book_id` INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
    `client_id` INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
    `type` ENUM('PRESTAMO', 'RETORNADO', 'VENDIDO') NOT NULL,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at`TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP 
        ON UPDATE CURRENT_TIMESTAMP
    `finshed` TINYINT(1) not NULL
);

As铆, creo, se soluciona el ejercicio propuesto:

<CREATE TABLE IF NOT EXISTS operations (
operation_id INTEGER UNSIGNED PRIMARY KEY AUTO_INCREMENT,
book_id INTEGER UNSIGNED NOT NULL,
client_id INTEGER UNSIGNED NOT NULL,
opertation_type ENUM('prestado', 'devuelto', 'vendido') NOT NULL,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
finished TINYINT(1) NOT NULL 
);> 

CREATE TABLE IF NOT EXISTS operaciones(
Idoperacion INTEGER PRIMARY KEY AUTO_INCREMENT,
book_id INTEGER NOT NULL,
idcliente INTEGER NOT NULL,
estado ENUM(鈥楶restado鈥, 鈥楧evuelto鈥, 鈥榁endido鈥) NOT NULL,
create_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
update_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
finalizado TINYINT (1) NOT NULL
);

quiero comprar motul

No estoy seguro pero la tabla operations puede ser algo asi:

CREATE TABLE IF NOT EXISTS operations(
	operation_id INTEGER UNSIGNED PRIMARY KEY AUTO_INCREMENT,
	book_id INTEGER UNSIGNED,
	client_id INTEGER UNSIGNED,
	`type` ENUM('prestado', 'devuelto', 'vendido') NOT NULL,
	create_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
	updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
	finished TINYINT(1) NOT NULL
);

datetime: desde cualquier a帽o
timestamp: formato yyyy-mm-dd, hh-mm-ss desde el a帽o 1970 contabiliza

Si quieren renombrar una columna pueden seguir el siguiente ejemplo, donde cambio el nombre de la columna cliend_id a client_id de la tabla clients.

ALTER TABLE clients RENAME COLUMN cliend_id TO client_id;

Mi soluci贸n al reto


CREATE TABLE IF NOT EXISTS operations(
	operation_id INTEGER UNSIGNED PRIMARY KEY AUTO_INCREMENT,
	book_id INTEGER UNSIGNED ,
	clienT_id INTEGER UNSIGNED,
	`type` ENUM('lend','sold','returned') NOT NULL,
	created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
	updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP 
		ON UPDATE CURRENT_TIMESTAMP,
	finished TINYINT NOT NULL
);

Siento que falta la recepci贸n de los FK, pero solo ha hablado que se debe poner del mismo tipo, me imagino que nos har谩 Alterar las tablas despu茅s.

CREATE TABLE IF NOT EXISTS operations (
	operation_id INTEGER UNSIGNED PRIMARY KEY AUTO_INCREMENT,
	book_id INTEGER UNSIGNED,
	client_id INTEGER UNSIGNED,
	`type` ENUM('lend', 'returned', 'sell') NOT NULL,
	created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
	updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
	finshed TINYINT(1) NOT NULL DEFAULT 0
);

Lista mi tarea compilando

mysql> desc operations;
+--------------+-------------------+------+-----+-------------------+-----------------------------------------------+
| Field        | Type              | Null | Key | Default           | Extra                                         |
+--------------+-------------------+------+-----+-------------------+-----------------------------------------------+
| operation_ID | int unsigned      | NO   | PRI | NULL              | auto_increment                                |
| book_id      | int unsigned      | YES  |     | NULL              |                                               |
| client_id    | int unsigned      | YES  |     | NULL              |                                               |
| type         | enum('V','P','D') | NO   |     | NULL              |                                               |
| created_at   | timestamp         | NO   |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED                             |
| updated_at   | timestamp         | NO   |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |
| finished     | tinyint(1)        | NO   |     | NULL              |                                               |
+--------------+-------------------+------+-----+-------------------+-----------------------------------------------+
7 rows in set (0.00 sec)

mysql>

驴Es necesario colocar el auto_increment?

La respuesta es no, pero es 煤til para poder llevar un mejor control en la DB sin ocasionar problemas, a diferencia de que si agregamos el id manualmente, el usuario podr铆a olvidarse que agrego ya el id 1 hace a帽os y volverlo a colocar. Esto generar铆a complicaciones

CREATE TABLE IF NOT EXISTS book_client (
id INTEGER UNSIGNED PRIMARY KEY AUTO_INCREMENT,
book_id INTEGER UNSIGNED NOT NULL,
client_id INTEGER UNSIGNED NOT NULL,
type ENUM(鈥榩restado鈥,鈥榲endido鈥, 鈥榙evuelto鈥) NOT NULL,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
finished TINYINT(1) NOT NULL
);

CREATE TABLE IF NOT EXISTS operations(
    operation_id INTEGER UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    book_id INTEGER UNSIGNED,
    client_id INTEGER UNSIGNED,
    type ENUM('lend', 'returned', 'sell'),
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

);

Debo aclarar algo ,al comienzo me quejaba de este docente, a pesar que se ve que es un maestro con much铆sima experiencia ,sent铆a que no le entend铆a mucho ,pero ya siguiendo los videos ,le estoy entiendo bastante ,brinda mucha informaci贸n , aunque s铆 a veces me pierdo un poco ,pero se nota que es un maestro capo.

  • CODE SQL TABLE OPERATIONS

  • TERMINAL MYSQL

  • COPY CODE

CREATE TABLE operations(
operation_id INTEGER UNSIGNED PRIMARY KEY AUTO_INCREMENT,
book_id INTEGER UNSIGNED NOT NULL,
client_id INTEGER UNSIGNED NOT NULL,
`type`ENUM ('IN', 'OUT', 'BUY')  NOT NULL,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
finshed TINYINT(1) NOT NULL 
);

CREATE TABLE IF NOT EXISTS operations(
operation_id INTEGER UNSIGNED PRIMARY KEY AUTO_INCREMENT,
book_id INTEGER UNSIGNED ,
client_id INTEGER UNSIGNED ,
type ENUM(鈥榁ENTA鈥,鈥楻ENTA鈥,鈥楧EVOLUCION鈥) NOT NULL,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
finished TINYINT(1) NOT NULL
);

No s茅 si esta correcto del todo, pero yo lo hice as铆

CREATE TABLE IF NOT EXISTS operations(
operation_id INTEGER UNSIGNED PRIMARY KEY AUTO_INCREMENT,
book_id INTEGER UNSIGNED NOT NULL,
client_id INTEGER UNSIGNED NOT NULL,
`type` ENUM('lend','returned','sell') NOT NULL,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
update_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

Hola ac谩 esta mi aporte. Siento que falta algo m谩s por agregar respecto a la relaci贸n que debe existir con el label op_type y el finished, pero me imagino que m谩s adelante explicar谩n esto 馃槃

CREATE TABLE IF NOT EXISTS operations (
    operation_id INTEGER UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    book_id INTEGER UNSIGNED,
    customer_id INTEGER UNSIGNED,
    op_type ENUM('LOANED', 'RETURNED', 'SOLD') COMMENT 'IF BOOK IS RETURNED OR SOLD, FINISHED LABEL WILL HAVE 1',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    price DOUBLE(6,2) NOT NULL DEFAULT 10.0,
    finished TINYINT(1) NOT NULL
);

ENUM()

sirve para especificar que valores puede recibir el campo, ejemplo:

gender ENUM('M','F','ND')

esto quiere decir que este campo solo recibe los valores que estan en la funcion ENUM()

CREATE TABLE IF NOT EXISTS operations(
operation_id INT PRIMARY KEY AUTO_INCREMENT,
book_id INT AUTO_INCREMENT,
client_id INT AUTO_INCREMENT,
type ENUM(鈥榁鈥, 鈥楶鈥, 鈥楧鈥) NOT NULL,
created_at TIMESTAMP NOT NULL DEFAULT
CURRENT_TIMESTAMP,
updated_at TIMESTAMP NOT NULL DEFAULT
CURRENT_TIMESTAMP ON UPDATE
CURRENT_TIMESTAMP,
finished TINYINT(1) NOT NULL
)

Listos, est谩 muy interesante este curso 馃槃

Aqu铆 mi tarea:

CREATE TABLE IF NOT EXISTS operations (
	operation_id INTEGER UNSIGNED PRIMARY KEY AUTO_INCREMENT,
	book_id INTEGER UNSIGNED,
	client_id INTEGER UNSIGNED,
	type ENUM('P', 'V', 'D') NOT NULL,
	create_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
	updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
	finished TINYINT(1) NOT NULL
);

el Query que utilice para crear la table de opraciones es el siguiente:

CREATE TABLE IF NOT EXISTS operations (
	opration_id INTEGER UNSIGNED PRIMARY KEY AUTO_INCREMENT,
	book_id INTEGER UNSIGNED,
	client_id INTEGER UNSIGNED,
	type ENUM('Prestado','Devuelto','Vendido')NOT NULL,
	created_at  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
	updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
	finished TINYINT(1) NOT NULL
);
CREATE TABLE IF NOT EXISTS operations (
	`operation_id` INTEGER UNSIGNED PRIMARY KEY AUTO_INCREMENT,
	`book_id` INTEGER UNSIGNED,
	`cliente_id` INTEGER UNSIGNED,
	`type` ENUM('lend', 'sell', 'retu') NOT NULL,
	`created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
	`updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
	`finshed` TINYINT(1) NOT NULL
);

CREATE TABLE IF NOT EXISTS operations(
op_id INTEGER(10) NOT NULL PRIMARY KEY AUTO_INCREMENT,
book_id INTEGER NOT NULL,
client_id INTEGER NOT NULL,
Tipo ENUM(鈥榁鈥,鈥楻鈥,鈥楶鈥) NOT NULL COMMENT 鈥 Vendido Prestado Regresado鈥,
Costo DOUBLE(4,2),
Creado_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
Actualizdo_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
Finishied TINYINT(1) NOT NULL

);

// Asi me qued贸

script para la tabla del reto 鈥渙perations鈥:

CREATE TABLE IF NOT EXISTS operations (
    operation_id INTEGER UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    book_id INTEGER UNSIGNED,
    client_id INTEGER UNSIGNED,
    type ENUM('lend', 'return', 'sell') NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
        ON UPDATE CURRENT_TIMESTAMP,
    finished TINYINT(1) NOT NULL DEFAULT 0
);
CREATE TABLE IF NOT EXISTS operations (
	operation_id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
	book_id INT UNSIGNED,
	client_id INT UNSIGNED, 
	`type` ENUM('B','R','S') NOT NULL COMMENT 'B = BORROWED, R = RETURNED, S = SOLD', 
	created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
	updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 
	finished TINYINT(1) NOT NULL,
	active TINYINT(1) NOT NULL DEFAULT 1
);
CREATE TABLE IF NOT EXISTS operations(
    operation_id INTEGER UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    book_id INTEGER UNSIGNED,
    client_id INTEGER UNSIGNED,
    type ENUM('b', 'r','s' ) NOT NULL COMMENT 'borrowed, return, sold' 
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP 
    ON UPDATE CURRENT_TIMESTAMP 
    fineshed TINYINT(1) NOT NULL 
);