Consultas SQL basadas en fecha y/o hora

Manejando SQL Server 2005, me encontre que tratando hacer consultas basadas en fechas, obtenía como resultado nada (empty). Esto se debía a que no tenía claro como trabajan los tipos de dato datetime y smalldatetime. Estos dos tipos de datos son muy similares, se diferencia en que datetime es más detallado a la hora de almacenar la fecha. Estas diferencias pueden ser vistas en la siguiente tabla:

Tipo	Minimo	Maximo	Precision
datetime	Ene 1, 1753 media-noche	Dic 31, 9999 23:59:59.997 (0.003 segundos hasta la media-noche)	Más cercano 3.33 millisegundos
smalldatetime	Ene 1, 1900 media-noche	Jun 6, 2079 23:59 (un minuto hasta la media-noche)	Más cercano al mínuto

Ambos, representan una fecha y hora que es igual al número de días en relación a una fecha base. En SQL Server la fecha base es la medianoche del 1 de Enero de 1900. Observando la tabla, el tipo de dato smalldatetime, puede solo representar fechas desde esta fecha base, en cambio el datetime puede representar fechas antes del 1 de enero de 1900. Para hacer esto, el almacena el valor como número negativo.

Para visualizar, como estos valores son guardados. Piensa en un valor decimal, cuya parte entera representa los días desde la fecha base y la parte fraccional (los numeros después del punto) representan la hora. Por ejemplo, el valor date/time mediodía del 4 de enero de 1900, es almacenado como 3.5, donde 3, representa tres días desde la fecha base y el 0.5, representa la mitad del día. Para ver esto en el SQL Server, ejecuta la siguiente consulta:
SELECT CAST(CAST(’1900-01-04 12:00′ AS datetime) AS float)

Otro datito importante es saber que el SQL Server no provee un tipo de dato que solo almacene fecha u hora. Es decir, que si tu almacenas un valor sin la hora (la parte fraccional) se le asigna el valor de cero (0). De igual forma, si tu almacenas un valor sin la fecha, la parte entera se le asigna automáticamente el valor de cero (0), que para la fecha significa 1 de enero de 1900. Para ejemplo, ejecuta la siguiente consulta:

SELECT CAST(’1900-01-04′ AS datetime), CAST(’10:00′ AS datetime)

el cual retornará el siguiente resultado:

1900-01-04 00:00:00.000 1900-01-01 10:00:00.000

Como Buscar Solo la Fecha

Para ilustrar usaremos la siguiente tabla:

ID DateVal
– ———————–
1 2001-02-28 10:00:00.000
2 2002-02-28 13:58:32.823
3 2002-02-29 00:00:00.000
4 2002-02-28 00:00:00.000

Si realizamos la siguiente consulta:

SELECT * FROM DateSample WHERE DateVal = ’2002-02-28′

El resultado solo traerá la fila #4, esto se debe a que el SQL Server buscará los datos cuya hora sea 00:00:00 (medianoche). El valor literal buscado (28 de febrero de 2002) implicítamente se le agregará la hora antes en mención. Entonces, como podemos evitar este suceso y obtener todos los registros cuya fecha sea 28 de febrero de 2002? Lo haremos de la siguiente forma:

Si la consulta es ejecuta frecuentemente, tú debes basar tu búsqueda en un rango de fechas, como la siguiente:

SELECT * FROM DateSample WHERE DateVal BETWEEN ’2002-02-28′ AND ’2002-02-28 23:59:59.997′

Nota: Ahora pensarás que yo soy un brutón porque debi usar como límite superior ’2002-02-29′ en vez de ’2002-02-28 23:59:59.997′. Pués no, si haces esto, la fila #3 será parte del resultado de tu consulta.

Otra forma de obtener el resultado de manera más sencilla (la que yo recomiendo), es de la siguiente manera:

SELECT * FROM DateSample WHERE DateVal >= ’2002-02-28′ AND DateVal < ’2002-02-29′

Como Buscar Solo por la Hora (How to search by time)

Para ilustrar, consideremos la siguiente tabla:

ID TimeVal
– ———————–
1 2002-02-28 10:00:00.000
2 1900-01-01 13:58:32.823
3 1900-01-01 09:59:59.997
4 1900-01-01 10:00:00.000

Si usamos la siguiente consulta para extraer las filas cuya hora es igual a las 10:00 AM:

SELECT * FROM TimeSample WHERE TimeVal = ’10:00:00′

solo conseguiremos la fila #4. La fila #1 no será parte del resultado, debido a que sucede lo mismo que con la fecha, pero en este caso el valor que es implicítamente agregado, es la fecha. En adición, la fila #3 no es parte del resultado porque es un valor super cerca, pero no igual a las 10:00 AM.

Para obviar la fecha, tu puedes crear una consulta que corte la parte entera del valor date/time, y solo deje la parte fraccional. Esta sería de la siguiente forma:

SELECT * FROM TimeSample WHERE TimeVal – CAST(FLOOR(CAST(TimeVal AS float)) AS datetime) = ’10:00′

Esta consulta nos devolvería la fila 1 y 4. Lamentablemente, no hay otra forma de conseguir este resultado sin usar esta combinación de formulas. Por tal motivo, hay que tomar mucho cuidado como se almacena la data datetime/smalldatetime que solo se usa para horas (time-only). Si tú necesitas realizar frecuentemente este tipo de búsqueda, te recomiendo que analices y reestructures el diseño de tu base de datos.

Si tú deseas hacer consultas basadas solo por horas para un intervalo de tiempo, bastaría con una simple consulta (query), esto; tomando en cuenta que se ha almacenado la data hora (time-only) consistentemente sin el componente fecha. La consulta sería algo como esto:

SELECT * FROM TimeSample WHERE TimeVal BETWEEN ’09:59′ AND ’10:01′

o

SELECT * FROM TimeSample WHERE TimeVal > ’09:59′ AND TimeVal < ’10:01′

Estas dos consultas retornarían los datos de la fila #3 y #4.

Si los datos de hora (time-only) han sido guardados en la base de datos inconsistemente, entonces se necesitará que la consulta evite o excluya la parte fecha de estos. Por ejemplo, tu podrías usar una consulta como esta:

SELECT * FROM TimeSample WHERE TimeVal – CAST(FLOOR(CAST(TimeVal AS float)) AS datetime) > ’09:59′ AND TimeVal – CAST(FLOOR(CAST(TimeVal AS float)) AS datetime) < ’10:01′

la cual nos devuelve las filas 1, 3 y 4. Recuerden que no podrías realizar correctamente la búsqueda sin estas funciones, cuando la data hora ha sido guardada inconsistentemente.

Nota: Toda esta información ha sido traducida del libro Murach’s SQL for SQL Server del autor Bryan Syverson.

Base datos para quiz

CREATE TABLE authors
(
au_id char(11) NOT NULL,
au_lname varchar(40) NOT NULL,
au_fname varchar(20) NOT NULL,
phone char(12) NOT NULL,
address varchar(40) NULL,
city varchar(20) NULL,
state char(2) NULL,
zip char(5) NULL,
contract bit NOT NULL,
PRIMARY KEY(au_id)
) TYPE=INNODB;

CREATE TABLE publishers
(
pub_id char(4) NOT NULL,
pub_name varchar(40) NULL,
city varchar(20) NULL,
state char(2) NULL,
country varchar(30) NULL,
PRIMARY KEY(pub_id)
) TYPE=INNODB;

CREATE TABLE titles
(
title_id char(6),
title varchar(80) NOT NULL,
type char(12) NOT NULL DEFAULT 'UNDECIDED',
pub_id char(4) NULL,
price int NULL,
advance int NULL,
royalty int NULL,
ytd_sales int NULL,
notes varchar(200) NULL,
pubdate timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY(title_id),
FOREIGN KEY(pub_id) REFERENCES publishers(pub_id)
) TYPE=INNODB;

CREATE TABLE titleauthor
(
au_id char(11),
title_id char(6),
au_ord tinyint NULL,
royaltyper int NULL,
PRIMARY KEY (au_id, title_id),
FOREIGN KEY (au_id) REFERENCES authors (au_id),
FOREIGN KEY (title_id) REFERENCES titles (title_id)
) TYPE=INNODB;

CREATE TABLE stores
(
stor_id char(4) NOT NULL,
stor_name varchar(40) NULL,
stor_address varchar(40) NULL,
city varchar(20) NULL,
state char(2) NULL,
zip char(5) NULL,
PRIMARY KEY (stor_id)
) TYPE=INNODB;

CREATE TABLE sales
(
stor_id char(4) NOT NULL,
ord_num varchar(20) NOT NULL,
ord_date timestamp NOT NULL,
qty smallint NOT NULL,
payterms varchar(12) NOT NULL,
title_id char(6),
FOREIGN KEY (stor_id) REFERENCES stores(stor_id),
FOREIGN KEY (title_id) REFERENCES titles(title_id),
PRIMARY KEY (stor_id,ord_num,title_id)
) TYPE=INNODB;

CREATE TABLE roysched
(
title_id char(6),
lorange int NULL,
hirange int NULL,
royalty int NULL,
FOREIGN KEY (title_id) REFERENCES titles(title_id)
) TYPE=INNODB;

CREATE TABLE discounts
(
discounttype varchar(40) NOT NULL,
stor_id char(4) NULL,
lowqty smallint NULL,
highqty smallint NULL,
discount dec(4,2) NOT NULL,
FOREIGN KEY (stor_id) REFERENCES stores(stor_id)
) TYPE=INNODB;

CREATE TABLE jobs
(
job_id smallint NOT NULL,
job_desc varchar(50) NOT NULL DEFAULT 'New Position - title not formalized yet',
min_lvl tinyint NOT NULL,
max_lvl tinyint NOT NULL,
PRIMARY KEY (job_id)
) TYPE=INNODB;


CREATE TABLE employee
(
emp_id char(9),
fname varchar(20) NOT NULL,
minit char(1) NULL,
lname varchar(30) NOT NULL,
job_id smallint NOT NULL DEFAULT 1,
job_lvl tinyint NOT NULL DEFAULT 10,
pub_id char(4) NOT NULL DEFAULT '9952',
hire_date timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (emp_id),
FOREIGN KEY (pub_id) REFERENCES publishers(pub_id),
FOREIGN KEY (job_id) REFERENCES jobs(job_id)
) TYPE=INNODB;

insert into authors values('409-56-7008', 'Bennet', 'Abraham', '415 658-9932', '6223 Bateman St.', 'Berkeley', 'CA', '94705', 1);
insert into authors values('213-46-8915', 'Green', 'Marjorie', '415 986-7020', '309 63rd St. #411', 'Oakland', 'CA', '94618', 1);
insert into authors values('238-95-7766', 'Carson', 'Cheryl', '415 548-7723', '589 Darwin Ln.', 'Berkeley', 'CA', '94705', 1);
insert into authors values('998-72-3567', 'Ringer', 'Albert', '801 826-0752', '67 Seventh Av.', 'Salt Lake City', 'UT', '84152', 1);
insert into authors values('899-46-2035', 'Ringer', 'Anne', '801 826-0752', '67 Seventh Av.', 'Salt Lake City', 'UT', '84152', 1);
insert into authors values('722-51-5454', 'DeFrance', 'Michel', '219 547-9982', '3 Balding Pl.', 'Gary', 'IN', '46403', 1);
insert into authors values('807-91-6654', 'Panteley', 'Sylvia', '301 946-8853', '1956 Arlington Pl.', 'Rockville', 'MD', '20853', 1);
insert into authors values('893-72-1158', 'McBadden', 'Heather', '707 448-4982', '301 Putnam', 'Vacaville', 'CA', '95688', 0);
insert into authors values('724-08-9931', 'Stringer', 'Dirk', '415 843-2991', '5420 Telegraph Av.', 'Oakland', 'CA', '94609', 0);
insert into authors values('274-80-9391', 'Straight', 'Dean', '415 834-2919', '5420 College Av.', 'Oakland', 'CA', '94609', 1);
insert into authors values('756-30-7391', 'Karsen', 'Livia', '415 534-9219', '5720 McAuley St.', 'Oakland', 'CA', '94609', 1);
insert into authors values('724-80-9391', 'MacFeather', 'Stearns', '415 354-7128', '44 Upland Hts.', 'Oakland', 'CA', '94612', 1);
insert into authors values('427-17-2319', 'Dull', 'Ann', '415 836-7128', '3410 Blonde St.', 'Palo Alto', 'CA', '94301', 1);
insert into authors values('672-71-3249', 'Yokomoto', 'Akiko', '415 935-4228', '3 Silver Ct.', 'Walnut Creek', 'CA', '94595', 1);
insert into authors values('267-41-2394', 'O''Leary', 'Michael', '408 286-2428', '22 Cleveland Av. #14', 'San Jose', 'CA', '95128', 1);
insert into authors values('472-27-2349', 'Gringlesby', 'Burt', '707 938-6445', 'PO Box 792', 'Covelo', 'CA', '95428', 3);
insert into authors values('527-72-3246', 'Greene', 'Morningstar', '615 297-2723', '22 Graybar House Rd.', 'Nashville', 'TN', '37215', 0);
insert into authors values('172-32-1176', 'White', 'Johnson', '408 496-7223', '10932 Bigge Rd.', 'Menlo Park', 'CA', '94025', 1);
insert into authors values('712-45-1867', 'del Castillo', 'Innes', '615 996-8275', '2286 Cram Pl. #86', 'Ann Arbor', 'MI', '48105', 1);
insert into authors values('846-92-7186', 'Hunter', 'Sheryl', '415 836-7128', '3410 Blonde St.', 'Palo Alto', 'CA', '94301', 1);
insert into authors values('486-29-1786', 'Locksley', 'Charlene', '415 585-4620', '18 Broadway Av.', 'San Francisco', 'CA', '94130', 1);
insert into authors values('648-92-1872', 'Blotchet-Halls', 'Reginald', '503 745-6402', '55 Hillsdale Bl.', 'Corvallis', 'OR', '97330', 1);
insert into authors values('341-22-1782', 'Smith', 'Meander', '913 843-0462', '10 Mississippi Dr.', 'Lawrence', 'KS', '66044', 0);

insert into publishers values('0736', 'New Moon Books', 'Boston', 'MA', 'USA');
insert into publishers values('0877', 'Binnet & Hardley', 'Washington', 'DC', 'USA');
insert into publishers values('1389', 'Algodata Infosystems', 'Berkeley', 'CA', 'USA');
insert into publishers values('9952', 'Scootney Books', 'New York', 'NY', 'USA');
insert into publishers values('1622', 'Five Lakes Publishing', 'Chicago', 'IL', 'USA');
insert into publishers values('1756', 'Ramona Publishers', 'Dallas', 'TX', 'USA');
insert into publishers values('9901', 'GGG&G', 'Munchen', NULL, 'Germany');
insert into publishers values('9999', 'Lucerne Publishing', 'Paris', NULL, 'France');

insert into titles values ('PC8888', 'Secrets of Silicon Valley', 'popular_comp', '1389', 20.00, 8000.00, 10, 4095, 'Muckraking reporting on the world''s largest computer hardware and software manufacturers.', '06/12/94');
insert into titles values ('BU1032', 'The Busy Executive''s Database Guide', 'business', '1389', 19.99, 5000.00, 10, 4095, 'An overview of available database systems with emphasis on common business applications. Illustrated.', '06/12/91');
insert into titles values ('PS7777', 'Emotional Security: A New Algorithm', 'psychology', '0736', 7.99, 4000.00, 10, 3336, 'Protecting yourself and your loved ones from undue emotional stress in the modern world. Use of computer and nutritional aids emphasized.', '06/12/91');
insert into titles values ('PS3333', 'Prolonged Data Deprivation: Four Case Studies', 'psychology', '0736', 19.99, 2000.00, 10, 4072, 'What happens when the data runs dry? Searching evaluations of information-shortage effects.', '06/12/91');
insert into titles values ('BU1111', 'Cooking with Computers: Surreptitious Balance Sheets', 'business', '1389', 11.95, 5000.00, 10, 3876, 'Helpful hints on how to use your electronic resources to the best advantage.', '06/09/91');
insert into titles values ('MC2222', 'Silicon Valley Gastronomic Treats', 'mod_cook', '0877', 19.99, 0.00, 12, 2032, 'Favorite recipes for quick, easy, and elegant meals.', '06/09/91');
insert into titles values ('TC7777', 'Sushi, Anyone?', 'trad_cook', '0877', 14.99, 8000.00, 10, 4095, 'Detailed instructions on how to make authentic Japanese sushi in your spare time.', '06/12/91');
insert into titles values ('TC4203', 'Fifty Years in Buckingham Palace Kitchens', 'trad_cook', '0877', 11.95, 4000.00, 14, 15096, 'More anecdotes from the Queen''s favorite cook describing life among English royalty. Recipes, techniques, tender vignettes.', '06/12/91');
insert into titles values ('PC1035', 'But Is It User Friendly?', 'popular_comp', '1389', 22.95, 7000.00, 16, 8780, 'A survey of software for the naive user, focusing on the ''friendliness'' of each.', '06/30/91');
insert into titles values('BU2075', 'You Can Combat Computer Stress!', 'business', '0736', 2.99, 10125.00, 24, 18722, 'The latest medical and psychological techniques for living with the electronic office. Easy-to-understand explanations.', '06/30/91');
insert into titles values('PS2091', 'Is Anger the Enemy?', 'psychology', '0736', 10.95, 2275.00, 12, 2045, 'Carefully researched study of the effects of strong emotions on the body. Metabolic charts included.', '06/15/91');
insert into titles values('PS2106', 'Life Without Fear', 'psychology', '0736', 7.00, 6000.00, 10, 111, 'New exercise, meditation, and nutritional techniques that can reduce the shock of daily interactions. Popular audience. Sample menus included, exercise video available separately.', '10/05/91');
insert into titles values('MC3021', 'The Gourmet Microwave', 'mod_cook', '0877', 2.99, 15000.00, 24, 22246, 'Traditional French gourmet recipes adapted for modern microwave cooking.', '06/18/91');
insert into titles values('TC3218', 'Onions, Leeks, and Garlic: Cooking Secrets of the Mediterranean', 'trad_cook', '0877', 20.95, 7000.00, 10, 375, 'Profusely illustrated in color, this makes a wonderful gift book for a cuisine-oriented friend.', '10/21/91');
insert into titles (title_id, title, pub_id) values('MC3026', 'The Psychology of Computer Cooking', '0877');
insert into titles values ('BU7832', 'Straight Talk About Computers', 'business', '1389', 19.99, 5000.00, 10, 4095, 'Annotated analysis of what computers can do for you: a no- hype guide for the critical user.', '06/22/91');
insert into titles values('PS1372', 'Computer Phobic AND Non-Phobic Individuals: Behavior Variations', 'psychology', '0877', 21.59, 7000.00, 10, 375, 'A must for the specialist, this book examines the difference between those who hate and fear computers and those who don''t.', '10/21/91');
insert into titles (title_id, title, type, pub_id, notes) values('PC9999', 'Net Etiquette', 'popular_comp', '1389', 'A must-read for computer conferencing.');


insert into titleauthor values('409-56-7008', 'BU1032', 1, 60);
insert into titleauthor values('486-29-1786', 'PS7777', 1, 100);
insert into titleauthor values('486-29-1786', 'PC9999', 1, 100);
insert into titleauthor values('712-45-1867', 'MC2222', 1, 100);
insert into titleauthor values('172-32-1176', 'PS3333', 1, 100);
insert into titleauthor values('213-46-8915', 'BU1032', 2, 40);
insert into titleauthor values('238-95-7766', 'PC1035', 1, 100);
insert into titleauthor values('213-46-8915', 'BU2075', 1, 100);
insert into titleauthor values('998-72-3567', 'PS2091', 1, 50);
insert into titleauthor values('899-46-2035', 'PS2091', 2, 50);
insert into titleauthor values('998-72-3567', 'PS2106', 1, 100);
insert into titleauthor values('722-51-5454', 'MC3021', 1, 75);
insert into titleauthor values('899-46-2035', 'MC3021', 2, 25);
insert into titleauthor values('807-91-6654', 'TC3218', 1, 100);
insert into titleauthor values('274-80-9391', 'BU7832', 1, 100);
insert into titleauthor values('427-17-2319', 'PC8888', 1, 50);
insert into titleauthor values('846-92-7186', 'PC8888', 2, 50);
insert into titleauthor values('756-30-7391', 'PS1372', 1, 75);
insert into titleauthor values('724-80-9391', 'PS1372', 2, 25);
insert into titleauthor values('724-80-9391', 'BU1111', 1, 60);
insert into titleauthor values('267-41-2394', 'BU1111', 2, 40);
insert into titleauthor values('672-71-3249', 'TC7777', 1, 40);
insert into titleauthor values('267-41-2394', 'TC7777', 2, 30);
insert into titleauthor values('472-27-2349', 'TC7777', 3, 30);
insert into titleauthor values('648-92-1872', 'TC4203', 1, 100);

insert into stores values('7066','Barnums','567 Pasadena Ave.','Tustin','CA','92789');
insert into stores values('7067','News & Brews','577 First St.','Los Gatos','CA','96745');
insert into stores values('7131','Doc-U-Mat: Quality Laundry and Books', '24-A Avogadro Way','Remulade','WA','98014');
insert into stores values('8042','Bookbeat','679 Carson St.','Portland','OR','89076');
insert into stores values('6380','Eric the Read Books','788 Catamaugus Ave.', 'Seattle','WA','98056');
insert into stores values('7896','Fricative Bookshop','89 Madison St.','Fremont','CA','90019');

insert into sales values('7066', 'QA7442.3', '09/13/94', 75, 'ON invoice','PS2091');
insert into sales values('7067', 'D4482', '09/14/94', 10, 'Net 60','PS2091');
insert into sales values('7131', 'N914008', '09/14/94', 20, 'Net 30','PS2091');
insert into sales values('7131', 'N914014', '09/14/94', 25, 'Net 30','MC3021');
insert into sales values('8042', '423LL922', '09/14/94', 15, 'ON invoice','MC3021');
insert into sales values('8042', '423LL930', '09/14/94', 10, 'ON invoice','BU1032');
insert into sales values('6380', '722a', '09/13/94', 3, 'Net 60','PS2091');
insert into sales values('6380', '6871', '09/14/94', 5, 'Net 60','BU1032');
insert into sales values('8042','P723', '03/11/93', 25, 'Net 30', 'BU1111');
insert into sales values('7896','X999', '02/21/93', 35, 'ON invoice', 'BU2075');
insert into sales values('7896','QQ2299', '10/28/93', 15, 'Net 60', 'BU7832');
insert into sales values('7896','TQ456', '12/12/93', 10, 'Net 60', 'MC2222');
insert into sales values('8042','QA879.1', '5/22/93', 30, 'Net 30', 'PC1035');
insert into sales values('7066','A2976', '5/24/93', 50, 'Net 30', 'PC8888');
insert into sales values('7131','P3087a', '5/29/93', 20, 'Net 60', 'PS1372');
insert into sales values('7131','P3087a', '5/29/93', 25, 'Net 60', 'PS2106');
insert into sales values('7131','P3087a', '5/29/93', 15, 'Net 60', 'PS3333');
insert into sales values('7131','P3087a', '5/29/93', 25, 'Net 60', 'PS7777');
insert into sales values('7067','P2121', '6/15/92', 40, 'Net 30', 'TC3218');
insert into sales values('7067','P2121', '6/15/92', 20, 'Net 30', 'TC4203');
insert into sales values('7067','P2121', '6/15/92', 20, 'Net 30', 'TC7777');

insert into roysched values('BU1032', 0, 5000, 10);
insert into roysched values('BU1032', 5001, 50000, 12);
insert into roysched values('PC1035', 0, 2000, 10);
insert into roysched values('PC1035', 2001, 3000, 12);
insert into roysched values('PC1035', 3001, 4000, 14);
insert into roysched values('PC1035', 4001, 10000, 16);
insert into roysched values('PC1035', 10001, 50000, 18);
insert into roysched values('BU2075', 0, 1000, 10);
insert into roysched values('BU2075', 1001, 3000, 12);
insert into roysched values('BU2075', 3001, 5000, 14);

insert into roysched values('BU2075', 5001, 7000, 16);
insert into roysched values('BU2075', 7001, 10000, 18);
insert into roysched values('BU2075', 10001, 12000, 20);
insert into roysched values('BU2075', 12001, 14000, 22);
insert into roysched values('BU2075', 14001, 50000, 24);
insert into roysched values('PS2091', 0, 1000, 10);
insert into roysched values('PS2091', 1001, 5000, 12);
insert into roysched values('PS2091', 5001, 10000, 14);
insert into roysched values('PS2091', 10001, 50000, 16);
insert into roysched values('PS2106', 0, 2000, 10);

insert into roysched values('PS2106', 2001, 5000, 12);
insert into roysched values('PS2106', 5001, 10000, 14);
insert into roysched values('PS2106', 10001, 50000, 16);
insert into roysched values('MC3021', 0, 1000, 10);
insert into roysched values('MC3021', 1001, 2000, 12);
insert into roysched values('MC3021', 2001, 4000, 14);
insert into roysched values('MC3021', 4001, 6000, 16);
insert into roysched values('MC3021', 6001, 8000, 18);
insert into roysched values('MC3021', 8001, 10000, 20);
insert into roysched values('MC3021', 10001, 12000, 22);

insert into roysched values('MC3021', 12001, 50000, 24);
insert into roysched values('TC3218', 0, 2000, 10);
insert into roysched values('TC3218', 2001, 4000, 12);
insert into roysched values('TC3218', 4001, 6000, 14);
insert into roysched values('TC3218', 6001, 8000, 16);
insert into roysched values('TC3218', 8001, 10000, 18);
insert into roysched values('TC3218', 10001, 12000, 20);
insert into roysched values('TC3218', 12001, 14000, 22);
insert into roysched values('TC3218', 14001, 50000, 24);
insert into roysched values('PC8888', 0, 5000, 10);
insert into roysched values('PC8888', 5001, 10000, 12);

insert into roysched values('PC8888', 10001, 15000, 14);
insert into roysched values('PC8888', 15001, 50000, 16);
insert into roysched values('PS7777', 0, 5000, 10);
insert into roysched values('PS7777', 5001, 50000, 12);
insert into roysched values('PS3333', 0, 5000, 10);
insert into roysched values('PS3333', 5001, 10000, 12);
insert into roysched values('PS3333', 10001, 15000, 14);
insert into roysched values('PS3333', 15001, 50000, 16);
insert into roysched values('BU1111', 0, 4000, 10);
insert into roysched values('BU1111', 4001, 8000, 12);
insert into roysched values('BU1111', 8001, 10000, 14);

insert into roysched values('BU1111', 12001, 16000, 16);
insert into roysched values('BU1111', 16001, 20000, 18);
insert into roysched values('BU1111', 20001, 24000, 20);
insert into roysched values('BU1111', 24001, 28000, 22);
insert into roysched values('BU1111', 28001, 50000, 24);
insert into roysched values('MC2222', 0, 2000, 10);
insert into roysched values('MC2222', 2001, 4000, 12);
insert into roysched values('MC2222', 4001, 8000, 14);
insert into roysched values('MC2222', 8001, 12000, 16);

insert into roysched values('MC2222', 12001, 20000, 18);
insert into roysched values('MC2222', 20001, 50000, 20);
insert into roysched values('TC7777', 0, 5000, 10);
insert into roysched values('TC7777', 5001, 15000, 12);
insert into roysched values('TC7777', 15001, 50000, 14);
insert into roysched values('TC4203', 0, 2000, 10);
insert into roysched values('TC4203', 2001, 8000, 12);
insert into roysched values('TC4203', 8001, 16000, 14);
insert into roysched values('TC4203', 16001, 24000, 16);
insert into roysched values('TC4203', 24001, 32000, 18);

insert into roysched values('TC4203', 32001, 40000, 20);
insert into roysched values('TC4203', 40001, 50000, 22);
insert into roysched values('BU7832', 0, 5000, 10);
insert into roysched values('BU7832', 5001, 10000, 12);
insert into roysched values('BU7832', 10001, 15000, 14);
insert into roysched values('BU7832', 15001, 20000, 16);
insert into roysched values('BU7832', 20001, 25000, 18);
insert into roysched values('BU7832', 25001, 30000, 20);
insert into roysched values('BU7832', 30001, 35000, 22);
insert into roysched values('BU7832', 35001, 50000, 24);

insert into roysched values('PS1372', 0, 10000, 10);
insert into roysched values('PS1372', 10001, 20000, 12);
insert into roysched values('PS1372', 20001, 30000, 14);
insert into roysched values('PS1372', 30001, 40000, 16);
insert into roysched values('PS1372', 40001, 50000, 18);

insert into discounts values('Initial Customer', NULL, NULL, NULL, 10.5);
insert into discounts values('Volume Discount', NULL, 100, 1000, 6.7);
insert into discounts values('Customer Discount', '8042', NULL, NULL, 5.0);

insert into jobs values (1, 'New Hire - Job not specified', 10, 10);
insert into jobs values (2, 'Chief Executive Officer', 200, 250);
insert into jobs values (3, 'Business Operations Manager', 175, 225);
insert into jobs values (4, 'Chief Financial Officier', 175, 250);
insert into jobs values (5, 'Publisher', 150, 250);
insert into jobs values (6, 'Managing Editor', 140, 225);
insert into jobs values (7, 'Marketing Manager', 120, 200);
insert into jobs values (8, 'Public Relations Manager', 100, 175);
insert into jobs values (9, 'Acquisitions Manager', 75, 175);
insert into jobs values (10, 'Productions Manager', 75, 165);
insert into jobs values (11, 'Operations Manager', 75, 150);
insert into jobs values (12, 'Editor', 25, 100);
insert into jobs values (13, 'Sales Representative', 25, 100);
insert into jobs values (14, 'Designer', 25, 100);

insert into employee values ('PTC11962M', 'Philip', 'T', 'Cramer', 2, 215, '9952', '11/11/89');
insert into employee values ('AMD15433F', 'Ann', 'M', 'Devon', 3, 200, '9952', '07/16/91');
insert into employee values ('F-C16315M', 'Francisco', '', 'Chang', 4, 227, '9952', '11/03/90');
insert into employee values ('LAL21447M', 'Laurence', 'A', 'Lebihan', 5, 175, '0736', '06/03/90');
insert into employee values ('PXH22250M', 'Paul', 'X', 'Henriot', 5, 159, '0877', '08/19/93');
insert into employee values ('SKO22412M', 'Sven', 'K', 'Ottlieb', 5, 150, '1389', '04/05/91');
insert into employee values ('RBM23061F', 'Rita', 'B', 'Muller', 5, 198, '1622', '10/09/93');
insert into employee values ('MJP25939M', 'Maria', 'J', 'Pontes', 5, 246, '1756', '03/01/89');
insert into employee values ('JYL26161F', 'Janine', 'Y', 'Labrune', 5, 172, '9901', '05/26/91');
insert into employee values ('CFH28514M', 'Carlos', 'F', 'Hernadez', 5, 211, '9999', '04/21/89');
insert into employee values ('VPA30890F', 'Victoria', 'P', 'Ashworth', 6, 140, '0877', '09/13/90');
insert into employee values ('L-B31947F', 'Lesley', '', 'Brown', 7, 120, '0877', '02/13/91');
insert into employee values ('ARD36773F', 'Anabela', 'R', 'Domingues', 8, 100, '0877', '01/27/93');
insert into employee values ('M-R38834F', 'Martine', '', 'Rance', 9, 75, '0877', '02/05/92');
insert into employee values ('PHF38899M', 'Peter', 'H', 'Franken', 10, 75, '0877', '05/17/92');
insert into employee values ('DBT39435M', 'Daniel', 'B', 'Tonini', 11, 75, '0877', '01/01/90');
insert into employee values ('H-B39728F', 'Helen', '', 'Bennett', 12, 35, '0877', '09/21/89');
insert into employee values ('PMA42628M', 'Paolo', 'M', 'Accorti', 13, 35, '0877', '08/27/92');
insert into employee values ('ENL44273F', 'Elizabeth', 'N', 'Lincoln', 14, 35, '0877', '07/24/90');
insert into employee values ('MGK44605M', 'Matti', 'G', 'Karttunen', 6, 220, '0736', '05/01/94');
insert into employee values ('PDI47470M', 'Palle', 'D', 'Ibsen', 7, 195, '0736', '05/09/93');
insert into employee values ('MMS49649F', 'Mary', 'M', 'Saveley', 8, 175, '0736', '06/29/93');
insert into employee values ('GHT50241M', 'Gary', 'H', 'Thomas', 9, 170, '0736', '08/09/88');
insert into employee values ('MFS52347M', 'Martin', 'F', 'Sommer', 10, 165, '0736', '04/13/90');
insert into employee values ('R-M53550M', 'Roland', '', 'Mendel', 11, 150, '0736', '09/05/91');
insert into employee values ('HAS54740M', 'Howard', 'A', 'Snyder', 12, 100, '0736', '11/19/88');
insert into employee values ('TPO55093M', 'Timothy', 'P', 'O''Rourke', 13, 100, '0736', '06/19/88');
insert into employee values ('KFJ64308F', 'Karin', 'F', 'Josephs', 14, 100, '0736', '10/17/92');
insert into employee values ('DWR65030M', 'Diego', 'W', 'Roel', 6, 192, '1389', '12/16/91');
insert into employee values ('M-L67958F', 'Maria', '', 'Larsson', 7, 135, '1389', '03/27/92');
insert into employee values ('PSP68661F', 'Paula', 'S', 'Parente', 8, 125, '1389', '01/19/94');
insert into employee values ('MAS70474F', 'Margaret', 'A', 'Smith', 9, 78, '1389', '09/29/88');
insert into employee values ('A-C71970F', 'Aria', '', 'Cruz', 10, 87, '1389', '10/26/91');
insert into employee values ('MAP77183M', 'Miguel', 'A', 'Paolino', 11, 112, '1389', '12/07/92');
insert into employee values ('Y-L77953M', 'Yoshi', '', 'Latimer', 12, 32, '1389', '06/11/89');
insert into employee values ('CGS88322F', 'Carine', 'G', 'Schmitt', 13, 64, '1389', '07/07/92');
insert into employee values ('PSA89086M', 'Pedro', 'S', 'Afonso', 14, 89, '1389', '12/24/90');
insert into employee values ('A-R89858F', 'Annette', '', 'Roulet', 6, 152, '9999', '02/21/90');
insert into employee values ('HAN90777M', 'Helvetius', 'A', 'Nagy', 7, 120, '9999', '03/19/93');
insert into employee values ('M-P91209M', 'Manuel', '', 'Pereira', 8, 101, '9999', '01/09/89');
insert into employee values ('KJJ92907F', 'Karla', 'J', 'Jablonski', 9, 170, '9999', '03/11/94');
insert into employee values ('POK93028M', 'Pirkko', 'O', 'Koskitalo', 10, 80, '9999', '11/29/93');
insert into employee values ('PCM98509F', 'Patricia', 'C', 'McKenna', 11, 150, '9999', '08/01/89');

FAQ Bases de datos

ntroducción

Este tema suele traer grandes dolores de cabeza cuando se trata de ser abordado usualmente, ya que muchos tutoriales parten de la base que uno sabe que es una base de datos, los diferentes tipos y como se usan. Así que este tutorial no pretende ser de ayuda para desarrollar ningún sistema, sino que su fin es el de explicar el sentido de las bases de datos y sus conceptos más básicos para así poder adentrarse cómodamente en el desarrollo de sistemas con bases de datos.

Primero vamos a partir con una serie de preguntas frecuentes que me fui encontrando:

FAQ

¿Qué es una DB?
Data Base = Base de datos en inglés

¿Qué es una BBDD o BD?
Base de datos

¿Para qué sirve una DB?
Almacenar datos

¿Cómo se aprende a programar en mySQL?
No se aprende, mySQL es una base de datos, no un lenguaje

¿SQL es una base de datos?
SQL server sí, pero SQL es un lenguaje

¿Cuántos tipos hay?
Muchos, pero los habituales en web son Access, mySQL y SQL Server 2000

¿Necesito algún lenguaje especial para conectarme?
Sí, SQL es el lenguaje para interactuar con las bases de datos

¿Qué es un Query?
Una consulta a la base de datos

¿Qué es ABM?
Alta, Baja y Modificación de cualquier cosa. Usualmente de una base de datos que contiene datos.

¿Qué es CMS?
Control Managment System : Un panel de control que administra un web site, y a menudo una base de datos.

Ahora, aclaradas estas dudas básicas, vamos al grano. Separaré en varios items la explicación a fin de un mejor orden.

Funcionalidad de un DB

Una base de datos (sea cual sea) es un soporte digital que tiene como fin el almacenamiento masivo de información en formato texto plano. No es capaz de almacenar imágenes como se cree, sino que almacena las rutas (path) de dichas fotos; ni almacena otro tipo de datos; sino que almacena sus rutas de acceso de ser necesario.

Las bases de datos, son utilizadas en sistemas que requieren una interacción fluida con la aplicación; estas se encargan muchas veces de administrar, editar, y dar de alta. Usualmente la base de datos, esta ligada a la programación directa del site, causando que una edición en ella cause una modificación directa en lo que ve el usuario.

Ejemplos de aplicación de una base de datos (entiéndase que están ligadas a un lenguaje dinámico como PHP o ASP):
E – comerce, Agendas, Libros de visitas, foros, portales, etc

Estructura normal de una DB

Una base de datos, a fin de ordenar la información de manera lógica, posee un orden que debe ser cumplido para acceder la información de manera coherente.

Cada base de datos tiene una o más tablas, las cuales cumplen la función de contener los campos. Un ejemplo de tabla sería “contactos”. Para entender mejor esto, sería como un libro en el excel. Mientras que los campos serían las columnas del excel donde se ordena cada datos insertado al libro. Ejemplo “id, nombres, apellidos, teléfono”. Y luego finalmente tenemos las filas (row), que son la información propiamente dicha.

Por consiguiente una base de datos posee el siguiente orden jerárquico:

• Tablas
• Campos
• Registros

Tipos usuales de bases de datos en la Web

En la web, se suelen usar 3 tipos de bases de datos:

Access: Es una base de datos desarrollada por Microsoft comúnmente utilizada bajo el lenguaje ASP (Active Server Pages). Esta base de datos, debe ser creada bajo el programa access, el cual crea archivo .mdb con la estructura ya explicada. El programa usa un entorno gráfico normal, y es muy parecido a usar excel.

MySQL: Es una base de datos con licencia GPL basada en un servidor, puede ser sólo creada por código. Usualmente se utiliza el programa phpMyAdmin como soporte para administrar la base de datos en el nivel de programación (a un usuario normal le resultaría complicado utilizarla desde linea de comandos).

SQL Server: Es una base de datos más potente que access desarrollada por Microsoft también, que se supone es recomendable arriba del millon datos.

PostgreSQL / Oracle: Son realmente mucho más poderosas que todas las antes mencionadas, aunque también se duplican los problemas. Administra muy bien grandes cantidades de datos, y suelen ser utilizadas en intranets y sistemas de gran calibre.

Lenguaje SQL

Este es el lenguaje que se utiliza para conectarse a una base de datos. Son sentencias, que realizan un query (consulta) a la DB a fin de que esta les responda con una cantidad de datos limitada según lo buscado. Básicamente, existen muchísimas funciones de SQL, pero detallaré las más usuales, con las cuales se pueden lograr una interacción buena con la DB.

Insertar Datos a una Tabla especifica:

INSERT INTO `Nombre Tabla` (` Nombre Campo ` , ` Nombre Campo ` , `Nombre Campo`) VALUES ('Valor', ' Valor', ' Valor ');

Editar Datos de una fila especifica

UPDATE ` Nombre Tabla ` SET ` Nombre Campo ` = 'Valor', ` Nombre Campo ` = ' Valor',` Nombre Campo ` = ' Valor' WHERE `id` = 'Numero Fila';

Nota: Siempre se incluye el campo id, a fin de identificar con un valor numérico una fila.

Borrar una Fila

DELETE FROM ` Nombre Tabla ` WHERE `id`='Numero Fila';

Seleccionar datos de una Fila

SELECT Nombre Campo, Nombre Campo FROM Nombre Tabla where id = Numero Fila;

Buscar datos dentro de una tabla

SELECT Nombre Campo FROM Nombre Tabla where Nombre Campo LIKE '%".Concepto de Búsqueda."%;

Contar Registros totales en un campo

SELECT COUNT (Nombre Campo) FROM Nombre Tabla;

Conclusión

Las bases de datos son soportes capaces de almacenar grandes cantidades de datos usando SQL como el lenguaje “consultor” de esos datos.

Entendiendo los Mysql JOIN

Este es un problema bastante común para los desarrolladores que trabajan sobre bases de datos:

Llaman un set de resultados y se encuentran que uno de los campos no es mas sino un listado de ID’s. Ahora como traigo los datos de ese campo?

Muy a menudo me encuentro con desarrolladores SQL que aplican la siguiente lógica para solucionar este problema:

SELECT * FROM países

foreach($países as $país) {
SELECT * FROM ciudades WHERE país = $país
foreach ($ciudades as $ciudad) {
…

Que como pueden ver, no solo es difícil de manejar sino que es imposible de crecer logicamente y en ultimas, confuso de leer. Mas que genera una cantidad de gasto de memoria tanto en el PC corriendo php como en la base de datos que genera la busqueda especifica.

Afortunadamente, tenemos la flexibilidad de una función MySQL hecha precisamente para llamar datos entre dos tablas conectadas con un campo en común: JOIN

Por ejemplo, usando JOIN, llamar un set de resultados de direcciones a base de país requeriría del siguiente query:

SELECT * FROM direcciones. AS dir
JOIN países AS p ON p.país = dir.pais
WHERE … cualquier condicion

Mejor! Esta búsqueda generaría el siguiente set hipotético de resultados para presentar usando php (ojo que esta organizado por las columnas de “país”):

 p.id  |  p.pais   | dir.id  | dir.pais  | dir.direccion
1     |  USA      | 3       | USA       | 200 West Chadderdon Avenue
2     |  Mexico   | 4       | Mexico    | 823 Monte Libano
4     |  Colombia | 2       | Colombia  | Carrera 9B Bis #117-32
3     |  Colombia | 1       | Colombia  | Calle 127 #42-45

Fácil, no? Bueno, a simple vista si, pero con esto estamos tan solo raspando la superficie del problema en el que nos podemos encontrar si tenemos dos tablas que no necesariamente contengan datos simétricos.

Para solucionar este problema, hay que considerar el juego de herramientas JOIN entero:
LEFT JOIN, RIGHT JOIN, INNER JOIN y OUTER JOIN.

Cuando me lo cruce originalmente, este post de Jeff Atwood que explica como funcionan los JOIN me soluciono una cantidad de problemas, hoy en día ya lo he reenviado tanto que decidí colgarlo aquí:

JOIN, una explicacion visual

Entonces, para lograr entender entonces que tan poderosa es la utilidad JOIN, voy a visualizar dos tablas que contengan tan solo algunos datos en común:

id nombre       id  nombre
-- ----         --  ----
1  Pirata       1   Rutabaga
2  Mico         2   Pirata
3  Ninja        3   Darth Vader
4  Spaghetti    4   Ninja

Listo? ahora veamos a ver como como caen las mezclas de datos usando JOIN

INNER JOIN

SELECT * FROM TablaA
INNER JOIN TablaB
ON TablaA.name = TablaB.name

id  name       id   name
--  ----       --   ----
1   Pirata     2    Pirata
3   Ninja      4    Ninja

Este genera una lista de resultados que existan tanto en A como en B simultaneamente

---

FULL OUTER JOIN

SELECT * FROM TablaA
FULL OUTER JOIN TablaB
ON TablaA.name = TablaB.name

id    name       id    name
--    ----       --    ----
1     Pirata     2     Pirata
2     Mico       null  null
3     Ninja      4     Ninja
4     Spaghetti  null  null
null  null       1     Rutabaga
null  null       3     Darth Vader

Genera un listado que incluya todos los resultados en A y en B, asi no exista relacion entre algun campo

---

LEFT OUTER JOIN

SELECT * FROM TablaA
LEFT OUTER JOIN TablaB
ON TablaA.name = TablaB.name

id  name       id    name
--  ----       --    ----
1   Pirata     2     Pirata
2   Mico       null  null
3   Ninja      4     Ninja
4   Spaghetti  null  null

Entrega todos los resultados que encuentra en la tabla A, pero tambien trae los resultados que empaten que aparezcan en la tabla B.

---

Usos y Derivados

… Conociendo como funcionan los JOIN, estos son otros ejemplos que se pueden generar usando la sintáxis JOIN completa.

SELECT * FROM TablaA
LEFT OUTER JOIN TablaB
ON TablaA.name = TablaB.name
WHERE TablaB.id IS null

id  name       id     name
--  ----       --     ----
2   Mico       null   null
4   Spaghetti  null   null

Solo quiero ver los records unicos que existan en la tabla A

SELECT * FROM TablaA
FULL OUTER JOIN TablaB
ON TablaA.name = TablaB.name
WHERE TablaA.id IS null
OR TablaB.id IS null

id    name       id    name
--    ----       --    ----
2     Mico       null  null
4     Spaghetti  null  null
null  null       1     Rutabaga
null  null       3     Darth Vader

Muestrame los records que no tengan relacion alguna entre la tabla A y la tabla B

---

Y ojo que el LEFT JOIN es intercambiable en sintaxis (mas no en logica) con un RIGHT JOIN donde

SELECT * FROM TablaA
LEFT JOIN TablaB
ON TablaA.name = TablaB.name

… es equivalente a

SELECT * FROM TablaB
RIGHT JOIN TablaA
ON TablaA.name = TablaB.name
Descargar ejemplo de una base de datos aqui