Intro to SYBASE: Queries for Data Retrieval

Queries in SYBASE

This lab will go through the following topic by examples:

How to retrieve some information from your database

After going through the examples below, you should be able to do the following exercises related to your own Database

(a) Report all outstanding orders of components.
(b) Report all suppliers who supply a given part number (specify a suitable part number according to your own data).
(c) Report all parts that will be required to make a specific product, and the amounts required for each component. You must be able to give all details of each component in this response.
(d) Report storage location of each component required for a specific product.

The structure of Queries in SYBASE is a superset of the SQL you have learnt in your lectures. Therefore, any query written in the SQL taught will work. Plus, you can do a lot of additional things.

For all the examples, I shall use the tables in the SYBASE Online manual.

Simple retrieval queries.

SELECT *
FROM publishers

NOTES:

Assumes that a table called publishers was created and already has some data in it.

Result:

  pub_id  pub_name              city         state  

  -----   --------------        ---------    ----  

  0736    New Age Books         Boston       MA 

  0877    Binnet & Hardley      Washington   DC  

  1389    Algodata Infosystems  Berkeley     CA  

   

  (3 rows affected)

You may give alternate names to the attributes in the displayed results. This is quite useful when you want to make outputs have simplified meanings for some users.

SELECT Publisher = pub_name, pub_id
FROM publishers

NOTES:
Gives a new name, "Publisher", to the attribute pub_id.

Result:

 Publisher                 pub_id  

 ----------------------    ------  

 New Age Books             0736  

 Binnet & Hardley          0877  

 Algodata Infosystems      1389  

   

 (3 rows affected)

Set the option quoted identifier to on within sybase.
1> set quoted_identifier on
2> go
1>

And now, you can use some more advanced options in your outputs. You can give new names to attributes which have spaces in them, as well as you can use any of the Sybase reserved functions. The following example shows how.

Suppose you have a table, sales_data, with the following data:

 name    sales       

 ------  -------- 

 jack      10 

 jill      20 

 james     40

Query:
SELECT "Sum of Sales" = sum( sales)
FROM sales_data

Result:

 Sum of Sales 

 ------------ 

     70 



 One row affected.

Notes:
Be careful how you use such functions, like sum, in a query. since the output is displayed meaningfully only in one row, if there is an attribute which has different values for each of the rows that is sum-med, it will generate misleading outputs.

You may use all kinds of arithematic operators in your queries. For the titles table, consider the following query:

SELECT title_id, (total_sales * price - advance) /2
FROM titles

 title_id                            

 --------   --------------   

 BU1032        38,429.53   

 BU2075        22,926.89   

 MC2222        20,309.84   

 MC3026             NULL  

 PC8888        36,950.00   



 etc..

You can also make your output look a little nicer by adding constant strings to each line of output as follows:

SELECT 'Total income is', Revenue = price * total_sales, 'for', Book# = title_id
FROM titles
WHERE title_id like 'B%'

Result:

                 title_id       Revenue                     

---------------- -------- ---   ---------  

Total income is  BU1032   for   38,429.53   

total income is  BU2075   for   22,926.89

Of course, the most interesting part of the SELECT clause is the WHERE clause, which allows us to choose only those rows that we are interested in. We look at some examples of the things we can do in the WHERE clause.

The search conditions can specify the following, or any logical combination (using AND, OR, or NOT) of these:

The where clause in a select statement specifies the criteria for exactly which rows are retrieved. The general format is:

SELECT  select_list    

FROM   table_list    

WHERE  search_conditions

Search conditions, or qualifications, in the where clause include:

Comparison operators (=, <, >, etc.)

   where advance * 2 > total_sales * price

Ranges ( between and not between )

   where total_sales between 4095 and 12000

Lists ( in, not in )
```
   where state in ("CA", "IN", "MD") 
```
Character matches ( like and not like )
```
   where phone not like "415%"  
```
Unknown values ( is null and is not null )
```
   where advance is null 
```

Combinations of these ( and , or )

   where advance < 5000 or total_sales between 2000 
         and 2500

Some extra information about string comparisons using LIKE :

The 'LIKE' keyword is used to match strings which are similar to a given text string. The comparison can use single- or multiple-character wildcards.

The "%" character in a string will match one or more characters (sort of like the "*" DOS commands.)

The "_" will match a single character (like the "?" in DOS commands.)

You can specify a character to be one out of a few, just as in PERL string matching, using [list-of-characters].

You can also specify to NOT match a set of characters inside square brackets using [^list-of-characters-to-not-match].

The examples are shown below:

like 'Mc%' search for every name that begins with the letters ``Mc'' (McBadden).
like '%inger' searches for every name that ends with ``inger'' (Ringer, Stringer).
like '%en%' searches for every name that has the letters ``en'' in it (Bennet, Green, McBadden).
like '_heryl' searches for every six-letter name ending with ``heryl'' (Cheryl).
like '[CK]ars[eo]n' searches for ``Carsen,'' `` Karsen,'' ``Carson,'' and ``Karson'' (Carson).
like '[M-Z]inger' searches for all the names ending with ``inger'' that begin with any single letter from M to Z (Ringer).
like 'M[^c]%' searches for all names beginning with ``M'' not having ``c'' as the second letter.

This query finds all the phone numbers in the authors table that have 852 as the area code:

SELECT phone  

FROM   authors  

WHERE  phone like '852%'

You can use NOT LIKE with the same wildcards. To find all the phone numbers in the authors table that do not have 415 as the area code, you could use either of these queries. They are equivalent.

SELECT phone  

FROM   authors  

WHERE  phone NOT LIKE '415%'

Should produce the same output as the following query:

SELECT phone  

FROM   authors  

WHERE NOT phone like '415%'

You can, of course, mix where-conditions in any query by simply using any combination of the logical operators. As usual, you must be careful about the sequence of the operator evaluation, or you will get unexpected results !

Logical Operator Precedence

Arithmetic and bitwise operators are handled before logical operators. When more than one logical operator is used in a statement, not is evaluated first, then and , and finally or.

For example, the following query finds all the business books in the titles table, no matter what their advances are, as well as all psychology books that have an advance greater than $5,500. The advance condition pertains to psychology books and not to business books because the and is handled before the or.

SELECT title_id, type, advance  

FROM titles  

WHERE type = "business" OR type = "psychology"  

      AND advance >5500

Result:

 title_id  type         advance  

 --------  ----------  ----------  

   

 BU1032    business     5,000.00  

 BU1111    business     5,000.00  

 BU2075    business    10,125.00  

 BU7832    business     5,000.00  

 PS1372    psychology   7,000.00  

 PS2106    psychology   6,000.00  



   
 (6 rows affected)

You can change the meaning of the query by adding parentheses to force evaluation of the or first. This query finds all business and psychology books that have advances over $5,500:

SELECT title_id, type, advance  

FROM  titles  

WHERE (type = "business" OR type = "psychology")  

      AND advance >5500

Result:

 title_id  type         advance  

 --------  ----------   ---------  

 BU2075    business     10,125.00  

 PS1372    psychology    7,000.00  

 PS2106    psychology    6,000.00  




 (3 rows affected)