Su majestad el SELECT

Resumen de la clase:

-- Listar todas la tuplas de la tabla clients
SELECT * FROM clients;

-- Listar todos los nombres de la tabla clients
SELECT name FROM clients;

-- Listar todos los nombres, email y género de la tabla clients
SELECT name, email, gender FROM clients;

-- Listar los 10 primeros resultados de la tabla clients
SELECT name, email, gender FROM clients LIMIT 10;

-- Listar todos los clientes de género Masculino
SELECT name, email, gender FROM clients WHERE gender = 'M';

-- Listar el año de nacimientos de los clientes, con la función YEAR()
SELECT YEAR(birthdate) FROM clients;

-- Mostrar el año actual
SELECT YEAR(NOW());

-- Listar los 10 primeros resultados de las edades de los clientes
SELECT YEAR(NOW()) - YEAR(birthdate) FROM clients LIMIT 10;

-- Listar nombre y edad de los 10 primeros clientes
SELECT name, YEAR(NOW()) - YEAR(birthdate) FROM clients LIMIT 10;

-- Listar clientes que coincidan con el nombre de "Saave"
SELECT * FROM clients WHERE name LIKE '%Saave%';

-- Listar clientes (nombre, email, edad y género). con filtro de genero = F y nombre que coincida con 'Lop'
--Usando alias para nombrar la función como 'edad'
SELECT name, email, YEAR(NOW()) - YEAR(birthdate) AS edad, gender FROM clients WHERE gender = 'F' AND name LIKE '%Lop%';

Existen una pautas para facilitar la formulación de un Query, o dicho de otra forma para traducir una consulta a lenguaje SQL:

• Qué quiero mostrar: SELECT

• De donde tomare los datos: FROM

• Como voy a filtrar esa información: WHERE

• Si deseo ordenarla, desde que rubro me voy a apoyar: ORDER BY

• Los filtros que deseo tengan mis datos: HAVING, LIKE

Seria un bueno tener varias clases o hasta un curso de puras funciones SQL y su utilidad.

Saludos desde el pasado!

Notas de clase 📚📚📚

-- Creacion de base de datos
CREATE DATABASE platzi_operation;
-- Creacion de base de datos usando un condicional
CREATE DATABASE IF NOT EXISTS platzi_operation;
-- Mostar Warnings
SHOW warnings;
-- Mostar bases de datos
SHOW DATABASES
-- Utilizar una base de datos
USE platzi_operation;
-- Mostrar tablas
SHOW TABLES;
-- Como saber que base de datos estoy usando, RTA:
SELECT DATABASE();
-- Borrar tabla
DROP TABLE books
-- Mostar tablas
SHOW TABLES;
-- Describe los campos de la base de datos
DESCRIBE books
DESC authors
-- Mostar todo el esquema de la tabla (Incluyendo los comentarios)
SHOW FULL COLUMNS FROM books;

-- CREACION DEL PROYECTO [LIBRERIA]

-- Buena practica: Usar el nombre de la tabla en plural
-- Tabla de libros
CREATE TABLE IF NOT EXISTS books(
  book_id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  author_id INT UNSIGNED ,
  title VARCHAR(100) NOT NULL,
  `year` INT UNSIGNED NOT NULL DEFAULT 1900,
  language VARCHAR(2) NOT NULL DEFAULT 'es' COMMENT 'ISO 639-1 Lenguage',
  cover_url VARCHAR(500),
  price DOUBLE(6,2) DEFAULT 10.0,
  sellable TINYINT(1) DEFAULT 1,
  copies INT NOT NULL DEFAULT 1,
  description TEXT
);

-- Tabla de autores
CREATE TABLE IF NOT EXISTS authors (
  author_id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(100) NOT NULL,
  nationality VARCHAR(3)
);

-- Tabla de clientes
CREATE TABLE IF NOT EXISTS 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 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
);

-- Tabla de operaciones
CREATE TABLE IF NOT EXISTS operations(
  operation_id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  book_id INT UNSIGNED,
  client_id INT UNSIGNED,
  `type` ENUM('prestado', 'devuelto', 'vendido'),
  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
);

-- Inserccion de datos

-- Relleno de autores
INSERT INTO authors(author_id, name, nationality) 
VALUES (NULL, 'Juan Rulfo', 'MEX');

INSERT INTO authors(name, nationality) 
VALUES('Gabriel Garcia Marquez', 'COL');

INSERT INTO authors 
VALUES(NULL, 'Juan Gabriel Vazquez', 'COL'); 

-- Relleno con TRUCASOOOOOOO : CHUNCKS 50 Num Magic
INSERT INTO authors(name, nationality)
VALUES ('Julio Cortázar', 'ARG'),
       ('Isabel Allende', 'CHI'),
       ('Octavio Paz', 'MEX'),
       ('Juan Carlos Onetti', 'URY');

-- Llave Perdida
INSERT INTO authors(author_id, name) 
VALUES(16, 'Pablo Neruda');

-- Autores

INSERT INTO `authors`(name, nationality) 
VALUES ('Sam Altman','USA'),
('Freddy Vega','COL'),
('Arthur Conan Doyle','GBR'),
('Chuck Palahniuk','USA'),
('Juan Rulfo','MEX'),
('Henning Mankel','SWE'),
('Jaideva Goswami','USA'),
('John Foreman','USA'),
('Stephen Hawking','USA'),
('Stephen Dubner','USA'),
('Edward Said','USA'),
('Vladimir Vapnik','RUS'),
('V P Menon','IND'),
('Leonard Mlodinow','USA'),
('Frank Shih','JAP'),
('Maria Konnikova','RUS'),
('Sebastian Gutierrez','ESP'),
('Kurt Vonnegut','USA'),
('Cedric Villani','FRA'),
('Gerald Sussman','USA'),
('Abraham Eraly','IND'),
('Frank Kafka','AUT'),
('John Pratt','USA'),
('Robert Nisbet','USA'),
('H. G. Wells',"ENG"),
('Werner Heisenberg','DEU'),
('Andy Oram',NULL),
('Terence Tao',"AUS"),
('Drew Conway',"USA"),
('Nate Silver',"USA"),
('Wes McKinney',"USA"),
('Thomas Cormen', "USA"),
('Siddhartha Deb',"IND"),
('Albert Camus',"FRA"),
('Adam Smith',"ENG"),
('Ken Follett',"ENG"),
('Fritjof Capra',"AUT"),
('Richard Feynman',"USA"),
('Ernest Hemingway',"USA"),
('Frederick Forsyth',"ENG"),
('Jeffery Archer',"ENG"),
('Randy Pausch',"USA"),
('Ayn Rand',"RUS"),
('Michael Crichton',"USA"),
('John Steinbeck',"USA"),
('Edgar Allen Poe',"USA"),
('Will Durant',NULL),
('P L Deshpande',NULL),
('John Grisham',"USA"),
('V. S. Naipaul',NULL),
('Joseph Heller',NULL),
('BBC',NULL),
('Bob Dylan',"USA"),
('Madan Gupta',"IND"),
('Alfred Stonier',NULL),
('W. H. Greene',NULL),
('Gary Bradsky',NULL),
('Andrew Tanenbaum',NULL),
('David Forsyth',NULL),
('Schilling Taub',NULL),
('Yashwant Kanetkar',NULL),
('Jonathan Stroud',NULL),
('Fyodor Dostoevsky',"RUS"),
('Dan Brown',"USA"),
('Amartya Sen',NULL),
('Amitav Ghosh',NULL),
('Lorraine Hansberry',NULL),
('Bob Woodward',NULL),
('Kuldip Nayar',NULL),
('Sunita Deshpande',NULL),
('William Dalrymple',NULL),
('Various',NULL),
('Sanjay Garg',NULL),
('V P Kale',NULL),
('Shashi Tharoor',"IND"),
('Dominique Lapierre',NULL),
('Bertrand Russell',"ENG"),
('Sam Harris',NULL),
('Earle Stanley Gardner',NULL),
('Peter Drucker',NULL),
('David Bodanis',NULL),
('Victor Hugo',"FRA"),
('Richard Gordon',NULL),
('George Orwell',NULL),
('Lee Iacoca',"USA"),
('William S Maugham',NULL),
('Robert Pirsig',NULL),
('Robert Fisk',NULL),
('Amir Aczel',NULL),
('Samuel Huntington',NULL),
('Richard Bach',NULL),
('Braithwaite',NULL),
('V S Naipaul',NULL),
('Jawaharlal Nehru',NULL),
('Gerald Durrell',NULL),
('Simon Singh',"ENG"),
('Hart Duda',NULL),
('Thomas Friedman',NULL),
('Keith Devlin',NULL),
('James Gleick',NULL),
('Joy Thomas',NULL),
('Muhammad Rashid',NULL),
('Ned Mohan',NULL),
('Simon Haykin',NULL),
('Alex Rutherford',NULL),
('Michael Baz-Zohar',NULL),
('Jim Corbett',NULL),
('Jules Verne',NULL),
('Deshpande P L',NULL),
('Eric Raymond',NULL),
('Sergio Franco',NULL),
('Allen Downey',NULL),
('Morris West',NULL),
('Phillip Janert',NULL),
('Carl Sagan',"USA"),
('E T Bell',NULL),
('Richard Dawkins',NULL),
('Sudhanshu Ranjan',"IND"),
('Kautiyla',NULL),
('Palkhivala',NULL),
('Sorabjee',NULL),
('Hussain Zaidi',NULL),
('Peter Ackroyd',NULL),
('Nariman',NULL),
('Jean Sassoon',NULL),
('Peter Dickinson',NULL),
('Machiavelli',NULL),
('Aldous Huxley',"ENG"),
('J K Rowling',"ENG"),
('Steig Larsson',"SWE"),
('Steve Eddins', NULL),
('Charles Dickens',"ENG");

-- Insercion de libros

INSERT INTO `books` VALUES (1,1,'The Startup Playbook',2013,'en',NULL,10.00,1,5,'Advice from the experts'),
(2,1,'The Startup Playbook',2014,'es',NULL,10.00,1,5,'Consejo de los expertos, traducido por Platzi'),
(3,3,'Estudio en escarlata',1887,'es',NULL,5.00,1,10,'La primera novela de Sherlock Holmes'),
(4,6,'Wallander: Asesinos sin rostro',1991,'es',NULL,15.00,1,3,''),
(5,6,'Wallander: Los perros de Riga',1992,'es',NULL,15.00,1,3,''),
(6,6,'Wallander: La leona blanca',1993,'es',NULL,15.00,1,3,''),
(7,6,'Wallander: El hombre sonriente',1994,'es',NULL,15.00,1,3,''),
(8,6,'Wallander: La falsa pista',1995,'es',NULL,15.00,1,3,''),
(9,6,'Wallander: La quinta mujer',1996,'es',NULL,15.00,1,3,''),
(10,6,'Wallander: Pisando los talones',1997,'es',NULL,15.00,1,3,''),
(11,6,'Wallander: Cortafuegos',1998,'es',NULL,15.00,1,3,''),
(12,5,'El llano en llamas',1953,'es',NULL,10.00,0,1,'Cuentos mexicanos'),
(13,7,'Fundamentals of Wavelets',1900,'en',NULL,NULL,1,4,NULL),
(14,8,'Data Smart',1900,'en',NULL,NULL,1,4,NULL),
(15,9,'God Created the Integers',1900,'en',NULL,NULL,1,4,NULL),
(16,10,'Superfreakonomics',1900,'en',NULL,NULL,1,4,NULL),
(17,11,'Orientalism',1900,'en',NULL,NULL,1,4,NULL),
(18,12,'The Nature of Statistical Learning Theory',1900,'en',NULL,NULL,1,4,NULL),
(19,13,'Integration of the Indian States',1900,'en',NULL,NULL,1,4,NULL),
(20,14,'The Drunkard's Walk',1900,'en',NULL,NULL,1,4,NULL),
(21,15,'Image Processing & Mathematical Morphology',1900,'en',NULL,NULL,1,4,NULL),
(22,16,'How to Think Like Sherlock Holmes',1900,'en',NULL,NULL,1,4,NULL),
(23,17,'Data Scientists at Work',1900,'en',NULL,NULL,1,4,NULL),
(24,18,'Slaughterhouse Five',1900,'en',NULL,NULL,1,4,NULL),
(25,19,'Birth of a Theorem',1900,'en',NULL,NULL,1,4,NULL),
(26,20,'Structure & Interpretation of Computer Programs',1900,'en',NULL,NULL,1,4,NULL),
(27,21,'The Age of Wrath',1900,'en',NULL,NULL,1,4,NULL),
(28,22,'The Trial',1900,'en',NULL,NULL,1,4,NULL),
(29,23,'Statistical Decision Theory',1900,'en',NULL,NULL,1,4,NULL),
(30,24,'Data Mining Handbook',1900,'en',NULL,NULL,1,4,NULL),
(31,25,'The New Machiavelli',1900,'en',NULL,NULL,1,4,NULL),
(32,26,'Physics & Philosophy',1900,'en',NULL,NULL,1,4,NULL),
(33,27,'Making Software',1900,'en',NULL,NULL,1,4,NULL),
(34,28,'Vol I Analysis',1900,'en',NULL,NULL,1,4,NULL),
(35,29,'Machine Learning for Hackers',1900,'en',NULL,NULL,1,4,NULL),
(36,30,'The Signal and the Noise',1900,'en',NULL,NULL,1,4,NULL),
(37,31,'Python for Data Analysis',1900,'en',NULL,NULL,1,4,NULL),
(38,32,'Introduction to Algorithms',1900,'en',NULL,NULL,1,4,NULL),
(39,33,'The Beautiful and the Damned',1900,'en',NULL,NULL,1,4,NULL),
(40,34,'The Outsider',1900,'en',NULL,NULL,1,4,NULL),
(41,3,'The - Vol I Complete Sherlock Holmes',1900,'en',NULL,NULL,1,4,NULL),
(42,3,'The - Vol II Complete Sherlock Holmes',1900,'en',NULL,NULL,1,4,NULL),
(43,37,'The Wealth of Nations',1900,'en',NULL,NULL,1,4,NULL),
(44,38,'The Pillars of the Earth',1900,'en',NULL,NULL,1,4,NULL),
(45,39,'The Tao of Physics',1900,'en',NULL,NULL,1,4,NULL),
(46,40,'Surely You's re Joking Mr Feynman',1900,'en',NULL,NULL,1,4,NULL),
(47,41,'A Farewell to Arms',1900,'en',NULL,NULL,1,4,NULL),
(48,42,'The Veteran',1900,'en',NULL,NULL,1,4,NULL),
(49,43,'False Impressions',1900,'en',NULL,NULL,1,4,NULL),
(50,44,'The Last Lecture',1900,'en',NULL,NULL,1,4,NULL),
(51,45,'Return of the Primitive',1900,'en',NULL,NULL,1,4,NULL),
(52,46,'Jurassic Park',1900,'en',NULL,NULL,1,4,NULL),
(53,47,'A Russian Journal',1900,'en',NULL,NULL,1,4,NULL),
(54,48,'Tales of Mystery and Imagination',1900,'en',NULL,NULL,1,4,NULL),
(55,10,'Freakonomics',1900,'en',NULL,NULL,1,4,NULL),
(56,39,'The Hidden Connections',1900,'en',NULL,NULL,1,4,NULL),
(57,51,'The Story of Philosophy',1900,'en',NULL,NULL,1,4,NULL),
(58,52,'Asami Asami',1900,'en',NULL,NULL,1,4,NULL),
(59,47,'Journal of a Novel',1900,'en',NULL,NULL,1,4,NULL),
(60,47,'Once There Was a War',1900,'en',NULL,NULL,1,4,NULL),
(61,47,'The Moon is Down',1900,'en',NULL,NULL,1,4,NULL),
(62,56,'The Brethren',1900,'en',NULL,NULL,1,4,NULL),
(63,57,'In a Free State',1900,'en',NULL,NULL,1,4,NULL),
(64,58,'Catch 22',1900,'en',NULL,NULL,1,4,NULL),
(65,59,'The Complete Mastermind',1900,'en',NULL,NULL,1,4,NULL),
(66,60,'Dylan on Dylan',1900,'en',NULL,NULL,1,4,NULL),
(67,61,'Soft Computing & Intelligent Systems',1900,'en',NULL,NULL,1,4,NULL),
(68,62,'Textbook of Economic Theory',1900,'en',NULL,NULL,1,4,NULL),
(69,63,'Econometric Analysis',1900,'en',NULL,NULL,1,4,NULL),
(70,64,'Learning OpenCV',1900,'en',NULL,NULL,1,4,NULL),
(71,65,'Data Structures Using C & C++',1900,'en',NULL,NULL,1,4,NULL),
(72,66,'A Modern Approach Computer Vision',1900,'en',NULL,NULL,1,4,NULL),
(73,67,'Principles of Communication Systems',1900,'en',NULL,NULL,1,4,NULL),
(74,68,'Let Us C',1900,'en',NULL,NULL,1,4,NULL),
(75,69,'The Amulet of Samarkand',1900,'en',NULL,NULL,1,4,NULL),
(76,70,'Crime and Punishment',1900,'en',NULL,NULL,1,4,NULL),
(77,71,'Angels & Demons',1900,'en',NULL,NULL,1,4,NULL),
(78,72,'The Argumentative Indian',1900,'en',NULL,NULL,1,4,NULL),
(79,73,'Sea of Poppies',1900,'en',NULL,NULL,1,4,NULL),
(80,72,'The Idea of Justice',1900,'en',NULL,NULL,1,4,NULL),
(81,75,'A Raisin in the Sun',1900,'en',NULL,NULL,1,4,NULL),
(82,76,'All the President's Men',1900,'en',NULL,NULL,1,4,NULL),
(83,43,'A Prisoner of Birth',1900,'en',NULL,NULL,1,4,NULL),
(84,78,'Scoop!',1900,'en',NULL,NULL,1,4,NULL),
(85,79,'Ahe Manohar Tari',1900,'en',NULL,NULL,1,4,NULL),
(86,80,'The Last Mughal',1900,'en',NULL,NULL,1,4,NULL),
(87,81,'Vol 39 No. 1 Social Choice & Welfare',1900,'en',NULL,NULL,1,4,NULL),
(88,52,'Radiowaril Bhashane & Shrutika',1900,'en',NULL,NULL,1,4,NULL),
(89,52,'Gun Gayin Awadi',1900,'en',NULL,NULL,1,4,NULL),
(90,52,'Aghal Paghal',1900,'en',NULL,NULL,1,4,NULL),
(91,85,'Maqta-e-Ghalib',1900,'en',NULL,NULL,1,4,NULL),
(92,86,'Manasa',1900,'en',NULL,NULL,1,4,NULL),
(93,87,'India from Midnight to Milennium',1900,'en',NULL,NULL,1,4,NULL),
(94,87,'The Great Indian Novel',1900,'en',NULL,NULL,1,4,NULL),
(95,89,'O Jerusalem!',1900,'en',NULL,NULL,1,4,NULL),
(96,89,'The City of Joy',1900,'en',NULL,NULL,1,4,NULL),
(97,89,'Freedom at Midnight',1900,'en',NULL,NULL,1,4,NULL),
(98,47,'The Winter of Our Discontent',1900,'en',NULL,NULL,1,4,NULL),
(99,93,'On Education',1900,'en',NULL,NULL,1,4,NULL),
(100,94,'Free Will',1900,'en',NULL,NULL,1,4,NULL),
(101,87,'Bookless in Baghdad',1900,'en',NULL,NULL,1,4,NULL),
(102,96,'The Case of the Lame Canary',1900,'en',NULL,NULL,1,4,NULL),
(103,9,'The Theory of Everything',1900,'en',NULL,NULL,1,4,NULL),
(104,98,'New Markets & Other Essays',1900,'en',NULL,NULL,1,4,NULL),
(105,99,'Electric Universe',1900,'en',NULL,NULL,1,4,NULL),
(106,100,'The Hunchback of Notre Dame',1900,'en',NULL,NULL,1,4,NULL),
(107,47,'Burning Bright',1900,'en',NULL,NULL,1,4,NULL),
(108,98,'The Age of Discontuinity',1900,'en',NULL,NULL,1,4,NULL),
(109,103,'Doctor in the Nude',1900,'en',NULL,NULL,1,4,NULL),
(110,104,'Down and Out in Paris & London',1900,'en',NULL,NULL,1,4,NULL),
(111,72,'Identity & Violence',1900,'en',NULL,NULL,1,4,NULL),
(112,80,'Beyond the Three Seas',1900,'en',NULL,NULL,1,4,NULL),
(113,107,'Talking Straight',1900,'en',NULL,NULL,1,4,NULL),
(114,108,'Vol 3 Maughams Collected Short Stories',1900,'en',NULL,NULL,1,4,NULL),
(115,42,'The Phantom of Manhattan',1900,'en',NULL,NULL,1,4,NULL),
(116,108,'Ashenden of The British Agent',1900,'en',NULL,NULL,1,4,NULL),
(117,111,'Zen & The Art of Motorcycle Maintenance',1900,'en',NULL,NULL,1,4,NULL),
(118,112,'The Great War for Civilization',1900,'en',NULL,NULL,1,4,NULL),
(119,45,'We the Living',1900,'en',NULL,NULL,1,4,NULL),
(120,114,'The Artist and the Mathematician',1900,'en',NULL,NULL,1,4,NULL),
(121,93,'History of Western Philosophy',1900,'en',NULL,NULL,1,4,NULL),
(122,72,'Rationality & Freedom',1900,'en',NULL,NULL,1,4,NULL),
(123,117,'Clash of Civilizations and Remaking of the World Order',1900,'en',NULL,NULL,1,4,NULL),
(124,39,'Uncommon Wisdom',1900,'en',NULL,NULL,1,4,NULL),
(125,119,'One',1900,'en',NULL,NULL,1,4,NULL),
(126,120,'To Sir With Love',1900,'en',NULL,NULL,1,4,NULL),
(127,121,'Half A Life',1900,'en',NULL,NULL,1,4,NULL),
(128,122,'The Discovery of India',1900,'en',NULL,NULL,1,4,NULL),
(129,52,'Apulki',1900,'en',NULL,NULL,1,4,NULL),
(130,93,'Unpopular Essays',1900,'en',NULL,NULL,1,4,NULL),
(131,42,'The Deceiver',1900,'en',NULL,NULL,1,4,NULL),
(132,76,'Veil: Secret Wars of the CIA',1900,'en',NULL,NULL,1,4,NULL),
(133,52,'Char Shabda',1900,'en',NULL,NULL,1,4,NULL),
(134,128,'Rosy is My Relative',1900,'en',NULL,NULL,1,4,NULL),
(135,108,'The Moon and Sixpence',1900,'en',NULL,NULL,1,4,NULL),
(136,130,'A Short History of the World',1900,'en',NULL,NULL,1,4,NULL),
(137,108,'The Trembling of a Leaf',1900,'en',NULL,NULL,1,4,NULL),
(138,103,'Doctor on the Brain',1900,'en',NULL,NULL,1,4,NULL),
(139,133,'Simpsons & Their Mathematical Secrets',1900,'en',NULL,NULL,1,4,NULL),
(140,134,'Pattern Classification',1900,'en',NULL,NULL,1,4,NULL),
(141,135,'From Beirut to Jerusalem',1900,'en',NULL,NULL,1,4,NULL),
(142,133,'The Code Book',1900,'en',NULL,NULL,1,4,NULL),
(143,112,'The Age of the Warrior',1900,'en',NULL,NULL,1,4,NULL),
(144,138,'The Numbers Behind Numb3rs',1900,'en',NULL,NULL,1,4,NULL),
(145,47,'A Life in Letters',1900,'en',NULL,NULL,1,4,NULL),
(146,140,'The Information',1900,'en',NULL,NULL,1,4,NULL),
(147,141,'Elements of Information Theory',1900,'en',NULL,NULL,1,4,NULL),
(148,142,'Power Electronics - Rashid',1900,'en',NULL,NULL,1,4,NULL),
(149,143,'Power Electronics - Mohan',1900,'en',NULL,NULL,1,4,NULL),
(150,144,'Neural Networks',1900,'en',NULL,NULL,1,4,NULL),
(151,47,'The Grapes of Wrath',1900,'en',NULL,NULL,1,4,NULL),
(152,52,'Vyakti ani Valli',1900,'en',NULL,NULL,1,4,NULL),
(153,12,'Statistical Learning Theory',1900,'en',NULL,NULL,1,4,NULL),
(154,148,'Empire of the Mughal - The Tainted Throne',1900,'en',NULL,NULL,1,4,NULL),
(155,148,'Empire of the Mughal - Brothers at War',1900,'en',NULL,NULL,1,4,NULL),
(156,148,'Empire of the Mughal - Ruler of the World',1900,'en',NULL,NULL,1,4,NULL),
(157,148,'Empire of the Mughal - The Serpent's Tooth',1900,'en',NULL,NULL,1,4,NULL),
(158,148,'Empire of the Mughal - Raiders from the North',1900,'en',NULL,NULL,1,4,NULL),
(159,153,'Mossad',1900,'en',NULL,NULL,1,4,NULL),
(160,154,'Jim Corbett Omnibus',1900,'en',NULL,NULL,1,4,NULL),
(161,155,'20000 Leagues Under the Sea',1900,'en',NULL,NULL,1,4,NULL),
(162,156,'Batatyachi Chal',1900,'en',NULL,NULL,1,4,NULL),
(163,156,'Hafasavnuk',1900,'en',NULL,NULL,1,4,NULL),
(164,156,'Urlasurla',1900,'en',NULL,NULL,1,4,NULL),
(165,68,'Pointers in C',1900,'en',NULL,NULL,1,4,NULL),
(166,160,'The Cathedral and the Bazaar',1900,'en',NULL,NULL,1,4,NULL),
(167,161,'Design with OpAmps',1900,'en',NULL,NULL,1,4,NULL),
(168,162,'Think Complexity',1900,'en',NULL,NULL,1,4,NULL),
(169,163,'The Devil's Advocate',1900,'en',NULL,NULL,1,4,NULL),
(170,45,'Ayn Rand Answers',1900,'en',NULL,NULL,1,4,NULL),
(171,45,'Philosophy: Who Needs It',1900,'en',NULL,NULL,1,4,NULL),
(172,166,'Data Analysis with Open Source Tools',1900,'en',NULL,NULL,1,4,NULL),
(173,167,'Broca's Brain',1900,'en',NULL,NULL,1,4,NULL),
(174,168,'Men of Mathematics',1900,'en',NULL,NULL,1,4,NULL),
(175,169,'Oxford book of Modern Science Writing',1900,'en',NULL,NULL,1,4,NULL),
(176,170,'Judiciary and Democracy Justice',1900,'en',NULL,NULL,1,4,NULL),
(177,171,'The Arthashastra',1900,'en',NULL,NULL,1,4,NULL),
(178,172,'We the People',1900,'en',NULL,NULL,1,4,NULL),
(179,172,'We the Nation',1900,'en',NULL,NULL,1,4,NULL),
(180,174,'The Courtroom Genius',1900,'en',NULL,NULL,1,4,NULL),
(181,175,'Dongri to Dubai',1900,'en',NULL,NULL,1,4,NULL),
(182,176,'Foundation History of England',1900,'en',NULL,NULL,1,4,NULL),
(183,80,'City of Djinns',1900,'en',NULL,NULL,1,4,NULL),
(184,178,'India's Legal System',1900,'en',NULL,NULL,1,4,NULL),
(185,179,'More Tears to Cry',1900,'en',NULL,NULL,1,4,NULL),
(186,180,'The Ropemaker',1900,'en',NULL,NULL,1,4,NULL),
(188,182,'The Prince',1900,'en',NULL,NULL,1,4,NULL),
(189,183,'Eyeless in Gaza',1900,'en',NULL,NULL,1,4,NULL),
(190,184,'Tales of Beedle the Bard',1900,'en',NULL,NULL,1,4,NULL),
(191,185,'Girl with the Dragon Tattoo',1900,'en',NULL,NULL,1,4,NULL),
(192,185,'Girl who kicked the Hornet's Nest',1900,'en',NULL,NULL,1,4,NULL),
(193,185,'Girl who played with Fire',1900,'en',NULL,NULL,1,4,NULL),
(194,28,'Structure and Randomness',1900,'en',NULL,NULL,1,4,NULL),
(195,189,'Image Processing with MATLAB',1900,'en',NULL,NULL,1,4,NULL),
(196,104,'Animal Farm',1900,'en',NULL,NULL,1,4,NULL),
(197,70,'The Idiot',1900,'en',NULL,NULL,1,4,NULL),
(198,192,'A Christmas Carol',1900,'en',NULL,NULL,1,4,NULL);

-- Insercion de clientes

INSERT INTO `clients`(name, email, birthdate, gender, active) VALUES ('Maria Dolores Gomez','Maria Dolores.95983222[email protected]','1971-06-06','F',1),
('Adrian Fernandez','Adrian.55818851[email protected]','1970-04-09','M',1),
('Maria Luisa Marin','Maria Luisa.83726282[email protected]','1957-07-30','F',1),
('Pedro Sanchez','Pedro.78522059[email protected]','1992-01-31','M',1),
('Pablo Saavedra','Pablo.93733268[email protected]','1960-07-21','M',1),
('Marta Carrillo','Marta.55741882[email protected]','1981-06-15','F',1),
('Javier Barrio','Javier.54966248[email protected]','1971-04-24','M',1),
('Milagros Garcia','Milagros.90074144[email protected]','1964-12-05','F',1),
('Carlos Quiros','Carlos.63291957[email protected]','1954-08-28','M',1),
('Carmen De la Torre','Carmen.57408761[email protected]','1966-05-14','F',1),
('Laura Moron','Laura.57774589[email protected]','1954-03-02','F',1),
('Maria Dolores Larrea','Maria Dolores.71788005[email protected]','1990-09-04','F',1),
('Maria Dolores Sanz','Maria Dolores.30948169[email protected]','2001-08-30','F',1),
('Jose Maria Bermudez','Jose Maria.24963969[email protected]','1998-05-23','M',1),
('Carlos Blanco','Carlos.94783133[email protected]','1952-08-07','M',1),
('Juan Carlos Jurado','Juan Carlos.71650477[email protected]','1990-12-12','M',1),
('David Gonzalez','David.54332034[email protected]','1976-05-03','M',1),
('Antonia Aranda','Antonia.91560262[email protected]','1979-10-25','F',1),
('Maria Moreno','Maria.58935447[email protected]','1997-01-12','F',1),
('David Casals','David.06746883[email protected]','1999-07-13','M',1),
('Mario Romero','Mario.46091382[email protected]','1985-03-29','M',1),
('Maria angeles Alba','Maria angeles.91808919[email protected]','1959-09-14','F',1),
('Rafael Espinola','Rafael.67605541[email protected]','1998-10-11','M',1),
('Montserrat alvarez','Montserrat.31114289[email protected]','1994-11-06','F',1),
('Maria Carmen Gomez','Maria Carmen.64351051[email protected]','1968-06-30','F',1),
('Maria Cruz Morillas','Maria Cruz.81385695[email protected]','1978-10-29','F',1),
('Josefa Roldan','Josefa.51417560[email protected]','1993-08-09','F',1),
('Monica Pla','Monica.18992324[email protected]','1972-06-08','F',1),
('Juana Maria Lopez','Juana Maria.51072683[email protected]','1990-07-15','F',1),
('Maria Carmen Ponce','Maria Carmen.41619980[email protected]','1984-07-26','F',1),
('Juan Carlos Rios','Juan Carlos.45673504[email protected]','1967-05-04','M',1),
('Isabel Alfaro','Isabel.77316882[email protected]','1980-07-25','F',1),
('Maria Isabel Armas','Maria Isabel.42010289[email protected]','1950-11-21','F',1),
('Maria Teresa Castillo','Maria Teresa.91228389[email protected]','2002-11-08','F',1),
('Andres Planells','Andres.09981449[email protected]','1992-06-19','M',1),
('Silvia Perez','Silvia.91812407[email protected]','1969-02-15','F',1),
('Pablo Gonzalez','Pablo.11605676[email protected]','2000-10-11','M',1),
('Maria Antonia Jimenez','Maria Antonia.98071058[email protected]','1998-06-23','F',1),
('Jesus Rodriguez','Jesus.86679475[email protected]','1961-01-17','M',1),
('Carmen Rodriguez','Carmen.81799536[email protected]','1973-02-17','F',1),
('Maria Dolores Rodriguez','Maria Dolores.75444599[email protected]','1962-08-14','F',1),
('Jordi Campos','Jordi.76000917[email protected]','1956-09-23','M',1),
('Carlos Caceres','Carlos.97628163[email protected]','1993-05-16','M',1),
('Carmen Robles','Carmen.29258188[email protected]','1955-06-19','F',1),
('Sara Rodriguez','Sara.16181250[email protected]','2001-06-07','F',1),
('Maria Carmen Rivera','Maria Carmen.59955426[email protected]','2000-05-27','F',1),
('Alberto Cabanas','Alberto.40633755[email protected]','1991-10-27','M',1),
('Jose Sanchez','Jose.52243847[email protected]','1976-12-06','M',1),
('Isabel Martinez','Isabel.90843261[email protected]','1962-07-01','F',1),
('David Sanchez','David.14910073[email protected]','1975-05-18','M',1),
('Sergio Sebastian','Sergio.09345984[email protected]','1959-08-30','M',1),
('Manuel Cabrera','Manuel.38738750[email protected]','1993-08-23','M',1),
('Marina Gabaldon','Marina.12101665[email protected]','1959-03-25','F',1),
('Rafael Galvez','Rafael.87947175[email protected]','1988-09-02','M',1),
('Francisco Villar','Francisco.13922268[email protected]','1952-04-25','M',1),
('Francisco Garcia','Francisco.34242509[email protected]','1989-01-22','M',1),
('Esther Pina','Esther.36300729[email protected]','1977-11-07','F',1),
('Maria Jesus Noya','Maria Jesus.95839533[email protected]','1996-08-07','F',1),
('Paula Ropero','Paula.53630073[email protected]','1998-09-04','F',1),
('Maria Carmen Iglesias','Maria Carmen.24044144[email protected]','1977-06-12','F',1),
('Albert Galvez','Albert.10067957[email protected]','1971-05-17','M',1),
('Carmen Lopez','Carmen.09399409[email protected]','1987-03-07','F',1),
('Francisco Jose Leon','Francisco Jose.07598657[email protected]','1965-12-11','M',1),
('Francisca Gonzalez','Francisca.19675393[email protected]','1957-12-23','F',1),
('Daniel Garcia','Daniel.01386486[email protected]','1979-05-29','M',1),
('Ana Maria Martinez','Ana Maria.91340418[email protected]','1980-09-14','F',1),
('Maria Aguilar','Maria.41749884[email protected]','2000-07-31','F',1),
('oscar Penas','oscar.31681177[email protected]','1981-10-02','M',1),
('Adrian Vela','Adrian.66561884[email protected]','1978-12-10','M',1),
('Francisco Alcalde','Francisco.52899588[email protected]','1967-03-11','M',1),
('Maria Dolores Perez','Maria Dolores.47800073[email protected]','2003-11-10','F',1),
('Juan Jose Tejada','Juan Jose.30429668[email protected]','1990-06-15','M',1),
('Cristobal Nogues','Cristobal.01001763[email protected]','2003-10-01','M',1),
('Maria Luisa Sanchez','Maria Luisa.91748033[email protected]','2000-02-03','F',1),
('Adrian Orta','Adrian.11458937[email protected]','1952-03-20','M',1),
('Maria Pilar Martin','Maria Pilar.93607154[email protected]','1996-08-29','F',1),
('Jesus Perez','Jesus.91931655[email protected]','1954-06-01','M',1),
('Jesus Perez','Jesus.15757299[email protected]','1956-08-29','M',1),
('Esther Capdevila','Esther.96440550[email protected]','1970-10-12','F',1),
('David Nieves','David.40697907[email protected]','1965-04-02','M',1),
('Antonia Giron','Antonia.32080105[email protected]','1983-01-22','F',1),
('Juan Casero','Juan.94063877[email protected]','1974-06-29','M',1),
('Manuel De Pablo','Manuel.01279669[email protected]','1973-03-23','M',1),
('angel Palomo','angel.28890315[email protected]','1991-07-04','M',1),
('Laura Herrera','Laura.98555932[email protected]','1966-12-12','F',1),
('Maria Josefa Benitez','Maria Josefa.36743977[email protected]','1987-04-17','F',1),
('Luis Saez','Luis.08103734[email protected]','1983-03-28','M',1),
('Susana Nevado','Susana.09442372[email protected]','1961-12-26','F',1),
('Miguel Gomez','Miguel.01631964[email protected]','1965-05-16','M',1),
('Julio Mayordomo','Julio.77582185[email protected]','1968-06-05','M',1),
('Sonia Mari','Sonia.06246888[email protected]','1994-10-13','F',1),
('Antonia Beltran','Antonia.96371304[email protected]','1967-11-17','F',1),
('Mercedes Perez','Mercedes.80944345[email protected]','1958-11-05','F',1),
('Concepcion Velez','Concepcion.56896097[email protected]','1964-04-05','F',1),
('Diego Correa','Diego.44862413[email protected]','1999-09-15','M',1),
('Juan Antonio Galan','Juan Antonio.95710220[email protected]','1982-11-20','M',1),
('Manuel Cerezo','Manuel.25853412[email protected]','1991-03-12','M',1),
('Rosa Maria Singh','Rosa Maria.41642169[email protected]','1956-12-31','F',1),
('Angeles Mena','angeles.88859550[email protected]','1987-09-22','F',1),
('Jose Hidalgo','Jose.05903641[email protected]','1973-08-13','M',1);

-- Comando on duplicate 
/* 🚫Nunca usa ON DUPLICATE KEY IGNORE ALL⛔
PROHIBIDISIMOOOOOOOOOOOOOOOOOOOO */

INSERT INTO clients(name, email, birthdate, gender, active) 
VALUES('Royer G', 'royjuni3431@gmail.com', '2002-02-16', 'M', 0)
ON DUPLICATE KEY IGNORE All;

INSERT INTO clients(name, email, birthdate, gender, active) 
VALUES('Royer G', 'royjuni3431@gmail.com', '2002-02-16', 'M', 0)
ON DUPLICATE KEY UPDATE active = VALUES(active);

-- Truco para la presentacion de los datos \G (Mas legible)
SELECT * FROM clients WHERE client_id = 101\G
SELECT * FROM clients\G

-- Queries anidados
/*
El laberinto de la Soledad, Octavio Paz, 1952
Vuelta al Laberinto de la Soledad, Octavio Paz, 1960
*/

INSERT INTO books(title, author_id)
VALUES('El Laberinto de la Soledad', 6)

INSERT INTO books(title, author_id, year)
VALUES ('Vuelda al Laberinto de la Soledad',
  (SELECT author_id FROM authors
   WHERE name = 'Octavio Paz' LIMIT 1),
   1960
);

-- Como realizar el shema y la insercion de la data desde un arrivo(s)
-- Creacion de esquema 
/* bash $ mysql -u root -p < all_shema.sql  */
-- Inserccion de datos
/* bash $ mysql -u root -p -D platzi_operation < all_data.sql */

-- Su majestad SELECT 👑 [Tablas Dinamicas - Temporales]
SELECT * FROM clients;
SELECT name FROM clients;
SELECT name, email, gender FROM clients;
-- Limitando los resultados
SELECT name, email, gender FROM clients LIMIT 10;
-- Condicionando la consulta
SELECT name, email, gender FROM clients WHERE gender = 'M';
SELECT birthdate FROM clients;
-- Usando funciones de Mysql
SELECT YEAR(birthdate) FROM clients;
SELECT name, YEAR(NOW()) - YEAR(birthdate) FROM clients LIMIT 10;
-- Condicionando con LIKE
SELECT * FROM clients WHERE name LIKE '%Saave%';
-- Consutas mas complejas
SELECT name, email, YEAR(NOW()) - YEAR(birthdate) AS 'edad', gender FROM clients WHERE gender = 'F' AND name LIKE '%Lop%'; 

-- Extraer la hora de mi pc usando MYSQL
SELECT NOW();
SELECT NOW(YEAR());

Como necesito limpiar pantalla en el cmd!!!ja

Comprendido.

SELECT name, email, gender FROM clients WHERE gender = 'f' ;
SELECT birthdate FROM clients WHERE gender = 'f' ;
SELECT YEAR(birthdate) FROM clients WHERE gender = 'f' ; 
SELECT NOW(),YEAR(birthdate) FROM clients WHERE gender = 'f' ;
SELECT YEAR(NOW()),YEAR(birthdate) FROM clients WHERE gender = 'f' ;
SELECT YEAR(NOW()) - YEAR(birthdate) AS EDAD FROM clients WHERE gender = 'f' ;
SELECT name,YEAR(NOW()) - YEAR(birthdate) AS EDAD FROM clients WHERE gender = 'f' LIMIT 10;
SELECT name,YEAR(NOW()) - YEAR(birthdate) AS EDAD, birthdate FROM clients WHERE name LIKE '%saave%';
SELECT name,email,YEAR(NOW()) - YEAR(birthdate) AS EDAD, birthdate, gender FROM clients WHERE gender = 'F' and name LIKE '%lop%';

Lenguaje DML
Lenguaje de manipulación de datos proporcionado por los sistemas gestores de base de datos que permite tareas de consultas o modificación de datos.

Select

select * from table where field = 'value';

Insert

insert into table (field1, field2) values (value1, value2);

Update

udate table field = 'value to modify' where field = value;

Delete

delete from table where field = value;

1. Con select lo que se hace es crear tablas temporales que se mostrarán en un momento dado selecciona todos los campos de clientes.

2. Select es la herramienta que se utiliza para sacar los datos de las tablas y convertir en esos datos en información.

3. Traer toda la información de la tabla no es necesariamente útil, es muy poco probable que sólo una tabla contenga toda la información que requerimos para contestar a ciertas preguntas que nos exige el modelo de negocios.

4. Realizar un DESC name_tabla nos da una noción como es la estructura de la tablas y después realizar un SELECT * FROM name_tabla LIMIT 10 nos da una noción de cómo se guardan los datos. Esto es muy funcional cuando la bases de datos no la hemos construido nosotros.

5. la esencia del SELECT lo es crear tablas temporales que se mostrarán en un momento dado con todos los campos que deseamos mostrar.

A summary of SQL and the structure of and SQL Statement.

SQL: Structured Query Language. It´s a language used to access and manipulate data stored in tables. Each table contains columns for the different fields, and rows for the diferrent records.

• Structure of an SQL statement:

SQL statement is composed of an ordener list of clauses such as SELECT, FROM, WHERE, etc. And must always end with a semicolon (;)

• Select all data in a table

SELECT *
FROM employees;

• Select specific columns

SELECT name, age
FROM students;

• Select without duplicate rows

SELECT DISTINCT name
FROM students;

• Sort rows

SELECT name, age
FROM friends
ORDER BY name, age DESC; -- ASC is the default 

The statement above displays names and ages of friends sorted alphabetically by name in ascending order and when two friends share the same name, the rows are sorted by age in descending order.

• Limit the number of rows

SELECT name, grade
FROM course_grades
ORDER BY grade DESC
LIMIT 5;

• Filter rows

SELECT product
FROM inventory
WHERE code = 'ABC123';

The WHERE clause states a conditions to hlod true for all the rows to be returned. The query above will only sjow the products whose code is ABC123.

• Conditions: numeric comparison

SELECT product
FROM inventory
WHERE amount <= 10;

Columns or fields that contain numeric values can be used in conditions with math comparison operators such as:

=, <, <=, >=, <>

• Conditions: string comparison

SELECT productName
FROM inventory
WHERE productName < 'B';

The query above shows all products whose name will star with the letter A (name comes before ‘B’ in dictionary oder)

Note that string values in the condition should be put between single quotes. Also note that any uppercase letter is smaller any lovercase letter.

• Conditions: list possible values

SELECT product
FROM inventory
WHERE amount IN (1,5,10);

If a columns can contain one of several possible values, you can filter them by using the IN o NOT IN operators.

The query above shows all product that have either 1,5 or 10 units left in the inventory.

• Condition: inclusive ranges

SELECT product
FROM inventory
WHERE amount BETWEEN 5 AND 9;

To math rows with values within a fiven range, use BETWEEN operator. It works for numbers, strings, dates and time stamps.

To match rows with values outside of a given range, use NOT BETWEEN.

-- This expression
amount BETWEEN 5 AND 9 

-- is esquivalent to this one
amount >= 5 AND amount <= 9

• Conditions: combining with AND

SELECT product
FROM inventory
WHERE amount < 5
	AND price > 1;

• Conditions: Combining with OR

SELECT product
FROM inventory
WHERE amount < 5
	OR price > 1;

• Conditions: mixing AND and OR

SELECT product
FROM inventory
WHERE amount < 5 
OR name = 'paper'
AND price > 1;

When using both AND and OR. It´s important to know that AND has higher precedence. That means that like in maths where multiplication is evaluated before addition, the AND will be evaluated before the OR.

The query above mathces all products that are named paper whose price is algo higher than 1. In addition to products that have more than 5 units left in the inventory.

If we want the OR to be evaluated first, we can use brackets().

SELECT product
FROM inventory
WHERE (amount < 5 
OR name = 'paper')
AND price > 1;

This query will matches all products that have more than 5 units or named paper, and in both cases the price has to be higher than 1.

Les comparto algunos comandos ùtiles con SELECT

seleccion           SELECT name FROM authors WHERE nationality='ENG';
                    SELECT name, nationality FROM authors WHERE nationality='USA';
                    SELECT name, nationality FROM authors LIMIT 10;
                    SELECT YEAR(birthdate) FROM clients;
                    SELECT YEAR(NOW()) ---SELECCIONA EL AÑO ACTUAL
                    SELECT YEAR(NOW()) - YEAR(birthdate) as edad FROM clients --SELECIONA LA EDAD
                    SELECT name, YEAR(NOW()) - YEAR(birthdate) FROM clients
                    SELECT * FROM clients WHERE name like '% saave%'

sel por operador    SELECT * FROM booksWHERE precio <= 200
                    SELECT name, nationality FROM booksWHERE precio BETWEEN 200 AND 500
                    SELECT * FROM tabla WHERE precio BETWEEN 200 AND 500
                    SELECT * FROM authors WHERE nationality = 'MEX' OR 'USA';
                    SELECT * FROM authors WHERE nationality = 'MEX' AND 'USA';

concatenar          SELECT CONCAT(name, ' ',nationality) FROM authors WHERE nationality='USA';
                    SELECT CONCAT_WS(' ' ,name ,nationality) FROM authors WHERE nationality='USA';
                    
contador            SELECT nationality, COUNT(*) FROM tabla GROUP BY nationality;
                   
ordenar             SELECT * FROM tabla ORDER BY nationality ASC;

promedio            SELECT AVG(precio) FROM inventario WHERE modelo_auto='chebrolet'

sel menor precio    SELECT * FROM inventario WHERE precio= (SELECT MIN(precio) FROM inventario)
                 
sel mayor precio    SELECT * FROM inventario WHERE precio= (SELECT MAX(precio) FROM inventario)

Extraer clientes menores de 50 año

select name 'NOMBRE', year(Now()) - year(birthdate) 'AÑOS', birthdate 'FECHA NACIMIENTO' from clients
where year(Now()) - year(birthdate) < 50;

Para aquellos que usan SQL Server pueden usar TOP, y en MySQL Y PostgreSQL usas LIMIT.

Una pregunta, es mejor operar en el mismo SELECT (sumas u tras operaciones como las que hace al sacar los años) o hacerlo en el mismo lenguaje de programación??

SELECT name, email, YEAR(NOW()) - YEAR(birthdate) AS edad, gender
FROM clients
WHERE gender = 'F'
AND name LIKE = '%lop%';

Complementando el comentario de deddyibanez en el que pregunta si es mejor usar las funciones agregadas de SQL en el Query, a hacer el Query y luego realizar las operaciones en el lenguaje de programación que se esté utilizando, qué pasa si se está trabajando en lenguajes con librerias enfocadas a data science como Python o R? ¿Qué experiencias han tenido? ¿Es preferible usar las alternativas de GROUP BY y JOIN que uno encuentra por ejemplo en el tidyverse o data.table de R, que usarlos en SQL?

En este momento Pablo Saavedra tiene 60 años

Una consulta colocando funciones como condición

SELECT name, YEAR(NOW()) - YEAR(birthdate) AS edad FROM clients WHERE gender = 'M' AND YEAR(NOW()) - YEAR(birthdate)  >= 58;

Un query mas completo

SELECT name, email, YEAR(NOW()) - YEAR(birthdate) AS edad, gender FROM clients
WHERE gender = 'F'
AND
name LIKE '%er%'
AND
YEAR(NOW()) - YEAR(birthdate) <= 40; ```

SELECTname, YEAR(NOW()) - YEAR(birthdate)
FROM clients 
WHEREYEAR(NOW()) - YEAR(birthdate) < '20'
limit20;

Me encanta ❤️

Esto me recuerda a los primeros pasos en ETL en el proceso de transformación. Muy bueno,

Me esta fascinando!!

SELECT: crea tablas dinámicas según nuestras necesidades.

Existen una pautas para facilitar la formulación de un Query, o dicho de otra forma para traducir una consulta a lenguaje SQL:

Qué quiero mostrar: SELECT

De donde tomare los datos: FROM

Como voy a filtrar esa información: WHERE

Si deseo ordenarla, desde que rubro me voy a apoyar: ORDER BY

Los filtros que deseo tengan mis datos: HAVING, LIKE

Para quienes quieran poblar la tabla transactions aleatoreamente con datos de las otras tablas

INSERT INTO transactions(book_id,client_id,type)
SELECT books.book_id,clients.client_id,'sell'
FROM books,clients ORDER BY RAND() LIMIT 5;

Ahi se guardaran 5 registros, cambien el 5 por la cantidad de registros que uieran guardar.

hola compañeros, les dejo mi script por si tuvieron algun problema

No paren de aprender

SELECT: practicamente crea tablas dinámicas según las condiciones que ponemos. Una herramienta poderosa

le tenia miedo al sql, por eso aprendi mongodb que es facil, pero ahora con esto 🤩 … todo cambiara !!

Un resumen de los usos del WHERE

Un pequeño resumen del comando SELECT

Saludos desde el pasado jajajajajaja!!! me encanta su estilo.

este es el código de la clase, noten que now - birthday no es el cálculo exacto de la edad:

-- seleccionar todo (*)
select * from clients;
-- describir table cients
desc clients;
-- seleccionar algunas columnas
select `name`, `email`, `gender` from clients;
-- limitar a 10 resultados
select `name`, `email`, `gender` from clients limit 10;
-- traer datos de forma condicional
select `name`, `email`, `gender` from clients where `gender` = 'F';
-- seleccionar el año de nacimiento con la función year
select year(birthdate) from clients;
-- seleccionar el nombre y un cálculo aproximado de la edad de clients
select `name`, year(now()) - year(birthdate) from clients limit 10;
-- se muestra la keyword like que hace match de un substring en la columna name
select * from clients where `name` like '%Saave%';
-- se muestra la capacidad de poner aliases a las  columnas
select `name`, `email`, year(now()) - year(`birthdate`) as `aprox_age`, `gender`
from clients
where gender = 'F' and name like '%Lop%';

Interesante las clases la verdad me ha gustado y he aprendido

CREATE TABLE `authors` (
  `author_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `nationality` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`author_id`),
  UNIQUE KEY `uniq_author` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=193 DEFAULT CHARSET=utf8;

INSERT INTO `authors` VALUES (1,'Sam Altman','USA'),
(2,'Freddy Vega','COL'),
(3,'Arthur Conan Doyle','GBR'),
(4,'Chuck Palahniuk','USA'),
(5,'Juan Rulfo','MEX'),
(6,'Henning Mankel','SWE'),
(7,'Jaideva Goswami','USA'),
(8,'John Foreman','USA'),
(9,'Stephen Hawking','USA'),
(10,'Stephen Dubner','USA'),
(11,'Edward Said','USA'),
(12,'Vladimir Vapnik','RUS'),
(13,'V P Menon','IND'),
(14,'Leonard Mlodinow','USA'),
(15,'Frank Shih','JAP'),
(16,'Maria Konnikova','RUS'),
(17,'Sebastian Gutierrez','ESP'),
(18,'Kurt Vonnegut','USA'),
(19,'Cedric Villani','FRA'),
(20,'Gerald Sussman','USA'),
(21,'Abraham Eraly','IND'),
(22,'Frank Kafka','AUT'),
(23,'John Pratt','USA'),
(24,'Robert Nisbet','USA'),
(25,'H. G. Wells',"ENG"),
(26,'Werner Heisenberg','DEU'),
(27,'Andy Oram',NULL),
(28,'Terence Tao',"AUS"),
(29,'Drew Conway',"USA"),
(30,'Nate Silver',"USA"),
(31,'Wes McKinney',"USA"),
(32,'Thomas Cormen', "USA"),
(33,'Siddhartha Deb',"IND"),
(34,'Albert Camus',"FRA"),
(37,'Adam Smith',"ENG"),
(38,'Ken Follett',"ENG"),
(39,'Fritjof Capra',"AUT"),
(40,'Richard Feynman',"USA"),
(41,'Ernest Hemingway',"USA"),
(42,'Frederick Forsyth',"ENG"),
(43,'Jeffery Archer',"ENG"),
(44,'Randy Pausch',"USA"),
(45,'Ayn Rand',"RUS"),
(46,'Michael Crichton',"USA"),
(47,'John Steinbeck',"USA"),
(48,'Edgar Allen Poe',"USA"),
(51,'Will Durant',NULL),
(52,'P L Deshpande',NULL),
(56,'John Grisham',"USA"),
(57,'V. S. Naipaul',NULL),
(58,'Joseph Heller',NULL),
(59,'BBC',NULL),
(60,'Bob Dylan',"USA"),
(61,'Madan Gupta',"IND"),
(62,'Alfred Stonier',NULL),
(63,'W. H. Greene',NULL),
(64,'Gary Bradsky',NULL),
(65,'Andrew Tanenbaum',NULL),
(66,'David Forsyth',NULL),
(67,'Schilling Taub',NULL),
(68,'Yashwant Kanetkar',NULL),
(69,'Jonathan Stroud',NULL),
(70,'Fyodor Dostoevsky',"RUS"),
(71,'Dan Brown',"USA"),
(72,'Amartya Sen',NULL),
(73,'Amitav Ghosh',NULL),
(75,'Lorraine Hansberry',NULL),
(76,'Bob Woodward',NULL),
(78,'Kuldip Nayar',NULL),
(79,'Sunita Deshpande',NULL),
(80,'William Dalrymple',NULL),
(81,'Various',NULL),
(85,'Sanjay Garg',NULL),
(86,'V P Kale',NULL),
(87,'Shashi Tharoor',"IND"),
(89,'Dominique Lapierre',NULL),
(93,'Bertrand Russell',"ENG"),
(94,'Sam Harris',NULL),
(96,'Earle Stanley Gardner',NULL),
(98,'Peter Drucker',NULL),
(99,'David Bodanis',NULL),
(100,'Victor Hugo',"FRA"),
(103,'Richard Gordon',NULL),
(104,'George Orwell',NULL),
(107,'Lee Iacoca',"USA"),
(108,'William S Maugham',NULL),
(111,'Robert Pirsig',NULL),
(112,'Robert Fisk',NULL),
(114,'Amir Aczel',NULL),
(117,'Samuel Huntington',NULL),
(119,'Richard Bach',NULL),
(120,'Braithwaite',NULL),
(121,'V S Naipaul',NULL),
(122,'Jawaharlal Nehru',NULL),
(128,'Gerald Durrell',NULL),
(133,'Simon Singh',"ENG"),
(134,'Hart Duda',NULL),
(135,'Thomas Friedman',NULL),
(138,'Keith Devlin',NULL),
(140,'James Gleick',NULL),
(141,'Joy Thomas',NULL),
(142,'Muhammad Rashid',NULL),
(143,'Ned Mohan',NULL),
(144,'Simon Haykin',NULL),
(148,'Alex Rutherford',NULL),
(153,'Michael Baz-Zohar',NULL),
(154,'Jim Corbett',NULL),
(155,'Jules Verne',NULL),
(156,'Deshpande P L',NULL),
(160,'Eric Raymond',NULL),
(161,'Sergio Franco',NULL),
(162,'Allen Downey',NULL),
(163,'Morris West',NULL),
(166,'Phillip Janert',NULL),
(167,'Carl Sagan',"USA"),
(168,'E T Bell',NULL),
(169,'Richard Dawkins',NULL),
(170,'Sudhanshu Ranjan',"IND"),
(171,'Kautiyla',NULL),
(172,'Palkhivala',NULL),
(174,'Sorabjee',NULL),
(175,'Hussain Zaidi',NULL),
(176,'Peter Ackroyd',NULL),
(178,'Nariman',NULL),
(179,'Jean Sassoon',NULL),
(180,'Peter Dickinson',NULL),
(182,'Machiavelli',NULL),
(183,'Aldous Huxley',"ENG"),
(184,'J K Rowling',"ENG"),
(185,'Steig Larsson',"SWE"),
(189,'Steve Eddins', NULL),
(192,'Charles Dickens',"ENG");

Use platzidatabase;
Select * from platziestudiantes where estudiantes_opinion= ‘No me gusta platzi’;
Empy set (0.00 sec)

Buenas noches pregunta ¿con que combinación de teclas en windows 10 se limpia la pantalla? yo lo hago con mysql> system clear; pero hay una forma mas rápida?

Gracias.

Disculpen, es necesario relacionar las tablas? es decir, usar el “references”? Gracias por los videos, sirven para practicar 😄

Limit, que palabra más importante.

select name, email, YEAR(NOW()) - YEAR(birthdate) AS edad, gender FROM clients WHERE gender = 'M' AND name like '%pablo%';

Alguna persona me puede asesorar ya que este Query no me funciona no se si sea por ’ '.
SELECT name,email, YEAR(NOW()) - YEAR(birthdate) AS edad, gender
FROM clients
WHERE gender = 'F’
AND name LIKE '%Lop%'
select * from clients where name like ‘%Sanche%’;

Gracias

Poderoso

Excelente clase, me gusto mucho.

Función year() para datetime

NOW(): Nos da la fecha y hora actual.

¿Se pueden usar expresiones regulares directamente en los queries de MySQL?

Muy bueno todo, aprendiendo cada día mas. Gracias!!

listo inserte la base de datos en xampp
me fui hasta el bin de mysql abri la consola y puse los datos hasta -p y hay seleccione el archivo y lo arrastre, y boala cargo el schema.sql

BUENAS CLASES

Excelente aportes

SELECT name, email, YEAR(NOW()) - YEAR(birthdate) AS edad FROM clients WHERE gender = ‘M’ AND name LIKE ‘%pe%’ LIMIT 15;

buen video

Entiendo todo re bien

SELECT
  (YEAR(NOW()) - YEAR(birthdate)) AS edad
FROM
  clients;

Esto les sirve para cambiar el nombre de la columna al hacer la consulta, si queremos presentar la información de manera óptima los alias son la herramienta perfecta.

Pablo Saavedra tiene 60 años :""")

select name as Nombre, email as E-mail, year(now()) - year(birthdate) as Edad,
gender as Género from clients where gender = ‘f’ and name like ‘%lop%’;

Comparto un ejemplo de mi ultimo query realizado

SELECT name AS Nombre, gender AS Género, active AS Activo, YEAR(NOW())-year(birthdate) AS Edad FROM clients WHERE name LIKE '%Maria%' AND gender = 'F'

SELECT column1, column2, ...
FROM table_name;
SELECT * FROM table_name;```

insert into transactions(book_id, client_id, type, created_at)
Values (1,4,‘sell’,“2005-06-12”),
(2,8,‘sell’,“2009-05-09”),
(3,6,‘lend’,“2006-05-30”),
(3,19,‘sell’,“2012-12-18”),
(23,76,‘lend’,“2009-12-23”),
(4,57,‘sell’,“2012-01-19”),
(7,23,‘sell’,“2021-05-29”),
(23,9,‘sell’,“2017-11-16”),
(9,17,‘lend’,“2013-03-05”),
(10,5,‘sell’,“2017-12-07”),
(11,27,‘sell’,“2015-05-08”),
(12,3,‘lend’,“2014-11-04”),
(13,72,‘sell’, “2019-10-04”),
(14,7,‘sell’,“2017-12-03”),
(15,65,‘lend’,“2020-07-20”),
(16,32,‘sell’,“2014-05-06”),
(17,54,‘lend’,“2008-03-03”),
(18,2,‘lend’,“2021-05-07”),
(19,1,‘sell’,“2016-09-06”),
(20,37,‘lend’,“2019-04-12”);

**REGEXP ** y **LIKE **se usan en casos totalmente diferentes.

**LIKE ** se usa para agregar comodines a una cadena, mientras que **REGEXP ** se usa para hacer coincidir un atributo con expresiones regulares.

Yo asustada porque en mi bd los clinetes no tenían la misma edad que en la de Alberto y es porque soy del futuro xD

Ya me había asustado del porque no me salía la edad que aparece en la clase, y claro pues si ya estamos 2022 jajajaj

Funciones en SQL

Existen funciones que pueden facilitar las tareas. Permitiendo crear tablas temporales dependiendo de lo que necesitemos.

Ejemplo:

-- Listar el año de nacimientos de los clientes, con la función YEAR()
SELECT YEAR(birthdate) FROM clients;

-- Mostrar el año actual
SELECT YEAR(NOW());

-- Listar los 10 primeros resultados de las edades de los clientes
SELECT YEAR(NOW()) - YEAR(birthdate) FROM clients LIMIT 10;

-- Listar nombre y edad de los 10 primeros clientes
SELECT name, YEAR(NOW()) - YEAR(birthdate) FROM clients LIMIT 10;

-- Listar clientes que coincidan con el nombre de "Saave"
SELECT * FROM clients WHERE name LIKE '%Saave%';

-- Listar clientes (nombre, email, edad y género). con filtro de genero = F y nombre que coincida con 'Lop'
--Usando alias para nombrar la función como 'edad'
SELECT name, email, YEAR(NOW()) - YEAR(birthdate) AS edad, gender FROM clients WHERE gender = 'F' AND name LIKE '%Lop%';

Como podemos ver estamos sacando la tabla ‘edad’ incluso cuando esta no existe en nuestra BD (Base de datos).

¿Se puede usar expresiones irregulares con la funcion “like” en MySql?

También puedes realizar consultas consultas second, minute, hour

excelente curso

Mi clase favorita!!!

Que buen curso :

Muy buena clase

es interesante, pero personalmente lo necesito ver varias veces para comprender los temas tratados aqui, es muy bueno explicando

Buena clase

Empecemos a hacer Queries

la esencia del SELECT lo es crear tablas temporales que se mostrarán en un momento dado con todos los campos que deseamos mostrar.

Traer toda la información de la tabla no es necesariamente útil, es muy poco probable que sólo una tabla contenga toda la información que requerimos para contestar a ciertas preguntas que nos exige el modelo de negocios.

SELECT * FROM clients WHERE name like '%Carlos%'; la función like permite buscar texto que se parezcan. Con el carácter % indicamos en que posición buscar el texto. palabra% indica antes, %palabra indica despues, %palabra% indica entre

Select es la herramienta que se utiliza para sacar los datos de las tablas y convertir en esos datos en información.

Traer toda la información de la tabla no es necesariamente útil, es muy poco probable que sólo una tabla contenga toda la información que requerimos para contestar a ciertas preguntas que nos exige el modelo de negocios.

SELECT name, YEAR(birthdate) FROM clients; Existen múltiples funciones en MySQL en el caso de year() sirve para los tipos de datos DATETIME y TIMESTAMP y trae solamente el año de la fecha

SELECT year(now()); se pueden mezclar las funciones para ayudarnos a mostrar datos que proveen más información un ejemplo de ello es la anterior sentencia que nos ayuda a ver en qué año estamos.

SELECT * FROM name_table LIMIT 10; LIMIT permite limitar la cantidad de registros por consultas, hay que destacar que LIMIT no traer tuplas en ningún orden específico.

SELECT NOW(); muestra la fecha actual

SELECT name_column_1, name_column_2 FROM name_table; podemos indicarle al select que columnas traer para mostrar información.

Realizar un DESC name_tabla nos da una noción como es la estructura de la tablas y después realizar un SELECT * FROM name_tabla LIMIT 10 nos da una noción de cómo se guardan los datos. Esto es muy funcional cuando la bases de datos no la hemos construido nosotros.

SELECT * FROM `clients` WHERE `gender`= 'M' LIMIT 10; La palabra reservada WHERE nos permite establecer condiciones para filtrar datos y escoger que tipo de información vamos a mostrar.

SELECT * FROM name_table; con esta sentencia estamos indicando que vamos a traer toda la información de una tabla específica, y con el * indicamos que deben ser todos los campos de la tabla

SELECT name, email, YEAR(NOW()) – YEAR(birthdate) AS Edad, gender
FROM clients
Where GENDER = “F”
AND name LIKE “%Lop%”;

Limit siempre va al final del query

Excelente clase sigo aprendiendo.

¿Cuál es el comendo para limpiar pantalla en Windows?

cls para windows limpiar pantalla
cd + nombreDirectorio para cambiar de directorio
cd … para regresar al directorio anterior
dir para listar todos directorios o carpetas existentes en la ruta donde estás.

Un aporte de un query que se me ocurrió en el transcurso de la clase, combinando nested queries (consultas anidadas), SELECT y los filtros que podemos aplicar.

SELECT name , edad FROM( SELECT name , YEAR(NOW()) - YEAR(birthdate) edad FROM clients LIMIT 10 ) new_table
WHERE edad > 49;

Se puede utilizar alias en la columna de operación utilizando AS <nombre_columna>

Resumen de la Clase:
Select: herramienta modular más importante.
- Where: condición para filtrado
- Limit: condición para limitar cantidad de resultados que trae el query.
- Like: función de cercanía de texto. Usa % como wildcard. Ej: ‘%Lop$’
- AS: renombra la columna resultante.

Código de la clase:

-- Clase 13 Modulo Select

select * from clients;
select name from clients;
select name, email, gender from clients;
select name, email, gender from clients limit 10;
select name, email, gender from clients where gender ='M';
SELECT name, email, gender FROM clients WHERE gender ='M';
SELECT birthdate FROM clients;
SELECT year(birthdate) FROM clients;
SELECT now();
SELECT year(now());
SELECT year(now()) - YEAR(birthdate) FROM clients LIMIT 10;
SELECT name, year(now()) - YEAR(birthdate) FROM clients LIMIT 10;
SELECT * FROM clients where name like '%Saave%';
SELECT name, email, YEAR(NOW()) - YEAR(birthdate) AS edad, gender 
FROM clients 
WHERE gender = 'F' 
    AND name LIKE '%Lop%';```

Si queremos hacer una query usando un Alias, debemos utilizar la palabra HAVING, en lugar de where:

SELECT name, YEAR(NOW()) - YEAR(birthdate) AS age FROM clients HAVING age  > 40 AND name LIKE 's%';

ajajajaj saludos desde el pasado

En la base de datos books hay unos titulos que suenan muy interesantes.

Saludos desde el futuro: Hoy 19/11/2020 09:51 am

<SELECT now();>

a todos saludos desde el pasado