Being Code Expert: Mysql

Showing posts with label Mysql. Show all posts

SQL COUNT

Write SQL COUNT queries.

The COUNT() the function returns the number of rows that match a specified criterion.

Example


Table: Employee                           
+----+-------+--------+--------------+    
| Id | Name  | Salary | DepartmentId |    
+----+-------+--------+--------------+    
| 1  | Ram   | 85000  | 1            |    
| 2  | Henry | 80000  | 2            |    
| 3  | Sam   | 60000  | 2            |    
| 4  | Shyam | 60000  | 1            |
| 5  | Geeta | 90000  | 1            |
| 6  | Sheet | 90000  | 1            |
| 7  | Leela | 80000  | 1            |
| 8  | Geeta | 70000  | 1            |
+----+-------+--------+--------------+

Approach

Mysql: Count all employee of department=1


SELECT COUNT(*) AS TOTALEMPLOYEE 
FROM EMPLOYEE WHERE DepartmentId=1;

Result: TOTAL EMPLOYEE=6

Mysql: Department wise employee count


SELECT DepartmentId,COUNT(*) AS TOTALEMPLOYEE 
FROM EMPLOYEE 
GROUP BY DepartmentId;

Result:


+--------------+---------------+
| DepartmentId | TOTALEMPLOYEE |
+--------------+---------------+
| 1            |   6           | 
| 2            |   2           | 
+--------------+---------------+

SQL AND QUERY

Write SQL select queries with AND operator.

SQL AND Operator The WHERE clause can be combined with AND operators

Example


Table: Employee                             
+----+-------+--------+--------------+      
| Id | Name  | Salary | DepartmentId |      
+----+-------+--------+--------------+      
| 1  | Ram   | 85000  | 1            |      
| 2  | Henry | 80000  | 2            |      
| 3  | Sam   | 60000  | 2            |      
| 4  | Shyam | 60000  | 1            |
| 5  | Geeta | 90000  | 1            |
| 6  | Sheet | 90000  | 1            |
| 7  | Leela | 80000  | 1            |
| 8  | Geeta | 70000  | 1            |
+----+-------+--------+--------------+

Approach

Mysql: Select Employee using AND operator.



SELECT * FROM EMPLOYEE WHERE DepartmentId=1 AND Salary=90000;

Result:

                        
+----+-------+--------+--------------+      
| Id | Name  | Salary | DepartmentId |      
+----+-------+--------+--------------+      
| 5  | Geeta | 90000  | 1            |
| 6  | Sheet | 90000  | 1            |
+----+-------+--------+--------------+

SQL OR QUERY

Write SQL select queries with OR operator.

SQL OR Operator The WHERE clause can be combined with OR operators

Example


Table: Employee                             
+----+-------+--------+--------------+      
| Id | Name  | Salary | DepartmentId |      
+----+-------+--------+--------------+      
| 1  | Ram   | 85000  | 1            |      
| 2  | Henry | 80000  | 2            |      
| 3  | Sam   | 60000  | 2            |      
| 4  | Shyam | 60000  | 1            |
| 5  | Geeta | 90000  | 1            |
| 6  | Sheet | 90000  | 1            |
| 7  | Leela | 80000  | 1            |
| 8  | Geeta | 70000  | 1            |
+----+-------+--------+--------------+

Approach

Mysql: Select Employee using OR operator


SELECT * FROM EMPLOYEE WHERE DepartmentId=1 or DepartmentId=2;

Result:

                        
+----+-------+--------+--------------+      
| Id | Name  | Salary | DepartmentId |      
+----+-------+--------+--------------+      
| 1  | Ram   | 85000  | 1            |      
| 2  | Henry | 80000  | 2            |      
| 3  | Sam   | 60000  | 2            |      
| 4  | Shyam | 60000  | 1            |
| 5  | Geeta | 90000  | 1            |
| 6  | Sheet | 90000  | 1            |
| 7  | Leela | 80000  | 1            |
| 8  | Geeta | 70000  | 1            |
+----+-------+--------+--------------+

Mysql: Select Employee using OR operator on a different column



SELECT * FROM EMPLOYEE WHERE DepartmentId=2 or Salary=90000;

Result:

                        
+----+-------+--------+--------------+      
| Id | Name  | Salary | DepartmentId |      
+----+-------+--------+--------------+      
| 2  | Henry | 80000  | 2            |      
| 3  | Sam   | 60000  | 2            |      
| 5  | Geeta | 90000  | 1            |
| 6  | Sheet | 90000  | 1            |
+----+-------+--------+--------------+

SQL TOP

Write SQL select TOP queries.

The SELECT TOP the command is used to specify the number of records to return

Example


Table: Employee                            
+----+-------+--------+--------------+     
| Id | Name  | Salary | DepartmentId |     
+----+-------+--------+--------------+   
| 1  | Ram   | 85000  | 1            |   
| 2  | Henry | 80000  | 2            |   
| 3  | Sam   | 60000  | 2            |   
| 4  | Shyam | 60000  | 1            |
| 5  | Geeta | 90000  | 1            |
| 6  | Sheet | 90000  | 1            |
| 7  | Leela | 80000  | 1            |
| 8  | Geeta | 70000  | 1            |
+----+-------+--------+--------------+

Approach

SQL SERVER: SELECT TWO 2 records


SELECT TOP 2 * FROM Employee;

Result:

+----+-------+--------+--------------+     
| Id | Name  | Salary | DepartmentId |     
+----+-------+--------+--------------+   
| 1  | Ram   | 85000  | 1            |   
| 2  | Henry | 80000  | 2            |
+----+-------+--------+--------------+

SQL SERVER: SELECT 2 records with WHERE condition


SELECT TOP 2 * FROM Employee WHERE DepartmentID=1;

Result:

+----+-------+--------+--------------+     
| Id | Name  | Salary | DepartmentId |     
+----+-------+--------+--------------+   
| 1  | Ram   | 85000  | 1            |   
| 4  | Shyam | 60000  | 1            |
+----+-------+--------+--------------+

SQL TRUNCATE

Write SQL TRUNCATE Query.

In SQL, the TRUNCATE TABLE statement is a Data Definition Language operation that marks the extent of a table for deallocation. The result of this operation quickly removes all data from a table,

Example


Table: Employee                             
+----+-------+--------+--------------+      
| Id | Name  | Salary | DepartmentId |      
+----+-------+--------+--------------+      
| 1  | Ram   | 85000  | 1            |      
| 2  | Henry | 80000  | 2            |      
| 3  | Sam   | 60000  | 2            |      
| 4  | Shyam | 60000  | 1            |
| 5  | Geeta | 90000  | 1            |
| 6  | Sheet | 90000  | 1            |
| 7  | Leela | 80000  | 1            |
| 8  | Geeta | 70000  | 1            |
+----+-------+--------+--------------+

Approach :

Mysql: Truncate table without Foreign Key Constraints


TRUNCATE TABLE EMPLOYEE

Mysql: Truncate table with Foreign Key Constraints: You cannot TRUNCATE a table that has FK constraints applied oN it

Approach 1:

Remove constraints
Perform TRUNCATE

Approach 2: Disabled the FOREIGN_KEY_CHECKS

SET FOREIGN_KEY_CHECKS = 0; 
TRUNCATE TABLE EMPLOYEE; 
SET FOREIGN_KEY_CHECKS = 1;

SQL UPDATE

Write SQL UPDATE queries.

The UPDATE statement is used to modify the existing records in a table.

Example


Table: Employee                             
+----+-------+--------+--------------+      
| Id | Name  | Salary | DepartmentId |      
+----+-------+--------+--------------+      
| 1  | Ram   | 85000  | 1            |      
| 2  | Henry | 80000  | 2            |      
| 3  | Sam   | 60000  | 2            |      
| 4  | Shyam | 60000  | 1            |
| 5  | Geeta | 90000  | 1            |
| 6  | Sheet | 90000  | 1            |
| 7  | Leela | 80000  | 1            |
| 8  | Geeta | 70000  | 1            |
+----+-------+--------+--------------+

Approach

Mysql/ Oracle: Update the first Employee (ID=1) name and salary.


UPDATE Employee  SET NAME ='RAM SINGH', Salary='90000' WHERE ID=1;

Mysql/ Oracle: Update the multiple records (rows) (Update salary of every employee of department 1 )

UPDATE Employee  SET  Salary='90000' WHERE DepartmentId=1;

SQL Select with LIMIT

Write SQL select LIMIT queries.

The SELECT LIMIT clause is used to specify the number of records to return

Example


Table: Employee                            
+----+-------+--------+--------------+     
| Id | Name  | Salary | DepartmentId |     
+----+-------+--------+--------------+   
| 1  | Ram   | 85000  | 1            |   
| 2  | Henry | 80000  | 2            |   
| 3  | Sam   | 60000  | 2            |   
| 4  | Shyam | 60000  | 1            |
| 5  | Geeta | 90000  | 1            |
| 6  | Sheet | 90000  | 1            |
| 7  | Leela | 80000  | 1            |
| 8  | Geeta | 70000  | 1            |
+----+-------+--------+--------------+

Approach

Mysql: SELECT TWO 2 records


SELECT * FROM Employee LIMIT 2;

Result:

+----+-------+--------+--------------+     
| Id | Name  | Salary | DepartmentId |     
+----+-------+--------+--------------+   
| 1  | Ram   | 85000  | 1            |   
| 2  | Henry | 80000  | 2            |
+----+-------+--------+--------------+

Mysql: SELECT 2 records with WHERE condition


SELECT * FROM Employee WHERE DepartmentID=1 LIMIT 2;

Result:

+----+-------+--------+--------------+     
| Id | Name  | Salary | DepartmentId |     
+----+-------+--------+--------------+   
| 1  | Ram   | 85000  | 1            |   
| 4  | Shyam | 60000  | 1            |
+----+-------+--------+--------------+

SQL DISTINCT Query

The SQL SELECT DISTINCT Statement

The SELECT DISTINCT statement is used to return only distinct (different) values

Example


Table: Orders                             
+----+-----------+--------+------------+      
| Id | Customer  | Item   | OrderDate  |      
+----+-----------+--------+------------+      
| 1  | Raj Kumar | Press  | 2021-03-10 |      
| 2  | Ram       | T-Shirt| 2021-03-10 |      
| 3  | Raj Kumar | Press  | 2021-03-10 |      
| 4  | Ram       | Press  | 2021-03-10 |      
| 5  | Raj Kumar | T-Shirt| 2021-03-10 |      
+----+-----------+--------+------------+

Approach

Mysql : select distinct based on multiple fields


SELECT distinct customer,items,order_date FROM orders;

Result


+-----------+--------+------------+      
| Customer  | Item   | OrderDate  |      
+-----------+--------+------------+      
| Raj Kumar | Press  | 2021-03-10 |      
| Ram       | T-Shirt| 2021-03-10 |      
| Ram       | Press  | 2021-03-10 |      
| Raj Kumar | T-Shirt| 2021-03-10 |      
+-----------+--------+------------+

Mysql : select distinct based on Customer field


SELECT distinct customer FROM orders;

Result


+-----------+
| Customer  |
+-----------+
| Raj Kumar |
| Ram       |
+-----------+

SQL UNIQUE/ DISTINCT

Write SQL to fetch UNIQUE records.

The SELECT DISTINCT statement is used to return only DISTINCT / UNIQUE values

Example

Table: Employee                          
+----+-------+--------+--------------+    
| Id | Name  | Salary | DepartmentId |    
+----+-------+--------+--------------+    
| 1  | Ram   | 85000  | 1            |    
| 2  | Henry | 80000  | 2            |    
| 3  | Sam   | 60000  | 2            |    
| 4  | Shyam | 60000  | 1            |
| 5  | Geeta | 90000  | 1            |
| 6  | Sheet | 90000  | 1            |
| 7  | Leela | 80000  | 1            |
| 8  | Geeta | 70000  | 1            |
+----+-------+--------+--------------+

Approach: Select unique employee

Mysql:


SELECT DISTINCT NAME FROM EMPLOYEE

Oracle:


SELECT DISTINCT NAME FROM EMPLOYEE

Result:

+--------+
| NAME   |
+--------+
| Ram    |
| Henry  |
| Sam    |
| Shyam  |
| Geeta  |
| Sheeta |
| Leela  |
+--------+

SQL INTERSECT

Write SQL to INTERSECTof two or more statements.

The SQL INTERSECT operator is used to combine two SELECT statements but returns rows only from the first SELECT statement that are identical to a row in the second SELECT statement

Example


Table: Employee                            
+----+-------+--------+--------------+     
| Id | Name  | Salary | DepartmentId |     
+----+-------+--------+--------------+     
| 1  | Ram   | 85000  | 1            |     
| 2  | Henry | 80000  | 2            |     
| 3  | Sam   | 60000  | 2            |    
| 4  | Shyam | 60000  | 1            |
| 5  | Geeta | 90000  | 1            |
| 6  | Sheet | 90000  | 1            |
| 7  | Leela | 80000  | 1            |
| 8  | Geeta | 70000  | 1            |
+----+-------+--------+--------------+

Approach

Oracle


SELECT * FROM EMPLOYEE WHERE ID IN(1,2)
INTERSECT 
SELECT * FROM EMPLOYEE WHERE ID IN (1)

Result

Table: Employee                            
+----+-------+--------+--------------+     
| Id | Name  | Salary | DepartmentId |     
+----+-------+--------+--------------+     
| 1  | Ram   | 85000  | 1            |     
+----+-------+--------+--------------+

Home