LDC SQL Assessment Test
Customer table
Cust_id
Cust_name
Invoice table
Cust_id
Invoice_Number
Invoice_Amount
Make up some sample data for each of the above tables (i.e., at least 6 records for each table) that would
support all of the queries requested below.
Solution:
Building Schema
create table Customer_table
(Cust_id int NOT NULL,
Cust_name varchar(20) NOT NULL,
PRIMARY KEY (Cust_id));
create table Invoice_table
(Cust_id int,
Invoice_Number int,
Invoice_Amount int,
PRIMARY KEY (Invoice_Number),
FOREIGN KEY (Cust_id) REFERENCES Customer_table(Cust_id));
insert into Customer_table
(Cust_id, Cust_name)
values (1, 'Adam');
insert into Customer_table
(Cust_id, Cust_name)
values (2, 'Duke');
insert into Customer_table
(Cust_id, Cust_name)
values (3, 'Sandler');
insert into Customer_table
(Cust_id, Cust_name)
values (4, 'Duke');
insert into Customer_table
(Cust_id, Cust_name)
values (5, 'Am');
insert into Customer_table
(Cust_id, Cust_name)
values (6, 'Pd');
insert into Invoice_table
(Cust_id, Invoice_Number, Invoice_Amount)
values (1, 11,5000);
insert into Invoice_table
(Cust_id, Invoice_Number, Invoice_Amount)
values (1, 115,50);
insert into Invoice_table
(Cust_id, Invoice_Number, Invoice_Amount)
values (1, 116,200);
insert into Invoice_table
(Cust_id, Invoice_Number, Invoice_Amount)
values (2, 110,400);
insert into Invoice_table
(Cust_id, Invoice_Number, Invoice_Amount)
values (3, 100,800);
insert into Invoice_table
(Cust_id, Invoice_Number, Invoice_Amount)
values (5, 99,500);
Cust_id Cust_name
1 Adam
2 Duke
3 Sandler
4 Duke
5 Am
6 Pd
Cust_id Invoice_Number Invoice_Amount
1 11 5000
1 115 50
1 116 200
2 110 400
3 100 800
5 99 500
1. Write a query that would show all invoices where invoice amount is more than $500. The query
should show two columns, in this order: Invoice Number and Invoice Amount. The query result
should show the invoice with the highest amount at the top of the report and the invoice with the
lowest invoice amount at the bottom of the report. Show the output would look like using your
sample data. Include a copy of the query statement with your results.
Select Invoice_Number , Invoice_Amount from Invoice_table where Invoice_Amount> 500 ORDER
BY Invoice_Amount DESC;
Invoice_Number Invoice_Amount
11 5000
100 800
1. Write a query that would show the number of invoices for each Customer. The query result
should show two columns: Customer Name and Number of Invoices. Show what the output
would look like using your sample data. Include a copy of the query statement with your results.
Select a.Cust_name , b.Invoice_number from Customer_table a, Invoice_table b where a.Cust_id =
b.Cust_id;
Cust_name Invoice_numbe
r
Adam 11
Adam 115
Adam 116
Duke 110
Sandler 100
Am 99
1. What is the Foreign Key in the invoice table? How might it be used?
Foreign key for invoice table is “Cust_id”. It acts as a cross-reference between Invoice_table and
Customer_table because it references the primary key of Customer_table, thereby establishing a link
between them.
1. Write a query that would show all customers that have no invoices. The query should show one
column: Customer Name, in alphabetical order. Show what the output would look like using
your sample data. Include a copy of the query statement with your results.
Select a.Cust_name FROM Customer_table a LEFT JOIN Invoice_table b ON a.Cust_ID = b.Cust_ID
WHERE b.Invoice_number IS NULL ORDER BY a.Cust_name;
Cust_name
Duke
Pd