README.md

Quick-Notes-SQL

Some Imp Questions

Difference between DBMS and File system

ACID Properties

Atomicity: operation on data should perform or should not perform at all

Consistancy: if a change in the database is made, it should remain preserved always.

Isolation: In DBMS, Isolation is the property of a database where no data should affect the other one and may occur concurrently, operation on one database should begin when the operation on the first database gets complete

Durability: Durability ensures the permanency of something

Inner Join Vs Natural Join Vs Cross Join

In inner join we have to specifie colums and condition on which we want to join (same value)

In natural join we don't need to specify column or conditon it merge same colum with same value in both the table

In Cross join every Row of 1st table is merge with every Row of 2nd table

GFG Article On inner vs Natural join
GFG Article On Natural Vs Cross Join

JOINS in SQL

FUll join is combination of left and right join

In cross join there is no null never, but in full join we can have NULL values as well, FULL JOIN article

FULL join and Natural Join is Different

What is the difference between the SQL statements DELETE, TRUNCATE and DROP?

DELETE: DML Commnad, we can delete specifice row, Can be rollback

TRUNCATE: DDL Command, We can delete every Row in one go, Can't rollback

Drop: DDL commmand, We can Drop(delete) whole structure,schema in one go, can't rollback

GFG Article

Aggregate and Scalar functions

Both the function return single value

Aggregate function: used to perform operation on values of column

Scalar function: used to perform on user input

LINK TO READ

INDEXING

Video to watch - 1

Video to watch - 2

Video to watch - 3

Video to watch

Primary Index: Unique value + Ordered Value, sparse

Cluster Index: Non-Unique Value(Multiple) + Ordered value , concept of block handler, sparse

Secondary Index: (non-unique or unique) value + unordered value , dense

Primary Index

Clustered Index

Secondary Index

Normalization

Keys in DBMS

super key:set of attributes which uniquely identify tuple,vvalue can be NULL

candidate key: minimum set of attributes which uniquely identify tuple, value can be NULL

Primary key: minimum set of attribute which uniquly identify tuple, value can not be NULL

2nd Highest salary

SELECT MAX(salary) as second_highest_salary
FROM employees
WHERE salary != (SELECT MAX(salary) FROM employees)

SELECT Salary FROM (SELECT Salary FROM Employee ORDER BY salary DESC LIMIT 2) AS Emp ORDER BY salary LIMIT 1

Nth Highest salary

SELECT e1.salary as Nth_highest_salary
FROM employees e1
WHERE n-1 = (SELECT COUNT(DISTINCT e2.salary) FROM employees e2 WHERE e1.salary<e2.salary)    

Questions

Write a SQL query to find the names of employees who have not been assigned to any project.

SELECT t1.employee_id from employee e1
left join projects t2 on t1.employee_id = t2.employee_id
where t2.employee_id IS NULL