Term
However, to be compliant with the ANSI standard, they all support at least the major commands ____ in a similar manner. |
|
Definition
(such as SELECT, UPDATE, DELETE, INSERT, WHERE) |
|
|
Term
Note: Most of the SQL database programs also have |
|
Definition
their own proprietary extensions in addition to the SQL standard! |
|
|
Term
To build a web site that shows data from a database, you will need: |
|
Definition
An RDBMS database program (i.e. MS Access, SQL Server, MySQL) To use a server-side scripting language, like PHP or ASP To use SQL to get the data you want To use HTML / CSS to style the page |
|
|
Term
|
Definition
Relational Database Management System. |
|
|
Term
|
Definition
SQL, and for all modern database systems such as MS SQL Server, IBM DB2, Oracle, MySQL, and Microsoft Access. |
|
|
Term
|
Definition
The data in RDBMS is stored in database objects |
|
|
Term
A table is a collection of |
|
Definition
related data entries and it consists of columns and rows. |
|
|
Term
The following SQL statement selects all the records in the "Customers" table: |
|
Definition
|
|
Term
SQL keywords are NOT case sensitive: |
|
Definition
select is the same as SELECT |
|
|
Term
Semicolon is the standard way to |
|
Definition
separate each SQL statement in database systems that allow more than one SQL statement to be executed in the same call to the server. |
|
|
Term
|
Definition
- extracts data from a database |
|
|
Term
|
Definition
- updates data in a database |
|
|
Term
|
Definition
- deletes data from a database |
|
|
Term
|
Definition
- inserts new data into a database |
|
|
Term
|
Definition
|
|
Term
|
Definition
|
|
Term
|
Definition
|
|
Term
|
Definition
|
|
Term
|
Definition
|
|
Term
|
Definition
- creates an index (search key) |
|
|
Term
|
Definition
|
|
Term
Note: The WHERE clause is not only used in SELECT |
|
Definition
statement, it is also used in UPDATE, DELETE statement, etc.! |
|
|
Term
The WHERE clause can be combined with |
|
Definition
AND, OR, and NOT operators. |
|
|
Term
The AND operator displays a record if |
|
Definition
all the conditions separated by AND are TRUE. |
|
|
Term
The OR operator displays a record if |
|
Definition
any of the conditions separated by OR is TRUE. |
|
|
Term
The NOT operator displays a record if |
|
Definition
the condition(s) is NOT TRUE. |
|
|
Term
|
Definition
used to sort the result-set in ascending or descending order. |
|
|
Term
The ORDER BY keyword sorts the records in ascending order by default. To sort the records in descending order, use |
|
Definition
|
|
Term
The INSERT INTO statement is used to |
|
Definition
insert new records in a table. |
|
|
Term
The first way specifies both the |
|
Definition
column names and the values to be inserted: |
|
|
Term
A field with a NULL value is a |
|
Definition
|
|
Term
How to Test for NULL Values? |
|
Definition
It is not possible to test for NULL values with comparison operators, such as =, <, or <>. We will have to use the IS NULL and IS NOT NULL operators instead. |
|
|
Term
|
Definition
UPDATE Customers SET ContactName = 'Alfred Schmidt', City= 'Frankfurt' WHERE CustomerID = 1; |
|
|
Term
It is possible to delete all rows in a table without deleting the table. This means that the table structure, attributes, and indexes will be intact: |
|
Definition
|
|
Term
The following SQL statement deletes all rows in the "Customers" table, without deleting the table: |
|
Definition
|
|
Term
|
Definition
UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE condition; |
|
|
Term
|
Definition
SELECT column_names FROM table_name WHERE column_name IS NULL; |
|
|
Term
|
Definition
SELECT column_names FROM table_name WHERE column_name IS NOT NULL; |
|
|
Term
|
Definition
INSERT INTO table_name (column1, column2, column3, ...) VALUES (value1, value2, value3, ...); |
|
|
Term
If you are adding values for all the columns of the table, you do not need to specify the column names in the SQL query. However, make sure the order of the values is in the same order as the columns in the table. The INSERT INTO syntax would be as follows: |
|
Definition
INSERT INTO table_name VALUES (value1, value2, value3, ...); |
|
|
Term
|
Definition
SELECT column1, column2, ... FROM table_name ORDER BY column1, column2, ... ASC|DESC; |
|
|
Term
|
Definition
SELECT column1, column2, ... FROM table_name WHERE condition1 AND condition2 AND condition3 ...; |
|
|
Term
|
Definition
SELECT column1, column2, ... FROM table_name WHERE condition1 OR condition2 OR condition3 ...; |
|
|
Term
|
Definition
SELECT column1, column2, ... FROM table_name WHERE NOT condition; |
|
|
Term
|
Definition
SELECT column1, column2, ... FROM table_name WHERE condition; |
|
|
Term
|
Definition
SELECT DISTINCT column1, column2, ... FROM table_name; |
|
|
Term
|
Definition
SELECT column1, column2, ... FROM table_name; Here, column1, column2, ... are the field names of the table you want to select data from. If you want to select all the fields available in the table, use the following syntax:
SELECT * FROM table_name; |
|
|
Term
SQL Server / MS Access Syntax: |
|
Definition
SELECT TOP number|percent column_name(s) FROM table_name WHERE condition; |
|
|
Term
|
Definition
SELECT column_name(s) FROM table_name WHERE condition LIMIT number; |
|
|
Term
|
Definition
SELECT column_name(s) FROM table_name WHERE ROWNUM <= number; |
|
|
Term
|
Definition
SELECT MIN(column_name) FROM table_name WHERE condition; |
|
|
Term
|
Definition
SELECT MAX(column_name) FROM table_name WHERE condition; |
|
|
Term
COUNT() Syntax The COUNT() function returns the number of rows that matches a specified criterion. |
|
Definition
SELECT COUNT(column_name) FROM table_name WHERE condition; |
|
|
Term
AVG() Syntax The AVG() function returns the average value of a numeric column. |
|
Definition
SELECT AVG(column_name) FROM table_name WHERE condition; |
|
|
Term
SUM() Syntax The SUM() function returns the total sum of a numeric column. |
|
Definition
SELECT SUM(column_name) FROM table_name WHERE condition; |
|
|
Term
The LIKE operator is used in a WHERE clause to search for a specified pattern in a column. There are two wildcards often used in conjunction with the LIKE operator: |
|
Definition
% - The percent sign represents zero, one, or multiple characters _ - The underscore represents a single character |
|
|
Term
|
Definition
SELECT column1, column2, ... FROM table_name WHERE columnN LIKE pattern; |
|
|
Term
Here are some examples showing different LIKE operators with '%' and '_' wildcards: |
|
Definition
LIKE Operator Description WHERE CustomerName LIKE 'a%' Finds any values that start with "a" WHERE CustomerName LIKE '%a' Finds any values that end with "a" WHERE CustomerName LIKE '%or%' Finds any values that have "or" in any position WHERE CustomerName LIKE '_r%' Finds any values that have "r" in the second position WHERE CustomerName LIKE 'a_%' Finds any values that start with "a" and are at least 2 characters in length WHERE CustomerName LIKE 'a__%' Finds any values that start with "a" and are at least 3 characters in length WHERE ContactName LIKE 'a%o' Finds any values that start with "a" and ends with "o" |
|
|
Term
The following SQL statement selects all customers with a CustomerName starting with "a": |
|
Definition
SELECT * FROM Customers WHERE CustomerName LIKE 'a%'; |
|
|
Term
The following SQL statement selects all customers with a CustomerName ending with "a": |
|
Definition
SELECT * FROM Customers WHERE CustomerName LIKE '%a'; |
|
|
Term
The following SQL statement selects all customers with a CustomerName that have "or" in any position: |
|
Definition
SELECT * FROM Customers WHERE CustomerName LIKE '%or%'; |
|
|
Term
The following SQL statement selects all customers with a CustomerName that have "r" in the second position: |
|
Definition
SELECT * FROM Customers WHERE CustomerName LIKE '_r%'; |
|
|
Term
The following SQL statement selects all customers with a CustomerName that does NOT start with "a": |
|
Definition
SELECT * FROM Customers WHERE CustomerName NOT LIKE 'a%'; |
|
|
Term
Wildcard Characters in SQL Server |
|
Definition
Symbol Description Example % Represents zero or more characters bl% finds bl, black, blue, and blob _ Represents a single character h_t finds hot, hat, and hit [] Represents any single character within the brackets h[oa]t finds hot and hat, but not hit ^ Represents any character not in the brackets h[^oa]t finds hit, but not hot and hat - Represents a range of characters c[a-b]t finds cat and cbt |
|
|
Term
Wildcard characters are used with the SQL LIKE operator. |
|
Definition
The LIKE operator is used in a WHERE clause to search for a specified pattern in a column. |
|
|
Term
|
Definition
SELECT column_name(s) FROM table_name WHERE column_name IN (value1, value2, ...); |
|
|
Term
|
Definition
SELECT column_name(s) FROM table_name WHERE column_name IN (SELECT STATEMENT); |
|
|
Term
|
Definition
SELECT column_name(s) FROM table_name WHERE column_name BETWEEN value1 AND value2; |
|
|
Term
he BETWEEN operator selects values |
|
Definition
within a given range. The values can be numbers, text, or dates. The BETWEEN operator is inclusive: begin and end values are included. |
|
|
Term
|
Definition
SELECT * FROM Products WHERE Price NOT BETWEEN 10 AND 20; |
|
|
Term
|
Definition
SELECT * FROM Products WHERE Price BETWEEN 10 AND 20 AND CategoryID NOT IN (1,2,3); |
|
|
Term
|
Definition
SELECT * FROM Orders WHERE OrderDate BETWEEN #01/07/1996# AND #31/07/1996#;
SELECT * FROM Orders WHERE OrderDate BETWEEN '1996-07-01' AND '1996-07-31'; |
|
|
Term
|
Definition
SQL aliases are used to give a table, or a column in a table, a temporary name.
Aliases are often used to make column names more readable.
An alias only exists for the duration of the query. |
|
|
Term
|
Definition
SELECT column_name AS alias_name FROM table_name; |
|
|
Term
|
Definition
SELECT column_name(s) FROM table_name AS alias_name; |
|
|
Term
The following SQL statement creates an alias named "Address" that combine four columns (Address, PostalCode, City and Country): |
|
Definition
SELECT CustomerName, Address + ', ' + PostalCode + ' ' + City + ', ' + Country AS Address FROM Customers; |
|
|