The intention here is to describe ANSI SQL, rather than vendor specific SQL.
You can use SQL Fiddle to try out SQL syntax, the site supports MySQL, Oracle, PostgreSQL, SQLite and MS SQL Server and hence serves as a useful test but there is nothing like a proper database.
This is quite simple but can be done in a couple of different ways:
INSERT INTO product_table VALUES ('ABC123', 'Bread', 4); INSERT INTO product_table (product_id, name, price) VALUES ('DEF456', 'Cheese', 10);These insert the same rows, but with the second option you can do the columns in the order you prefer, rather than table definition order.
There is a lot of flexibility about how you can do an update of some or all of the data in a table, but a good example is
UPDATE product_table SET price = 10 WHERE product_id = 'ABC123';This gives an idea of the basics.
This is another necessary option and is simply done as follows:
DELETE FROM product_table WHERE type = 'discontinued';If you miss the WHERE clause then every row in the table is deleted. This is the same as
TRUNCATE product_table;However in most databases TRUNCATE is much quicker, and often DELETE will say how many rows were deleted from the table but TRUNCATE does not.
A good visual starting point is Visual Representation of SQL Joins - CodeProject
It is easy to forget how "BETWEEN" works, so, for clarity it is this:
WHERE Length BETWEEN 5 AND 10 WHERE Length >= 5 AND Length <= 10It is also worth noting that BETWEEN works with "WHERE" and "HAVING".
Sometimes I find it handy to get a count of the rows in a number of or all tables in a database. The following query gives a nice example of how this can be done:
SELECT 'people' AS "Table Name", COUNT(*) "Row Count" FROM people UNION ALL SELECT 'desks' AS "Table Name", COUNT(*) "Row Count" FROM desks UNION ALL SELECT 'offices' AS "Table Name", COUNT(*) "Row Count" FROM offices UNION ALL SELECT 'companies' AS "Table Name", COUNT(*) "Row Count" FROM companies
The output will look something like this:
| Table Name | Row Count |
|---|---|
| people | 323 |
| desks | 25 |
| offices | 3 |
| companies | 2 |
It is important to say that this is not necessarily the best way. For example with very large tables the query can take a long time to execute. Depending on which database software you are using there might be a better way, for example there may be a system schema for database information or statistics. The above works when you have basic query privileges but if you have a higher level of access then it will be better to study your database software for better options.
If you want to understand what happens when the send a SQL query for execution then have a read of Life of a SQL query which gives a good overview.