Jump to content
Sign in to follow this  
Wrexy

Anyone know SQL?

Recommended Posts

I am looking at this T-SQL example and trying to figure out a few things that I cant find info on.

 

 

The example:

 

 

 

Write a query that counts the number of sales orders that each sales person has achieved in the years 2003 and 2004. Use the Sales.SalesOrderHeader and Sales.SalesPerson table.

a. In the query window, type the following SQL statements.

 

 

The answer:

 

 

SELECT SALESPERSONID,

(SELECT COUNT(*) FROM Sales.SalesOrderHeader

WHERE SalesPersonID=a.SalesPersonID AND

Year(OrderDate)='2002') AS OrdersIn2002,

(SELECT COUNT(*) FROM Sales.SalesOrderHeader

WHERE SalesPersonID=a.SalesPersonID AND

Year(OrderDate)='2003') as OrdersIn2003,

(SELECT COUNT(*) FROM Sales.SalesOrderHeader

WHERE SalesPersonID=a.SalesPersonID AND

Year(OrderDate)='2004') as OrdersIn2004

FROM Sales.SalesPerson AS a

 

 

1) How did he come up with the name SALESPERSONID if its not in the example question ? (In the first line) Could he have named it anything

 

2) What does the ' ' do ? I am guessing it means to only get data from that column

 

3) How did he get the name OrderDate ? (line 4,7,10) If that information hasnt been given to him.

Share this post


Link to post
Share on other sites

edit: ' ' are written with 2002 2003 2004

Share this post


Link to post
Share on other sites

1) That is the name of the entity (attribute) in the table

2) Used in many programming languages to denote a string literal, i.e. look for that literal value between the quotes

3) I'm not sure what the hell that is, possibly a parameter for a function, but I'm too lazy to look it up. Sorry, I haven't done any SQL in a while

Share this post


Link to post
Share on other sites

1, 3 questions)I guess if that's the only information given to you then you have to use some imagination to come up with your own column names. Unless you are actually given the db with tables precreated.

 

2) quotes mean that you compare to values within the quotes - not a column in the db.

 

Also it's rather shitty way to use subqueries

Edited by ru55kj

Share this post


Link to post
Share on other sites

The guys above me are bang on, but to expand a little on Shadow_Matrix with the stuff I remember struggling with when I started with SQL and general programming:

 

2) Used in many programming languages to denote a string literal, i.e. look for that literal value between the quotes

There are no tables or databases in SQL that have spaces in them. For that reason, you don't have to refer to a table or a database using any kind of quotes. Think USE databasename; or SELECT * FROM tablename; You also don't have to use quotes for numeric values (but you can if you want, which is what the example does), as you can't interpret 123 as anything more or less than 123, for example. Now think about if you're searching for a phrase like 'the quick brown fox'. Due to the spaces in between those four words, the SQL wouldn't understand where the phrase you're searching for, starts and ends, so you have to enclose the phrase in quotes to escape the phrase from the rest of the query.

 

3) Year(OrderDate) - This looks like a table name. Braces are acceptable in table names.

 

I think overall though, you're missing a copy of the schema. The schema will tell you what the database is called, what the tables are called, what the columns are called, etc. If you don't have the schema, then you're free to bullshit table and column names as you see fit :)

Edited by pyro

Share this post


Link to post
Share on other sites

I was going to say, there are field references in that query that probably exist within the example tables. It does look like there is information missing.

 

1) I suspect he would of selected * from Sales.salesperson (SELECT * from sales.salesperson) which would display all fields and all data on that table.

From there you can see field names, and SALESPERONID is a field on the table. Thats what i would of done to view all fields linked to the table in question.

 

3) Year(orderdate) looks like a field from sales.salesperson.

 

That query is a bit messy anyway.

Share this post


Link to post
Share on other sites

Thanks for they help, good to know ns can still work.

 

 

Yes there is a database that goes with it, I think I should tell you my story:

This is the second part of Microsofts IT academy training for SQL (MCTS)

I should know all this from the first training (MTA) that I did, But the lab exercises (where you use a virtual pc to solve DBA/Developer issues) didnt work so I was forced to skip them.

 

Now I am on the second part, and since then, Micrsoft has updated to a new server and the old problems are fixed.

 

Might be worthwhile to go back and do the MTA again.

Edited by Wrexy

Share this post


Link to post
Share on other sites

Another one:

 

 

"Human Resources has requested a report for each employees firstname, lastname, hiredate and sickleavehours for all employees"

 

(From before I have learnt to go through the database and find the correct table by selecting "view top 1000 fields" to find where shit is located)

 

firstname and lastname are in person.contact, and hiredate, sickleavhours are in humanresources.employee.

 

this is what I did:

 

USE Adventureworks

GO

SELECT firstname, lastname, hiredate, sickleavehours

FROM Person.contact, humanresources.employee

 

 

 

And it worked but took ages (so long that I had to stop it)

 

 

the correct answer was:

 

 

USE adventurworks

GO

SELECT firstname,lastname,hiredate, sickleavehours

FROM person.contact AS c INNER JOIN

humanresources.employee AS e

ON c.contactID= E.contactID

 

 

 

1) whats with the AS c and AS e ? changing the letters gives a error, so I am guessing the c and e are saved somewhere, Or is that the letter of the drive its saved on.

Edited by Wrexy

Share this post


Link to post
Share on other sites

All the C and E are used for is aliasing the tables. It just saves you having to type the full table name each time and can make field names / tables easier to read.

 

In that example C = person.contact and E=humanresources.employee

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

Sign in to follow this  

×