1. The master database that ships with sql2000, why is it there and what is the main purpose of it?
Believe it or not, SQL existed for years before MicoSoft was a company. They've got to try and make their SQL act like standard SQL. "master" is their way of getting this done. How does the system know that you added a new DB to the list? It's in master. How does the system execute stored procs that aren't in your DB? The system knows to look in master to find code that's not in your DB. "msdb" was their way of enhancing standard SQL, so it looks there first. It uses that to do overall management that is not standard SQL. Then over time the separation of tasks between these two DBs blurred and master does custom MS work as well. Anyway, touch these two DBs at your peril. (I'd like to talk to the SQL people that were in my company about their modifying these DBs before they left.) "model" tells the system what structure to put on new DBs added to the system. Touch that one after you are a guru. "tempdb" is a place to store temporary information. This info lasts for differing amounts of time depending on the method of creation. Don't put anything there that you want to keep. "NorthWind" and "Pubs" are example user DBs supplied with SQL server.
2. Why are tables that are prefixed with sys not visible like the other tables.
Why is the sun brighter than the moon? There are two types of tables, "system" and "user". Generally "user" tables are created by you and "system" tables are created by MicoSoft. Both types of tables are visible in Enterprise Manager and in Query Analyser's browser window. In EM they are bunched together in one list. In QA they have their separate tree views. You have to have administrator access to be able to look at the information on system tables. I haven't tried to alter any data on these tables but it wouldn't surprise me if an administrator couldn't directly change information in these tables. It would surprise me if they could. I'm in no mood to find out. Anyway the development tools are built to interface with user tables so if you use their tools to look for tables, the system tables won't be visable. You don't have to use the development tools to build queries and I've done just that to look at these tables. If I handed that code over to someone who doesn't have administrator access, it would blow up in their face. You have to know what you are doing when working with system tables.
Since the last update on the table is this query still the perferred method?
Like I said in the last post, this depends. Do you want to find the lowest price that will do the job, no matter what company supplies the product? Then, the answer is yes. Otherwise, no.
You need to know what the select statement does because that determines the results you get. The results you want change based on business needs. What does "group by" do? What does "order by" do? What's the difference between "where" and "having"? What are aggregate functions and why would you use them? This takes time and work on your part. The best way to learn this is by playing on QA. Figure out what you want to find out, then see if you can build a command that will find it out.