Oracle_Sql_Cheatsheet

.xlsx

School

Rutgers University, Newark *

*We aren’t endorsed by this school

Course

311

Subject

Information Systems

Date

Oct 30, 2023

Type

xlsx

Pages

Uploaded by jackelynbenavides842

Command Description Category Truncate Describe Create Insert Insert multiple values at a time Update Update attributes in a table Alter Adding Column Alter Dropping Column Alter Modify Column/Datatype Delete Select DML Distinct DML From DML Where DML DML Selects attributes to be returned by the query It is used to specify that the statement is a query which returns unique values in specified column Specifies the tables from which data will be retrieved Filters the rows of data based on provided criteria Where(Comparison Operators) Equal to(=); Less Than(<); Less Than Or Equal to(<=); Greater Than (>); Greater Than Or Equal to(>-=); Not Equal to (<> or !=)

Where(Logical Operators) DML Where(Special) DML Where(Special) DML Where(Special) DML Where(Special) DML Where(Special) DML Offset DML Group By DML Having DML Allows to include multiple conditions in a query through the use of logical operators (AND OR, AND NOT) BETWEEN : Used to check whether an attribute is within a range IN : Used to check whether an attribute value matches any value within a value list LIKE : Used to check whether an attribute value matches a given string pattern; Allows you to use % and underscore(_) wildcard characters to make matches when the entire string is not known IS NULL: Used to check whether an attribute value is null NOT IN: Value must be compared against the list must evaluate to False when compared against every value in the list The first row returned by LIMIT will be determined by offset_value Groups the rows of data into collections based on sharing the same values in one or more attributes Filters the groups formed in the GROUP BY clause based on provided criteria

Order By DML Natural Join DML Join Using DML Join On DML Left Join DML Right Join DML Sorts the final query result rows in ascending or descending order based on the values of one or more atributes The join is based on all the columns in the two tables that have the same name and data types The query returns only the rows with matching values in the column indicated in the USING clause-and that column must exist in both tables. SELECT table1.column, table2.column FROM table1 JOIN table2 ON (table1.column_name = table2.column_name); The LEFT JOIN keyword returns all records from the left table (table1), and the matched records from the right table (table2). The result is NULL from the right side, if there is no match. Note: In some databases LEFT JOIN is called LEFT OUTER JOIN. ANSI standard defines three types of outer joins: Left, right, and full The right outer join returns not only the rows matching the join condition(that is rows with matching values in the common column)

Full Join DML Cross Join DML The full outer join returns not only the rows matching the join condition, but it also returns all of the rows with unmatched values in the table on either side A cross join performs a relational product (also known as Cartesian Product) of two tables.

Syntax SELECT DISTINCT column_name FROM table_name; INSERT ALL INTO mytable (column1, column2, column_n) VALUES (expr1, expr2, expr_n) INTO mytable (column1, column2, column_n) VALUES (expr1, expr2, expr_n) INTO mytable (column1, column2, column_n) VALUES (expr1, expr2, expr_n) UPDATE table-Name SET column-Name = Value [ , column-Name = Value ]* WHERE CURRENT OF SELECT * FROM table_name; SELECT column1, column2, ... FROM table_name; SELECT columnlist FROM tablelist; SELECT column_name(s) FROM table_name WHERE conditionlist ORDER BY columnlist [ASC|DESC]; SELECT column_name(s) FROM table_name WHERE conditionlist ORDER BY columnlist [ASC|DESC];

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version