Create
Learn
Share

C175 Ch.6

rename
verickle's version from 2017-08-21 22:34

SQL DATA DEFINITION COMMANDS

Question Answer
CREATE SCHEMA AUTHORIZATIONCreates a database schema
CREATE TABLECreates a new table in the user's database schema
NOT NULLEnsures that a column will not have null values
UNIQUEEnsures that a column will not have duplicate values
PRIMARY KEYDefines a primary key for a table
FOREIGN KEYDefines a foreign key for a table
DEFAULTDefines a default value for a column (when no value is given)
CHECKValidates data in an attribute
CREATE INDEXCreates an index for a table
CREATE VIEWCreates a dynamic subset of rows and columns from one or more tables
ALTER TABLEModifies a table's definition (adds, modifies, or deletes attributes or constraints)
CREATE TABLE ASCreates a new table based on a query in the user's database schema
DROP TABLEPermanently deletes a table (and its data)
DROP INDEXPermanently deletes an index
DROP VIEWPermanently deletes a view
memorize

SQL DATA MANIPULATION COMMANDS

Question Answer
INSERTInserts row(s) into a table
SELECTSelects attributes from rows in one or more tables or views
WHERERestricts the selection of rows based on a conditional expression
GROUP BYGroups the selected rows based on one or more attributes
HAVINGRestricts the selection of grouped rows based on a condition
ORDER BYOrders the selected rows based on one or more attributes
UPDATEModifies an attribute's values in one or more table's rows
DELETEDeletes one or more rows from a table
COMMITPermanently saves data changes
ROLLBACKRestores data to its original values
memorize

, <, >, <=, >=, <>, != : Used in conditional expressions

Question Answer
AND/OR/NOTUsed in conditional expressions
Special operatorsUsed in conditional expressions
BETWEENChecks whether an attribute value is within a range
IS NULLChecks whether an attribute value is null
LIKEChecks whether an attribute value matches a given string pattern
INChecks whether an attribute value matches any value within a value list
EXISTSChecks whether a subquery returns any rows
DISTINCTLimits values to unique values
Aggregate functionsUsed with SELECT to return mathematical summaries on columns
COUNTReturns the number of rows with non-null values for a given column
MINReturns the minimum attribute value found in a given column
MAXReturns the maximum attribute value found in a given column
SUMReturns the sum of all values for a given column
AVGReturns the average of all values for a given column
memorize