USE MYSCHEMA;

CREATE TABLE DATENTIME (
 JOIN_DATE DATE,
 JOIN_TIME TIME,
 JOIN_DATE_TIME DATETIME
);

INSERT INTO DATENTIME(JOIN_DATE,JOIN_TIME, JOIN_DATE_TIME)
VALUES ('2010-10-25','10:12:14','2010-10-25 10:12:14');

INSERT INTO DATENTIME(JOIN_DATE,JOIN_TIME, JOIN_DATE_TIME)
VALUES ('2010/10/25','10:12:14','2010-10-25 10:12:14');

INSERT INTO DATENTIME(JOIN_DATE,JOIN_TIME, JOIN_DATE_TIME)
VALUES ('2010%10%25','10:12:14','2010-10-25 10:12:14');

INSERT INTO DATENTIME(JOIN_DATE,JOIN_TIME, JOIN_DATE_TIME)
VALUES ('2010^10^25','10:12:14','2010-10-25 10:12:14');

INSERT INTO DATENTIME(JOIN_DATE,JOIN_TIME, JOIN_DATE_TIME)
VALUES ('2010*10*25','10:12:14','2010-10-25 10:12:14');

INSERT INTO DATENTIME(JOIN_DATE,JOIN_TIME, JOIN_DATE_TIME)
VALUES ('2010*10*25','10:12:14','2010-10-25 10:12:14');

SELECT * FROM DATENTIME;

#AUTOMATIC INITIALIZATION OF DATETIME
CREATE TABLE DTABLE (
  SNAME VARCHAR(20),
  STIME TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  SDATE DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  ID INT
);

DROP TABLE DTABLE;

SELECT * FROM DTABLE;

INSERT INTO DTABLE(SNAME, ID)
VALUES
('Hitoshi',120),
('Kishimoto',150),
('Gautam',132),
('Fired',110);

UPDATE DTABLE
SET SNAME = 'Gtm' WHERE ID = 132;

UPDATE DTABLE
SET ID = 122 WHERE ID = 123;

CREATE TABLE DTABLE2 (
  SNAME VARCHAR(20),
  SYEAR YEAR,
  ID INT
);

INSERT INTO DTABLE2(SNAME, SYEAR, ID)
VALUES
('Gautam',2017,120),
('Mike',17,130),
('Harry',80,140),
('Remo','2010',150),
('Zero','0',160);

SELECT * FROM DTABLE2;

DROP TABLE ENUMS;

CREATE TABLE ENUMS(
 SNAME VARCHAR(25),
 SIZE ENUM('SMALL','MEDIUM','LARGE')
);

INSERT INTO ENUMS(SNAME, SIZE)
VALUES('GAUTAM','MEDIUM'),
('MIKE','SMALL'),
('BIKE','LARGE');

SELECT * FROM ENUMS WHERE SIZE = 'SMALL';

SELECT * FROM ENUMS WHERE SIZE = 3;

CREATE TABLE YEAR2(
 SOMEVALUE YEAR(2)
);

CREATE TABLE RESTAURANT(
 ITEM VARCHAR(30),
 SIZE ENUM('SMALL','MEDIUM','LARGE')
);

INSERT INTO RESTAURANT(ITEM, SIZE)
VALUES('PIZZA',1),
('BURGER',2),
('COLA',1),
('FRIES',3);

SELECT * FROM RESTAURANT;

SELECT * FROM RESTAURANT WHERE SIZE = 'SMALL';
SELECT * FROM RESTAURANT WHERE SIZE = 1;