PRACTICE EXERCISE FOR SQL

TABLE DESIGN/STRUCTURE:

PRICE
Name Type
STOCK_ID INT(12) DATE_YYYYMMDD DATE
OPEN_PR FLOAT(24,2) HIGH FLOAT(24,2) LOW FLOAT(24,2)
CLOSE_PR FLOAT(24,2) VOLUME INT(12)

Nullable No
No
No

STOCK
Name Type
STOCK_ID INT(12) STOCK_NAME VARCHAR(10) REVENUE
DOUBLE(53,2) PRE_TAX_INCOME DOUBLE(53,2)
POST_TAX_INCOME DOUBLE(53,2)

Nullable No
No

TABLE DATA:

PRICE
STOCK_ID 1
1
2
2
3
3

DATE_YYYYMMDD OPEN_PR HIGH LOW CLOSE_PR VOLUME

2013-06-03
2013-05-31
2013-06-03
2013-05-31
2013-06-03
2013-05-31
447.78 452.36 442.48 450.72 13239052 452.5 457.1 449.5 449.74
13838340 863.54 873.97 855.02 867.63 2473631 868.12 877 867.52 871.22
2019407 26.37 26.62 26.13 26.39 16445040 26.2 26.6 26.09 26.3 23906901

STOCK
STOCK_ID 1
2
3

STOCK_NAME Apple Inc

Google Inc
Yahoo Inc

REVENUE PRE_TAX_INCOME POST_TAX_INCOME

43603000000 12905000000 9547000000
13969000000 3611000000 3324000000
114037000 203040000 173310000

CREATE TABLE Statement:

The following statement creates table PRICE, STOCK with the fields and
data types mentioned in the table CREATE TABLE PRICE

(
STOCK_ID INT(12) NOT NULL,
DATE_YYYYMMDD DATE NOT NULL,
OPEN FLOAT(24,2) NOT NULL,
HIGH FLOAT(24,2),
LOW FLOAT(24,2),
CLOSE FLOAT(24,2),
VOLUME INT(12)
);

CREATE TABLE STOCK

(
STOCK_ID INT(12) NOT NULL,
STOCK_NAME VARCHAR(10) NOT NULL,
REVENUE DOUBLE(53,2),

PRE_TAX_INCOME DOUBLE(53,2), POST_TAX_INCOME

DOUBLE(53,2), );

The following table is being created so that it can be used to test alter table
and delete table statements
CREATE TABLE ALTERDROPTEST1

(
STOCK_ID INT(12) NOT NULL,DATE_YYYYMMDD DATE NOT
NULL,
OPEN FLOAT(24,2) NOT NULL,HIGH FLOAT(24,2),
LOW FLOAT(24,2),CLOSE FLOAT(24,2),VOLUME INT(12)
);

ALTER TABLE Statement:

The following alter table statement is to rename the table name
ALTER TABLE ALTERDROPTEST1 RENAME TO ALTERDROPTEST;
The following alter table statement is to add a column flag to the table
ALTER TABLE ALTERDROPTEST ADD (flag char(1));
The following alter table statement is to drop a column flag to the table
ALTER TABLE ALTERDROPTEST DROP COLUMN flag;
The following alter table statement is to rename the column name
ALTER TABLE ALTERDROPTEST CHANGE DATE_YYYYMMDD TO
DATE_TIME DATE; The following alter table statement is to modify the
size and nullable property of column number ALTER TABLE
ALTERDROPTEST MODIFY VOLUME INT(15) NOT NULL;

DROP TABLE Statement:

The following drop table statement will delete the table from the database.
DROP TABLE ALTERDROPTEST
Setting up Constraints: Primary Key & Foreign Key:
The following create table statement will create Exchange table with
primary constraint. CREATE TABLE EXCHANGE
(EXCHANGE_ID INT(12) not null,
EXCHANGE_NAME VARCHAR(10),
SECTOR _NAME VARCHAR(20),
SECTOR _ID INT(12),
CONSTRAINT EXCHANGE_pk PRIMARY KEY (EXCHANGE_ID)

);

EXCHANGE
Name
EXCHANGE_ID
EXCHANGE_NAME SECTOR_NAME SECTOR_ID

Type
INT(12)
VARCHAR(10) VARCHAR(20) INT(12)

Null able Constraints No PRIMARY KEY

The following create table statement will create Equity table with primary
key and foreign key constraint.

CREATE TABLE EQUITY

(STOCK_ID INT(12) not null,
EXCHANGE_ID INT(12),
STOCK_NAME VARCHAR(20),
DATE_YYYYMMDD DATE,
PRICE FLOAT(24,2),
CONSTRAINT EQUITY_pk PRIMARY KEY (STOCK_ID),
CONSTRAINT EXCHANGE_fk FOREIGN KEY(EXCHANGE_ID)
REFERENCES EXCHANGE (EXCHANGE_ID)

);

EQUITY
Name Type
STOCK_ID INT(12) EXCHANGE_ID INT(12) STOCK_NAME
VARCHAR(20) DATE_YYYYMMDD DATE
PRICE FLOAT(24,2)
Null able No

Constraints PRIMARY KEY FOREIGN KEY

The following statements insert data into price table:

INSERT Statement:
INSERT INTO PRICE (STOCK_ID, DATE_YYYYMMDD, OPEN_PR,
HIGH, LOW, CLOSE_PR, VOLUME) VALUES (1,’2013-06-
03’,’447.78’,’452.36’,’442.48’,’450.72’, 13239052);
INSERT INTO PRICE (STOCK_ID, DATE_YYYYMMDD, OPEN_PR,
HIGH, LOW, CLOSE_PR, VOLUME) VALUES (1, ’2013-05-
31’,’452.5’,’457.1’,’449.5’,’449.74’, 13838340);
INSERT INTO PRICE (STOCK_ID, DATE_YYYYMMDD, OPEN_PR,
HIGH, LOW, CLOSE_PR, VOLUME) VALUES (2, ’2013-06-
03’,’863.54’,’873.97’,’855.02’,’867.63’, 2473631);
INSERT INTO PRICE (STOCK_ID, DATE_YYYYMMDD, OPEN_PR,
HIGH, LOW, CLOSE_PR, VOLUME) VALUES (2, ’2013-05-
31’,’868.12’,’877’,’867.52’,’871.22’, 2019407);
INSERT INTO PRICE (STOCK_ID, DATE_YYYYMMDD, OPEN_PR,
HIGH, LOW, CLOSE_PR, VOLUME) VALUES (3, ’2013-06-
03’,’26.37’,’26.62’,’26.13’,’26.39’, 16445040);
INSERT INTO PRICE (STOCK_ID, DATE_YYYYMMDD, OPEN_PR,
HIGH, LOW, CLOSE_PR, VOLUME) VALUES (3, ’2013-05-
31’,’26.2’,’26.6’,’26.09’,’26.3’, 23906901);
COMMIT;

The following statements insert data into stock table (this is a shorter
statement as you can see):

INSERT INTO STOCK VALUES (1,’ Apple Inc’, 43603000000,

12905000000, 9547000000); INSERT INTO STOCK VALUES (2,’ Google
Inc’, 13969000000, 3611000000, 3324000000); INSERT INTO STOCK
VALUES (3,’Yahoo Inc’, 114037000, 203040000, 173310000); COMMIT;
EQUITY
STOCK_ID EXCHANGE_ID STOCK_NAME DATE_YYYYMMDD
PRICE 1 1001 Apple Inc 2013-06-05 447.78 2 1001 Google Inc 2013-06-
05 863.54 3 1003 Yahoo Inc 2013-06-05 26.39
INSERT INTO EQUITY VALUES (1, 1001, ’Apple Inc’, ‘2013-06-05’,
‘447.78’); INSERT INTO EQUITY VALUES (2, 1001, ’Google Inc’,
‘2013-06-05’, ‘863.54’); Commit;

EXCHANGE
EXCHANGE_ID EXCHANGE_NAME SECTOR_NAME
SECTOR_ID 1001 ICE Tech 1 1002 CME Commodities 2

INSERT INTO EXCHANGE VALUES (1001,’ICE’, ‘Tech’, 1); INSERT

INTO EXCHANGE VALUES (1002,’CME’, ‘Commodities’, 2);

SELECT statement:
Select * from price;
Select * from price order by date;--sorting by date
Select * from price order by volume; --sorting by volume
Select * from price where stock_id =1; --using ‘where’ clause to indicate
specifics.
Select * from stock where stock_name like ‘Google%’; --using ‘LIKE’ when you don’t know complete
name. Suffix. Select * from stock where stock_name like ‘% Inc’; --using ‘LIKE’ when you don’t know

complete name. Prefix. Select sector_name , exchange_name from exchange where

sector_id=1;
Select * from equity where exchange_id =1001 and stock_name = ‘Google
Inc’;

UPDATE Statement:
The following statement updates the column ‘sector_name’ in table
‘exchange’ from ‘Commodities’ to ‘Manufacturing’
update Exchange set sector_name = ‘Manufacturing’ where exchange_id =
1002;
rollback; -- When you use ‘rollback’, the action of update statement is NOT
saved.
update Exchange set sector_name = ‘Manufacturing’ where exchange_id =
1002;
Commit; -- When you use ‘commit’, the action of update statement is saved.

DELETE Statement:
Delete from Exchange where exchange_id = 1002;
rollback; -- When you use ‘rollback’, the action of delete statement is NOT
saved. Delete from Exchange where exchange_id = 1002;
Commit; -- When you use ‘commit’, the action of delete statement is saved.
Run the following insert statement since we use this record in Joins
section. INSERT INTO EXCHANGE VALUES (1002,’CME’,
‘Commodities’, 2);
AGGREGATE FUNCTIONS:
Aggregate functions perform a calculation on a set of values and return a single value. Except for COUNT, aggregate functions ignore null values. Aggregate functions are frequently
used with the GROUP BY clause of the SELECT statement.

Some of the aggregate functions are : count(), avg(),sum(),max(),min() and

many more.
count(): row/record counts
Select count(*) from price;
Select stock_id , count(*) from price group by stock_id;--using group by
function for grouping. avg(): average function
Select avg(volume) from price;
Select stock_id , avg(volume) from price group by stock_id; --using group
by function for grouping. Select stock_id , avg(volume) from price group
by stock_id having avg(volume) >1000 ; --using having clause Select
stock_id , avg(volume) from price group by stock_id having avg(volume)
<1000 ; --using having clause sum(): sum function
Select sum(volume) from price;
Select stock_id , sum(volume) from price group by stock_id; --using group
by function for grouping. Select stock_id , sum(volume) from price group
by stock_id having sum(volume) >1000 ; --using having clause Select
stock_id , sum(volume) from price group by stock_id having sum(volume)
<1000 ; --using having clause max(): maximum function.
Select max(volume) from price;
Select stock_id , max(volume) from price group by stock_id; --using group
by function for grouping. Select stock_id , max(volume) from price group
by stock_id having max(volume) >1000 ; --using having clause Select
stock_id , max(volume) from price group by stock_id having max(volume)
<1000 ; --using having clause min() : minimum function.
Select min(volume) from price;
Select stock_id , min(volume) from price group by stock_id; --using group
by function for grouping. Select stock_id , min(volume) from price group
by stock_id having min(volume) >1000 ; --using having clause Select
stock_id , min(volume) from price group by stock_id having min(volume)
<1000 ; --using having clause
JOINS:
INNER JOIN:

SELECT * FROM PRICE

INNER JOIN STOCK
ON PRICE.STOCK_ID = STOCK.STOCK_ID;--brings out all data from
both tables
--can also be written by substituting 'INNER JOIN' with 'JOIN'. When you use simply 'JOIN' it is understood as 'INNER JOIN'. SELECT

PRICE.STOCK_ID, PRICE.DATE_YYYYMMDD,
STOCK.STOCK_NAME,
PRICE.CLOSE_PR, PRICE.VOLUME, STOCK.REVENUE,
STOCK.POST_TAX_INCOME
FROM PRICE
INNER JOIN STOCK
ON PRICE.STOCK_ID = STOCK.STOCK_ID; --Enables you to choose
data from both tables that you want to see.

OUTER JOIN:

LEFT OUTER JOIN:

SELECT EQUITY.STOCK_NAME, EQUITY.PRICE,
EXCHANGE.EXCHANGE_NAME
FROM EQUITY
LEFT OUTER JOIN EXCHANGE
ON EQUITY.EXCHANGE_ID = EXCHANGE.EXCHANGE_ID;--gives
all rows from equity and nulls for absent rows in exchange.
--can also be written by substituting 'LEFT OUTER JOIN' with 'LEFT JOIN' IN some databases. When you use simply 'LEFT JOIN' it is understood as 'LEFT OUTER JOIN'.

RIGHT OUTER JOIN:

SELECT EQUITY.STOCK_NAME, EQUITY.PRICE,
EXCHANGE.EXCHANGE_NAME
FROM EQUITY
RIGHT OUTER JOIN EXCHANGE
ON EQUITY.EXCHANGE_ID = EXCHANGE.EXCHANGE_ID; --gives
all rows from exchange and nulls for absent rows in equity.
--can also be written by substituting 'RIGHT OUTER JOIN' with 'RIGHT JOIN' IN some databases. When you use simply 'RIGHT JOIN' it is understood as 'RIGHT OUTER JOIN'.
SELF JOIN:

SELECT
A.STOCK_ID,A.DATE_YYYYMMDD,B.STOCK_ID,
B.DATE_YYYYMMDD,A.OPEN_PR,B.CLOSE_PR,
B.VOLUME
FROM PRICE A
INNER JOIN PRICE B
ON (A.STOCK_ID = B.STOCK_ID AND A.DATE_YYYYMMDD =
B.DATE_YYYYMMDD) WHERE A.OPEN_PR > B.CLOSE_PR;--A and
B are Aliases for the table stock
VIEW:
CREATE VIEW Statement:
CREATE VIEW STOCK_PRICE AS
SELECT PRICE.STOCK_ID, PRICE.DATE_YYYYMMDD,
STOCK.STOCK_NAME, PRICE.CLOSE_PR, PRICE.VOLUME
FROM PRICE
INNER JOIN STOCK
ON PRICE.STOCK_ID = STOCK.STOCK_ID;

‘CREATE OR REPLACE VIEW’ Statement:

CREATE OR REPLACE VIEW STOCK_PRICE AS
SELECT PRICE.STOCK_ID, PRICE.DATE_YYYYMMDD,
STOCK.STOCK_NAME, PRICE.CLOSE_PR, PRICE.VOLUME,
STOCK.REVENUE, STOCK.POST_TAX_INCOME FROM PRICE
INNER JOIN STOCK
ON PRICE.STOCK_ID = STOCK.STOCK_ID;

DROP VIEW Statement: DROP VIEW STOCK_PRICE

INDEX:
CREATE INDEX Statement:
CREATE INDEX ID_STOCK_DATE ON PRICE (STOCK_ID,
DATE_YYYYMMDD); -- Duplicate values are allowed
CREATE UNIQUE INDEX UNQ_STOCK_ID ON STOCK (STOCK_ID);
-- No Duplicate values are allowed
DROP INDEX Statement:
ALTER TABLE PRICE DROP INDEX ID_STOCK_DATE; ALTER
TABLE STOCK DROP UNQ_STOCK_ID;
DO IT YOURSELF EXERCISE
1. Create the following tables as per the structure defined below.
TABLE DESIGN/STRUCTURE:

RATE_TYPE
Name
RATE_TYPE_ID
RATE_TYPE_NAME TERM

Type
INT(12)
VARCHAR(30) VARCHAR(5)

Nullable No
Constraints PRIMARY KEY

RATE
Name
RATE_ID
RATE_TYPE_ID EXPIRY_DATE BID_RATE
ASK_RATE
MID_RATE

Type
INT(12)
INT(12)
DATE
FLOAT(24,4) FLOAT(24,4) FLOAT(24,4)

Nullable No
No

Constraints PRIMARY KEY FOREIGN KEY

TENOR
Name
TENOR_ID
TENOR_NAME TENOR_MONTHS

INSTRUMENT_TYPE Name
INSTRUMENT_TYPE_ID INSTRUMENT_TYPE

Type
INT(12)
VARCHAR(10) INT(12)

Type
INT(12)
Nullable No
Constraints PRIMARY KEY VARCHAR(25)

INSTRUMENT
Name
INSTRUMENT_ID
INSTRUMENT_TYPE_ID TENOR_ID
RATE_ID
INSTRUMENT_NAME STRIKE

Nullable No
Constraints PRIMARY KEY

Type
INT(12)
INT(12)
INT(12)
INT(12)
VARCHAR(50) INT(12)

Nullable No
No

Constraints PRIMARY KEY FOREIGN KEY

2. Insert the following data in the tables created above.
TABLE DATA:

RATE_TYPE
RATE_TYPE_ID RATE_TYPE_NAME TERM 11 LIBOR 1M 12
LIBOR 2M 13 LIBOR 3M 14 LIBOR 6M 15 LIBOR 9M

RATE
RATE_ID RATE_TYPE_ID EXPIRY_DATE 1 11 30-JUN-15 2 12 30-
JUN-16 3 13 30-JUN-17 4 14 30-JUN-18 5 15 30-JUN-19

BID_RATE ASK_RATE
0.024 0.04
0.028 0.04
0.031 0.04
0.033 0.04
.0352 0.04

MID_RATE
0.032
0.034
0.0355
0.0365
0.0376

TENOR
TENOR_ID TENOR_NAME TENOR_MONTHS 1 0M 0
2 3M 3
3 6M 6
4 9M 9
5 1Y 12

INSTRUMENT_TYPE
INSTRUMENT_TYPE_ID INSTRUMENT_TYPE 1
AMERICAN_CALL 2 AMERICAN_PUT 3 EUROPEAN_CALL 4
EUROPEAN_PUT 5 FORWARD
INSTRUMENT
INSTRUMENT_ID INSTRUMENT_TYPE_ID TENOR_ID RATE_ID
1 3 4 15 2 3 3 14 3 4 4 15 4 4 3 14 5 5 2 13

INSTRUMENT_NAME STRIKE AAPL.100.EUROPEAN_CALL.9M 100

AAPL.150.EUROPEAN_CALL.6M 150

AAPL.100.EUROPEAN_PUT.9M 100

AAPL.150.EUROPEAN_PUT.6M 150

AAPL.200.FORWARD.3M 200

3. Write a SQL with an inner join between RATE_TYPE and RATE

tables.
4. Write a SQL with a left outer join between INSTRUMENT and
TENOR tables.
5. Write a SQL with a right outer join between INSTRUMENT and
TENOR tables.
6. Write a SQL with a self join on the table RATE and returns rows
where MID_RATE is between bid_rate and ask_rate.
7. Write a SQL to create a View on
instrument,instrument_type,tenor,rate_type and rate giving data with
columns instrument.instrument_name,
instrument_type.instrument_type, tenor.tenor_name,
rate_type.rate_type_name,rate.expiry_date,rate.bid_rate,rate.ask_rate
and rate.mid_rate
8. Create a unique index on tenor table tenor_id column
9. Create an index on instrument_type table instrument_type_id
column. This is NOT a unique index.
10. Write a sql to give number of records present in the table
11. Write a sql to return average, maximum, minimum and sum of
strikes in the table INSTRUMENT.
12. Write a sql to return all the rows from instrument table sorted in
ascending order on strike column.
13. Write a sql to return all the rows from instrument table sorted in
descending order on strike column.