1. Delete duplicate rows from the table.
DELETE FROM your_table
WHERE [your_primary_key_column] NOT IN (
SELECT MIN([your_primary_key_column])
FROM your_table
GROUP BY column1, column2, ... -- List of columns to identify duplicates
);
2. Display the alternate row from the table.
SELECT * FROM (
SELECT *,
ROW_NUMBER() OVER (ORDER BY [your_ordering_column]) AS RowNum
FROM your_table
) AS SubQuery
WHERE RowNum % 2 = 1;
3. Delete alternate row from the table.
DELETE FROM your_table
WHERE [your_primary_key_column] IN (
SELECT [your_primary_key_column] FROM (
SELECT [your_primary_key_column],
ROW_NUMBER() OVER (ORDER BY [your_ordering_column]) AS RowNum
FROM your_table
) AS SubQuery
WHERE RowNum % 2 = 0
);
4. Update multiple rows using a single update statement.
UPDATE your_table
SET column1 = CASE
WHEN condition1 THEN value1
WHEN condition2 THEN value2
ELSE column1
END,
column2 = CASE
WHEN condition1 THEN value3
WHEN condition2 THEN value4
ELSE column2
END,
...
WHERE your_condition_column = your_condition;
5. Find the third highest paid and third lowest paid salary.
SELECT DISTINCT Salary
FROM Employees
ORDER BY Salary DESC
OFFSET 2 ROWS FETCH NEXT 1 ROW ONLY; -- Third highest paid salary
SELECT DISTINCT Salary
FROM Employees
ORDER BY Salary ASC
OFFSET 2 ROWS FETCH NEXT 1 ROW ONLY; -- Third lowest paid salary
6. Display the 3rd, 4th, 9th rows from the table.
SELECT *
FROM (
SELECT *,
ROW_NUMBER() OVER (ORDER BY [your_ordering_column]) AS RowNum
FROM your_table
) AS SubQuery
WHERE RowNum IN (3, 4, 9);
7. Display the ename, which starts with j, k, l, or m.
SELECT ename
FROM Employees
WHERE ename LIKE '[j-m]%';
8. Show all employees hired in the first half of the month.
SELECT *
FROM Employees
WHERE DAY(hire_date) <= 15;
9. Display three records in the first row, two records in the second
row, and one record in the third row in a single SQL statement.
SELECT *
FROM (
SELECT *,
NTILE(3) OVER (ORDER BY [your_ordering_column]) AS RowGroup
FROM your_table
) AS SubQuery
WHERE RowGroup IN (1, 2, 3);
10. SQL statements for rollback, commit, and savepoints:
Rollback: To undo changes made during a transaction before it is committed.
sql
ROLLBACK;
Commit: To save changes made during the transaction permanently.
sql
COMMIT;
Savepoint: To create a point in a transaction to which you can later roll back.
SAVEPOINT savepoint_name;
