SQL

SELECT COUNT(*) FROM EmployeeSalary WHERE Project = 'P1';

SELECT FullName 
FROM EmployeeDetails 
WHERE EmpId IN 
(SELECT EmpId FROM EmpolyeeSalary 
WHERE Salary BETWEEN 5000 AND 10000);

SELECT Project, count(EmpId) EmpProjectCount 
FROM EmployeeSalary 
GROUP BY Project 
ORDER BY EmpProjectCount DESC;

SELECT MID(FullName, 0, LOCATE(' ',FullName)) FROM EmployeeDetails;

SELECT SUBSTRING(FullName, 0, CHARINDEX(' ',FullName)) FROM EmployeeDetails;

SELECT LEFT(FullName, CHARINDEX(' ',FullName) - 1) FROM EmployeeDetails;

SELECT E.FullName, S.Salary  
FROM EmployeeDetails E LEFT JOIN EmployeeSalary S
ON E.EmpId = S.EmpId;

SELECT DISTINCT E.FullName
FROM EmpDetails E
INNER JOIN EmpDetails M
ON E.EmpID = M.ManagerID;

SELECT * FROM EmployeeDetails E 
WHERE EXISTS 
(SELECT * FROM EmployeeSalary S WHERE  E.EmpId = S.EmpId);

SELECT EmpId, Project, Salary, COUNT(*)
FROM EmployeeSalary
GROUP BY EmpId, Project, Salary
HAVING COUNT(*) > 1;

DELETE FROM EmployeeSalary  
WHERE EmpId IN (
SELECT EmpId 
FROM EmployeeSalary       
GROUP BY Project, Salary
HAVING COUNT(*) > 1));

DELETE FROM EmployeeSalary
WHERE rowid NOT IN
(SELECT MAX(rowid) FROM EmployeeSalary GROUP BY EmpId);

SELECT E.EmpId, E.Project, E.Salary
FROM (
    SELECT *, Row_Number() OVER(ORDER BY EmpId) AS RowNumber
    FROM EmployeeSalary
) E
WHERE E.RowNumber % 2 = 1

SELECT E.EmpId, E.Project, E.Salary
FROM (
    SELECT *, Row_Number() OVER(ORDER BY EmpId) AS RowNumber
    FROM EmployeeSalary
) E
WHERE E.RowNumber % 2 = 0

SELECT * INTO newTable FROM EmployeeDetails;

SELECT * INTO newTable FROM EmployeeDetails WHERE 1 = 0;

CREATE TABLE newTable LIKE EmployeeDetails; 

SELECT * FROM EmployeeSalary
INTERSECT
SELECT * FROM ManagerSalary

SELECT * FROM EmployeeSalary
MINUS
SELECT * FROM ManagerSalary

SELECT NOW();

SELECT getdate();

SELECT SYSDATE FROM DUAL;

SELECT * FROM EmployeeDetails
WHERE DateOfJoining BETWEEN '01-01-2016' AND date '31-12-2016';

SELECT * FROM EmployeeDetails
WHERE YEAR(DateOfJoining) = '2016';

SELECT * FROM EmployeeSalary ORDER BY Salary DESC LIMIT N

SELECT TOP N * FROM EmployeeSalary ORDER BY Salary DESC

SELECT * FROM (SELECT * FROM EmployeeSalary ORDER BY Salary DESC)
WHERE ROWNUM <= 3;

SELECT TOP 1 Salary
FROM (
      SELECT DISTINCT TOP N Salary
      FROM Employee
      ORDER BY Salary DESC
      )
ORDER BY Salary ASC

SELECT Salary FROM Employee ORDER BY Salary DESC LIMIT N-1,1;

SELECT Salary
FROM EmployeeSalary Emp1
WHERE 2 = (
                SELECT COUNT( DISTINCT ( Emp2.Salary ) )
                FROM EmployeeSalary Emp2
                WHERE Emp2.Salary > Emp1.Salary
            )

SELECT Salary
FROM EmployeeSalary Emp1
WHERE N-1 = (
                SELECT COUNT( DISTINCT ( Emp2.Salary ) )
                FROM EmployeeSalary Emp2
                WHERE Emp2.Salary > Emp1.Salary
            )

1. DDL – Data Definition Language
2. DML – Data Manipulation Language
3. DCL – Data Control Language
4. TCL – Transactional Control Language

• CREATE – Used to create table in the database
• DROP – Drops the table from the database
• ALTER – Alters the structure of the database
• TRUNCATE – Deletes all the records from the database but not its database structure
• RENAME – Renames a database object

• GRANT – Grants permission to a database user
• REVOKE – Removes access privileges from a user provided with the GRANT command
• Deny – Explicitly prevents a user from receiving a particular permission(e.g. preventing a particular user belonging to a group to receive the access controls

• COMMIT – Commit write the changes to the database
• SAVEPOINT – Savepoints are the breakpoints, these divide the transaction into smaller logical units which could be further roll-backed.
• ROLLBACK – Rollbacks are used to restore the database since a last commit.

CREATE TABLE EMP_SALARY(EmpID int NOT NULL, NAME VARCHAR (30) NOT NULL, Salary INT CHECK (Salary > 1000), PRIMARY KEY (EmpID));

• Delete command is a DML command, it removes rows from table based on the condition specified in the where clause, being a DML statement we can rollback changes made by delete command.
• Truncate is a DDL command, it removes all the rows from table and also frees the space held unlike delete command. It takes lock on the table while delete command takes lock on rows of table.
• Drop is a DDL command, it removes the complete data along with the table structure(unlike truncate command that removes only the rows).

1. Inner Join – To fetch rows from two tables having matching data in the specified columns of both the tables.

   SELECT * FROM TABLE1 INNER JOIN TABLE2 ON TABLE1.columnA = TABLE2.columnA;
   

2. Left Join – To fetch all rows from left table and matching rows of the right table

   SELECT * FROM TABLE1 LEFT JOIN TABLE2 ON TABLE1.columnA = TABLE2.columnA;
   

3. Right Join – To fetch all rows from right table and matching rows of the left table

   SELECT * FROM TABLE1 RIGHT JOIN TABLE2 ON TABLE1.columnA = TABLE2.columnA;
   

4. Full Outer Join – To fetch all rows of left table and all rows of right table

   SELECT * FROM TABLE1 FULL OUTER JOIN TABLE2 ON TABLE1.columnA = TABLE2.columnA;
   

5. Self Join – Joining a table to itself, for referencing its own data

   SELECT * FROM TABLE1 T1, TABLE1 T2 WHERE T1.columnA = T2.columnB;
   

Select Employee from Emp_Project wh2ere Project = P2;

Select Employee from Emp_Project groupby Employee having count(Project)>1;

SELECT * INTO newtable FROM oldTable;

CREATE VIEW VIEW_NAME AS
SELECT COLUMN1, COLUMN2
FROM  TABLE_NAME WHERE CONDITION;

SELECT Employee, Count(Project )
FROM Emp_Project
WHERE Employee != 'A'
GROUP BY Project;

1. Atomicity – All the operations in the transaaction are performed as a whole or not performed at all.
2. Consistency – State of database changes only on successful committed transaction.
3. Isolation – Even with concurrent execution of the multiple transactions, the final state of the DB would be same as if transactions got executed sequentially. In other words each transaction is isolated from one another.
4. Durability – Even in the state of crash or power loss the state of committed transaction remain persistent.

1. Shared locks – Allows data to be read-only(Select operations), prevents the data to be updated when in shared lock.
2. Update locks – Applied to resources that can be updated. There can be only one update lock on a data at a time.
3. Exclusive locks – Used to lock data being modified(INSERT, UPDATE, or DELETE) by one transaction thus ensuring that multiple updates cannot be made to the same resource at the same time.
4. Intent locks – A notification mechanism using which a transaction conveys that intends to acquire lock on data.
5. Schema locks- Used for operations when schema or structure of the database is required to be updated.
6. Bulk Update locks – Used in case of bulk operations when the TABLOCK hint is used.

• Count() – Returns the count of the number of rows returned by the SQL expression
• Max() – Returns the max value out of the total values
• Min() – Returns the min value out of the total values
• Avg() – Returns the average of the total values
• Sum() – Returns the sum of the values returned by the SQL expression

• UCASE() – USed to convert a string to upper case
• LCASE() – Used to convert a string to lower case
• ROUND() – Used to round a number to the decimal places specified
• NOW() – Used to fetch current system date and time
• LEN() – Used to find length of a string
• SUBSTRING() or MID() – MID and SUBSTRING are synonyms in SQL. They are used to extract a substring from a string by specifying the start and end index. Syntax – SUBSTRING(ColumnName,startIndex,EndIndex).
• LOCATE() – Used to find the index of the character in a string. Syntax – LOCATE(character,ColumnName)
• LTRIM() – Used to trim spaces from left
• RTRIM() – Used to trim spaces from right

CREATE PROCEDURE 
procedureName
AS
Begin
     Set of SQL statements
End

1. Stored procedures improve performance as the procedures are pre-compiled as well as cached.
2. Make queries easily maintainable and reusable as any change is required to be made at single location.
3. Reduce network usage and traffic.
4. Improve security as stored procedures restrict direct access to the database.

CREATE TRIGGER 
triggerName 
triggerTime{Before or After}
triggerEvent{Insert, Update or Delete}     
ON tableName 
FOR EACH ROW     
triggerBody

DELETE PT 
FROM ParentTable PT
LEFT JOIN ChildTable CT 
ON PT.ID = CT.ID 
WHERE PT.ID IS NULL