Sql Server: SQL Keywords Reference

Wednesday, December 18, 2024

SQL Keywords Reference

Here’s a quick reference guide for common SQL keywords and their usage. SQL (Structured Query Language) is used to interact with databases and perform operations such as data retrieval, insertion, updates, and deletion.

1. SELECT

Used to retrieve data from a database.

SELECT column1, column2 FROM table_name;

2. INSERT INTO

Used to insert data into a table.

INSERT INTO table_name (column1, column2) VALUES (value1, value2);

3. UPDATE

Used to modify existing records in a table.

UPDATE table_name SET column1 = value1, column2 = value2 WHERE condition;

4. DELETE

Used to remove records from a table.

DELETE FROM table_name WHERE condition;

5. CREATE DATABASE

Used to create a new database.

CREATE DATABASE database_name;

6. DROP DATABASE

Used to delete an entire database.

DROP DATABASE database_name;

7. CREATE TABLE

Used to create a new table within a database.

CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    column3 datatype,
    ...
);

8. DROP TABLE

Used to delete a table from a database.

DROP TABLE table_name;

9. ALTER TABLE

Used to modify an existing table (e.g., add, delete, or modify columns).

ALTER TABLE table_name ADD column_name datatype;
ALTER TABLE table_name DROP COLUMN column_name;
ALTER TABLE table_name MODIFY column_name datatype;

10. WHERE

Used to filter records based on a specified condition.

SELECT column1, column2 FROM table_name WHERE condition;

11. ORDER BY

Used to sort the result set in ascending or descending order.

SELECT column1, column2 FROM table_name ORDER BY column1 [ASC|DESC];

12. GROUP BY

Used to group rows that have the same values in specified columns.

SELECT COUNT(*), column1 FROM table_name GROUP BY column1;

13. HAVING

Used to filter records after grouping (usually with GROUP BY).

SELECT COUNT(*), column1 FROM table_name GROUP BY column1 HAVING COUNT(*) > 1;

14. JOIN

Used to combine rows from two or more tables based on a related column.

INNER JOIN: Returns records that have matching values in both tables.

SELECT column1, column2 FROM table1 INNER JOIN table2 ON table1.id = table2.id;

LEFT JOIN (LEFT OUTER JOIN): Returns all records from the left table and the matched records from the right table.

SELECT column1, column2 FROM table1 LEFT JOIN table2 ON table1.id = table2.id;

RIGHT JOIN (RIGHT OUTER JOIN): Returns all records from the right table and the matched records from the left table.

SELECT column1, column2 FROM table1 RIGHT JOIN table2 ON table1.id = table2.id;

FULL JOIN (FULL OUTER JOIN): Returns all records when there is a match in either left or right table.

SELECT column1, column2 FROM table1 FULL JOIN table2 ON table1.id = table2.id;

15. UNION

Combines the results of two or more SELECT statements. Duplicate records are removed.

SELECT column1 FROM table1
UNION
SELECT column1 FROM table2;

16. DISTINCT

Used to return only distinct (unique) values.

SELECT DISTINCT column1 FROM table_name;

17. AND, OR, NOT

Used to combine multiple conditions in a WHERE clause.

SELECT * FROM table_name WHERE condition1 AND condition2;
SELECT * FROM table_name WHERE condition1 OR condition2;
SELECT * FROM table_name WHERE NOT condition;

18. IN

Used to specify multiple values in a WHERE clause.

SELECT * FROM table_name WHERE column1 IN (value1, value2, value3);

19. BETWEEN

Used to filter the result set within a range.

SELECT * FROM table_name WHERE column1 BETWEEN value1 AND value2;

20. LIKE

Used to search for a specified pattern in a column.

SELECT * FROM table_name WHERE column1 LIKE 'pattern%';

21. IS NULL

Used to test for null values.

SELECT * FROM table_name WHERE column1 IS NULL;

22. AS

Used to rename a column or table with an alias.

SELECT column1 AS alias_name FROM table_name;

23. LIMIT / OFFSET

Used to specify the number of records to return or skip.

SELECT * FROM table_name LIMIT 10;
SELECT * FROM table_name LIMIT 10 OFFSET 5;

24. CASE

Used for conditional expressions.

SELECT column1, 
       CASE 
           WHEN column2 = 'value' THEN 'result1'
           ELSE 'result2'
       END AS alias_name 
FROM table_name;

25. EXISTS

Used to test for the existence of any record in a subquery.

SELECT * FROM table_name WHERE EXISTS (SELECT * FROM another_table WHERE condition);

26. ALL, ANY

Used to compare a value to a set of values returned by a subquery.

SELECT * FROM table_name WHERE column1 > ALL (SELECT column2 FROM another_table);
SELECT * FROM table_name WHERE column1 = ANY (SELECT column2 FROM another_table);

27. INDEX

Used to create an index on a table to speed up retrieval.

CREATE INDEX index_name ON table_name (column1, column2);

28. VIEW

Used to create a virtual table based on the result of a SELECT query.

CREATE VIEW view_name AS
SELECT column1, column2 FROM table_name WHERE condition;

29. TRUNCATE

Used to delete all rows in a table, but keeps the table structure.

TRUNCATE TABLE table_name;

30. TRANSACTION

Used to manage a set of SQL operations as a transaction (start, commit, or rollback).

BEGIN TRANSACTION;
UPDATE table_name SET column1 = value WHERE condition;
COMMIT;
-- or
ROLLBACK;

31. SAVEPOINT

Used to set a savepoint within a transaction, which allows rolling back to that point.

SAVEPOINT savepoint_name;

32. ROLLBACK

Used to undo changes made in the current transaction.

ROLLBACK TO savepoint_name;

33. COMMIT

Used to save all changes made in the current transaction.

COMMIT;

This covers many of the foundational SQL keywords and functions. Depending on the database system (MySQL, PostgreSQL, SQL Server, etc.), some keywords or functions may vary slightly or have additional functionality.

Sql Server