Scribd Logo
Upload

Welcome to Scribd!

  • 47 views

    Table 2-1 Core SQL:2003 Commands

    Uploaded by

    Zoran Vasic
    The document lists SQL commands and clauses that are part of the SQL:2003 standard. It includes commands for data definition, manipulation, and transaction control. It also covers clauses for selection like WHERE, ORDER BY, JOIN, UNION and operators like LIKE, BETWEEN, IN to filter and compare data in SQL queries. Examples are provided for many of the commands and clauses.

    Copyright:

    © All Rights Reserved
    Download as DOCX, PDF, TXT or read online from Scribd

    Table 2-1 Core SQL:2003 Commands

    Uploaded by

    Zoran Vasic
    47 views8 pages
    The document lists SQL commands and clauses that are part of the SQL:2003 standard. It includes commands for data definition, manipulation, and transaction control. It also covers clauses for selection like WHERE, ORDER BY, JOIN, UNION and operators like LIKE, BETWEEN, IN to filter and compare data in SQL queries. Examples are provided for many of the commands and clauses.

    Original Description:

    sql

    Original Title

    sql

    Copyright

    © © All Rights Reserved

    Available Formats

    DOCX, PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    The document lists SQL commands and clauses that are part of the SQL:2003 standard. It includes commands for data definition, manipulation, and transaction control. It also covers clauses for selection like WHERE, ORDER BY, JOIN, UNION and operators like LIKE, BETWEEN, IN to filter and compare data in SQL queries. Examples are provided for many of the commands and clauses.

    Copyright:

    © All Rights Reserved
    Download as DOCX, PDF, TXT or read online from Scribd
    Download as docx, pdf, or txt
    47 views8 pages

    Table 2-1 Core SQL:2003 Commands

    Uploaded by

    Zoran Vasic
    The document lists SQL commands and clauses that are part of the SQL:2003 standard. It includes commands for data definition, manipulation, and transaction control. It also covers clauses for selection like WHERE, ORDER BY, JOIN, UNION and operators like LIKE, BETWEEN, IN to filter and compare data in SQL queries. Examples are provided for many of the commands and clauses.

    Copyright:

    © All Rights Reserved
    Download as DOCX, PDF, TXT or read online from Scribd
    Download as docx, pdf, or txt
    of 8

    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 Orders.OrderID, Orders.OrderDate, Customers.CustomerName
    FROM Customers, Orders
    WHERE Customers.CustomerName="Around the Horn" AND
    Customers.CustomerID=Orders.CustomerID;

    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 Orders.OrderID, Customers.CustomerName, Orders.OrderDate
    FROM Orders
    INNER JOIN Customers
    ON Orders.CustomerID=Customers.CustomerID;

    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 Customers.CustomerName, Orders.OrderID
    FROM Customers
    INNER JOIN Orders
    ON Customers.CustomerID=Orders.CustomerID
    ORDER BY Customers.CustomerName;

    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;

    You might also like