LAB 3: Using SQL
1. Adding Referential Constraints
Use the ALTER TABLE command to:
-
make manager number a foreign key in the Portfolio table.
-
make portfolio number and stock symbol foreign keys in the Holding table.
The view: FOREIGN_KEYS contains data on all foreign keys you have
created (this view is created from the user_constraints and user_cons_columns
tables). Enter the following commands to show the details of the
foreign keys you have created:
select * from foreign_keys where table_name = 'PORTFOLIO';
select * from foreign_keys where table_name = 'HOLDING';
2. Using the CHECK clause to specify validation rules:
Use the ALTER TABLE command to add a check clause on the SALARY column
in the Manager table to ensure the salary value is at least $40,000.
Attempt to insert the following row into the Manager table: M4,
Granger, 35000.
3. Using the SELECT statement:
Code select statements for the following queries:
-
display the entries in the stock table in descending order of price
-
display all entries in the stock table where the stock name starts with
a C
-
display the portfolio number and stock symbol of all entries in the holding
table that have more than 600 shares
-
display the average price of stock in the stock table
-
using a join, display the portfolio number, cash, stock symbol and number
of shares from the portfolio and holding tables
-
using a subquery, display the stock name of all stock held in portfolio
P008
4. Creating views:
Create the following views:
-
create a view called share_price that contains the portfolio number, stock
symbol and current price of the stock.
-
create a view called manager_cash that contains the manager number and
the total cash in portfolios managed by the manager.