Scribd
Upload
5 views9 pages

Truncate vs Delete in SQL Explained

The document outlines the differences between the SQL commands 'Truncate' and 'Delete', highlighting that Truncate is a DDL command that is faster, cannot be rolled back, and resets the auto-increment counter, while Delete is a DML command that is slower, can be rolled back, and supports a WHERE clause. It also provides various SQL query examples for selecting, filtering, sorting, and joining data from tables. Additionally, it explains the use of aggregate functions and the distinction between HAVING and WHERE clauses.

Uploaded by

examscap
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
5 views9 pages

Truncate vs Delete in SQL Explained

The document outlines the differences between the SQL commands 'Truncate' and 'Delete', highlighting that Truncate is a DDL command that is faster, cannot be rolled back, and resets the auto-increment counter, while Delete is a DML command that is slower, can be rolled back, and supports a WHERE clause. It also provides various SQL query examples for selecting, filtering, sorting, and joining data from tables. Additionally, it explains the use of aggregate functions and the distinction between HAVING and WHERE clauses.

Uploaded by

examscap
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd

Difference between Truncate and Delete

-Truncate:
1)DDL Command
2) Faster speed
3) No where clause
4) Does not fire trigger
5) Can’t be rolled back in most DBs
6) Reset auto-increment counter
-Delete:
1)DML Command
2) Slower speed
3) supports where clause
4) Fires trigger
5) Can be rolled back (if in transaction)
6) Doesn’t reset auto-increment counter
Queries:
>Select all columns from a table - SELECT * FROM employees;
>Select unique values - SELECT DISTINCT department FROM employees;
>Filter records - SELECT * FROM employees WHERE salary > 50000;
>Sorting - SELECT * FROM employees ORDER BY salary DESC;
>Count, Avg, Max, Min, Sum - SELECT COUNT(*), AVG(salary), MAX(salary),
MIN(salary) FROM employees;
>Group By - SELECT department, COUNT(*) FROM employees GROUP BY
department;
>Having vs Where - SELECT department, COUNT(*) FROM employees
GROUP BY department
HAVING COUNT(*) > 5;

>Get employee name and their department name -


SELECT [Link], d.dept_name
FROM employees e
JOIN departments d ON e.dept_id = d.dept_id;
>Get all employees and their department names (even if they don’t belong to a
department) -
SELECT [Link], d.dept_name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.dept_id;
>Find employees with no department –
SELECT [Link]
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.dept_id
WHERE d.dept_id IS NULL;

>Find employees who earn more than average –


SELECT name
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);
>Find the nth highest salary (e.g., 2nd highest) — correlated subquery-
SELECT DISTINCT salary
FROM employees e1
WHERE 2 = (
SELECT COUNT(DISTINCT salary)
FROM employees e2
WHERE [Link] > [Link]
);
Select
Where
Order By

If no DESC then ascending order


The following SQL statement selects all customers from the "Customers" table,
sorted by the "Country" and the "CustomerName" column. This means that it
orders by Country, but if some rows have the same Country, it orders them by
CustomerName:

Insert Into

Update Table
Without where all records will be updated

Delete From

without where everything will be deleted


Top

Limit

As

without
Join

better way

join and inner join same


Union

Having

You might also like