Table 2-1 Core SQL:2003 Commands
ALTER DOMAIN DECLARE CURSOR FREE LOCATOR
ALTER TABLE DECLARE TABLE GET DIAGNOSTICS
CALL DELETE GRANT
CLOSE DISCONNECT HOLD LOCATOR
COMMIT DROP ASSERTION INSERT
CONNECT DROP CHARACTER SET OPEN
CREATE ASSERTION DROP COLLATION RELEASE SAVEPOINT
CREATE CHARACTER SET DROP DOMAIN RETURN
CREATE COLLATION DROP ORDERING REVOKE
CREATE DOMAIN DROP ROLE ROLLBACK
CREATE FUNCTION DROP SCHEMA SAVEPOINT
CREATE METHOD DROP SPECIFIC FUNCTION SELECT
CREATE ORDERING DROP SPECIFIC SET CONNECTION
PROCEDURE
CREATE PROCEDURE DROP SPECIFIC SET CONSTRAINTS
ROUTINE
CREATE ROLE DROP TABLE SET ROLE
CREATE SCHEMA DROP TRANSFORM SET SESSION AUTHORIZATION
CREATE TABLE DROP TRANSLATION SET SESSION
CHARACTERISTICS
CREATE TRANSFORM DROP TRIGGER SET TIME ZONE
CREATE TRANSLATION DROP TYPE SET TRANSACTION
CREATE TRIGGER DROP VIEW START TRANSACTION
CREATE TYPE FETCH UPDATE
CREATE VIEW
SELECT * FROM EMPLOYEE WHERE Age>40 OR Salary>60000 ;
SELECT SELECT FROM SELECT WHERE SELECT = WHERE ;?????
SELECT * FROM Customers;
SELECT column_name,column_name
FROM table_name;
SELECT DISTINCT column_name,column_name
FROM table_name;
SELECT column_name,column_name
FROM table_name
WHERE column_name operator value;
SELECT column_name,column_name
FROM table_name
WHERE column_name operator value;
SELECT column_name, column_name
FROM table_name
ORDER BY column_name ASC|DESC, column_name ASC|DESC;
INSERT INTO table_name (column1,column2,column3,...)
VALUES (value1,value2,value3,...);
SQL UPDATE Statement
UPDATE table_name
SET column1=value1,column2=value2,...
WHERE some_column=some_value;
Example
UPDATE Customers
SET ContactName='Alfred Schmidt', City='Hamburg'
WHERE CustomerName='Alfreds Futterkiste';
Update Warning!
Be careful when updating records. If we had omitted the WHERE clause, in the
example above, like this:
UPDATE Customers
SET ContactName='Alfred Schmidt', City='Hamburg';
SQL DELETE Statement
DELETE FROM table_name
WHERE some_column=some_value;
Example
DELETE FROM Customers
WHERE CustomerName='Alfreds Futterkiste' AND ContactName='Maria
Anders';
DELETE FROM table_name; - BRISE SVE PODATKE
SQL Injection
Server Code
txtUserId = getRequestString("UserId");
txtSQL = "SELECT * FROM Users WHERE UserId = " + txtUserId;
SQL SELECT TOP Clause
SELECT TOP number|percent column_name(s)
FROM table_name;
SELECT TOP 2 * FROM Customers;
SELECT TOP 50 PERCENT * FROM Customers;
SQL LIKE Syntax
SELECT column_name(s)
FROM table_name
WHERE column_name LIKE pattern;
Example
SELECT * FROM Customers
WHERE City LIKE 's%'; ILI NOT LIKE %nekoime% %-wild card zamenjue sve
moguce karaktere
SQL Wildcards
Wildcard Description
% A substitute for zero or more characters
_ A substitute for a single character
[charlist] Sets and ranges of characters to match
[^charlist] Matches only a character NOT specified within the brackets
or
[!charlist]
Example
SELECT * FROM Customers
WHERE City LIKE 'ber%';
Example
SELECT * FROM Customers
WHERE City LIKE '%es%';
Example
SELECT * FROM Customers
WHERE City LIKE '_erlin';
The following SQL statement selects all customers with a City starting with "b",
"s", or "p":
Example
SELECT * FROM Customers
WHERE City LIKE '[bsp]%';
Example
SELECT * FROM Customers
WHERE City LIKE '[a-c]%';
SQL IN Operator
SQL IN Syntax
SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1,value2,...); slicno kao where or
SQL BETWEEN Operator
SQL BETWEEN Syntax
SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;
Example
SELECT * FROM Products
WHERE Price BETWEEN 10 AND 20;
Example
SELECT * FROM Products
WHERE Price NOT BETWEEN 10 AND 20;
Example
SELECT * FROM Products
WHERE ProductName BETWEEN 'C' AND 'M';
SELECT * FROM Orders
WHERE OrderDate BETWEEN #07/04/1996# AND #07/09/1996#;
SQL Aliases
SQL Alias Syntax for Columns
SELECT column_name AS alias_name
FROM table_name;
SQL Alias Syntax for Tables
SELECT column_name(s)
FROM table_name AS alias_name;
Example
SELECT [Link], [Link], [Link]
FROM Customers, Orders
WHERE [Link]="Around the Horn" AND
[Link]=[Link];
Aliases can be useful when:
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
SQL Joins
Example
SELECT [Link], [Link], [Link]
FROM Orders
INNER JOIN Customers
ON [Link]=[Link];
Different SQL JOINs
Before we continue with examples, we will list the types of the different SQL JOINs
you can use:
INNER JOIN: Returns all rows when there is at least one match in BOTH
tables
LEFT JOIN: Return all rows from the left table, and the matched rows from
the right table
RIGHT JOIN: Return all rows from the right table, and the matched rows
from the left table
FULL JOIN: Return all rows when there is a match in ONE of the tables
SQL INNER JOIN Keyword
SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name=table2.column_name;
Example
SELECT [Link], [Link]
FROM Customers
INNER JOIN Orders
ON [Link]=[Link]
ORDER BY [Link];
SQL LEFT JOIN Syntax
SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name=table2.column_name;
SQL RIGHT JOIN Syntax
SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name=table2.column_name;
SQL FULL OUTER JOIN Syntax
SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2
ON table1.column_name=table2.column_name;
SQL UNION Operator
SQL UNION Syntax
SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;
Example (podaci iz dve tabele)
SELECT City, Country FROM Customers
WHERE Country='Germany'
UNION ALL
SELECT City, Country FROM Suppliers
WHERE Country='Germany'
ORDER BY City;
