•
•
•
•
What is DaniWeb IT Discussion Community?
You're currently browsing the MS SQL section within the Web Development category of DaniWeb, a massive community of 392,047 software developers, web developers, Internet marketers, and tech gurus who are all enthusiastic about making contacts, networking, and learning from each other. In fact, there are 4,300 IT professionals currently interacting right now! Registration is free, only takes a minute and lets you enjoy all of the interactive features of the site.
Please support our MS SQL advertiser:
Views: 5542 | Replies: 10
 |
| |
•
•
Join Date: Feb 2005
Location: Ballarat, Australia
Posts: 164
Reputation: 
Rep Power: 4
Solved Threads: 6
Junior Poster
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
will produce the table an output of everything in the test table
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
This Will Produce
Paul
John
Kim
John Smith is the John Selected, since John Howard is after him in the database.
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
NOT IN ........
BETWEEN Value is used if its between two values
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
This will output Paul
NOT LIKE ........
Now lets order our table by FirstName
and we get our table but ordered by FirstName
INSERT
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
This Produces John Smith
Now ends Pauls basic SQL made hard tutorial...
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")BETWEEN Value is used if its between two values
SELECT ItemName FROM stockTable WHERE Quantity BETWEEN 100 AND 50
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...
Last edited by happygeek : Oct 28th, 2006 at 9:19 am. Reason: Formatting
•
•
Join Date: Dec 2004
Location: Fort Bragg, NC
Posts: 189
Reputation:
Rep Power: 4
Solved Threads: 2
Junior Poster
Re: Basic SQL made Hard 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!
dynastyCODERS#1 when it comes to Programming Tutorials, Database designs and discussions, Operating Systems, you name it, check us out and drop us a line to tell us your opinions on any and everything in mind!;)
•
•
Join Date: Nov 2004
Posts: 83
Reputation:
Rep Power: 4
Solved Threads: 0
Junior Poster in Training
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.
•
•
Join Date: Dec 2004
Location: Fort Bragg, NC
Posts: 189
Reputation:
Rep Power: 4
Solved Threads: 2
Junior Poster
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.
dynastyCODERS#1 when it comes to Programming Tutorials, Database designs and discussions, Operating Systems, you name it, check us out and drop us a line to tell us your opinions on any and everything in mind!;)
•
•
Join Date: Feb 2005
Location: Ballarat, Australia
Posts: 164
Reputation:
Rep Power: 4
Solved Threads: 6
Junior Poster
Theres a nice place that provides freesql servers www.freesql.org, I don't think they are accepting new accounts currently tho 
Thanks mike
Thanks mike
•
•
Join Date: Nov 2004
Posts: 83
Reputation:
Rep Power: 4
Solved Threads: 0
Junior Poster in Training
So i still do not have an answer to my questions, i went to the mysql.com site and did not find what i was looking for, thanks anyways
•
•
Join Date: Feb 2005
Location: Ballarat, Australia
Posts: 164
Reputation:
Rep Power: 4
Solved Threads: 6
Junior Poster
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
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
•
•
Join Date: Nov 2004
Posts: 83
Reputation:
Rep Power: 4
Solved Threads: 0
Junior Poster in Training
thank you for the replay, can anyone possibly provide links to tutorial sites about sql and or php?
•
•
Join Date: Mar 2005
Location: Kincardine On
Posts: 2
Reputation:
Rep Power: 0
Solved Threads: 0
Newbie Poster
•
•
•
•
Originally Posted by Blade10878
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..
|
•
•
•
•
Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
•
•
•
•
•
•
•
•
DaniWeb MS SQL Marketplace
Hybrid Mode
- Previous Thread: Conditional index unique check?
- Next Thread: Problems with my sql server version
