Ingres corporation page basic sql exercises ingres corporation creating tables
Basic SQL Exercises
Contents
© Ingres Corporation
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
|
© Ingres Corporation |
---|
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:
1. 2. |
||
---|---|---|
eg | ||
|
sql trainingdb < data.sql
The | expected | result | should | |
---|---|---|---|---|
show | ||||
|
Page 10 |
How to start Ingres?
oIf the ingres system is not running, type ingstart
![]() |
|
|
---|---|---|
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:
|
||||||||||
---|---|---|---|---|---|---|---|---|---|---|
![]() |
![]() |
|||||||||
|
||||||||||
4.1Simple Retrievals
Either
a.Using VisualSQL, launch Ingres VisualSQL against the trainingdb database (Start Programs Ingres Ingres VisualSQL).
|
© Ingres Corporation | |
---|---|---|
1. |
|
|
(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)
1 2 |
---|
3 |
---|
![]() |
---|
|
© Ingres Corporation | |
---|---|---|
6 |
|
|
7 |
---|
Basic SQL Exercises © Ingres Corporation
4.4Joining Tables
1. |
---|
2. |
---|
3. | (? rows) |
---|
© Ingres Corporation Page 22
|
---|
3. |
---|
SQL Functions
© Ingres Corporation
1. | How many vendors are located in the city of Sheffield? | (1 row) |
---|
2. |
---|
3. |
---|
4. |
|
---|
(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
|
||
---|---|---|
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. |
|
---|
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. |
|
---|
commit;
4. |
|
---|
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. |
|
---|
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‟
);