sql-querying-and-database-design/Morgan_Assignment5_Part1.sql at main · KadenaPSG/sql-querying-and-database-design · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
/*
    Name: Gilbert Morgan
    DTSC660: Data and Database Managment with SQL
    Assignment 5- PART 1
*/

--------------------------------------------------------------------------------
/*				                 Query 1            		  		          */
--------------------------------------------------------------------------------

SELECT
    b.cust_ID,
    d.account_number,
    b.loan_number
FROM
    borrower b
JOIN
    depositor d ON b.cust_ID = d.cust_ID
ORDER BY
    b.cust_ID;

--------------------------------------------------------------------------------
/*				                  Query 2           		  		          */
--------------------------------------------------------------------------------

SELECT
    c.cust_ID,
    c.customer_city,
    b.branch_name,
    b.branch_city,
    a.account_number
FROM
    customer c
JOIN
    depositor d ON c.cust_ID = d.cust_ID
JOIN
    account a ON d.account_number = a.account_number
JOIN
    branch b ON a.branch_name = b.branch_name
WHERE
    c.customer_city = b.branch_city;

--------------------------------------------------------------------------------
/*				                  Query 3           		  		          */
--------------------------------------------------------------------------------

SELECT
    cust_ID,
    customer_name
FROM
    customer
WHERE
    cust_ID IN (
        SELECT cust_ID
        FROM borrower
        EXCEPT
        SELECT cust_ID
        FROM depositor
    )
ORDER BY
    cust_ID;

--------------------------------------------------------------------------------
/*				                  Query 4           		  		          */
--------------------------------------------------------------------------------

SELECT DISTINCT
    a.branch_name
FROM
    customer c
JOIN
    depositor d ON c.cust_ID = d.cust_ID
JOIN
    account a ON d.account_number = a.account_number
WHERE
    c.customer_city = 'Harrison'
ORDER BY
    a.branch_name;

--------------------------------------------------------------------------------
/*				                  Query 5           		  		          */
--------------------------------------------------------------------------------

SELECT
    cust_ID,
    customer_name
FROM
    customer
WHERE
    (customer_street, customer_city) IN (
        SELECT customer_street, customer_city
        FROM customer
        WHERE cust_ID = '12345'
    )
    AND cust_ID <> '12345'
ORDER BY
    cust_ID;

--------------------------------------------------------------------------------
/*				                  Query 6           		  		          */
--------------------------------------------------------------------------------

SELECT
    c.cust_ID,
    c.customer_name
FROM
    customer c
JOIN
    depositor d ON c.cust_ID = d.cust_ID
JOIN
    account a ON d.account_number = a.account_number
JOIN
    branch b ON a.branch_name = b.branch_name
WHERE
    b.branch_city = 'Brooklyn'
GROUP BY
    c.cust_ID, c.customer_name
HAVING
    COUNT(DISTINCT b.branch_name) = (
        SELECT COUNT(DISTINCT branch_name)
        FROM branch
        WHERE branch_city = 'Brooklyn'
    );

--------------------------------------------------------------------------------
/*				                  Query 7           		  		          */
--------------------------------------------------------------------------------

SELECT
    l.loan_number,
    c.customer_name,
    l.branch_name
FROM
    loan l
JOIN
    borrower b ON l.loan_number = b.loan_number
JOIN
    customer c ON b.cust_ID = c.cust_ID
WHERE
    l.branch_name = 'Yonkahs Bankahs'
    AND CAST(l.amount AS NUMERIC) > (
        SELECT CAST(AVG(CAST(amount AS NUMERIC)) AS NUMERIC)
        FROM loan
        WHERE branch_name = 'Yonkahs Bankahs'
    );