SQL-LAB EXAM-Solution

Description

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

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)

5. (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

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