sql_samples.htm

来自「Absolute Database 5.12 src. Absolute Da」· HTM 代码 · 共 620 行 · 第 1/3 页

      <td> select Event_Name, Pos('Finals', Event_Name) from events </td>
      <td> This sample illustrates use of POS function. The query retrieves events' 
        names address of position of the word 'Finals' in it.</td>
    </tr>
    <tr> 
      <td> Function_Substring.sql</td>
      <td> select SubString(Event_Name,2,9) from events </td>
      <td> This sample illustrates use of SUBSTRING function. The query retrieves 
        events' names where first letter is excluded as well as letters after 
        tenth.</td>
    </tr>
    <tr> 
      <td> Function_Sum.sql</td>
      <td> select VenueNo, Sum(Ticket_Price) from events group by VenueNo </td>
      <td> This sample illustrates use of SUM function. The query retrieves sum 
        of ticket prices for every venue.</td>
    </tr>
    <tr> 
      <td> Function_Sysdate_Now.sql</td>
      <td> select Event_Name, Event_Date, sysdate as CurDate <br>
        from events <br>
        where Event_Date < now </td>
      <td> This sample illustrates use of SYSDATE and NOW functions. The query 
        retrieves all events that have occured already. Notice that functions 
        NOW and SYSDATE do same things and you can use either.</td>
    </tr>
    <tr> 
      <td> Function_ToDate.sql</td>
      <td> select Event_Name, Event_Date, sysdate as CurDate from events <br>
        where Event_Date < ToDate('11/19/2003 10:15:30 am','MM/DD/YYYY hh:nn:ss 
        ampm') </td>
      <td> This sample illustrates use of TODATE function. The query retrieves 
        all events that have occured before the specified date.</td>
    </tr>
    <tr> 
      <td> Function_ToString.sql</td>
      <td> select Event_Name, ToString(Event_Date, <br> "'Event date is 'mm/dd/yyyy 
        hh24:nn:ss '!'") Formated_Date from events <br>
        where Event_Date < now </td>
      <td> This sample illustrates use of TOSTRING function. The query retrieves 
        all events that have occured already together with formatted date.</td>
    </tr>
    <tr> 
      <td> Function_Trim.sql</td>
      <td> select Trim(' '+Event_Name+' ') from events </td>
      <td> This sample illustrates use of TRIM function. The query retrieves events' 
        names. Although some blanks are appended on both sides of column "Event_Name", 
        function trim removes them. Use LTRIM or RTRIM if you want to remove blanks 
        from on side only.</td>
    </tr>
    <tr> 
      <td> Str_Concat.sql</td>
      <td> select FirstName + ' ' + LastName as Name from employee </td>
      <td> This sample illustrates how to use string concatenation. The query 
        constructs a column "Name" using field "FirstName", then blank space, 
        then field "LastName" from table "Employee".</td>
    </tr>
    <tr> 
      <td> Str_Concat_2.sql</td>
      <td> select FirstName || ' ' || LastName as Name from employee </td>
      <td> This sample illustrates how to use string concatenation. The query 
        constructs a column "Name" using field "FirstName", then blank space, 
        then field "LastName" from table "Employee".</td>
    </tr>
    <tr> 
      <td> Simple_Case.sql</td>
      <td> select event_name, case VenueNo <br>
        when 2 then 'Memorial Stadium' <br>
        when 4 then 'Coliseum' <br>
        else 'unknown venue: VenueNo='+VenueNo <br>
        end as Venue<br>
        from events </td>
      <td> This sample illustrates use of simple CASE function. For each row in 
        the dataset being retrieved, field "VenueNo" is compared to 2, and, if 
        it is true, the string 'Memorial Stadium' appears to the right of event 
        name. If it is not true, then "VenueNo" is compared to 4, and if it equals 
        to 4, string 'Coliseum' appears. If "EventNo" matches neither 2 nor 4, 
        ELSE part of the statement is executed and 'unknown venue: VenueNo='is 
        shown followed by value of the field "VenueNo".</td>
    </tr>
    <tr> 
      <td> Searched_Case.sql</td>
      <td> select Event_Name, Case <br>
        when Event_Date<now then 'In the past' <br>
        when Event_Date>now then 'In the future' <br>
        else 'Right now' end as 'When' from events </td>
      <td> This sample illustrates use of SEARCHED CASE construction. The query 
        retrieves all events and notification whether it had happened already.</td>
    </tr>
    <tr> 
      <td> Operator_Like.sql</td>
      <td> select * from events where Event_Name Like '%men%' </td>
      <td> This sample illustrates use of LIKE operator. The query retrieves all 
        records from "events" where "Event_Name" contains substring 'men'.</td>
    </tr>
    <tr> 
      <td> Operator_Between.sql</td>
      <td> select * from events where VenueNo Between 3 and 8 </td>
      <td> This sample illustrates use of BETWEEN operator. The query retrieves 
        all records from "events" where VenueNo is more than 2 and less than 9.</td>
    </tr>
    <tr> 
      <td> In_Operator.sql</td>
      <td> select * from Employee where FirstName in ('Roberto', 'Roger') <br>
        order by FirstName </td>
      <td> This example shows how to use keyword IN. The query retrieves all data 
        from table "Employee" concerning employees with names 'Roberto' or 'Roger'.</td>
    </tr>
    <tr> 
      <td> Function_Upper.sql</td>
      <td> select Event_Name, Upper(Event_Name) from events </td>
      <td> This sample illustrates use of UPPER function. The query retrieves 
        events' names in normal capitalization and with all letters in upper case.</td>
    </tr>
    <tr> 
      <td> Subquery_where_exists.sql</td>
      <td> select Venue from venues v where not exists (select * from events <br>
        where VenueNo=v.VenueNo) </td>
      <td> This sample demonstrates use of subquery in where clause together with 
        EXISTS keyword. The query returns all venues where no event will take 
        place.</td>
    </tr>
    <tr> 
      <td> Subquery_where_in.sql</td>
      <td> select Venue from venues where VenueNo in (select VenueNo from events) 
      </td>
      <td> This sample illustrates use of subquery in where clause together with 
        IN keyword. The query returns only those venues where some event will 
        occur.</td>
    </tr>
    <tr> 
      <td> Subquery_where_equal.sql</td>
      <td> select Venue from venues v where 1 = (select count(*) from events where 
        VenueNo=v.VenueNo) </td>
      <td> This sample illustrates use of subquery in where clause when result 
        being evaluated is compared to a constant. This query shows all venues 
        where only one event will occur.</td>
    </tr>
    <tr> 
      <td> Subquery_where_compare_any.sql</td>
      <td> select e.Event_Name, v.Capacity from events e, venues v <br>
        where e.VenueNo=v.VenueNo and v.Capacity > any (select Capacity from venues 
        where VenueNo between 3 and 4) </td>
      <td> This example illustrates use of keyword ANY in where clause. This query 
        returns events that will occur on venues with capacity more than in venue 
        3 or 4.</td>
    </tr>
    <tr> 
      <td> Subquery_where_compare_all.sql</td>
      <td> select e.Event_Name, v.Capacity from events e, venues v <br>
        where e.VenueNo=v.VenueNo and v.Capacity < all (select Capacity from venues 
        where VenueNo between 3 and 4) </td>
      <td> This example illustrates use of keyword ALL in where clause. This query 
        returns events that will occur on venues with capacity lower than in venues 
        3 and 4.</td>
    </tr>
    <tr> 
      <td> subquery_update.sql</td>
      <td> update Events e <br>
        set Ticket_price = (select Capacity from Venues where VenueNo=e.VenueNo)*2 
        <br>
        where Ticket_price <= 5 </td>
      <td> This sample demonstrates use of subquery in SET clause. The query works 
        as follows. First, only those entries are picked that have "Ticket_price" 
        5 of less. Then corresponding venue is taken, its capacity is multiplied 
        by two and set as the new ticket price. Notice that NULL fields are not 
        affected.</td>
    </tr>
    <tr> 
      <td> Subquery_select_as_field.sql</td>
      <td> select Venue, (select count(*) from Events where VenueNo = v.VenueNo) 
        EventsCount <br>
        from Venues v <br>
        order by EventsCount desc </td>
      <td> This sample demonstrates use of subquery representing a separate field. 
        For each entry in table "Venues" the query counts quantity of events from 
        table "Events" and places this number in a column named "EventsCount".</td>
    </tr>
    <tr> 
      <td> Subquery_select_from_select.sql</td>
      <td> select count(*) UniqueNamesCount from (select distinct FirstName <br>
        from Employee) </td>
      <td> This sample illustrates use of a subquery in FROM clause. The subquery 
        retrieves count of unique names in table "Employee", then function "count" 
        counts quantity of these names.</td>
    </tr>
    <tr> 
      <td> Subquery_Insert_Into.sql</td>
      <td> drop table venues2; <br>
        create table venues2 <br>
        ( VenueNo autoinc, Venue varchar(30), Capacity integer, Venue_Map graphic, 
        Remarks memo, primary key idxPrimaryKey (VenueNo) ); <br>
        insert into Venues2 <br>
        select * from venues v <br>
        where not exists (select * from events where VenueNo=v.VenueNo) </td>
      <td> This sample demonstrates use of subquery in INSERT INTO clause. The 
        first query in the script deletes table Venues2. Next one creates it. 
        The third query inserts into the new table all venues where no event will 
        take place. Notice that two subqueries here are enclosed one into another.</td>
    </tr>
    <tr> 
      <td> Alter_Table_Add.sql</td>
      <td> drop table MyTable;<br>
        create table MyTable (ID AutoInc, val1 Integer, val2 string(10));<br>
        insert into MyTable (val1, val2) values (2,'2');<br>
        insert into MyTable (val1, val2) values (4,'3');<br>
        insert into MyTable (val1, val2) values (5,'9');<br>