Monday, 11 October 2021

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       | [email protected] |
|     301 | praveen    | g         | [email protected]          |
|     302 | lakshman   | k         | [email protected]         |
|     303 | Naveen     | d         | [email protected]            |
|     304 | ambani     | e         | [email protected]             |
|     305 | saritha    | g         | [email protected]            |
|     500 | harika     | p         | [email protected]             |
+---------+------------+-----------+------------------------------+
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>








No comments:

Post a Comment

Note: only a member of this blog may post a comment.