Title: Basic SQL Commands ($$$)
Course: PHP/MySQL Course

Basic Select Command

Read page 312 to 317 about the Select command, sorting, limiting your results, and the use of the where clause of the command using both the comparison operators and the LIKE operator.

Notes:
- A good reference for the select is the following tutorial link:
  - http://www.firstsql.com/tutor2.htm
- The select statement is the most important statement to master. It allows you great flexibility at retrieving sets of data from the database with exactly the conditions, fields, and order that you want.

Question?

If you wanted to list all records from a table named employee with a last name ending in the letters ins which statement would you use?

- SELECT * FROM employees WHERE last('ins');
- SELECT * FROM employees WHERE last LIKE '%ins';
- SELECT * FROM employees WHERE last >= 'ins'
- SELECT * FROM employees WHERE last LIKE '*ins';

Selecting from multiple tables

Read pages 317 to 322 about selecting from multiple tables.

Notes:
- The two tables that you select from are related by a common field
in one table this field will usually be the primary key, and in the other table the field will be a column with the id that matches the id of the other table, called a foreign key:

Take a look at this diagram:

Orders:

OrderID | Qty | Date | ProductID
101 | 10 | 2009-07-01 | 231-111
102 | 25 | 2009-08-02 | 112-100
103 | 8 | 2009-08-11 | 112-111
104 | 25 | 2009-08-12 | 112-100
105 | 11 | 2009-08-12 | 231-111

Products:

ProductID | Desc | Price
112-100 | Bolt Cutter 12” | 25.25
231-111 | Angle Wrench | 15.95
114-200 | Torque Nub | 2.75
112-111 | Bolt Cutter 8” | 18.95
122-100 | Torque Spline | 5.98

SELECT OrderID, Date, Qty,
FROM Orders, Products
WHERE Orders.ProductID = Products.ProductID

Result of select:

OrderID | Date | Qty | Desc
101 | 2009-07-01 | 10 | Angle Wrench
102 | 2009-08-02 | 25 | Bolt Cutter 12”
103 | 2009-08-11 | 8 | Bolt Cutter 8”
104 | 2009-08-12 | 25 | Bolt Cutter 12”
105 | 2009-08-12 | 11 | Angle Wrench

Note that the colored rows show how the where clause will match up the row in the Products table that matches the ProductID. The the requested columns are then pulled and presented in the row of the resulting select output.

This type of select is the most common multi-table select for relational databases.
- Outer joins and sub queries are not used as often as the inner join.
- Using where as the did on the middle of page 319 and using the phrase inner join as shown on the next page do exactly the same thing. I prefer the where method, because it is a little less typing. Some people prefer the INNER JOIN method because it documents better what your intention is.

Question?
The two tables that you join data together from normally should be related by a common id or key?

- true
- False

Update Command

Read about the update and replace command on pages 323 to 326.

Notes:
- The update command will be most commonly use to update one or more fields in one record identified by its primary key.

update employees set salary = 25000 where id = 1001;

The example above is typical, change the data of one field for one employee ( id = 1001).

- You can think of update as the edit existing data command

Question?
Which command below will give employee number 101 a raise of 10%?

- update employee 101 to salary * 1.1;
- update employees set salary = salary * 1.1;
- update employees set salary = salary * 1.1 where id = 101
- update employees where id = 101, set salary = salary * 1.1;

Delete Command

Read about the delete command on pages 326 to 328.

Notes:
- most commonly you will delete one record. For example when product number 121 is obsolete, you may delete its product record with:

delete from products where productID = 121;

Question?
If I have the following table:

Products:

ProductID | Desc | Price
112-100 | Bolt Cutter 12” | 25.25
231-111 | Angle Wrench | 15.95
114-200 | Torque Nub | 2.75
112-111 | Bolt Cutter 8” | 18.95
122-100 | Torque Spline | 23.25

how may records will be left in the table after this delete command?

delete from Products where Price > 10.00;

- 1|one|ONE|One

String and Date/Time functions

go ahead and read from page 328 to the end of the chapter about the special functions.

These functions provide a lot of extra features you can use in SQL to make our databases do amazing things that you would normally have to do in our php code.

But, I am not requiring a knowledge of these functions any test or quiz.

Some of the most useful functions that you will use are

- the COUNT() function to return counts of records that match a criteria.
- The DATE_FORMAT function to format data into the date/time format you want to present to the user
- The UNIX_TIMESTAMP() function is also very useful to extract the timestamp for use in php by the built-in time/date functions covered in previous chapters
- Another feature of SQL that is not covered, that useful to know are the GROUP BY and SUM features to allow you to group records and sum fields within each group See the following two links for more information:

Grouping Queries -- using the GROUP BY clause and HAVING clause
Aggregate Queries -- using Set Functions and the HAVING clause

Question?
Which feature in this section did you like the best

- .*