|
I just took an online evaluation of my SQL skills (SQL-FU?).
How would you answer this question:
"
Given the following SQL statement:
SELECT customers.customerid, currentbalance
FROM customers
LEFT OUTER JOIN customer_balance ON customers.customerid =
customer_balance.currentbalance
Which of the following statements correctly describe the result set?
A. Only customerid values with a matching value in the customer_balance table will be displayed in the result set.
B. All currentbalance columns from the customer_balance table will be displayed in the result set. Customerid values will be displayed for each customerid with a matching value in the customers table. Null will be displayed as the value for currentbalance for all customerids with no match in the customer_balance table.
C. All currentbalance columns from the customer_balance table will be displayed in the result set. Customerid values will be displayed for each customerid with a matching value in the customers table. A customerid value of null will be displayed for each customerid without a matching value in the customers table.
D. All customerid columns from the customer_balance table will be displayed in the result set. Current balance values will be displayed for each customerid with a matching customerid value in the customer_balance table. Zero will be displayed as the value for current balance for all customerids with no match in the customer_balance table.
E. All customerid columns from the customers table will be displayed in the result set. Current balance values will be displayed for each customerid with a matching customerid value in the customer_balance table. Null will be displayed as the value for current balance for all customerids with no match in the customer_balance table.
"
I decided that customers.customerid = customer_balance.currentbalance was a typo and chose E.
Ah, well, at least I got 83rd percentile. 
|
|
|
|
|
Assuming that it was a typo (which it must be since there was no option for "this is crap and if I can't pick this error up I shouldn't be using SQL"), then answer E is correct.
|
|
|
|
|
hi
read my this query
insert into attandance values('18-AUG-2009','General','1','IT',(to_date('10:32:00 AM',hh:mi:ss
tt'),(to_date('10:32:00 AM','hh:mi:ss tt)),'jawad kahtri');
when i use this show error
error is
Bind variable "32" not declared.
thanks in advance.
jawad khatri
|
|
|
|
|
If nothing else you are missing some single quotes, one between AM', and hh:mi:ss
the other between ss tt and ))
This fixes those two errors for you:
insert into attandance values('18-AUG-2009','General','1','IT',(to_date('10:32:00 AM','hh:mi:ss tt'),(to_date('10:32:00 AM','hh:mi:ss tt')),'jawad kahtri');
Whether there is anything else wrong time will tell, but it had no chance of working without those quotes!
|
|
|
|
|
thanks sir
but when i insert this show error
ERROR at line 1:
ORA-00907: missing right parenthesis
Name Null? Type
------------------------------- -------- ----
ATT_DATE NOT NULL DATE
SHIFT NOT NULL VARCHAR2(8)
EMP_ID NOT NULL NUMBER
DEP_ID VARCHAR2(20)
TIMEIN DATE
TIMEOUT DATE
EMP_NAME VARCHAR2(20)
this is my table iwant to insert time in time in or time out.
please help me.
thanks
|
|
|
|
|
Well, it's missing a right parenthesis - ie: )
insert into attandance values('18-AUG-2009','General','1','IT',
(to_date('10:32:00 AM','hh:mi:ss tt'), <----------- MISSING FROM HERE
(to_date('10:32:00 AM','hh:mi:ss tt')),
'jawad kahtri');
If you break it down like this, you can clearly see that the missing parenthesis is on the first to_date - compare it to the one below it!
|
|
|
|
|
sir now i write this query
insert into attandance values
(to_date('18-AUG-2009','DD-MON-YYYY')),
'General',
'1',
'IT',
(to_date('10:32:00 AM','hh:mi:ss AM')),
(to_date('10:32:00 AM','hh:mi:ss AM')),
'jawad kahtri');
show error in line 2
ERROR at line 2:
ORA-00933: SQL command not properly ended
what is means ???
|
|
|
|
|
The general form of this type of insert is this:
insert into TABLE (field list) values (value list)
You are now missing an opening parenthesis for the value list. Since you aren't specifying a field list, your list of values must have one for every field in the table, in the correct order.
insert into attandance values ( <----------------- ADDED PARENTHESIS HERE
(to_date('18-AUG-2009','DD-MON-YYYY')),
'General',
'1',
'IT',
(to_date('10:32:00 AM','hh:mi:ss AM')),
(to_date('10:32:00 AM','hh:mi:ss AM')),
'jawad kahtri'
);
|
|
|
|
|
|
I do suggest you get a book on PLSQL if you are going to continue to use Oracle (or any sql database). The problems you faced are straight observational details, you missed single quotes and brackets, this is probably from lack of practice so working through a bunch of examples will give you both practice and knowledge in SQL syntax.
|
|
|
|
|
_Damian S_ wrote: Whether there is anything else wrong time will tell
"My interest is in the future because I'm going to spend the rest of my life there." - Charles F. Kettering
|
|
|
|
|
Dear All,
Could you please tell me about the Database backup in Sql Server 2005 that was selected "Append to the Existing backup set" in the "Backup to Existing Media set".Please tell me if i restore the Backup file database would have duplicates datas or it will remove the duplicates automatically?
hope your help as earlier.
|
|
|
|
|
Create a simple database, add a few rows, then test each variation to see what happens. Then you can write an article explaining it to us.
Henry Minute
Do not read medical books! You could die of a misprint. - Mark Twain
Girl: (staring) "Why do you need an icy cucumber?"
“I want to report a fraud. The government is lying to us all.”
|
|
|
|
|
ap·pend [ ə pénd ] (past and past participle ap·pend·ed, present participle ap·pend·ing, 3rd person present singular ap·pends)
transitive verb
Definition:
1. add extra information: to add extra information to something, especially to a document
2. add authorized signature: to add an authorized signature to a bill or an official agreement as a final part of the ratification or agreement process ( formal )
All principals to the sale must append their signatures.
3. attach something: to attach or fasten something to something else
[Mid-17th century. < Latin appendere "hang upon" < pendere "hang"]
No, you will not have duplicate records unless your database does.
"My interest is in the future because I'm going to spend the rest of my life there." - Charles F. Kettering
|
|
|
|
|
Hi,
I have a table in my sql report which has repeated values for the repeated values of one column.. something like master table with multiple values and detail table with multiplw values.
How can I implement it in sql report?
as i cannot insert a table inside a table!!...
|
|
|
|
|
I'm not sure what you mean by "SQL Report".  Are you using a report writer ?
Using a report writer, you would create 3 groupings:
1 Master level
2 child level
3 grandchild level
You can then place summary fields at each grouping footer and display headers for each group change.
Maybe list the data you are trying to report on and we can give a more detailed solution.
|
|
|
|
|
 sorry for the confusion,
i reworked the report, this is a sql report and am developing it using vs.
I need to take sum of the values in a table column but there is no way i can do that as all i can access in a new textbox are globals, fields and table textboxes don't figure in globals!!!
also master detail kind of display using the values from the stored procedure is unknown to me.
|
|
|
|
|
I don't think I can help you. I'm not experienced with SQL Report. I'm much better at Crystal Report in Visual Studio 2005 Professional.
|
|
|
|
|
I have a stored proc that inserts data into four tables and the stored proc executes perfectly fine when executed in sql server.To execute the stored procedure from my data access layer I chose a method which call the method in class that connects to my database, but now when trying to run the whole application, it comes back to me with the "StackOverflowExeption" any idea what might be causing that? because the error occurs right on my connection string.
ML Lingwati
|
|
|
|
|
Try posting your code and connection string.
|
|
|
|
|
Hi all,
I am trying to write a procedure which takes 1 parameter as @StudentID and returns Student Information for the given StudentId Parameter form Student and StudentDetail table.
Now i want that if parameter is not passed(i.e NULL) then procedure should return all students information,but if StudentId passed then for that student only...
I am trying this by using COALESCE function in sql server 2005.
Following is DDL for my procedure.
CREATE PROCEDURE [dbo].[GetStudentById]
@StudentId AS NUMERIC
AS
BEGIN
SELECT
S.StudentId,
S.StudentFName,
S.StudentMName,
S.StudentLName,
S.Address,
SD.StudentClassId
FROM
[dbo].Student S
JOIN StudentDetail SD ON S.StudentId = SD.StudentId
WHERE
S.StudentId = COALESCE(@StudentId,NULL)
END
But not getting desired result ..When i am passing NULL 0 rows are returned.
please help me out...
Thank & Regards
Dipak Thesiya
dipak
|
|
|
|
|
Your coalesce is wrong. Try this
WHERE S.StudentId = COALESCE(@StudentId,S.StudentId)
what you were doing is saying when (@StudentId use null, what you really wanted to say was when (@StudentId is null don't compare - which effectively means compare to the column value.
Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP
|
|
|
|
|
Thanks BOB....its working fine....and ur description is very clear..i mean short and sweet...
thanks.... 
dipak
|
|
|
|
|
No problem, pleased to help
Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP
|
|
|
|
|
I use the following
where (IsNull(@StudentID,0) = 0 OR StudentID = @StudentID)
and have done for years, I wonder if there is a benifit to using COALESCE. It is certainly more elegant than using the OR method.
Never underestimate the power of human stupidity
RAH
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
