| ADD | Will add a new column to an existing table. | ALTER TABLE student ADD email_address VARCHAR(255) |
|---|
| ALTER TABLE | Adds edits or deletes columns in a table | ALTER TABLE student DROP COLUMN email_address; |
|---|
| ALTER COLUMN | Can change the datatype of a table’s column | ALTER TABLE student ALTER COLUMN phone VARCHAR(15) |
|---|
| AS | Renames a table/column with an alias existing only for the query duration. | SELECT name AS student_name, phone FROM student; |
|---|
| ASC | Used in conjunction with ORDER BY to sort data in ascending order. | SELECT column1, column2, … FROM table_name ORDER BY column1, column2, … ASC; |
|---|
| DESC | Used in conjunction with ORDER BY to sort data in descending order. | SELECT column1, column2, … FROM table_name ORDER BY column1, column2, … DESC; |
|---|
| CHECK | Constrains the value which can be added to a column. | CREATE TABLE student(fullName varchar(255), age INT, CHECK(age >= 18)); |
|---|
| CREATE DATABASE | Creates a new database. | CREATE DATABASE student; |
|---|
| DEFAULT | Sets the default value for a given column. | CREATE TABLE products(ID int, name varchar(255) DEFAULT ‘Username’, from date DEFAULT GETDATE()); |
|---|
| DELETE | Delete values from a table. | DELETE FROM users WHERE user_id= 674; |
|---|
| DROP COLUMN | Deletes/Drops a column from a table. | ALTER TABLE student DROP COLUMN name; |
|---|
| DROP DATABASE | Completely deletes a database with all its content within. | DROP DATABASE student; |
|---|
| DROP DEFAULT | Removes a default value for a column. | ALTER TABLE student ALTER COLUMN age DROP DEFAULT; |
|---|
| DROP TABLE | Deletes a table from a database. | DROP TABLE students; |
|---|
| FROM | Determines which table to read or delete data from. | SELECT * FROM students; |
|---|
| IN | Used with WHERE clause for multiple OR conditionals. | SELECT * FROM students WHERE name IN(‘Scaler’, ‘Interviewbit’,‘Academy’); |
|---|
| ORDER BY | Used to sort given data in Ascending or Descending order. | SELECT * FROM student ORDER BY age ASC |
|---|
| SELECT DISTINCT | Works in the same war as SELECT, except that only unique values are included in the results. | SELECT DISTINCT age from student; |
|---|
| TOP | Used in conjunction with SELECT to select a fixed number of records from a table. | SELECT TOP 5 * FROM students; |
|---|
| VALUES | Used along with the INSERT INTO keyword to add new values to a table. | INSERT INTO Customers (CustomerName, City, Country) VALUES (‘Cardinal’, ‘Stavanger’, ‘Norway’); |
|---|
| WHERE | Filters given data based on some given condition. | SELECT * FROM students WHERE age >= 18; |
|---|
| UNIQUE | Ensures that all values in a column are different. | UNIQUE (ID) |
|---|
| UNION | Used to combine the result-set of two or more SELECT statements. | SELECT column_name(s) FROM Table1 UNION SELECT column_name(s) FROM Table2; |
|---|
| UNION ALL | Combines the result set of two or more SELECT statements(it allows duplicate values) | SELECT City FROM table1 UNION ALL SELECT City FROM table2 ORDER BY City; |
|---|
| SELECT TOP | Used to specify the number of records to return. | SELECT TOP 3 * FROM Students; |
|---|
| LIMIT | Puts a restriction on how many rows are returned from a query. | SELECT * FROM table1 LIMIT 3; |
|---|
| UPDATE | Modifies the existing records in a table. | UPDATE Customers SET ContactName = ‘Scaler’, City = ‘India’ WHERE CustomerID = 1; |
|---|
| SET | Used with UPDATE to specify which columns and values should be updated in a table. | UPDATE Customers SET ContactName = ‘Scaler’, City= ‘India’ WHERE CustomerID = 1; |
|---|
| IS NULL | Column values are tested for NULL values using this operator. | SELECT CustomerName, ContactName, Address FROM Customers WHERE Address IS NULL; |
|---|
| LIKE | Used to search for a specified pattern in a column. | SELECT * FROM Students WHERE Name LIKE ‘a%’; |
|---|
| ROWNUM | Returns a number indicating the order in which Oracle selects the row from a table or set of joined rows. | SELECT * FROM Employees WHERE ROWNUM < 10; |
|---|
| GROUP BY | Groups rows that have the same values into summary rows. | SELECT COUNT(StudentID), State FROM Students GROUP BY State; |
|---|
| HAVING | Enables the user to specify conditions that filter which group results appear in the results. | HAVING COUNT(CustomerID) > 5; |
|---|
