Lab | SQL basics (selection and aggregation)

Introduction

In this lab, you will be using the files_for_lab/mysql_dump.sql database. Load it to Sequel Pro, and there you will find a bank database which we will use for the following exercise.

Here, we will practice selecting and projecting data. You can finish all questions with only those clauses:

• SELECT
• SELECT DISTINCT
• FROM
• WHERE
• ORDER BY
• LIMIT

You can save your solutions in a file solutions.sql.

Instructions

Assume that any _id columns are incremental, meaning that higher ids always occur after lower ids. For example, a client with a higher client_id joined the bank after a client with a lower client_id.

Query 1

Get the id values of the first 5 clients from district_id with a value equals to 1.

Expected result:

2
3
22
23
28

Query 2

In the client table, get an id value of the last client where the district_id equals to 72.

Expected result:

13576

Query 3

Get the 3 lowest amounts in the loan table.

Expected result:

4980
5148
7656

Query 4

What are the possible values for status, ordered alphabetically in ascending order in the loan table?

Expected result:

A
B
C
D

Query 5

What is the loan_id of the highest payment received in the loan table?

Expected result:

6312

Query 6

What is the loan amount of the lowest 5 account_ids in the loan table? Show the account_id and the corresponding amount

Expected result:

#id     amount
2	    80952
19	    30276
25	    30276
37	    318480
38	    110736

Query 7

What are the account_ids with the lowest loan amount that have a loan duration of 60 in the loan table?

Expected result:

10954
938
10711
1766
10799

Query 8

What are the unique values of k_symbol in the order table?

Note: There shouldn't be a table name order, since order is reserved from the ORDER BY clause. You have to use backticks to escape the order table name.

Expected result:

LEASING
POJISTNE
SIPO
UVER

Query 9

In the order table, what are the order_ids of the client with the account_id 34?

Expected result:

29445
29446
29447

Query 10

In the order table, which account_ids were responsible for orders between order_id 29540 and order_id 29560 (inclusive)?

Expected result:

88
90
96
97

Query 11

In the order table, what are the individual amounts that were sent to (account_to) id 30067122?

Expected result:

5123

Query 12

In the trans table, show the trans_id, date, type and amount of the 10 first transactions from account_id 793 in chronological order, from newest to oldest.

Expected result:

3556468	981231	PRIJEM	78.6
233254	981216	VYDAJ	600
233104	981212	VYDAJ	1212
233248	981211	VYDAJ	851
233176	981207	VYDAJ	204
3556467	981130	PRIJEM	75.1
233395	981130	VYDAJ	14.6
233103	981112	VYDAJ	1212
233247	981111	VYDAJ	851
233175	981107	VYDAJ	204

Query 13

In the client table, of all districts with a district_id lower than 10, how many clients are from each district_id? Show the results sorted by the district_id in ascending order.

Expected result:

1	663
2	46
3	63
4	50
5	71
6	53
7	45
8	69
9	60

Query 14

In the card table, how many cards exist for each type? Rank the result starting with the most frequent type.

Expected result:

classic	659
junior	145
gold	88

Query 15

Using the loan table, print the top 10 account_ids based on the sum of all of their loan amounts.

Expected result:

7542	590820
8926	566640
2335	541200
817	    538500
2936	504000
7049	495180
10451	482940
6950	475680
7966	473280
339	    468060

Query 16

In the loan table, retrieve the number of loans issued for each day, before (excl) 930907, ordered by date in descending order.

Expected result:

930906	1
930803	1
930728	1
930711	1
930705	1

Query 17

In the loan table, for each day in December 1997, count the number of loans issued for each unique loan duration, ordered by date and duration, both in ascending order. You can ignore days without any loans in your output.

Expected result:

971206	24	1
971206	36	1
971208	12	3
971209	12	1
971209	24	1
971210	12	1
971211	24	1
971211	48	1
971213	24	1
971220	36	1
971221	36	1
971224	60	1
971225	24	1
971225	60	1

Query 18

In the trans table, for account_id 396, sum the amount of transactions for each type (VYDAJ = Outgoing, PRIJEM = Incoming). Your output should have the account_id, the type and the sum of amount, named as total_amount. Sort alphabetically by type.

Expected result:

396	PRIJEM	1028138.6999740601
396	VYDAJ	1485814.400024414

Query 19

From the previous output, translate the values for type to English, rename the column to transaction_type, round total_amount down to an integer

Expected result:

396	INCOMING	1028138
396	OUTGOING	1485814

Query 20

From the previous result, modify your query so that it returns only one row, with a column for incoming amount, outgoing amount and the difference.

Expected result:

396	1028138	1485814	-457676

Query 21

Continuing with the previous example, rank the top 10 account_ids based on their difference.

Expected result:

9707	869527
3424	816372
3260	803055
2486	735219
1801	725382
4470	707243
3674	703531
9656	702786
2227	696564
6473	692580