SQL01-Creating_table_basic_query

/* CREATING DATABASE */

CREATE DATABASE Livraria;

/* SELECTING DATABASE */

USE Livraria;

/* CREATING THE TABLE */

CREATE TABLE Livros (
bookName VARCHAR (50),
bookAuthor VARCHAR (50),
authorSex CHAR (1),
numberPages INTEGER (4),
namePublisher VARCHAR (20),
priceBook FLOAT (10,2),
statePublisher CHAR (2),
yearPublished INTEGER (4)
);

/* INSERTING DATA OF TABLE */

INSERT INTO livros (
bookName, bookAuthor, authorSex, numberPages, namePublisher, priceBook, statePublisher, yearPublished)
VALUES
('Cavaleiro Real', 'Ana Claudia', 'F', 465, 'Atlas', 49.90, 'RJ', 2009),
('SQL para leigos', 'Joao Nunes', 'M', 450, 'Addison', 98.00, 'SP', 2018),
('Receitas Caseiras', 'Celia Tavares', 'F', 210, 'Atlas', 45.00, 'RJ', 2018),
('Pessoas Efetivas','Eduardo Santos','M',390,'Beta',78.99,'RJ',2018),
('Habitos Saudaveis','Eduardo Santos','M',630,'Beta',150.98,'RJ',2019),
('A Casa Marrom','Hermes Macedo','M',250,'Bubba',60,'MG',2016),
('Estacio Querido','Geraldo Francisco','M',310,'Insignia',100,'ES',2015),
('Pra sempre amigas','Leda Silva','F',510,'Insignia',78.98,'ES',2011),
('Copas Inesqueciveis','Marco Alcantara','M',200,'Larson',130.98,'RS',2018),
('O poder da mente','Clara Mafra','F',120,'Continental',56.58,'SP',2017)
;

/* 1 - ALL DATA OF DATABASE */

SELECT * , now() AS dateQuery FROM livros;

/* 2 - All books and publishers */

SELECT bookName, namePublisher FROM livros;

/* 3 - Name of Book, State of Publisher and Male Author */

SELECT bookName, statePublisher, bookAuthor FROM livros
WHERE authorSex = 'M';

/* 4 - Name of Book, Number of Pages and Female Author */

SELECT bookName, numberPages, bookAuthor FROM livros
WHERE authorSex = 'F';

/* 5 - Price of books from SP Publisher state */

SELECT bookName, priceBook FROM livros
WHERE statePublisher = 'SP';

/* 6 - All data from male authors from RJ or SP Publisher state */

SELECT * from livros
WHERE authorSex = 'M' 
AND statePublisher IN ('RJ', 'SP');