Computer Programming || Gaming || Technology || ai

mysql queries


MySQL String Functions

Function Description
ASCII Returns the number code that represents the specific character
CHAR_LENGTH Returns the length of the specified string (in characters)
CHARACTER_LENGTH Returns the length of the specified string (in characters)
CONCAT Concatenates two or more expressions together
CONCAT_WS Concatenates two or more expressions together and adds a separator between them
FIELD Returns the position of a value in a list of values
FIND_IN_SET Returns the position of a string in a string list
FORMAT Formats a number as a format of “#,###.##”, rounding it to a certain number of decimal places
INSERT Inserts a substring into a string at a specified position for a certain number of characters
INSTR Returns the position of the first occurrence of a string in another string
LCASE Converts a string to lower-case
LEFT Extracts a substring from a string (starting from left)
LENGTH Returns the length of the specified string (in bytes)
LOCATE Returns the position of the first occurrence of a substring in a string
LOWER Converts a string to lower-case
LPAD Returns a string that is left-padded with a specified string to a certain length
LTRIM Removes leading spaces from a string
MID Extracts a substring from a string (starting at any position)
POSITION Returns the position of the first occurrence of a substring in a string
REPEAT Repeats a string a specified number of times
REPLACE Replaces all occurrences of a specified string
REVERSE Reverses a string and returns the result
RIGHT Extracts a substring from a string (starting from right)
RPAD Returns a string that is right-padded with a specified string to a certain length
RTRIM Removes trailing spaces from a string
SPACE Returns a string with a specified number of spaces
STRCMP Tests whether two strings are the same
SUBSTR Extracts a substring from a string (starting at any position)
SUBSTRING Extracts a substring from a string (starting at any position)
SUBSTRING_INDEX Returns the substring of string before number of occurrences of delimiter
TRIM Removes leading and trailing spaces from a string
UCASE Converts a string to upper-case
UPPER Converts a string to upper-case


MySQL Numeric Functions

Function Description
ABS Returns the absolute value of a number
ACOS Returns the arc cosine of a number
ASIN Returns the arc sine of a number
ATAN Returns the arc tangent of a number or the arc tangent of n and m
ATAN2 Returns the arc tangent of n and m
AVG Returns the average value of an expression
CEIL Returns the smallest integer value that is greater than or equal to a number
CEILING Returns the smallest integer value that is greater than or equal to a number
COS Returns the cosine of a number
COT Returns the cotangent of a number
COUNT Returns the number of records in a select query
DEGREES Converts a radian value into degrees
DIV Used for integer division
EXP Returns e raised to the power of number
FLOOR Returns the largest integer value that is less than or equal to a number
GREATEST Returns the greatest value in a list of expressions
LEAST Returns the smallest value in a list of expressions
LN Returns the natural logarithm of a number
LOG Returns the natural logarithm of a number or the logarithm of a number to a specified base
LOG10 Returns the base-10 logarithm of a number
LOG2 Returns the base-2 logarithm of a number
MAX Returns the maximum value of an expression
MIN Returns the minimum value of an expression
MOD Returns the remainder of n divided by m
PI Returns the value of PI displayed with 6 decimal places
POW Returns m raised to the nth power
POWER Returns m raised to the nth power
RADIANS Converts a value in degrees to radians
RAND Returns a random number or a random number within a range
ROUND Returns a number rounded to a certain number of decimal places
SIGN Returns a value indicating the sign of a number
SIN Returns the sine of a number
SQRT Returns the square root of a number
SUM Returns the summed value of an expression
TAN Returns the tangent of a number
TRUNCATE Returns a number truncated to a certain number of decimal places

MySQL Date Functions

Function Description
ADDDATE Returns a date after a certain time/date interval has been added
ADDTIME Returns a time/datetime after a certain time interval has been added
CURDATE Returns the current date
CURRENT_DATE Returns the current date
CURRENT_TIME Returns the current time
CURRENT_TIMESTAMP Returns the current date and time
CURTIME Returns the current time
DATE Extracts the date value from a date or datetime expression
DATEDIFF Returns the difference in days between two date values
DATE_ADD Returns a date after a certain time/date interval has been added
DATE_FORMAT Formats a date as specified by a format mask
DATE_SUB Returns a date after a certain time/date interval has been subtracted
DAY Returns the day portion of a date value
DAYNAME Returns the weekday name for a date
DAYOFMONTH Returns the day portion of a date value
DAYOFWEEK Returns the weekday index for a date value
DAYOFYEAR Returns the day of the year for a date value
EXTRACT Extracts parts from a date
FROM_DAYS Returns a date value from a numeric representation of the day
HOUR Returns the hour portion of a date value
LAST_DAY Returns the last day of the month for a given date
LOCALTIME Returns the current date and time
LOCALTIMESTAMP Returns the current date and time
MAKEDATE Returns the date for a certain year and day-of-year value
MAKETIME Returns the time for a certain hour, minute, second combination
MICROSECOND Returns the microsecond portion of a date value
MINUTE Returns the minute portion of a date value
MONTH Returns the month portion of a date value
MONTHNAME Returns the full month name for a date
NOW Returns the current date and time
PERIOD_ADD Takes a period and adds a specified number of months to it
PERIOD_DIFF Returns the difference in months between two periods
QUARTER Returns the quarter portion of a date value
SECOND Returns the second portion of a date value
SEC_TO_TIME Converts numeric seconds into a time value
STR_TO_DATE Takes a string and returns a date specified by a format mask
SUBDATE Returns a date after which a certain time/date interval has been subtracted
SUBTIME Returns a time/datetime value after a certain time interval has been subtracted
SYSDATE Returns the current date and time
TIME Extracts the time value from a time/datetime expression
TIME_FORMAT Formats a time as specified by a format mask
TIME_TO_SEC Converts a time value into numeric seconds
TIMEDIFF Returns the difference between two time/datetime values
TIMESTAMP Converts an expression to a datetime value and if specified adds an optional time interval to the value
TO_DAYS Converts a date into numeric days
WEEK Returns the week portion of a date value
WEEKDAY Returns the weekday index for a date value
WEEKOFYEAR Returns the week of the year for a date value
YEAR Returns the year portion of a date value
YEARWEEK Returns the year and week for a date value

MySQL Advanced Functions

Function Description
BIN Converts a decimal number to a binary number
BINARY Converts a value to a binary string
CASE Lets you evaluate conditions and return a value when the first condition is met
CAST Converts a value from one datatype to another datatype
COALESCE Returns the first non-null expression in a list
CONNECTION_ID Returns the unique connection ID for the current connection
CONV Converts a number from one number base to another
CONVERT Converts a value from one datatype to another, or one character set to another
CURRENT_USER Returns the user name and host name for the MySQL account used by the server to authenticate the current client
DATABASE Returns the name of the default database
IF Returns one value if a condition is TRUE, or another value if a condition is FALSE
IFNULL Lets you to return an alternate value if an expression is NULL
ISNULL Tests whether an expression is NULL
LAST_INSERT_ID Returns the first AUTO_INCREMENT value that was set by the most recent INSERT or UPDATE statement
NULLIF Compares two expressions
SESSION_USER Returns the user name and host name for the current MySQL user
SYSTEM_USER Returns the user name and host name for the current MySQL user
USER Returns the user name and host name for the current MySQL user
VERSION Returns the version of the MySQL database

 

SQL Server String Functions

Function Description
ASCII Returns the number code that represents the specific character
CHAR Returns the ASCII character based on the number code
CHARINDEX Returns the location of a substring in a string
CONCAT Concatenates two or more strings together
Concat with + Concatenates two or more strings together
DATALENGTH Returns the length of an expression (in bytes)
LEFT Extracts a substring from a string (starting from left)
LEN Returns the length of the specified string
LOWER Converts a string to lower-case
LTRIM Removes leading spaces from a string
NCHAR Returns the Unicode character based on the number code
PATINDEX Returns the location of a pattern in a string
REPLACE Replaces a sequence of characters in a string with another set of characters
RIGHT Extracts a substring from a string (starting from right)
RTRIM Removes trailing spaces from a string
SPACE Returns a string with a specified number of spaces
STR Returns a string representation of a number
STUFF Deletes a sequence of characters from a string and then inserts another sequence of characters into the string, starting at a specified position
SUBSTRING Extracts a substring from a string
UPPER Converts a string to upper-case


SQL Server Numeric Functions

Function Description
ABS Returns the absolute value of a number
AVG Returns the average value of an expression
CEILING Returns the smallest integer value that is greater than or equal to a number
COUNT Returns the count of an expression
FLOOR Returns the largest integer value that is equal to or less than a number
MAX Returns the maximum value of an expression
MIN Returns the minimum value of an expression
RAND Returns a random number or a random number within a range
ROUND Returns a number rounded to a certain number of decimal places
SIGN Returns a value indicating the sign of a number
SUM Returns the summed value of an expression

SQL Server Date Functions

Function Description
CURRENT_TIMESTAMP Returns the current date and time
DATEADD Returns a date after a certain time/date interval has been added
DATEDIFF Returns the difference between two date values, based on the interval specified
DATENAME Returns a specified part of a given date, as a string value
DATEPART Returns a specified part of a given date, as an integer value
DAY Returns the day of the month (from 1 to 31) for a given date
GETDATE Returns the current date and time
GETUTCDATE Returns the current UTC date and time
MONTH Returns the month (from 1 to 12) for a given date
YEAR Returns the year (as a four-digit number) for a given date

SQL Server Conversion Functions

Function Description
CAST Converts an expression from one data type to another
CONVERT Converts an expression from one data type to another

SQL Server Advanced Functions

Function Description
COALESCE Returns the first non-null expression in a list
CURRENT_USER Returns the name of the current user in the SQL Server database
ISDATE Returns 1 if the expression is a valid date, otherwise 0
ISNULL Lets you return an alternative value when an expression is NULL
ISNUMERIC Returns 1 if the expression is a valid number, otherwise 0
NULLIF Compares two expressions
SESSION_USER Returns the user name of the current session in the SQL Server database
SESSIONPROPERTY Returns the setting for a specified option of a session
SYSTEM_USER Returns the login name information for the current user in the SQL Server database
USER_NAME Returns the user name in the SQL Server database

 

MS Access String Functions

Function Description
Asc Returns the number code that represents the specific character
Chr Returns the character based on the number code
Concat with & Concatenates multiple strings together into a single string with the & operator
CurDir Returns the current path
Format Formats a string expression
InStr Returns the position of the first occurrence of a string in another string
InstrRev Returns the position of the first occurrence of a string in another string, starting from the end of the string
LCase Converts a string to lower-case
Left Extracts a substring from a string (starting from left)
Len Returns the length of the specified string
LTrim Removes leading spaces from a string
Mid Extracts a substring from a string (starting at any position)
Replace Replaces a sequence of characters in a string with another set of characters (a number of times)
Right Extracts a substring from a string (starting from right)
RTrim Removes trailing spaces from a string
Space Returns a string with a specified number of spaces
Split Splits a string into substrings (based on a delimiter) and returns the result as an array of substrings
Str Returns a string representation of a number
StrComp Returns an integer value representing the result of a string comparison
StrConv Returns a converted string
StrReverse Returns a string whose characters are in reverse order
Trim Removes leading and trailing spaces from a string
UCase Converts a string to upper-case


MS Access Numeric Functions

Function Description
Abs Returns the absolute value of a number
Atn Returns the arctangent of a number
Avg Returns the average value of an expression
Cos Returns the cosine of an angle
Count Returns the number of records in a select query
Exp Returns e raised to the nth power
Fix Returns the integer portion of a number
Format Takes a numeric expression and returns it as a formatted string
Int Returns the integer portion of a number
Max Returns the maximum value of an expression
Min Returns the minimum value of an expression
Randomize Allows you to change the seed value used by the random number generator for the Rnd() function
Rnd Generates a random number
Round Returns a number rounded to a certain number of decimal places
Sgn Returns the sign of a number
Sqr Returns the square root of a number
Sum Returns the summed value of an expression
Val Accepts a string as input and returns the numbers found in that string

MS Access Date Functions

Function Description
Date Returns the current system date
DateAdd Returns a date after a time/date interval has been added
DateDiff Returns the difference between two date values
DatePart Returns a specified part of a date
DateSerial Returns a date given a year, month, and day value
DateValue Converts a string to a date
Day Returns the day of the month (from 1 to 31) for a date
Format Takes a date expression and returns it as a formatted string
Hour Returns the hour (from 0 to 23) for a time value
Minute Returns the minute of the hour (from 0 to 59) for a time value
Month Returns the month (from 1 to 12) for a date
MonthName Returns the monthname given a number from 1 to 12
Now Returns the current system date and time
Second Returns the second of the minute (from 0 to 59) for a time value
Time Returns the current system time
TimeSerial Returns a time given an hour, minute, and second value
TimeValue Converts a string to a time
Weekday Returns a number (from 1 to 7) representing the day of the week for a date
WeekdayName Returns the weekday name given a number from 1 to 7
Year Returns the year (four digits) for a date

MS Access Information Functions

Function Description
CurrentUser Returns the name of the current Access user
Environ Returns the value of an operating system environment variable
IsDate Returns TRUE if the expression is a valid date, otherwise FALSE
IsNull Returns TRUE if the expression is a null value, otherwise FALSE
IsNumeric Returns TRUE if the expression is a valid number, otherwise FALSE

 

Oracle String Functions

Function Description
ASCII Returns the number code that represents the specified character
ASCIISTR Converts a string in any character set to an ASCII string using the database character set
CHR Returns the character based on the number code
COMPOSE Returns a Unicode string
CONCAT Allows you to concatenate two strings together
Concat with || Allows you to concatenate two or more strings together
CONVERT Converts a string from one character set to another
DECOMPOSE Accepts a string and returns a Unicode string
DUMP Returns a varchar2 value that includes the datatype code, the length in bytes, and the internal representation of the expression
INITCAP Sets the first character in each word to uppercase and the rest to lowercase
INSTR Returns the location of a substring in a string
INSTR2 Returns the location of a substring in a string, using UCS2 code points
INSTR4 Returns the location of a substring in a string, using UCS4 code points
INSTRB Returns the location of a substring in a string, using bytes instead of characters
INSTRC Returns the location of a substring in a string, using Unicode complete characters
LENGTH Returns the length of the specified string
LENGTH2 Returns the length of the specified string, using UCS2 code points
LENGTH4 Returns the length of the specified string, using UCS4 code points
LENGTHB Returns the length of the specified string, using bytes instead of characters
LENGTHC Returns the length of the specified string, using Unicode complete of characters
LOWER Converts all letters in the specified string to lowercase
LPAD Pads the left-side of a string with a specific set of characters
LTRIM Removes all specified characters from the left-hand side of a string
NCHR Returns the character based on the number code in the national character set
REGEXP_INSTR Returns the location of a regular expression pattern in a string
REGEXP_REPLACE Allows you to replace a sequence of characters in a string with another set of characters using regular expression pattern matching
REGEXP_SUBSTR Allows you to extract a substring from a string using regular expression pattern matching
REPLACE Replaces a sequence of characters in a string with another set of characters
RPAD Pads the right-side of a string with a specific set of characters
RTRIM Removes all specified characters from the right-hand side of a string
SOUNDEX Returns a phonetic representation (the way it sounds) of a string
SUBSTR Allows you to extract a substring from a string
TRANSLATE Replaces a sequence of characters in a string with another set of characters
TRIM Removes all specified characters either from the beginning or the end of a string
UPPER Converts all letters in the specified string to uppercase
VSIZE Returns the number of bytes in the internal representation of an expression


Oracle Numeric Functions

Function Description
ABS Returns the absolute value of a number
ACOS Returns the arc cosine of a number
ASIN Returns the arc sine of a number
ATAN Returns the arc tangent of a number
ATAN2 Returns the arc tangent of n and m
AVG Returns the average value of an expression
BITAND Returns an integer representing an AND operation on the bits of expr1 and expr2
CEIL Returns the smallest integer value that is greater than or equal to a number
COS Returns the cosine of a number
COSH Returns the hyperbolic cosine of a number
COUNT Returns the count of an expression
EXP Returns e raised to the power of number
FLOOR Returns the largest integer value that is equal to or less than a number
GREATEST Returns the greatest value in a list of expressions
LEAST Returns the smallest value in a list of expressions
LN Returns the natural logarithm of a number
LOG Returns the natural logarithm of a number to a specified base
MAX Returns the maximum value of an expression
MEDIAN Returns the median of an expression
MIN Returns the minimum value of an expression
MOD Returns the remainder of n divided by m
POWER Returns m raised to the nth power
REGEXP_COUNT Counts the number of times that a pattern occurs in a string
REMAINDER Returns the remainder of m divided by n
ROUND Returns a number rounded to a certain number of decimal places
ROWNUM Returns a number that represents the order that a row is
SIGN Returns a value indicating the sign of a number
SIN Returns the sine of a number
SQRT Returns the square root of a number
SUM Returns the summed value of an expression
TAN Returns the tangent of a number
TANH Returns the hyperbolic tangent of n
TRUNC Returns a number truncated to a certain number of decimal places

Oracle Date Functions

Function Description
ADD_MONTHS Returns a date with a specified number of months added
CURRENT_DATE Returns the current date in the time zone of the current SQL session as set by the ALTER SESSION command
CURRENT_TIMESTAMP Returns the current date and time in the time zone of the current SQL session as set by the ALTER SESSION command
DBTIMEZONE returns the database time zone as a time zone offset or a time zone region name
EXTRACT Extracts a value from a date or interval value
LAST_DAY Returns the last day of the month based on a date value
LOCALTIMESTAMP Returns the current date and time in the time zone of the current SQL session as set by the ALTER SESSION command
MONTHS_BETWEEN Returns the number of months between date1 and date2
NEW_TIME Converts a date from time zone1 to a date in time zone2
NEXT_DAY Returns the first weekday that is greater than a date
ROUND Returns a date rounded to a specific unit of measure
SESSIONTIMEZONE Returns the current session’s time zone as a time zone offset or a time zone region name
SYSDATE Returns the current system date and time on your local database
SYSTIMESTAMP Returns the current system date and time (including fractional seconds and time zone) on your local database
TRUNC Returns a date truncated to a specific unit of measure
TZ_OFFSET Returns the time zone offset of a value

 

SQL Arithmetic Operators

Operator Description Example
+ Add Try it
Subtract Try it
* Multiply Try it
/ Divide Try it
% Modulo Try it

SQL Bitwise Operators

Operator Description
& Bitwise AND
| Bitwise OR
^ Bitwise exclusive OR

SQL Comparison Operators

Operator Description Example
= Equal to Try it
> Greater than Try it
< Less than Try it
>= Greater than or equal to Try it
<= Less than or equal to Try it
<> Not equal to Try it


SQL Compound Operators

Operator Description
+= Add equals
-= Subtract equals
*= Multiply equals
/= Divide equals
%= Modulo equals
&= Bitwise AND equals
^-= Bitwise exclusive equals
|*= Bitwise OR equals

SQL Logical Operators

Operator Description Example
ALL TRUE if all of the subquery values meet the condition Try it
AND TRUE if all the conditions separated by AND is TRUE Try it
ANY TRUE if any of the subquery values meet the condition Try it
BETWEEN TRUE if the operand is within the range of comparisons Try it
EXISTS TRUE if the subquery returns one or more records Try it
IN TRUE if the operand is equal to one of a list of expressions Try it
LIKE TRUE if the operand matches a pattern Try it
NOT Displays a record if the condition(s) is NOT TRUE Try it
OR TRUE if any of the conditions separated by OR is TRUE Try it
SOME TRUE if any of the subquery values meet the condition

 

MySQL Data Types

In MySQL there are three main data types: text, number, and date.

Text data types:

Data type Description
CHAR(size) Holds a fixed length string (can contain letters, numbers, and special characters). The fixed size is specified in parenthesis. Can store up to 255 characters
VARCHAR(size) Holds a variable length string (can contain letters, numbers, and special characters). The maximum size is specified in parenthesis. Can store up to 255 characters. Note: If you put a greater value than 255 it will be converted to a TEXT type
TINYTEXT Holds a string with a maximum length of 255 characters
TEXT Holds a string with a maximum length of 65,535 characters
BLOB For BLOBs (Binary Large OBjects). Holds up to 65,535 bytes of data
MEDIUMTEXT Holds a string with a maximum length of 16,777,215 characters
MEDIUMBLOB For BLOBs (Binary Large OBjects). Holds up to 16,777,215 bytes of data
LONGTEXT Holds a string with a maximum length of 4,294,967,295 characters
LONGBLOB For BLOBs (Binary Large OBjects). Holds up to 4,294,967,295 bytes of data
ENUM(x,y,z,etc.) Let you enter a list of possible values. You can list up to 65535 values in an ENUM list. If a value is inserted that is not in the list, a blank value will be inserted.Note: The values are sorted in the order you enter them.

You enter the possible values in this format: ENUM(‘X’,’Y’,’Z’)

SET Similar to ENUM except that SET may contain up to 64 list items and can store more than one choice


Number data types:

Data type Description
TINYINT(size) -128 to 127 normal. 0 to 255 UNSIGNED*. The maximum number of digits may be specified in parenthesis
SMALLINT(size) -32768 to 32767 normal. 0 to 65535 UNSIGNED*. The maximum number of digits may be specified in parenthesis
MEDIUMINT(size) -8388608 to 8388607 normal. 0 to 16777215 UNSIGNED*. The maximum number of digits may be specified in parenthesis
INT(size) -2147483648 to 2147483647 normal. 0 to 4294967295 UNSIGNED*. The maximum number of digits may be specified in parenthesis
BIGINT(size) -9223372036854775808 to 9223372036854775807 normal. 0 to 18446744073709551615 UNSIGNED*. The maximum number of digits may be specified in parenthesis
FLOAT(size,d) A small number with a floating decimal point. The maximum number of digits may be specified in the size parameter. The maximum number of digits to the right of the decimal point is specified in the d parameter
DOUBLE(size,d) A large number with a floating decimal point. The maximum number of digits may be specified in the size parameter. The maximum number of digits to the right of the decimal point is specified in the d parameter
DECIMAL(size,d) A DOUBLE stored as a string , allowing for a fixed decimal point. The maximum number of digits may be specified in the size parameter. The maximum number of digits to the right of the decimal point is specified in the d parameter

*The integer types have an extra option called UNSIGNED. Normally, the integer goes from an negative to positive value. Adding the UNSIGNED attribute will move that range up so it starts at zero instead of a negative number.

Date data types:

Data type Description
DATE() A date. Format: YYYY-MM-DDNote: The supported range is from ‘1000-01-01’ to ‘9999-12-31’
DATETIME() *A date and time combination. Format: YYYY-MM-DD HH:MI:SSNote: The supported range is from ‘1000-01-01 00:00:00’ to ‘9999-12-31 23:59:59’
TIMESTAMP() *A timestamp. TIMESTAMP values are stored as the number of seconds since the Unix epoch (‘1970-01-01 00:00:00’ UTC). Format: YYYY-MM-DD HH:MI:SSNote: The supported range is from ‘1970-01-01 00:00:01’ UTC to ‘2038-01-09 03:14:07’ UTC
TIME() A time. Format: HH:MI:SSNote: The supported range is from ‘-838:59:59’ to ‘838:59:59’
YEAR() A year in two-digit or four-digit format.Note: Values allowed in four-digit format: 1901 to 2155. Values allowed in two-digit format: 70 to 69, representing years from 1970 to 2069

*Even if DATETIME and TIMESTAMP return the same format, they work very differently. In an INSERT or UPDATE query, the TIMESTAMP automatically set itself to the current date and time. TIMESTAMP also accepts various formats, like YYYYMMDDHHMISS, YYMMDDHHMISS, YYYYMMDD, or YYMMDD.


SQL Server Data Types

String data types:

Data type Description Max size Storage
char(n) Fixed width character string 8,000 characters Defined width
varchar(n) Variable width character string 8,000 characters 2 bytes + number of chars
varchar(max) Variable width character string 1,073,741,824 characters 2 bytes + number of chars
text Variable width character string 2GB of text data 4 bytes + number of chars
nchar Fixed width Unicode string 4,000 characters Defined width x 2
nvarchar Variable width Unicode string 4,000 characters
nvarchar(max) Variable width Unicode string 536,870,912 characters
ntext Variable width Unicode string 2GB of text data
binary(n) Fixed width binary string 8,000 bytes
varbinary Variable width binary string 8,000 bytes
varbinary(max) Variable width binary string 2GB
image Variable width binary string 2GB

Number data types:

Data type Description Storage
bit Integer that can be 0, 1, or NULL
tinyint Allows whole numbers from 0 to 255 1 byte
smallint Allows whole numbers between -32,768 and 32,767 2 bytes
int Allows whole numbers between -2,147,483,648 and 2,147,483,647 4 bytes
bigint Allows whole numbers between -9,223,372,036,854,775,808 and 9,223,372,036,854,775,807 8 bytes
decimal(p,s) Fixed precision and scale numbers.Allows numbers from -10^38 +1 to 10^38 –1.

The p parameter indicates the maximum total number of digits that can be stored (both to the left and to the right of the decimal point). p must be a value from 1 to 38. Default is 18.

The s parameter indicates the maximum number of digits stored to the right of the decimal point. s must be a value from 0 to p. Default value is 0

5-17 bytes
numeric(p,s) Fixed precision and scale numbers.Allows numbers from -10^38 +1 to 10^38 –1.

The p parameter indicates the maximum total number of digits that can be stored (both to the left and to the right of the decimal point). p must be a value from 1 to 38. Default is 18.

The s parameter indicates the maximum number of digits stored to the right of the decimal point. s must be a value from 0 to p. Default value is 0

5-17 bytes
smallmoney Monetary data from -214,748.3648 to 214,748.3647 4 bytes
money Monetary data from -922,337,203,685,477.5808 to 922,337,203,685,477.5807 8 bytes
float(n) Floating precision number data from -1.79E + 308 to 1.79E + 308.The n parameter indicates whether the field should hold 4 or 8 bytes. float(24) holds a 4-byte field and float(53) holds an 8-byte field. Default value of n is 53. 4 or 8 bytes
real Floating precision number data from -3.40E + 38 to 3.40E + 38 4 bytes

Date data types:

Data type Description Storage
datetime From January 1, 1753 to December 31, 9999 with an accuracy of 3.33 milliseconds 8 bytes
datetime2 From January 1, 0001 to December 31, 9999 with an accuracy of 100 nanoseconds 6-8 bytes
smalldatetime From January 1, 1900 to June 6, 2079 with an accuracy of 1 minute 4 bytes
date Store a date only. From January 1, 0001 to December 31, 9999 3 bytes
time Store a time only to an accuracy of 100 nanoseconds 3-5 bytes
datetimeoffset The same as datetime2 with the addition of a time zone offset 8-10 bytes
timestamp Stores a unique number that gets updated every time a row gets created or modified. The timestamp value is based upon an internal clock and does not correspond to real time. Each table may have only one timestamp variable

Other data types:

Data type Description
sql_variant Stores up to 8,000 bytes of data of various data types, except text, ntext, and timestamp
uniqueidentifier Stores a globally unique identifier (GUID)
xml Stores XML formatted data. Maximum 2GB
cursor Stores a reference to a cursor used for database operations
table Stores a result-set for later processing

Microsoft Access Data Types

Data type Description Storage
Text Use for text or combinations of text and numbers. 255 characters maximum
Memo Memo is used for larger amounts of text. Stores up to 65,536 characters. Note:You cannot sort a memo field. However, they are searchable
Byte Allows whole numbers from 0 to 255 1 byte
Integer Allows whole numbers between -32,768 and 32,767 2 bytes
Long Allows whole numbers between -2,147,483,648 and 2,147,483,647 4 bytes
Single Single precision floating-point. Will handle most decimals 4 bytes
Double Double precision floating-point. Will handle most decimals 8 bytes
Currency Use for currency. Holds up to 15 digits of whole dollars, plus 4 decimal places. Tip: You can choose which country’s currency to use 8 bytes
AutoNumber AutoNumber fields automatically give each record its own number, usually starting at 1 4 bytes
Date/Time Use for dates and times 8 bytes
Yes/No A logical field can be displayed as Yes/No, True/False, or On/Off. In code, use the constants True and False (equivalent to -1 and 0). Note: Null values are not allowed in Yes/No fields 1 bit
Ole Object Can store pictures, audio, video, or other BLOBs (Binary Large OBjects) up to 1GB
Hyperlink Contain links to other files, including web pages
Lookup Wizard Let you type a list of options, which can then be chosen from a drop-down list 4 bytes

 

SQL Statement Syntax
AND / OR SELECT column_name(s)
FROM table_name
WHERE condition
AND|OR condition
ALTER TABLE ALTER TABLE table_name
ADD column_name datatypeor

ALTER TABLE table_name
DROP COLUMN column_name

AS (alias) SELECT column_name AS column_alias
FROM table_nameor

SELECT column_name
FROM table_name  AS table_alias

BETWEEN SELECT column_name(s)
FROM table_name
WHERE column_name
BETWEEN value1 AND value2
CREATE DATABASE CREATE DATABASE database_name
CREATE TABLE CREATE TABLE table_name
(
column_name1 data_type,
column_name2 data_type,
column_name3 data_type,

)
CREATE INDEX CREATE INDEX index_name
ON table_name (column_name)or

CREATE UNIQUE INDEX index_name
ON table_name (column_name)

CREATE VIEW CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition
DELETE DELETE FROM table_name
WHERE some_column=some_valueor

DELETE FROM table_name
(Note: Deletes the entire table!!)

DELETE * FROM table_name
(Note: Deletes the entire table!!)

DROP DATABASE DROP DATABASE database_name
DROP INDEX DROP INDEX table_name.index_name (SQL Server)
DROP INDEX index_name ON table_name (MS Access)
DROP INDEX index_name (DB2/Oracle)
ALTER TABLE table_name
DROP INDEX index_name (MySQL)
DROP TABLE DROP TABLE table_name
EXISTS IF EXISTS (SELECT * FROM table_name WHERE id = ?)
BEGIN
–do what needs to be done if exists
END
ELSE
BEGIN
–do what needs to be done if not
END
GROUP BY SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name
HAVING SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name
HAVING aggregate_function(column_name) operator value
IN SELECT column_name(s)
FROM table_name
WHERE column_name
IN (value1,value2,..)
INSERT INTO INSERT INTO table_name
VALUES (value1, value2, value3,….)or

INSERT INTO table_name
(column1, column2, column3,…)
VALUES (value1, value2, value3,….)

INNER JOIN SELECT column_name(s)
FROM table_name1
INNER JOIN table_name2
ON table_name1.column_name=table_name2.column_name
LEFT JOIN SELECT column_name(s)
FROM table_name1
LEFT JOIN table_name2
ON table_name1.column_name=table_name2.column_name
RIGHT JOIN SELECT column_name(s)
FROM table_name1
RIGHT JOIN table_name2
ON table_name1.column_name=table_name2.column_name
FULL JOIN SELECT column_name(s)
FROM table_name1
FULL JOIN table_name2
ON table_name1.column_name=table_name2.column_name
LIKE SELECT column_name(s)
FROM table_name
WHERE column_name LIKE pattern
ORDER BY SELECT column_name(s)
FROM table_name
ORDER BY column_name [ASC|DESC]
SELECT SELECT column_name(s)
FROM table_name
SELECT * SELECT *
FROM table_name
SELECT DISTINCT SELECT DISTINCT column_name(s)
FROM table_name
SELECT INTO SELECT *
INTO new_table_name [IN externaldatabase]
FROM old_table_nameor

SELECT column_name(s)
INTO new_table_name [IN externaldatabase]
FROM old_table_name

SELECT TOP SELECT TOP number|percent column_name(s)
FROM table_name
TRUNCATE TABLE TRUNCATE TABLE table_name
UNION SELECT column_name(s) FROM table_name1
UNION
SELECT column_name(s) FROM table_name2
UNION ALL SELECT column_name(s) FROM table_name1
UNION ALL
SELECT column_name(s) FROM table_name2
UPDATE UPDATE table_name
SET column1=value, column2=value,…
WHERE some_column=some_value
WHERE SELECT column_name(s)
FROM table_name
WHERE column_name operator value
  1. SELECT * FROM Customers;
  2. SELECT column1, column2, …
    FROM table_name;
  3. SELECT CustomerName, City FROM Customers;
  4. SELECT DISTINCT column1, column2, …
    FROM table_name;
  5. SELECT Country FROM Customers;
  6. SELECT DISTINCT Country FROM Customers;
  7. SELECT COUNT(DISTINCT Country) FROM Customers;
  8. SELECT Count(*) AS DistinctCountries
    FROM (SELECT DISTINCT Country FROM Customers);
  9. SELECT column1, column2, …
    FROM table_name
    WHERE condition;
  10. SELECT * FROM Customers
    WHERE Country=’Mexico’;
  11. SELECT * FROM Customers
    WHERE CustomerID=1;
  12. SELECT column1, column2, …
    FROM table_name
    WHERE condition1 AND condition2 AND condition3 …;
  13. SELECT column1, column2, …
    FROM table_name
    WHERE condition1 OR condition2 OR condition3 …;
  14. SELECT column1, column2, …
    FROM table_name
    WHERE NOT condition;
  15. SELECT * FROM Customers
    WHERE Country=’Germany’ AND City=’Berlin’;
  16. SELECT * FROM Customers
    WHERE City=’Berlin’ OR City=’München’;
  17. SELECT * FROM Customers
    WHERE NOT Country=’Germany’;
  18. SELECT * FROM Customers
    WHERE Country=’Germany’ AND (City=’Berlin’ OR City=’München’);
  19. SELECT * FROM Customers
    WHERE NOT Country=’Germany’ AND NOT Country=’USA’;
  20. SELECT column1, column2, …
    FROM table_name
    ORDER BY column1, column2, … ASC|DESC;
  21. SELECT * FROM Customers
    ORDER BY Country;
  22. SELECT * FROM Customers
    ORDER BY Country DESC;
  23. SELECT * FROM Customers
    ORDER BY Country, CustomerName;
  24. SELECT * FROM Customers
    ORDER BY Country ASC, CustomerName DESC;
  25. INSERT INTO table_name (column1, column2, column3, …)
    VALUES (value1, value2, value3, …);
  26. INSERT INTO table_name
    VALUES (value1, value2, value3, …);
  27. INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country)
    VALUES (‘Cardinal’, ‘Tom B. Erichsen’, ‘Skagen 21’, ‘Stavanger’, ‘4006’, ‘Norway’);
  28. INSERT INTO Customers (CustomerName, City, Country)
    VALUES (‘Cardinal’, ‘Stavanger’, ‘Norway’);
  29. SELECT column_names
    FROM table_name
    WHERE column_name IS NULL;
  30. SELECT column_names
    FROM table_name
    WHERE column_name IS NOT NULL;
  31. SELECT LastName, FirstName, Address FROM Persons
    WHERE Address IS NULL;
  32. SELECT LastName, FirstName, Address FROM Persons
    WHERE Address IS NOT NULL;
  33. UPDATE table_name
    SET column1 = value1, column2 = value2, …
    WHERE condition;
  34. UPDATE Customers
    SET ContactName = ‘Alfred Schmidt’, City= ‘Frankfurt’
    WHERE CustomerID = 1;
  35. UPDATE Customers
    SET ContactName=’Juan’
    WHERE Country=’Mexico’;
  36. UPDATE Customers
    SET ContactName=’Juan’;
  37. DELETE FROM table_name
    WHERE condition;
  38. DELETE FROM Customers
    WHERE CustomerName=’Alfreds Futterkiste’;
  39. DELETE * FROM table_name;  /or same/  DELETE FROM table_name;
  40. SELECT TOP number|percent column_name(s)
    FROM table_name
    WHERE condition;
  41. SELECT column_name(s)
    FROM table_name
    WHERE condition
    LIMIT number;
  42. SELECT column_name(s)
    FROM table_name
    WHERE ROWNUM <= number;
  43. SELECT TOP 3 * FROM Customers;
  44. SELECT * FROM Customers
    LIMIT 3;
  45. SELECT * FROM Customers
    WHERE ROWNUM <= 3;
  46. SELECT TOP 50 PERCENT * FROM Customers;
  47. SELECT TOP 3 * FROM Customers
    WHERE Country=’Germany’;
  48. SELECT * FROM Customers
    WHERE Country=’Germany’
    LIMIT 3;
  49. SELECT * FROM Customers
    WHERE Country=’Germany’ AND ROWNUM <= 3;
  50. SELECT MIN(column_name)
    FROM table_name
    WHERE condition;
  51. SELECT MAX(column_name)
    FROM table_name
    WHERE condition;
  52. SELECT MIN(Price) AS SmallestPrice
    FROM Products;
  53. SELECT MAX(Price) AS LargestPrice
    FROM Products;
  54. SELECT COUNT(column_name)
    FROM table_name
    WHERE condition;
  55. SELECT AVG(column_name)
    FROM table_name
    WHERE condition;
  56. SELECT SUM(column_name)
    FROM table_name
    WHERE condition;
  57. SELECT COUNT(ProductID)
    FROM Products;
  58. SELECT AVG(Price)
    FROM Products;
  59. SELECT SUM(Quantity)
    FROM OrderDetails;
  60. SELECT column1, column2, …
    FROM table_name
    WHERE columnN LIKE pattern;
  61. SELECT * FROM Customers
    WHERE CustomerName LIKE ‘a%’;
  62. SELECT * FROM Customers
    WHERE CustomerName LIKE ‘%a’;
  63. SELECT * FROM Customers
    WHERE CustomerName LIKE ‘%or%’;
  64. SELECT * FROM Customers
    WHERE CustomerName LIKE ‘_r%’;
  65. SELECT * FROM Customers
    WHERE CustomerName LIKE ‘a_%_%’;
  66. SELECT * FROM Customers
    WHERE ContactName LIKE ‘a%o’;
  67. SELECT * FROM Customers
    WHERE CustomerName NOT LIKE ‘a%’;
  68. SELECT * FROM Customers
    WHERE City LIKE ‘ber%’;
  69. SELECT * FROM Customers
    WHERE City LIKE ‘%es%’;
  70. SELECT * FROM Customers
    WHERE City LIKE ‘_erlin’;
  71. SELECT * FROM Customers
    WHERE City LIKE ‘L_n_on’;
  72. SELECT * FROM Customers
    WHERE City LIKE ‘[bsp]%’;
  73. SELECT * FROM Customers
    WHERE City LIKE ‘[a-c]%’;
  74. SELECT * FROM Customers
    WHERE City LIKE ‘[!bsp]%’;
  75. SELECT * FROM Customers
    WHERE City NOT LIKE ‘[bsp]%’;
  76. SELECT column_name(s)
    FROM table_name
    WHERE column_name IN (value1, value2, …);
  77. SELECT column_name(s)
    FROM table_name
    WHERE column_name IN (SELECT STATEMENT);
  78. SELECT * FROM Customers
    WHERE Country IN (‘Germany’, ‘France’, ‘UK’);
  79. SELECT * FROM Customers
    WHERE Country NOT IN (‘Germany’, ‘France’, ‘UK’);
  80. SELECT * FROM Customers
    WHERE Country IN (SELECT Country FROM Suppliers);
  81. SELECT column_name(s)
    FROM table_name
    WHERE column_name BETWEEN value1 AND value2;
  82. SELECT * FROM Products
    WHERE Price BETWEEN 10 AND 20;
  83. SELECT * FROM Products
    WHERE Price NOT BETWEEN 10 AND 20;
  84. SELECT * FROM Products
    WHERE (Price BETWEEN 10 AND 20)
    AND NOT CategoryID IN (1,2,3);
  85. SELECT * FROM Products
    WHERE ProductName BETWEEN ‘Carnarvon Tigers’ AND ‘Mozzarella di Giovanni’
    ORDER BY ProductName;
  86. SELECT * FROM Products
    WHERE ProductName NOT BETWEEN ‘Carnarvon Tigers’ AND ‘Mozzarella di Giovanni’
    ORDER BY ProductName;
  87. SELECT * FROM Orders
    WHERE OrderDate BETWEEN #07/04/1996# AND #07/09/1996#;
  88. SELECT column_name AS alias_name
    FROM table_name;
  89. SELECT column_name(s)
    FROM table_name AS alias_name;
  90. SELECT CustomerID as ID, CustomerName AS Customer
    FROM Customers;
  91. SELECT CustomerName AS Customer, ContactName AS [Contact Person]
    FROM Customers;
  92. SELECT CustomerName, Address + ‘, ‘ + PostalCode + ‘ ‘ + City + ‘, ‘ + Country AS Address
    FROM Customers;
  93. SELECT CustomerName, CONCAT(Address,’, ‘,PostalCode,’, ‘,City,’, ‘,Country) AS Address
    FROM Customers;
  94. SELECT o.OrderID, o.OrderDate, c.CustomerName
    FROM Customers AS c, Orders AS o
    WHERE c.CustomerName=”Around the Horn” AND c.CustomerID=o.CustomerID;
  95. SELECT Orders.OrderID, Orders.OrderDate, Customers.CustomerName
    FROM Customers, Orders
    WHERE Customers.CustomerName=”Around the Horn” AND Customers.CustomerID=Orders.CustomerID;
  96. SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate
    FROM Orders
    INNER JOIN Customers ON Orders.CustomerID=Customers.CustomerID;
  97. SELECT column_name(s)
    FROM table1
    INNER JOIN table2 ON table1.column_name = table2.column_name;
  98. SELECT Orders.OrderID, Customers.CustomerName
    FROM Orders
    INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID;
  99. SELECT Orders.OrderID, Customers.CustomerName, Shippers.ShipperName
    FROM ((Orders
    INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID)
    INNER JOIN Shippers ON Orders.ShipperID = Shippers.ShipperID);
  100. SELECT column_name(s)
    FROM table1
    LEFT JOIN table2 ON table1.column_name = table2.column_name;
  101. SELECT Customers.CustomerName, Orders.OrderID
    FROM Customers
    LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID
    ORDER BY Customers.CustomerName;
  102. SELECT column_name(s)
    FROM table1
    RIGHT JOIN table2 ON table1.column_name = table2.column_name;
  103. SELECT Orders.OrderID, Employees.LastName, Employees.FirstName
    FROM Orders
    RIGHT JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID
    ORDER BY Orders.OrderID;
  104. SELECT column_name(s)
    FROM table1
    FULL OUTER JOIN table2 ON table1.column_name = table2.column_name;
  105. SELECT Customers.CustomerName, Orders.OrderID
    FROM Customers
    FULL OUTER JOIN Orders ON Customers.CustomerID=Orders.CustomerID
    ORDER BY Customers.CustomerName;
  106. SELECT column_name(s)
    FROM table1 T1, table1 T2
    WHERE condition;
  107. SELECT A.CustomerName AS CustomerName1, B.CustomerName AS CustomerName2, A.City
    FROM Customers A, Customers B
    WHERE A.CustomerID <> B.CustomerID
    AND A.City = B.City
    ORDER BY A.City;
  108. SELECT column_name(s) FROM table1
    UNION
    SELECT column_name(s) FROM table2;
  109. SELECT column_name(s) FROM table1
    UNION ALL
    SELECT column_name(s) FROM table2;
  110. SELECT City FROM Customers
    UNION
    SELECT City FROM Suppliers
    ORDER BY City;
  111. SELECT City FROM Customers
    UNION ALL
    SELECT City FROM Suppliers
    ORDER BY City;
  112. SELECT City, Country FROM Customers
    WHERE Country=’Germany’
    UNION
    SELECT City, Country FROM Suppliers
    WHERE Country=’Germany’
    ORDER BY City;
  113. SELECT City, Country FROM Customers
    WHERE Country=’Germany’
    UNION ALL
    SELECT City, Country FROM Suppliers
    WHERE Country=’Germany’
    ORDER BY City;
  114. SELECT ‘Customer’ As Type, ContactName, City, Country
    FROM Customers
    UNION
    SELECT ‘Supplier’, ContactName, City, Country
    FROM Suppliers;
  115. SELECT column_name(s)
    FROM table_name
    WHERE condition
    GROUP BY column_name(s)
    ORDER BY column_name(s);
  116. SELECT COUNT(CustomerID), Country
    FROM Customers
    GROUP BY Country;
  117. SELECT COUNT(CustomerID), Country
    FROM Customers
    GROUP BY Country
    ORDER BY COUNT(CustomerID) DESC;
  118. SELECT Shippers.ShipperName, COUNT(Orders.OrderID) AS NumberOfOrders FROM Orders
    LEFT JOIN Shippers ON Orders.ShipperID = Shippers.ShipperID
    GROUP BY ShipperName;
  119. SELECT column_name(s)
    FROM table_name
    WHERE condition
    GROUP BY column_name(s)
    HAVING condition
    ORDER BY column_name(s);
  120. SELECT COUNT(CustomerID), Country
    FROM Customers
    GROUP BY Country
    HAVING COUNT(CustomerID) > 5;
  121. SELECT COUNT(CustomerID), Country
    FROM Customers
    GROUP BY Country
    HAVING COUNT(CustomerID) > 5
    ORDER BY COUNT(CustomerID) DESC;
  122. SELECT Employees.LastName, COUNT(Orders.OrderID) AS NumberOfOrders
    FROM (Orders
    INNER JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID)
    GROUP BY LastName
    HAVING COUNT(Orders.OrderID) > 10;
  123. SELECT Employees.LastName, COUNT(Orders.OrderID) AS NumberOfOrders
    FROM Orders
    INNER JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID
    WHERE LastName = ‘Davolio’ OR LastName = ‘Fuller’
    GROUP BY LastName
    HAVING COUNT(Orders.OrderID) > 25;
  124. SELECT column_name(s)
    FROM table_name
    WHERE EXISTS
    (SELECT column_name FROM table_name WHERE condition);
  125. SELECT SupplierName
    FROM Suppliers
    WHERE EXISTS (SELECT ProductName FROM Products WHERE SupplierId = Suppliers.supplierId AND Price < 20);
  126. SELECT SupplierName
    FROM Suppliers
    WHERE EXISTS (SELECT ProductName FROM Products WHERE SupplierId = Suppliers.supplierId AND Price = 22);
  127. SELECT column_name(s)
    FROM table_name
    WHERE column_name operator ANY
    (SELECT column_name FROM table_name WHERE condition);
  128. SELECT column_name(s)
    FROM table_name
    WHERE column_name operator ALL
    (SELECT column_name FROM table_name WHERE condition);
  129. SELECT ProductName
    FROM Products
    WHERE ProductID = ANY (SELECT ProductID FROM OrderDetails WHERE Quantity = 10);
  130. SELECT ProductName
    FROM Products
    WHERE ProductID = ANY (SELECT ProductID FROM OrderDetails WHERE Quantity > 99);
  131. SELECT ProductName
    FROM Products
    WHERE ProductID = ALL (SELECT ProductID FROM OrderDetails WHERE Quantity = 10);
  132. SELECT *
    INTO newtable [IN externaldb]
    FROM oldtable
    WHERE condition;
  133. SELECT column1column2column3, …
    INTO newtable [IN externaldb]
    FROM oldtable
    WHERE condition;
  134. SELECT * INTO CustomersBackup2017
    FROM Customers;
  135. SELECT * INTO CustomersBackup2017 IN ‘Backup.mdb’
    FROM Customers;
  136. SELECT CustomerName, ContactName INTO CustomersBackup2017
    FROM Customers;
  137. SELECT * INTO CustomersGermany
    FROM Customers
    WHERE Country = ‘Germany’;
  138. SELECT Customers.CustomerName, Orders.OrderID
    INTO CustomersOrderBackup2017
    FROM Customers
    LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID;
  139. SELECT * INTO newtable
    FROM oldtable
    WHERE 1 = 0;
  140. INSERT INTO table2
    SELECT * FROM table1
    WHERE condition;
  141. INSERT INTO table2 (column1column2column3, …)
    SELECT column1column2column3, …
    FROM table1
    WHERE condition;
  142. INSERT INTO Customers (CustomerName, City, Country)
    SELECT SupplierName, City, Country FROM Suppliers;
  143. INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country)
    SELECT SupplierName, ContactName, Address, City, PostalCode, Country FROM Suppliers;
  144. INSERT INTO Customers (CustomerName, City, Country)
    SELECT SupplierName, City, Country FROM Suppliers
    WHERE Country=’Germany’;
  145. SELECT ProductName, UnitPrice * (UnitsInStock + UnitsOnOrder)
    FROM Products;
  146. SELECT ProductName, UnitPrice * (UnitsInStock + COALESCE(UnitsOnOrder, 0))
    FROM Products
  147. SELECT ProductName, UnitPrice * (UnitsInStock + IFNULL(UnitsOnOrder, 0))
    FROM Products
  148. SELECT ProductName, UnitPrice * (UnitsInStock + ISNULL(UnitsOnOrder, 0))
    FROM Products
  149. SELECT ProductName, UnitPrice * (UnitsInStock + IIF(IsNull(UnitsOnOrder), 0, UnitsOnOrder))
    FROM Products
  150. SELECT ProductName, UnitPrice * (UnitsInStock + NVL(UnitsOnOrder, 0))
    FROM Products
  151. CREATE PROCEDURE procedure_name
    AS
    sql_statement
    GO;
  152. EXEC procedure_name;
  153. CREATE PROCEDURE SelectAllCustomers
    AS
    SELECT * FROM Customers
    GO;
  154. EXEC SelectAllCustomers;
  155. CREATE PROCEDURE SelectAllCustomers @City nvarchar(30)
    AS
    SELECT * FROM Customers WHERE City = @City
    GO;
  156. EXEC SelectAllCustomers City = “London”;
  157. CREATE PROCEDURE SelectAllCustomers @City nvarchar(30), @PostalCode nvarchar(10)
    AS
    SELECT * FROM Customers WHERE City = @City AND PostalCode = @PostalCode
    GO;
  158. EXEC SelectAllCustomers City = “London”, PostalCode = “WA1 1DP”;
  159. –Select all:  comments
    SELECT * FROM Customers;
  160. SELECT * FROM Customers — WHERE City=’Berlin’;
  161. –SELECT * FROM Customers;
  162. SELECT * FROM Products;
  163. /*Select all the columns
    of all the records
    in the Customers table:*/
    SELECT * FROM Customers;
  164. /*SELECT * FROM Customers;
    SELECT * FROM Products;
    SELECT * FROM Orders;
    SELECT * FROM Categories;*/
    SELECT * FROM Suppliers;
  165. SELECT CustomerName, /*City,*/ Country FROM Customers;
  166. SELECT * FROM Customers WHERE (CustomerName LIKE ‘L%’
    OR CustomerName LIKE ‘R%’ /*OR CustomerName LIKE ‘S%’
    OR CustomerName LIKE ‘T%’*/ OR CustomerName LIKE ‘W%’)
    AND Country=’USA’
    ORDER BY CustomerName;
  167. CREATE DATABASE databasename;
  168. CREATE DATABASE testDB;
  169. DROP DATABASE databasename;
  170. DROP DATABASE testDB;
  171. CREATE TABLE table_name (
        column1 datatype,
        column2 datatype,
        column3 datatype,
    ….
    );
  172. CREATE TABLE Persons (
    PersonID int,
    LastName varchar(255),
    FirstName varchar(255),
    Address varchar(255),
    City varchar(255)
    );
  173. CREATE TABLE new_table_name AS
    SELECT column1, column2,…
    FROM existing_table_name
    WHERE ….;
  174. DROP TABLE table_name;
  175. DROP TABLE Shippers;
  176. TRUNCATE TABLE table_name;
  177. ALTER TABLE table_name
    ADD column_name datatype;
  178. ALTER TABLE table_name
    DROP COLUMN column_name;
  179. ALTER TABLE table_name
    ALTER COLUMN column_name datatype;
  180. ALTER TABLE table_name
    MODIFY COLUMN column_name datatype;
  181. ALTER TABLE table_name
    MODIFY column_name datatype;
  182. ALTER TABLE Persons
    ADD DateOfBirth date;
  183. ALTER TABLE Persons
    ALTER COLUMN DateOfBirth year;
  184. ALTER TABLE Persons
    DROP COLUMN DateOfBirth;
  185. CREATE TABLE table_name (
        column1 datatype constraint,
        column2 datatype constraint,
        column3 datatype constraint,
    ….
    );
  186. CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255) NOT NULL,
    Age int
    );
  187. CREATE TABLE Persons (
    ID int NOT NULL UNIQUE,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int
    );
  188. CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    UNIQUE (ID)
    );
  189. CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    CONSTRAINT UC_Person UNIQUE (ID,LastName)
    );
  190. ALTER TABLE Persons
    ADD UNIQUE (ID);
  191. ALTER TABLE Persons
    ADD CONSTRAINT UC_Person UNIQUE (ID,LastName);
  192. ALTER TABLE Persons
    DROP UNIQUE UC_Person;
  193. ALTER TABLE Persons
    DROP CONSTRAINT UC_Person;
  194. CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    PRIMARY KEY (ID)
    );
  195. CREATE TABLE Persons (
    ID int NOT NULL PRIMARY KEY,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int
    );
  196. CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    CONSTRAINT PK_Person PRIMARY KEY (ID,LastName)
    );
  197. ALTER TABLE Persons
    ADD PRIMARY KEY (ID);
  198. ALTER TABLE Persons
    ADD CONSTRAINT PK_Person PRIMARY KEY (ID,LastName);
  199. ALTER TABLE Persons
    DROP PRIMARY KEY;
  200. ALTER TABLE Persons
    DROP CONSTRAINT PK_Person;
  201. CREATE TABLE Orders (
    OrderID int NOT NULL,
    OrderNumber int NOT NULL,
    PersonID int,
    PRIMARY KEY (OrderID),
    FOREIGN KEY (PersonID) REFERENCES Persons(PersonID)
    );
  202. CREATE TABLE Orders (
    OrderID int NOT NULL PRIMARY KEY,
    OrderNumber int NOT NULL,
    PersonID int FOREIGN KEY REFERENCES Persons(PersonID)
    );
  203. CREATE TABLE Orders (
    OrderID int NOT NULL,
    OrderNumber int NOT NULL,
    PersonID int,
    PRIMARY KEY (OrderID),
    CONSTRAINT FK_PersonOrder FOREIGN KEY (PersonID)
    REFERENCES Persons(PersonID)
    );
  204. ALTER TABLE Orders
    ADD FOREIGN KEY (PersonID) REFERENCES Persons(PersonID);
  205. ALTER TABLE Orders
    ADD CONSTRAINT FK_PersonOrder
    FOREIGN KEY (PersonID) REFERENCES Persons(PersonID);
  206. ALTER TABLE Orders
    DROP FOREIGN KEY FK_PersonOrder;
  207. ALTER TABLE Orders
    DROP CONSTRAINT FK_PersonOrder;
  208. CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    CHECK (Age>=18)
    );
  209. CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int CHECK (Age>=18)
    );
  210. CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    City varchar(255),
    CONSTRAINT CHK_Person CHECK (Age>=18 AND City=’Sandnes’)
    );
  211. ALTER TABLE Persons
    ADD CHECK (Age>=18);
  212. ALTER TABLE Persons
    ADD CONSTRAINT CHK_PersonAge CHECK (Age>=18 AND City=’Sandnes’);
  213. ALTER TABLE Persons
    DROP CONSTRAINT CHK_PersonAge;
  214. ALTER TABLE Persons
    DROP CHECK CHK_PersonAge;
  215. CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    City varchar(255) DEFAULT ‘Sandnes’
    );
  216. CREATE TABLE Orders (
    ID int NOT NULL,
    OrderNumber int NOT NULL,
    OrderDate date DEFAULT GETDATE()
    );
  217. ALTER TABLE Persons;
  218. ALTER City SET DEFAULT ‘Sandnes’;
  219. ALTER TABLE Persons
    MODIFY City DEFAULT ‘Sandnes’;
  220. ALTER TABLE Persons
    ALTER City DROP DEFAULT;
  221. ALTER TABLE Persons
    ALTER COLUMN City DROP DEFAULT;
  222. CREATE INDEX index_name
    ON table_name (column1column2, …);
  223. CREATE UNIQUE INDEX index_name
    ON table_name (column1column2, …);
  224. CREATE INDEX idx_lastname
    ON Persons (LastName);
  225. CREATE INDEX idx_pname
    ON Persons (LastName, FirstName);
  226. DROP INDEX index_name ON table_name;
  227. DROP INDEX table_name.index_name;
  228. DROP INDEX index_name;
  229. ALTER TABLE table_name
    DROP INDEX index_name;
  230. CREATE TABLE Persons (
    ID int NOT NULL AUTO_INCREMENT,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    PRIMARY KEY (ID)
    );
  231. ALTER TABLE Persons AUTO_INCREMENT=100;
  232. INSERT INTO Persons (FirstName,LastName)
    VALUES (‘Lars’,’Monsen’);
  233. CREATE TABLE Persons (
    ID int IDENTITY(1,1) PRIMARY KEY,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int
    );
  234. INSERT INTO Persons (FirstName,LastName)
    VALUES (‘Lars’,’Monsen’);
  235. CREATE TABLE Persons (
    ID Integer PRIMARY KEY AUTOINCREMENT,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int
    );
  236. INSERT INTO Persons (FirstName,LastName)
    VALUES (‘Lars’,’Monsen’);
  237. CREATE SEQUENCE seq_person
    MINVALUE 1
    START WITH 1
    INCREMENT BY 1
    CACHE 10;
  238. INSERT INTO Persons (ID,FirstName,LastName)
    VALUES (seq_person.nextval,’Lars’,’Monsen’);
  239. SELECT * FROM Orders WHERE OrderDate=’2008-11-11′
  240. SELECT * FROM Orders WHERE OrderDate=’2008-11-11′
  241. CREATE VIEW view_name AS
    SELECT column1, column2, …
    FROM table_name
    WHERE condition;
  242. CREATE VIEW [Current Product List] AS
    SELECT ProductID, ProductName
    FROM Products
    WHERE Discontinued = No;
  243. SELECT * FROM [Current Product List];
  244. CREATE VIEW [Products Above Average Price] AS
    SELECT ProductName, UnitPrice
    FROM Products
    WHERE UnitPrice > (SELECT AVG(UnitPrice) FROM Products);
  245. SELECT * FROM [Products Above Average Price];
  246. CREATE VIEW [Category Sales For 1997] AS
    SELECT DISTINCT CategoryName, Sum(ProductSales) AS CategorySales
    FROM [Product Sales for 1997]
    GROUP BY CategoryName;
  247. SELECT * FROM [Category Sales For 1997];
  248. SELECT * FROM [Category Sales For 1997]
    WHERE CategoryName = ‘Beverages’;
  249. CREATE OR REPLACE VIEW view_name AS
    SELECT column1, column2, …
    FROM table_name
    WHERE condition;
  250. CREATE OR REPLACE VIEW [Current Product List] AS
    SELECT ProductID, ProductName, Category
    FROM Products
    WHERE Discontinued = No;
  251. DROP VIEW view_name;
  252. txtUserId = getRequestString(“UserId”);
    txtSQL = “SELECT * FROM Users WHERE UserId = ” + txtUserId;
  253. SELECT * FROM Users WHERE UserId = 105 OR 1=1;
  254. SELECT UserId, Name, Password FROM Users WHERE UserId = 105 or 1=1;
  255. uName = getRequestString(“username”);
    uPass = getRequestString(“userpassword”);sql = ‘SELECT * FROM Users WHERE Name =”‘ + uName + ‘” AND Pass =”‘ + uPass + ‘”‘
  256. SELECT * FROM Users WHERE Name =”John Doe” AND Pass =”myPass”
  257. SELECT * FROM Users WHERE Name =”” or “”=”” AND Pass =”” or “”=””
  258. SELECT * FROM Users; DROP TABLE Suppliers
  259. txtUserId = getRequestString(“UserId”);
    txtSQL = “SELECT * FROM Users WHERE UserId = ” + txtUserId;
  260. SELECT * FROM Users WHERE UserId = 105; DROP TABLE Suppliers;
  261. txtUserId = getRequestString(“UserId”);
    txtSQL = “SELECT * FROM Users WHERE UserId = @0”;
    db.Execute(txtSQL,txtUserId);
  262. txtNam = getRequestString(“CustomerName”);
    txtAdd = getRequestString(“Address”);
    txtCit = getRequestString(“City”);
    txtSQL = “INSERT INTO Customers (CustomerName,Address,City) Values(@0,@1,@2)”;
    db.Execute(txtSQL,txtNam,txtAdd,txtCit);
  263. txtUserId = getRequestString(“UserId”);
    sql = “SELECT * FROM Customers WHERE CustomerId = @0”;
    command = new SqlCommand(sql);
    command.Parameters.AddWithValue(“@0”,txtUserID);
    command.ExecuteReader();
  264. txtNam = getRequestString(“CustomerName”);
    txtAdd = getRequestString(“Address”);
    txtCit = getRequestString(“City”);
    txtSQL = “INSERT INTO Customers (CustomerName,Address,City) Values(@0,@1,@2)”;
    command = new SqlCommand(txtSQL);
    command.Parameters.AddWithValue(“@0”,txtNam);
    command.Parameters.AddWithValue(“@1”,txtAdd);
    command.Parameters.AddWithValue(“@2”,txtCit);
    command.ExecuteNonQuery();
  265. $stmt = $dbh->prepare(“INSERT INTO Customers (CustomerName,Address,City)
    VALUES (:nam, :add, :cit)”);
    $stmt->bindParam(‘:nam’, $txtNam);
    $stmt->bindParam(‘:add’, $txtAdd);
    $stmt->bindParam(‘:cit’, $txtCit);
    $stmt->execute();

 

 

Have any Question or Comment?

Leave a Reply

Your email address will not be published. Required fields are marked *




Calender

October 2020
M T W T F S S
 1234
567891011
12131415161718
19202122232425
262728293031