sql_samples.htm
来自「Absolute Database 5.12 src. Absolute Da」· HTM 代码 · 共 620 行 · 第 1/3 页
HTM
620 行
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title>SQL samples</title>
<meta http-equiv="Content-Type"
content="text/html; charset=iso-8859-1">
</head>
<body bgcolor="#dedede">
<table height="252" border="1" cellpadding="1" cellspacing="1">
<tbody>
<tr>
<td width="214"><div align="center"><font size="+2">File</font></div></td>
<td width="437"><div align="center"><font size="+2">SQL</font></div></td>
<td width="308"><div align="center"><font size="+2">Description</font></div></td>
</tr>
<tr>
<td>Simple_Select.sql</td>
<td>select * from employee</td>
<td>This sample illustrates use of simple data retrieval from single table.</td>
</tr>
<tr>
<td>Distinct.sql</td>
<td>select distinct FirstName from employee</td>
<td>This sample illustrates use of DISTINCT keyword. The query returns all
names from the field "FirstName" excluding duplicates.</td>
</tr>
<tr>
<td> Select_Top.sql</td>
<td> select Top 5,4 * from venues </td>
<td> This sample illustrates use of TOP option. The query retrieves first
five entries beginning with 4th.</td>
</tr>
<tr>
<td> Select_Order_By.sql</td>
<td> <p>select Event_Name, Venue, Ticket_Price<br>
from Events e, Venues v <br>
where e.VenueNo = v.VenueNo <br>
order by Ticket_Price asc, Venue desc </p></td>
<td> This example illustrates use of ORDER BY clause. The query lists all
events and venues where they take place. Result dataset is sorted by ticket
price (ascending) and venue name (descending order).</td>
</tr>
<tr>
<td> Select_Union.sql</td>
<td> select Event_Name from events where VenueNo<3 <br>
union <br>
select Event_Name from events where VenueNo>8 </td>
<td> This sample illustrates use of UNION keyword. In this query results
of two "select" statements are combined in single dataset.</td>
</tr>
<tr>
<td> <p>Select_Intersect.sql</p></td>
<td> select VenueNo from Venues <br>
intersect corresponding by (VenueNo) <br>
select VenueNo fromEvents </td>
<td> This sample illustrates use of INTERSECT keyword. The query shows only
those VenueNo that are present in both tables.</td>
</tr>
<tr>
<td> Select_Having.sql</td>
<td> select VenueNo, Avg(Ticket_Price) from events <br>
group by VenueNo <br>
Having Event_Date<now </td>
<td> This sample illustrates use of HAVING clause. The query retrieves average
ticket price for every venue using events that happened before.</td>
</tr>
<tr>
<td> Select_Group_By.sql</td>
<td> select FirstName, count(FirstName) cnt <br>
from Employee <br>
group by FirstName <br>
order by cnt desc </td>
<td> This example illustrates use of GROUP BY keywords. After performing
this query the resulting dataset will contain names of employees and a
number - how many times this name is met in the table.</td>
</tr>
<tr>
<td> Except_Corresponding.sql</td>
<td> select * from Venues <br>
except corresponding by (VenueNo) <br>
select * from Events </td>
<td> This query illustrates use of EXCEPT keyword. In the example below
dataset returned will contain all entries from field "VenueNo" that exist
in table "Venues" but do NOT exist in table "Events".</td>
</tr>
<tr>
<td> Expr_Arithmetic.sql</td>
<td> select (ticket_price+10)*2 as price from events </td>
<td> This sample illustrates use of arithmetic functions in queries. In
this query for every entry value of field "ticket_price" is added to 10
and then multiplied by two. The result column is given name "price".</td>
</tr>
<tr>
<td> Select_Different_DB.sql</td>
<td> select Event_Name, Venue, Ticket_Price <br>
from "C:\Program Files\AbsoluteDatabase\Demos\Data\Demos.abs".Events e,
"C:\Program Files\AbsoluteDatabase\Demos\Data\Demos.abs".Venues v <br>
where e.VenueNo = v.VenueNo </td>
<td> This example illustrates get data from TWO DATABASES IN SAME QUERY.
The query lists all events, event prices and venues where they take place.
Note that you can specify two different databases.</td>
</tr>
<tr>
<td> Select_From_2_Tables.sql</td>
<td> select Event_Name, Venue <br>
from Events e, Venues v <br>
where e.VenueNo = v.VenueNo <br>
order by Event_Name, Venue </td>
<td> This example illustrates how to get data from two tables using EQUI-JOIN.
The query lists all events and venues where they take place. Notice that
only those entries will be shown that have corresponding values in both
tables. If event name has empty field "VenueNo" or its value is never
met in same field of table "Venues", then this event will not be included
in the resulting dataset.</td>
</tr>
<tr>
<td> Inner_Join.sql</td>
<td> select Event_Name, Venue <br>
from Events e <br>
inner join Venues v ON (e.VenueNo=v.VenueNo) <br>
order by Event_Name </td>
<td> This sample illustrates use of INNER JOIN statment. The query shows
events and venues where it will occur. Notice that if VenueNo corresponding
to event is not found in "venues" table, it will not be shown.</td>
</tr>
<tr>
<td> Left_Join.sql</td>
<td> select Event_Name, Venue <br>
from Events e <br>
left join Venues v on e.VenueNo = v.VenueNo <br>
order by Event_Name, Venue </td>
<td> This example illustrates use of LEFT JOIN action. This query retrieves
all event names and places where they happen. Venue is attached to event
name if value of field "VenueNo" is equal in both tables. If there is
no corresponding venue for event, then NULL is placed beside it.</td>
</tr>
<tr>
<td> Full_Join.sql</td>
<td> select Event_Name, Venue <br>
from Events e <br>
full join Venues v on e.VenueNo = v.VenueNo <br>
order by Event_Name, Venue </td>
<td> This example illustrates use of FULL JOIN action. This query retrieves
all event names and places where they happen. Venue is attached to event
name if value of field "VenueNo" is equal in both tables. If there is
no corresponding venue for event, then NULL is placed beside it. Likewise,
if a corresponding event for a venue couldn't be found, NULL is placed
to the left of it.</td>
</tr>
<tr>
<td> Natural_Full_Join.sql</td>
<td> select Event_Name, Venue <br>
from Events <br>
natural full join Venues <br>
order by Event_Name, Venue </td>
<td> This example illustrates use of FULL JOIN action with NATURAL option.
This query retrieves all event names and places where they happen. Venue
is attached to event name if value of same named field is equal in both
tables. If there is no corresponding venue for event, then NULL is placed
beside it. Likewise, if a corresponding event for a venue couldn't be
found, NULL is placed to the left of it.</td>
</tr>
<tr>
<td> Function_Avg.sql</td>
<td> select VenueNo, Avg(Ticket_Price) from events group by VenueNo </td>
<td> This sample illustrates use of AVG function. The query retrieves average
ticket price for every venue.</td>
</tr>
<tr>
<td> Function_Cast.sql</td>
<td> select Event_Name, Cast(Event_Date As String) from events </td>
<td> This sample illustrates use of CAST function. The query retrieves all
events and their dates as strings.</td>
</tr>
<tr>
<td> Function_Count.sql</td>
<td> select count(*) c from Employee </td>
<td> This sample illustrates use of COUNT function. This query counts number
of rows in the table "Employee". Having performed it, you will have one
column named "c" and one row in it containing number of rows in the table.</td>
</tr>
<tr>
<td> Function_Length.sql</td>
<td> select Event_Name, Length(Event_Name) from events </td>
<td> This sample illustrates use of LENGTH function. The query retrieves
events' names and their lengths.</td>
</tr>
<tr>
<td> Function_Lower.sql</td>
<td> select Event_Name, Lower(Event_Name) from events </td>
<td> This sample illustrates use of LOWER function. The query retrieves
events' names in normal capitalization and with all letters in lower case.</td>
</tr>
<tr>
<td> Function_Max.sql</td>
<td> select max(Capacity) from venues </td>
<td> This sample illustrates use of MAX function. The query retrieves maximal
capacity from venues.</td>
</tr>
<tr>
<td> Function_Min.sql</td>
<td> select min(Capacity) from venues </td>
<td> This sample illustrates use of MIN function. The query retrieves minimal
capacity from venues.</td>
</tr>
<tr>
<td> Function_Pos.sql</td>
⌨️ 快捷键说明
复制代码Ctrl + C
搜索代码Ctrl + F
全屏模式F11
增大字号Ctrl + =
减小字号Ctrl + -
显示快捷键?