SQL

Difference between JOIN AND UNION?

In simple terms, joins combine data into new columns.  If two tables are joined together, then the data from the first table is shown in one set of columns alongside the second table’s column in the same row.
Unions combine data into new rows.  If two tables are “unioned” together, then the data from the first table is in one set of rows, and the data from the second table in another set.  The rows are in the same result.

What is Equi Join?

Equi join is a special type of join in which we use only an equality operator. Hence, when you make a query for join using equality operator then that join query comes under Equi join.

Inner join can have equality (=) and other operators (like <,>,<>) in the join condition.
Equi join only have equality (=) operator in the join condition.
Equi join can be an Inner join, Left Outer join, Right Outer join
The USING clause is not supported by SQL Server and Sybase. This clause is supported by Oracle and MySQL.

What is Natural Join?

A natural join is a type of equi join which occurs implicitly by comparing all the same names columns in both tables. The join result has only one column for each pair of equally named columns.
In Natural join, you can't see what columns from both the tables will be used in the join. In Natural join, you might not get the desired result what you are expecting.
Natural join clause is not supported by SQL Server, it is supported by Oracle and MySQL.

Difference between WHERE and HAVING?

The WHERE clause filters rows from the result set based on a condition, whereas the HAVING clause filters groups based on an aggregate value computed for each group.

For example, consider a table with columns "Department" and "Salary".

The following SQL query uses a WHERE clause to select only the rows where the salary is greater than 50,000:

SELECT Department, SUM(Salary)
FROM employees
WHERE Salary > 50000
GROUP BY Department;

The following SQL query uses a HAVING clause to select only the groups where the sum of salaries is greater than 100,000:

SELECT Department, SUM(Salary)
FROM employees
GROUP BY Department
HAVING SUM(Salary) > 100000;

Difference between UNION AND UNION ALL.

The UNION operator combines the result sets of two or more SELECT statements into a single result set and removes duplicate rows, while UNION ALL combines the result sets and retains duplicate rows.

Example: Consider two tables, table1 and table2, each with columns "A" and "B".

SELECT A, B FROM table1
UNION
SELECT A, B FROM table2;

The above query will return the combined result set of table1 and table2, but with duplicates removed.

SELECT A, B FROM table1
UNION ALL
SELECT A, B FROM table2;

The above query will return the combined result set of table1 and table2, with duplicates retained.

Difference between delete and truncate.

The DELETE and TRUNCATE statements are used to remove data from a database table, but they differ in several key ways:

1. DELETE is a DML (Data Manipulation Language) operation that removes specific rows from a table based on a condition. It can be rolled back and it generates undo logs.

  DELETE FROM table_name WHERE condition;

2. TRUNCATE is a DDL (Data Definition Language) operation that removes all data from a table by deallocating the data pages used to store the data. It is faster and more efficient than DELETE, as it does not generate undo logs or fire triggers, but it cannot be rolled back.

TRUNCATE TABLE table_name;

In summary, DELETE is slower but more flexible, and TRUNCATE is faster but less flexible. Choose TRUNCATE when you want to quickly remove all data from a table, and DELETE when you need to remove specific rows based on a condition.

Select the student who gets max marks (from SQL).

SELECT student_name, marks
FROM students
WHERE marks = (SELECT MAX(marks) FROM students);

SELECT student_name, marks
FROM students
ORDER BY marks DESC
LIMIT 1;

SELECT student_name, marks
FROM (
  SELECT student_name, marks, ROW_NUMBER() OVER (ORDER BY marks DESC) AS rank
  FROM students
) t
WHERE rank = 1;

Write a query on SELF JOIN.

A self-join is a regular join, but the table is joined with itself. This is useful when you want to compare rows within a single table.

Here is an example of a self join using a table named "employees" with columns "employee_id", "name", and "manager_id":

SELECT e1.name AS employee, e2.name AS manager
FROM employees e1
JOIN employees e2 ON e1.manager_id = e2.employee_id;

Select the employee with the second highest salary.

SELECT employee_name, salary
FROM (
  SELECT employee_name, salary,
         ROW_NUMBER() OVER (ORDER BY salary DESC) AS rank
  FROM employees
) t
WHERE rank = 2;
----------------------------------------------------------------------
SELECT employee_name, salary
FROM (
  SELECT employee_name, salary,
         DENSE_RANK() OVER (ORDER BY salary DESC) AS rank
  FROM employees
) t
WHERE rank = 2;
----------------------------------------------------------------------
SELECT employee_name, salary
FROM employees
WHERE salary IN (
  SELECT DISTINCT salary
  FROM employees
  ORDER BY salary DESC
  LIMIT 2
);
----------------------------------------------------------------------
SELECT employee_name, salary
FROM employees
WHERE salary = (
  SELECT MIN(salary)
  FROM (
    SELECT DISTINCT salary
    FROM employees
    ORDER BY salary DESC
    LIMIT 2
  ) t
);
----------------------------------------------------------------------
SELECT employee_name, salary
FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET 1;
----------------------------------------------------------------------

1. DDL

Create
CREATE TABLE Persons
(PersonID int,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255));
        
Alter 
ALTER TABLE Persons
ADD DateOfBirth date
ALTER COLUMN DateOfBirth year
DROP COLUMN DateOfBirth

Drop
DROP TABLE Persons

Truncate 
TRUNCATE TABLE Persons

2. DML

Select
SELECT * FROM Persons

Insert
INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country)
VALUES ('Cardinal','Tom B. Erichsen','Skagen 21','Stavanger','4006','Norway');

Update 
UPDATE Customers
SET ContactName='Alfred Schmidt', City='Hamburg';


Delete
DELETE FROM Customers
WHERE CustomerName='Alfreds Futterkiste' AND ContactName='Maria Anders';

3. DCL

GRANT ALL ON employees TO Madhu;
REVOKE ALL ON employees TO Madhu;

4. TCL

COMMIT
ROLLBACK