When querying the database, you use the
SELECT clause to select data from the database.
Say you had a basic table, containing people, and you wanted their names and birthdays.
SELECT name, birthday FROM People
This query would return all the names and birthdays. Next, we are going to refine our results more.
Expanding off the previous example, imagine we have 100000s of rows of information, and we only want people who live in New Zealand. To do this, we would add a
SELECT name, birthday FROM People WHERE country='NEW ZEALAND'
Now we will only see the people who live in New Zealand’s birthdays.
There are a variety of SQL commands you can use to expand on your queries. For example,
COUNT will return a number of rows.
SELECT COUNT(*) FROM People WHERE country='NEW ZEALAND'
This will return a count of the number of people in New Zealand. Other functions include
MIN MAX AVG SUM.
DISTINCT(country) would return a unique list of countries.
Imagine we wanted to view all the people with similar last names, except when we did our query, they were all out of order. We can use the
ORDER BY clause to order our results.
SELECT fname, lname FROM People WHERE country='NEW ZEALAND' ORDER BY lname
This will return a set of the people in New Zealand ordered by last name. You can add ASC or DESC after the ORDER BY to make it change the order of the sort.
Imagine we had a list of customers, and a list of orders. Say we wanted to find a list of the orders and the customer names. To do this, we would use a WHERE statement and join two tables together.
SELECT * FROM Customers C, Orders O WHERE C.customerID = O.customerID
This would return a list of customers and their orders. We may see a customer’s details more than once if they have ordered more than one order.
Imagine we had a list of customers, and a list of orders. Say we wanted to find a list of the customers that hadn’t ordered anything. To achieve this, we would:
SELECT fname, lname FROM Customers C, Orders O WHERE C.customerID = O.customerID AND O.customerID NOT IN
(SELECT OO.customerID FROM Orders OO)
This would return a list of customers who have not ordered anything. The NOT IN is highly effective for doing this. You can also use an IN.