Scribd
Upload
5 views

SQL_Commands_Notes

Sqp notes

Uploaded by

prabhuyoube
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
5 views

SQL_Commands_Notes

Sqp notes

Uploaded by

prabhuyoube
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
You are on page 1/ 4

SQL Commands - Comprehensive Notes

SQL Commands - Comprehensive Notes

1. Data Definition Language (DDL)

- Commands used to define and manage database structure:

- CREATE: Creates new database objects like tables, indexes, or views.

Syntax: CREATE TABLE table_name (column1 datatype, column2 datatype, ...);

- ALTER: Modifies existing database objects.

Syntax: ALTER TABLE table_name ADD column_name datatype;

- DROP: Deletes database objects permanently.

Syntax: DROP TABLE table_name;

- TRUNCATE: Removes all records from a table without logging.

Syntax: TRUNCATE TABLE table_name;

2. Data Manipulation Language (DML)

- Commands used to manipulate data in the database:

- SELECT: Retrieves data from one or more tables.

Syntax: SELECT column1, column2 FROM table_name WHERE condition;

- INSERT: Adds new rows to a table.

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

- UPDATE: Modifies existing data in a table.

Syntax: UPDATE table_name SET column1 = value1 WHERE condition;

- DELETE: Removes rows from a table.

Syntax: DELETE FROM table_name WHERE condition;


3. Data Control Language (DCL)

- Commands used to control access to data:

- GRANT: Provides privileges to users.

Syntax: GRANT privilege ON object TO user;

- REVOKE: Removes privileges from users.

Syntax: REVOKE privilege ON object FROM user;

4. Transaction Control Language (TCL)

- Commands used to manage transactions in a database:

- COMMIT: Saves all changes made in the transaction.

Syntax: COMMIT;

- ROLLBACK: Reverts changes made in the current transaction.

Syntax: ROLLBACK;

- SAVEPOINT: Sets a point within a transaction for partial rollbacks.

Syntax: SAVEPOINT savepoint_name;

- RELEASE SAVEPOINT: Removes a savepoint.

Syntax: RELEASE SAVEPOINT savepoint_name;

5. Query Clauses in SQL

- Clauses used to refine queries:

- WHERE: Filters records based on a condition.

Syntax: SELECT * FROM table_name WHERE condition;

- GROUP BY: Groups rows sharing a property for aggregation.

Syntax: SELECT column, COUNT(*) FROM table_name GROUP BY column;

- HAVING: Filters grouped data.

Syntax: SELECT column, COUNT(*) FROM table_name GROUP BY column HAVING condition;

- ORDER BY: Sorts results in ascending or descending order.


Syntax: SELECT * FROM table_name ORDER BY column ASC|DESC;

- LIMIT: Restricts the number of rows returned.

Syntax: SELECT * FROM table_name LIMIT number;

6. Joins in SQL

- Combines rows from two or more tables:

- INNER JOIN: Retrieves records with matching values in both tables.

Syntax: SELECT * FROM table1 INNER JOIN table2 ON table1.column = table2.column;

- LEFT JOIN: Retrieves all rows from the left table and matching rows from the right.

Syntax: SELECT * FROM table1 LEFT JOIN table2 ON table1.column = table2.column;

- RIGHT JOIN: Retrieves all rows from the right table and matching rows from the left.

Syntax: SELECT * FROM table1 RIGHT JOIN table2 ON table1.column = table2.column;

- FULL OUTER JOIN: Retrieves all rows when there is a match in either table.

Syntax: SELECT * FROM table1 FULL OUTER JOIN table2 ON table1.column = table2.column;

7. SQL Functions

- Functions to perform calculations or manipulate data:

- Aggregate Functions: COUNT(), SUM(), AVG(), MAX(), MIN().

Syntax: SELECT COUNT(column) FROM table_name;

- String Functions: CONCAT(), LENGTH(), SUBSTRING(), UPPER(), LOWER().

Syntax: SELECT CONCAT(column1, column2) FROM table_name;

- Date Functions: NOW(), CURDATE(), YEAR(), MONTH(), DAY().

Syntax: SELECT NOW();

8. Views in SQL

- Virtual tables created by a query:

- CREATE VIEW: Creates a view.


Syntax: CREATE VIEW view_name AS SELECT columns FROM table_name WHERE condition;

- DROP VIEW: Deletes a view.

Syntax: DROP VIEW view_name;

9. Indexes in SQL

- Improves query performance:

- CREATE INDEX: Creates an index.

Syntax: CREATE INDEX index_name ON table_name(column);

- DROP INDEX: Deletes an index.

Syntax: DROP INDEX index_name;

10. Triggers in SQL

- Executes automatically in response to events:

- CREATE TRIGGER: Creates a trigger.

Syntax: CREATE TRIGGER trigger_name BEFORE|AFTER event ON table_name FOR EACH

ROW BEGIN ... END;

- DROP TRIGGER: Deletes a trigger.

Syntax: DROP TRIGGER trigger_name;

Conclusion

SQL commands provide powerful tools to manage and interact with databases efficiently. Mastering

these commands ensures effective database handling.

You might also like