Database Journal Forums http://forums.databasejournal.com Discuss all things related to the world of databases at the active Database Journal community. Forum topics include news, database design, ASP, MS Access, SQL and more. en Thu, 17 May 2012 16:51:59 GMT vBulletin 60 http://forums.databasejournal.com/images/misc/rss.jpg Database Journal Forums http://forums.databasejournal.com change collation at once http://forums.databasejournal.com/showthread.php?t=53205&goto=newpost Thu, 17 May 2012 06:48:47 GMT Hi, sorry to post this again but i just realized i had posted it on the wrong forum. anyway, i have a large database that has many fields with collation set to latin1_Swedish_ci and i want to change all of them to utf8_unicode_ci at once but i cant find the proper query for that. Something like UPDATE collate SET utf8_unicode_ci WHERE collate=latin1_Swedish_ci; Is it possible? Thank you. Hi, sorry to post this again but i just realized i had posted it on the wrong forum.

anyway, i have a large database that has many fields with collation set to latin1_Swedish_ci and i want to change all of them to utf8_unicode_ci at once but i cant find the proper query for that. Something like UPDATE collate SET utf8_unicode_ci WHERE collate=latin1_Swedish_ci;

Is it possible?

Thank you. ]]> MySQL supercain http://forums.databasejournal.com/showthread.php?t=53205 SQL: columns into row http://forums.databasejournal.com/showthread.php?t=53204&goto=newpost Tue, 15 May 2012 14:43:55 GMT Could you please advise me on how to transfer columns into rows member diag ------------------------ Smith 354.0 Smith 250.00 Smith 278.00 so it reads: member diag ------------------------ Smith 354.0, 250.00, 278.00 thank you! Could you please advise me on how to transfer columns into rows

member diag
------------------------
Smith 354.0
Smith 250.00
Smith 278.00

so it reads:

member diag
------------------------
Smith 354.0, 250.00, 278.00

thank you! ]]> Ask an Expert sova1708 http://forums.databasejournal.com/showthread.php?t=53204 change collation at once http://forums.databasejournal.com/showthread.php?t=53203&goto=newpost Tue, 15 May 2012 04:14:31 GMT Hi, i have a large database that has many fields with collation set to latin1_Swedish_ci and i want to change all of them to utf8_unicode_ci at once but i cant find the proper query for that. Something like UPDATE collate SET utf8_unicode_ci WHERE collate=latin1_Swedish_ci; Is it possible? Thank you. Hi,

i have a large database that has many fields with collation set to latin1_Swedish_ci and i want to change all of them to utf8_unicode_ci at once but i cant find the proper query for that. Something like UPDATE collate SET utf8_unicode_ci WHERE collate=latin1_Swedish_ci;

Is it possible?

Thank you. ]]> Structured Query Language (SQL) supercain http://forums.databasejournal.com/showthread.php?t=53203 problem with basic junction in query: not be able to retreive the proper data http://forums.databasejournal.com/showthread.php?t=53202&goto=newpost Mon, 14 May 2012 04:42:39 GMT I have the following tables:

Person (PK_PersonID, PersonFullName, FK_MbtiID)
Mbti(PK_MbtiID)
Dimension(PK_DimensionID, DimensionName)
MbtiDimension(FK_PK_DimensionID, FK_PK_MbtiID)

Dimension has data as followed:

PK_DimensionID, DimensionName
---------------------------------------------
E, Extraversion
I, Introversion
S, Sensing
N, Intuition
T, Thinking
F, Feeling
J, Judging
P, Perception

there are 16 mbti types from these dimensions, so the table Mbti has data as following:

PK_MbtiID
--------------
ISTJ
ISFJ
INFJ
INTJ
ISTP
ISFP
INFP
INTP
ESTP
ESFP
etc...

The link (entity relation many to many) between Mbti and Dimension is MbtiDimension which has data as following:

FK_PK_MbtiID, FK_PK_DimensionID
-------------------------------------------------
ISTJ, I
ISTJ, S
ISTJ, T
ISTJ, J
ISFJ, I
ISFJ, S
ISFJ, F
ISFJ, J
INFJ, I
INFJ, N
INFJ, F
INFJ, J
etc...

Person has one personnality,

PK_PersonID, PersonName, FK_MbtiID
---------------------------------------------------
1, John Citizen, ISTJ
2, Patrick Dupont, ISFJ
etc...


I want to retreive the John Citizen given the parameter I, S, T.

I am doing the following:

SELECT personName
FROM Person P, Mbti M, MbtiDimension md, Dimension d
WHERE P.FK_MbtiID = M.PK_PersonID
AND M.PK_MbtiID = md.FK_PK_MbtiID
AND md.FK_PK_DimensionID = d. PK_DimensionID
AND DimensionName = "Introversion"
AND DimensionName = "Sensing"
AND DimensionName = "Thinking"

It does not return anything. However when I do the following:

SELECT personName
FROM Person P, Mbti M, MbtiDimension md, Dimension d
WHERE P.FK_MbtiID = M.PK_PersonID
AND M.PK_MbtiID = md.FK_PK_MbtiID
AND md.FK_PK_DimensionID = d. PK_DimensionID
AND DimensionName = "Introversion"
OR DimensionName = "Sensing"
OR DimensionName = "Thinking"

It returns me 2 lines, I understand why but I do not understand however why I do not have any line with the and command. Am I missing something?

Could you help me, solving this.

thank you in advance. ]]> Microsoft Access cpietri http://forums.databasejournal.com/showthread.php?t=53202 Error in sqlcourse2.com lesson 9? http://forums.databasejournal.com/showthread.php?t=53201&goto=newpost Wed, 09 May 2012 23:15:14 GMT The exercise at the end of the lesson says "Select the item and per unit price for each item in the items_ordered table. Hint: Divide the price by the quantity."

The answer provided is:
select item, sum(price)/sum(quantity)
from items_ordered
group by item;

I think it should instead be:
select item, sum(quantity*price)/sum(quantity)
from items_ordered
group by item;

You can see why by looking at any item with a quantity exceeding 1, say, Sleeping Bags. Three Sleeping Bags were purchased, one at $89.22 and two at $88.70. This is a price per unit of ($89.22 + 2 x $88.70)/3 = $88.87. Compare this to the answer code provided, which generates a result of ($89.22 + $88.70)/3 = $59.31 for Sleeping Bags. It appears that the answer provided is ignoring the fact that two sleeping bags, not one, were purchased at a price of $88.70.

Have I understood this correctly? ]]> Feedback jjbarlas http://forums.databasejournal.com/showthread.php?t=53201 Sql lesson 5 not functioning properly http://forums.databasejournal.com/showthread.php?t=53200&goto=newpost Wed, 09 May 2012 03:48:27 GMT am working on Lesson 5 and it is coming up with the following error "invalid charaters used in command". I c&p from the answer and just changed the field names and the table name. ]]> Feedback SrThespian http://forums.databasejournal.com/showthread.php?t=53200 sp_send_dbmail it was working http://forums.databasejournal.com/showthread.php?t=53199&goto=newpost Tue, 08 May 2012 15:39:42 GMT I have a job which backs up the DB & send an email using sp_send_dbmail, it's been working for months up until now. Nothing to my knowledge, no matter how I run it I receive:

"Msg 22050, Level 16, State 1, Line 0
Error formatting query, probably invalid parameters
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded. "

By restarting the SQL Services will that fix the problem? ]]> Microsoft SQL Server 2005 kappa02 http://forums.databasejournal.com/showthread.php?t=53199 tables get lock during backup in db2 v9.5 http://forums.databasejournal.com/showthread.php?t=53198&goto=newpost Tue, 08 May 2012 08:03:09 GMT hello every body
i have a problem in db2 v9.5.i hope you can help

In IBM DB2 v9.5,when I back up my database and restore it on a new database, some of my tables in restored database are locked.
My back up command is :
CONNECT TO MYDB;
QUIESCE DATABASE IMMEDIATE FORCE CONNECTIONS;
CONNECT RESET;
; BACKUP DATABASE MYDB TO "C:\" WITH 2 BUFFERS BUFFER 1024 PARALLELISM 1 WITHOUT PROMPTING
CONNECT TO MYDB;
UNQUIESCE DATABASE;
; CONNECT RESET;
This problem occurs every time I backup and restore my DB.
thank you in advance ]]> IBM DB2 Hamsoo http://forums.databasejournal.com/showthread.php?t=53198 <![CDATA[Problem on design stage of database modeling for "Myers-Briggs Type Indicator"]]> http://forums.databasejournal.com/showthread.php?t=53197&goto=newpost Tue, 08 May 2012 07:40:58 GMT Hi,

I want to model a database corresponding of the "Myers-Briggs Type Indicator" idea of classification.

Basically he uses dichotomies as follows:
Extraversion (E) - (I) Introversion
Sensing (S) - (N) Intuition
Thinking (T) - (F) Feeling
Judging (J) - (P) Perception

and from the dichotomies a matrix table is produce as follow:
ISTJ ISFJ INFJ INTJ
ISTP ISFP INFP INTP
ESTP ESFP ENFP ENTP
ESTJ ESFJ ENFJ ENTJ

The rules are that:
- E or I cannot be put together it is either E or I, the same goes for S&N, T&F, J&P.
- The order is important.
- The all four concepts should be added together and not one should be missing otherwise it does not make any sense.

There is also the concept of traits which for my perspective should be another table. Ex: Shy is a trait and shy refers to (I) Introversion.

These are the concepts I am a little confuse how to model.

I thought about doing something like

Table characteristic (Id, Name) ex of data:
E, Extraversion
I, Introversion
S, Sensing
etc...

Table Personnality type (Id, Characteristic_Id#1, Characteristic_Id#2, Characteristic_Id#3, Characteristic_Id#4)
ex of data
1, I, S, T, J
etc...

but I feel like the repetition of the filed 4 times is not very proper isn't it?

Could you help me clarify a bit this concept oriented to the database? Note that I will have to do a lot of SQL to attack the db.

Thank you in advance for your time.
Clem. ]]> Database Design cpietri http://forums.databasejournal.com/showthread.php?t=53197 Maximum number of Columns in a table http://forums.databasejournal.com/showthread.php?t=53196&goto=newpost Sat, 05 May 2012 18:22:46 GMT What is the maximum number of columns in a table? As you can see in my attachment, table MortgageBank has 22 tables. Is this ok? Thanks What is the maximum number of columns in a table? As you can see in my attachment, table MortgageBank has 22 tables. Is this ok?


Thanks
Attached Images
File Type: jpg Bank7.jpg (54.7 KB)
]]> Microsoft SQL Server 2008 samijohn http://forums.databasejournal.com/showthread.php?t=53196 Test, just a test http://forums.databasejournal.com/showthread.php?t=53195&goto=newpost Sat, 05 May 2012 11:57:00 GMT Hello. And Bye. Hello. And Bye. ]]> Sybase XRumerTest http://forums.databasejournal.com/showthread.php?t=53195 Move from spreadsheet database to MS SQL Server 7 http://forums.databasejournal.com/showthread.php?t=53194&goto=newpost Thu, 03 May 2012 09:47:40 GMT If you managed a business with 53 staff using a spreadsheet database like the one at www.supersoftbox.com and when you grow up to 100 staff, would MS SQL Server 7 be a good move, or should an MS Access be enough? (moves from selling 200 ellectronic articles in 3 stores, to selling 700 articles in 5 stores) -Laxer If you managed a business with 53 staff using a spreadsheet database like the one at www.supersoftbox.com and when you grow up to 100 staff, would MS SQL Server 7 be a good move, or should an MS Access be enough? (moves from selling 200 ellectronic articles in 3 stores, to selling 700 articles in 5 stores)
-Laxer ]]> MS SQL Server 7/MS SQL Server 2000 Laxer http://forums.databasejournal.com/showthread.php?t=53194 In-memory database interest ? http://forums.databasejournal.com/showthread.php?t=53193&goto=newpost Wed, 02 May 2012 09:20:13 GMT Hi,

I don't really have any trouble but i quite interesting to your judgment. I wonder if in-memory databases are more performant than database management you implement in a programming language. I aware they give some real avantages like SQL interface, Triggers mecanism, concurrency and more. But don't we lose in performance when we connect to the database, then making transactions, committing transactions or transcript the SQL response into a programming langage.

If we just make our own database management, we don't have to connect or committing, we don't have to parse SQL to use data. Don't it be more efficient, quick and performant?

Thank you ! ]]> General Database Discussions seline http://forums.databasejournal.com/showthread.php?t=53193 Really need a help http://forums.databasejournal.com/showthread.php?t=53192&goto=newpost Wed, 02 May 2012 05:50:27 GMT can someone please help me solve this drill?

there's a schema:

ACTIVITY_TYPE (Activity_type_code, Activity_type)
USERS (User_code, User_name, Password)
PROJECT (Project_code, Project_name, Customer_code, Cost, Start_date, End_date)
CUSTOMER (Customer_code, Customer_name)
ACTIVITY (Activity_code, Activity_name, Project_code, Activity_type_code, Complexity)
ACTIVITY_IMPORTANCE (Customer_code, Activity_code, importance)
CREATED_BY (Activity_code, User_code)
ASSIGNED_TO (Activity_code, Developer_name)
DEVELOPER (Developer_name, Seniority)
NEEDED_PARTS (Activity_code, PartID, cost)

and i need to return (in RA) all activities (from (activity_name)) and projects names who belonged to them that meet to following requirtements:

1. high complexity
2.the customer who ordered a project payed for each of the projects he ordered at least 100k
3.at least some of the project's building time was on april 2012

thank you very much!! the help is much needed ]]> General Database Discussions pilot_raam http://forums.databasejournal.com/showthread.php?t=53192 INNER JOIN vs WHERE http://forums.databasejournal.com/showthread.php?t=53191&goto=newpost Tue, 01 May 2012 10:21:41 GMT Hi,

I need to filter out records from a query that select items from 6-7 tables (using union all).
All these tables has a field of transaction guid.
I also have a Transactions table with all tran. guids.

The question is what better to do:
Use an INER JOIN 6-7 times on each item table
or
Add "WHERE itemTransactionGuid in (select trnGuid from Transactions)" on each item table
:confused:

The query is part of a report's SP so performance is important.
Any help will be appriciated.
Thanks!! ]]> Microsoft SQL Server 2008 mrkorr http://forums.databasejournal.com/showthread.php?t=53191