MS SQL

Basic SQL made Hard

Please support our MS SQL advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases

•

Join Date: Feb 2005

Posts: 181

Reputation: Paul.Esson is an unknown quantity at this point

Solved Threads: 10

Paul.Esson Paul.Esson is offline

Offline

Junior Poster

Basic SQL made Hard

Feb 15th, 2005

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

Help with Code Tags

MS SQL Syntax (Toggle Plain Text)

SELECT * FROM test

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

Help with Code Tags

MS SQL Syntax (Toggle Plain Text)

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

 Help with Code Tags 
 MS SQL Syntax (Toggle Plain Text) 
SELECT DISTINCT FirstName FROM test
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.

 Help with Code Tags 
 MS SQL Syntax (Toggle Plain Text) 
SELECT LastName FROM test WHERE FirstName = "John"
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

 Help with Code Tags 
 MS SQL Syntax (Toggle Plain Text) 
SELECT FirstName FROM test WHERE LastName IN ("Esson", "Smith")
SELECT FirstName FROM test WHERE LastName IN ("Esson", "Smith")

NOT IN ........

BETWEEN Value is used if its between two values

 Help with Code Tags 
 MS SQL Syntax (Toggle Plain Text) 
SELECT ItemName FROM stockTable WHERE Quantity BETWEEN 100 AND 50
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

 Help with Code Tags 
 MS SQL Syntax (Toggle Plain Text) 
SELECT FirstName FROM test WHERE FirstName LIKE "P_ul"
SELECT FirstName FROM test WHERE FirstName LIKE "P_ul"

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

Now lets order our table by FirstName

 Help with Code Tags 
 MS SQL Syntax (Toggle Plain Text) 
SELECT * FROM test ORDER BY FirstName
SELECT * FROM test ORDER BY FirstName

and we get our table but ordered by FirstName

INSERT

 Help with Code Tags 
 MS SQL Syntax (Toggle Plain Text) 
INSERT INTO TABLE (field1, field2, etc....) VALUES (value1, value2, etc...)
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

 Help with Code Tags 
 MS SQL Syntax (Toggle Plain Text) 
SELECT FirstName,LastName FROM test WHERE FirstName = "John" AND LastName != "Howard"
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 10:19 am. Reason: Formatting

•

Join Date: Dec 2004

Posts: 196

Reputation: mikeSQL is an unknown quantity at this point

Solved Threads: 3

mikeSQL

Offline

Junior Poster

Re: Basic SQL made Hard

Feb 15th, 2005

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: 86

Reputation: Blade10878 is an unknown quantity at this point

Solved Threads: 1

Blade10878 Blade10878 is offline

Offline

Junior Poster in Training

Re: Basic SQL made Hard

Feb 15th, 2005

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

Posts: 196

Reputation: mikeSQL is an unknown quantity at this point

Solved Threads: 3

mikeSQL

Offline

Junior Poster

Re: Basic SQL made Hard

Feb 16th, 2005

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.

•

Join Date: Feb 2005

Posts: 181

Reputation: Paul.Esson is an unknown quantity at this point

Solved Threads: 10

Paul.Esson Paul.Esson is offline

Offline

Junior Poster

Re: Basic SQL made Hard

Feb 16th, 2005

Theres a nice place that provides freesql servers www.freesql.org, I don't think they are accepting new accounts currently tho

Thanks mike

•

Join Date: Nov 2004

Posts: 86

Reputation: Blade10878 is an unknown quantity at this point

Solved Threads: 1

Blade10878 Blade10878 is offline

Offline

Junior Poster in Training

Re: Basic SQL made Hard

Feb 16th, 2005

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

Posts: 181

Reputation: Paul.Esson is an unknown quantity at this point

Solved Threads: 10

Paul.Esson Paul.Esson is offline

Offline

Junior Poster

Re: Basic SQL made Hard

Feb 17th, 2005

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

•

Join Date: Nov 2004

Posts: 86

Reputation: Blade10878 is an unknown quantity at this point

Solved Threads: 1

Blade10878 Blade10878 is offline

Offline

Junior Poster in Training

Re: Basic SQL made Hard

Mar 7th, 2005

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

•

Join Date: Mar 2005

Posts: 2

Reputation: synergz is an unknown quantity at this point

Solved Threads: 0

synergz

Offline

Newbie Poster

Re: Basic SQL made Hard

Apr 1st, 2005

•

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..