Monday, 11 October 2021

FOREIGN KEY sql videos in telugu 62

 https://youtu.be/Iz-4KIF0-aY

-----------------------------------------------------
DELETE FROM table_name WHERE condition;

SQL JOIN
A JOIN clause is used to combine rows from two or more tables, based on a related column between them.




(INNER) JOIN: Returns records that have matching values in both tables
LEFT (OUTER) JOIN: Returns all records from the left table, and the matched records from the right table
RIGHT (OUTER) JOIN: Returns all records from the right table, and the matched records from the left table
FULL (OUTER) JOIN: Returns all records when there is a match in either left or right table
CREATE TABLE customers(
    cust_id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(100),
    last_name VARCHAR(100),
    email VARCHAR(100)
);


CREATE TABLE orders(
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_date DATE,
    amount DECIMAL(8,2),
    customer_id INT,
    FOREIGN KEY(customer_id) REFERENCES customers(cust_id)
);

INSERT INTO customers (first_name, last_name, email) 
VALUES ('venkat', 'vlr', '[email protected]'),
       ('praveen', 'g', '[email protected]'),
       ('lakshman', 'k', '[email protected]'),
       ('Naveen', 'd', '[email protected]'),
       ('ambani', 'e', '[email protected]');
       
INSERT INTO orders (order_date, amount, customer_id)
VALUES ('2021/05/10', 199.99, 300),
       ('2021/09/11', 321.50, 301),
       ('2021/06/12', 789.67, 303),
       ('2021/01/03', 102.50, 304),
       ('2021/04/11', 850.25, 305),
       ('2021/03/13', 689.25, 500),
       ('2021/02/11', 31.50, 301),
       ('2021/06/12', 79.67, 303),
       ('2021/08/03', 102.50, 304),
       ('2021/07/11', 80.25, 305),
       ('2021/12/13', 669.25, 500);


Finding Orders Placed By venkat: Using a subquery


SELECT * FROM orders WHERE customer_id =
    (
        SELECT cust_id FROM customers
        WHERE first_name='venkat'
    );

------------------
Warning: C:\Program Files\MySQL\MySQL Server 5.6\bin\mysql.exe: ignoring option '--no-beep' due to invalid value ''
Enter password: ********
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.51-log MySQL Community Server (GPL)

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

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 vlrinst
Database changed
mysql> show tables;
+-------------------+
| Tables_in_vlrinst |
+-------------------+
| customers         |
| dept              |
| emp               |
| faculty           |
| orders            |
| student           |
+-------------------+
6 rows in set (0.00 sec)

mysql> desc customers;
+------------+--------------+------+-----+---------+----------------+
| Field      | Type         | Null | Key | Default | Extra          |
+------------+--------------+------+-----+---------+----------------+
| cust_id    | int(11)      | NO   | PRI | NULL    | auto_increment |
| first_name | varchar(100) | YES  |     | NULL    |                |
| last_name  | varchar(100) | YES  |     | NULL    |                |
| email      | varchar(100) | YES  |     | NULL    |                |
+------------+--------------+------+-----+---------+----------------+
4 rows in set (0.06 sec)

mysql> desc orders;
+-------------+--------------+------+-----+---------+----------------+
| Field       | Type         | Null | Key | Default | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| id          | int(11)      | NO   | PRI | NULL    | auto_increment |
| order_date  | date         | YES  |     | NULL    |                |
| amount      | decimal(8,2) | YES  |     | NULL    |                |
| customer_id | int(11)      | YES  | MUL | NULL    |                |
+-------------+--------------+------+-----+---------+----------------+
4 rows in set (0.04 sec)

mysql> slect * from customers;
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 customers' at line 1
mysql> select * from customers;
+---------+------------+-----------+------------------------------+
| cust_id | first_name | last_name | email                        |
+---------+------------+-----------+------------------------------+
|     300 | venkat     | ch        | [email protected]        |
|     301 | venkat     | vlr       | [email protected] |
|     302 | praveen    | g         | [email protected]          |
|     303 | lakshman   | k         | [email protected]         |
|     304 | Naveen     | d         | [email protected]            |
|     305 | ambani     | e         | [email protected]             |
|     500 | naresh     | ch        | [email protected]        |
|     501 | venkat     | vlr       | [email protected] |
|     502 | praveen    | g         | [email protected]          |
|     503 | lakshman   | k         | [email protected]         |
|     504 | Naveen     | d         | [email protected]            |
|     505 | ambani     | e         | [email protected]             |
+---------+------------+-----------+------------------------------+
12 rows in set (0.00 sec)

mysql> delete from customers where cust_id in (502,503,504,505);
Query OK, 4 rows affected (0.11 sec)

mysql> select * from customers;
+---------+------------+-----------+------------------------------+
| cust_id | first_name | last_name | email                        |
+---------+------------+-----------+------------------------------+
|     300 | venkat     | ch        | [email protected]        |
|     301 | venkat     | vlr       | [email protected] |
|     302 | praveen    | g         | [email protected]          |
|     303 | lakshman   | k         | [email protected]         |
|     304 | Naveen     | d         | [email protected]            |
|     305 | ambani     | e         | [email protected]             |
|     500 | naresh     | ch        | [email protected]        |
|     501 | venkat     | vlr       | [email protected] |
+---------+------------+-----------+------------------------------+
8 rows in set (0.00 sec)

mysql> delete from customers where cust_id =501;
Query OK, 1 row affected (0.06 sec)

mysql> update customers set first_name ="ramesh" where cust_id=300;
Query OK, 1 row affected (0.06 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from customers;
+---------+------------+-----------+------------------------------+
| cust_id | first_name | last_name | email                        |
+---------+------------+-----------+------------------------------+
|     300 | ramesh     | ch        | [email protected]        |
|     301 | venkat     | vlr       | [email protected] |
|     302 | praveen    | g         | [email protected]          |
|     303 | lakshman   | k         | [email protected]         |
|     304 | Naveen     | d         | [email protected]            |
|     305 | ambani     | e         | [email protected]             |
|     500 | naresh     | ch        | [email protected]        |
+---------+------------+-----------+------------------------------+
7 rows in set (0.00 sec)

mysql> select * from orders;
+----+------------+--------+-------------+
| id | order_date | amount | customer_id |
+----+------------+--------+-------------+
|  6 | 2021-05-10 | 199.99 |         301 |
+----+------------+--------+-------------+
1 row in set (0.00 sec)

mysql> INSERT INTO orders (order_date, amount, customer_id)
    -> VALUES ('2021/05/10', 199.99, 300),
    ->        ('2021/09/11', 321.50, 301),
    ->        ('2021/06/12', 789.67, 303),
    ->        ('2021/01/03', 102.50, 304),
    ->        ('2021/04/11', 850.25, 305);
Query OK, 5 rows affected (0.08 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql>        ('2021/03/13', 689.25, 500);
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 ''2021/03/13', 689.25, 500)' at line 1
mysql>        ('2021/02/11', 31.50, 301),
    ->        ('2021/06/12', 79.67, 303),
    ->        ('2021/08/03', 102.50, 304),
    ->        ('2021/07/11', 80.25, 305);
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 ''2021/02/11', 31.50, 301),
       ('2021/06/12', 79.67, 303),
       ('2021/08/0' at line 1
mysql> delete from orders;
Query OK, 6 rows affected (0.05 sec)

mysql> INSERT INTO orders (order_date, amount, customer_id)
    -> VALUES ('2021/05/10', 199.99, 300),
    ->        ('2021/09/11', 321.50, 301),
    ->        ('2021/06/12', 789.67, 303),
    ->        ('2021/01/03', 102.50, 304),
    ->        ('2021/04/11', 850.25, 305);
Query OK, 5 rows affected (0.05 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql>        ('2021/03/13', 689.25, 500);
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 ''2021/03/13', 689.25, 500)' at line 1
mysql>        ('2021/02/11', 31.50, 301),
    ->        ('2021/06/12', 79.67, 303),
    ->        ('2021/08/03', 102.50, 304),
    ->        ('2021/07/11', 80.25, 305);
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 ''2021/02/11', 31.50, 301),
       ('2021/06/12', 79.67, 303),
       ('2021/08/0' at line 1
mysql> delete from orders;
Query OK, 5 rows affected (0.07 sec)

mysql> INSERT INTO orders (order_date, amount, customer_id)
    -> VALUES ('2021/05/10', 199.99, 300),
    ->        ('2021/09/11', 321.50, 301),
    ->        ('2021/06/12', 789.67, 303),
    ->        ('2021/01/03', 102.50, 304),
    ->        ('2021/04/11', 850.25, 305),
    ->        ('2021/03/13', 689.25, 500),
    ->        ('2021/02/11', 31.50, 301),
    ->        ('2021/06/12', 79.67, 303),
    ->        ('2021/08/03', 102.50, 304),
    ->        ('2021/07/11', 80.25, 305),
    ->        ('2021/12/13', 669.25, 500);
Query OK, 11 rows affected (0.10 sec)
Records: 11  Duplicates: 0  Warnings: 0

mysql> select * from customers;
+---------+------------+-----------+------------------------------+
| cust_id | first_name | last_name | email                        |
+---------+------------+-----------+------------------------------+
|     300 | ramesh     | ch        | [email protected]        |
|     301 | venkat     | vlr       | [email protected] |
|     302 | praveen    | g         | [email protected]          |
|     303 | lakshman   | k         | [email protected]         |
|     304 | Naveen     | d         | [email protected]            |
|     305 | ambani     | e         | [email protected]             |
|     500 | naresh     | ch        | [email protected]        |
+---------+------------+-----------+------------------------------+
7 rows in set (0.00 sec)

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

mysql> select * from orders where customer_id =
    -> (select cust_id from customers where first_name="Venkat");
+----+------------+--------+-------------+
| id | order_date | amount | customer_id |
+----+------------+--------+-------------+
| 18 | 2021-09-11 | 321.50 |         301 |
| 23 | 2021-02-11 |  31.50 |         301 |
+----+------------+--------+-------------+
2 rows in set (0.00 sec)

mysql>


cross join
sELECT * FROM customers, orders;

No comments:

Post a Comment

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