SQL
EXEC procedure_name;
Execute a Stored Procedure
ALTER TABLE Persons DROP CHECK CHK_PersonAge;
MySQL:
SELECT * INTO newtable FROM oldtable WHERE 1 = 0;
SELECT INTO can also be used to create a new, empty table using the schema of another. Just add a WHERE clause that causes the query to return no data:
SELECT CustomerName, CONCAT(Address,', ',PostalCode,', ',City,', ',Country) AS Address FROM Customers;
To get the SQL statement above to work in MySQL use the following:
ALTER TABLE table_name DROP COLUMN column_name;
ALTER TABLE - DROP COLUMN To delete a column in a table, use the following syntax (notice that some database systems don't allow deleting a column):
Auto-increment allows a unique number to be generated automatically when a new record is inserted into a table. Often this is the primary key field that we would like to be created automatically every time a new record is inserted.
AUTO INCREMENT Field
BACKUP DATABASE testDB TO DISK = 'D:\backups\testDB.bak'; Tip: Always back up the database to a different drive than the actual database. If you get a disk crash, you will not lose your backup file along with the database.
BACKUP DATABASE Example The following SQL statement creates a full back up of the existing database "testDB" to the D disk:
BACKUP DATABASE testDB TO DISK = 'D:\backups\testDB.bak' WITH DIFFERENTIAL; Tip: A differential back up reduces the back up time (since only the changes are backed up).
BACKUP WITH DIFFERENTIAL Example The following SQL statement creates a differential back up of the database "testDB":
CREATE INDEX index_name ON table_name (column1, column2, ...);
CREATE INDEX Syntax Creates an index on a table. Duplicate values are allowed
CREATE TABLE new_table_name AS SELECT column1, column2,... FROM existing_table_name WHERE ....;
Create Table Using Another Table A copy of an existing table can also be created using CREATE TABLE. The new table gets the same column definitions. All columns or specific columns can be selected. If you create a new table using an existing table, the new table will be filled with the existing values from the old table. Syntax
DROP DATABASE testDB; Tip: Make sure you have admin privilege before dropping any database. Once a database is dropped, you can check it in the list of databases with the following SQL command: SHOW DATABASES;
DROP DATABASE Example The following SQL statement drops the existing database "testDB":
ALTER TABLE Orders DROP FOREIGN KEY FK_PersonOrder;
DROP a FOREIGN KEY Constraint To drop a FOREIGN KEY constraint, use the following SQL: MySQL:
$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();
INSERT INTO STATEMENT IN PHP:
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);
JOIN Three Tables The following SQL statement selects all orders with customer and shipper information:
/*Select all the columns of all the records in the Customers table:*/ SELECT * FROM Customers;
Multi-line Comments Multi-line comments start with /* and end with */. Any text between /* and */ will be ignored. The following example uses a multi-line comment as an explanation:
ALTER TABLE table_name DROP INDEX index_name;
MySQL:
CREATE TABLE Persons ( ID int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Age int, UNIQUE (ID) );
MySQL:
SELECT * FROM Products WHERE Price NOT BETWEEN 10 AND 20;
NOT BETWEEN Example To display the products outside the range of the previous example, use NOT BETWEEN:
SELECT ProductName FROM Products WHERE ProductID = ALL (SELECT ProductID FROM OrderDetails WHERE Quantity = 10);
SQL ALL Example The ALL operator returns TRUE if all of the subquery values meet the condition. The following SQL statement returns TRUE and lists the productnames if ALL the records in the OrderDetails table has quantity = 10:
SQL injection is a code injection technique that might destroy your database. SQL injection is one of the most common web hacking techniques. SQL injection is the placement of malicious code in SQL statements, via web page input.
SQL Injection
SELECT * FROM Users WHERE Name ="John Doe" AND Pass ="myPass" The SQL above is valid and will return all rows from the "Users" table, since OR ""="" is always TRUE.
SQL Injection Based on ""="" is Always True Here is an example of a user login on a web site: Username: Password: Example uName = getRequestString("username"); uPass = getRequestString("userpassword"); sql = 'SELECT * FROM Users WHERE Name ="' + uName + '" AND Pass ="' + uPass + '"'
ALTER TABLE Persons MODIFY Age int NOT NULL;
SQL NOT NULL on ALTER TABLE To create a NOT NULL constraint on the "Age" column when the "Persons" table is already created, use the following SQL:
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;
SQL Self JOIN Example The following SQL statement matches customers that are from the same city:
SELECT ProductName, UnitPrice * (UnitsInStock + ISNULL(UnitsOnOrder, 0)) FROM Products
SQL Server The SQL Server ISNULL() function lets you return an alternative value when an expression is NULL:
ALTER TABLE Persons ALTER COLUMN City DROP DEFAULT
SQL Server / Oracle / MS Access:
ALTER TABLE Persons DROP CONSTRAINT PK_Person;
SQL Server / Oracle / MS Access:
CREATE TABLE Persons ( ID int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Age int CHECK (Age>=18) );
SQL Server / Oracle / MS Access:
•DATE - format YYYY-MM-DD •DATETIME - format: YYYY-MM-DD HH:MI:SS •SMALLDATETIME - format: YYYY-MM-DD HH:MI:SS •TIMESTAMP - format: a unique number Note: The date types are chosen for a column when you create a new table in your database!
SQL Server comes with the following data types for storing a date or a date/time value in the database:
TRUNCATE TABLE table_name;
SQL TRUNCATE TABLE The TRUNCATE TABLE statement is used to delete the data inside a table, but not the table itself. Syntax
SELECT City FROM Customers UNION ALL SELECT City FROM Suppliers ORDER BY City;
SQL UNION ALL Example The following SQL statement returns the cities (duplicate values also) from both the "Customers" and the "Suppliers" table:
SELECT City, Country FROM Customers WHERE Country='Germany' UNION ALL SELECT City, Country FROM Suppliers WHERE Country='Germany' ORDER BY City;
SQL UNION ALL With WHERE The following SQL statement returns the German cities (duplicate values also) from both the "Customers" and the "Suppliers" table:
SELECT City FROM Customers UNION SELECT City FROM Suppliers ORDER BY City;
SQL UNION Example The following SQL statement returns the cities (only distinct values) from both the "Customers" and the "Suppliers" table:
ALTER TABLE Persons ADD UNIQUE (ID);
SQL UNIQUE Constraint on ALTER TABLE To create a UNIQUE constraint on the "ID" column when the table is already created, use the following SQL: MySQL / SQL Server / Oracle / MS Access:
CREATE TABLE Persons ( ID int NOT NULL UNIQUE, LastName varchar(255) NOT NULL, FirstName varchar(255), Age int );
SQL UNIQUE Constraint on CREATE TABLE The following SQL creates a UNIQUE constraint on the "ID" column when the "Persons" table is created: SQL Server / Oracle / MS Access:
CREATE OR REPLACE VIEW view_name AS SELECT column1, column2, ... FROM table_name WHERE condition;
SQL Updating a View A view can be updated with the CREATE OR REPLACE VIEW command. SQL CREATE OR REPLACE VIEW Syntax
CREATE PROCEDURE SelectAllCustomers @City nvarchar(30) AS SELECT * FROM Customers WHERE City = @City GO;
Stored Procedure With One Parameter The following SQL statement creates a stored procedure that selects Customers from a particular City from the "Customers" table:
CREATE TABLE Persons ( Personid int NOT NULL AUTO_INCREMENT, LastName varchar(255) NOT NULL, FirstName varchar(255), Age int, PRIMARY KEY (Personid) );
Syntax for MySQL The following SQL statement defines the "Personid" column to be an auto-increment primary key field in the "Persons" table:
SELECT column1, column2, ... FROM table_name WHERE columnN LIKE pattern;
The LIKE operator is used in a WHERE clause to search for a specified pattern in a column. There are two wildcards used in conjunction with the LIKE operator: •% - The percent sign represents zero, one, or multiple characters •_ - The underscore represents a single character Note: MS Access uses a question mark (?) instead of the underscore (_). The percent sign and the underscore can also be used in combinations! LIKE Syntax
SELECT * INTO newtable [IN externaldb] FROM oldtable WHERE condition;
The SELECT INTO statement copies data from one table into a new table. SELECT INTO Syntax Copy all columns into a new table:
SELECT column_name(s) FROM table_name WHERE column_name operator ANY (SELECT column_name FROM table_name WHERE condition);
The SQL ANY and ALL Operators The ANY and ALL operators are used with a WHERE or HAVING clause. The ANY operator returns true if any of the subquery values meet the condition. The ALL operator returns true if all of the subquery values meet the condition. ANY Syntax
The COUNT() function returns the number of rows that matches a specified criteria. The AVG() function returns the average value of a numeric column. The SUM() function returns the total sum of a numeric column.
The SQL COUNT(), AVG() and SUM() Functions
CREATE DATABASE databasename;
The SQL CREATE DATABASE Statement The CREATE DATABASE statement is used to create a new SQL database. Syntax
DELETE FROM table_name WHERE condition; Note: Be careful when deleting records in a table! Notice the WHERE clause in the DELETE statement. The WHERE clause specifies which record(s) should be deleted. If you omit the WHERE clause, all records in the table will be deleted!
The SQL DELETE Statement The DELETE statement is used to delete existing records in a table. DELETE Syntax
DROP TABLE table_name; Note: Be careful before dropping a table. Deleting a table will result in loss of complete information stored in the table!
The SQL DROP TABLE Statement The DROP TABLE statement is used to drop an existing table in a database. Syntax
SELECT column_name(s) FROM table_name WHERE EXISTS (SELECT column_name FROM table_name WHERE condition);
The SQL EXISTS Operator The EXISTS operator is used to test for the existence of any record in a subquery. The EXISTS operator returns true if the subquery returns one or more records. EXISTS Syntax
IN Syntax SELECT column_name(s) FROM table_name WHERE column_name IN (value1, value2, ...); OR SELECT column_name(s) FROM table_name WHERE column_name IN (SELECT STATEMENT);
The SQL IN Operator The IN operator allows you to specify multiple values in a WHERE clause. The IN operator is a shorthand for multiple OR conditions.
The INSERT INTO SELECT statement copies data from one table and inserts it into another table. •INSERT INTO SELECT requires that data types in source and target tables match •The existing records in the target table are unaffected
The SQL INSERT INTO SELECT Statement
SELECT column_name(s) FROM table1 UNION SELECT column_name(s) FROM table2;
The SQL UNION Operator The UNION operator is used to combine the result-set of two or more SELECT statements. Each SELECT statement within UNION must have the same number of columns The columns must also have similar data types The columns in each SELECT statement must also be in the same order UNION Syntax
SELECT CustomerName, Address + ', ' + PostalCode + ' ' + City + ', ' + Country AS Address FROM Customers;
The following SQL statement creates an alias named "Address" that combine four columns (Address, PostalCode, City and Country):
INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country) VALUES; Did you notice that we did not insert any number into the CustomerID field? The CustomerID column is an auto-increment field and will be generated automatically when a new record is inserted into the table.
The following SQL statement inserts a new record in the "Customers" table:
SELECT * FROM Customers ORDER BY Country ASC, CustomerName DESC;
The following SQL statement selects all customers from the "Customers" table, sorted ascending by the "Country" and descending by the "CustomerName"
SELECT * FROM Customers ORDER BY Country, CustomerName;
The following SQL statement selects all customers from the "Customers" table, sorted by the "Country" and the "CustomerName" column:
SELECT LastName, FirstName, Address FROM Persons WHERE Address IS NULL;
The following SQL statement uses the IS NULL operator to list all persons that have no address:
SELECT CustomerName, City, Country FROM Customers ORDER BY (CASE WHEN City IS NULL THEN Country ELSE City END);
The following SQL will order the customers by City. However, if City is NULL, then order by Country:
NOT NULL - Ensures that a column cannot have a NULL value UNIQUE - Ensures that all values in a column are different PRIMARY KEY - A combination of a NOT NULL and UNIQUE. Uniquely identifies each row in a table FOREIGN KEY - Uniquely identifies a row/record in another table CHECK - Ensures that all values in a column satisfies a specific condition DEFAULT - Sets a default value for a column when no value is specified INDEX - Used to create and retrieve data from the database very quickly
The following constraints are commonly used in SQL: NOT NULL - UNIQUE - PRIMARY KEY - FOREIGN KEY - CHECK - DEFAULT - INDEX -
ALTER TABLE Orders ADD CONSTRAINT FK_PersonOrder FOREIGN KEY (PersonID) REFERENCES Persons(PersonID);
To allow naming of a FOREIGN KEY constraint, and for defining a FOREIGN KEY constraint on multiple columns, use the following SQL syntax: MySQL / SQL Server / Oracle / MS Access:
CREATE TABLE Persons ( ID int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Age int, CONSTRAINT PK_Person PRIMARY KEY (ID,LastName) ); Note: In the example above there is only ONE PRIMARY KEY (PK_Person). However, the VALUE of the primary key is made up of TWO COLUMNS (ID + LastName).
To allow naming of a PRIMARY KEY constraint, and for defining a PRIMARY KEY constraint on multiple columns, use the following SQL syntax: MySQL / SQL Server / Oracle / MS Access:
SELECT * FROM Customers WHERE City LIKE '_erlin'
Using the _ Wildcard The following SQL statement selects all customers with a City starting with any character, followed by "erlin":
SELECT TOP 3 * FROM Customers WHERE Country='Germany';
ADD a WHERE CLAUSE The following SQL statement selects the first three records from the "Customers" table, where the country is "Germany":
SELECT column_name(s) FROM table_name WHERE column_name operator ALL (SELECT column_name FROM table_name WHERE condition); Note: The operator must be a standard comparison operator (=, <>, !=, >, >=, <, or <=).
ALL Syntax
ALTER TABLE table_name ALTER COLUMN column_name datatype;
ALTER TABLE - ALTER/MODIFY COLUMN To change the data type of a column in a table, use the following syntax: SQL Server / MS Access:
Note that parameters are represented in the SQL statement by a @ marker. The SQL engine checks each parameter to ensure that it is correct for its column and are treated literally, and not as part of the SQL to be executed.
ASP.NET Razor Example txtUserId = getRequestString("UserId"); txtSQL = "SELECT * FROM Users WHERE UserId = @0"; db.Execute(txtSQL,txtUserId);
SELECT AVG(column_name) FROM table_name WHERE condition;
AVG() Syntax
When a web site requires only a simple database, Microsoft Access can be a solution. Access is not well suited for very high-traffic, and not as powerful as MySQL, SQL Server, or Oracle.
Access
SELECT column_name(s) FROM table_name AS alias_name;
Alias Table Syntax
•There are more than one table involved in a query •Functions are used in the query •Column names are big or not very readable •Two or more columns are combined together
Aliases can be useful when:
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);
Another Example
SELECT 'Customer' As Type, ContactName, City, Country FROM Customers UNION SELECT 'Supplier', ContactName, City, Country FROM Suppliers;
Another UNION Example The following SQL statement lists all customers and suppliers:
SELECT ProductName, UnitPrice * (UnitsInStock + IFNULL(UnitsOnOrder, 0)) FROM Products or we can use the COALESCE() function, like this: SELECT ProductName, UnitPrice * (UnitsInStock + COALESCE(UnitsOnOrder, 0)) FROM Products
Solutions MySQL The MySQL IFNULL() function lets you return an alternative value if an expression is NULL:
CREATE PROCEDURE SelectAllCustomers AS SELECT * FROM Customers GO;
Stored Procedure Example The following SQL statement creates a stored procedure named "SelectAllCustomers" that selects all records from the "Customers" table:
CREATE PROCEDURE SelectAllCustomers @City nvarchar(30), @PostalCode nvarchar(10) AS SELECT * FROM Customers WHERE City = @City AND PostalCode = @PostalCode GO;
Stored Procedure With Multiple Parameters Setting up multiple parameters is very easy. Just list each parameter and the data type separated by a comma as shown below. The following SQL statement creates a stored procedure that selects Customers from a particular City with a particular PostalCode from the "Customers" table:
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') );
To allow naming of a CHECK constraint, and for defining a CHECK constraint on multiple columns, use the following SQL syntax: MySQL / SQL Server / Oracle / MS Access:
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) );
To allow naming of a FOREIGN KEY constraint, and for defining a FOREIGN KEY constraint on multiple columns, use the following SQL syntax: MySQL / SQL Server / Oracle / MS Access:
ALTER TABLE Persons ADD CONSTRAINT PK_Person PRIMARY KEY (ID,LastName); Note: If you use the ALTER TABLE statement to add a primary key, the primary key column(s) must already have been declared to not contain NULL values (when the table was first created).
To allow naming of a PRIMARY KEY constraint, and for defining a PRIMARY KEY constraint on multiple columns, use the following SQL syntax: MySQL / SQL Server / Oracle / MS Access:
SELECT CustomerName, /*City,*/ Country FROM Customers;
To ignore just a part of a statement, also use the /* */ comment. The following example uses a comment to ignore part of a line:
INSERT INTO Persons (FirstName,LastName) VALUES ('Lars','Monsen'); The SQL statement above would insert a new record into the "Persons" table. The "Personid" column would be assigned a unique value. The "FirstName" column would be set to "Lars" and the "LastName" column would be set to "Monsen".
To insert a new record into the "Persons" table, we will NOT have to specify a value for the "Personid" column (a unique value will be added automatically):
INSERT INTO Persons (Personid,FirstName,LastName) VALUES (seq_person.nextval,'Lars','Monsen'); The SQL statement above would insert a new record into the "Persons" table. The "Personid" column would be assigned the next number from the seq_person sequence. The "FirstName" column would be set to "Lars" and the "LastName" column would be set to "Monsen".
To insert a new record into the "Persons" table, we will have to use the nextval function (this function retrieves the next value from seq_person sequence):
ALTER TABLE Persons ADD CONSTRAINT UC_Person UNIQUE (ID,LastName);
To name a UNIQUE constraint, and to define a UNIQUE constraint on multiple columns, use the following SQL syntax: MySQL / SQL Server / Oracle / MS Access:
CREATE TABLE Persons ( ID int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Age int, CONSTRAINT UC_Person UNIQUE (ID,LastName) );
To name a UNIQUE constraint, and to define a UNIQUE constraint on multiple columns, use the following SQL syntax: MySQL / SQL Server / Oracle / MS Access:
UPDATE Customers SET ContactName = 'Alfred Schmidt', City= 'Frankfurt' WHERE CustomerID = 1;
UPDATE Table The following SQL statement updates the first customer (CustomerID = 1) with a new contact person and a new city.
To protect a web site from SQL injection, you can use SQL parameters. SQL parameters are values that are added to an SQL query at execution time, in a controlled manner.
Use SQL Parameters for Protection
SELECT * FROM Customers WHERE City LIKE '[!bsp]%' OR SELECT * FROM Customers WHERE City NOT LIKE '[bsp]%'
Using the [!charlist] Wildcard The two following SQL statements select all customers with a City NOT starting with "b", "s", or "p":
SELECT * FROM Customers WHERE Country='Mexico';
WHERE Clause Example The following SQL statement selects all the customers from the country "Mexico", in the "Customers" table:
SELECT column1, column2, ... FROM table_name WHERE condition;
WHERE Syntax Note: The WHERE clause is not only used in SELECT statement, it is also used in UPDATE, DELETE statement, etc.!
SELECT * FROM [Products Above Average Price];
We can query the view above as follows:
A stored procedure is a prepared SQL code that you can save, so the code can be reused over and over again. So if you have an SQL query that you write over and over again, save it as a stored procedure, and then just call it to execute it. You can also pass parameters to a stored procedure, so that the stored procedure can act based on the parameter value(s) that is passed.
What is a Stored Procedure?
INSERT INTO table2 (column1, column2, column3, ...) SELECT column1, column2, column3, ... FROM table1 WHERE condition;
Copy only some columns from one table into another table:
SELECT column1, column2, column3, ... INTO newtable [IN externaldb] FROM oldtable WHERE condition; The new table will be created with the column-names and types as defined in the old table. You can create new column names using the AS clause.
Copy only some columns into a new table:
ALTER TABLE Persons DROP COLUMN DateOfBirth;
DROP COLUMN Example Next, we want to delete the column named "DateOfBirth" in the "Persons" table. We use the following SQL statement:
ALTER TABLE Persons DROP CONSTRAINT CHK_PersonAge;
DROP a CHECK Constraint To drop a CHECK constraint, use the following SQL: SQL Server / Oracle / MS Access:
ALTER TABLE Persons ALTER City DROP DEFAULT;
DROP a DEFAULT Constraint To drop a DEFAULT constraint, use the following SQL: MySQL:
EXEC SelectAllCustomers City = "London";
Execute the stored procedure above as follows:
EXEC SelectAllCustomers;
Execute the stored procedure above as follows:
(INNER) JOIN: Returns records that have matching values in both tables •LEFT (OUTER) JOIN: Return all records from the left table, and the matched records from the right table •RIGHT (OUTER) JOIN: Return all records from the right table, and the matched records from the left table •FULL (OUTER) JOIN: Return all records when there is a match in either left or right table
Here are the different types of the JOINs in SQL:
INSERT INTO table2 SELECT * FROM table1 WHERE condition;
INSERT INTO SELECT Syntax Copy all columns from one table to another table:
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();
INSERT INTO STATEMENT IN ASP.NET:
SELECT column_names FROM table_name WHERE column_name IS NULL;
IS NULL Syntax
INSERT INTO table_name VALUES (value1, value2, value3, ...);
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:
•[charlist] - Defines sets and ranges of characters to match •[^charlist] or [!charlist] - Defines sets and ranges of characters NOT to match
In MS Access and SQL Server you can also use:
INSERT INTO Customers (CustomerName, City, Country) VALUES
It is also possible to only insert data in specific columns. The following SQL statement will insert a new record, but only insert data in the "CustomerName", "City", and "Country" columns (CustomerID will be updated automatically):
UPDATE Customers SET ContactName='Juan' WHERE Country='Mexico';
It is the WHERE clause that determines how many records that will be updated. The following SQL statement will update the contactname to "Juan" for all records where country is "Mexico":
ALTER TABLE Persons ALTER COLUMN City SET DEFAULT 'Sandnes';
MS Access:
Microsoft's SQL Server is a popular database software for database-driven web sites with high traffic. SQL Server is a very powerful, robust and full featured SQL database system.
MS SQL Server
ALTER TABLE table_name MODIFY COLUMN column_name datatype;
My SQL / Oracle (prior version 10G):
MySQL is also a popular database software for web sites. MySQL is a very powerful, robust and full featured SQL database system. MySQL is an inexpensive alternative to the expensive Microsoft and Oracle solutions.
MySQL
ALTER TABLE Persons AUTO_INCREMENT=100;
MySQL uses the AUTO_INCREMENT keyword to perform an auto-increment feature. By default, the starting value for AUTO_INCREMENT is 1, and it will increment by 1 for each new record. To let the AUTO_INCREMENT sequence start with another value, use the following SQL statement:
SELECT * FROM Products WHERE ProductName NOT BETWEEN 'Carnarvon Tigers' AND 'Mozzarella di Giovanni' ORDER BY ProductName;
NOT BETWEEN Text Values Example The following SQL statement selects all products with a ProductName NOT BETWEEN 'Carnarvon Tigers' and 'Mozzarella di Giovanni':
ALTER TABLE Persons ADD DateOfBirth date; Notice that the new column, "DateOfBirth", is of type date and is going to hold a date. The data type specifies what type of data the column can hold. For a complete reference of all the data types available in MS Access, MySQL, and SQL Server, go to our complete Data Types reference.
Now we want to add a column named "DateOfBirth" in the "Persons" table. We use the following SQL statement:
Note: The example above will not work in Firefox and Microsoft Edge! Because COUNT(DISTINCT column_name) is not supported in Microsoft Access databases. Firefox and Microsoft Edge are using Microsoft Access in our examples. Here is the workaround for MS Access:
SELECT Count(*) AS DistinctCountries FROM (SELECT DISTINCT Country FROM Customers);
The following SQL statement selects all (and duplicate) values from the "Country" column in the "Customers" table:
SELECT Country FROM Customers;
selects the "CustomerName" and "City" columns from the "Customers" table
SELECT CustomerName, City FROM Customers;
selects only the DISTINCT values from the "Country" column in the "Customers" table:
SELECT DISTINCT Country FROM Customers;
The SELECT DISTINCT statement is used to return only distinct (different) values.
SELECT DISTINCT column1, column2, ... FROM table_name;
NOT Syntax
SELECT column1, column2, ... FROM table_name WHERE NOT condition;
AND Syntax
SELECT column1, column2, ... FROM table_name WHERE condition1 AND condition2 AND condition3 ...;
OR Syntax
SELECT column1, column2, ... FROM table_name WHERE condition1 OR condition2 OR condition3 ...;
ALTER TABLE table_name ADD column_name datatype;
SQL ALTER TABLE Statement The ALTER TABLE statement is used to add, delete, or modify columns in an existing table. The ALTER TABLE statement is also used to add and drop various constraints on an existing table. ALTER TABLE - ADD Column To add a column in a table, use the following syntax:
SELECT ProductName FROM Products WHERE ProductID = ANY (SELECT ProductID FROM OrderDetails WHERE Quantity = 10);
SQL ANY Examples The ANY operator returns TRUE if any of the subquery values meet the condition. The following SQL statement returns TRUE and lists the productnames if it finds ANY records in the OrderDetails table that quantity = 10:
SELECT column_name AS alias_name FROM table_name;
SQL Aliases 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. Alias Column Syntax
SELECT OrderID, Quantity, CASE WHEN Quantity > 30 THEN "The quantity is greater than 30" WHEN Quantity = 30 THEN "The quantity is 30" ELSE "The quantity is under 30" END AS QuantityText FROM OrderDetails;
SQL CASE Examples The following SQL goes through conditions and returns a value when the first condition is met:
The CHECK constraint is used to limit the value range that can be placed in a column. If you define a CHECK constraint on a single column it allows only certain values for this column. If you define a CHECK constraint on a table it can limit the values in certain columns based on values in other columns in the row.
SQL CHECK Constraint
ALTER TABLE Persons ADD CHECK (Age>=18);
SQL CHECK on ALTER TABLE To create a CHECK constraint on the "Age" column when the table is already created, use the following SQL: MySQL / SQL Server / Oracle / MS Access:
CREATE TABLE Persons ( ID int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Age int, CHECK (Age>=18) );
SQL CHECK on CREATE TABLE The following SQL creates a CHECK constraint on the "Age" column when the "Persons" table is created. The CHECK constraint ensures that you can not have any person below 18 years: MySQL:
The CREATE INDEX statement is used to create indexes in tables. Indexes are used to retrieve data from the database very fast. The users cannot see the indexes, they are just used to speed up searches/queries. Note: Updating a table with indexes takes more time than updating a table without (because the indexes also need an update). So, only create indexes on columns that will be frequently searched against.
SQL CREATE INDEX Statement
CREATE TABLE Persons ( PersonID int, LastName varchar(255), FirstName varchar(255), Address varchar(255), City varchar(255) );
SQL CREATE TABLE Example The following example creates a table called "Persons" that contains five columns: PersonID, LastName, FirstName, Address, and City:
CREATE VIEW [Brazil Customers] AS SELECT CustomerName, ContactName FROM Customers WHERE Country = "Brazil";
SQL CREATE VIEW Examples The following SQL creates a view that shows all customers from Brazil:
In SQL, a view is a virtual table based on the result-set of an SQL statement. A view contains rows and columns, just like a real table. The fields in a view are fields from one or more real tables in the database. You can add SQL functions, WHERE, and JOIN statements to a view and present the data as if the data were coming from one single table.
SQL CREATE VIEW Statement
--Select all: SELECT * FROM Customers;
SQL Comments Comments are used to explain sections of SQL statements, or to prevent execution of SQL statements. Note: The examples in this chapter will not work in Firefox and Microsoft Edge! Comments are not supported in Microsoft Access databases. Firefox and Microsoft Edge are using Microsoft Access database in our examples. Single Line Comments Single line comments start with --. Any text between -- and the end of the line will be ignored (will not be executed). The following example uses a single-line comment as an explanation:
SQL constraints are used to specify rules for the data in a table. Constraints are used to limit the type of data that can go into a table. This ensures the accuracy and reliability of the data in the table. If there is any violation between the constraint and the data action, the action is aborted. Constraints can be column level or table level. Column level constraints apply to a column, and table level constraints apply to the whole table.
SQL Constraints
The DEFAULT constraint is used to provide a default value for a column. The default value will be added to all new records IF no other value is specified.
SQL DEFAULT Constraint
ALTER TABLE Persons ALTER City SET DEFAULT 'Sandnes';
SQL DEFAULT on ALTER TABLE To create a DEFAULT constraint on the "City" column when the table is already created, use the following SQL: MySQL:
CREATE TABLE Persons ( ID int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Age int, City varchar(255) DEFAULT 'Sandnes' );
SQL DEFAULT on CREATE TABLE The following SQL sets a DEFAULT value for the "City" column when the "Persons" table is created: My SQL / SQL Server / Oracle / MS Access:
DROP TABLE Shippers;
SQL DROP TABLE Example The following SQL statement drops the existing table "Shippers":
•DATE - format YYYY-MM-DD •DATETIME - format: YYYY-MM-DD HH:MI:SS •TIMESTAMP - format: YYYY-MM-DD HH:MI:SS •YEAR - format YYYY or YY
SQL Date Data Types MySQL comes with the following data types for storing a date or a date/time value in the database:
SELECT SupplierName FROM Suppliers WHERE EXISTS (SELECT ProductName FROM Products WHERE SupplierId = Suppliers.supplierId AND Price < 20);
SQL EXISTS Examples The following SQL statement returns TRUE and lists the suppliers with a product price less than 20:
A FOREIGN KEY is a key used to link two tables together. A FOREIGN KEY is a field (or collection of fields) in one table that refers to the PRIMARY KEY in another table. The table containing the foreign key is called the child table, and the table containing the candidate key is called the referenced or parent table. The FOREIGN KEY constraint is used to prevent actions that would destroy links between tables. The FOREIGN KEY constraint also prevents invalid data from being inserted into the foreign key column, because it has to be one of the values contained in the table it points to.
SQL FOREIGN KEY Constraint
CREATE TABLE Orders ( OrderID int NOT NULL, OrderNumber int NOT NULL, PersonID int, PRIMARY KEY (OrderID), FOREIGN KEY (PersonID) REFERENCES Persons(PersonID) );
SQL FOREIGN KEY on CREATE TABLE The following SQL creates a FOREIGN KEY on the "PersonID" column when the "Orders" table is created: MySQL:
SELECT Customers.CustomerName, Orders.OrderID FROM Customers FULL OUTER JOIN Orders ON Customers.CustomerID=Orders.CustomerID ORDER BY Customers.CustomerName; A selection from the result set may look like this: CustomerName OrderID Alfreds Futterkiste Ana Trujillo Emparedados y helados 10308 Antonio Moreno Taquería 10365 10382 10351 Note: The FULL OUTER JOIN keyword returns all the rows from the left table (Customers), and all the rows from the right table (Orders). If there are rows in "Customers" that do not have matches in "Orders", or if there are rows in "Orders" that do not have matches in "Customers", those rows will be listed as well.
SQL FULL OUTER JOIN Example The following SQL statement selects all customers, and all orders:
SELECT column_name(s) FROM table1 FULL OUTER JOIN table2 ON table1.column_name = table2.column_name;
SQL FULL OUTER JOIN Keyword The FULL OUTER JOIN keyword return all records when there is a match in either left (table1) or right (table2) table records. Note: FULL OUTER JOIN can potentially return very large result-sets! FULL OUTER JOIN Syntax
SELECT COUNT(CustomerID), Country FROM Customers GROUP BY Country;
SQL GROUP BY Examples The following SQL statement lists the number of customers in each country:
If you want your web site to be able to store and retrieve data from a database, your web server should have access to a database-system that uses the SQL language. If your web server is hosted by an Internet Service Provider (ISP), you will have to look for SQL hosting plans. The most common SQL hosting databases are MS SQL Server, Oracle, MySQL, and MS Access.
SQL Hosting
Look at the following SELECT statement: SELECT ProductName, UnitPrice * (UnitsInStock + UnitsOnOrder) FROM Products; In the example above, if any of the "UnitsOnOrder" values are NULL, the result will be NULL.
SQL IFNULL(), ISNULL(), COALESCE(), and NVL() Functions Look at the following "Products" table: P_Id ProductName UnitPrice UnitsInStock UnitsOnOrder 1 Jarlsberg 10.45 16 15 2 Mascarpone 32.56 23 3 Gorgonzola 15.67 9 20 Suppose that the "UnitsOnOrder" column is optional, and may contain NULL values. Look at the following SELECT statement:
SELECT Orders.OrderID, Customers.CustomerName FROM Orders INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID; Note: The INNER JOIN keyword selects all rows from both tables as long as there is a match between the columns. If there are records in the "Orders" table that do not have matches in "Customers", these orders will not be shown!
SQL INNER JOIN Example The following SQL statement selects all orders with customer information
INSERT INTO Customers (CustomerName, City, Country) SELECT SupplierName, City, Country FROM Suppliers;
SQL INSERT INTO SELECT Examples The following SQL statement copies "Suppliers" into "Customers" (the columns that are not filled with data, will contain NULL):
SELECT * FROM Users WHERE UserId = 105; DROP TABLE Suppliers;
SQL Injection Based on Batched SQL Statements Most databases support batched SQL statement. A batch of SQL statements is a group of two or more SQL statements, separated by semicolons. The SQL statement below will return all rows from the "Users" table, then delete the "Suppliers" table. Example SELECT * FROM Users; DROP TABLE Suppliers Look at the following example: Example txtUserId = getRequestString("UserId"); txtSQL = "SELECT * FROM Users WHERE UserId = " + txtUserId; And the following input: User id: The valid SQL statement would look like this:
SELECT Customers.CustomerName, Orders.OrderID FROM Customers LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID ORDER BY Customers.CustomerName; Note: The LEFT JOIN keyword returns all records from the left table (Customers), even if there are no matches in the right table (Orders).
SQL LEFT JOIN Example The following SQL statement will select all customers, and any orders they might have:
SELECT column_name(s) FROM table1 LEFT JOIN table2 ON table1.column_name = table2.column_name; Note: In some databases LEFT JOIN is called LEFT OUTER JOIN.
SQL LEFT JOIN Keyword The LEFT JOIN keyword returns all records from the left table (table1), and the matched records from the right table (table2). The result is NULL from the right side, if there is no match.
SELECT * FROM Customers WHERE CustomerName LIKE 'a%';
SQL LIKE Examples The following SQL statement selects all customers with a CustomerName starting with "a":
By default, a column can hold NULL values. The NOT NULL constraint enforces a column to NOT accept NULL values. This enforces a field to always contain a value, which means that you cannot insert a new record, or update a record without adding a value to this field.
SQL NOT NULL Constraint
CREATE TABLE Persons ( ID int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255) NOT NULL, Age int );
SQL NOT NULL on CREATE TABLE The following SQL ensures that the "ID", "LastName", and "FirstName" columns will NOT accept NULL values when the "Persons" table is created:
The PRIMARY KEY constraint uniquely identifies each record in a table. Primary keys must contain UNIQUE values, and cannot contain NULL values. A table can have only one primary key, which may consist of single or multiple fields.
SQL PRIMARY KEY Constraint
ALTER TABLE Persons ADD PRIMARY KEY (ID);
SQL PRIMARY KEY on ALTER TABLE To create a PRIMARY KEY constraint on the "ID" column when the table is already created, use the following SQL: MySQL / SQL Server / Oracle / MS Access:
MySQL: CREATE TABLE Persons ( ID int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Age int, PRIMARY KEY (ID) ); SQL Server / Oracle / MS Access: CREATE TABLE Persons ( ID int NOT NULL PRIMARY KEY, LastName varchar(255) NOT NULL, FirstName varchar(255), Age int );
SQL PRIMARY KEY on CREATE TABLE The following SQL creates a PRIMARY KEY on the "ID" column when the "Persons" table is created: MySQL:
SELECT Orders.OrderID, Employees.LastName, Employees.FirstName FROM Orders RIGHT JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID ORDER BY Orders.OrderID; Note: The RIGHT JOIN keyword returns all records from the right table (Employees), even if there are no matches in the left table (Orders).
SQL RIGHT JOIN Example The following SQL statement will return all employees, and any orders they might have placed:
SELECT column_name(s) FROM table1 RIGHT JOIN table2 ON table1.column_name = table2.column_name; Note: In some databases RIGHT JOIN is called RIGHT OUTER JOIN.
SQL RIGHT JOIN Keyword The RIGHT JOIN keyword returns all records from the right table (table2), and the matched records from the left table (table1). The result is NULL from the left side, when there is no match. RIGHT JOIN Syntax
SELECT * INTO CustomersBackup2017 FROM Customers;
SQL SELECT INTO Examples The following SQL statement creates a backup copy of Customers:
SELECT column_name(s) FROM table1 T1, table1 T2 WHERE condition;
SQL Self JOIN A self JOIN is a regular join, but the table is joined with itself. Self JOIN Syntax
CREATE TABLE Orders ( OrderID int NOT NULL PRIMARY KEY, OrderNumber int NOT NULL, PersonID int FOREIGN KEY REFERENCES Persons(PersonID) );
SQL Server / Oracle / MS Access:
SELECT TOP 50 PERCENT * FROM Customers;
SQL TOP PERCENT Example The following SQL statement selects the first 50% of the records from the "Customers" table:
SELECT City, Country FROM Customers WHERE Country='Germany' UNION SELECT City, Country FROM Suppliers WHERE Country='Germany' ORDER BY City;
SQL UNION With WHERE The following SQL statement returns the German cities (only distinct values) from both the "Customers" and the "Suppliers" table:
The UNIQUE constraint ensures that all values in a column are different. Both the UNIQUE and PRIMARY KEY constraints provide a guarantee for uniqueness for a column or set of columns. A PRIMARY KEY constraint automatically has a UNIQUE constraint. However, you can have many UNIQUE constraints per table, but only one PRIMARY KEY constraint per table.
SQL UNIQUE Constraint
CREATE TABLE table_name ( column1 datatype constraint, column2 datatype constraint, column3 datatype constraint, .... );
SQL constraints are used to specify rules for data in a table. SQL Create Constraints Constraints can be specified when the table is created with the CREATE TABLE statement, or after the table is created with the ALTER TABLE statement.
SELECT * FROM Customers WHERE CustomerID=1;
SQL requires single quotes around text values (most database systems will also allow double quotes). However, numeric fields should not be enclosed in quotes:
SELECT SUM(Quantity) FROM OrderDetails;
SUM() Example The following SQL statement finds the sum of the "Quantity" fields in the "OrderDetails" table: Example
SELECT * FROM Customers WHERE Country IN ('Germany', 'France', 'UK');
IN Operator Examples The following SQL statement selects all customers that are located in "Germany", "France" and "UK":
ALTER TABLE Persons ADD CONSTRAINT df_City DEFAULT 'Sandnes' FOR City;
SQL Server:
CREATE PROCEDURE procedure_name AS sql_statement GO;
Stored Procedure Syntax
CREATE TABLE Persons ( Personid int IDENTITY(1,1) PRIMARY KEY, LastName varchar(255) NOT NULL, FirstName varchar(255), Age int ); The MS SQL Server uses the IDENTITY keyword to perform an auto-increment feature. In the example above, the starting value for IDENTITY is 1, and it will increment by 1 for each new record. Tip: To specify that the "Personid" column should start at value 10 and increment by 5, change it to IDENTITY(10,5).
Syntax for SQL Server The following SQL statement defines the "Personid" column to be an auto-increment primary key field in the "Persons" table:
SELECT UserId, Name, Password FROM Users WHERE UserId = 105 or 1=1; A hacker might get access to all the user names and passwords in a database, by simply inserting 105 OR 1=1 into the input field.
The SQL above is valid and will return ALL rows from the "Users" table, since OR 1=1 is always TRUE. Does the example above look dangerous? What if the "Users" table contains names and passwords? The SQL statement above is much the same as this:
ALTER TABLE Customers DROP COLUMN Email;
The following SQL deletes the "Email" column from the "Customers" table:
SELECT CustomerName AS Customer, ContactName AS [Contact Person] FROM Customers;
The following SQL statement creates two aliases, one for the CustomerName column and one for the ContactName column. Note: It requires double quotation marks or square brackets if the alias name contains spaces:
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;
The following SQL statement lists if the employees "Davolio" or "Fuller" have registered more than 25 orders:
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;
The following SQL statement lists the employees that have registered more than 10 orders:
SELECT COUNT(CustomerID), Country FROM Customers GROUP BY Country HAVING COUNT(CustomerID) > 5 ORDER BY COUNT(CustomerID) DESC;
The following SQL statement lists the number of customers in each country, sorted high to low (Only include countries with more than 5 customers):
SELECT COUNT(CustomerID), Country FROM Customers GROUP BY Country ORDER BY COUNT(CustomerID) DESC;
The following SQL statement lists the number of customers in each country, sorted high to low:
SELECT COUNT(CustomerID), Country FROM Customers GROUP BY Country HAVING COUNT(CustomerID) > 5;
The following SQL statement lists the number of customers in each country. Only include countries with more than 5 customers:
SELECT * FROM Customers WHERE City LIKE '[a-c]%'
The following SQL statement selects all customers with a City starting with "a", "b", or "c":
SELECT * FROM Users WHERE UserId = 105 OR 1=1;
The rest of this chapter describes the potential dangers of using user input in SQL statements. SQL Injection Based on 1=1 is Always True Look at the example above again. The original purpose of the code was to create an SQL statement to select a user, with a given user id. If there is nothing to prevent a user from entering "wrong" input, the user can enter some "smart" input like this: UserId: 105 OR 1=1 Then, the SQL statement will look like this:
LIKE
Search for a pattern
UPDATE Customers SET ContactName='Juan';
Update Warning! Be careful when updating records. If you omit the WHERE clause, ALL records will be updated!
ALTER TABLE Customers ADD Email varchar(255);
The following SQL adds an "Email" column to the "Customers" table:
CASE WHEN condition1 THEN result1 WHEN condition2 THEN result2 WHEN conditionN THEN resultN ELSE result END;
CASE Syntax
CREATE INDEX idx_pname ON Persons (LastName, FirstName);
If you want to create an index on a combination of columns, you can list the column names within the parentheses, separated by commas:
SELECT ProductName, UnitPrice * (UnitsInStock + IIF(IsNull(UnitsOnOrder), 0, UnitsOnOrder)) FROM Products
MS Access The MS Access IsNull() function returns TRUE (-1) if the expression is a null value, otherwise FALSE (0):
ALTER TABLE Orders DROP CONSTRAINT FK_PersonOrder;
SQL Server / Oracle / MS Access:
--SELECT * FROM Customers; SELECT * FROM Products;
The following example uses a single-line comment to ignore a statement:
SELECT * FROM [Brazil Customers];
We can query the view above as follows:
•% - The percent sign represents zero, one, or multiple characters •_ - The underscore represents a single character
A wildcard character is used to substitute any other character(s) in a string. Wildcard characters are used with the SQL LIKE operator. The LIKE operator is used in a WHERE clause to search for a specified pattern in a column. There are two wildcards used in conjunction with the LIKE operator:
SELECT AVG(Price) FROM Products;
AVG() Example The following SQL statement finds the average price of all products:
SELECT COUNT(column_name) FROM table_name WHERE condition;
COUNT() Syntax
CREATE UNIQUE INDEX index_name ON table_name (column1, column2, ...); Note: The syntax for creating indexes varies among different databases. Therefore: Check the syntax for creating indexes in your database.
CREATE UNIQUE INDEX Syntax Creates a unique index on a table. Duplicate values are not allowed
CREATE VIEW view_name AS SELECT column1, column2, ... FROM table_name WHERE condition; Note: A view always shows up-to-date data! The database engine recreates the data, using the view's SQL statement, every time a user queries a view.
CREATE VIEW Syntax
ALTER TABLE Persons ALTER COLUMN DateOfBirth year; Notice that the "DateOfBirth" column is now of type year and is going to hold a year in a two- or four-digit format.
Change Data Type Example Now we want to change the data type of the column named "DateOfBirth" in the "Persons" table. We use the following SQL statement:
DROP INDEX index_name;
DB2/Oracle:
DROP INDEX index_name ON table_name;
DROP INDEX Statement The DROP INDEX statement is used to delete an index in a table. MS Access:
ALTER TABLE Persons DROP PRIMARY KEY;
DROP a PRIMARY KEY Constraint To drop a PRIMARY KEY constraint, use the following SQL: MySQL:
MySQL: ALTER TABLE Persons DROP INDEX UC_Person; SQL Server / Oracle / MS Access: ALTER TABLE Persons DROP CONSTRAINT UC_Person;
DROP a UNIQUE Constraint To drop a UNIQUE constraint, use the following SQL: MySQL:
EXEC SelectAllCustomers City = "London", PostalCode = "WA1 1DP";
Execute the stored procedure above as follows:
SELECT Shippers.ShipperName, COUNT(Orders.OrderID) AS NumberOfOrders FROM Orders LEFT JOIN Shippers ON Orders.ShipperID = Shippers.ShipperID GROUP BY ShipperName;
GROUP BY With JOIN Example The following SQL statement lists the number of orders sent by each shipper:
DELETE FROM table_name; or: DELETE * FROM table_name;
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:
Oracle is also a popular database software for database-driven web sites with high traffic. Oracle is a very powerful, robust and full featured SQL database system.
Oracle
SELECT ProductName, UnitPrice * (UnitsInStock + NVL(UnitsOnOrder, 0)) FROM Products
Oracle The Oracle NVL() function achieves the same result:
ALTER TABLE table_name MODIFY column_name datatype;
Oracle 10G and later:
ALTER TABLE Persons MODIFY City DEFAULT 'Sandnes';
Oracle:
List all customers within database.
SELECT * FROM Customers;
The most difficult part when working with dates is to be sure that the format of the date you are trying to insert, matches the format of the date column in the database. As long as your data contains only the date portion, your queries will work as expected. However, if a time portion is involved, it gets more complicated.
SQL Dates
DROP VIEW view_name;
SQL Dropping a View A view is deleted with the DROP VIEW command. SQL DROP VIEW Syntax
ALTER TABLE Orders ADD FOREIGN KEY (PersonID) REFERENCES Persons(PersonID);
SQL FOREIGN KEY on ALTER TABLE To create a FOREIGN KEY constraint on the "PersonID" column when the "Orders" table is already created, use the following SQL: MySQL / SQL Server / Oracle / MS Access:
DROP INDEX table_name.index_name;
SQL Server:
txtUserId = getRequestString("UserId"); txtSQL = "SELECT * FROM Users WHERE UserId = " + txtUserId;
SQL in Web Pages SQL injection usually occurs when you ask a user for input, like their username/userid, and instead of a name/id, the user gives you an SQL statement that you will unknowingly run on your database. Look at the following example which creates a SELECT statement by adding a variable (txtUserId) to a select string. The variable is fetched from user input (getRequestString):
SELECT TOP 3 * FROM Customers;
The following SQL statement selects the first three records from the "Customers" table:
SELECT column_name(s) FROM table1 UNION ALL SELECT column_name(s) FROM table2; Note: The column names in the result-set are usually equal to the column names in the first SELECT statement in the UNION.
UNION ALL Syntax The UNION operator selects only distinct values by default. To allow duplicate values, use UNION ALL:
SELECT * FROM Customers WHERE City LIKE '[bsp]%'
Using the [charlist] Wildcard The following SQL statement selects all customers with a City starting with "b", "s", or "p":
SELECT column_names FROM table_name WHERE column_name IS NOT NULL;
IS NOT NULL Syntax
lists the number of different (distinct) customer countries:
SELECT COUNT(DISTINCT Country) FROM Customers;
SELECT SUM(column_name) FROM table_name WHERE condition;
SUM() Syntax
SELECT MAX(Price) AS LargestPrice FROM Products;
The following SQL statement finds the price of the most expensive product:
IN
To specify multiple possible values for a column
SELECT * INTO CustomersBackup2017 IN 'Backup.mdb' FROM Customers;
The following SQL statement uses the IN clause to copy the table into a new table in another database:
SELECT LastName, FirstName, Address FROM Persons WHERE Address IS NOT NULL;
The following SQL statement uses the IS NOT NULL operator to list all persons that do have an address:
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;
The following example uses a comment to ignore part of a statement:
/*SELECT * FROM Customers; SELECT * FROM Products; SELECT * FROM Orders; SELECT * FROM Categories;*/ SELECT * FROM Suppliers;
The following example uses a multi-line comment to ignore many statements:
SELECT * FROM Customers -- WHERE City='Berlin';
The following example uses a single-line comment to ignore the end of a line:
txtUserId = getRequestString("UserId"); sql = "SELECT * FROM Customers WHERE CustomerId = @0"; command = new SqlCommand(sql); command.Parameters.AddWithValue("@0",txtUserID); command.ExecuteReader();
The following examples shows how to build parameterized queries in some common web languages. SELECT STATEMENT IN ASP.NET:
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 3 characters in length WHERE ContactName LIKE 'a%o' Finds any values that start with "a" and ends with "o"
Tip: You can also combine any number of conditions using AND or OR operators. Here are some examples showing different LIKE operators with '%' and '_' wildcards:
SELECT * FROM Customers WHERE ContactName LIKE 'a%o';
The following SQL statement selects all customers with a ContactName that starts with "a" and ends with "o":
SELECT * FROM Customers WHERE CustomerName LIKE '%a';
The following SQL statement selects all customers with a CustomerName ending with "a":
SELECT * FROM Customers WHERE CustomerName NOT LIKE 'a%';
The following SQL statement selects all customers with a CustomerName that does NOT start with "a":
SELECT * FROM Customers WHERE CustomerName LIKE '%or%';
The following SQL statement selects all customers with a CustomerName that have "or" in any position:
SELECT * FROM Customers WHERE CustomerName LIKE '_r%';
The following SQL statement selects all customers with a CustomerName that have "r" in the second position:
SELECT * FROM Customers WHERE CustomerName LIKE 'a_%_%';
The following SQL statement selects all customers with a CustomerName that starts with "a" and are at least 3 characters in length:
SELECT * FROM Customers WHERE City='Berlin' OR City='München';
The following SQL statement selects all fields from "Customers" where city is "Berlin" OR "München":
SELECT * FROM Customers WHERE Country='Germany' AND City='Berlin';
The following SQL statement selects all fields from "Customers" where country is "Germany" AND city is "Berlin":
SELECT * FROM Customers WHERE NOT Country='Germany' AND NOT Country='USA';
The following SQL statement selects all fields from "Customers" where country is NOT "Germany" and NOT "USA":
SELECT * FROM Customers WHERE NOT Country='Germany';
The following SQL statement selects all fields from "Customers" where country is NOT "Germany":
SELECT * FROM Products WHERE Price BETWEEN 10 AND 20;
The following SQL statement selects all products with a price BETWEEN 10 and 20:
ALTER TABLE Persons ADD CONSTRAINT CHK_PersonAge CHECK (Age>=18 AND City='Sandnes');
To allow naming of a CHECK constraint, and for defining a CHECK constraint on multiple columns, use the following SQL syntax: MySQL / SQL Server / Oracle / MS Access:
SELECT * FROM Orders WHERE OrderDate BETWEEN #01/07/1996# AND #31/07/1996#; OR SELECT * FROM Orders WHERE OrderDate BETWEEN '1996-07-01' AND '1996-07-31';
BETWEEN Dates Example The following SQL statement selects all orders with an OrderDate BETWEEN '01-July-1996' and '31-July-1996':
SELECT * FROM Products WHERE ProductName BETWEEN 'Carnarvon Tigers' AND 'Mozzarella di Giovanni' ORDER BY ProductName;
BETWEEN Text Values Example The following SQL statement selects all products with a ProductName BETWEEN 'Carnarvon Tigers' and 'Mozzarella di Giovanni':
SELECT * FROM Products WHERE (Price BETWEEN 10 AND 20) AND NOT CategoryID IN (1,2,3);
BETWEEN with IN Example The following SQL statement selects all products with a price BETWEEN 10 and 20. In addition; do not show products with a CategoryID of 1,2, or 3:
BETWEEN
Between an inclusive range
SELECT COUNT(ProductID) FROM Products;
COUNT() Example The following SQL statement finds the number of products: Example
CREATE DATABASE testDB; Tip: Make sure you have admin privilege before creating any database. Once a database is created, you can check it in the list of databases with the following SQL command: SHOW DATABASES;
CREATE DATABASE Example The following SQL statement creates a database called "testDB":
CREATE INDEX idx_lastname ON Persons (LastName);
CREATE INDEX Example The SQL statement below creates an index named "idx_lastname" on the "LastName" column in the "Persons" table:
CREATE TABLE Persons ( Personid AUTOINCREMENT PRIMARY KEY, LastName varchar(255) NOT NULL, FirstName varchar(255), Age int ); The MS Access uses the AUTOINCREMENT keyword to perform an auto-increment feature. By default, the starting value for AUTOINCREMENT is 1, and it will increment by 1 for each new record. Tip: To specify that the "Personid" column should start at value 10 and increment by 5, change the autoincrement to AUTOINCREMENT(10,5).
Syntax for Access The following SQL statement defines the "Personid" column to be an auto-increment primary key field in the "Persons" table:
CREATE SEQUENCE seq_person MINVALUE 1 START WITH 1 INCREMENT BY 1 CACHE 10; The code above creates a sequence object called seq_person, that starts with 1 and will increment by 1. It will also cache up to 10 values for performance. The cache option specifies how many sequence values will be stored in memory for faster access.
Syntax for Oracle In Oracle the code is a little bit more tricky. You will have to create an auto-increment field with the sequence object (this object generates a number sequence). Use the following CREATE SEQUENCE syntax:
SELECT column_name(s) FROM table_name WHERE column_name BETWEEN value1 AND value2;
The BETWEEN operator selects values within a given range. The values can be numbers, text, or dates. The BETWEEN operator is inclusive: begin and end values are included. BETWEEN Syntax
CREATE TABLE Orders ( ID int NOT NULL, OrderNumber int NOT NULL, OrderDate date DEFAULT GETDATE() );
The DEFAULT constraint can also be used to insert system values, by using functions like GETDATE():
SELECT column_name(s) FROM table1 INNER JOIN table2 ON table1.column_name = table2.column_name;
The INNER JOIN keyword selects records that have matching values in both tables. INNER JOIN Syntax
INSERT INTO table_name (column1, column2, column3, ...) VALUES (value1, value2, value3, ...);
The INSERT INTO statement is used to insert new records in a table. INSERT INTO Syntax It is possible to write the INSERT INTO statement in two ways. The first way specifies both the column names and the values to be inserted:
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;
The following SQL statement selects all the orders from the customer with CustomerID=4 (Around the Horn). We use the "Customers" and "Orders" tables, and give them the table aliases of "c" and "o" respectively (Here we use aliases to make the SQL shorter):
SELECT * FROM Customers WHERE Country='Germany' AND ROWNUM <= 3;
The following SQL statement shows the equivalent example using ROWNUM
SELECT * FROM Customers WHERE ROWNUM <= 3;
The following SQL statement shows the equivalent example using ROWNUM:
SELECT MAX(column_name) FROM table_name WHERE condition;
The MAX() function returns the largest value of the selected column
SELECT MIN(column_name) FROM table_name WHERE condition;
The MIN() function returns the smallest value of the selected column. MIN() Syntax
SELECT column1, column2, ... FROM table_name ORDER BY column1, column2, ... ASC|DESC;
The ORDER BY keyword is used to sort the result-set in ascending or descending order. The ORDER BY keyword sorts the records in ascending order by default. To sort the records in descending order, use the DESC keyword.
PersonID LastName FirstName Address City Tip: The empty "Persons" table can now be filled with data with the SQL INSERT INTO statement.
The PersonID column is of type int and will hold an integer. The LastName, FirstName, Address, and City columns are of type varchar and will hold characters, and the maximum length for these fields is 255 characters. The empty "Persons" table will now look like this:
SELECT TOP number|percent column_name(s) FROM table_name WHERE condition;
The SELECT TOP clause is used to specify the number of records to return. The SELECT TOP clause is useful on large tables with thousands of records. Returning a large number of records can impact on performance. Note: Not all database systems support the SELECT TOP clause. MySQL supports the LIMIT clause to select a limited number of records, while Oracle uses ROWNUM. Uses both SQL Server / MS Access Syntax:
BACKUP DATABASE databasename TO DISK = 'filepath';
The SQL BACKUP DATABASE Statement The BACKUP DATABASE statement is used in SQL Server to create a full back up of an existing SQL database. Syntax
BACKUP DATABASE databasename TO DISK = 'filepath' WITH DIFFERENTIAL;
The SQL BACKUP WITH DIFFERENTIAL Statement A differential back up only backs up the parts of the database that have changed since the last full database backup. Syntax
The CASE statement goes through conditions and return a value when the first condition is met (like an IF-THEN-ELSE statement). So, once a condition is true, it will stop reading and return the result. If no conditions are true, it returns the value in the ELSE clause. If there is no ELSE part and no conditions are true, it returns NULL.
The SQL CASE Statement
CREATE TABLE table_name ( column1 datatype, column2 datatype, column3 datatype, .... ); The column parameters specify the names of the columns of the table. The datatype parameter specifies the type of data the column can hold (e.g. varchar, integer, date, etc.). Tip: For an overview of the available data types, go to our complete Data Types Reference.
The SQL CREATE TABLE Statement The CREATE TABLE statement is used to create a new table in a database. Syntax
DROP DATABASE databasename; Note: Be careful before dropping a database. Deleting a database will result in loss of complete information stored in the database!
The SQL DROP DATABASE Statement The DROP DATABASE statement is used to drop an existing SQL database. Syntax
SELECT column_name(s) FROM table_name WHERE condition GROUP BY column_name(s) ORDER BY column_name(s);
The SQL GROUP BY Statement The GROUP BY statement is often used with aggregate functions (COUNT, MAX, MIN, SUM, AVG) to group the result-set by one or more columns. GROUP BY Syntax
SELECT column_name(s) FROM table_name WHERE condition GROUP BY column_name(s) HAVING condition ORDER BY column_name(s);
The SQL HAVING Clause The HAVING clause was added to SQL because the WHERE keyword could not be used with aggregate functions. HAVING Syntax
UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE condition; Note: Be careful when updating records in a table! Notice the WHERE clause in the UPDATE statement. The WHERE clause specifies which record(s) that should be updated. If you omit the WHERE clause, all records in the table will be updated!
The UPDATE statement is used to modify the existing records in a table. UPDATE Syntax
SELECT * FROM Customers LIMIT 3;
The following SQL statement shows the equivalent example using the LIMIT clause:
SELECT * FROM Customers WHERE Country='Germany' LIMIT 3;
The following SQL statement shows the equivalent example using the LIMIT clause:
CREATE OR REPLACE VIEW [Brazil Customers] AS SELECT CustomerName, ContactName, City FROM Customers WHERE Country = "Brazil";
The following SQL adds the "City" column to the "Brazil Customers" view:
CREATE TABLE TestTable AS SELECT customername, contactname FROM customers;
The following SQL creates a new table called "TestTables" (which is a copy of the "Customers" table):
CREATE VIEW [Products Above Average Price] AS SELECT ProductName, Price FROM Products WHERE Price > (SELECT AVG(Price) FROM Products);
The following SQL creates a view that selects every product in the "Products" table with a price higher than the average price:
DROP VIEW [Brazil Customers];
The following SQL drops the "Brazil Customers" view:
INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country) SELECT SupplierName, ContactName, Address, City, PostalCode, Country FROM Suppliers;
The following SQL statement copies "Suppliers" into "Customers" (fill all columns):
SELECT Customers.CustomerName, Orders.OrderID INTO CustomersOrderBackup2017 FROM Customers LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID;
The following SQL statement copies data from more than one table into a new table:
SELECT CustomerName, ContactName INTO CustomersBackup2017 FROM Customers;
The following SQL statement copies only a few columns into a new table:
SELECT * INTO CustomersGermany FROM Customers WHERE Country = 'Germany';
The following SQL statement copies only the German customers into a new table:
INSERT INTO Customers (CustomerName, City, Country) SELECT SupplierName, City, Country FROM Suppliers WHERE Country='Germany';
The following SQL statement copies only the German suppliers into "Customers":
SELECT CustomerID AS ID, CustomerName AS Customer FROM Customers;
The following SQL statement creates two aliases, one for the CustomerID column and one for the CustomerName column:
DELETE FROM Customers WHERE CustomerName='Alfreds Futterkiste';
The following SQL statement deletes the customer "Alfreds Futterkiste" from the "Customers" table:
SELECT MIN(Price) AS SmallestPrice FROM Products;
The following SQL statement finds the price of the cheapest product:
SELECT Orders.OrderID, Orders.OrderDate, Customers.CustomerName FROM Customers, Orders WHERE Customers.CustomerName="Around the Horn" AND Customers.CustomerID=Orders.CustomerID;
The following SQL statement is the same as above, but without aliases:
SELECT ProductName FROM Products WHERE ProductID = ANY (SELECT ProductID FROM OrderDetails WHERE Quantity > 99);
The following SQL statement returns TRUE and lists the productnames if it finds ANY records in the OrderDetails table that quantity > 99:
SELECT SupplierName FROM Suppliers WHERE EXISTS (SELECT ProductName FROM Products WHERE SupplierId = Suppliers.supplierId AND Price = 22);
The following SQL statement returns TRUE and lists the suppliers with a product price equal to 22:
SELECT * FROM Customers ORDER BY Country DESC;
The following SQL statement selects all customers from the "Customers" table, sorted DESCENDING by the "Country" column:
SELECT * FROM Customers ORDER BY Country;
The following SQL statement selects all customers from the "Customers" table, sorted by the "Country" column:
SELECT * FROM Customers WHERE Country NOT IN ('Germany', 'France', 'UK');
The following SQL statement selects all customers that are NOT located in "Germany", "France" or "UK":
SELECT * FROM Customers WHERE Country IN (SELECT Country FROM Suppliers);
The following SQL statement selects all customers that are from the same countries as the suppliers:
SELECT * FROM Customers WHERE City LIKE '%es%'
The following SQL statement selects all customers with a City containing the pattern "es":
SELECT * FROM Customers WHERE City LIKE 'L_n_on'
The following SQL statement selects all customers with a City starting with "L", followed by any character, followed by "n", followed by any character, followed by "on":
SELECT * FROM Customers WHERE City LIKE 'ber%'
The following SQL statement selects all customers with a City starting with "ber":
SELECT * FROM Customers WHERE Country='Germany' AND (City='Berlin' OR City='München');
You can also combine the AND, OR and NOT operators. The following SQL statement selects all fields from "Customers" where country is "Germany" AND city must be "Berlin" OR "München" (use parenthesis to form complex expressions):
