Scribd
Upload
2 views4 pages

SQL Command Reference Guide

The document provides a complete SQL command reference, detailing various commands such as CREATE TABLE, ALTER TABLE, and DELETE, along with their usage and syntax. It covers table management, data manipulation, and query operations, including examples for each command. Additionally, it explains constraints like NOT NULL, UNIQUE, and FOREIGN KEY, as well as aggregate functions and sorting methods.

Uploaded by

Jansdale Yusi
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
2 views4 pages

SQL Command Reference Guide

The document provides a complete SQL command reference, detailing various commands such as CREATE TABLE, ALTER TABLE, and DELETE, along with their usage and syntax. It covers table management, data manipulation, and query operations, including examples for each command. Additionally, it explains constraints like NOT NULL, UNIQUE, and FOREIGN KEY, as well as aggregate functions and sorting methods.

Uploaded by

Jansdale Yusi
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 SQL Command Reference

CREATE TABLE

Use: Defines a new table.


CREATE TABLE Employees (
EmpID NUMBER(6) PRIMARY KEY,
EmpName VARCHAR2(50),
Salary NUMBER(8,2)
);

ALTER TABLE

Use: Modifies an existing table.


ALTER TABLE Employees ADD Email VARCHAR2(100);

DROP TABLE

Use: Deletes a table and its data.


DROP TABLE Employees;

RENAME

Use: Renames a table or column.


RENAME Employees TO Staff;

SET UNUSED

Use: Marks columns for later removal.


ALTER TABLE Employees SET UNUSED (MiddleName);

DROP UNUSED COLUMNS

Use: Removes columns marked as unused.


ALTER TABLE Employees DROP UNUSED COLUMNS;

INSERT

Use: Adds a new row.


INSERT INTO Employees (EmpID, EmpName, Salary) VALUES (101, 'John Doe', 50000);

UPDATE

Use: Modifies existing row(s).


UPDATE Employees SET Salary = 60000 WHERE EmpID = 101;

DELETE
Use: Deletes specific row(s).
DELETE FROM Employees WHERE EmpID = 101;

TRUNCATE

Use: Deletes all rows (faster than DELETE).


TRUNCATE TABLE Employees;

COMMIT

Use: Saves all changes.


COMMIT;

ROLLBACK

Use: Reverts changes since last commit.


ROLLBACK;

SAVEPOINT

Use: Creates a point for partial rollback.


SAVEPOINT before_salary_update;

ROLLBACK TO SAVEPOINT

Use: Rolls back to a specific point.


ROLLBACK TO SAVEPOINT before_salary_update;

SELECT

Use: Retrieves data.


SELECT EmpName, Salary FROM Employees WHERE Salary > 50000;

NOT NULL

Use: Prevents null values.


EmpName VARCHAR2(50) NOT NULL

UNIQUE

Use: Ensures all values are different.


Email VARCHAR2(100) UNIQUE

PRIMARY KEY

Use: Uniquely identifies each record.


EmpID NUMBER PRIMARY KEY

FOREIGN KEY
Use: Enforces referential integrity.
FOREIGN KEY (DeptID) REFERENCES Departments(DeptID)

CHECK

Use: Ensures values meet a condition.


Salary NUMBER(8,2) CHECK (Salary > 0)

DEFAULT

Use: Sets a default value if none is provided.


HireDate DATE DEFAULT SYSDATE

VARCHAR2(size)

Use: Variable-length text.


EmpName VARCHAR2(50)

NUMBER(p, s)

Use: Numeric with precision and scale.


Salary NUMBER(8,2)

DATE

Use: Stores date and time.


HireDate DATE

CLOB, BLOB, BFILE, RAW

Use: Large text/binary/external files.


Content CLOB, Image BLOB

Subquery

Use: Query inside another query.


SELECT EmpName FROM Employees WHERE DeptID = (SELECT DeptID FROM Departments WHERE
DeptName = 'HR');

Join

Use: Combines rows from two or more tables.


SELECT [Link], [Link] FROM Employees E JOIN Departments D ON [Link] = [Link];

Aggregate Functions

Use: Performs a calculation on a set of values.


SELECT AVG(Salary) FROM Employees;
ORDER BY

Use: Sorts the result set.


SELECT EmpName FROM Employees ORDER BY Salary DESC;

GROUP BY

Use: Groups rows with the same values.


SELECT DeptID, COUNT(*) FROM Employees GROUP BY DeptID;

You might also like