Right Join in sql sql videos in telugu 66

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

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

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> 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;

+--------+--------+------------+--------+------------+

+--------+--------+------------+--------+------------+

| 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;

+------------+--------+--------+--------------+

+------------+--------+--------+--------------+

| 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;

+--------------+-------+------+-----+---------+-------+

+--------------+-------+------+-----+---------+-------+

| payment_id | int | NO | PRI | NULL | |

| emp_id | int | NO | MUL | 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;

+--------+--------+------------+--------+------------+

+--------+--------+------------+--------+------------+

| 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;

+------------+--------+--------+--------------+

+------------+--------+--------+--------------+

| 305 | 102 | 1800 | 2015-09-15 |