Interview Database Queries Questions

 

Based on what the candidate mentioned during the introduction, this section can be skipped or not:

Database/Queries questions include:

  • Test on join and complicated functions such as max and avg
table1: company_employees
field = company_name, employee_id
e.g.
company_name       employee_id
Amazon                   12345
Amazon                   54321
Microsoft                23456
Microsoft                65432

table2: employee_credits
field = employee_id, credit_score
e.g.
employee_id          credit_score
12345                    500
12345                    600
54321                    700
54321                    800
23456                    900
23456                    920
65432                    930
65432                    1000

a. Please write a query that will return the fields company_name, employee_id, credit_score and return the result for the highest average credit score
e.g result
Microsoft  65432  965

b. Count how many employees are in each of the company
e.g.
Amazon 2
Microsoft 2

Database/Queries questions include:

  • Test on join and functions such as group by
Table 1: accounts
Fields: account_id, active_status
e.g.
account_id       active_status
12345            Active
23456            Active
34567            Suspended
45678            Active

Table 2: bills
Fields: account_id, billing_period, bill_id
e.g.
account_id       bill_id         billing_period    
12345            111             2013-01-01 00:00:00
12345            211             2013-02-01 00:00:00
12345            311             2013-03-01 00:00:00
12345            411             2013-04-01 00:00:00
23456            121             2013-01-01 00:00:00
23456            221             2013-02-01 00:00:00
23456            321             2013-03-01 00:00:00
23456            421             2013-04-01 00:00:00
34567            131             2013-01-01 00:00:00
34567            231             2013-02-01 00:00:00
45678            141             2013-01-01 00:00:00
45678            241             2013-02-01 00:00:00
45678            341             2013-03-01 00:00:00
45678            441             2013-04-01 00:00:00

Table 3: charges
Fields: charge_id, bill_id, charge_amount
e.g.
charge_id       bill_id         charge_amount    
11111            111            0.50
21111            211            1.00
31111            311            1.50
41111            411            0.99
12111            121            0.01
22111            221            24.00
32111            321            22.00
42111            421            60.00
13111            131            1.00
23111            231            23.00
14111            141            100.00
24111            241            121.00
34111            341            23.00
44111            441            00.99

a.Can you identify the primary and foreign keys?

b.Can you write a query that will return all the accounts that have bills in July 01, 2013?

c.Can you write a query that will count how many accounts in each of the month between January 01,2013 and July 01,
 2013 that have charge > '1'