0

Intro

I'm not the best at this, but I thought we were lacking a SQL tutorial...


Test table

My Little Test Table called "test" for this great exercise contains the following

FirstName LastName EyeColor
Paul Esson Brown
John Smith Blue
John Howard Green
Kim Beazley Gray


SELECT

This selects data from a table in SQL. The basic syntax of select is as follows:

SELECT field FROM table

So

SELECT * FROM test

will produce the table an output of everything in the test table

SELECT FirstName FROM test

Will Produce

Paul
John
John
Kim

As you can clearly see, this part is quite easy.


WHERE

If we don't want to know about two johns we can try

SELECT DISTINCT FirstName FROM test

This Will Produce

Paul
John
Kim

John Smith is the John Selected, since John Howard is after him in the database.

SELECT LastName FROM test WHERE FirstName = "John"

Will Produce

Smith
Howard

WHERE can be used with lots of different logical operators, such as
= is equal to
<> or != is not equal to
> greater than
< less than
>= less than or equal to
<= ....

IN A list follows this, if its in the list then its used
such as

SELECT FirstName FROM test WHERE LastName IN ("Esson", "Smith")

NOT IN ........

BETWEEN Value is used if its between two values

SELECT ItemName FROM stockTable WHERE Quantity BETWEEN 100 AND 50

NOT BETWEEN ......

IS NULL Where the Field is Null
IS NOT NULL ......

LIKE Where the field is like an expression _ represents
one char % represents multiple

SELECT FirstName FROM test WHERE FirstName LIKE "P_ul"

This will output Paul
NOT LIKE ........

Now lets order our table by FirstName

SELECT * FROM test ORDER BY FirstName

and we get our table but ordered by FirstName


INSERT

INSERT INTO table (field1, field2, etc....) VALUES (value1, value2, etc...)

That was quick!

AND

Yet again I do not want to look at Howard another easy way of forgeting about him is to use and

SELECT FirstName,LastName FROM test WHERE FirstName = "John" AND LastName != "Howard"

This Produces John Smith

Now ends Pauls basic SQL made hard tutorial...

5
Contributors
10
Replies
11
Views
12 Years
Discussion Span
Last Post by shadowrider
0

You know Paul, thats a nice tutorial. Sorry I have been lacking or slackin, crackalackin, all he above on this part of the site here that I love the most. Ive been workin to hard outside of the CPU, but I will try harder next time. Thanks and good T's again bro!

0

that is a good tutorial, here is my thought, I have been curious about databases and what they are and what they are used for, especially the web. If anyone has a link for an example of a database on the web I would love to see. As for the tutorial, I don't really understand it, of course I also don't know what a database is.

0

You can goto www.mysql.com for extream learning and downloadable files IE; Tools that will teach you everything you want to know. I also have some tutorials which I will be putting up on my site for Database Learners as well.

0

Well, From the top of my head (meaning prolly wildly inaccurate) a database is simply a store of data, that data is stored in tables each table has many rows each with different names witch take different values INT, TEXT perhaps.
On the internet these are used alot, say you have a online shop, the details on your stock could be stored in a table in a database with the row names being StockName, ItemNo (this would be a primary key (don't worry about that)), Price, etc...

Then using a serverside language like PHP or ASP.NET you could format a page that listed the contents of the database in an intuative (sp) way.

So, a database is a useful way to store data, its faster and often easyer then using plain text files.

SQL is the Structured Query Language, alot of databases use this as a way of well interfacing with them, SQL is quite similar across different databases (there are some slight differences) so learning SQL really allows you to interface with alot of different databases. The tutorial shows you some of the things you can do with SQL.

I hope this helps you out

0

thank you for the replay, can anyone possibly provide links to tutorial sites about sql and or php?

1

thank you for the replay, can anyone possibly provide links to tutorial sites about sql and or php?

Not sure what you are looking for. You will not find a much simpler tutorial than this, and understanding the nature of a database is fundemental to seeing how it can be used on the web. I suggest you take the time to print out the tutorial, and walk away from the computer. Follow the tutorial step by step, it should become clear. If it doesnt then post questions to help clarify it, and SQL people with experience will try to assist. The analogy is you cannot drive a car until you understand what the various controls do, and any programming task is similar. Ince you understand the SQL language then you you can look at how this can be parsed using PHP to create startling web based applications

Here is a SQL database in a nutshell:
It is a set of related tables. Each table has columns with names like FIrstName, LastName, Address etc etc.. In a true SQL database there are more than one table, but all the tables are linked using keys. Although not a good SQL example, a look at MSAccess can help to firm up this understanding, with some helpful graphic tools.

These columns have attributes so that thy contain data of the correct type, and possibly that each line or row of data is unique. the example in the above tutorial, is about as simple a table as you can get, as it has no relationships to other tables. So try to read . The author did a great job.... but you have to read it to understand this simple tutorial.before you will be ready for the next step of seeing how it is used in a database. If you cannot understand this, the next step will be overwhelming

Now once you can wrap your brain around what a table is, and that you can get information out of the table in the order that you want, or limited by constraints that you want. This information comes out as text, and using a language like PHP or perl, you can use this info to build a dynamic web site that gives you only the info you need.

If you want to see real world examples... You are looking at one, the Daniweb forum stores these posts in a database, and when you make a selection, it retrieves the info, and presents it in the format you see. Another good example is eBay, go and look at an ebay list of things, and then click on the the price column hyperlink, and you will get a re-ordered price list. to do this ebay sends a SQL command to its database that says "order by price ascending.... or decending"

This is an advanced area of script based programming so it takes work on the students part..

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.