SQL-LAB EXAM-Solution

    • Speci cations

You are given the following investment account schema for the stock market(BIST). In the schema, per-formedOrders table contains all the transactions that are either \BUY" or \SELL".

The sample data for relations is also given below. Note that these data are for illustration purpose and they are NOT used for evaluating your solutions for the questions.

Customer (cid, name, birthday, city)

Share (shareID, shareName, shareName, priceBuy, priceSell)

Account (accountNo, cid) REFERENCES Customer(cid)

performedOrders(tid, accountNo, shareID, type, amount, price, time)

REFERENCES Account(accountNo) Share(shareID)

ShareOwned(accountNo, shareID, totalAmount) REFERENCES Account(accountNo) Share(shareID)





    • Questions

Prepare appropriate SQL queries for given de nitions.

    1. (15 pts) Delete all rows in \performedOrders" table that contains orders from the customer accounts whose cid is 10002.

    2. (15 pts) For customers whose name ends with \t", list their cids and sum of totalAmount of shares he/she owns. (List cids and \sum of totalAmount"s where cids are in ascending order).


    3. (15 pts) List distinct accountNo of accounts that have shares from at least two di erent companies whose priceBuy is greater than 10.(i.e., priceBuy>10 ) (List distinct accountNos in ascending order)

    4. (15 pts) List distinct cids of customers who has performed \BUY" order(s) but no \SELL" order(s) before \2019-11-07".(i.e., time<\2019-11-07") (List distinct cids in ascending order)



1

    5. (20 pts) List distinct accountNo of accounts that own all the shares whose sell price (priceSell) is greater than 10.(i.e., priceSell>10 ) (List distinct accountNos in ascending order)

    6. (20 pts) List tid, amount and time of \BUY" type transactions in performedOrders table which are performed by \high stakeholders". The \high stakeholders" are customers whose sum of \tota-lAmount" of all owned shares are greater than 60. (List tids, amounts and times where tids are in ascending order)

i.e., from the given instance, customer whose cid is 10001 is \high stake holder". He has 2 accounts; a10001 1 and a10001 2. From these accounts, sum of totalAmount of his shares are 100+20+20+10=150 which is greater than 60.

Customer whose cid is 10003 is also \high stakeholder" because sum of totalAmount of his share is 100 which is greater than 60.




    • Regulations

        1. Use Chromium web browser.

        2. DO NOT forget to put semicolon(;) after your queries!

        3. You are not allowed to use LIMIT clause in your queries.

        4. In each "evaluation" and "run" operations, we create the database from the beginning. So do not be afraid of deleting/updating rows.

        5. The grades at the end of the sql-lab will not be the nal grades. While evaluating your queries we may use instances di erent from those given in during the lab session. These sample instances for each of the tables are given on next page. Hence, your nal grades may change.




Table 1: Customer

cid
name
birthday
city
10001
Ahmet
1987-04-01
Ankara
10002
Berk
1990-06-12
Istanbul
10003
Ceren
1956-10-24
Izmir
10004
Damla
1962-10-16
Antalya
10005
Mehmet
1975-06-11
Ankara









Table 2: Share

shareID
shareName
priceBuy
priceSell
1
ISCTR
10.00
5.00
2
TUPRS
100.00
50.00
3
BJKAS
20.00
10.00
4
ASELS
30.00
20.00
5
FENER
5.00
4.00













2

Table 3: Account

accountNo
cid
a10001

1
10001




a10001

2
10001
a10002

1
10002
a10002

2
10002




a10003

1
10003




a10004

1
10004




a10005

1
10005














Table 4: performedOrders

tid
accountNo
shareID
type
amount
price
time
1
a10001

1
2
BUY
100
100.00
2019-11-06 14:42:52
2
a10001

1
4
BUY
20
30.00
2019-11-06 17:35:32
3
a10001

2
2
BUY
90
100.00
2019-11-06 11:00:32
4
a10001

2
2
SELL
80
50.00
2019-11-06 19:35:32









5
a10002

1
2
BUY
10
100.00
2019-11-06 17:00:02
6
a10002

1
2
SELL
5
50.00
2019-11-08 17:50:42
7
a10002

2
4
BUY
10
30.00
2019-11-06 12:00:42
8
a10002

2
4
SELL
5
20.00
2019-11-08 13:50:42
9
a10003

1
3
BUY
200
20.00
2019-11-06 10:50:15









10
a10003

1
3
SELL
100
10.00
2019-11-06 13:52:13









11
a10004

1
4
BUY
30
30.00
2019-11-05 09:57:13
12
a10004

1
4
SELL
10
20.00
2019-11-08 11:58:13
13
a10005

1
4
BUY
50
30.00
2019-11-06 14:59:13
14
a10005

1
5
BUY
50
5.00
2019-11-06 14:59:13
15
a10005

1
5
SELL
20
4.00
2019-11-06 15:50:00

























Table 5: ShareOwned

accountNo
shareID
totalAmount
a10001

1
2
100
a10001

1
4
20





a10001

1
3
20





a10001

2
2
10
a10002

1
2
5
a10002

2
4
5





a10003

1
3
100





a10004

1
4
20





a10005

1
4
20
a10005

1
5
30








3