December 18, 2016 at 9:05 am
I'm new to SQL so I'm not really sure what to do.
Also not sure if I'm on the right forum for this, If not I'm sorry.
I'm trying to modify this:
SELECT *
FROM Product
WHERE SuppNo = 'S3399214'
AND ProdPrice >= 300;
December 18, 2016 at 9:08 am
allenjoey24 (12/18/2016)
I'm new to SQL so I'm not really sure what to do.Also not sure if I'm on the right forum for this, If not I'm sorry.
I'm trying to modify this:
SELECT *
FROM Product
WHERE SuppNo = 'S3399214'
AND ProdPrice >= 300;
Your query looks like it should run OK. What problem are you having? What do you mean by 'base table'?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
December 18, 2016 at 9:18 am
LOL that is what I thought! But the textbook I'm reading wants me to query and "modify it somehow so that it uses base tables only". I'm not really sure what a base table is.
An internet definition I came across says
"A base table is a physical structure that contains stored records. This structure can have any physical format as long as it can be presented to the user as a collection of rows and columns."
But I don't think that's helpful.
December 18, 2016 at 9:34 am
allenjoey24 (12/18/2016)
LOL that is what I thought! But the textbook I'm reading wants me to query and "modify it somehow so that it uses base tables only". I'm not really sure what a base table is.An internet definition I came across says
"A base table is a physical structure that contains stored records. This structure can have any physical format as long as it can be presented to the user as a collection of rows and columns."
But I don't think that's helpful.
Your table "Product" is the base table and even fits the description you provided.
Is it possible that you need to modify the query to create a view that references base tables?
December 18, 2016 at 9:49 am
I think that might be what it meant. So is it asking me to modify my queries so that instead of referencing a column on a table like Table.Column to just Table. Not sure if I'm making sense.
The question was
"For the query in problem 5, modify the query so that it uses base tables only. "
If I'm already using a base table, does that mean that I already completed the problem?
December 18, 2016 at 10:02 am
allenjoey24 (12/18/2016)
I think that might be what it meant. So is it asking me to modify my queries so that instead of referencing a column on a table like Table.Column to just Table. Not sure if I'm making sense.The question was
"For the query in problem 5, modify the query so that it uses base tables only. "
If I'm already using a base table, does that mean that I already completed the problem?
If a view is required this is the Code:
CREATE VIEW vw_product
AS
SELECT *
FROM Product
WHERE SuppNo = 'S3399214'
AND ProdPrice >= 300;
SELECT * FROM vw_product;
If the solution requires only a query on the base tables then your initial query is the answer.
December 18, 2016 at 10:19 am
It didn't say anything about turning it into a view, I appreciate your help.
Can I ask how you came to that conclusion? I have two other questions that are just like that one. The other two tables have INNER JOINS in them, would that still count as a base table?
December 18, 2016 at 10:58 am
what text book are you reading...?
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
December 18, 2016 at 11:02 am
Database Design, application development, and Administration by Mannino
December 19, 2016 at 3:16 am
I must admit that this time I have no idea what is expected. The query is OK, so I am not sure what else could be expected....
December 19, 2016 at 3:28 am
From what we know, we can't assume that Product is a table and not a view. What does this query return?SELECT
SCHEMA_NAME(schema_id) + '.' + name AS ObjectName
,'View' AS ObjectType
FROM sys.views
WHERE name = 'Product'
UNION ALL
SELECT
SCHEMA_NAME(schema_id) + '.' + name
,'Table'
FROM sys.tables
WHERE name = 'Product'
John
December 19, 2016 at 3:34 am
I did not think of that...that would make a lot of sense....
December 19, 2016 at 4:28 am
allenjoey24 (12/18/2016)
I think that might be what it meant. So is it asking me to modify my queries so that instead of referencing a column on a table like Table.Column to just Table. Not sure if I'm making sense.The question was
"For the query in problem 5, modify the query so that it uses base tables only. "
If I'm already using a base table, does that mean that I already completed the problem?
what does the "query in problem 5" look like?
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
December 19, 2016 at 9:33 pm
Is product not possibly a view?
December 20, 2016 at 12:03 am
I think if you wanted a correct answer, you will have to post the full question. In what you gave, I will have to assume that Product is not an actual table, but a view. My reasoning being that a base table is the actual table, not derived or otherwise.
That said, learn not to use "SELECT *" and learn to add the schema "dbo."
SELECT SuppNo, Product, (Other columns)
FROM dbo.Product
WHERE SuppNo = 'S3399214'
AND ProdPrice >= 300;
"SELECT *" is ok when running ad-hoc queries, but should not be used in permanent code. The "dbo." part helps SQL to determine "which schema" faster.
Sorry for repeating... Did not read all the comments.
5ilverFox
Consulting DBA / Developer
South Africa
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply