-- Pass: King@4056
-- Port: 5432

-- https://www.youtube.com/watch?v=H6988OpZKTU&t=557s
-- https://www.programiz.com/sql/inner-join


-- Insert data from current table
INSERT INTO Orders (order_id, item, amount, customer_id)
VALUES (8, 'Hari Ram', 16000, 2);


SELECT * FROM `customer` as c INNER JOIN `paymeny` as p ON c.customer_id = p.customer_id


-- @@@@@@@@@@@ Using inner Join we find selective record from 2 different tables

SELECT n.first_name, n.last_name, o.item, o.amount
FROM NewCustomer1 as n
INNER JOIN Orders as o 
ON n.customer_id = o.customer_id;

-- INNER JOIN

SELECT * 
FROM customer as c 
INNER JOIN payment as p 
ON c.customer_id = p.customer_id;

SELECT n.first_name, n.last_name, o.salary, o.amount
FROM NewCustomer1 as c 
INNER JOIN Orders as o 
ON n.customer_id = o.customer_id;

-- LEFT JOIN

SELECT * 
FROM customer AS c
LEFT JOIN payment AS p
ON c.customer_id = p.customer_id;

-- RIGHT JOIN

SELECT *
FROM customer AS c
RIGHT JOIN payment AS p
ON c.customer_id = p.customer_id;

-- FULL OUTER JOIN

SELECT *
FROM customer AS c
FULL OUTER JOIN payment AS p
ON c.customer_id = p.customer_id;

-- #############################################################

-- @@@@@@@@@@@ Using inner Join we find selective record from 2 different tables
-- Method 1
SELECT --first_name, last_name, amount, Count(*) 
    	 first_name, last_name, amount
FROM Orders INNER JOIN NewCustomer1 WHERE Orders.customer_id = NewCustomer1.customer_id;

-- Method 2
SELECT first_name, last_name, amount, Count(*) 
    -- first_name, last_name, amount
FROM Orders INNER JOIN NewCustomer1 WHERE NewCustomer1.customer_id = Orders.customer_id;

-- Or
SELECT first_name, last_name, item, amount
FROM NewCustomer1 INNER JOIN Orders 
ON NewCustomer1.customer_id = Orders.customer_id;

-- How to create new table which structure should be same as another table here not insert row or data

CREATE TABLE customer3 AS SELECT * FROM Customers WHERE 1=2;

-- Create a duplicate table if you already have a table with data

-- Select * Into customer From tbl1 Where 1 = 2 	//  not working

CREATE TABLE NewCustomer AS SELECT * FROM Customers;

-- If you already have a table with same structure and you just want to copy data then use this.

Insert into NewCustomer Select * from Customers;

-- Delete spacific row from the table from your database or delete any single row according to row id

DELETE FROM NewCustomer WHERE customer_id=1;

-- Delete all record or row from the table

DELETE FROM NewCustomer;

-- Delete table from your database
DROP TABLE NewCustomer;

-- Delete all row data 

TRUNCATE TABLE NewCustomer1

-- mysql query to find duplicate records are there in the table or not
with ctc as(
SELECT *, row_number() over (partition by customer_id order by customer_id) as rn FROM NewCustomer)
SELECT * FROM ctc WHERE rn>1

select first_name, count(*) as rn from Customers group by first_name having rn > 1;

SELECT COUNT(*),customer_id FROM NewCustomer GROUP BY customer_id;		-- // Show All row with duplicate row
-- Method 1
SELECT COUNT(*),customer_id FROM NewCustomer GROUP BY customer_id HAVING COUNT(*)>1 		-- // Show only duplicate row 
-- Method 2
SELECT *, row_number() over(partition by customer_id order by customer_id) as rn FROM NewCustomer




--  Find duplicate record according to first name
SELECT first_name, COUNT(first_name) 
FROM NewCustomer
GROUP BY first_name
HAVING COUNT(first_name) > 1;

-- Delete duplicate record from the table

DELETE FROM Customers
WHERE customer_id IN (
    SELECT customer_id
    FROM Customers
    GROUP BY customer_id
    HAVING COUNT(*) > 1
);

-- Add New Collumn in if already have db table

ALTER TABLE Customers ADD Email varchar(255);

-- Add Multiple New Collumn in if already have db table

ALTER TABLE customer2
    ADD COLUMN count SMALLINT(6) NOT NULL AFTER `last_name`,
    ADD COLUMN city VARCHAR(12) NOT NULL AFTER `count`,
    ADD COLUMN state VARCHAR(12) NOT NULL AFTER city;


-- Find last 2nd heighest salary // mysql query to find last 2nd highest salary
SELECT MAX(amount) FROM Orders WHERE amount < (SELECT MAX(amount) FROM Orders);

-- find nth heighest salary

SELECT DISTINCT amount
FROM Orders
ORDER BY amount DESC
LIMIT 3;


-- Find 4th heighest salary, here set number you want to find  like 3
Select amount from Orders order by amount DESC limit 3,1;

-- #############################################################

-- SELECT first_name, age, status FROM Shippings, Customers;


-- ===============================================================

CREATE TABLE students (
  id INT NOT NULL auto_increment,
  name varchar(100) not null,
  email varchar(100) not null unique,
  city_id int null,
  primary key(id),
  foreign key (city_id) references cities(cid)
);