We have recently begun to learn MySQL in school and now i need some tips for my school work. As course book we use Database systems fourth edition by Connolly & Begg.
So even if someone can say where in the book i can find answers it would be of great help because i don't find them.

Questions:
1. I need to use INSERT to crypt information and i have no clue how to do this, i can use INSERT to add information to a table but not crypt.

2. Then i need to write an INSERT using SELECT to place customers in levels acording to how much they earn, according to:
0: 0-9,999€
1: 10,000-19,999€
2: 20,000-29,999€
and so on....

If someone can provide me whit any information or tips or syntax i would be greatfull.

i think i can help u with the second question if u provide me a bit details about ur two tables (like table definitions) or may be even one table if u have stored everything in one.

The tables look like following:

Client: clientNo, fName, lName, city, join Date
and has the following data:
"C001","Axel","Rose","New York","1991-03-01"
"C002","Steve","Harris","London","1995-10-23"
"C003","Jane","Doe","Miami","1988-12-09"
"C004","Paris","Hilton","Los Angeles","2000-06-13"
"C005","Louis","Armstrong","Georgia","1981-11-23"

Account: accountNo, clientNo, balance, pin
and has the following data:
123456,"C001","342.00",3456
231415,"C004","52.23",3784
234576,"C005","238.12",3425
342534,"C002","3452.00",3434
837462,"C003","23.45",6284

Premium: clientNo, level
this one doesnt have any data exept the clientNo, it is to the premium table the second question is for.

clientNo and accountNo is the primary keys in all tables.

It would be great if you can help me because i'm stuck.

Will keep it Short:

All you need is here below but would advise you to read the recommended book thoroughly and use the site: http://dev.mysql.com

This site is going to be your backbone besides al the information you'll need about setting up the mysql server and learning to design DB's(databases):



Explanation:

First create the DB, then the table,

alter the table (column operations etc, see documentation on syntax and more)

add primary key (field -this is the column must be unique and not null can also be combination of two fields)

insert data into the table following a predefined format (the sql syntax and data type - e.g varhchar, char, int, smallint etc)

md5 is an encryption algorithm/standard pls read up supported types for mysql.

PS quite a lot of white spaces in the values() , this is as a result of better formatting so as not to get smileys.

Hope this helped.

Have fun.

Cheers

Old_Spen

Thanks for the answer, but i didn't get it to crypt. It's the pin column I need to crypt, and when i used


it didn't work, only a zero was displayed in the pin field, and when i tried to fiend the row whit a select it didn't find anything.

Ok, i solved the part with the encryption, had a litle syntax error.

But i still need to construct an INSERT clause with SELECT so i can put a level on each client as i described in my first post.

I think i need to do somekind of procedure but i don't know how it should be done.
Advice needed!

You should look at working with nested queries, joins and unions, this helps a lot.

An example is as follows taking it that you have a salary table that has data from the account table.

e.g
select table1.col1 from table1 where table1.col2 in (select * from table2.colX where salary <= 9999 )

The where condition will be changed for the subsequent value ranges.

**Note that this is just an example. Hope it helped.

Cheers.

Old_Spen

I'm starting to get the idea. I probably should do this as a procedure for easier future use. But one thing still remains, how do i get the procedure to asign a level to each customer automaticaly, is that even possible.

It depends on what you are using as procedural language. I guess you are using php with this.

Anyway you will have to create a table specifying the levels according to salary earned and this salary value will also be a referencing column in the persons account or personal information.

Joins are your best bet int this case.

I see no other way.

Cheers

No we havent reached PHP yet, just plain Mysql.

I too can't find any other way, and it isn't described in the book either.