SQL Notes

Sorting | Filtering | Calculated Fields | String Manipulation | Numeric Manipulation | Date And Time Manipulation | Aggregate Functions | Grouping Data | Subqueries | Joins | Combined Quiries | Inserting Data | Updating & Deleting Data | Table Creations & Manipulation | Views
BLOB (& TEXT) Type Sizes:
Long BLOB4 GB
Medium BLOB16 MB
BLOB64 KB
Tiny BLOB255 B

INTEGER Types:
BIGINTSigned: -9223372036854775808 to 9223372036854775807. Unsigned: 0 to 18446744073709551615.
INTSigned: -2147483648 to 2147483647. Unsigned: 0 to 4294967295.
MEDIUMINTSigned: -8388608 to 8388607. Unsigned: 0 to 16777215.
SMALLINTSigned: -32768 to 32767. Unsigned: 0 to 65535.

Use signed or unsigned BIGINT for storing UNIX timestamps.

SORTING DATA [top]
Sorting Multiple Colums
SELECT prod_id, prod_price, prod_name FROM Products ORDER BY prod_price, prod_name;
Sorting Multiple Colums By Position
SELECT prod_id, prod_price, prod_name FROM Products ORDER BY 2, 3;
Sorting In Different Directions
SELECT prod_id, prod_price, prod_name FROM Products ORDER BY prod_price DESC, prod_name;

FILTERING DATA [top]
The WHERE Clause
SELECT prod_name, prod_price FROM Products WHERE prod_price = 3.49;
Range of Values
SELECT prod_name, prod_price FROM Products WHERE prod_price BETWEEN 5 AND 10;
Checking For No Value
SELECT prod_name FROM Products WHERE prod_price IS NULL
The AND Operator
SELECT prod_id, prod_price, prod_name FROM Products WHERE vend_id = 'DLL01' AND prod_price <= 4;
The OR Operator
SELECT prod_id, prod_price, prod_name FROM Products WHERE vend_id = 'DLL01' OR vend_id = 'BRS01';
Order of Evaluation
SELECT prod_name, prod_price FROM Products WHERE (vend_id = 'DLL01' OR vend_id = 'BRS01') AND prod_price >= 10;
The IN Operator
SELECT prod_name, prod_price FROM Products WHERE vend_id IN ('DLL01','BRS01') ORDER BY prod_name;
The NOT Operator
SELECT prod_name FROM Products WHERE NOT vend_id = 'DLL01' ORDER BY prod_name;
The LIKE Operator - %
SELECT prod_id, prod_name FROM Products WHERE prod_name LIKE '%bean bag%';
The LIKE Operator - _
SELECT prod_id, prod_name FROM Products WHERE prod_name LIKE '__ inch teddy bear';
The LIKE Operator - [ ] (containes range)
SELECT cust_contact FROM Customers WHERE cust_contact LIKE '[JM]%' ORDER BY cust_contact;
The LIKE Operator - [^ ] (does not containe range)
SELECT cust_contact FROM Customers WHERE cust_contact LIKE '[^JM]%' ORDER BY cust_contact;
The DISTINCT Clause (allows only unique values)
SELECT DISTINCT date from `trollz_skus` WHERE sku='sku0001' AND vendor='ImpTech';

CALCULATED FIELDS [top]
The + Operator
SELECT vend_city+', '+vend_state+' '+vend_zip FROM Vendors ORDER BY vend_name;
The || Operator
SELECT vend_city || ', ' || vend_state || ' ' || vend_zip FROM Vendors ORDER BY vend_name;
Using Aliases
SELECT RTRIM(vend_city) || ', ' || RTRIM(vend_state) || ' ' || RTRIM(vend_zip) AS address2 FROM Vendors ORDER BY vend_name;
Performing Mathmatical Calculations
SELECT prod_id, quantity, item_price, quantity*item_price AS expanded_price FROM OrderItems WHERE order_num = 20008;

STRING MANIPULATION FUNCTIONS [top]
ASCII(str)Returns the ASCII code value of the leftmost character of the string str. Returns 0 if str is the empty string. Returns NULL if str is NULL
ORD(str)If the leftmost character of the string str is a multi-byte character, returns the code of multi-byte character by returning the ASCII code value of the character in the format of: ((first byte ASCII code)*256+(second byte ASCII code))[*256+third byte ASCII code...]. If the leftmost character is not a multi-byte character, returns the same value as the like ASCII() function does
CONV(N,from_base,to_base)Converts numbers between different number bases. Returns a string representation of the number N, converted from base from_base to base to_base. Returns NULL if any argument is NULL. The argument N is interpreted as an integer, but may be specified as an integer or a string. The minimum base is 2 and the maximum base is 36. If to_base is a negative number, N is regarded as a signed number. Otherwise, N is treated as unsigned. CONV works with 64-bit precision
CHAR(N,...)CHAR() interprets the arguments as integers and returns a string consisting of the characters given by the ASCII code values of those integers. NULL values are skipped
CONCAT(str1,str2,...)Returns the string that results from concatenating the arguments. Returns NULL if any argument is NULL. May have more than 2 arguments. A numeric argument is converted to the equivalent string form
CONCAT_WS(separator, str1, str2,...)CONCAT_WS() stands for CONCAT With Separator and is a special form of CONCAT(). The first argument is the separator for the rest of the arguments. The separator can be a string as well as the rest of the arguments. If the separator is NULL, the result will be NULL. The function will skip any NULLs and empty strings, after the separator argument. The separator will be added between the strings to be concatenated
LENGTH(str) SQLReturns the length of the string str
LOCATE(substr,str[,pos])Returns the position of the first occurrence of substring substr in string str, starting at position pos. Returns 0 if substr is not in str
LPAD(str,len,padstr)Returns the string str, left-padded with the string padstr until str is len characters long. If str is longer than len' then it will be shortened to len characters.
RPAD(str,len,padstr)Returns the string str, right-padded with the string padstr until str is len characters long. If str is longer than len' then it will be shortened to len characters.
LEFT(str,len) SQLReturns the leftmost len characters from the string str
RIGHT(str,len) SQLReturns the rightmost len characters from the string str
SUBSTRING(str,pos[,len]) or SUBSTRING(str FROM pos FOR len) SQLReturns a substring len characters long from string str, starting at position pos. The variant form that uses FROM is ANSI SQL92 syntax
SUBSTRING_INDEX(str,delim,count)Returns the substring from string str before count occurrences of the delimiter delim. If count is positive, everything to the left of the final delimiter (counting from the left) is returned. If count is negative, everything to the right of the final delimiter (counting from the right) is returned
LTRIM(str) SQLTrims white space from left of str
RTRIM(str) SQLTrims white space from right of str
TRIM([[BOTH | LEADING | TRAILING] [remstr] FROM] str) SQLReturns the string str with all remstr prefixes and/or suffixes removed. If none of the specifiers BOTH, LEADING or TRAILING are given, BOTH is assumed. If remstr is not specified, spaces are removed.
SOUNDEX(str) SQLRetruns a strings SOUNDEX value of str
SPACE(N)Returns a string consisting of N space characters.
REPLACE(str,from_str,to_str)Returns the string str with all all occurrences of the string from_str replaced by the string to_str
REPEAT(str,count)Returns a string consisting of the string str repeated count times. If count <= 0, returns an empty string. Returns NULL if str or count are NULL
REVERSE(str)Returns the string str with the order of the characters reversed
INSERT(str,pos,len,newstr)Returns the string str, with the substring beginning at position pos and len characters long replaced by the string newstr
ELT(N,str1,str2,str3,...)Returns str1 if N = 1, str2 if N = 2, and so on. Returns NULL if N is less than 1 or greater than the number of arguments. ELT() is the complement of FIELD()
FIELD(str,str1,str2,str3,...)Returns the index of str in the str1, str2, str3, ... list. Returns 0 if str is not found. FIELD() is the complement of ELT()
FIND_IN_SET(str,strlist)Returns a value 1 to N if the string str is in the list strlist consisting of N substrings. A string list is a string composed of substrings separated by , characters. If the first argument is a constant string and the second is a column of type SET, the FIND_IN_SET() function is optimised to use bit arithmetic! Returns 0 if str is not in strlist or if strlist is the empty string. Returns NULL if either argument is NULL. This function will not work properly if the first argument contains a ,
MAKE_SET(bits,str1,str2,...)Returns a set (a string containing substrings separated by , characters) consisting of the strings that have the corresponding bit in bits set. str1 corresponds to bit 0, str2 to bit 1, etc. NULL strings in str1, str2, ... are not appended to the result
EXPORT_SET(bits,on,off,[separator,[number_of_bits]])Returns a string where for every bit set in 'bit', you get an 'on' string and for every reset bit you get an 'off' string. Each string is separated with 'separator' (default ',') and only 'number_of_bits' (default 64) of 'bits' is used
LOWER(str) SQLConverts string to lowercase
UPPER(str) SQLConverts string to uppercase
LOAD_FILE(file_name)Reads the file and returns the file contents as a string. The file must be on the server, you must specify the full pathname to the file, and you must have the file privilege. The file must be readable by all and be smaller than max_allowed_packet

NUMERIC MANIPULATION FUNCTIONS [top]
ABS(X)Returns the absolute value of X
SIGN(X) SQLReturns the sign of the argument as -1, 0, or 1, depending on whether X is negative, zero, or positive
MOD(N,M)Modulo (like the % operator in C). Returns the remainder of N divided by M
FLOOR(X) SQLReturns the largest integer value not greater than X
CEILING(X) SQLReturns the smallest integer value not less than X
ROUND(X[,D])Returns the argument X, rounded to a number with D decimals. If D is 0, the result will have no decimal point or fractional part
EXP(X) SQLReturns the value of e (the base of natural logarithms) raised to the power of X
LOG(X)Returns the natural logarithm of X
LOG10(X)Returns the base-10 logarithm of X
POW(X,Y) or POWER(X,Y)Returns the value of X raised to the power of Y
SQRT(X) SQLReturns the non-negative square root of X
PI() SQLReturns the value of PI. The default shown number of decimals is 5, but MySQL internally uses the full double precession for PI
COS(X) SQLReturns the cosine of X, where X is given in radians
SIN(X) SQLReturns the sine of X, where X is given in radians
TAN(X) SQLReturns the tangent of X, where X is given in radians
ACOS(X)Returns the arc cosine of X, that is, the value whose cosine is X. Returns NULL if X is not in the range -1 to 1
ASIN(X)Returns the arc sine of X, that is, the value whose sine is X. Returns NULL if X is not in the range -1 to 1
ATAN(X)Returns the arc tangent of X, that is, the value whose tangent is X
ATAN(Y,X) or ATAN2(Y,X)Returns the arc tangent of the two variables X and Y. It is similar to calculating the arc tangent of Y / X, except that the signs of both arguments are used to determine the quadrant of the result
COT(X)Returns the cotangent of X
RAND([N])Returns a random floating-point value in the range 0 to 1.0. If an integer argument N is specified, it is used as the seed value
LEAST(X,Y,...)With two or more arguments, returns the smallest (minimum-valued) argument. The arguments are compared using the following rules:
  • If the return value is used in an INTEGER context, or all arguments are integer-valued, they are compared as integers.
  • If the return value is used in a REAL context, or all arguments are real-valued, they are compared as reals.
  • If any argument is a case-sensitive string, the arguments are compared as case-sensitive strings.
  • In other cases, the arguments are compared as case-insensitive strings
GREATEST(X,Y,...)Returns the largest (maximum-valued) argument. The arguments are compared using the same rules as for LEAST
DEGREES(X)Returns the argument X, converted from radians to degrees
RADIANS(X)Returns the argument X, converted from degrees to radians
TRUNCATE(X,D)Returns the number X, truncated to D decimals. If D is 0, the result will have no decimal point or fractional part:

DATE & TIME MANIPULATION FUNCTIONS (MySQL Specific?) [top]
DAYOFWEEK(date)Returns the weekday index
WEEKDAY(date)Returns the weekday index for date (0 = Monday, 1 = Tuesday, ... 6 = Sunday)
DAYOFMONTH(date)Returns the day of the month for date, in the range 1 to 31
DAYOFYEAR(date)Returns the day of the year for date, in the range 1 to 366
MONTH(date)Returns the month for date, in the range 1 to 12
DAYNAME(date)Returns the name of the weekday for date
MONTHNAME(date)Returns the name of the month for date
QUARTER(date)Returns the quarter of the year for date, in the range 1 to 4
WEEK(date,first)With a single argument, returns the week for date, in the range 0 to 53. With the two-argument form you can specify whether the week starts on Sunday or Monday. Sunday = 0. Monday = 1
YEAR(date)Returns the year for date, in the range 1000 to 9999
YEARWEEK(date,first)Returns year and week for a date. The second arguments works exactly like in WEEK(). Note that the year may be different from the year in the date argument for the first and the last week of the year
HOUR(time)Returns the hour for time, in the range 0 to 23
MINUTE(time)Returns the minute for time, in the range 0 to 59
SECOND(time)Returns the second for time, in the range 0 to 59
PERIOD_ADD(P,N)Adds N months to period P (in the format YYMM or YYYYMM). Returns a value in the format YYYYMM
PERIOD_DIFF(P1,P2)Returns the number of months between periods P1 and P2. P1 and P2 should be in the format YYMM or YYYYMM
DATE_ADD(date,INTERVAL expr type) and DATE_SUB(date,INTERVAL expr type) date is a DATETIME or DATE value specifying the starting date. expr is an expression specifying the interval value to be added or subtracted from the starting date. expr is a string; it may start with a - for negative intervals. type is a keyword indicating how the expression should be interpreted. 
type value	Expected expr format
SECOND		SECONDS
MINUTE		MINUTES
HOUR		HOURS
DAY		DAYS
MONTH		MONTHS
YEAR		YEARS
MINUTE_SECOND	"MINUTES:SECONDS"
HOUR_MINUTE	"HOURS:MINUTES"
DAY_HOUR	"DAYS HOURS"
YEAR_MONTH	"YEARS-MONTHS"
HOUR_SECOND	"HOURS:MINUTES:SECONDS"
DAY_MINUTE	"DAYS HOURS:MINUTES"
DAY_SECOND	"DAYS HOURS:MINUTES:SECONDS"
EXTRACT(type FROM date)The EXTRACT() function uses the same kinds of interval type specifiers as DATE_ADD() or DATE_SUB(), but extracts parts from the date rather than performing date arithmetic.
TO_DAYS(date)Given a date date, returns a daynumber (the number of days since year 0)
FROM_DAYS(N)Given a daynumber N, returns a DATE value
DATE_FORMAT(date,format)Formats the date value according to the format string. The following specifiers may be used in the format string: 
Specifier	Description
%M		Month name (January..December)
%W		Weekday name (Sunday..Saturday)
%D		Day of the month with English suffix (1st, 2nd, 3rd, etc.)
%Y		Year, numeric, 4 digits
%y		Year, numeric, 2 digits
%X		Year for the week where Sunday is the first day of the week, numeric, 4 digits, used with '%V'
%x		Year for the week, where Monday is the first day of the week, numeric, 4 digits, used with '%v'
%a		Abbreviated weekday name (Sun..Sat)
%d		Day of the month, numeric (00..31)
%e		Day of the month, numeric (0..31)
%m		Month, numeric (01..12)
%c		Month, numeric (1..12)
%b		Abbreviated month name (Jan..Dec)
%j		Day of year (001..366)
%H		Hour (00..23)
%k		Hour (0..23)
%h		Hour (01..12)
%I		Hour (01..12)
%l		Hour (1..12)
%i		Minutes, numeric (00..59)
%r		Time, 12-hour (hh:mm:ss [AP]M)
%T		Time, 24-hour (hh:mm:ss)
%S		Seconds (00..59)
%s		Seconds (00..59)
%p		AM or PM
%w		Day of the week (0=Sunday..6=Saturday)
%U		Week (0..53), where Sunday is the first day of the week
%u		Week (0..53), where Monday is the first day of the week
%V		Week (1..53), where Sunday is the first day of the week. Used with '%X'
%v		Week (1..53), where Monday is the first day of the week. Used with '%x'
%%		A literal %.
TIME_FORMAT(time,format)This is used like the DATE_FORMAT() function above, but the format string may contain only those format specifiers that handle hours, minutes, and seconds. Other specifiers produce a NULL value or 0.
CURRENT_DATE()Returns today's date as a value in 'YYYY-MM-DD' or YYYYMMDD format, depending on whether the function is used in a string or numeric context.
CURRENT_TIME()Returns the current time as a value in 'HH:MM:SS' or HHMMSS format, depending on whether the function is used in a string or numeric context.
NOW() or SYSDATE() or CURRENT_TIMESTAMP()Returns the current date and time as a value in 'YYYY-MM-DD HH:MM:SS' or YYYYMMDDHHMMSS format, depending on whether the function is used in a string or numeric context
UNIX_TIMESTAMP(date)If called with no argument, returns a Unix timestamp (seconds since '1970-01-01 00:00:00' GMT) as an unsigned integer. If UNIX_TIMESTAMP() is called with a date argument, it returns the value of the argument as seconds since '1970-01-01 00:00:00' GMT. date may be a DATE string, a DATETIME string, a TIMESTAMP, or a number in the format YYMMDD or YYYYMMDD in local time
FROM_UNIXTIME(unix_timestamp)Returns a representation of the unix_timestamp argument as a value in 'YYYY-MM-DD HH:MM:SS' or YYYYMMDDHHMMSS format, depending on whether the function is used in a string or numeric context
SEC_TO_TIME(seconds)Returns the seconds argument, converted to hours, minutes, and seconds, as a value in 'HH:MM:SS' or HHMMSS format, depending on whether the function is used in a string or numeric context
TIME_TO_SEC(time)Returns the time argument, converted to seconds

AGGREGATE FUNCTIONS [top]
The AVG() Function
SELECT AVG(prod_price) AS avg_price FROM Products [WHERE vend_id = 'DLL01'];
The COUNT() Function
SELECT COUNT(*) AS num_cust FROM Customers;
SELECT COUNT(cust_email) AS num_cust FROM Customers;
The MAX() Function
SELECT MAX(prod_price) AS max_price FROM Products;
The MIN() Function
SELECT MIN(prod_price) AS min_price FROM Products;
The SUM() Function
SELECT SUM(quantity) AS item_orders FROM OrderItems WHERE order_num=20005;
SELECT SUM(item_price*quantity) AS total_price FROM OrderItems WHERE order_num=20005;
Being DISTINCT
SELECT AVG(DISTINCT prod_price) AS avg_price FROM Products WHERE vend_id = 'DLL01';

GROUPING DATA [top]
GROUP BY
SELECT vend_id, COUNT(*) AS num_prods FROM Products GROUP BY vend_id;
HAVING
SELECT cust_id, COUNT(*) AS orders FROM Orders GROUP BY cost_id HAVING COUNT(*) >=2;
WHERE & HAVING
SELECT vend_id, COUNT(*) AS num_prods FROM Products WHERE prod_price >= 4 GROUP BY vend_id HAVING COUNT(*) >= 2;
GROUP BY & ORDER BY
SELECT order_num, COUNT(*) AS items FROM OrderItems GROUP BY order_num HAVING COUNT(*) >= 3 ORDER BY items, order_num;

SUBQUERIES [top]
Filtering by Subquery
SELECT cust_id FROM Orders WHERE order_num IN (SELECT order_num FROM OrderItems WHERE prod_id = 'RGAN01');
Using Subqueries As Calculated Fields
SELECT cust_name, cust_state, (
SELECT COUNT(*) FROM Orders WHERE cust_id = cust_id) AS orders FROM Customers ORDER BY cust_name;

JOINS [top]
WHERE Clause Join (Inner Join)
SELECT vend_name, prod_name, prod_price FROM Vendors, Products;
INNER JOIN
SELECT vend_name, prod_name, prod_price
FROM Vendors INNER JOIN Products ON vendors.vend_id = products.vend_id;
Joining Multiple Tables
SELECT prod_name, vend_name, prod_price, quantity
FROM OrderItems, Products, Vedors
WHERE Products.vend_id = Vendors.vend_id
AND OrderItems.prod_id = Products.prod_id
AND order_num = 20007;
Using Table Aliases
SELECT cust_name, cust_contact
FROM Customers AS C, Orders AS O, OrderItems AS OI
WHERE C.cust_id = O.cust_id
AND OI.order_num - O.order_num
AND prod_id = 'RGAN01';
Self Joins
SELECT c1.cust_id, c1.cust_name, c1.cust_contact
FROM Customers AS c1, Customers AS c2
WHERE c1.cust_name = c2.cust_name
AND c2.cust_contact = 'Jim Jones';
Natural Joins
SELECT C.*, O.order_num, O.order_date, OI.prod_id, OI._quantity, OI.item_price
FROM Customers AS C, Orders AS O, OrderItems AS OI
WHERE C.cust_id = O.cust_id
AND OI.order_num = O.order_num
AND prod_id = 'RGAN01';
Outer Join (Left)
SELECT Customers.cust_id, Orders.order_num
FROM Customers, Orders
WHERE Customers.cust_id *= Orders.cust_id;
SELECT Customers.cust_id, Orders.order_num
FROM Customers LEFT OUTER JOIN Orders
WHERE Customers.cust_id = Orders.cust_id;
Full Outer Join
SELECT Customers.cust_id, Orders.order_num
FROM Orders FULL OUTER JOIN Customers
ON Orders.cust_id = Customers.cust_id;

COMBINED QUIRIES [top]
Using UNION
SELECT cust_name, cust_contact, cust_email FROM Customers
WHERE cust_state IN ('IL','IN','MI')
UNION
SELECT cust_name, cust_contact, cust_email FROM Customers
WHERE cust_name = 'Fun4All';
Including Duplicate Rows
SELECT cust_name, cust_contact, cust_email FROM Customers
WHERE cust_state IN ('IL','IN','MI')
UNION ALL
SELECT cust_name, cust_contact, cust_email FROM Customers
WHERE cust_name = 'Fun4All';
Sorting Combined Quiry Results
SELECT cust_name, cust_contact, cust_email FROM Customers
WHERE cust_state IN ('IL','IN','MI')
UNION
SELECT cust_name, cust_contact, cust_email FROM Customers
WHERE cust_name = 'Fun4All'
ORDER BY cust_name, cust_contact;

INSERTING DATA [top]
Inserting Complete Rows
INSERT INTO Customers(
	cust_id,
	cust_name,
	cust_address,
	cust_city,
	cust_state,
	cust_zip,
	cust_country,
	cust_contact,
	cust_email)
VALUES('1000000006',
	'Toy Land',
	'123 Any Street',
	'New York',
	'NY',
	'11111',
	'USA',
	NULL,
	NULL);
Inserting Partial Rows
INSERT INTO Customers(
	cust_id,
	cust_name,
	cust_address,
	cust_city,
	cust_state,
	cust_zip,
	cust_country)
VALUES('1000000006',
	'Toy Land',
	'123 Any Street',
	'New York',
	'NY',
	'11111',
	'USA');
Inserting Retrieved Data
INSERT INTO Customers(
	cust_id,
	cust_contact,
	cust_email,
	cust_name,
	cust_address,
	cust_city,
	cust_state,
	cust_zip,
	cust_country)
SELECT cust_id,
	cust_contact,
	cust_email,
	cust_name,
	cust_address,
	cust_city,
	cust_state,
	cust_zip,
	cust_country
FROM CustNew;
Table Copying
SELECT * INTO CustCopy FROM Customers;

UPDATING & DELETING DATA [top]
Updating Data
UPDATE Customers
SET cust_contact = 'Sam Roberts',
cust_email = 'sam@toyland.com'
WHERE cust_id = '1000000006';
Deleting Column Values
UPDATE Customers SET cust_email = NULL WHERE cust_id = '1000000005';
Deleting A Row
DELETE FROM Customers WHERE cust_id = '1000000006';
Deleting All Rows
TRUNCATE TABLE Customers

TABLE CREATIONS & MANIPULATION [top]
Creating Table with Default Values
CREATE TABLE OrderItems
(
	order_numb	INTEGER		NOT NULL,
	order_item	INTEGER		NOT NULL,
	prod_id		CHAR(10)	NOT NULL,
	quantity	INTEGER		NOT NULL	DEFAULT 1,
	item_price	DECIMAL(8,2)	NOT NULL
);
Adding A Column
ALTER TABLE Vendors ADD vend_phone CHAR(20);
Droping A Column
ALTER TABLE Vendors DROP COLUMN vend_phone
Droping A Table
DROP TABLE CustCopy;

VIEWS [top]
Creating A "Join" View
CREATE VIEW ProductCustomers AS
SELECT cust_name, cust_contact, prod_id
FROM Customers, Orders, OrderItems
WHERE Customers.cust_id = Orders.cust_id
    AND OrderItems.order_num = Orders.order_num
Using Views to Reformat Retrieved Data
CREATE VIEW CustomerMailingLables AS
SELECT cust_name,
	cust_address AS cust_address1,
	RTRIM(cust_city) + ', ' + RTRIM(cust_state) + ' ' + RTRIM(cust_zip) AS cust_address2,
	cust_country AS cust_address3
FROM Customers
SELECT * FROM CustomerMailingLables;
Views with Filters
CREATE VIEW CustomerMailingLables AS
SELECT cust_name,
	cust_address AS cust_address1,
	RTRIM(cust_city) + ', ' + RTRIM(cust_state) + ' ' + RTRIM(cust_zip) AS cust_address2,
	cust_country AS cust_address3
FROM Customers
WHERE NOT cust_address IS NULL
	AND NOT cust_city IS NULL
	AND NOT cust_state IS NULL
	AND NOT cust_zip IS NULL
	AND NOT cust_country IS NULL;
Views with Calculated Fields
CREATE VIEW OrderItemsExpanded AS
SELECT	order_num,
	prod_id,
	quantity,
	item_price,
	quantity*item_price AS expanded_price
FROM OrderItems;
SELECT * FROM OrderItemsExpanded WHERE order_num = 20008;

STORED PROCEDURES [top]

CAPTION [top]

CAPTION [top]