Scribd
Upload
3 views4 pages

SQL Keywords Guide

The document provides a comprehensive list of important SQL keywords categorized into Data Query Language, Data Definition Language, Data Manipulation Language, Data Control Language, and Transaction Control Language, along with their explanations and examples. It also includes clauses, operators, and other useful keywords that enhance SQL operations. The guide emphasizes that different RDBMS may have variations in syntax and extensions.

Uploaded by

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

SQL Keywords Guide

The document provides a comprehensive list of important SQL keywords categorized into Data Query Language, Data Definition Language, Data Manipulation Language, Data Control Language, and Transaction Control Language, along with their explanations and examples. It also includes clauses, operators, and other useful keywords that enhance SQL operations. The guide emphasizes that different RDBMS may have variations in syntax and extensions.

Uploaded by

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

Complete List of Important SQL Keywords with Explanation

SQL (Structured Query Language) uses keywords to perform various operations on databases such as
creating, modifying, querying, and controlling access. Below is a comprehensive list of commonly used
SQL keywords categorized for clarity, with explanations:

1. Data Query Language (DQL)

• SELECT: Retrieves data from one or more tables.


• Example: SELECT name, age FROM students;

2. Data Definition Language (DDL)

• CREATE: Creates a new table, database, index, or view.

• Example: CREATE TABLE students (id INT, name VARCHAR(50));

• ALTER: Modifies an existing database object (like a table).

• Example: ALTER TABLE students ADD age INT;

• DROP: Deletes tables, views, or databases.

• Example: DROP TABLE students;

• TRUNCATE: Deletes all rows in a table without logging individual row deletions.

• Example: TRUNCATE TABLE students;

3. Data Manipulation Language (DML)

• INSERT: Adds new data into a table.

• Example: INSERT INTO students (id, name) VALUES (1, 'John');

• UPDATE: Modifies existing records.

• Example: UPDATE students SET name = 'Johnny' WHERE id = 1;

• DELETE: Removes existing records from a table.

• Example: DELETE FROM students WHERE id = 1;

1
4. Data Control Language (DCL)

• GRANT: Gives user access privileges to database.

• Example: GRANT SELECT ON students TO user1;

• REVOKE: Removes user access rights.

• Example: REVOKE SELECT ON students FROM user1;

5. Transaction Control Language (TCL)

• COMMIT: Saves all changes made in the transaction.

• Example: COMMIT;

• ROLLBACK: Reverts changes made in the current transaction.

• Example: ROLLBACK;

• SAVEPOINT: Sets a point within a transaction to which you can roll back later.

• Example: SAVEPOINT sp1;

• SET TRANSACTION: Specifies characteristics for the transaction.

• Example: SET TRANSACTION READ ONLY;

6. Clauses and Operators

• WHERE: Filters records based on a condition.

• SELECT * FROM students WHERE age > 18;

• AND / OR / NOT: Combine multiple conditions.

• WHERE age > 18 AND gender = 'M';

• IN: Checks if a value is within a list.

• WHERE name IN ('John', 'Jane');

• BETWEEN: Checks if a value is in a range.

• WHERE age BETWEEN 18 AND 25;

• LIKE: Matches a pattern.

2
• WHERE name LIKE 'J%';

• IS NULL / IS NOT NULL: Checks for null values.

• WHERE address IS NULL;

• ORDER BY: Sorts the result set.

• ORDER BY age DESC;

• GROUP BY: Groups rows sharing a property.

• GROUP BY department;

• HAVING: Filters groups created by GROUP BY.

• HAVING COUNT(*) > 5;

• DISTINCT: Removes duplicate values.

• SELECT DISTINCT department FROM employees;

• AS: Renames a column or table (alias).

• SELECT name AS student_name FROM students;

• JOIN (INNER, LEFT, RIGHT, FULL): Combines rows from two or more tables.

• SELECT * FROM a INNER JOIN b ON [Link] = [Link];

7. Other Useful Keywords

• UNION / UNION ALL: Combines results from multiple SELECT queries.

• SELECT name FROM a UNION SELECT name FROM b;

• CASE: Conditional logic in SELECT or WHERE.

• SELECT name, CASE WHEN age > 18 THEN 'Adult' ELSE 'Minor' END FROM
students;

• EXISTS / NOT EXISTS: Checks if a subquery returns any rows.

• WHERE EXISTS (SELECT * FROM b WHERE [Link] = [Link]);

• ALL / ANY / SOME: Compare value to a list or subquery.

• WHERE salary > ALL (SELECT salary FROM dept);

3
• DEFAULT: Assigns a default value to a column.

• CREATE TABLE test (status VARCHAR(10) DEFAULT 'active');

• CHECK: Adds a condition for a column.

• CHECK (age >= 18);

• PRIMARY KEY: Uniquely identifies each record.

• id INT PRIMARY KEY;

• FOREIGN KEY: Maintains referential integrity.

• FOREIGN KEY (dept_id) REFERENCES departments(id);

• INDEX: Improves query speed.

• CREATE INDEX idx_name ON students(name);

This guide covers the most essential and commonly used SQL keywords. Each RDBMS may have its own
extensions or slight syntax variations (like MySQL, SQL Server, PostgreSQL, etc.).

Common questions

Powered by AI

JOIN operations in SQL allow combining rows from two or more tables based on related columns, essential for data integration . Various types such as INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL JOIN cater to different needs; e.g., INNER JOIN fetches only matching rows . However, these operations can significantly impact performance, with complex joins potentially slowing down queries if tables are large or not properly indexed. Optimizing indexes and understanding relationships between the tables are critical to maintaining performance .

SQL DQL keywords such as SELECT are essential for retrieving data from databases by specifying the columns and conditions under which the data should be returned . A strategic consideration is the need for efficient queries to minimize system load, which might involve indexing relevant columns or using WHERE clauses wisely to filter data and reduce the result set size .

SELECT DISTINCT is critical for eliminating duplicate records in query results, ensuring that the data set reflects unique entries, which is particularly important for reporting and accurate data analysis . The command can impact performance negatively, especially on large tables, because it requires additional processing to check for duplicates. Optimizing the query with appropriate indexing can mitigate some performance issues .

GRANT and REVOKE commands are used to manage user permissions in databases, which is crucial for security. GRANT assigns access privileges to users, allowing them to perform specific operations . REVOKE removes these privileges, thereby preventing unauthorized access and operations . Proper use of these commands ensures that only authorized users can access or modify the database, thus safeguarding sensitive information against unauthorized access .

TCL involves commands like COMMIT, ROLLBACK, and SAVEPOINT, which are critical for maintaining database consistency during transactions. COMMIT ensures that all changes are saved, making them permanent . ROLLBACK reverts changes if there's an issue, maintaining data integrity . SAVEPOINT allows partial rollbacks within transactions, providing flexibility and error recovery in complex operations . SET TRANSACTION can also specify transaction characteristics, further enhancing control .

INNER JOIN returns rows with matching values in both tables, which means it retrieves only the data that meets join conditions from all involved tables . OUTER JOIN, including LEFT, RIGHT, or FULL, returns all rows from one table and the matched rows from the other, with NULLs for non-matching portions. This means OUTER JOINs can include unmatched data, providing a more comprehensive view but possibly increasing the volume of data handled . These operations influence data completeness and performance based on how much data needs to be processed .

The DELETE command removes specific records from a table based on a condition and logs each row deletion, making it suitable for removing specific entries with the option to rollback . TRUNCATE, on the other hand, deletes all rows in a table without logging individual row deletions, often used for bulk deletions because it is faster and requires fewer resources, though it generally cannot be rolled back .

GROUP BY is used for aggregating data by grouping rows that share a specified attribute, facilitating operations like SUM or COUNT to produce summary statistics . The HAVING clause filters these groups based on aggregate conditions, enabling refined analysis such as identifying groups meeting certain criteria . Together, they enhance the usability of aggregated data by enabling complex group-based analysis and reporting, but require careful handling to ensure performance does not degrade with large datasets .

DEFAULT constraints are used to set initial values if none is provided, simplifying data entry and ensuring consistent default values . CHECK constraints enforce data integrity by allowing only values that meet a specified condition, thus preventing invalid data entry . However, overreliance on these constraints can lead to complexity in schema design and potential performance issues if not implemented judiciously. They require careful planning to ensure they align with business rules without unduly slowing down database operations .

UNION combines results from multiple SELECT queries into a single result set, enhancing query flexibility by allowing retrieval from varied data sources . CASE provides conditional logic within SQL, enabling complex evaluations and dynamic output formatting within queries . While UNION is primarily used to merge results from different datasets, CASE is used to manipulate output based on conditional states within a single dataset. Their combined use can powerfully streamline and diversify complex querying tasks .

You might also like