Navigate to C#

SQL: Structured Query Language

• A specialized and standard language used to manage and manipulate the data within the relational databases.
• To execute SQL queries we use a relational database management system (RDBMS)
• Allows developers and database administrators to manage large amounts of data effectively.
• SQL is used to perform C.R.U.D operations: Create, Retrieve (Read/Select), Update (Alter) and Delete (Drop)
• Used to create, retrieve, update, delete, modify, filter, sort, group, aggregate and query databases.
• Apply SQL constraints to specify rules for the data in a table.
• Join multiple tables using a shared key (column) (JOINS | UNION | UNION ALL)
• Organize data in the selected order ASC (Ascending) or DESC (Descending)
• SQL commands are instructions used to communicate with the database.

Relational Database:

• A database that uses the tabular schema of rows and columns to store and manage data.
• Relational DBMS: A program/software/application used to create, update, and manage relational databases.
• The most well-known RDBMS: MySQL, PostgreSQL, MariaDB, Microsoft SQL Server, and Oracle Database.

Non-Relational Database

• A database that does not use the tabular schema of rows and columns found in most traditional database systems.
• Non-relational databases store data as simple key/value pairs, as JSON, or as a graph consisting of edges and vertices.
• Non-RDBMS: MongoDB, Amazon DynamoDB, Redis, Google Cloud Firestore, etc.

Differences in the databases

• Syntax/Keywords of a particular database may be different (i.e. TOP: Microsoft SQL and LIMIT: MySQL)
• Data types keywords may be different.
• Functions might be different based on name or argument types or order of arguments.

SQL Categories | Subset of SQL

DDL	DQL	DML	DCL
Data Definition Language	Data Query Language	Data Manipulation Language	Data Control Language
Define Data Structure	Retrieve Data from Database	Manipulate Data in Database	Control Access to Data stored in Database
CREATE ALTER DROP RENAME TRUNCATE COMMENT	SELECT	INSERT UPDATE DELETE MERGE	GRANT REVOKE

Most Basic and Common Queries:

SELECT * <All Columns>
FROM     <Table Name>

SELECT   <Columns>
FROM     <Table>
JOIN     <Another Table>
ON       <Common Columns>
WHERE    <Filter Condition>
GROUP BY <Grouping>
HAVING   <Aggregate Filter>
ORDER BY <Column List>
LIMIT    <No. of Rows>

1. Select the name in upper case as an alias

SELECT
UPPER(Name) as UpperCaseName
FROM Employee;

2. Fetch top N employee and order by salary in descending order

SELECT * 
FROM Employee
ORDER BY Salary DESC
LIMIT 5;

3. Concat employees first name and last name in a single column as full name with whitespace

SELECT 
CONCAT(FirstName, ' ', LastName) AS FullName
FROM Employee;

4. Retrieve the designation along with the total salaries paid for each of them

SELECT Designation, SUM(Salary)
FROM Employee
GROUP BY Designation;

5. Retrieve the name of the employee which includes the name "Kiran"

SELECT * 
FROM Employee
WHERE Name Like 'Kiran%';

6. Retrieve only first name from full name

SELECT 
SUBSTRING(FullName, 0, Charindex(' ',FullName))
FROM Employee;

7. Fetch duplicate records from a table

SELECT EID, Department, COUNT(*)
FROM Employee
GROUP BY EID, FullName
HAVING COUNT(*) > 1;

8. Remove duplicates

DELETE FROM Employee
WHERE EID IN (SELECT EID FROM Employee
              GROUP BY Department
              HAVING COUNT(*) > 1);

9. Clone table or empty table with same structure

CREATE TABLE NewTable 
SELECT * FROM OldTable;

SELECT * 
INTO NewTable 
FROM OldTable
WHERE 0 = 1;

CREATE TABLE NewTable
LIKE OldTable;

INSERT 
INTO NewTable
SELECT * 
FROM OldTable;

10. Fetch common records between 2 tables

SELECT * FROM Table1
INTERSECT
SELECT * FROM Table2;

11. Increase the income of all employees by 5% in a table

UPDATE Employee
SET Income = Income + (Income * 0.05);

12. Find names of employees starting with "A"

SELECT Name 
FROM Employee
WHERE Name LIKE 'A%';

13. Find the number of employees working in the Department of Data Science

SELECT COUNT(*) 
FROM Employee
WHERE Department = 'Data Science';

14. Find details of the employees whose first name ends with 'A' and contains 6 alphabets

SELECT * FROM Employee
WHERE FirstName LIKE '______A'

15. Find Employees whose Salary lies between 10,000 and 50,000

SELECT * FROM Employee
WHERE Salary BETWEEN 10000 AND 50000;

16. Find the highest salary in the department

SELECT ID, MAX(Salary) 
FROM Employees
GROUP BY ID;

Select from multiple tables (JOINS)

SELECT D.Name AS 'Department', E.Name AS 'Employee', E.Salary
FROM Employee E
INNER JOIN Department D
ON E.ID = D.ID
WHERE (ID, Salary)
IN (SELECT ID, MAX(Salary) 
    FROM Employees 
    GROUP BY ID)

17. Select second highest salary

-- MAX:
SELECT MAX(Salary) 
FROM Employee
WHERE Salary < (SELECT MAX(Salary) FROM Employee);                               

-- LIMIT:
SELECT Salary
FROM (SELECT Salary FROM Employee ORDER BY  Salary DESC LIMIT 2)
AS EmployeeSalary 
ORDER BY Salary LIMIT 1;

-- TOP:
SELECT Top 1 Salary
FROM (SELECT Top 2 Salary FROM Employee ORDER BY  Salary DESC)
AS EmployeeSalary 
ORDER BY Salary ASC;

18. Find all duplicate emails

SELECT Email
FROM Employee
GROUP BY Email
HAVING COUNT(Email) > 1;

Drop duplicates from the table

-- Create a temporary table:
SELECT DISTINCT * 
INTO NewTable
FROM OldTable;

-- DROP old table:
DROP * FROM OldTable;

-- INSERT into an old table from the new table:
INSERT INTO OldTable
SELECT * FROM NewTable;

-- DROP new table:
DROP TABLE NewTable;

Compare two columns

SELECT * FROM Employee
WHERE Employee_Name IN (SELECT Employee_Name FROM Department);