<file_sep>Je suis <NAME>. Je suis très contente de faire ce bootcamp!
<file_sep>SET SQL_SAFE_UPDATES = 0;

UPDATE salesperson
SET store = 'MIAMI'
WHERE name = "MARTIN";

UPDATE customers 
SET email = "<EMAIL>"
WHERE name = "<NAME>";

UPDATE customers 
SET email = "<EMAIL>"
WHERE name = "<NAME>";

UPDATE customers 
SET email = "<EMAIL>"
WHERE name = "<NAME>";

UPDATE customers
SET email = "<EMAIL>"
WHERE name = "<NAME>";
<file_sep>/*Who Have Published What At Where?*/
SELECT 
    a.au_id AS AUTHOR_ID,
    a.au_lname AS LAST_NAME,
    a.au_fname AS FIRST_NAME,
    t.title AS TITLE,
    p.pub_name AS PUBLISHER
FROM
    authors a,
    titleauthor ta,
    titles t,
    publishers p
WHERE
    a.au_id = ta.au_id
        AND ta.title_id = t.title_id
        AND t.pub_id = p.pub_id;

/*Check*/
SELECT 
    COUNT(*)
FROM
    titleauthor;

/*Who Have Published How Many At Where?*/
SELECT 
    a.au_id AS AUTHOR_ID,
    a.au_lname AS LAST_NAME,
    a.au_fname AS FIRST_NAME,
    p.pub_name AS PUBLISHER,
    COUNT(t.title) AS TITLE_COUNT
FROM
    authors a,
    titleauthor ta,
    titles t,
    publishers p
WHERE
    a.au_id = ta.au_id
        AND ta.title_id = t.title_id
        AND t.pub_id = p.pub_id
GROUP BY AUTHOR_ID , LAST_NAME , FIRST_NAME , PUBLISHER;

/*Check*/
SELECT 
    a.au_id AS AUTHOR_ID,
    a.au_lname AS LAST_NAME,
    a.au_fname AS FIRST_NAME,
    p.pub_name AS PUBLISHER,
    COUNT(t.title) AS TITLE_COUNT
FROM
    authors a,
    titleauthor ta,
    titles t,
    publishers p
WHERE
    a.au_id = ta.au_id
        AND ta.title_id = t.title_id
        AND t.pub_id = p.pub_id
GROUP BY AUTHOR_ID , LAST_NAME , FIRST_NAME , PUBLISHER;

SELECT 
    SUM(TITLE_COUNT)
FROM
    challenge2;

/*Best Selling Authors*/
SELECT 
    a.au_id AS AUTHOR_ID,
    a.au_lname AS LAST_NAME,
    a.au_fname AS FIRST_NAME,
    SUM(s.qty) AS TITLES_SOLD
FROM
    authors a,
    titleauthor ta,
    titles t,
    sales s
WHERE
    a.au_id = ta.au_id
        AND ta.title_id = t.title_id
        AND t.title_id = s.title_id
GROUP BY AUTHOR_ID , LAST_NAME , FIRST_NAME
ORDER BY TITLES_SOLD DESC
LIMIT 3;

/*Best Selling Authors*/
SELECT 
    a.au_id AS AUTHOR_ID,
    a.au_lname AS LAST_NAME,
    a.au_fname AS FIRST_NAME,
    IFNULL(SUM(s.qty), 0) AS TITLES_SOLD
FROM
    authors a
        LEFT JOIN
    titleauthor ta ON a.au_id = ta.au_id
        LEFT JOIN
    titles t ON ta.title_id = t.title_id
        LEFT JOIN
    sales s ON t.title_id = s.title_id
GROUP BY AUTHOR_ID , LAST_NAME , FIRST_NAME
ORDER BY TITLES_SOLD DESC;
<file_sep>/*Challenge 1 - Step 1*/
	SELECT 
    titleauthor.au_id AS authorID,
    titleauthor.title_id AS titleID,
    titles.advance * titleauthor.royaltyper / 100 AS advance,
    titles.price * sales.qty * titles.royalty / 100 AS sales_royalty
FROM
    titleauthor
        INNER JOIN
    titles ON titleauthor.title_id = titles.title_id
        INNER JOIN
    sales ON titles.title_id = sales.title_id;
/*GROUP BY authorID, titleID*/

/*Challenge 1 - Step 2*/
SELECT 
    authorID, titleID, SUM(sales_royalty), advance AS sales_royalty_sum
FROM
    (SELECT 
        titleauthor.au_id AS authorID,
            titleauthor.title_id AS titleID,
            titles.advance * titleauthor.royaltyper / 100 AS advance,
            titles.price * sales.qty * titles.royalty / 100 AS sales_royalty
    FROM
        titleauthor
    INNER JOIN titles ON titleauthor.title_id = titles.title_id
    INNER JOIN sales ON titles.title_id = sales.title_id) advance_royalty_list
GROUP BY authorID , titleID;

/*Challenge 1 - Step 3*/
SELECT 
    authorID,
    SUM(advance_sum) + SUM(sales_royalty_sum) AS total_profit
FROM
    (SELECT 
        authorID,
            titleID,
            SUM(sales_royalty) AS sales_royalty_sum,
            advance AS advance_sum
    FROM
        (SELECT 
        titleauthor.au_id AS authorID,
            titleauthor.title_id AS titleID,
            titles.advance * titleauthor.royaltyper / 100 AS advance,
            titles.price * sales.qty * titles.royalty / 100 AS sales_royalty
    FROM
        titleauthor
    INNER JOIN titles ON titleauthor.title_id = titles.title_id
    INNER JOIN sales ON titles.title_id = sales.title_id) advance_royalty_list
    GROUP BY authorID , titleID) advance_royalty_aggregated
GROUP BY authorID
ORDER BY total_profit DESC
LIMIT 3;

/*Challenge 2 - Step 1*/
DROP TABLE advance_royalty_list;
CREATE TEMPORARY TABLE advance_royalty_list
SELECT titleauthor.au_id AS authorID,
titleauthor.title_id AS titleID, 
titles.advance * titleauthor.royaltyper / 100 AS advance,
titles.price * sales.qty * titles.royalty / 100 AS sales_royalty
FROM titleauthor
INNER JOIN titles
ON titleauthor.title_id = titles.title_id
INNER JOIN sales
ON titles.title_id = sales.title_id;

/*Challenge 2 - Step 2*/
DROP TABLE advance_royalty_aggregated;
CREATE TEMPORARY TABLE advance_royalty_aggregated
SELECT authorID, titleID, SUM(sales_royalty) AS sales_royalty_sum, SUM(advance) as advance_sum
FROM advance_royalty_list
GROUP BY authorID, titleID;

/*Challenge 2 - Step 3*/
CREATE TEMPORARY TABLE result
SELECT authorID, SUM(advance_sum) + SUM(sales_royalty_sum) AS total_profit 
FROM advance_royalty_aggregated
GROUP BY authorID
ORDER BY total_profit DESC
LIMIT 3;

/*Challenge 3*/
CREATE TABLE most_profiting_authors (
    au_id VARCHAR(45),
    profits FLOAT(2)
);

INSERT INTO most_profiting_authors
SELECT * FROM result<file_sep>DELETE FROM cars WHERE id = 1;

SELECT * FROM cars;<file_sep>[LocalizedFileNames]
2.-EDA.ipynb=@2.-EDA,0
<file_sep>Je suis Ji Na
<file_sep>*********Project Description**********

Teenage forum textual data in a csv file provided from a Mongolian start-up company is translated into english using Google Translate API.



*********Project Goals**********

* Handle csv file to create a dataset
* Create a database with different tables in MySQL
* Request MySQL database via python
* Use Google Translate API to translate long texts



**********Diagram to illustrate**********

[Recieved a csv file		[Saved the mongolian text		[Fetch the mongolian text				[Save the translated text		[Join two tables of mongolian text
			==> 					==> 	from the database and 				==> 					==>	and its translation to create a new table
with mongolian text]		into a database table]			translate it using Google Translation API]		into a new table]			with mongolian and english text with its emoji]



**********Resources**********

CSV file with teenage forum data
Google Translate API



**********Links**********

Trello : https://trello.com/b/cuMbUGnX/module-1-project-kanban
Github : https://github.com/jina2020/dataV2-labs.git (module-1/Module 1 project)<file_sep>USE lab_mysql;

CREATE TABLE IF NOT EXISTS `lab_mysql`.`Customers` (
  `id` INT NULL AUTO_INCREMENT,
  `idCustomer` VARCHAR(45) NOT NULL,
  `name` VARCHAR(45) NOT NULL,
  `phone_number` VARCHAR(45) NOT NULL,
  `email` VARCHAR(45) NOT NULL,
  `adress` VARCHAR(45) NULL,
  `city` VARCHAR(45) NULL,
  `state` VARCHAR(45) NULL,
  `country` VARCHAR(45) NULL,
  `zip_code` VARCHAR(45) NULL,
  UNIQUE INDEX `idCar_UNIQUE` (`id` ASC),
  PRIMARY KEY (`idCustomer`))
ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS `lab_mysql`.`Cars` (
  `id` INT NULL AUTO_INCREMENT,
  `idCar` VARCHAR(45) NOT NULL,
  `manufacturer` VARCHAR(45) NULL,
  `model` VARCHAR(45) NULL,
  `year` INT NULL,
  `color` VARCHAR(45) NULL,
  UNIQUE INDEX `id_UNIQUE` (`id` ASC),
  PRIMARY KEY (`idCar`))
ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS `lab_mysql`.`SalesPerson` (
  `id` INT NULL AUTO_INCREMENT,
  `SalesPersoncol` VARCHAR(45) NOT NULL,
  `name` VARCHAR(45) NOT NULL,
  `store` VARCHAR(45) NOT NULL,
  UNIQUE INDEX `id_UNIQUE` (`id` ASC),
  PRIMARY KEY (`SalesPersoncol`))
ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS `lab_mysql`.`Invoices` (
  `id` INT NULL AUTO_INCREMENT,
  `idInvoice` VARCHAR(45) NOT NULL,
  `date` DATETIME NOT NULL,
  `car` VARCHAR(45) NOT NULL,
  `customer` VARCHAR(45) NOT NULL,
  `salesperson` VARCHAR(45) NOT NULL,
  `Customers_idCustomer` VARCHAR(45) NOT NULL,
  `SalesPerson_SalesPersoncol` VARCHAR(45) NOT NULL,
  `Cars_idCar` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`idInvoice`, `Customers_idCustomer`, `SalesPerson_SalesPersoncol`, `Cars_idCar`),
  UNIQUE INDEX `id_UNIQUE` (`id` ASC),
  INDEX `fk_Invoices_Customers_idx` (`Customers_idCustomer` ASC),
  INDEX `fk_Invoices_SalesPerson1_idx` (`SalesPerson_SalesPersoncol` ASC),
  INDEX `fk_Invoices_Cars1_idx` (`Cars_idCar` ASC),
  CONSTRAINT `fk_Invoices_Customers`
    FOREIGN KEY (`Customers_idCustomer`)
    REFERENCES `lab_mysql`.`Customers` (`idCustomer`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Invoices_SalesPerson1`
    FOREIGN KEY (`SalesPerson_SalesPersoncol`)
    REFERENCES `lab_mysql`.`SalesPerson` (`SalesPersoncol`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Invoices_Cars1`
    FOREIGN KEY (`Cars_idCar`)
    REFERENCES `lab_mysql`.`Cars` (`idCar`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

