Ingres corporation page basic sql exercises ingres corporation creating tables

Basic SQL Exercises
Contents

2		5

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

The	expected	result	should
show
© Ingres Corporation				Page 10

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:

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).

Basic SQL Exercises		© Ingres Corporation
1.	Display all the rows in the part table, sorted by description.
(9 rows)

2.		(1 row)

© Ingres Corporation Page 15

Basic SQL Exercises © Ingres Corporation

4.2 Data Manipulation

		© Ingres Corporation
6.
(3 rows)

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

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

(1 row)

3

Basic SQL Exercises		© Ingres Corporation
6	Verify that all rows are missing from the part table but DO NOT

7

Basic SQL Exercises © Ingres Corporation

4.4Joining Tables

1.

2.

© Ingres Corporation Page 22

	Create a view called vw_part_prices to display the all part numbers, descriptions, prices, catalogue numbers and vendor numbers and names for priceinfo entries. Verify your result by retrieving all the data; sort the result on description. (22 rows)

3.

SQL Functions

1.	How many vendors are located in the city of Sheffield?	(1 row)

2.

3.

4.	Find the total price for order number 3001. Rename the result column “total price”

(1 row)

5.

Basic SQL Exercises © Ingres Corporation

4.5.1Conversion and date functions

1.	Select the 4 rightmost letters of each part description.

2.

Basic SQL Exercises © Ingres Corporation

4.6Unions and Subqueries

1.

2.

© Ingres Corporation Page 26

Basic SQL Exercises
3.	Which parts are priced at the lowest price?

4.	Which parts are priced at lower than the average price?

4.6.1Changing Data Using Subqueries

1.

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;

3.

Basic SQL Exercises © Ingres Corporation

5.2Data Manipulation

2.	Check the result by retrieving the part information for part number 10.

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;

7.

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;

3.

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

1.

commit;

commit;

© Ingres Corporation Page 36

1.

commit;

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

commit;

© Ingres Corporation Page 37

commit;

2.	Find the unit price and quantity information for order number 3001. All the information in the podetail table.

commit;

4.	Find the total price for order number 3001. Rename the result column “total price”.

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

1.	Display the order numbers, dates and status for all orders, completed and pending. Remember that all orders in the pohistory table are completed. (Hint: Use a union).

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

6.

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‟
);