Translate

Monday, 11 October 2021

Python with mysql insert records sql videos in telugu 72

 https://youtu.be/xWe3JCIvgXY

-----------------------------------------
import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  password="ram1234",
  database="ramesh"
)

mycursor = mydb.cursor()

mycursor.execute("SELECT * FROM customers")

myresult = mycursor.fetchall()

for x in myresult:
  print(x)
-----------------
import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  password="ram1234",
  database="ramesh"
)

mycursor = mydb.cursor()

mycursor.execute("SELECT name, address FROM customers")

myresult = mycursor.fetchall()

for x in myresult:
  print(x)
---------------------
Using the fetchone() Method
If you are only interested in one row, you can use the fetchone() method.

The fetchone() method will return the first row of the result:

import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  password="ram1234",
  database="ramesh"
)

mycursor = mydb.cursor()

mycursor.execute("SELECT * FROM customers")

myresult = mycursor.fetchone()

print(myresult)
------------
import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  password="ram1234",
  database="ramesh"
)

mycursor = mydb.cursor()

sql = "SELECT * FROM customers WHERE address LIKE '%way%'"

mycursor.execute(sql)

myresult = mycursor.fetchall()

for x in myresult:
  print(x)
------------------------
import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  password="ram1234",
  database="ramesh"
)

mycursor = mydb.cursor()

sql = "SELECT * FROM customers ORDER BY name DESC"

mycursor.execute(sql)

myresult = mycursor.fetchall()

for x in myresult:
  print(x)
---------------------
import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  password="ram1234",
  database="ramesh"
)

mycursor = mydb.cursor()

sql = "DELETE FROM customers WHERE name = 'ram1'"

mycursor.execute(sql)

mydb.commit()

print(mycursor.rowcount, "record(s) deleted")
------------
import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  password="ram1234",
  database="ramesh"
)

mycursor = mydb.cursor()

sql = "DROP TABLE customers"

mycursor.execute(sql)
-------------------
import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  password="ram1234",
  database="ramesh"
)

mycursor = mydb.cursor()

sql = "UPDATE customers SET address = 'Canyon 123' WHERE address = 'Valley 345'"

mycursor.execute(sql)

mydb.commit()

print(mycursor.rowcount, "record(s) affected")

Python with mysql create db ,table sql videos in telugu 71

 https://youtu.be/uya226USaDQ

-----------------------------------------------------
import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  password="ram1234",
  database="ramesh"
)

mycursor = mydb.cursor()

sql = "INSERT INTO customers (name, address) VALUES (%s, %s)"
val = ("ram1", "btm 55")
mycursor.execute(sql, val)

mydb.commit()

print(mycursor.rowcount, "record inserted.")

Notice the statement: mydb.commit(). It is required to make the changes, otherwise no changes are made to the table.
mydb.commit()
-----------------
Insert Multiple Rows

To insert multiple rows into a table, use the executemany() method.

The second parameter of the executemany() method is a list of tuples, containing the data you want to insert:


import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  password="ram1234",
  database="ramesh"
)

mycursor = mydb.cursor()

sql = "INSERT INTO customers (name, address) VALUES (%s, %s)"
val = [
  ('ramesh', 'fLowstreet 4'),
  ('sureesh', 'bApple st 652'),
  ('chiru', 'eMountain 21'),
  ('kumarl', 'gValley 345'),
  ('praveen', 'fOcean blvd 2'),
  ('anji', 'bGreen Grass 1'),
  ('hari', 'nSky st 331'),
  ('nalini', 'nOne way 98'),
  ('nani', 'uYellow Garden 2'),
  ('koti', 'wPark Lane 38'),
  ('gopi', 'qCentral st 954'),
  ('ramu', 'rMain Road 989'),
  ('madhu', 'qqwSideway 1633')
]

mycursor.executemany(sql, val)

mydb.commit()

print(mycursor.rowcount, "was inserted.")
------------------

Get Inserted ID

import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  password="ram1234",
  database="ramesh"
)

mycursor = mydb.cursor()

sql = "INSERT INTO customers (name, address) VALUES (%s, %s)"
val = ("mahesh", "bantumilli")
mycursor.execute(sql, val)

mydb.commit()

print("1 record inserted, ID:", mycursor.lastrowid)

python with mysql sql videos in telugu 70

 https://youtu.be/qEAyeTiEm0c

--------------------------------------------
To create a database in MySQL, use the "CREATE DATABASE" statement:

import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  password="ram1234"
)

my1 = mydb.cursor()

my1.execute("CREATE DATABASE ramesh")
-----------
import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  password="ram1234"
)

mycursor = mydb.cursor()

mycursor.execute("SHOW DATABASES")

for x in mycursor:
  print(x)
----------------------
Creating a Table




import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  password="ram1234",
  database="ramesh"
)

mycursor = mydb.cursor()

mycursor.execute("CREATE TABLE customers (name VARCHAR(255), address VARCHAR(255))")
---------------

You can check if a table exist by listing all tables in your database with the "SHOW TABLES" statement:



import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  password="ram1234",
  database="ramesh"
)

mycursor = mydb.cursor()

mycursor.execute("SHOW TABLES")

for x in mycursor:
  print(x)
Primary Key
When creating a table, you should also create a column with a unique key for each record.

This can be done by defining a PRIMARY KEY.

We use the statement "INT AUTO_INCREMENT PRIMARY KEY" which will insert a unique number for each record. Starting at 1, and increased by one for each record.

Example
Create primary key when creating the table:

import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  password="ram1234",
  database="ramesh"
)

mycursor = mydb.cursor()

mycursor.execute("CREATE TABLE customers (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255), address VARCHAR(255))")
If the table already exists, use the ALTER TABLE keyword:

Example
Create primary key on an existing table:

import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  password="ram1234",
  database="ramesh"
)

mycursor = mydb.cursor()

mycursor.execute("ALTER TABLE customers ADD COLUMN id INT AUTO_INCREMENT PRIMARY KEY")
-----------------------
Microsoft Windows [Version 10.0.19042.1110]
(c) Microsoft Corporation. All rights reserved.

C:\Users\VLR Training>cd desktop

C:\Users\VLR Training\Desktop>pyton ram.py
'pyton' is not recognized as an internal or external command,
operable program or batch file.

C:\Users\VLR Training\Desktop>python ram.py

C:\Users\VLR Training\Desktop>python ram.py
('information_schema',)
('mysql',)
('performance_schema',)
('ram',)
('ramesh',)
('sakila',)
('sys',)
('world',)

C:\Users\VLR Training\Desktop>python ram.py
  File "C:\Users\VLR Training\Desktop\ram.py", line 7
    database="ramesh"
    ^
SyntaxError: invalid syntax

C:\Users\VLR Training\Desktop>python ram.py

C:\Users\VLR Training\Desktop>python ram.py

C:\Users\VLR Training\Desktop>python ram.py
  File "C:\Users\VLR Training\Desktop\ram.py", line 16
    Primary Key
            ^
SyntaxError: invalid syntax

C:\Users\VLR Training\Desktop>python ram.py
('customers',)
('sal',)

C:\Users\VLR Training\Desktop>python ram.py

C:\Users\VLR Training\Desktop>70 python with mysql create db ,table  sql videos in telugu

Joins exercises in sql sql videos in telugu part 02 69

 https://youtu.be/eA5mZFbI4d4

------------------------------
python --version
python -m pip install mysql-connector-python
----------
Test MySQL Connector
To test if the installation was successful, or if you already have "MySQL Connector" installed, create a Python page with the following content:



import mysql.connector
-------------

Create Connection
Start by creating a connection to the database.

Use the username and password from your MySQL database:



import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  password="ram1234"
)

print(mydb)
--------------------------


Microsoft Windows [Version 10.0.19042.1110]
(c) Microsoft Corporation. All rights reserved.

C:\Users\VLR Training>python --version
Python was not found; run without arguments to install from the Microsoft Store, or disable this shortcut from Settings > Manage App Execution Aliases.

C:\Users\VLR Training>python

C:\Users\VLR Training>python --version
Python 3.9.6

C:\Users\VLR Training>python -m pip install mysql-connector-python
Collecting mysql-connector-python
  Downloading mysql_connector_python-8.0.26-cp39-cp39-win_amd64.whl (799 kB)
     |████████████████████████████████| 799 kB 2.2 MB/s
Collecting protobuf>=3.0.0
  Downloading protobuf-3.17.3-cp39-cp39-win_amd64.whl (909 kB)
     |████████████████████████████████| 909 kB 6.4 MB/s
Collecting six>=1.9
  Downloading six-1.16.0-py2.py3-none-any.whl (11 kB)
Installing collected packages: six, protobuf, mysql-connector-python
Successfully installed mysql-connector-python-8.0.26 protobuf-3.17.3 six-1.16.0
WARNING: You are using pip version 21.1.3; however, version 21.2.2 is available.
You should consider upgrading via the 'C:\Users\VLR Training\AppData\Local\Microsoft\WindowsApps\PythonSoftwareFoundation.Python.3.9_qbz5n2kfra8p0\python.exe -m pip install --upgrade pip' command.

C:\Users\VLR Training>cd desktop

C:\Users\VLR Training\Desktop>pyton ram.py
'pyton' is not recognized as an internal or external command,
operable program or batch file.

C:\Users\VLR Training\Desktop>python ram.py

C:\Users\VLR Training\Desktop>python ram.py
  File "C:\Users\VLR Training\Desktop\ram.py", line 10

    ^
SyntaxError: unexpected EOF while parsing

C:\Users\VLR Training\Desktop>python ram.py
<mysql.connector.connection.MySQLConnection object at 0x000001C935AF1BE0>

C:\Users\VLR Training\Desktop>

Joins exercises in sql sql videos in telugu part 01 68

 https://youtu.be/q77fMAE1Tgw

-------------------------------------------
-- PROBLEM 4

SELECT
    first_name,
    IFNULL(AVG(grade), 0) AS average
FROM students
LEFT JOIN papers
    ON students.id = papers.student_id
GROUP BY students.id
ORDER BY average DESC;
-- PROBLEM 5

SELECT first_name, 
       Ifnull(Avg(grade), 0) AS average, 
       CASE 
         
         WHEN Avg(grade) >= 75 THEN 'PASSING' 
WHEN Avg(grade) > 60  THEN 'Medium' 
         ELSE 'FAILING' 
       end                   AS passing_status 
FROM   students 
       LEFT JOIN papers 
              ON students.id = papers.student_id 
GROUP  BY students.id 
ORDER  BY average DESC;



-----------------------------------
Enter password: *******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 14
Server version: 8.0.25 MySQL Community Server - GPL

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use ram
Database changed
mysql> SELECT
    ->     first_name,
    ->     IFNULL(AVG(grade), 0) AS average
    -> FROM students
    -> LEFT JOIN papers
    ->     ON students.id = papers.student_id
    -> GROUP BY students.id
    -> ORDER BY average DESC;
+------------+---------+
| first_name | average |
+------------+---------+
| vinod      | 97.0000 |
| praveen    | 85.0000 |
| ramesh     | 66.5000 |
| lakshman   |  0.0000 |
| harika     |  0.0000 |
+------------+---------+
5 rows in set (0.02 sec)

mysql> select * from papers;
+--------+-------+------------+
| title  | grade | student_id |
+--------+-------+------------+
| ds     |    61 |          1 |
| c      |    72 |          1 |
| java   |    95 |          2 |
| python |    99 |          2 |
| telugu |    85 |          4 |
+--------+-------+------------+
5 rows in set (0.00 sec)

mysql> SELECT
    ->     first_name,
    ->     IFNULL(AVG(grade), 0) AS average
    -> FROM students
    -> LEFT JOIN papers
    ->     ON students.id = papers.student_id
    -> GROUP BY students.id
    -> ORDER BY average DESC;
+------------+---------+
| first_name | average |
+------------+---------+
| vinod      | 97.0000 |
| praveen    | 85.0000 |
| ramesh     | 66.5000 |
| lakshman   |  0.0000 |
| harika     |  0.0000 |
+------------+---------+
5 rows in set (0.00 sec)

mysql> SELECT first_name,
    ->        Ifnull(Avg(grade), 0) AS average,
    ->        CASE
    ->          WHEN Avg(grade) IS NULL THEN 'FAILING'
    ->          WHEN Avg(grade) >= 75 THEN 'PASSING'
    ->          ELSE 'FAILING'
    ->        end                   AS passing_status
    -> FROM   students
    ->        LEFT JOIN papers
    ->               ON students.id = papers.student_id
    -> GROUP  BY students.id
    -> ORDER  BY average DESC;
+------------+---------+----------------+
| first_name | average | passing_status |
+------------+---------+----------------+
| vinod      | 97.0000 | PASSING        |
| praveen    | 85.0000 | PASSING        |
| ramesh     | 66.5000 | FAILING        |
| lakshman   |  0.0000 | FAILING        |
| harika     |  0.0000 | FAILING        |
+------------+---------+----------------+
5 rows in set (0.01 sec)

mysql> SELECT first_name,
    ->        Ifnull(Avg(grade), 0) AS average,
    ->        CASE
    ->
    ->          WHEN Avg(grade) >= 75 THEN 'PASSING'
    ->          ELSE 'FAILING'
    ->        end                   AS passing_status
    -> FROM   students
    ->        LEFT JOIN papers
    ->               ON students.id = papers.student_id
    -> GROUP  BY students.id
    -> ORDER  BY average DESC;
+------------+---------+----------------+
| first_name | average | passing_status |
+------------+---------+----------------+
| vinod      | 97.0000 | PASSING        |
| praveen    | 85.0000 | PASSING        |
| ramesh     | 66.5000 | FAILING        |
| lakshman   |  0.0000 | FAILING        |
| harika     |  0.0000 | FAILING        |
+------------+---------+----------------+
5 rows in set (0.00 sec)

mysql> SELECT first_name,
    ->        Ifnull(Avg(grade), 0) AS average,
    ->        CASE
    ->
    ->          WHEN Avg(grade) >= 75 THEN 'PASSING'
    ->  WHEN Avg(grade) > 60  THEN 'Medium'
    ->          ELSE 'FAILING'
    ->        end                   AS passing_status
    -> FROM   students
    ->        LEFT JOIN papers
    ->               ON students.id = papers.student_id
    -> GROUP  BY students.id
    -> ORDER  BY average DESC;
+------------+---------+----------------+
| first_name | average | passing_status |
+------------+---------+----------------+
| vinod      | 97.0000 | PASSING        |
| praveen    | 85.0000 | PASSING        |
| ramesh     | 66.5000 | Medium         |
| lakshman   |  0.0000 | FAILING        |
| harika     |  0.0000 | FAILING        |
+------------+---------+----------------+
5 rows in set (0.00 sec)

mysql>

ON DELETE CASCADE in sql sql videos in telugu 67

https://youtu.be/9UIy5yzJOT8 

------------------------------------------------------------
mysql> select * from students;
+----+------------+
| id | first_name |
+----+------------+
|  1 | ramesh     |
|  2 | vinod      |
|  3 | lakshman   |
|  4 | praveen    |
|  5 | harika     |
+----+------------+
5 rows in set (0.00 sec)

mysql> select * from papers;
+--------+-------+------------+
| title  | grade | student_id |
+--------+-------+------------+
| ds     |    61 |          1 |
| c      |    72 |          1 |
| java   |    95 |          2 |
| python |    99 |          2 |
| telugu |    85 |          4 |
+--------+-------+------------+
5 rows in set (0.00 sec)

mysql> desc students;
+------------+--------------+------+-----+---------+----------------+
| Field      | Type         | Null | Key | Default | Extra          |
+------------+--------------+------+-----+---------+----------------+
| id         | int          | NO   | PRI | NULL    | auto_increment |
| first_name | varchar(100) | YES  |     | NULL    |                |
+------------+--------------+------+-----+---------+----------------+
2 rows in set (0.09 sec)

mysql> desc papers;
+------------+--------------+------+-----+---------+-------+
| Field      | Type         | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| title      | varchar(100) | YES  |     | NULL    |       |
| grade      | int          | YES  |     | NULL    |       |
| student_id | int          | YES  | MUL | NULL    |       |
+------------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
--------------------------------------

CREATE TABLE students (
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(100)
);


CREATE TABLE papers (
    title VARCHAR(100),
    grade INT,
    student_id INT,
    FOREIGN KEY (student_id) 
        REFERENCES students(id)
        ON DELETE CASCADE
);

-------------
INSERT INTO students (first_name) VALUES 
('ramesh'), 
('vinod'), 
('lakshman'), 
('praveen'), 
('harika');

INSERT INTO papers (student_id, title, grade ) VALUES
(1, 'ds', 61),
(1, 'c', 72),
(2, 'java', 95),
(2, 'python', 99),
(4, 'telugu', 85);

------------------

+------------+--------+-------+
| first_name | title  | grade |
+------------+--------+-------+
| vinod      | python |    99 |
| vinod      | java   |    95 |
| praveen    | telugu |    85 |
| ramesh     | c      |    72 |
| ramesh     | ds     |    61 |
+------------+--------+-------+
5 rows in set (0.00 sec)


SELECT first_name, title, grade
FROM students
INNER JOIN papers
    ON students.id = papers.student_id
ORDER BY grade DESC;
-------------------------------------

SELECT
 first_name,
IFNULL(title, 'MISSING') as title,
IFNULL(grade, 0) as grade
 from students left join papers 
 ON students.id = papers.student_id;


SELECT first_name, title, grade
FROM students
LEFT JOIN papers
    ON students.id = papers.student_id;
-----------------------------------

SELECT
    first_name,
    IFNULL(title, 'MISSING'),
    IFNULL(grade, 0)
FROM students
LEFT JOIN papers
    ON students.id = papers.student_id;
------------------------
-------------------------
Enter password: *******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 13
Server version: 8.0.25 MySQL Community Server - GPL

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use ram
Database changed
mysql> show tables;
+---------------+
| Tables_in_ram |
+---------------+
| customers     |
| emp           |
| orders        |
| papers        |
| pay           |
| students      |
+---------------+
6 rows in set (0.03 sec)

mysql> select * from students;
+----+------------+
| id | first_name |
+----+------------+
|  1 | ramesh     |
|  2 | vinod      |
|  3 | lakshman   |
|  4 | praveen    |
|  5 | harika     |
+----+------------+
5 rows in set (0.00 sec)

mysql> select * from papers;
+--------+-------+------------+
| title  | grade | student_id |
+--------+-------+------------+
| ds     |    61 |          1 |
| c      |    72 |          1 |
| java   |    95 |          2 |
| python |    99 |          2 |
| telugu |    85 |          4 |
+--------+-------+------------+
5 rows in set (0.00 sec)

mysql> desc students;
+------------+--------------+------+-----+---------+----------------+
| Field      | Type         | Null | Key | Default | Extra          |
+------------+--------------+------+-----+---------+----------------+
| id         | int          | NO   | PRI | NULL    | auto_increment |
| first_name | varchar(100) | YES  |     | NULL    |                |
+------------+--------------+------+-----+---------+----------------+
2 rows in set (0.09 sec)

mysql> desc papers;
+------------+--------------+------+-----+---------+-------+
| Field      | Type         | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| title      | varchar(100) | YES  |     | NULL    |       |
| grade      | int          | YES  |     | NULL    |       |
| student_id | int          | YES  | MUL | NULL    |       |
+------------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> ;C:\Users\VLR Training\Desktop\sql recording - Shortcut.lnk"
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"C:\Users\VLR Training\Desktop\sql recording - Shortcut.lnk"' at line 1
mysql> drop table students,papers;
Query OK, 0 rows affected (2.19 sec)

mysql> CREATE TABLE students (
    ->     id INT AUTO_INCREMENT PRIMARY KEY,
    ->     first_name VARCHAR(100)
    -> );
Query OK, 0 rows affected (1.24 sec)

mysql> CREATE TABLE papers (
    ->     title VARCHAR(100),
    ->     grade INT,
    ->     student_id INT,
    ->     FOREIGN KEY (student_id)
    ->         REFERENCES students(id)
    ->         ON DELETE CASCADE
    -> );
Query OK, 0 rows affected (1.37 sec)

mysql> INSERT INTO students (first_name) VALUES
    -> ('ramesh'),
    -> ('vinod'),
    -> ('lakshman'),
    -> ('praveen'),
    -> ('harika');
Query OK, 5 rows affected (0.16 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> INSERT INTO papers (student_id, title, grade ) VALUES
    -> (1, 'ds', 61),
    -> (1, 'c', 72),
    -> (2, 'java', 95),
    -> (2, 'python', 99),
    -> (4, 'telugu', 85);
Query OK, 5 rows affected (0.08 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> SELECT first_name, title, grade
    -> FROM students
    -> INNER JOIN papers
    ->     ON students.id = papers.student_id
    -> ORDER BY grade DESC;
+------------+--------+-------+
| first_name | title  | grade |
+------------+--------+-------+
| vinod      | python |    99 |
| vinod      | java   |    95 |
| praveen    | telugu |    85 |
| ramesh     | c      |    72 |
| ramesh     | ds     |    61 |
+------------+--------+-------+
5 rows in set (0.00 sec)

mysql> select first_name,title,grade from students inner join papers
    -> on students.id=papers.student_id orderby grade desc;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'orderby grade desc' at line 2
mysql> select first_name,title,grade from students inner join papers
    -> on students.id=papers.student_id order by grade desc;
+------------+--------+-------+
| first_name | title  | grade |
+------------+--------+-------+
| vinod      | python |    99 |
| vinod      | java   |    95 |
| praveen    | telugu |    85 |
| ramesh     | c      |    72 |
| ramesh     | ds     |    61 |
+------------+--------+-------+
5 rows in set (0.00 sec)

mysql> SELECT first_name, title, grade
    -> FROM students
    -> LEFT JOIN papers
    ->     ON students.id = papers.student_id;
+------------+--------+-------+
| first_name | title  | grade |
+------------+--------+-------+
| ramesh     | ds     |    61 |
| ramesh     | c      |    72 |
| vinod      | java   |    95 |
| vinod      | python |    99 |
| lakshman   | NULL   |  NULL |
| praveen    | telugu |    85 |
| harika     | NULL   |  NULL |
+------------+--------+-------+
7 rows in set (0.00 sec)

mysql> SELECT first_name, title, grade
    -> FROM students
    -> LEFT JOIN papers
    ->     ON students.id = papers.student_id;
+------------+--------+-------+
| first_name | title  | grade |
+------------+--------+-------+
| ramesh     | ds     |    61 |
| ramesh     | c      |    72 |
| vinod      | java   |    95 |
| vinod      | python |    99 |
| lakshman   | NULL   |  NULL |
| praveen    | telugu |    85 |
| harika     | NULL   |  NULL |
+------------+--------+-------+
7 rows in set (0.00 sec)

mysql> SELECT
    ->     first_name,
    ->     IFNULL(title, 'MISSING'),
    ->     IFNULL(grade, 0)
    -> FROM students
    -> LEFT JOIN papers
    ->     ON students.id = papers.student_id;
+------------+--------------------------+------------------+
| first_name | IFNULL(title, 'MISSING') | IFNULL(grade, 0) |
+------------+--------------------------+------------------+
| ramesh     | ds                       |               61 |
| ramesh     | c                        |               72 |
| vinod      | java                     |               95 |
| vinod      | python                   |               99 |
| lakshman   | MISSING                  |                0 |
| praveen    | telugu                   |               85 |
| harika     | MISSING                  |                0 |
+------------+--------------------------+------------------+
7 rows in set (0.00 sec)

mysql> SELECT
    ->     first_name,
    ->     IFNULL(title, 'MISSING') as title,
    ->     IFNULL(grade, 0) as grade,
    ->     from students left join papers on
    ->     students.id=papers.student_id;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'from students left join papers on
    students.id=papers.student_id' at line 5
mysql> SELECT
    ->     first_name,
    ->     IFNULL(title, 'MISSING') as title,
    ->     IFNULL(grade, 0) as grade,
    ->     from students left join papers on
    ->     students.id = papers.student_id;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'from students left join papers on
    students.id = papers.student_id' at line 5
mysql> SELECTSELECT
    ->     first_name,
    ->     IFNULL(title, 'MISSING'),
    ->     IFNULL(grade, 0)
    -> FROM students
    -> LEFT JOIN papers
    ->     ON students.id = papers.student_id;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECTSELECT
    first_name,
    IFNULL(title, 'MISSING'),
    IFNULL(grade, 0)
' at line 1
mysql> SELECT
    ->  first_name,
    -> IFNULL(title, 'MISSING') as title,
    -> IFNULL(grade, 0) as grade,
    ->  from students left join papers
    ->  on students.id = papers.student_id;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'from students left join papers
 on students.id = papers.student_id' at line 5
mysql> SELECT
    ->  first_name,
    -> IFNULL(title, 'MISSING') as title,
    -> IFNULL(grade, 0) as grade,
    ->  from students left join papers
    ->  ON students.id = papers.student_id;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'from students left join papers
 ON students.id = papers.student_id' at line 5
mysql> SELECT
    ->  first_name,
    -> IFNULL(title, 'MISSING') as title,
    -> IFNULL(grade, 0) as grade
    ->  from students left join papers
    ->  ON students.id = papers.student_id;
+------------+---------+-------+
| first_name | title   | grade |
+------------+---------+-------+
| ramesh     | ds      |    61 |
| ramesh     | c       |    72 |
| vinod      | java    |    95 |
| vinod      | python  |    99 |
| lakshman   | MISSING |     0 |
| praveen    | telugu  |    85 |
| harika     | MISSING |     0 |
+------------+---------+-------+
7 rows in set (0.00 sec)

mysql> SELECT
    ->  first_name,
    -> IFNULL(title, 'MISSING') as title,
    -> IFNULL(grade, 0) as grade
    ->  from students left join papers
    ->  ON students.id = papers.student_id;
+------------+---------+-------+
| first_name | title   | grade |
+------------+---------+-------+
| ramesh     | ds      |    61 |
| ramesh     | c       |    72 |
| vinod      | java    |    95 |
| vinod      | python  |    99 |
| lakshman   | MISSING |     0 |
| praveen    | telugu  |    85 |
| harika     | MISSING |     0 |
+------------+---------+-------+
7 rows in set (0.00 sec)

mysql>











Right Join in sql sql videos in telugu 66

 https://youtu.be/hLd95feK2dU

--------------------------------------------------
Use the ON DELETE CASCADE option to specify whether you want rows deleted in a child table when corresponding rows are deleted in the parent table. If you do not specify cascading deletes, the default behavior of the database server prevents you from deleting data in a table if other tables reference it.
------------------
CREATE TABLE Emp (  
  emp_id int(10) NOT NULL,  
  name varchar(40) NOT NULL,  
  birthdate date NOT NULL,  
  gender varchar(10) NOT NULL,  
  hire_date date NOT NULL,  
  PRIMARY KEY (emp_id)  
);  

INSERT INTO Emp (emp_id, name, birthdate, gender, hire_date) VALUES  
(101, 'Bryan', '1988-08-12', 'M', '2015-08-26'),  
(102, 'Joseph', '1978-05-12', 'M', '2014-10-21'),  
(103, 'Mike', '1984-10-13', 'M', '2017-10-28'),  
(104, 'Daren', '1979-04-11', 'M', '2006-11-01'),  
(105, 'Marie', '1990-02-11', 'F', '2018-10-12');  
------------------
CREATE TABLE Pay (  
  payment_id int(10) PRIMARY KEY NOT NULL,  
  emp_id int(10) NOT NULL,  
  amount float NOT NULL,  
  payment_date date NOT NULL,  
  FOREIGN KEY (emp_id) REFERENCES Emp (emp_id)  ON DELETE CASCADE    
);  


INSERT INTO Pay (payment_id, emp_id, amount, payment_date) VALUES   
(301, 101, 1200, '2015-09-15'),  
(302, 101, 1200, '2015-09-30'),  
(303, 101, 1500, '2015-10-15'),  
(304, 101, 1500, '2015-10-30'),  
(305, 102, 1800, '2015-09-15'),  
(306, 102, 1800, '2015-09-30');
------------------









CREATE TABLE Pay (  
  payment_id int(10) PRIMARY KEY NOT NULL,  
  emp_id int(10) NOT NULL,  
  amount float NOT NULL,  
  payment_date date NOT NULL,  
  FOREIGN KEY (emp_id) REFERENCES Emp (emp_id) ON DELETE CASCADE  
);  


INSERT INTO Pay (payment_id, emp_id, amount, payment_date) VALUES   
(301, 101, 1200, '2015-09-15'),  
(302, 101, 1200, '2015-09-30'),  
(303, 101, 1500, '2015-10-15'),  
(304, 101, 1500, '2015-10-30'),  
(305, 102, 1800, '2015-09-15'),  
(306, 102, 1800, '2015-09-30')
------------------

ON DELETE CASCADE


DELETE FROM Emp WHERE emp_id = 102; 
---------------------
Enter password: *******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 8.0.25 MySQL Community Server - GPL

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use ram;
Database changed
mysql> drop table emp;
Query OK, 0 rows affected (1.55 sec)

mysql> drop table pay;
Query OK, 0 rows affected (0.49 sec)

mysql> show tables;
+---------------+
| Tables_in_ram |
+---------------+
| customers     |
| orders        |
+---------------+
2 rows in set (0.03 sec)

mysql> CREATE TABLE Emp (
    ->   emp_id int(10) NOT NULL,
    ->   name varchar(40) NOT NULL,
    ->   birthdate date NOT NULL,
    ->   gender varchar(10) NOT NULL,
    ->   hire_date date NOT NULL,
    ->   PRIMARY KEY (emp_id)
    -> );
Query OK, 0 rows affected, 1 warning (0.45 sec)

mysql>
mysql> INSERT INTO Emp (emp_id, name, birthdate, gender, hire_date) VALUES
    -> (101, 'Bryan', '1988-08-12', 'M', '2015-08-26'),
    -> (102, 'Joseph', '1978-05-12', 'M', '2014-10-21'),
    -> (103, 'Mike', '1984-10-13', 'M', '2017-10-28'),
    -> (104, 'Daren', '1979-04-11', 'M', '2006-11-01'),
    -> (105, 'Marie', '1990-02-11', 'F', '2018-10-12');
Query OK, 5 rows affected (0.06 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> CREATE TABLE Pay (
    ->   payment_id int(10) PRIMARY KEY NOT NULL,
    ->   emp_id int(10) NOT NULL,
    ->   amount float NOT NULL,
    ->   payment_date date NOT NULL,
    ->   FOREIGN KEY (emp_id) REFERENCES Employee (emp_id)
    -> );
ERROR 1824 (HY000): Failed to open the referenced table 'employee'
mysql>
mysql>
mysql> INSERT INTO Pay (payment_id, emp_id, amount, payment_date) VALUES
    -> (301, 101, 1200, '2015-09-15'),
    -> (302, 101, 1200, '2015-09-30'),
    -> (303, 101, 1500, '2015-10-15'),
    -> (304, 101, 1500, '2015-10-30'),
    -> (305, 102, 1800, '2015-09-15'),
    -> (306, 102, 1800, '2015-09-30')
    -> ;
ERROR 1146 (42S02): Table 'ram.pay' doesn't exist
mysql> CREATE TABLE Pay (
    ->   payment_id int(10) PRIMARY KEY NOT NULL,
    ->   emp_id int(10) NOT NULL,
    ->   amount float NOT NULL,
    ->   payment_date date NOT NULL,
    ->   FOREIGN KEY (emp_id) REFERENCES Emp (emp_id)
    -> );
Query OK, 0 rows affected, 2 warnings (1.31 sec)

mysql>
mysql> INSERT INTO Pay (payment_id, emp_id, amount, payment_date) VALUES
    -> (301, 101, 1200, '2015-09-15'),
    -> (302, 101, 1200, '2015-09-30'),
    -> (303, 101, 1500, '2015-10-15'),
    -> (304, 101, 1500, '2015-10-30'),
    -> (305, 102, 1800, '2015-09-15'),
    -> (306, 102, 1800, '2015-09-30');
Query OK, 6 rows affected (0.17 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> select * from emp;
+--------+--------+------------+--------+------------+
| emp_id | name   | birthdate  | gender | hire_date  |
+--------+--------+------------+--------+------------+
|    101 | Bryan  | 1988-08-12 | M      | 2015-08-26 |
|    102 | Joseph | 1978-05-12 | M      | 2014-10-21 |
|    103 | Mike   | 1984-10-13 | M      | 2017-10-28 |
|    104 | Daren  | 1979-04-11 | M      | 2006-11-01 |
|    105 | Marie  | 1990-02-11 | F      | 2018-10-12 |
+--------+--------+------------+--------+------------+
5 rows in set (0.09 sec)

mysql> delete from emp where emp_id=101;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`ram`.`pay`, CONSTRAINT `pay_ibfk_1` FOREIGN KEY (`emp_id`) REFERENCES `emp` (`emp_id`))
mysql> delete from emp where emp_id=104;
Query OK, 1 row affected (0.08 sec)

mysql> select * from pay;
+------------+--------+--------+--------------+
| payment_id | emp_id | amount | payment_date |
+------------+--------+--------+--------------+
|        301 |    101 |   1200 | 2015-09-15   |
|        302 |    101 |   1200 | 2015-09-30   |
|        303 |    101 |   1500 | 2015-10-15   |
|        304 |    101 |   1500 | 2015-10-30   |
|        305 |    102 |   1800 | 2015-09-15   |
|        306 |    102 |   1800 | 2015-09-30   |
+------------+--------+--------+--------------+
6 rows in set (0.00 sec)

mysql> delete from emp where emp_id=102;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`ram`.`pay`, CONSTRAINT `pay_ibfk_1` FOREIGN KEY (`emp_id`) REFERENCES `emp` (`emp_id`))
mysql> drop table pay;
Query OK, 0 rows affected (1.52 sec)

mysql> CREATE TABLE Pay (
    ->   payment_id int(10) PRIMARY KEY NOT NULL,
    ->   emp_id int(10) NOT NULL,
    ->   amount float NOT NULL,
    ->   payment_date date NOT NULL,
    ->   FOREIGN KEY (emp_id) REFERENCES Emp (emp_id)  ON DELETE CASCADE
    -> );
Query OK, 0 rows affected, 2 warnings (0.97 sec)

mysql>
mysql> INSERT INTO Pay (payment_id, emp_id, amount, payment_date) VALUES
    -> (301, 101, 1200, '2015-09-15'),
    -> (302, 101, 1200, '2015-09-30'),
    -> (303, 101, 1500, '2015-10-15'),
    -> (304, 101, 1500, '2015-10-30'),
    -> (305, 102, 1800, '2015-09-15'),
    -> (306, 102, 1800, '2015-09-30');
Query OK, 6 rows affected (0.31 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> desc pay;
+--------------+-------+------+-----+---------+-------+
| Field        | Type  | Null | Key | Default | Extra |
+--------------+-------+------+-----+---------+-------+
| payment_id   | int   | NO   | PRI | NULL    |       |
| emp_id       | int   | NO   | MUL | NULL    |       |
| amount       | float | NO   |     | NULL    |       |
| payment_date | date  | NO   |     | NULL    |       |
+--------------+-------+------+-----+---------+-------+
4 rows in set (0.13 sec)

mysql> delete from emp where emp_id=101;
Query OK, 1 row affected (0.07 sec)

mysql> slect * from emp;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'slect * from emp' at line 1
mysql> select * from emp;
+--------+--------+------------+--------+------------+
| emp_id | name   | birthdate  | gender | hire_date  |
+--------+--------+------------+--------+------------+
|    102 | Joseph | 1978-05-12 | M      | 2014-10-21 |
|    103 | Mike   | 1984-10-13 | M      | 2017-10-28 |
|    105 | Marie  | 1990-02-11 | F      | 2018-10-12 |
+--------+--------+------------+--------+------------+
3 rows in set (0.00 sec)

mysql> select * from pay;
+------------+--------+--------+--------------+
| payment_id | emp_id | amount | payment_date |
+------------+--------+--------+--------------+
|        305 |    102 |   1800 | 2015-09-15   |
|        306 |    102 |   1800 | 2015-09-30   |
+------------+--------+--------+--------------+
2 rows in set (0.00 sec)

mysql>













left Join in sql sql videos in telugu 65

 https://youtu.be/peZ8si1Is5E

-------------------------------------------------
The RIGHT JOIN keyword returns all records from the right table (table2), and the matching records from the left table (table1). The result is 0 records from the left side, if there is no match.

SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name = table2.column_name;
---------
CREATE TABLE Emp (  
  emp_id int(10) NOT NULL,  
  name varchar(40) NOT NULL,  
  birthdate date NOT NULL,  
  gender varchar(10) NOT NULL,  
  hire_date date NOT NULL,  
  PRIMARY KEY (emp_id)  
);  

INSERT INTO Emp (emp_id, name, birthdate, gender, hire_date) VALUES  
(101, 'Bryan', '1988-08-12', 'M', '2015-08-26'),  
(102, 'Joseph', '1978-05-12', 'M', '2014-10-21'),  
(103, 'Mike', '1984-10-13', 'M', '2017-10-28'),  
(104, 'Daren', '1979-04-11', 'M', '2006-11-01'),  
(105, 'Marie', '1990-02-11', 'F', '2018-10-12');  
------------------
CREATE TABLE Pay (  
  payment_id int(10) PRIMARY KEY NOT NULL,  
  emp_id int(10) NOT NULL,  
  amount float NOT NULL,  
  payment_date date NOT NULL
  
);  


INSERT INTO Pay (payment_id, emp_id, amount, payment_date) VALUES   
(401, 106, 1200, '2015-09-15'),  
(402, 105, 1200, '2015-09-30'),  
(403, 109, 1500, '2015-10-15'),  
(404, 110, 1500, '2015-10-30'),  
(405, 144, 1800, '2015-09-15'),  
(406, 102, 1800, '2015-09-30');

 select first_name,amount from customers
left join orders
on customers.cust_id=orders.customer_id
;

 select * from customers
right join orders
on customers.cust_id=orders.customer_id
;



Enter password: *******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 8.0.25 MySQL Community Server - GPL

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select ram;
ERROR 1054 (42S22): Unknown column 'ram' in 'field list'
mysql> show databases
    -> ;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| ram                |
| sakila             |
| sys                |
| world              |
+--------------------+
7 rows in set (0.01 sec)

mysql> use ram;
Database changed
mysql> show tables;
+---------------+
| Tables_in_ram |
+---------------+
| customers     |
| orders        |
+---------------+
2 rows in set (0.00 sec)

mysql>  select first_name,amount from customers
    -> left join orders
    -> on customers.cust_id=orders.customer_id
    -> ;
+------------+--------+
| first_name | amount |
+------------+--------+
| venkat     | 199.99 |
| praveen    | 321.50 |
| praveen    |  31.50 |
| lakshman   |   NULL |
| Naveen     | 789.67 |
| Naveen     |  79.67 |
| ambani     | 102.50 |
| ambani     | 102.50 |
| saritha    | 850.25 |
| saritha    |  80.25 |
| harika     | 689.25 |
| harika     | 669.25 |
+------------+--------+
12 rows in set (0.00 sec)

mysql>  select * from customers
    -> left join orders
    -> on customers.cust_id=orders.customer_id
    -> ;
+---------+------------+-----------+------------------------------+------+------------+--------+-------------+
| cust_id | first_name | last_name | email                        | id   | order_date | amount | customer_id |
+---------+------------+-----------+------------------------------+------+------------+--------+-------------+
|     300 | venkat     | vlr       | venkat.vlrtraining@gmail.com |   12 | 2021-05-10 | 199.99 |         300 |
|     301 | praveen    | g         | g.praveen@gmail.com          |   13 | 2021-09-11 | 321.50 |         301 |
|     301 | praveen    | g         | g.praveen@gmail.com          |   18 | 2021-02-11 |  31.50 |         301 |
|     302 | lakshman   | k         | lakshman.k@gmail.com         | NULL | NULL       |   NULL |        NULL |
|     303 | Naveen     | d         | Naveend@gmail.com            |   14 | 2021-06-12 | 789.67 |         303 |
|     303 | Naveen     | d         | Naveend@gmail.com            |   19 | 2021-06-12 |  79.67 |         303 |
|     304 | ambani     | e         | e.ambani@aol.com             |   15 | 2021-01-03 | 102.50 |         304 |
|     304 | ambani     | e         | e.ambani@aol.com             |   20 | 2021-08-03 | 102.50 |         304 |
|     305 | saritha    | g         | saritha@gmail.com            |   16 | 2021-04-11 | 850.25 |         305 |
|     305 | saritha    | g         | saritha@gmail.com            |   21 | 2021-07-11 |  80.25 |         305 |
|     500 | harika     | p         | harika.p@aol.com             |   17 | 2021-03-13 | 689.25 |         500 |
|     500 | harika     | p         | harika.p@aol.com             |   22 | 2021-12-13 | 669.25 |         500 |
+---------+------------+-----------+------------------------------+------+------------+--------+-------------+
12 rows in set (0.00 sec)

mysql>  select * from customers
    -> right join orders
    -> on customers.cust_id=orders.customer_id
    -> ;
+---------+------------+-----------+------------------------------+----+------------+--------+-------------+
| cust_id | first_name | last_name | email                        | id | order_date | amount | customer_id |
+---------+------------+-----------+------------------------------+----+------------+--------+-------------+
|     300 | venkat     | vlr       | venkat.vlrtraining@gmail.com | 12 | 2021-05-10 | 199.99 |         300 |
|     301 | praveen    | g         | g.praveen@gmail.com          | 13 | 2021-09-11 | 321.50 |         301 |
|     303 | Naveen     | d         | Naveend@gmail.com            | 14 | 2021-06-12 | 789.67 |         303 |
|     304 | ambani     | e         | e.ambani@aol.com             | 15 | 2021-01-03 | 102.50 |         304 |
|     305 | saritha    | g         | saritha@gmail.com            | 16 | 2021-04-11 | 850.25 |         305 |
|     500 | harika     | p         | harika.p@aol.com             | 17 | 2021-03-13 | 689.25 |         500 |
|     301 | praveen    | g         | g.praveen@gmail.com          | 18 | 2021-02-11 |  31.50 |         301 |
|     303 | Naveen     | d         | Naveend@gmail.com            | 19 | 2021-06-12 |  79.67 |         303 |
|     304 | ambani     | e         | e.ambani@aol.com             | 20 | 2021-08-03 | 102.50 |         304 |
|     305 | saritha    | g         | saritha@gmail.com            | 21 | 2021-07-11 |  80.25 |         305 |
|     500 | harika     | p         | harika.p@aol.com             | 22 | 2021-12-13 | 669.25 |         500 |
+---------+------------+-----------+------------------------------+----+------------+--------+-------------+
11 rows in set (0.00 sec)

mysql>
mysql> CREATE TABLE Emp (
    ->   emp_id int(10) NOT NULL,
    ->   name varchar(40) NOT NULL,
    ->   birthdate date NOT NULL,
    ->   gender varchar(10) NOT NULL,
    ->   hire_date date NOT NULL,
    ->   PRIMARY KEY (emp_id)
    -> );
Query OK, 0 rows affected, 1 warning (2.57 sec)

mysql>
mysql> INSERT INTO Emp (emp_id, name, birthdate, gender, hire_date) VALUES
    -> (101, 'Bryan', '1988-08-12', 'M', '2015-08-26'),
    -> (102, 'Joseph', '1978-05-12', 'M', '2014-10-21'),
    -> (103, 'Mike', '1984-10-13', 'M', '2017-10-28'),
    -> (104, 'Daren', '1979-04-11', 'M', '2006-11-01'),
    -> (105, 'Marie', '1990-02-11', 'F', '2018-10-12');
Query OK, 5 rows affected (0.29 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> CREATE TABLE Pay (
    ->   payment_id int(10) PRIMARY KEY NOT NULL,
    ->   emp_id int(10) NOT NULL,
    ->   amount float NOT NULL,
    ->   payment_date date NOT NULL,
    ->
    -> );
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 7
mysql>
mysql>
mysql> INSERT INTO Pay (payment_id, emp_id, amount, payment_date) VALUES
    -> (301, 101, 1200, '2015-09-15'),
    -> (302, 101, 1200, '2015-09-30'),
    -> (303, 101, 1500, '2015-10-15'),
    -> (304, 101, 1500, '2015-10-30'),
    -> (305, 102, 1800, '2015-09-15'),
    -> (306, 102, 1800, '2015-09-30')
    ->
    -> ;
ERROR 1146 (42S02): Table 'ram.pay' doesn't exist
mysql> CREATE TABLE Pay (
    ->   payment_id int(10) PRIMARY KEY NOT NULL,
    ->   emp_id int(10) NOT NULL,
    ->   amount float NOT NULL,
    ->   payment_date date NOT NULL,
    ->
    -> );
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 7
mysql> CREATE TABLE Pay (
    ->   payment_id int(10) PRIMARY KEY NOT NULL,
    ->   emp_id int(10) NOT NULL,
    ->   amount float NOT NULL,
    ->   payment_date date NOT NULL
    ->
    -> );
Query OK, 0 rows affected, 2 warnings (1.14 sec)

mysql>
mysql> INSERT INTO Pay (payment_id, emp_id, amount, payment_date) VALUES
    -> (301, 101, 1200, '2015-09-15'),
    -> (302, 101, 1200, '2015-09-30'),
    -> (303, 101, 1500, '2015-10-15'),
    -> (304, 101, 1500, '2015-10-30'),
    -> (305, 102, 1800, '2015-09-15'),
    -> (306, 102, 1800, '2015-09-30');
Query OK, 6 rows affected (0.17 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> select * from emp
    -> ;
+--------+--------+------------+--------+------------+
| emp_id | name   | birthdate  | gender | hire_date  |
+--------+--------+------------+--------+------------+
|    101 | Bryan  | 1988-08-12 | M      | 2015-08-26 |
|    102 | Joseph | 1978-05-12 | M      | 2014-10-21 |
|    103 | Mike   | 1984-10-13 | M      | 2017-10-28 |
|    104 | Daren  | 1979-04-11 | M      | 2006-11-01 |
|    105 | Marie  | 1990-02-11 | F      | 2018-10-12 |
+--------+--------+------------+--------+------------+
5 rows in set (0.02 sec)

mysql> select * from pay;
+------------+--------+--------+--------------+
| payment_id | emp_id | amount | payment_date |
+------------+--------+--------+--------------+
|        301 |    101 |   1200 | 2015-09-15   |
|        302 |    101 |   1200 | 2015-09-30   |
|        303 |    101 |   1500 | 2015-10-15   |
|        304 |    101 |   1500 | 2015-10-30   |
|        305 |    102 |   1800 | 2015-09-15   |
|        306 |    102 |   1800 | 2015-09-30   |
+------------+--------+--------+--------------+
6 rows in set (0.00 sec)

mysql> select * from emp left join pay on
    -> emp.emp_id = pay.emp_id;
+--------+--------+------------+--------+------------+------------+--------+--------+--------------+
| emp_id | name   | birthdate  | gender | hire_date  | payment_id | emp_id | amount | payment_date |
+--------+--------+------------+--------+------------+------------+--------+--------+--------------+
|    101 | Bryan  | 1988-08-12 | M      | 2015-08-26 |        304 |    101 |   1500 | 2015-10-30   |
|    101 | Bryan  | 1988-08-12 | M      | 2015-08-26 |        303 |    101 |   1500 | 2015-10-15   |
|    101 | Bryan  | 1988-08-12 | M      | 2015-08-26 |        302 |    101 |   1200 | 2015-09-30   |
|    101 | Bryan  | 1988-08-12 | M      | 2015-08-26 |        301 |    101 |   1200 | 2015-09-15   |
|    102 | Joseph | 1978-05-12 | M      | 2014-10-21 |        306 |    102 |   1800 | 2015-09-30   |
|    102 | Joseph | 1978-05-12 | M      | 2014-10-21 |        305 |    102 |   1800 | 2015-09-15   |
|    103 | Mike   | 1984-10-13 | M      | 2017-10-28 |       NULL |   NULL |   NULL | NULL         |
|    104 | Daren  | 1979-04-11 | M      | 2006-11-01 |       NULL |   NULL |   NULL | NULL         |
|    105 | Marie  | 1990-02-11 | F      | 2018-10-12 |       NULL |   NULL |   NULL | NULL         |
+--------+--------+------------+--------+------------+------------+--------+--------+--------------+
9 rows in set (0.00 sec)

mysql> select * from emp right join pay on
    -> emp.emp_id = pay.emp_id;
+--------+--------+------------+--------+------------+------------+--------+--------+--------------+
| emp_id | name   | birthdate  | gender | hire_date  | payment_id | emp_id | amount | payment_date |
+--------+--------+------------+--------+------------+------------+--------+--------+--------------+
|    101 | Bryan  | 1988-08-12 | M      | 2015-08-26 |        301 |    101 |   1200 | 2015-09-15   |
|    101 | Bryan  | 1988-08-12 | M      | 2015-08-26 |        302 |    101 |   1200 | 2015-09-30   |
|    101 | Bryan  | 1988-08-12 | M      | 2015-08-26 |        303 |    101 |   1500 | 2015-10-15   |
|    101 | Bryan  | 1988-08-12 | M      | 2015-08-26 |        304 |    101 |   1500 | 2015-10-30   |
|    102 | Joseph | 1978-05-12 | M      | 2014-10-21 |        305 |    102 |   1800 | 2015-09-15   |
|    102 | Joseph | 1978-05-12 | M      | 2014-10-21 |        306 |    102 |   1800 | 2015-09-30   |
+--------+--------+------------+--------+------------+------------+--------+--------+--------------+
6 rows in set (0.00 sec)

mysql> INSERT INTO Pay (payment_id, emp_id, amount, payment_date) VALUES
    -> (401, 106, 1200, '2015-09-15'),
    -> (402, 105, 1200, '2015-09-30'),
    -> (403, 109, 1500, '2015-10-15'),
    -> (404, 110, 1500, '2015-10-30'),
    -> (405, 144, 1800, '2015-09-15'),
    -> (406, 102, 1800, '2015-09-30');
Query OK, 6 rows affected (0.11 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> select * from emp right join pay on
    -> emp.emp_id = pay.emp_id;
+--------+--------+------------+--------+------------+------------+--------+--------+--------------+
| emp_id | name   | birthdate  | gender | hire_date  | payment_id | emp_id | amount | payment_date |
+--------+--------+------------+--------+------------+------------+--------+--------+--------------+
|    101 | Bryan  | 1988-08-12 | M      | 2015-08-26 |        301 |    101 |   1200 | 2015-09-15   |
|    101 | Bryan  | 1988-08-12 | M      | 2015-08-26 |        302 |    101 |   1200 | 2015-09-30   |
|    101 | Bryan  | 1988-08-12 | M      | 2015-08-26 |        303 |    101 |   1500 | 2015-10-15   |
|    101 | Bryan  | 1988-08-12 | M      | 2015-08-26 |        304 |    101 |   1500 | 2015-10-30   |
|    102 | Joseph | 1978-05-12 | M      | 2014-10-21 |        305 |    102 |   1800 | 2015-09-15   |
|    102 | Joseph | 1978-05-12 | M      | 2014-10-21 |        306 |    102 |   1800 | 2015-09-30   |
|   NULL | NULL   | NULL       | NULL   | NULL       |        401 |    106 |   1200 | 2015-09-15   |
|    105 | Marie  | 1990-02-11 | F      | 2018-10-12 |        402 |    105 |   1200 | 2015-09-30   |
|   NULL | NULL   | NULL       | NULL   | NULL       |        403 |    109 |   1500 | 2015-10-15   |
|   NULL | NULL   | NULL       | NULL   | NULL       |        404 |    110 |   1500 | 2015-10-30   |
|   NULL | NULL   | NULL       | NULL   | NULL       |        405 |    144 |   1800 | 2015-09-15   |
|    102 | Joseph | 1978-05-12 | M      | 2014-10-21 |        406 |    102 |   1800 | 2015-09-30   |
+--------+--------+------------+--------+------------+------------+--------+--------+--------------+
12 rows in set (0.02 sec)

mysql> INSERT INTO Pay (payment_id, emp_id, amount, payment_date) VALUES
    -> (401, 106, 1200, '2015-09-15'),
    -> (402, 105, 1200, '2015-09-30'),
    -> (403, 109, 1500, '2015-10-15'),
    -> (404, 110, 1500, '2015-10-30'),
    -> (405, 144, 1800, '2015-09-15'),
    -> (406, 102, 1800, '2015-09-30')werwer
    -> ;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'werwer' at line 7
mysql>










--------------------
---------------------
------------------
CREATE TABLE Emp (  
  emp_id int(10) NOT NULL,  
  name varchar(40) NOT NULL,  
  birthdate date NOT NULL,  
  gender varchar(10) NOT NULL,  
  hire_date date NOT NULL,  
  PRIMARY KEY (emp_id)  
);  

INSERT INTO Emp (emp_id, name, birthdate, gender, hire_date) VALUES  
(101, 'Bryan', '1988-08-12', 'M', '2015-08-26'),  
(102, 'Joseph', '1978-05-12', 'M', '2014-10-21'),  
(103, 'Mike', '1984-10-13', 'M', '2017-10-28'),  
(104, 'Daren', '1979-04-11', 'M', '2006-11-01'),  
(105, 'Marie', '1990-02-11', 'F', '2018-10-12');  
------------------
CREATE TABLE Pay (  
  payment_id int(10) PRIMARY KEY NOT NULL,  
  emp_id int(10) NOT NULL,  
  amount float NOT NULL,  
  payment_date date NOT NULL,  
  FOREIGN KEY (emp_id) REFERENCES Employee (emp_id) ON DELETE CASCADE  
);  


INSERT INTO Pay (payment_id, emp_id, amount, payment_date) VALUES   
(301, 101, 1200, '2015-09-15'),  
(302, 101, 1200, '2015-09-30'),  
(303, 101, 1500, '2015-10-15'),  
(304, 101, 1500, '2015-10-30'),  
(305, 102, 1800, '2015-09-15'),  
(306, 102, 1800, '2015-09-30')
------------------

ON DELETE CASCADE


DELETE FROM Emp WHERE emp_id = 102; 

Inner Join in sql sql videos in telugu 64

 https://youtu.be/7IXm2J4xMwQ

----------------------------------------------------
SQL LEFT JOIN Keyword
The LEFT JOIN keyword returns all records from the left table (table1), and the matching records from the right table (table2). The result is 0 records from the right side, if there is no match.

SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name = table2.column_name


 select first_name,cust_id,sum(amount) from customers
 left join orders
 on customers.cust_id=orders.customer_id
group by cust_id;

----------------
 select first_name,cust_id,ifnull(sum(amount),0) from customers
 left join orders
 on customers.cust_id=orders.customer_id
group by cust_id;
-----------
 select first_name,cust_id,ifnull(sum(amount),0) as total
 from customers
 left join orders
 on customers.cust_id=orders.customer_id
group by cust_id 
order by total;
-----------
Enter password: *******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 8.0.25 MySQL Community Server - GPL

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases
    -> ;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| ram                |
| sakila             |
| sys                |
| world              |
+--------------------+
7 rows in set (0.38 sec)

mysql> use ram
Database changed
mysql> show tables;
+---------------+
| Tables_in_ram |
+---------------+
| customers     |
| orders        |
+---------------+
2 rows in set (0.11 sec)

mysql> select * from customers;
+---------+------------+-----------+------------------------------+
| cust_id | first_name | last_name | email                        |
+---------+------------+-----------+------------------------------+
|     300 | venkat     | vlr       | venkat.vlrtraining@gmail.com |
|     301 | praveen    | g         | g.praveen@gmail.com          |
|     302 | lakshman   | k         | lakshman.k@gmail.com         |
|     303 | Naveen     | d         | Naveend@gmail.com            |
|     304 | ambani     | e         | e.ambani@aol.com             |
|     305 | saritha    | g         | saritha@gmail.com            |
|     500 | harika     | p         | harika.p@aol.com             |
+---------+------------+-----------+------------------------------+
7 rows in set (0.12 sec)

mysql> select * from orders;
+----+------------+--------+-------------+
| id | order_date | amount | customer_id |
+----+------------+--------+-------------+
| 12 | 2021-05-10 | 199.99 |         300 |
| 13 | 2021-09-11 | 321.50 |         301 |
| 14 | 2021-06-12 | 789.67 |         303 |
| 15 | 2021-01-03 | 102.50 |         304 |
| 16 | 2021-04-11 | 850.25 |         305 |
| 17 | 2021-03-13 | 689.25 |         500 |
| 18 | 2021-02-11 |  31.50 |         301 |
| 19 | 2021-06-12 |  79.67 |         303 |
| 20 | 2021-08-03 | 102.50 |         304 |
| 21 | 2021-07-11 |  80.25 |         305 |
| 22 | 2021-12-13 | 669.25 |         500 |
+----+------------+--------+-------------+
11 rows in set (0.04 sec)

mysql> select first_name,amount from customers
    -> left join orders
    -> on customers.cust_id=orders.customer_id:
    -> ;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ':' at line 3
mysql> select first_name,amount from customers
    -> left join orders
    -> on customers.cust_id=orders.customer_id;
+------------+--------+
| first_name | amount |
+------------+--------+
| venkat     | 199.99 |
| praveen    | 321.50 |
| praveen    |  31.50 |
| lakshman   |   NULL |
| Naveen     | 789.67 |
| Naveen     |  79.67 |
| ambani     | 102.50 |
| ambani     | 102.50 |
| saritha    | 850.25 |
| saritha    |  80.25 |
| harika     | 689.25 |
| harika     | 669.25 |
+------------+--------+
12 rows in set (0.04 sec)

mysql>  select first_name,amount from customers
    ->  left join orders
    ->  on customers.cust_id=orders.customer_id;
+------------+--------+
| first_name | amount |
+------------+--------+
| venkat     | 199.99 |
| praveen    | 321.50 |
| praveen    |  31.50 |
| lakshman   |   NULL |
| Naveen     | 789.67 |
| Naveen     |  79.67 |
| ambani     | 102.50 |
| ambani     | 102.50 |
| saritha    | 850.25 |
| saritha    |  80.25 |
| harika     | 689.25 |
| harika     | 669.25 |
+------------+--------+
12 rows in set (0.00 sec)

mysql>  select first_name,customer_id,amount from customers
    ->  left join orders
    ->  on customers.cust_id=orders.customer_id;
+------------+-------------+--------+
| first_name | customer_id | amount |
+------------+-------------+--------+
| venkat     |         300 | 199.99 |
| praveen    |         301 | 321.50 |
| praveen    |         301 |  31.50 |
| lakshman   |        NULL |   NULL |
| Naveen     |         303 | 789.67 |
| Naveen     |         303 |  79.67 |
| ambani     |         304 | 102.50 |
| ambani     |         304 | 102.50 |
| saritha    |         305 | 850.25 |
| saritha    |         305 |  80.25 |
| harika     |         500 | 689.25 |
| harika     |         500 | 669.25 |
+------------+-------------+--------+
12 rows in set (0.00 sec)

mysql>  select first_name,cust_id,amount from customers
    ->  left join orders
    ->  on customers.cust_id=orders.customer_id;
+------------+---------+--------+
| first_name | cust_id | amount |
+------------+---------+--------+
| venkat     |     300 | 199.99 |
| praveen    |     301 | 321.50 |
| praveen    |     301 |  31.50 |
| lakshman   |     302 |   NULL |
| Naveen     |     303 | 789.67 |
| Naveen     |     303 |  79.67 |
| ambani     |     304 | 102.50 |
| ambani     |     304 | 102.50 |
| saritha    |     305 | 850.25 |
| saritha    |     305 |  80.25 |
| harika     |     500 | 689.25 |
| harika     |     500 | 669.25 |
+------------+---------+--------+
12 rows in set (0.00 sec)

mysql>
mysql>  select first_name,cust_id,sum(amount) from customers
    ->  left join orders
    ->  on customers.cust_id=orders.customer_id;
+------------+---------+-------------+
| first_name | cust_id | sum(amount) |
+------------+---------+-------------+
| venkat     |     300 |     3916.33 |
+------------+---------+-------------+
1 row in set (0.04 sec)

mysql>  select first_name,cust_id,sum(amount) from customers
    ->  left join orders
    ->  on customers.cust_id=orders.customer_id
    -> group by cust_id;
+------------+---------+-------------+
| first_name | cust_id | sum(amount) |
+------------+---------+-------------+
| venkat     |     300 |      199.99 |
| praveen    |     301 |      353.00 |
| lakshman   |     302 |        NULL |
| Naveen     |     303 |      869.34 |
| ambani     |     304 |      205.00 |
| saritha    |     305 |      930.50 |
| harika     |     500 |     1358.50 |
+------------+---------+-------------+
7 rows in set (0.00 sec)

mysql>  select first_name,cust_id,ifnull(sum(amount),0) from customers
    ->  left join orders
    ->  on customers.cust_id=orders.customer_id
    -> group by cust_id;
+------------+---------+-----------------------+
| first_name | cust_id | ifnull(sum(amount),0) |
+------------+---------+-----------------------+
| venkat     |     300 |                199.99 |
| praveen    |     301 |                353.00 |
| lakshman   |     302 |                  0.00 |
| Naveen     |     303 |                869.34 |
| ambani     |     304 |                205.00 |
| saritha    |     305 |                930.50 |
| harika     |     500 |               1358.50 |
+------------+---------+-----------------------+
7 rows in set (0.02 sec)

mysql>  select first_name,cust_id,ifnull(sum(amount),0) as total
    ->  from customers
    ->  left join orders
    ->  on customers.cust_id=orders.customer_id
    -> group by cust_id;
+------------+---------+---------+
| first_name | cust_id | total   |
+------------+---------+---------+
| venkat     |     300 |  199.99 |
| praveen    |     301 |  353.00 |
| lakshman   |     302 |    0.00 |
| Naveen     |     303 |  869.34 |
| ambani     |     304 |  205.00 |
| saritha    |     305 |  930.50 |
| harika     |     500 | 1358.50 |
+------------+---------+---------+
7 rows in set (0.00 sec)

mysql>  select first_name,cust_id,ifnull(sum(amount),0) as total
    ->  from customers
    ->  left join orders
    ->  on customers.cust_id=orders.customer_id
    -> group by cust_id
    -> orderby total;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'orderby total' at line 6
mysql>  select first_name,cust_id,ifnull(sum(amount),0) as total
    ->  from customers
    ->  left join orders
    ->  on customers.cust_id=orders.customer_id
    -> group by cust_id
    -> order by total;
+------------+---------+---------+
| first_name | cust_id | total   |
+------------+---------+---------+
| lakshman   |     302 |    0.00 |
| venkat     |     300 |  199.99 |
| ambani     |     304 |  205.00 |
| praveen    |     301 |  353.00 |
| Naveen     |     303 |  869.34 |
| saritha    |     305 |  930.50 |
| harika     |     500 | 1358.50 |
+------------+---------+---------+
7 rows in set (0.03 sec)

mysql>