Structured Query Language
A FOREIGN KEY is a key in another table that relates from the PRIMARY KEY in the parent table. A Foreign Key is needed in the related table to connect data from the primary table.
Let's take a look at the following example
Course Table
| CourseID | Course |
| 1 | Math 101 |
| 2 | English 101 |
Students Table
Every table must be identified with a unique key or the Primary Key. Primary Key uniquely identifies each record in the table. It makes searching of record faster.
A Primary Key cannot contain a NULL values and must be unique in every record.
Different DBMS have different implementations to set the primary keys, so let’s see each of them below.
SQL PRIMARY KEY Syntax
Besides creating the database from the DBMS software like Oracle, MyQL, Microsoft Access etc. We can also create database with the query language. It is as simple as writing a SELECT, INSERT, UPDATE, DELETE Statement.
Creating a database using query language is very useful if for example you deploy your application to a computer that doesn’t have a database yet.
We can create a new database with the following syntax
SQL Create Database Syntax
The FULL JOIN keyword will return all rows from both tables that you specified. The same as other JOINS, FULL JOIN has the same syntax as show below.
Consider the following table for this exercise
Users
The RIGHT JOIN keyword works the same way as LEFT JOIN keyword except that it returns all rows from the right table. This means that even there is no matching record from the left join the rows will still show from the second table.
Consider the following table for this exercise
Users
There are times when you want to retrieve all data from one table even there is no matching record from both tables. Unlike SQL INNER Join, SQL LEFT Join will still show you the result from the left table or the first table. SQL INNER Join as discussed in the previous chapter will only show records that match on both tables based on the column you specified.
SELECT column_name(s) FROM First_table_name LEFT JOIN Second_table_name ON First_table_name.column_name = Second_table_name.column_name
SQL INNER JOIN is almost the same as the normal join. But the INNER JOIN will only list those rows which have at least one matching value from both tables.
SQL INNER JOIN Syntax
SELECT column_name(s) FROM First_table_name INNER JOIN Second_table_name ON First_table_name.column_name= Second_table_name.column_name Consider the following table for this exercise
Users
In this tutorial I will teach you on how to enable remote connection on SQL Server. I will use SQL Server 2005 Express Edition on this tutorial. However, this is also applicable to any version of SQL Server. The only difference is the GUI for different kinds of SQL Server version.
This tutorial is very useful if you want to access your SQL Server database from other computer and you want to connect two or more computers from your network.
SQL JOIN is used to combine two or more tables based on primary key and foreign keys.
We use Join in conjunction with the table we normalize during database design. Consider a Department table we have in our previous example. We create a Department table that serves as our master file to be called from other table.
SQL JOIN can be very time consuming on large table, e.g. one with millions of records, since it has to match each row of one table, and check it with each row of other tables mentioned in the JOIN keyword.
As in normal life we associate some nicknames with people, or call them with names that are not there actual names SQL also allows us to do the same with the table columns, since it is much easier to memorize, view as it is in real life. Now the point where SQL ALIAS becomes important is that whenever we have a table which has complex column names we can easily rename the column understand it easily.
NOTE that SQL Alias will not change the actual name of the column in the table.
