Assignment 5
Understanding SQL
100 points: (Questions 1 to 7 each worth 12 points, Question 8 worth 16 points)
Please provide detail answer and explain If you run the following SQL queries what will be on your report. In simple words what will be the output of each of the following queries?
These questions are related to Databases Premiere Products described in lecture slide Database Concepts posted under this week for your review. Please read the PP slides to understand the Premiere Products database and answer the following questions.
For Example:
SELECT CustomerNum, CustomerName, CreditLimit, Balance FROM Customer WHERE CreditLimit>Balance ;
Your Answer: The output will list the number, name, credit limit, and balance for all customers with credit limits that exceed their balances
Now read each of the following 7 queries and submit a word document with your answers similar to the above example. Please only submit your answers do not repeat the queries.
- SELECT CustomerNum, CustomerName, FROM Customer WHERE RepNum=’35’ UNION SELECT Customer.CustomerNum, CustomerName, FROM Customer, Orders WHERE Customer.CustomerNum=Orders.CustomerNum ;
- SELECT Description FROM Part WHERE Warehouse=’3’ OR OnHand>20 ;
- SELECT CustomerNum, CustomerName, Street, City, State, Zip FROM Customer WHERE Street LIKE “*Oxford”;
- SELECT CustomerNum, CustomerName, Street, CreditLimit FROM Customer ORDER BY CreditLimit DESC, CustomerName ;
- SELECT CustomerNum, CustomerName, CreditLimit, RepNum
FROM Customer
WHERE RepNum=’35’
AND CreditLimit>2000;
- SELECT CustomerNum, CustomerName, CreditLimit, RepNum
FROM Customer
WHERE RepNum=’35’
OR CreditLimit<3000;
- SELECT CustomerNum, CustomerName
FROM Customer, Rep
WHERE Customer.RepNum=Rep.RepNum
AND LastName=’Perez’ AND FirstName=’Juan’;
- Review E-R diagram for the Premiere Products database on PP slide page 19 and
Explain what are the relationship between these tables. (16 points)
Good Luck