February 22, 2015 at 3:41 pm
I need to create a few select queries based on an er diagram.
These are my Create Table statements and import statements:
Create Table Agent (Aid integer primary key, Pid integer, aName text);
Create Table Product (Pid integer primary key, pName text);
Create Table Supplier (Sid integer primary key, sName text);
Create table Supplies (Sid integer, Pid integer, price decimal(8,2));
.import agent.txt Agent
.import product.txt Product
.import supplier.txt Supplier
.import supplies.txt Supplies
I think I got all my crete table statements are correct. I am very new to sql so any help would be appreciated!
I need to Find the number of agents for each supplier that has at least one agent. The result should be tuples of the form (sid, sName, number of agents)
-Select Sid, sName, count(Aid) from Agent A join Supplier S on (S.Sid = A.Sid) group by S.Sid, S.sName, Aid;
But it gives me this error: no such column: A.Sid
Im thinking I might have a problem with my create table statement and/or primary key statements?
Thanks!
February 23, 2015 at 3:53 am
I would change your table definitions this way:
CREATE TABLE Agents (
agent_id integer PRIMARY KEY
,product_id integer
,agent_name varchar(50)
);
CREATE TABLE Products (
product_id integer PRIMARY KEY
,product_name varchar(50)
);
CREATE TABLE Suppliers (
supplier_id integer PRIMARY KEY
,supplier_name varchar(50)
);
CREATE TABLE Supplies (
supplier_id integer
,product_id integer
,price decimal(8,2)
,PRIMARY KEY(supplier_id, product_id)
);
text is a deprecated data type. In this case varchar(50) is probably enough.
Tables are collections of rows, so use plural nouns.
Don't use obscure column names such as sid or pid, use product_id instead.
Your Agents table assumes that a single product is sold by an Agent: is this correct?
As far as your query is concerned, this should do:
SELECT S.supplier_id, S.supplier_name, COUNT(DISTINCT A.agent_id)
FROM Suppliers AS S
INNER JOIN Supplies AS SU
ON S.supplier_id = SU.supplier_id
INNER JOIN Agents AS A
ON SU.product_id = A.product_id
-- Gianluca Sartori
February 23, 2015 at 5:44 am
spaghettidba (2/23/2015)
As far as your query is concerned, this should do:
SELECT S.supplier_id, S.supplier_name, COUNT(DISTINCT A.agent_id)
FROM Suppliers AS S
INNER JOIN Supplies AS SU
ON S.supplier_id = SU.supplier_id
INNER JOIN Agents AS A
ON SU.product_id = A.product_id
Don't forget the GROUP BY at the end.
SELECT S.supplier_id, S.supplier_name, COUNT(DISTINCT A.agent_id)
FROM Suppliers AS S
INNER JOIN Supplies AS SU
ON S.supplier_id = SU.supplier_id
INNER JOIN Agents AS A
ON SU.product_id = A.product_id
GROUP BY S.supplier_id, S.supplier_name;
Gianluca's other comments are right on. The names and data types you pick now are things you'll live with for a while, so pick wisely.
February 23, 2015 at 6:24 am
Ed Wagner (2/23/2015)
Don't forget the GROUP BY at the end.
GAH! My bad, I forgot to select the last line while pasting. Thanks for spotting, Ed.
-- Gianluca Sartori
February 23, 2015 at 6:50 am
February 23, 2015 at 9:01 am
Heh... table names as plural nouns. Makes for some interesting and non-automatable naming conventions.
So, let me ask... do you have an Employee table or an Employees table? Do you have a Company table or a Companies table? Do you have an Invoice table or an Invoices table? Do you have an InvoiceDetail table or an InvoicesDetails table? 😉
Everyone has their own standard but to avoid things like the differences in pluralization of words, I name tables after what a single row contains. It also helps in the naming of Identity columns. For example, I have a Company with a CompanyID column. If it were a Companies table, I just couldn't bring myself to having a CompaniesID column nor a CompanyID column in a Companies table.
Like I said, to each their own but I typically won't pluralize table names.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 23, 2015 at 9:47 am
Highly debatable...
The ISO standards suggest that table names are pluralized. I admit that I agree with Celko on this one.
I have to admit that the points you make are good. Actually, I still am in doubt for the English language, but I would never ever use singular table names for the Italian language. A table named "Employee" might also sound good, but "Dipendente" sounds so horribly wrong that I would never allow it into production. 🙂
-- Gianluca Sartori
February 23, 2015 at 1:01 pm
spaghettidba (2/23/2015)
Highly debatable...The ISO standards suggest that table names are pluralized. I admit that I agree with Celko on this one.
No problem... I don't follow those either. 😛
--Jeff Moden
Change is inevitable... Change for the better is not.
February 23, 2015 at 1:28 pm
Jeff Moden (2/23/2015)
Heh... table names as plural nouns. Makes for some interesting and non-automatable naming conventions.So, let me ask... do you have an Employee table or an Employees table? Do you have a Company table or a Companies table? Do you have an Invoice table or an Invoices table? Do you have an InvoiceDetail table or an InvoicesDetails table? 😉
Like I said, to each their own but I typically won't pluralize table names.
I got through 3 years of Jr High English classes, 3 years of High School English classes, a number of college English classes and a whole lot of graduate school papers/thesis using one simple rule. Does it sound good? To this day, I could not diagram a sentence if my life depended upon it. But I know what "sounds good"!
So that is what I use in my naming conventions. It has to sound good and make sense. I'm with Jeff on this one.
__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/
February 23, 2015 at 2:34 pm
Jeff Moden (2/23/2015)
spaghettidba (2/23/2015)
Highly debatable...The ISO standards suggest that table names are pluralized. I admit that I agree with Celko on this one.
No problem... I don't follow those either. 😛
I guess we'll have to agree that we disagree 😛
-- Gianluca Sartori
February 23, 2015 at 7:08 pm
spaghettidba (2/23/2015)
Jeff Moden (2/23/2015)
spaghettidba (2/23/2015)
Highly debatable...The ISO standards suggest that table names are pluralized. I admit that I agree with Celko on this one.
No problem... I don't follow those either. 😛
I guess we'll have to agree that we disagree 😛
Actually, we only partially disagree. There's some good stuff in the ISO and ANSI specs especially when it comes to very basic principle dictated by MIL-TP-41.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 23, 2015 at 7:28 pm
LinksUp (2/23/2015)
Jeff Moden (2/23/2015)
Heh... table names as plural nouns. Makes for some interesting and non-automatable naming conventions.So, let me ask... do you have an Employee table or an Employees table? Do you have a Company table or a Companies table? Do you have an Invoice table or an Invoices table? Do you have an InvoiceDetail table or an InvoicesDetails table? 😉
Like I said, to each their own but I typically won't pluralize table names.
I got through 3 years of Jr High English classes, 3 years of High School English classes, a number of college English classes and a whole lot of graduate school papers/thesis using one simple rule. Does it sound good? To this day, I could not diagram a sentence if my life depended upon it. But I know what "sounds good"!
So that is what I use in my naming conventions. It has to sound good and make sense. I'm with Jeff on this one.
Heh... this all reminds me of how strange the English language is. Most folks would say that they're "going to have a couple of beers" usually meaning two beers. If "beer" is one beer, then at least two beer would be beers, which means that a "couple of beers" actually means at least 4 beers. 😛
Then there are wonderful pluralizations that change the whole word. There can be two people but not two peoples unless you're talking about more than one group of people as in a country inhabited by two peoples. Instead, you have to say two persons or two people. Oddly enough, when the plural doesn't have a number with it, it becomes people again as in those people over there. Well, except for how I just used it. 😛
That's all part of why I don't use plurals in database names, table names, or column names unless the column is denormalized as a {gasp!} CSV or a bit of XML Shinola. :hehe:
p.s. That's why I say I'm going to "have some beer". No high or low limits that way. :w00t:
--Jeff Moden
Change is inevitable... Change for the better is not.
February 24, 2015 at 1:10 am
Jeff Moden (2/23/2015)
So, let me ask... do you have an Employee table or an Employees table? Do you have a Company table or a Companies table? Do you have an Invoice table or an Invoices table? Do you have an InvoiceDetail table or an InvoicesDetails table? 😉
Fish, fishes or school?
Goose, geese or gaggle?
Lion, lions or pride?
I'd love to know what ANSI says about those.
I tend to go with the plurals (except fishes) and not the classifiers, but I draw the line at InvoicesDetails (I use InvoiceDetails).
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
February 24, 2015 at 2:29 pm
Jeff Moden (2/23/2015)
Heh... table names as plural nouns. Makes for some interesting and non-automatable naming conventions.So, let me ask... do you have an Employee table or an Employees table? Do you have a Company table or a Companies table? Do you have an Invoice table or an Invoices table? Do you have an InvoiceDetail table or an InvoicesDetails table? 😉
Everyone has their own standard but to avoid things like the differences in pluralization of words, I name tables after what a single row contains. It also helps in the naming of Identity columns. For example, I have a Company with a CompanyID column. If it were a Companies table, I just couldn't bring myself to having a CompaniesID column nor a CompanyID column in a Companies table.
Like I said, to each their own but I typically won't pluralize table names.
+1
If a row represents a a singular entity, I prefer to name the table with a singular noun.
Don Simpson
February 24, 2015 at 2:59 pm
dwain.c (2/24/2015)
Jeff Moden (2/23/2015)
So, let me ask... do you have an Employee table or an Employees table? Do you have a Company table or a Companies table? Do you have an Invoice table or an Invoices table? Do you have an InvoiceDetail table or an InvoicesDetails table? 😉Fish, fishes or school?
Goose, geese or gaggle?
Lion, lions or pride?
I'd love to know what ANSI says about those.
I tend to go with the plurals (except fishes) and not the classifiers, but I draw the line at InvoicesDetails (I use InvoiceDetails).
Well, the Italian language doesn't have all this irregular stuff and I can tell for sure that I've never come across any database with singular table names in my whole career. On the other hand, I'm pretty sure that no Italian member was sitting in the ANSI committee when the standard was definded, so they must have had some other languages in mind 😛
-- Gianluca Sartori
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply