No tienes acceso a esta clase

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

Curso de SQL y MySQL

Curso de SQL y MySQL

Alberto Alcocer

Alberto Alcocer

Su majestad el SELECT

15/24
Recursos

Aportes 130

Preguntas 13

Ordenar por:

Los aportes, preguntas y respuestas son vitales para aprender en comunidad. Reg铆strate o inicia sesi贸n para participar.

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%';

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

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

Saludos desde el pasado!

Como necesito limpiar pantalla en el cmd!!!ja

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].names','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());

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%';

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 鈥楤鈥 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.

  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.

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;

Hola, desde windows, copie el archivo all_squema.sql a la ruta C:\Program Files\MySQL\MySQL Server 8.0\bin

y desde la consola puse
source all_shema.sql

y voul谩!
https://www.librebyte.net/gnulinux/mysql-ejecutar-script-sql/

Un resumen de los usos del WHERE

Un peque帽o resumen del comando SELECT

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

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

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.

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; ```

Me encanta 鉂わ笍

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;

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

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

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??

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;

SELECT: crea tablas din谩micas seg煤n nuestras necesidades.

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)

Interesante las clases la verdad me ha gustado y he aprendido

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

Me esta fascinando!!

En este momento Pablo Saavedra tiene 60 a帽os

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?

-- Seleccionar los nombres y los correos
select name, email from clients limit 10;
-- Ver estructura de la tabla
desc clients;
--------------
-- Seleccionar hombres
select name, email, gender from clients where gender = 'M';
-- Seleccionar cumplea帽os
select birthdate from clients;
-- Seleccionar el cumplea帽os, solo el a帽o
select year(birthdate) from clients;
-- funcion now
Select year(NOW());
-- Calcular edad
Select name, year(now()) - year(birthdate) from clients;
-- funcion like
Select * from clients where name like '%Saave%';
-- nombre, edad, email, que sean mujeres y que en su nombre exisita la cadena lop
select name, email, year(now()) - year(birthdate) AS edad, gender from clients
where gender = 'F'
AND name LIKE '%Lop%';

**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.

Que interesante se estan colocando estas clases.

En la fecha que se grabo este curso (2018) el 鈥榗liente鈥 Pablo Saveedra tenia 58 a帽os, hoy en 2022 y el tiene 62.

Dato inutil.

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 鈥榚dad鈥 incluso cuando esta no existe en nuestra BD (Base de datos).

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

Les comparto este recurso que encontre, me ha gustado bastante para practicar distinto querys 馃槃

Jos茅 Juan S谩nchez Hern谩ndez
IES Celia Vi帽as (Almer铆a) - 2021/2022

https://josejuansanchez.org/bd/ejercicios-consultas-sql/index.html

Un saludo desde el pasado jaja que buen tiro, excelente clase

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

--CONSULTA CLIENTES DE ACUERRDO AL GENERO
SELECT name, email, gender FROM clients WHERE gender = 'F';
--CONSULTA A脩O DE NACIMEINTO
SELECT year(birthdate) FROM  clients;
--FECHA ACTUAL
SELECT NOW();
--SOLO EL A脩O ACTUAL DE ACUERDO A LA FECHA
SELECT YEAR(NOW());
--CALCULO EDAD ACTUAL DE LAS PERSONAS
SELECT name,YEAR(NOW()) - YEAR(birthdate) FROM clients limit 10;
--BUSCA en columna Name Saave o cercanos a este
SELECT * from clients WHERE name LIKE '%Saave%';
--Traer nombre, email, calculo de edad(con AS edad se le da nombre al a columna), genero
-- y que exista cadena de texto  '%lop'
SELECT name, email, YEAR(NOW()) - YEAR(birthdate) AS edad, gender 
FROM clients
WHERE gender = 'F'
AND name LIKE '%Lop%';
<code> 

No tenia ni idea que se permit铆a hacer operaciones aritm茅ticas dentro de la query, es superpoderosa SQL

Funciones MySQL
Select Year (birthday) from clients
Select NOW (); = fecha y hora hoy
Select year (NOW())
Select year (now()) 鈥 year (birthday) from clients
Select name, year (now()) 鈥 year (birthday) from clients
Like = funci贸n de comparaci贸n
Select * from clients where name like 鈥%鈥榮tring鈥%鈥;

informacio que puede servir para entender un poco mas sobre la sintanxis del select en Mysql:

https://hazloexpress.com/sentencia-select/

<SELECT now();>

a todos saludos desde el pasado

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

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.

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

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

Excelente aportes

Use platzidatabase;
Select * from platziestudiantes where estudiantes_opinion= 鈥楴o me gusta platzi鈥;
Empy set (0.00 sec)

Muy buena clase

excelente curso

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

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");

Mi clase favorita!!!

Disculpen, es necesario relacionar las tablas? es decir, usar el 鈥渞eferences鈥? Gracias por los videos, sirven para practicar 馃槃

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

BUENAS CLASES

Limit, que palabra m谩s importante.

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

Excelente clase, me gusto mucho.

Que buen curso :

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


SELECT name, email, YEAR(NOW()) 鈥 YEAR(birthdate) AS Edad, gender
FROM clients
Where GENDER = 鈥淔鈥
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%';

驴Se puede usar expresiones irregulares con la funcion 鈥渓ike鈥 en MySql?

Entiendo todo re bien

Empecemos a hacer Queries

Buena clase

buen video

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

jajaja tiene su a帽ito el curso.

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 :""")

Excelente clase sigo aprendiendo.

驴Cu谩l es el comendo para limpiar pantalla en Windows?

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

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_column_1, name_column_2 FROM name_table; podemos indicarle al select que columnas traer para mostrar 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.

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 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.

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 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 NOW(); muestra la fecha actual

SELECT EL REY DE ESTE CURSO !!!

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.

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

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

El objetivo es crear tener una bases de datos bien dise帽ada y crear querys que sean puntuales pero al mismo tiempo (poderosos) con la capacidad de brindar informaci贸n precisa, legible para tomar decisiones correctas

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.

Limit siempre va al final del query

Para los que no tengan valores en la tabla de transactions, les dejo esto.

DROP TABLE IF EXISTS `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',
  PRIMARY KEY (`transaction_id`)
) ENGINE=InnoDB AUTO_INCREMENT=101 DEFAULT CHARSET=utf8;

INSERT INTO `transactions` (`transaction_id`, `book_id`, `client_id`, `type`, `created_at`, `modified_at`, `finished`) 
VALUES (1,169,16,'lend','2019-12-19 13:20:53','2020-11-27 09:16:25',1),
(2,106,32,'lend','2021-06-26 09:14:33','2020-04-30 02:16:12',0),
(3,96,94,'lend','2020-12-11 19:05:56','2020-11-16 10:44:03',1),
(4,51,37,'lend','2021-02-25 04:33:51','2020-10-19 22:21:09',0),
(5,13,76,'sell','2020-11-07 04:45:39','2019-11-12 16:09:42',0),
(6,159,11,'lend','2020-12-09 17:09:12','2020-02-04 00:15:38',0),
(7,136,28,'sell','2019-11-09 18:46:47','2020-05-12 06:27:21',0),
(8,187,28,'sell','2021-06-22 11:49:43','2021-06-16 04:09:27',0),
(9,179,13,'lend','2019-11-16 04:42:05','2021-02-10 10:15:13',1),
(10,86,97,'lend','2020-09-06 04:47:09','2020-08-12 05:26:09',1),
(11,130,4,'sell','2020-06-06 23:18:58','2019-10-21 21:20:13',1),
(12,32,18,'sell','2021-03-01 22:56:00','2019-10-13 19:58:07',0),
(13,77,40,'lend','2020-03-12 09:40:17','2020-10-30 15:48:50',1),
(14,126,21,'lend','2020-04-27 00:38:00','2021-05-22 09:50:46',1),
(15,137,13,'lend','2020-06-23 17:08:18','2020-01-06 09:27:01',0),
(16,6,31,'lend','2020-01-26 13:19:59','2021-10-02 22:17:43',1),
(17,10,79,'lend','2020-03-04 22:21:11','2020-03-01 00:51:20',1),
(18,134,61,'sell','2021-04-08 13:41:34','2020-03-30 19:57:01',1),
(19,105,91,'sell','2020-05-16 19:26:27','2021-03-04 07:03:39',1),
(20,38,10,'lend','2020-06-14 01:00:38','2020-09-16 18:59:59',0),
(21,115,64,'lend','2020-08-23 09:28:37','2020-01-08 07:57:19',1),
(22,50,88,'sell','2021-08-25 03:32:58','2020-02-28 01:59:09',0),
(23,100,1,'lend','2020-08-31 02:04:37','2021-06-05 08:38:43',1),
(24,119,2,'sell','2020-03-29 20:39:20','2021-08-29 16:28:44',0),
(25,43,77,'sell','2020-07-20 05:29:45','2021-05-03 06:17:32',1),
(26,85,4,'sell','2020-11-01 20:11:44','2020-10-19 09:47:03',0),
(27,97,45,'lend','2021-08-19 19:48:46','2021-02-13 19:52:41',0),
(28,159,68,'lend','2021-03-06 06:42:50','2020-02-21 22:52:23',1),
(29,13,45,'lend','2021-04-30 18:27:41','2021-04-09 12:19:22',1),
(30,194,75,'sell','2021-04-22 23:58:34','2021-06-01 06:41:41',0),
(31,115,4,'lend','2020-06-11 14:26:22','2020-09-16 16:34:15',1),
(32,172,54,'lend','2019-10-07 14:35:15','2021-01-03 00:36:46',1),
(33,91,44,'sell','2021-04-26 20:09:08','2021-04-19 00:41:28',1),
(34,100,43,'sell','2020-06-22 05:38:05','2019-12-29 05:46:11',1),
(35,87,46,'lend','2021-08-09 00:26:07','2021-04-06 18:18:35',0),
(36,153,67,'lend','2020-11-30 10:45:12','2021-05-26 05:54:29',1),
(37,41,7,'sell','2020-11-21 13:52:07','2020-12-06 16:27:18',0),
(38,74,55,'lend','2019-12-14 01:36:22','2020-09-05 21:13:13',1),
(39,115,16,'sell','2021-06-01 02:28:46','2020-03-19 00:37:38',1),
(40,89,26,'sell','2021-03-26 04:08:23','2020-02-18 00:51:52',1),
(41,120,95,'lend','2021-08-27 08:41:57','2019-11-23 03:42:37',0),
(42,77,83,'lend','2019-10-30 14:59:05','2020-08-01 19:53:34',0),
(43,152,10,'lend','2020-01-05 03:01:08','2020-03-11 00:59:06',1),
(44,197,34,'sell','2020-06-01 02:48:53','2020-02-21 06:12:27',1),
(45,16,18,'lend','2019-10-27 21:18:55','2019-10-31 16:39:01',1),
(46,156,15,'sell','2021-09-12 06:19:20','2021-04-22 01:30:33',1),
(47,53,53,'lend','2019-12-02 04:36:14','2021-02-04 11:38:38',0),
(48,29,7,'lend','2020-06-08 15:54:33','2020-02-05 17:09:06',1),
(49,7,19,'sell','2021-09-11 12:49:47','2021-06-02 05:30:42',1),
(50,98,23,'lend','2020-03-07 07:07:34','2020-12-14 19:00:03',1),
(51,140,29,'sell','2021-02-11 08:47:36','2020-09-11 09:32:14',1),
(52,11,38,'sell','2021-01-12 00:27:17','2020-12-28 13:30:43',0),
(53,9,69,'lend','2019-11-15 05:51:36','2020-01-04 16:18:48',0),
(54,56,36,'sell','2020-06-02 17:54:51','2021-03-21 13:47:37',0),
(55,101,78,'sell','2021-03-30 15:22:50','2020-07-14 00:39:22',1),
(56,142,68,'lend','2021-03-08 02:20:27','2019-12-26 09:17:09',0),
(57,176,98,'lend','2019-10-25 20:13:24','2021-07-01 20:02:53',0),
(58,26,84,'sell','2020-04-26 18:17:46','2021-05-13 17:56:36',0),
(59,64,97,'lend','2021-06-14 07:54:34','2020-03-13 23:22:07',0),
(60,58,41,'lend','2020-02-13 03:17:26','2020-10-30 14:31:34',0),
(61,80,15,'sell','2021-08-30 23:07:03','2020-05-12 02:04:24',1),
(62,79,63,'lend','2020-07-25 10:44:51','2020-03-01 21:06:47',1),
(63,60,43,'lend','2021-09-28 11:57:58','2020-12-04 14:18:17',0),
(64,89,33,'lend','2021-03-19 14:54:12','2020-12-19 21:11:29',0),
(65,2,91,'sell','2020-06-06 10:52:17','2019-11-16 00:39:38',0),
(66,151,4,'lend','2020-09-01 08:10:46','2020-02-23 04:24:28',1),
(67,100,56,'lend','2020-02-14 23:03:59','2020-07-22 12:14:44',0),
(68,90,36,'sell','2020-02-13 11:32:46','2021-06-08 11:44:08',1),
(69,148,75,'lend','2021-04-22 23:34:26','2020-05-23 01:41:17',1),
(70,60,52,'lend','2020-10-28 12:41:05','2020-10-17 14:14:51',1),
(71,128,87,'lend','2020-03-01 14:36:20','2020-02-05 10:55:03',0),
(72,46,61,'sell','2020-02-11 14:32:48','2020-04-19 05:30:35',1),
(73,126,95,'sell','2021-07-04 13:06:12','2021-07-26 07:36:35',0),
(74,51,86,'sell','2020-08-08 09:17:31','2020-02-23 17:27:04',1),
(75,167,7,'lend','2020-12-30 03:09:25','2020-11-18 11:36:51',1),
(76,66,78,'lend','2020-05-02 11:09:55','2019-12-20 03:22:58',1),(
77,40,38,'sell','2020-06-12 06:27:20','2020-07-09 13:13:58',1),
(78,123,63,'lend','2020-05-04 06:20:02','2020-12-02 08:40:51',1),
(79,120,76,'sell','2021-06-24 18:14:46','2021-02-23 23:41:10',1),
(80,28,76,'lend','2021-08-19 03:05:38','2021-02-14 12:22:03',1),
(81,14,42,'lend','2020-05-19 02:55:34','2020-05-27 10:26:30',1),
(82,65,70,'lend','2020-04-11 11:42:17','2020-05-22 12:40:46',1),
(83,103,30,'sell','2021-08-28 12:36:36','2020-09-15 20:51:31',0),
(84,108,48,'lend','2020-01-16 05:16:56','2019-12-05 04:26:03',0),
(85,22,36,'sell','2021-01-01 10:11:54','2020-12-04 21:59:15',0),
(86,123,36,'sell','2021-08-11 15:44:26','2020-07-25 23:01:23',1),
(87,160,23,'sell','2020-12-15 17:46:58','2021-02-04 16:58:17',0),
(88,88,74,'lend','2019-12-09 01:18:50','2020-09-15 10:38:15',1),
(89,97,100,'sell','2020-05-08 19:45:30','2021-09-25 10:09:47',0),
(90,110,94,'sell','2019-11-14 16:17:45','2021-06-16 05:46:46',0),
(91,186,65,'sell','2021-05-14 09:20:29','2020-11-08 23:41:07',0),
(92,23,31,'sell','2021-08-03 03:47:41','2020-03-22 15:54:38',1),
(93,28,100,'lend','2020-02-06 19:40:50','2020-11-10 14:26:57',0),
(94,23,61,'sell','2021-09-24 07:30:07','2019-12-09 21:01:29',1),
(95,159,60,'sell','2021-05-31 19:48:09','2020-06-29 20:25:03',0),
(96,1,48,'lend','2021-01-24 23:49:01','2020-08-20 13:40:02',1),
(97,97,93,'lend','2020-07-27 05:57:52','2021-01-10 16:16:36',0),
(98,163,57,'lend','2021-09-30 04:20:45','2020-04-28 02:22:32',0),
(99,125,57,'sell','2020-03-14 21:32:46','2020-10-25 12:22:35',0),
(100,183,52,'lend','2021-01-13 21:33:02','2021-06-16 18:29:00',1);

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