SQL Query Help?

Question

Hi there I've made up some queries and was hoping someone could answers these and show me how you did this? I'm trying to learn SQL queries, any who help would be much appreciated!

1. The full name of all employees in descending order of last name.

2. The full name of all employees and their employment position in descending order of position and last name. You must display the position name in words not the positionID.

3. How busy each optometrist has been. Your SQL statement should return the full names of all optometrists, and the total number of appointments they have conducted. You must use the word ‘Optometrist’ not the positionID to select optometrists in your statement. Note that even optometrists with zero appointments should be displayed in the results.

4. A new customer record needs to be added to the database. Write an SQL statement to add the following details:
Customer Name: Fredrick Williams
Email: [e-mail]
Address: 35 Coast Road, Southport 4290
Primary Phone: [phone]

5.The full name, email and primary phone number and total number of invoices for all customers in ascending order of last name. Note that even customers with zero invoices should be displayed in the results.

6.List all details (including productID, product name, price and current stock) for any products with ‘bass’ as a part of the product name. Order the result from the most expensive

Thank you so much!

Answer 1

1. Select * from FirstName, LastName order by LastName Desc

2. Select * from FirstName, LastName, Position order by Position, LastName

3. Depends upon what is meant. Is Optometrist a field or an entry in the PositionID. So:

Select * from ****(* where ...= "Optometrist" ... count ...tableName.appointments



You should be able to handle the rest.

Answer 2

"The full name of all employees in descending order of last name. "



You don't tell us what the tables or columns are named. But, it's likely something like:



SELECT FirstName + ' ' + LastName AS Name FROM Employees ORDER BY LastName Desc



"2. The full name of all employees and their employment position in descending order of position and last name. You must display the position name in words not the positionID. "



Again, this is very hard without knowing anything about the tables involved.



SELECT FirstName + ' ' + LastName AS Name, Position.PositionName FROM Employees JOIN Position ON Employee.ID = Position.EmployeeID ORDER BY PositionName DESC, LastName Desc



Don't even try to claim you "made up" these queries. These are obviously from a textbook.

Answer 3

Teach yourself at SQL Exercises: http://sql-ex.com/