Difference Between SQL Server Codes?

Question

For the school assignment, I need to create a database and run reports. I created the code, and a classmate also created the code, and it works with the same thing, but it is in a format that I have not seen and do not quite understand.

Here is my:

SELECT Course.Name AS 'Course Name', Program.Name AS 'Program Name' FROM Course, Program, ProgramCourse WHERE ProgramCourse.CourseID = Course.ID AND ProgramCourse.ProgramID = Program.ID GO 

And here it is:

 CREATE VIEW NumberOfCoursePerProgram AS SELECT p.name AS ProgramName, c.name AS CourseName FROM Program p JOIN ProgramCourse pc ON pc.ProgramID = p.ID JOIN Course c ON c.ID = pc.CourseID GO 

I performed both queries using the data in the tables I created. They return almost the same results, only in a slightly different order, but answer the question asked. In any case, if I remove p from Program p from my code, it will return an error

An identifier with multiple parts of "p.name" cannot be associated.

So how can SQL Server accept p.name and p.ID etc. when I never set these variables? I don’t quite understand how the code works on it. Mine seems simple and straightforward, and I definitely understand what is happening there. So can someone explain it?

thanks

Answer 1

There are several differences. First, it creates a VIEW , not just a select statement:

 CREATE VIEW NumberOfCoursePerProgram AS 

After creating the view, you can query the view in the same way as the table:

 SELECT * FROM NumberOfCoursePerProgram; 

Secondly, it uses ANSI JOIN, not the implied JOIN. His method is more modern and, most likely, considered more correct by today's standards:

 JOIN ProgramCourse pc ON pc.ProgramID = p.ID JOIN Course c ON c.ID= pc.CourseID 

Instead

 FROM Course, Program, ProgramCourse 

Also note that it assigns table aliases when referencing a table:

 FROM Program p 

p at the end allows you to replace p instead of specifying the entire name of the Program table elsewhere in the query. For example, now you can say WHERE p.Foo > 5 , and not WHERE Program.Foo > 5 . In this case, it is just a shortcut and saves a few characters. However, suppose you referenced the same table twice (for example, JOINING on two different rows in the same table). In this case, you may need to provide aliases for each table in order to eliminate which one is.

Answer 2

They are called an alias in SQL. The alias is primarily designed to provide greater readability and easier code writing.

The readability of a SELECT statement can be improved by providing a table alias, also known as a correlation name or range variable. A table alias can be assigned either with or without the AS keyword:

• table_name AS table alias
• table_name table_alias

So, in your query, p is an alias of Program , so now you can refer to your Program table with the name p , and not the entire Program chain.

Similarly, you can access the column names of your Program table by simply writing p with a dot and a column name. Something like p.column . This method is very useful if you use JOINS , and some of your tables have similar column names.

EDIT: -

Although most of the points are covered in a different answer. I will simply add that you should avoid the habit of the JOINING table as you are doing right now.

you can check

Bad Kicking Habits: Using Old-Style JOINs

from Aaron Bertrand for reference.

Answer 3

 CREATE VIEW NumberOfCoursePerProgram AS BEGIN SELECT p.name AS ProgramName, c.name AS CourseName FROM Program p JOIN ProgramCourse pc ON pc.ProgramID= p.ID JOIN Course c ON c.ID= pc.CourseID END GO 

Note that both the Program and Course tables have a specific table alias.

Part of the selection should indicate the table from which the column name comes from. This is exactly what you did. Your partner simply added aliases to the table names. These aliases are shorter, and the query looks a little smaller than a large wall of text.

Another difference is the use of joints. Joins are typically used to link results from two tables that have a corresponding column.

Typically, columns are the primary key, and the foreign key is in the second table.

Your request is fine, but join syntax is preferred.

Edit: once a view has been created, it is now compiled and can be used in select, as in a table.

 SELECT * FROM NumberOfCoursePerProgram 

If you need to change the view, you can use

 ALTER VIEW NumberOfCoursePerProgram AS ...... ...... 

Answer 4

In SQL Server, you can give the table an alias without AS, its optional.

If you added AS, it will work the same as you.

It may also be useful to note that it uses a connection rather than a where where connection, which is my preference because it is easier to read and update the ethics of the update code.

Sometimes you need to join the WHERE clause due to the many conditions for joining, but this is quite rare in your case.

Answer 5

There are a few things here:

if I remove the "p" from the "Program p" from my code, it will return "Multiple identifier" p.name "cannot be linked."

P is an alias for program . In this way. If you ever need to use program , you can simply call it P

As for what it does: it is better to make a JOIN in this case, and not to make some SELECT tables that you do. In simple cases, this does not really matter, but what if the course and program had millions of lines. Along the way, you basically pick everything. When connecting to ProgramID , you get only those elements in ProgramCourse that correspond to the entries in the program ( ID and CourseID ).

Another important point. You make a simple SELECT . There are objects in SQL called VIEWS that act like a virtual table. Now he can do SELECT * FROM NumberOfCoursePerProgram any time, and he will never have to make any connections and selects again.

Hope this helps ...