--  DROP DATABASE `classicmodels`;

--  CREATE DATABASE IF NOT EXISTS `classicmodels`;
-- SHOW DATABASES;
-- USE `classicmodels`;
DROP TABLE IF EXISTS `offices`;

CREATE TABLE `offices` (
  `officeCode` varchar(10) NOT NULL,
  `city` varchar(50) NOT NULL,
  `phone` varchar(50) NOT NULL,
  `addressLine1` varchar(50) NOT NULL,
  `addressLine2` varchar(50),
  `state` varchar(50),
  `country` varchar(50) NOT NULL,
  `postalCode` varchar(15) NOT NULL,
  `territory` varchar(10) NOT NULL,
  PRIMARY KEY (`officeCode`)
);

SHOW TABLES; -- showing table

INSERT INTO `offices` 

(officeCode,city,phone,addressLine1,addressLine2,state,country,postalCode,territory) VALUES 

('1','San Francisco','+1 650 219 4782','100 Market Street','Suite 300','CA','USA','94080','NA'),

('2','Boston','+1 215 837 0825','1550 Court Place','Suite 102','MA','USA','02107','NA'),

('3','NYC','+1 212 555 3000','523 East 53rd Street','apt. 5A','NY','USA','10022','NA'),

('4','Paris','+33 14 723 4404','43 Rue Jouffroy D\'abbans',NULL,NULL,'France','75017','EMEA'),

('5','Tokyo','+81 33 224 5000','4-1 Kioicho',NULL,'Chiyoda-Ku','Japan','102-8578','Japan'),

('6','Sydney','+61 2 9264 2451','5-11 Wentworth Avenue','Floor #2',NULL,'Australia','NSW 2010','APAC'),

('7','London','+44 20 7877 2041','25 Old Broad Street','Level 7',NULL,'UK','EC2N 1HN','EMEA'),

('aaa','San Francisco','+1 650 219 4782','100 Market Street','Suite 300','CA','USA','94080','NA');


SELECT * FROM `offices`;

DESCRIBE `offices`;

select officeCode , city from offices;

-- Q.Add some more entires into the offices table, using just the required (NOT NULL) columns.

INSERT INTO `offices` 

(officeCode,city,phone,addressLine1,country,postalCode,territory) VALUES 

('8','kolkata','+91 8080808080','MCKV institute of E','india','743126','Howrah');

SELECT * FROM `offices`;

-- Q2. Explore what happens if you don't provide a value for a column marked as not null.
-- Ans. ERROR 1048 (23000) at line 60: Column 'territory' cannot be null

-- INSERT INTO `offices` 
-- (officeCode,city,phone,addressLine1,country,postalCode,territory) VALUES 
-- ('8','kolkata','+91 8080808080','MCKV institute of E','india','743126',NULL);
-- SELECT * FROM `offices`;


-- Q3. Try adding an entry with a primary key matching the an existing entry.
-- Ans. ERROR 1062 (23000) at line 69: Duplicate entry '8' for key 'offices.PRIMARY'

-- INSERT INTO `offices` 
-- (officeCode,city,phone,addressLine1,country,postalCode,territory) VALUES 
-- ('8','Jagatdal','+91 65567','Techno institute of E','india','743126','KOlkata');
-- SELECT * FROM `offices`;

-- Q4. Retrieve and display just the city and phone number information for each office.

select city , phone from offices;

-- DROP TABLE IF EXISTS `employees`;

CREATE TABLE `employees` (
  `employeeNumber` int(11) NOT NULL,
  `lastName` varchar(50) NOT NULL,
  `firstName` varchar(50) NOT NULL,
  `extension` varchar(10) NOT NULL,
  `email` varchar(100) NOT NULL,
  `officeCode` varchar(10) NOT NULL,
  `reportsTo` int(11) DEFAULT NULL,
  `jobTitle` varchar(50) NOT NULL,
  PRIMARY KEY (`employeeNumber`),
  FOREIGN KEY (`reportsTo`) REFERENCES `employees` (`employeeNumber`),
  FOREIGN KEY (`officeCode`) REFERENCES `offices` (`officeCode`)
);

INSERT INTO `employees` VALUES
(0001,'John','Wick','x2024','[email protected]','8',null,'Elder One'),
(1002,'Murphy','Diane','x5800','[email protected]','1',0001,'President'),
(1056,'Patterson','Mary','x4611','[email protected]','1',1002,'VP Sales'),
(1076,'Firrelli','Jeff','x9273','[email protected]','1',1002,'VP Marketing'),
(1088,'Patterson','William','x4871','[email protected]','6',1056,'Sales Manager (APAC)'),
(1102,'Bondur','Gerard','x5408','[email protected]','4',1056,'Sale Manager (EMEA)'),
(1143,'Bow','Anthony','x5428','[email protected]','1',1056,'Sales Manager (NA)'),
(1165,'Jennings','Leslie','x3291','[email protected]','1',1143,'Sales Rep'),
(1166,'Thompson','Leslie','x4065','[email protected]','1',1143,'Sales Rep'),
(1188,'Firrelli','Julie','x2173','[email protected]','2',1143,'Sales Rep'),
(1216,'Patterson','Steve','x4334','[email protected]','2',1143,'Sales Rep'),
(1286,'Tseng','Foon Yue','x2248','[email protected]','3',1143,'Sales Rep'),
(1323,'Vanauf','George','x4102','[email protected]','3',1143,'Sales Rep'),
(1337,'Bondur','Loui','x6493','[email protected]','4',1102,'Sales Rep'),
(1370,'Hernandez','Gerard','x2028','[email protected]','4',1102,'Sales Rep'),
(1401,'Castillo','Pamela','x2759','[email protected]','4',1102,'Sales Rep'),
(1501,'Bott','Larry','x2311','[email protected]','7',1102,'Sales Rep'),
(1504,'Jones','Barry','x102','[email protected]','7',1102,'Sales Rep'),
(1611,'Fixter','Andy','x101','[email protected]','6',1088,'Sales Rep'),
(1612,'Marsh','Peter','x102','[email protected]','6',1088,'Sales Rep'),
(1619,'King','Tom','x103','[email protected]','6',1088,'Sales Rep'),
(1621,'Nishi','Mami','x101','[email protected]','5',1056,'Sales Rep'),
(1625,'Kato','Yoshimi','x102','[email protected]','5',1621,'Sales Rep'),
(1702,'Gerard','Martin','x2312','[email protected]','4',1102,'Sales Rep');

select * from employees;

-- Create a new office location and add some employees for the new location.
-- (Done Already see kolkata officecode 8 and in employees john wick employeeNumber - 0001)

CREATE TABLE IF NOT EXISTS `Customers` (
  `customerNumber` INT(11) NOT NULL,
  `customerName` VARCHAR(50) NOT NULL,
  `contactLastName` VARCHAR(50) NOT NULL,
  `contactFirstName` VARCHAR(50) NOT NULL,
  `phone` VARCHAR(50) NOT NULL,
  `addressLine1` VARCHAR(50) NOT NULL,
  `addressLine2` VARCHAR(50) NULL DEFAULT NULL,
  `city` VARCHAR(50) NOT NULL,
  `state` VARCHAR(50) NULL DEFAULT NULL,
  `postalCode` VARCHAR(15) NULL DEFAULT NULL,
  `country` VARCHAR(50) NOT NULL,
  `salesRepEmployeeNumber` INT(11) NULL,
  `creditLimit` DOUBLE NULL DEFAULT NULL,
  `customerLocation` POINT NOT NULL,
  PRIMARY KEY (`customerNumber`),
  FOREIGN KEY (`salesRepEmployeeNumber`) REFERENCES `employees` (`employeeNumber`));
INSERT INTO `Customers` 
(`customerNumber`,`customerName`,`contactLastName`,`contactFirstName`,`phone`,`addressLine1`,`addressLine2`,`city`,`state`,`postalCode`,`country`,`salesRepEmployeeNumber`,`creditLimit`,`customerLocation`)
VALUES
 ('103','Atelier graphique','Schmitt','Carine ','40.32.2555','54, rue Royale',NULL,'Nantes',NULL,'44000','France','1370','21000',ST_GeomFromText('POINT(47.2168424 -1.5567445)', 4326)),
 ('112','Signal Gift Stores','King','Jean','7025551838','8489 Strong St.',NULL,'Las Vegas','NV','83030','USA','1166','71800',ST_GeomFromText('POINT(36.114646 -115.172816)', 4326)),
 ('114','Australian Collectors, Co.','Ferguson','Peter','03 9520 4555','636 St Kilda Road','Level 3','Melbourne','Victoria','3004','Australia','1611','117300',ST_GeomFromText('POINT(-37.8131869 144.9629796)', 4326)),
 ('119','La Rochelle Gifts','Labrune','Janine ','40.67.8555','67, rue des Cinquante Otages',NULL,'Nantes',NULL,'44000','France','1370','118200',ST_GeomFromText('POINT(47.2168424 -1.5567445)', 4326)),
 ('121','Baane Mini Imports','Bergulfsen','Jonas ','07-98 9555','Erling Skakkes gate 78',NULL,'Stavern',NULL,'4110','Norway','1504','81700',ST_GeomFromText('POINT(58.9982871 10.0355946)', 4326)),
 ('124','Mini Gifts Distributors Ltd.','Nelson','Susan','4155551450','5677 Strong St.',NULL,'San Rafael','CA','97562','USA','1165','210500',ST_GeomFromText('POINT(37.9735346 -122.5310874)', 4326)),
 ('125','Havel & Zbyszek Co','Piestrzeniewicz','Zbyszek ','(26) 642-7555','ul. Filtrowa 68',NULL,'Warszawa',NULL,'01-012','Poland',NULL,'0',ST_GeomFromText('POINT(52.2296756 21.0122287)', 4326)),
 ('128','Blauer See Auto, Co.','Keitel','Roland','+49 69 66 90 2555','Lyonerstr. 34',NULL,'Frankfurt',NULL,'60528','Germany','1504','59700',ST_GeomFromText('POINT(50.1115118 8.6805059)', 4326)),
 ('129','Mini Wheels Co.','Murphy','Julie','6505555787','5557 North Pendale Street',NULL,'San Francisco','CA','94217','USA','1165','64600',ST_GeomFromText('POINT(37.7749295 -122.465158)', 4326)),
 ('131','Land of Toys Inc.','Lee','Kwai','2125557818','897 Long Airport Avenue',NULL,'NYC','NY','10022','USA','1323','114900',ST_GeomFromText('POINT(40.7143528 -74.0059731)', 4326)),
 ('141','Euro+ Shopping Channel','Freyre','Diego ','(91) 555 94 44','C/ Moralzarzal, 86',NULL,'Madrid',NULL,'28034','Spain','1370','227600',ST_GeomFromText('POINT(40.4166909 -3.7003454)', 4326)),
 ('144','Volvo Model Replicas, Co','Berglund','Christina ','0921-12 3555','Berguvsv',NULL,'Lule',NULL,'S-958 22','Sweden','1504','53100',ST_GeomFromText('POINT(65.6216366 21.9852321)', 4326)),
 ('145','Danish Wholesale Imports','Petersen','Jytte ','31 12 3555','Vinb',NULL,'Kobenhavn',NULL,'1734','Denmark','1401','83400',ST_GeomFromText('POINT(55.693403 12.583046)', 4326)),
 ('146','Saveley & Henriot, Co.','Saveley','Mary ','78.32.5555','2, rue du Commerce',NULL,'Lyon',NULL,'69004','France','1337','123900',ST_GeomFromText('POINT(45.767299 4.8343287)', 4326)),
 ('148','Dragon Souveniers, Ltd.','Natividad','Eric','+65 221 7555','Bronz Sok.','Bronz Apt. 3/6 Tesvikiye','Singapore',NULL,'079903','Singapore','1621','103800',ST_GeomFromText('POINT(1.352083 103.819836)', 4326));
 
 select * from Customers;

 SHOW TABLES;

 SELECT firstName ,lastName ,employeeNumber FROM `employees` WHERE `jobTitle`="Sales Rep";
 SELECT firstName ,lastName ,employeeNumber FROM `employees` WHERE `jobTitle`="Sales Rep" and officeCode="1";

delete FROM offices WHERE officeCode="aaa";

SELECT * FROM offices;

update employees set reportsTo="1002" WHERE `jobTitle`="Sales Rep" and officeCode="1";

SELECT firstName ,lastName ,reportsTo FROM `employees` WHERE `jobTitle`="Sales Rep" and officeCode="1";

-- List the customers in the United States with a credit limit higher than \$1000.
SELECT customerName , customerNumber from customers WHERE country="USA" and creditLimit >= "1000";

-- List the employee codes for sales representatives of customers in Spain, France and Italy.
-- Make another query to list the names and email addresses of those employees.

SELECT salesRepEmployeeNumber from customers WHERE country IN ("Spain" , "France" , "Italy");

SELECT firstName , lastName , email from employees where employeeNumber IN(1370 , 1337);


-- Change the job title "Sales Rep" to "Sales Representative"
update employees SET jobTitle="Sales Representative" where jobTitle= "Sales Rep";
SELECT * from employees WHERE jobTitle="Sales Representative";

-- Delete the entries for Sales Representatives working in London.
-- delete from employees WHERE officeCode = "7" and jobTitle = "Sales Representative"; (NOT WORKING)

-- Show a list of employees who are not sales representatives

 SELECT firstName ,lastName ,employeeNumber FROM `employees` WHERE `jobTitle` <> "Sales Rep";
  -- SELECT firstName ,lastName ,employeeNumber FROM `employees` WHERE `jobTitle` ! ="Sales Rep"; same as upper

-- Show a list of customers with "Toys" in their name
SELECT customerName, country from Customers where customerName LIKE "%Toys%" ;