'order by alternating table' results and 'begins with' select

[Lisawynn]

Hi. I am just starting to learn SQL and have been working on a couple of questions and yes this is homework related. I am having trouble with 3 questions. I have the basis for the answers but am stuck on a couple of points. I don't expect a direct answer but a push in the right direction would be helpful. Also, if anyone can recommend additional resources for assisting with learning SQL, that would be great.

The first question is as follows:

A company is preparing invitations to its annual holiday celebration and wants to invite its sales staff (everyone in the SALESREPS table) and its customers (everyone in the CUSTOMERS table). Write a SQL statement that returns output looking like the following (including the sort order):

Bill Adams Employee
First Corp. Customer
Nancy Angelli Employee
Solomon Inc. Customer


Bill Adams is the Sales Rep for the Customer First Corp. and Nancy Angellie is the Sales Rep for the Customer Solomon Inc. This is what I have currently:

SELECT name, ‘Employee’ FROM salesreps UNION SELECT company, ‘Customers’ FROM customers ORDER BY 1, 2;

The Order By is not correct as it lists all the results for Employee and then all the results from Customers and I am trying to figure out how to get it to sort by employee, customer, employee, customer as illustrated above.

The salesrep table contains the following:
empl_num, name, age, rep_office, title, hire_date, manager, quota, sales

The customers table contains the following:
cust_num, company, cust_rep, credit_limit

The second question is:

Write a SQL statement that shows the customer number of customers that have placed an order with an amount greater than $3,000.00. List each customer number only once, and sort the results alphabetically.

This is what I have:

SELECT DISTINCT cust FROM orders WHERE amount < 3000.00;

This will return a list of customer numbers but I don't know how to sort them alphabetically because they are numerical. I know that I have to link this statement with another that incorporates the data from the customers table (the company name is alphabetical and can be sorted as such) but I am not sure how to do that. I tried using union but I don't want company to appear as a column in the results, only the numerical cust column. The customers table contains cust_num, company, cust_rep, credit_limit

The third question is:

Write a SQL statement that shows the order number, the date the order was taken and the amount of the order for all products whose product id starts with a '4' (Remember to use ANSI-compatible syntax.)

This is what I have for a solution:

SELECT order_num, order_date, amount FROM orders JOIN products ON product_id like '4%';

It produces results when the statement is executed. Would this be correct?

Thank you for your time.

[Tangerine Dream]

1st question:
The salesrep table contains the following:
empl_num, name, age, rep_office, title, hire_date, manager, quota, sales

The customers table contains the following:
cust_num, company, cust_rep, credit_limit
If 'cust_rep' is a FK and migrated 'empl_num', then something awful like this (hope someone will post a more elegant query):



SELECT c1, c2 FROM
(
SELECT empl_num AS id, name AS c1, 'Employee' AS c2 FROM salesrep
UNION
SELECT cust_rep AS id, company AS c1, 'Customer' AS c2 FROM customers
) AS t
GROUP BY t.id;



2nd question:
The customers table contains cust_num, company, cust_rep, credit_limit
Maybe they want this:


SELECT o.cust AS `customer number`
FROM customers c INNER JOIN orders o ON c.cust_num = o.cust
GROUP BY o.cust
HAVING SUM(amount) > 3000.00;

-- or
SELECT COUNT(DISTINCT o.cust) AS `number of customers`
FROM customers c INNER JOIN orders o ON c.cust_num = o.cust
GROUP BY o.cust
HAVING SUM(amount) > 3000.00;

where 'orders.cust' is FK

3rd question:
It produces results when the statement is executed. Would this be correct?
Probably. Would you post DDL statements for 'orders' and 'product' table

[Lisawynn]

Thanks for your response. I will try your suggestions.

For the second question, the statement that you have suggested is the one that I have already tried. The data for cust is in the orders table; the data for company is in the customers table. How do I get the statement to sort alphabetically using the data contained in 'company' which is alphabetical when the data in 'cust' is numerical?

Here is the question as it was given to us:

Using the SQL_BOOK database developed in Module 3, write a SQL Statement that shows the customer number of customers that have placed an order with an amount greater than $3,000.00. List each customer number only once, and sort the results alphabetically.

The customers table is:

cust_num, company, cust_rep, credit_limit

The orders table is:

order_num, order_date, cust, rep, mfr, product, qty, amount

The other tables in the database do not directly relate to this query (as far as I can tell!!)

I am sorry but I have not learned what a DDL statement is yet. If you can advise, I would be happy to provide that information as you had requested. I apologize for this but as I said, I have just started learning SQL so its sort of frustrating at this point.

Thanks again for your assistance!!!

[Tangerine Dream]

Write a SQL statement that shows the customer number of customers that have placed an order with an amount greater than $3,000.00. List each customer number only once, and sort the results alphabetically.
Check post #2, but i don't quite undestand what " the customer number of customers " construct means

I am sorry but I have not learned what a DDL statement is yet.
DDL = data definition language: CREATE DATABASE, CREATE TABLE, etc. CREATE statements

[Lisawynn]

This is the DDL for the Customers table and the Orders table:

This is for the Customers table:

CREATE TABLE CUSTOMERS
(CUST_NUM INTEGER NOT NULL,
COMPANY VARCHAR(20) NOT NULL,
CUST_REP INTEGER,
CREDIT_LIMIT DOUBLE,
PRIMARY KEY (CUST_NUM), FOREIGN KEY HASREP (CUST_REP) REFERENCES SALESREPS ON DELETE SET NULL);

This is for the orders table:

CREATE TABLE ORDERS
(ORDER_NUM INTEGER NOT NULL,
ORDER_DATE DATE NOT NULL,
CUST INTEGER NOT NULL,
REP INTEGER,
MFR CHAR(3) NOT NULL,
PRODUCT CHAR(5) NOT NULL,
QTY INTEGER NOT NULL, AMOUNT DOUBLE NOT NULL, PRIMARY KEY (ORDER_NUM),
FOREIGN KEY PLACEDBY(CUST) REFERENCES CUSTOMERS ON DELETE CASCADE, FOREIGN KEY TAKENBY(REP) REFERENCES SALESREPS ON DELETE SET NULL, FOREIGN KEY ISFOR (MFR, PRODUCT) REFERENCES PRODUCTS ON DELETE RESTRICT);

Thanks!