CHAR is like a fixed-size box: When you use CHAR, you tell the database that you want to store a certain number of characters, no matter how long the actual text is. It's like getting a box that can hold exactly 10 letters. So, if you put the word "apple" in it, it will take up 10 spaces, and 6 of them will be empty.
Example: If you store "apple" in a CHAR(10) column, it will be stored as "apple " (6 empty spaces).
VARCHAR is more flexible: With VARCHAR, the database will only use as much space as needed for the text you want to store. It's like getting a stretchy box that adjusts its size to fit what you put inside.
Example: If you store "apple" in a VARCHAR(10) column, it will be stored as "apple" (no empty spaces).
Now, why is VARCHAR generally better:
So, for most situations, especially when you're not sure how long your text might be, VARCHAR is the preferred choice because it's efficient and flexible. It helps your database use resources wisely and keeps your data storage optimized.
CREATE DATABASE mydatabase;
CREATE DATABASE IF NOT EXISTS db-name;
SHOW DATABASES;
USE mydatabase;
We can also delete a database which is done by command "DROP DATABASE".
The DROP DATABASE statement is used to drop an existing SQL database.
DROP DATABASE databasename;
CREATE TABLE table_name (
column1 datatype,
column2 datatype,
column3 datatype,
...
PRIMARY KEY (one_or_more_columns)
);
Using this syntax to create EMPLOYEE table. Make sure you are inside a database.
CREATE TABLE EMPLOYEE (
Empno VARCHAR(6) PRIMARY KEY,
Ename VARCHAR(20) NOT NULL,
Job CHAR(10),
Hiredate DATE,
Basic_Sal DECIMAL(9,2),
Comm DECIMAL(7,2),
Dept_no VARCHAR(4)
);
Using DESCRIBE:
mysql> DESCRIBE EMPLOYEE;
+-----------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| Empno | varchar(6) | NO | PRI | NULL | |
| Ename | varchar(20) | NO | | NULL | |
| Job | char(10) | YES | | NULL | |
| Hiredate | date | YES | | NULL | |
| Basic_Sal | decimal(9,2) | YES | | NULL | |
| Comm | decimal(7,2) | YES | | NULL | |
| Dept_no | varchar(4) | YES | | NULL | |
+-----------+--------------+------+-----+---------+-------+
7 rows in set (0.06 sec)
Using SHOW COLUMNS:
mysql> SHOW COLUMNS FROM EMPLOYEE;
+-----------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| Empno | varchar(6) | NO | PRI | NULL | |
| Ename | varchar(20) | NO | | NULL | |
| Job | char(10) | YES | | NULL | |
| Hiredate | date | YES | | NULL | |
| Basic_Sal | decimal(9,2) | YES | | NULL | |
| Comm | decimal(7,2) | YES | | NULL | |
| Dept_no | varchar(4) | YES | | NULL | |
+-----------+--------------+------+-----+---------+-------+
7 rows in set (0.01 sec)
Adding Columns
ALTER TABLE employees
ADD COLUMN email VARCHAR(100);
Dropping Columns
ALTER TABLE employees
DROP COLUMN email;
Modifying Data Types
ALTER TABLE employees
MODIFY COLUMN salary DECIMAL(10, 2);
Renaming Columns
ALTER TABLE employees
CHANGE COLUMN old_column_name new_column_name VARCHAR(50);
Adding Constraints - Primary Key
Syntax:
ALTER TABLE table_name
ADD PRIMARY KEY (column_name);
Example:
-- Add a primary key to the "Students" table for the "student_id" column
ALTER TABLE Students
ADD PRIMARY KEY (student_id);
Adding Constrainsts - Foreign Key
Syntax for Adding a Foreign Key:
ALTER TABLE table_name
ADD CONSTRAINT foreign_key_name
FOREIGN KEY (column_name) REFERENCES referenced_table(referenced_column);
Example:
-- Add a foreign key constraint to the "CourseEnrollments" table linking it to the "Students" table
ALTER TABLE CourseEnrollments
ADD CONSTRAINT fk_student_id
FOREIGN KEY (student_id) REFERENCES Students(student_id);
Adding Constraints - NOT NULL
ALTER TABLE
statement with the MODIFY
clause.
ALTER TABLE EMPLOYEE
MODIFY Job char(10) NOT NULL;
Remove a Primary Key Constraint:
ALTER TABLE your_table_name
DROP PRIMARY KEY;
Remove a Foreign Key Constraint:
ALTER TABLE your_table_name
DROP FOREIGN KEY your_fk_constraint_name;
DROP TABLE employees;
Now we know how table is created, now we will enter data into it.
INSERT INTO employees (id, name, salary)
VALUES (1, 'John', 50000);
If you are adding values for all the columns of the table, you do not need to specify the column names in the SQL query. However, make sure the order of the values is in the same order as the columns in the table. Here, the INSERT INTO syntax would be as follows:
INSERT INTO table_name
VALUES (value1, value2, value3, ...);
INSERT INTO employees (id, name, salary)
VALUES
(2, 'Alice', 55000),
(3, 'Bob', 60000),
(4, 'Eva', 48000);
Example ↓
INSERT INTO EMPLOYEE (Empno, Ename, Job, Hiredate, Basic_Sal, Comm, Dept_no)
VALUES
('E0001', 'Kim', 'Manager', '2002-12-15', 5000, 500, 'D001'),
('E0002', 'Bruce', 'Analyst', '1999-04-24', 4000, 400, 'D002'),
('E0003', 'Amold', 'Clerk', '2001-01-10', 2500, 250, 'D004'),
('E0004', 'Holvfield', 'Tester', '2001-10-10', 3000, 300, 'D002'),
('E0005', 'Kelly', 'Admin', '1999-04-11', 2000, 200, 'D003');
INSERT INTO employees (name, salary)
VALUES ('Jane', 52000);
SELECT column1, column2, ...
FROM table_name;
SELECT * FROM table_name;
SELECT CustomerName, City
FROM Customers;
SELECT column1, column2, ...
FROM table_name WHERE condition;
!=
).Let's use Operators for the following table.
To retrieve employees with a salary of $50,000, you can use the following SQL query:
SELECT * FROM Employees WHERE Salary = 50000;
To retrieve employees with a salary greater than $55,000, you can use the following SQL query:
SELECT * FROM Employees WHERE Salary > 55000;
To retrieve employees with a salary less than $60,000, you can use the following SQL query:
SELECT * FROM Employees WHERE Salary < 60000;
To retrieve employees with a salary greater than or equal to $55,000, you can use the following SQL query:
SELECT * FROM Employees WHERE Salary >= 55000;
To retrieve employees with a salary less than or equal to $55,000, you can use the following SQL query:
SELECT * FROM Employees WHERE Salary <= 55000;
To retrieve employees with a salary not equal to $60,000, you can use either of the following SQL queries:
SELECT * FROM Employees WHERE Salary <> 60000;
SELECT * FROM Employees WHERE Salary != 60000;
SELECT column1, column2, ...
FROM table_name WHERE condition1 AND condition2 AND condition3 ...;
SELECT column1, column2, ...
FROM table_name WHERE condition1 OR condition2 OR condition3 ...;
SELECT column1, column2, ...
FROM table_name WHERE NOT condition;
SELECT * FROM Customers WHERE Country='Mexico';
SELECT * FROM Customers WHERE Country='India' AND City='Japan';
SELECT * FROM Customers WHERE Country='America' AND (City='India' OR City='Korea');
SELECT DISTINCT column1, column2 FROM table_name;
Suppose we have a table called "Orders" with the following data:
+---------+-------------+---------+
| OrderID | CustomerName| Product |
+---------+-------------+---------+
| 1 | John Doe | Widget |
| 2 | Jane Smith | Gadget |
| 3 | John Doe | Widget |
+---------+-------------+---------+
To retrieve a list of distinct products ordered, you can use the following SQL query:
SELECT DISTINCT Product FROM Orders;
The result will only include unique product names:
+---------+
| Product |
+---------+
| Widget |
| Gadget |
+---------+
Suppose we have a table called "Employees" with the following data:
+----+-----------+--------------+
| ID | Firstname | Lastname |
+----+-----------+--------------+
| 1 | John | Doe |
| 2 | Jane | Smith |
| 3 | James | Johnson |
| 4 | Alex | Anderson |
| 5 | Robert | Robinson |
+----+-----------+--------------+
To retrieve employees whose first names start with "J," you can use the following SQL query:
SELECT * FROM Employees WHERE Firstname LIKE 'J%';
This query will return employees with first names starting with "J":
+----+-----------+--------------+
| ID | Firstname | Lastname |
+----+-----------+--------------+
| 1 | John | Doe |
| 2 | Jane | Smith |
| 3 | James | Johnson |
+----+-----------+--------------+
To retrieve employees whose last names end with "son," you can use the following SQL query:
SELECT * FROM Employees WHERE Lastname LIKE '%son';
This query will return employees with last names ending with "son":
+----+-----------+--------------+
| ID | Firstname | Lastname |
+----+-----------+--------------+
| 1 | John | Doe |
| 3 | James | Johnson |
| 5 | Robert | Robinson |
+----+-----------+--------------+
You can also use the underscore (_) wildcard to match single characters:
SELECT * FROM Employees WHERE Firstname LIKE '_a%';
This query will return employees with first names that have "a" as the second character:
+----+-----------+--------------+
| ID | Firstname | Lastname |
+----+-----------+--------------+
| 4 | Alex | Anderson |
| 5 | Robert | Robinson |
+----+-----------+--------------+
These are just a few examples of how the LIKE operator can be used to search for patterns in data.
SELECT * FROM table_name WHERE column_name IN (value1, value2, value3, ...);
Suppose we have a table called "Products" with the following data:
+----+----------+-------------+
| ID | Product | Category_ID |
+----+----------+-------------+
| 1 | Laptop | 1 |
| 2 | Phone | 2 |
| 3 | Tablet | 1 |
| 4 | Monitor | 3 |
| 5 | Printer | 4 |
+----+----------+-------------+
To retrieve products from categories 1 and 3, you can use the following SQL query:
SELECT * FROM Products WHERE Category_ID IN (1, 3);
The result will include products from categories 1 and 3:
+----+---------+-------------+
| ID | Product | Category_ID |
+----+---------+-------------+
| 1 | Laptop | 1 |
| 3 | Tablet | 1 |
| 4 | Monitor | 3 |
+----+---------+-------------+
This allows you to filter results based on a list of specific values in a column.
SELECT * FROM table_name WHERE column_name BETWEEN value1 AND value2;
Suppose we have a table called "Orders" with the following data:
+----+------------+------------+
| ID | Order_Date | Order_Amt |
+----+------------+------------+
| 1 | 2023-02-15 | 500.00 |
| 2 | 2023-04-10 | 750.00 |
| 3 | 2023-05-20 | 300.00 |
| 4 | 2023-03-05 | 900.00 |
| 5 | 2023-06-25 | 600.00 |
+----+------------+------------+
To retrieve orders placed between January 1, 2023, and June 30, 2023, you can use the following SQL query:
SELECT * FROM Orders WHERE Order_Date BETWEEN '2023-01-01' AND '2023-06-30';
The result will include orders placed within the specified date range:
+----+------------+------------+
| ID | Order_Date | Order_Amt |
+----+------------+------------+
| 1 | 2023-02-15 | 500.00 |
| 2 | 2023-04-10 | 750.00 |
| 3 | 2023-05-20 | 300.00 |
| 5 | 2023-06-25 | 600.00 |
+----+------------+------------+
This allows you to filter results within a specified range of values in a column.
SELECT * FROM table_name WHERE column_name IS NULL;
Suppose we have a table called "Customers" with the following data:
+----+-----------+--------------------+
| ID | FirstName | Email |
+----+-----------+--------------------+
| 1 | John | john@example.com |
| 2 | Jane | NULL |
| 3 | James | james@example.com |
| 4 | Alex | NULL |
| 5 | Robert | robert@example.com |
+----+-----------+--------------------+
To retrieve customers with NULL email addresses, you can use the following SQL query:
SELECT * FROM Customers WHERE Email IS NULL;
The result will include customers with NULL email addresses:
+----+-----------+--------------+
| ID | FirstName | Email |
+----+-----------+--------------+
| 2 | Jane | NULL |
| 4 | Alex | NULL |
+----+-----------+--------------+
This allows you to filter rows based on whether a specific column contains NULL values.
SELECT column_name AS new_name FROM table_name;
Suppose we have a table called "Employees" with the following data:
+----+-----------+------------+
| ID | FirstName | LastName |
+----+-----------+------------+
| 1 | John | Doe |
| 2 | Jane | Smith |
| 3 | James | Johnson |
| 4 | Alex | Anderson |
| 5 | Robert | Robinson |
+----+-----------+------------+
To retrieve the first name and last name of employees with renamed columns "First Name" and "Last Name," you can use the following SQL query:
SELECT FirstName AS "First Name", LastName AS "Last Name" FROM Employees;
The result will include the specified columns with the new names:
+-----------+------------+
| First Name| Last Name |
+-----------+------------+
| John | Doe |
| Jane | Smith |
| James | Johnson |
| Alex | Anderson |
| Robert | Robinson |
+-----------+------------+
This allows you to make query results more readable and descriptive by assigning custom names to columns or expressions.
SELECT column1, column2 FROM table_name ORDER BY column1 [ASC|DESC];
SELECT product_name, price FROM products ORDER BY price DESC;
SELECT first_name, last_name FROM employees ORDER BY last_name, first_name;
SELECT product_name, price, price * 1.1 AS discounted_price FROM products ORDER BY discounted_price;
SELECT column_name FROM table_name ORDER BY column_name NULLS LAST;
SELECT product_name, price FROM products ORDER BY 2 DESC, 1 ASC;
SELECT column1, aggregate_function(column2) FROM table_name GROUP BY column1;
SELECT department, AVG(salary) FROM employees GROUP BY department;
SELECT department, gender, AVG(salary) FROM employees GROUP BY department, gender;
SELECT department, AVG(salary) FROM employees GROUP BY department HAVING AVG(salary) > 50000;
SELECT department, COUNT(*) FROM employees GROUP BY department ORDER BY COUNT(*) DESC;
+---------+----------+----------+-------------+
| OrderID | Product | Quantity | TotalAmount |
+---------+----------+----------+-------------+
| 1 | Widget A | 10 | 2500.00 |
| 2 | Widget B | 5 | 1000.00 |
| 3 | Widget A | 8 | 2000.00 |
| 4 | Widget C | 12 | 3600.00 |
| 5 | Widget B | 7 | 1400.00 |
+---------+----------+----------+-------------+
SELECT COUNT(*) FROM Sales;
Result: 5 (Count of rows in the "Sales" table)
SELECT SUM(TotalAmount) FROM Sales;
Result: 10500.00 (Sum of the "TotalAmount" column)
SELECT AVG(Quantity) FROM Sales;
Result: 8.4 (Average quantity of products sold)
SELECT MAX(TotalAmount) FROM Sales;
Result: 3600.00 (Maximum total amount)
SELECT MIN(Quantity) FROM Sales;
Result: 5 (Minimum quantity of products sold)
The UPDATE statement is used to modify existing records in a table.
Syntax ↓
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;
Example:
UPDATE Customers
SET City = 'New York'
WHERE CustomerID = 1;
The DELETE statement is used to remove records from a table.
Syntax ↓
DELETE FROM table_name WHERE condition;
Example:
DELETE FROM Orders WHERE OrderID = 100;
To delete all rows from a table, use the DELETE statement without a WHERE clause.
Syntax ↓
DELETE FROM table_name;
Example:
DELETE FROM Customers;
To delete single or multiple rows based on specific conditions, use the DELETE statement with a WHERE clause.
Syntax ↓
DELETE FROM table_name WHERE condition;
Example:
DELETE FROM Employees WHERE Salary < 30000;
The TRUNCATE statement is used to remove all records from a table quickly.
Syntax ↓
TRUNCATE TABLE table_name;
Example:
TRUNCATE TABLE Customers;
While both DELETE and TRUNCATE can be used to remove data from a table, there are key differences:
Default Autocommit:
-- Default autocommit mode (each statement is a transaction)
SET autocommit = ON;
Disable Autocommit:
-- Disable autocommit (requires explicit commit or rollback)
SET autocommit = OFF;
-- Disable autocommit
SET autocommit = OFF;
-- Start a transaction
START TRANSACTION;
-- SQL statements within the transaction
UPDATE accounts SET balance = balance - 100 WHERE account_id = 'A';
UPDATE accounts SET balance = balance + 100 WHERE account_id = 'B';
-- Commit the transaction
COMMIT;
-- Disable autocommit
SET autocommit = OFF;
-- Start a transaction
START TRANSACTION;
-- SQL statements within the transaction
UPDATE accounts SET balance = balance - 100 WHERE account_id = 'A';
UPDATE accounts SET balance = balance + 100 WHERE account_id = 'B';
-- Rollback the transaction (simulate an error or cancellation)
ROLLBACK;
Example:
+-----------------------------------------+
| Id | Name | Age | City |
+-----------------------------------------+
| 1 | Ram Kumar | 19 | Agra |
| 2 | Salman Khan | 18 | Bhopal |
| 3 | Meera Khan | 19 | Agra |
| 4 | Sarita Kumari | 21 | Delhi |
+-----------------------------------------+
CREATE TABLE table_name (
id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(50) NOT NULL,
age INT NOT NULL,
city VARCHAR(10) NOT NULL,
PRIMARY KEY(id)
);
AUTO_INCREMENT
attribute for the 'id' column ensures that the database
system automatically assigns a unique value to 'id' for each new record. This eliminates the
need for manually specifying the 'id' value during insertion.When a table has already been created without a specified primary key, you can alter it to add a primary key constraint afterward.
ALTER TABLE table_name
ADD PRIMARY KEY(id);
This ALTER TABLE statement adds a primary key constraint to the 'id' column of the existing table.
Example:
Student Table
+-----------------------------------------+
| Id | Name | Age | City |
+-----------------------------------------+
| 1 | Ram Kumar | 19 | 1 |
| 2 | Salman Khan | 18 | 2 |
| 3 | Meera Khan | 19 | 1 |
| 4 | Sarita Kumari | 21 | 3 |
+-----------------------------------------+
City Table
+--------------+
| Cid | City |
+--------------+
| 1 | Agra |
| 2 | Bhopal |
| 3 | Delhi |
+--------------+
CREATE TABLE student(
id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(50) NOT NULL,
age INT NOT NULL,
city VARCHAR(10) NOT NULL,
PRIMARY KEY(id),
FOREIGN KEY(city) REFERENCES City(cid);
)
ALTER TABLE table_name
ADD FOREIGN KEY(city) REFERENCES City(cid);
Creating City Table:
mysql> CREATE TABLE City(cid INT NOT NULL AUTO_INCREMENT, cityname VARCHAR(50) NOT NULL, PRIMARY KEY (cid));
Query OK, 0 rows affected (0.12 sec)
mysql> INSERT INTO City(cityname) VALUES('Agra'), ('Dehli'), ('Bhopal'), ('Jaipur'), ('Noida');
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> select * from City;
+-----+----------+
| cid | cityname |
+-----+----------+
| 1 | Agra |
| 2 | Dehli |
| 3 | Bhopal |
| 4 | Jaipur |
| 5 | Noida |
+-----+----------+
5 rows in set (0.00 sec)
Creating Personal Table
mysql> CREATE TABLE Personal(id INT NOT NULL, name VARCHAR(50) NOT NULL, percentage INT NOT NULL, age INT NOT NULL, gender VARCHAR(1) NOT NULL, city INT NOT NULL, PRIMARY KEY (id), FOREIGN KEY (city) REFERENCES City (cid));
Query OK, 0 rows affected (0.15 sec)
mysql> INSERT INTO Personal VALUES (1, "Ram Kumar", 45, 19, "M", 1);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO Personal VALUES (2, "Sarita Kumari", 55, 22, "F", 2), (3, "Salman Khan", 62, 20, "M", 1), (4, "Juhi Chawla", 47, 19, "F", 3), (5, "Anil Kapoor", 74, 22, "M", 1), (6, "John Abrahm", 64, 21, "M", 2), (7, "Shahid Kapoor", 52, 20, "M", 1);
Query OK, 6 rows affected (0.00 sec)
Records: 6 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM Personal;
+----+---------------+------------+-----+--------+------+
| id | name | percentage | age | gender | city |
+----+---------------+------------+-----+--------+------+
| 1 | Ram Kumar | 45 | 19 | M | 1 |
| 2 | Sarita Kumari | 55 | 22 | F | 2 |
| 3 | Salman Khan | 62 | 20 | M | 1 |
| 4 | Juhi Chawla | 47 | 19 | F | 3 |
| 5 | Anil Kapoor | 74 | 22 | M | 1 |
| 6 | John Abrahm | 64 | 21 | M | 2 |
| 7 | Shahid Kapoor | 52 | 20 | M | 1 |
+----+---------------+------------+-----+--------+------+
7 rows in set (0.00 sec)
SELECT columns
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;
The syntax above demonstrates the structure of an INNER JOIN query. It specifies the columns to be selected and the tables involved. The condition for the join is defined using the ON keyword, where the common columns (usually a foreign key in one table and its corresponding primary key in the other) are specified.
mysql> SELECT * FROM Personal INNER JOIN City ON Personal.city = City.cid;
+----+---------------+------------+-----+--------+------+-----+----------+
| id | name | percentage | age | gender | city | cid | cityname |
+----+---------------+------------+-----+--------+------+-----+----------+
| 1 | Ram Kumar | 45 | 19 | M | 1 | 1 | Agra |
| 3 | Salman Khan | 62 | 20 | M | 1 | 1 | Agra |
| 5 | Anil Kapoor | 74 | 22 | M | 1 | 1 | Agra |
| 7 | Shahid Kapoor | 52 | 20 | M | 1 | 1 | Agra |
| 2 | Sarita Kumari | 55 | 22 | F | 2 | 2 | Dehli |
| 6 | John Abrahm | 64 | 21 | M | 2 | 2 | Dehli |
| 4 | Juhi Chawla | 47 | 19 | F | 3 | 3 | Bhopal |
+----+---------------+------------+-----+--------+------+-----+----------+
7 rows in set (0.00 sec)
We can also give alias name like this ↓
SELECT * FROM Personal p INNER JOIN City c ON p.city = c.cid;
Now to remove some column on inner join we can do the following:
mysql> SELECT Personal.id, Personal.name, City.cityname FROM Personal INNER JOIN City ON Personal.id = City.cid;
+----+---------------+----------+
| id | name | cityname |
+----+---------------+----------+
| 1 | Ram Kumar | Agra |
| 2 | Sarita Kumari | Dehli |
| 3 | Salman Khan | Bhopal |
| 4 | Juhi Chawla | Jaipur |
| 5 | Anil Kapoor | Noida |
+----+---------------+----------+
5 rows in set (0.00 sec)
We can also use WHERE Clause
mysql> SELECT p.id, p.name, p.percentage, p.age, c.cityname FROM Personal p INNER JOIN City c ON
p.city = c.cid WHERE c.cityname = 'Agra';
+----+---------------+------------+-----+----------+
| id | name | percentage | age | cityname |
+----+---------------+------------+-----+----------+
| 1 | Ram Kumar | 45 | 19 | Agra |
| 3 | Salman Khan | 62 | 20 | Agra |
| 5 | Anil Kapoor | 74 | 22 | Agra |
| 7 | Shahid Kapoor | 52 | 20 | Agra |
+----+---------------+------------+-----+----------+
4 rows in set (0.00 sec)
Example:
Student Table (A)
+----------------------------------------------+
| Id | Name | Age | City (F.K) |
+----------------------------------------------+
| 1 | Ram Kumar | 19 | 1 |
| 2 | Salman Khan | 18 | 2 |
| 3 | Meera Khan | 19 | |
| 4 | Sarita Kumari | 21 | 3 |
+----------------------------------------------+
City Table (B)
+--------------------+
| Cid (P.K) | City |
+--------------------+
| 1 | Agra |
| 2 | Bhopal |
| 3 | Delhi |
| 4 | Noida |
+--------------------+
Left Join Result
+--------------------------------------------------------------+
| Id | Name | Age | City (F.K) | Cid | City |
+--------------------------------------------------------------+
| 1 | Ram Kumar | 19 | 1 | 1 | Agra |
| 2 | Salman Khan | 18 | 2 | 2 | Bhopal |
| 3 | Meera Khan | 19 | | | |
| 4 | Sarita Kumari | 21 | 3 | 3 | Delhi |
+--------------------------------------------------------------+
SELECT columns
FROM table1
LEFT JOIN table2
ON table.column_name = table2.column_name;
The syntax above illustrates the structure of a LEFT JOIN query, where data from the left table is selected along with any matching data from the right table based on the specified column relationship.
mysql> SELECT * FROM Personal LEFT JOIN City ON Personal.city = City.cid;
+----+---------------+------------+-----+--------+------+------+----------+
| id | name | percentage | age | gender | city | cid | cityname |
+----+---------------+------------+-----+--------+------+------+----------+
| 1 | Ram Kumar | 45 | 19 | M | 1 | 1 | Agra |
| 2 | Sarita Kumari | 55 | 22 | F | 2 | 2 | Dehli |
| 3 | Salman Khan | 62 | 20 | M | 1 | 1 | Agra |
| 4 | Juhi Chawla | 47 | 19 | F | 3 | 3 | Bhopal |
| 5 | Anil Kapoor | 74 | 22 | M | 1 | 1 | Agra |
| 6 | John Abrahm | 64 | 21 | M | 2 | 2 | Dehli |
| 7 | Shahid Kapoor | 52 | 20 | M | 1 | 1 | Agra |
+----+---------------+------------+-----+--------+------+------+----------+
SELECT columns
FROM table1
RIGHT JOIN table2
ON table1.column_name = table3.column_name;
| |
(F.K) (P.K)
mysql> SELECT * FROM Personal RIGHT JOIN City ON Personal.city = City.cid;
+------+---------------+------------+------+--------+------+-----+----------+
| id | name | percentage | age | gender | city | cid | cityname |
+------+---------------+------------+------+--------+------+-----+----------+
| 1 | Ram Kumar | 45 | 19 | M | 1 | 1 | Agra |
| 3 | Salman Khan | 62 | 20 | M | 1 | 1 | Agra |
| 5 | Anil Kapoor | 74 | 22 | M | 1 | 1 | Agra |
| 7 | Shahid Kapoor | 52 | 20 | M | 1 | 1 | Agra |
| 2 | Sarita Kumari | 55 | 22 | F | 2 | 2 | Dehli |
| 6 | John Abrahm | 64 | 21 | M | 2 | 2 | Dehli |
| 4 | Juhi Chawla | 47 | 19 | F | 3 | 3 | Bhopal |
| NULL | NULL | NULL | NULL | NULL | NULL | 4 | Jaipur |
| NULL | NULL | NULL | NULL | NULL | NULL | 5 | Noida |
+------+---------------+------------+------+--------+------+-----+----------+
9 rows in set (0.00 sec)
Example:
Table A
+---------+
| Column |
+---------+
| A1 |
| A2 |
+---------+
Table B
+---------+
| Column |
+---------+
| B1 |
| B2 |
+---------+
Cross Join Result
+---------------------+
| ColumnA | ColumnB |
+---------------------+
| A1 | B1 |
| A1 | B2 |
| A2 | B1 |
| A2 | B2 |
+---------------------+
SELECT *
FROM table1
CROSS JOIN table2;
The CROSS JOIN syntax involves selecting all columns from both tables without specifying any condition for the join.
mysql> SELECT * FROM Personal CROSS JOIN City;
+----+---------------+------------+-----+--------+------+-----+----------+
| id | name | percentage | age | gender | city | cid | cityname |
+----+---------------+------------+-----+--------+------+-----+----------+
| 1 | Ram Kumar | 45 | 19 | M | 1 | 5 | Noida |
| 1 | Ram Kumar | 45 | 19 | M | 1 | 4 | Jaipur |
| 1 | Ram Kumar | 45 | 19 | M | 1 | 3 | Bhopal |
| 1 | Ram Kumar | 45 | 19 | M | 1 | 2 | Dehli |
| 1 | Ram Kumar | 45 | 19 | M | 1 | 1 | Agra |
| 2 | Sarita Kumari | 55 | 22 | F | 2 | 5 | Noida |
| 2 | Sarita Kumari | 55 | 22 | F | 2 | 4 | Jaipur |
| 2 | Sarita Kumari | 55 | 22 | F | 2 | 3 | Bhopal |
| 2 | Sarita Kumari | 55 | 22 | F | 2 | 2 | Dehli |
| 2 | Sarita Kumari | 55 | 22 | F | 2 | 1 | Agra |
| 3 | Salman Khan | 62 | 20 | M | 1 | 5 | Noida |
| 3 | Salman Khan | 62 | 20 | M | 1 | 4 | Jaipur |
| 3 | Salman Khan | 62 | 20 | M | 1 | 3 | Bhopal |
| 3 | Salman Khan | 62 | 20 | M | 1 | 2 | Dehli |
| 3 | Salman Khan | 62 | 20 | M | 1 | 1 | Agra |
| 4 | Juhi Chawla | 47 | 19 | F | 3 | 5 | Noida |
| 4 | Juhi Chawla | 47 | 19 | F | 3 | 4 | Jaipur |
| 4 | Juhi Chawla | 47 | 19 | F | 3 | 3 | Bhopal |
| 4 | Juhi Chawla | 47 | 19 | F | 3 | 2 | Dehli |
| 4 | Juhi Chawla | 47 | 19 | F | 3 | 1 | Agra |
| 5 | Anil Kapoor | 74 | 22 | M | 1 | 5 | Noida |
| 5 | Anil Kapoor | 74 | 22 | M | 1 | 4 | Jaipur |
| 5 | Anil Kapoor | 74 | 22 | M | 1 | 3 | Bhopal |
| 5 | Anil Kapoor | 74 | 22 | M | 1 | 2 | Dehli |
| 5 | Anil Kapoor | 74 | 22 | M | 1 | 1 | Agra |
| 6 | John Abrahm | 64 | 21 | M | 2 | 5 | Noida |
| 6 | John Abrahm | 64 | 21 | M | 2 | 4 | Jaipur |
| 6 | John Abrahm | 64 | 21 | M | 2 | 3 | Bhopal |
| 6 | John Abrahm | 64 | 21 | M | 2 | 2 | Dehli |
| 6 | John Abrahm | 64 | 21 | M | 2 | 1 | Agra |
| 7 | Shahid Kapoor | 52 | 20 | M | 1 | 5 | Noida |
| 7 | Shahid Kapoor | 52 | 20 | M | 1 | 4 | Jaipur |
| 7 | Shahid Kapoor | 52 | 20 | M | 1 | 3 | Bhopal |
| 7 | Shahid Kapoor | 52 | 20 | M | 1 | 2 | Dehli |
| 7 | Shahid Kapoor | 52 | 20 | M | 1 | 1 | Agra |
+----+---------------+------------+-----+--------+------+-----+----------+
35 rows in set (0.01 sec)
Example:
Student Table
+----------------------------------------------+
| Id | Name | Age | City (F.K) |
+----------------------------------------------+
| 1 | Ram Kumar | 19 | 1 |
| 2 | Salman Khan | 18 | 2 |
| 3 | Meera Khan | 19 | |
| 4 | Sarita Kumari | 21 | 3 |
+----------------------------------------------+
City Table
+--------------------+
| Cid (P.K) | City |
+--------------------+
| 1 | Agra |
| 2 | Bhopal |
| 3 | Delhi |
| 4 | Noida |
+--------------------+
Result we want:
+-----------------------------+
| City | Total Students |
+-----------------------------+
| Agra | 2 |
| Bhopal | 1 |
| Delhi | 1 |
+-----------------------------+
SELECT columns
FROM table_name
WHERE condition
GROUP BY column_name(s);
If we are getting data from two tables and we want to use GROUP BY.
SELECT columns
FROM table1 INNER JOIN table2
ON table1.column_name = table2.column_name
WHERE condition
GROUP BY column_name(s);
mysql> SELECT city, COUNT(city) FROM Personal GROUP BY city;
+------+-------------+
| city | COUNT(city) |
+------+-------------+
| 1 | 4 |
| 2 | 2 |
| 3 | 1 |
+------+-------------+
3 rows in set (0.01 sec)
mysql> SELECT City.cityname, COUNT(Personal.city) AS Total_student FROM Personal;
+----------+---------------+
| cityname | Total_student |
+----------+---------------+
| Agra | 4 |
| Dehli | 2 |
| Bhopal | 1 |
+----------+---------------+
3 rows in set (0.00 sec)
We put WHERE clause before using GROUP BY
mysql> SELECT City.cityname, COUNT(Personal.city) AS Total_student FROM Personal
INNER JOIN City ON Personal.city = City.cid WHERE Personal.age >= 20 GROUP BY Personal.city;
+----------+---------------+
| cityname | Total_student |
+----------+---------------+
| Dehli | 2 |
| Agra | 3 |
+----------+---------------+
2 rows in set (0.00 sec)
Now if we want those cities from where there are more than 5 students. so this condition will work on total students it is done by HAVING Clause.
SELECT columns
FROM table_name
GROUP BY column_name(s)
HAVING condition;
mysql> SELECT City.cityname, COUNT(Personal.city) AS Total_student FROM Personal INNER JOIN City ON Personal.city = City.cid GROUP BY Personal.city HAVING COUNT(Personal.city) > 3;
+----------+---------------+
| cityname | Total_student |
+----------+---------------+
| Agra | 4 |
+----------+---------------+
1 row in set (0.00 sec)
Reference