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', '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);
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 | 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> 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 | 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 |
+---------+------------+-----------+------------------------------+
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 | 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> 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 | 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> 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.