Case Statement sql videos in telugu 61

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

SQL FOREIGN KEY Constraint

The FOREIGN KEY constraint is used to prevent actions that would destroy links between tables.

A FOREIGN KEY is a field (or collection of fields) in one table, that refers to the PRIMARY KEY in another table.

The table with the foreign key is called the child table, and the table with the primary key is called the referenced or parent 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', 'venkat.vlrtraining@gmail.com'),

('praveen', 'g', 'g.praveen@gmail.com'),

('lakshman', 'k', 'lakshman.k@gmail.com'),

('Naveen', 'd', 'Naveend@gmail.com'),

('ambani', 'e', 'e.ambani@aol.com');

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

---------

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 11

Server version: 5.6.51-log 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 vlrinst;

Database changed

mysql> show tables;

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

| Tables_in_vlrinst |

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

| dept |

| emp |

| faculty |

| student |

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

4 rows in set (0.00 sec)

mysql> CREATE TABLE customers(

-> cust_id INT AUTO_INCREMENT PRIMARY KEY,

-> first_name VARCHAR(100),

-> last_name VARCHAR(100),

-> email VARCHAR(100)

-> );

Query OK, 0 rows affected (0.24 sec)

mysql> insert into customers(cust_id,first_name,last_name,email)

-> values(300,"venkat","ch","ramesh.1204@gmail.com");

Query OK, 1 row affected (0.06 sec)

mysql> select * from customers;

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

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

| 300 | venkat | ch | ramesh.1204@gmail.com |

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

1 row in set (0.00 sec)

mysql> insert into customers(cust_id,first_name,last_name,email)

-> values(300,"naresh","ch","ramesh.1204@gmail.com");

ERROR 1062 (23000): Duplicate entry '300' for key 'PRIMARY'

mysql> INSERT INTO customers (first_name, last_name, email)

-> VALUES ('venkat', 'vlr', 'venkat.vlrtraining@gmail.com'),

-> ('praveen', 'g', 'g.praveen@gmail.com'),

-> ('lakshman', 'k', 'lakshman.k@gmail.com'),

-> ('Naveen', 'd', 'Naveend@gmail.com'),

-> ('ambani', 'e', 'e.ambani@aol.com');

Query OK, 5 rows affected (0.04 sec)

Records: 5 Duplicates: 0 Warnings: 0

mysql> select * from customers;

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

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

| 300 | venkat | ch | ramesh.1204@gmail.com |

| 301 | venkat | vlr | venkat.vlrtraining@gmail.com |

| 302 | praveen | g | g.praveen@gmail.com |

| 303 | lakshman | k | lakshman.k@gmail.com |

| 304 | Naveen | d | Naveend@gmail.com |

| 305 | ambani | e | e.ambani@aol.com |

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

6 rows in set (0.00 sec)

mysql> insert into customers(cust_id,first_name,last_name,email)

-> values(500,"naresh","ch","naresh.1204@gmail.com");

Query OK, 1 row affected (0.07 sec)

mysql> select * from customers;\

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

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

| 300 | venkat | ch | ramesh.1204@gmail.com |

| 301 | venkat | vlr | venkat.vlrtraining@gmail.com |

| 302 | praveen | g | g.praveen@gmail.com |

| 303 | lakshman | k | lakshman.k@gmail.com |

| 304 | Naveen | d | Naveend@gmail.com |

| 305 | ambani | e | e.ambani@aol.com |

| 500 | naresh | ch | naresh.1204@gmail.com |

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

7 rows in set (0.00 sec)

mysql> INSERT INTO customers (first_name, last_name, email)

-> VALUES ('venkat', 'vlr', 'venkat.vlrtraining@gmail.com'),

-> ('praveen', 'g', 'g.praveen@gmail.com'),

-> ('lakshman', 'k', 'lakshman.k@gmail.com'),

-> ('Naveen', 'd', 'Naveend@gmail.com'),

-> ('ambani', 'e', 'e.ambani@aol.com');

Query OK, 5 rows affected (0.04 sec)

Records: 5 Duplicates: 0 Warnings: 0

mysql> select * from customers;\

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

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

| 300 | venkat | ch | ramesh.1204@gmail.com |

| 301 | venkat | vlr | venkat.vlrtraining@gmail.com |

| 302 | praveen | g | g.praveen@gmail.com |

| 303 | lakshman | k | lakshman.k@gmail.com |

| 304 | Naveen | d | Naveend@gmail.com |

| 305 | ambani | e | e.ambani@aol.com |

| 500 | naresh | ch | naresh.1204@gmail.com |

| 501 | venkat | vlr | venkat.vlrtraining@gmail.com |

| 502 | praveen | g | g.praveen@gmail.com |

| 503 | lakshman | k | lakshman.k@gmail.com |

| 504 | Naveen | d | Naveend@gmail.com |

| 505 | ambani | e | e.ambani@aol.com |

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

12 rows in set (0.00 sec)

mysql> 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)

-> );

Query OK, 0 rows affected (0.43 sec)

mysql> INSERT INTO orders (order_date, amount, customer_id)

-> VALUES ('2021/05/10', 199.99, 1),

-> ('2021/04/11', 351.50, 1),

-> ('2021/16/12', 789.67, 2),

-> ('2021/01/03', 102.50, 2),

-> ('2021/04/11', 850.25, 5);

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`vlrinst`.`orders`, CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`cust_id`))

mysql> ('2021/05/13', 689.25, 3);

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/05/13', 689.25, 3)' at line 1

mysql>

mysql> INSERT INTO orders (order_date, amount, customer_id)

-> VALUES ('2021/05/10', 199.99, 301);

Query OK, 1 row affected (0.06 sec)

mysql> select * from orders;

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

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

| 6 | 2021-05-10 | 199.99 | 301 |

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

1 row in set (0.00 sec)

mysql> INSERT INTO orders (order_date, amount, customer_id)

-> ('2021/05/13', 689.25, 900);

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/05/13', 689.25, 900)' at line 2

mysql> INSERT INTO orders (order_date, amount, customer_id)

-> VALUES ('2021/05/10', 199.99, 7701);

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`vlrinst`.`orders`, CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`cust_id`))

mysql>