Ingres corporation page basic sql exercises ingres corporation creating tables

Basic SQL Exercises
Contents

© Ingres Corporation

Basic SQL Exercises © Ingres Corporation

5.4Joining Tables ...................................................................... 36 5.5Views Based on More than One Table ..................................... 37 5.6SQL Functions ..................................................................... 38 5.6.1 Conversion and date functions ...................................... 39 5.7Unions and Subqueries ......................................................... 40 5.7.1 Changing Data Using Subqueries ................................... 41

1.1Reference Material

The Ingres SQL reference guide (SQLREF) is available online at http://docs.ingres.com/

This self-paced training assumes that you have already installed Ingres.

To install Ingres, please download the Ingres installation media from:

2.1Extract setup Materials
The self-paced training pack contains this document and a set of setup files. Extract these files into an empty folder/directory and open a command prompt within this area.

Example

2.2.1Creating database and Connecting

1. Make sure Ingres is started by typing ingstart eg.

You will see the following screen:

2.2.2Building Tables

2.2.2.1Getting Help

This verifies that there are no tables available to use yet in this database.

Basic SQL Exercises © Ingres Corporation

2.2.2.2Creating Tables

created tables.

Verify that the tables have been created. The output should show:

sql trainingdb < data.sql

How to start Ingres?

oIf the ingres system is not running, type ingstart

	Training database does not exist? oTo check that the database trainingdb has been created, try

Basic SQL Exercises © Ingres Corporation

If the connection is unsuccessful, create the training database by following from steps 2.2.1 above.

Then retry running the setups from step 2.2.2.2 (above).

the following tables:

poheader PK orderrno integer 4 FK1
	podetail PK, FK2 PK, FK1 integer 4 integer 4

4.1Simple Retrievals

Either
a.Using VisualSQL, launch Ingres VisualSQL against the trainingdb database (Start  Programs  Ingres  Ingres VisualSQL).

© Ingres Corporation Page 15

Basic SQL Exercises © Ingres Corporation

4.2 Data Manipulation

10.Delete from the poheader table, those orders that are now duplicated

12.Change the invoiceno for order number 3010 to „inv00824‟.

(1 row)

Basic SQL Exercises © Ingres Corporation

4.4Joining Tables

© Ingres Corporation Page 22

SQL Functions

© Ingres Corporation

(1 row)

Basic SQL Exercises © Ingres Corporation

4.5.1Conversion and date functions

Basic SQL Exercises © Ingres Corporation

4.6Unions and Subqueries

© Ingres Corporation Page 26

4.6.1Changing Data Using Subqueries

End of exercise

For this reason, there might be alternative solutions to the solutions which are provided within this section.

5.1Simple Retrievals

select description
from part
where partno = 4;

commit;

select distinct partno
from priceinfo
where price = 230.45;

commit;

commit;

5.1.1Using Simple Views

create view vw_large_orders as
select * from podetail
where qty > 500;

commit;

Basic SQL Exercises © Ingres Corporation

5.2Data Manipulation

select *
from part
where partno = 10‟

insert into poheader (orderno
, orddate
, vendorno
, invoiceno)
values (3010
, „today‟
, 205
, „inv0101‟);

commit;

Basic SQL Exercises © Ingres Corporation

5. Vendor 205 (Furniture Heaven) is no longer supplying Part 5 (Elm Tables).

6. Check the result by retrieving all vendors who still supply part 5.

select *
from priceinfo
where partno = 5;

Basic SQL Exercises © Ingres Corporation

8. Add these rows of data from the poheader table into the pohistory. Do not worry about the pototal column at this point.

commit;

10.Delete from the poheader table, those orders that are now duplicated in the pohistory table (i.e. with a status of „c‟)

commit;

12.Change the invoiceno for order number 3010 to „inv00824‟.

commit;

© Ingres Corporation Page 33

2. Verify your results but DO NOT COMMIT YOUR CHANGES.

select *
from priceinfo
where vendorno = 201;

update priceinfo
set price = price + 100
where vendorno = 201;

select *
from priceinfo
where vendorno = 201;

select *
from part;

© Ingres Corporation Page 34

commit;

© Ingres Corporation Page 35

commit;

commit;

© Ingres Corporation Page 36

commit;

2. Verify your result by retrieving all the data; sort the result on description.

commit;

© Ingres Corporation Page 37

commit;

commit;

5. Use the view vw_part_prices to display the lowest price for each part with a description beginning with „Pine‟ (Note: there are some parts that do not have price information. These will be shown with a null price).

select description, min(price)
from vw_part_prices
where description like „Pine%‟
group by description;

commit;

5.7Unions and Subqueries

3. Which parts are prices at the lowest price?

select price
, partno
,vendorno
from priceinfo
where price =
( select min(price)
from priceinfo);

© Ingres Corporation Page 40

Basic SQL Exercises © Ingres Corporation

5.7.1Changing Data Using Subqueries

2. Check the result by retrieving all of the updated prices.

select vendorno
, price
from priceinfo
where vendorno in
(select vendorno
from vendor
where city = „Leeds‟
);