Scribd Logo
Upload

Welcome to Scribd!

  • 6 views

    SQL Commands

    Uploaded by

    amajobe34

    Copyright:

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

    SQL Commands

    Uploaded by

    amajobe34
    6 views29 pages

    Original Title

    SQL commands

    Copyright

    © © All Rights Reserved

    Available Formats

    PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    6 views29 pages

    SQL Commands

    Uploaded by

    amajobe34

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    You are on page 1of 29

    SQL Assessment Commands

    Query Command StatementExamples


    • SELECT
    • INSERT
    • AND
    • FROM
    • IF
    • WHERE
    • OR
    • DELETE
    • DROP
    • ALTER
    SELECT Command Statement
    • The SELECTstatement is used to select data from adatabase

    Examples:
    The following SQLstatement selects the "CustomerName" and "City" columns from
    the "Customers" table:

    SELECTCustomerName, City FROMCustomers;

    The following SQLstatement selects all the columns from the "Customers"table:

    SELECT* FROM Customers;


    WHERE Command Statement
    • The WHEREstatement is used to filter records. The WHEREclause is used to extract
    records that fulfil a specified criteriaonly.

    Examples:
    The following SQLstatement selects all the customers from the country "Mexico", in the
    "Customers" table:

    SELECT* FROMCustomers
    WHERECountry='Mexico’;

    However, numeric fields should not be enclosed in quotes:

    SELECT* FROMCustomers
    WHERE CustomerID=1;
    WHERE Clause Operators
    Operator Description

    = Equal
    <> Not Equal
    > Greater than
    < Less than
    >= Greater than or equal
    <= Less than orequal
    BETWEEN Between an inclusive range
    LIKE Search for a pattern
    IN Tospecify multiple possible values for a column
    AND CommandStatement
    • The AND and ORoperators are used to filter records based on more than
    one condition:
    • The AND operator displays a record if all the conditions separated by AND isTRUE:

    Example:
    The following SQLstatement selects all fields from "Customers"where
    country is "Germany" AND city is"Berlin":

    SELECT* FROM Customers


    WHERECountry='Germany' ANDCity='Berlin';
    OR Command Statement
    • The AND and ORoperators are used to filter records based on more than
    one condition:
    • The ORoperator displays a record if any of the conditions separated by ORis TRUE.

    Examples:
    The following SQLstatement selects all fields from "Customers" where cityis
    "Berlin" OR"München":

    SELECT* FROM Customers


    WHERECity='Berlin' ORCity='München';
    NOT Command Statement
    • The NOToperator displays a record if the condition(s) is NOTTRUE:

    Examples:
    The following SQLstatement selects all fields from "Customers" where
    country is NOT"Germany":

    SELECT* FROMCustomers
    WHERENOTCountry='Germany';
    Combining AND, OR and NOT operators
    • You can also combine the AND, ORand NOToperators:

    Examples:
    The following SQLstatement selects all fields from "Customers" where country is
    "Germany" AND city must be "Berlin" OR"München":

    SELECT* FROM Customers


    WHERECountry='Germany' AND (City='Berlin' ORCity='München’);

    The following SQLstatement selects all fields from "Customers" where country is
    NOT"Germany" and NOT"USA":

    SELECT* FROM Customers


    WHERENOTCountry='Germany' AND NOTCountry='USA';
    INSERT INTO CommandStatement
    • The INSERTINTO statement is used to insert new records in a table:

    Syntax:
    INSERTINTO table_name (column1, column2, column3, ...)
    VALUES(value1, value2, value3, ...);

    Examples:
    The following SQLstatement inserts a new record in the "Customers" table:

    INSERTINTO Customers (CustomerName, ContactName, Address, City, PostalCode,


    Country)
    VALUES('Cardinal', 'Tom B. Erichsen', 'Skagen 21', 'Stavanger', '4006', 'Norway');
    INSERT INTO only specified tablecolumns
    • It is also possible toonly insert data in specific columns.

    Examples:
    The following SQLstatement will insert a new record, but only insert
    data in the "CustomerName", "City", and "Country" columns:

    INSERTINTO Customers (CustomerName , City, Country)


    VALUES('Cardinal', 'Stavanger', 'Norway’);
    SQL NULL Values
    • A field with a NULLvalue is a field with no value.

    Note:
    A NULL value is different from a zero value or a field that contains
    spaces. A field with a NULL value is one that has been left blank during
    record creation.
    UPDATE Command Statement
    • The UPDATEstatement is used to modify the existing records in a table.

    Example:
    The following SQLstatement updates the first customer (CustomerID = 1)
    with a new contact person and a new city (Alfred and Frankfurt are new
    values):

    UPDATECustomers
    SETContactName = 'Alfred Schmidt', City= 'Frankfurt'
    WHERECustomerID =1;
    UPDATE Multiple Records
    • It is the WHEREclause that determines how many records that will be
    updated:

    Example:
    The following SQLstatement will update the contactname to"Juan" for all
    records where country is"Mexico":

    UPDATECustomers
    SETContactName='Juan'
    WHERECountry='Mexico’;

    Note: Missing out the WHERE statement will alter every value under SET
    DELETE Command Statement
    • The DELETEstatement is used to delete existing records in atable.

    Example:
    The following SQLstatement deletes the customer “John Smith" from the
    "Customers" table:
    DELETE FROM
    DCustomers
    WWHERECustomerName=‘John
    Smith’;
    Note: To delete all from a table, use:

    DELETE* FROMtable_name;
    CREATE DATABASE Command Statement
    • The CREATEDATABASEstatement is used to create a new SQL
    database:

    Syntax:
    CREATEDATABASE databasename;

    Example:
    CREATEDATABASEtestDB;
    DROP DATABASE CommandStatement
    • The DROPDATABASEstatement is used to drop an existing SQLdatabase:

    Syntax:

    DROPDATABASEdatabasename;

    Example:

    DROPDATABASEtestDB;
    CREATE TABLE Command Statement
    • The CREATETABLEstatement is used to create a new table in a
    database:

    Syntax:
    CREATETABLEtable_name (
    column1 datatype,
    column2 datatype,
    column3 datatype,
    ....
    );
    CREATE TABLEcontinued…
    Example:
    CREATETABLEtesttable (
    PersonID int,
    LastName varchar(255),
    FirstName varchar(255),
    Address varchar(255),
    City varchar(255)
    );
    DROP TABLE CommandStatement
    • The DROPTABLEstatement is used to drop an existing table in a
    database.

    Syntax:
    DROPTABLEtable_name;

    Example:
    DROPTABLEtesttable;
    TRUNCATE TABLE Command Statement
    • The TRUNCATETABLEstatement is used to delete the data inside a
    table, but not the tableitself.

    Syntax:
    TRUNCATETABLEtable_name;

    Example:
    TRUNCATETABLEtesttable;
    ALTER TABLE Command Statement
    • The ALTERTABLEstatement is used to add, delete, or modify columns
    in an existing table.
    • The ALTERTABLEstatement is also used to add and drop various
    constraints on an existing table.

    Syntax:
    To add a column in a table, use the followingsyntax:

    ALTERTABLEtable_name
    ADD column_namedatatype;
    ALTER TABLEcontinued…
    Syntax:
    To delete a column in a table, use the following syntax:

    ALTERTABLEtable_name
    DROPCOLUMNcolumn_name;

    Examples:

    ALTERTABLEtesttable
    ADD DateOfBirth date;
    ALTER TABLEcontinued…
    Examples:
    The following deletes the column named "DateOfBirth" in the “testtable"
    table:

    ALTERTABLEPersons
    DROPCOLUMNDateOfBirth;

    The following changes the data type of the column named "DateOfBirth" in
    the “testtable" table (date to year):

    ALTERTABLEPersons
    ALTERCOLUMN DateOfBirth year;
    SQL Constraints Examples
    • SQLconstraints are used to specify rules for the data in atable:

    • The following constraints are commonly used in SQL:


    • NOT NULL - Ensures that a column cannot have a NULLvalue
    • UNIQUE - Ensures that all values ina column are different
    • PRIMARY KEY - A combination of a NOTNULL and UNIQUE. Uniquely
    identifies each row in atable
    • FOREIGN KEY - Uniquely identifies a row/record in anothertable
    • CHECK - Ensures that all values in a column satisfies a specificcondition
    • DEFAULT - Sets a default value for a column when no value isspecified
    • INDEX - Used to create and retrieve data from the database very quickly
    PRIMARY KEYConstraint
    • The PRIMARY KEYconstraint uniquely identifies each record in adatabase
    table.
    • Primary keys must contain UNIQUE values, and cannot contain NULL
    values.

    PRIMARY KEY on CREATETABLE:


    CREATETABLEtesttable (
    ID int NOTNULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    PRIMARY KEY(ID)
    );
    PRIMARY KEYcontinued…
    PRIMARY KEY on ALTERTABLE:
    ALTERTABLEtesttable
    ADD PRIMARY KEY(ID);

    DROP a PRIMARY KEY:


    ALTERTABLEtesttable
    DROPPRIMARYKEY;
    FOREIGN KEYConstraint
    • A FOREIGN KEYis a key used to link two tables together.
    • A FOREIGNKEYis a field (or multiple) in one table that refers to the
    PRIMARY KEYin another table:

    FOREIGN KEY on CREATETABLE:


    CREATETABLEOrders (
    OrderID int NOTNULL,
    OrderNumber int NOTNULL,
    PersonID int,
    PRIMARY KEY(OrderID),
    FOREIGNKEY(PersonID) REFERENCEStesttable(PersonID)
    );
    FOREIGN KEYcontinued…
    FOREIGN KEY on ALTERTABLE:
    ALTERTABLEOrders
    ADD FOREIGNKEY(PersonID) REFERENCEStesttable(PersonID);

    DROP a FOREIGN KEY:


    ALTERTABLEOrders
    DROPFOREIGNKEYFK_PersonOrder;

    You might also like