Scribd Logo
Upload

Welcome to Scribd!

  • 3 views

    Sqlnotes 1

    Uploaded by

    goldbergdc9

    Copyright:

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

    Sqlnotes 1

    Uploaded by

    goldbergdc9
    3 views2 pages

    Original Title

    sqlnotes1

    Copyright

    © © All Rights Reserved

    Available Formats

    DOCX, PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    Copyright:

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

    Sqlnotes 1

    Uploaded by

    goldbergdc9

    Copyright:

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

    SQL Summary by Kate Lange

    Note: Please use these notes as a reminder of work already learnt, do not study from these notes
    alone.

    SELECT:

     SELECT* (wildcard selects all fields)


     SELECT TOP <n>
     SELECT <field1>, <field2>
     SELECT DISTINCT (removes dupicates)
     SELECT <Calculation> AS <Name of new Field>
     SELECT INT <value>
     SELECT ROUND <Value>
     SELECT MOD <value> (modulus)
     SELECT INT(RND <field/Value> )* (max-min +1) + min
     SELECT LEFT, RIGHT (<Field>, <n>)
     SELECT MID (<Field>, <n1> <n2>)
     SELECT LEN <Field> ( gives number of characters)
    o SELECT Surename & ‘ ’ & LEFT (FirstName, 1) AS FullNameAndInitals
     SELECT MAX/MIN (<field>) AS <new Alias> (give larges and smallest numbers in their fields
    respectively)
     SELECT SUM (<field>) AS <new Alias> (adds all values in a field)
     SELECT COUNT (<field>) As <new Alias> (Counts values in a field)
     SELECT AVG (<field>) As <new Alias> (averages all numbers in a field)

    FROM:

     <tableName>

    WHERE:

     WHERE <condition> (something like totalPages > 3


     WHERE <field> BETWEEN <condition> AND <condition>
     WHERE <field> IN (values) AND NOT IN (values)
     WHERE <field> LIKE ‘abc*’ (must start with abc but after it can end in anything cause wild
    card *)
     WHERE <Field> <Condition (<; > ect)> (<SELECT e.g. AVG(Pages)>)

    ORDER BY:

     <field1>, <field2>
     ASC and DESC

    GROUP BY:

     Allows to order via an aggregate function. IE use Alias


     HAVING works the same why that WHERE does for SELECT

    Calculating Time working with years

     SELECT
     YEAR/MONTH/DAY/HOUR/MINUTE/SECOND (dateTimeValue)
    ie YEAR (<field Value>) isolates the year from that value
     NOW () can also be seen as a variable
     EG
    o SELECT DOB,
    YEAR (DOB) AS Year,
    (NOW ()- DOB)/ 365.25 AS ActualAge

    INSERT INTO:

    INSERT INTO <tableName>

    VALUES <Values>

    MULTIPLE RECORDS w/ EMBBEDDED SELECT

    INSERT INTO <tableName> <Fields>

    SELECT <fields>

    FROM <tableName>

    WHERE <Conditions>

    Eg:

    INSERT INTO PrintLogs (PDate, SureName,FirstName…)

    SELECT PDATE, ‘Soap’, ‘joe’….)

    FROME PrintLogs

    Where SureName = ‘Schlute’ AND FirstName = ‘Faith’

    This adds records for Joe that are exactly the same as Faiths

    UPDATE:

    UPDATE <tableName>

    SET <Field> = <value>,<Field> = <value>,…

    WHERE <condition>

    DELETE :

    DELETE FROM <tableName>

    WHERE <condition>

    You might also like