SQL Foreign Key and Joins

Hello everybody! As I promised I am going to write about Foreign Key and Joins in SQL.

Foreign  Key

Foreign Key are keys that references the primary key of another table. Let's create the table Salesman:

Create Table Salesman

And after I will create the Sale table that will references the Client and Salesman tables:

Create Table Sale

Take a look, I told the database the columns ClientID and SalesmanID are Foreign Key and after I used the keyword REFERENCES to reference the table and between parenthesis the column referenced.

Just remembering, you can not insert an ID for Client and Salesman in Sale table that not exists in Client and Salesman tables. Always a value of a Foreign Key should exists in the referenced table.

Joins

Now let's talk about the existing Joins in SQL:

Inner Join

Inner Join

Starting by INNER JOIN, as the image shows, returns all the data associated. I will select the client's name, salesman's name and the value of the sales using INNER JOIN:

Inner Join

Left Join

Left Join

LEFT JOIN, as the image shows, returns all data from the table on the left side of the JOIN and all the associated data from the right table. 

Left Join

Right Join

Right Join

RIGHT JOIN is like the LEFT JOIN, but inverted. I will change the LEFT JOIN in the previous query for a RIGHT JOIN

Right Join

In that case the client John Doe disappeared.

Outer Join

Outer Join

OUTER JOIN, known as FULL OUTER JOIN, returns all data from all tables.

Outer Join

Left Excluding Join

Left Excluding Join

The contrary of LEFT JOIN, returns all data of the left side table not associated with the right side table.

Left Excluding Join

Right Excluding Join

Right Excluding Join

Like the LEFT EXCLUING JOIN but with the right side.

Right Excluding Join

Outer Excluding Join

Outer Excluding Join

OUTER EXLUDING JOIN returns all data that are not associated.

Outer Excluding Join

You can also mix more than one join:

Mix of Joins

Image explaining all JOINS:

All Join

Alias

As you can see, I used some acronyms in my queries like "SM", "S", "C", "ClientName", "SalesmanName", this is called ALIAS, it is like a nickname for tables and columns. The first three are ALIAS for tables, I used to better identify the tables in my queries; take a look: I used only the alias on my JOINS. And the last two are ALIAS for columns to better identify them in the results. The use of alias is not required, you decide when to use, use if you think that ALIAS will facilitate your queries.

SQL's Basic Operations

Hi everybody! Today I am going to write for you, my friends in the beginning of career, about the four basic operations of a database: Select, Insert, Update, Delete and also about Primary Key and how to create tables. In the next post I will write about Foreign Key and Joins.

Let's begin with the creation of the Client table, with the fields: ClientID, Name, BirthDate and Address.

Creating the Client table

Take a look at the ClientID, it has the properties: PRIMARY KEY, IDENTITY and NOT NULL.

Primary Key: It is the table's identifier field, unique, the value can not repeat in the table, never can be NULL, in this table the primary key is a simple primary key, but the key can be a composite key with the same rules of the primary key but with two or more fields. Let's see an example, the creation of the City table with composite key: City and State. You can insert the values: City: Santos and State: São Paulo just one time.

Composite Key

Not Null: As the name says, the field does not accepts NULL values.

Identity: It means the field is auto increment, each insert you do not need to pass the value of the field, the SQL will get the last value of the field and sum 1. You can configure the IDENTITY, in example, IDENTITY (1,2) the first value is the start value, the second value is how much will be incremented to the last value, in this example the value starts in 1 and in the next insert will be 3, 5, 7, 9... The default of IDENTITY is (1,1).

Insert

As the name says, you insert new values in database. Let's see the query:

Insert

Let's take a look in the syntax:

INSERT INTO table_name (FIELDS) values (VALUES)

INSERT INTO tells the database that you are doing an insert, after you tell the fields followed by the word VALUES and after you tell the values, in the same order of the fields. The fields are not required, but it is a good practice, if you will insert values for all the fields you just need to tell the values.

Select

Select is the command to recover data from database.I will do four selects. First of all, I am going to do a select for all fields without filter.

Select 1

Now as second example, I am going to select only the name of all clients.

Select only name

Third example, select everything from client that has ID = 1

Select with ID = 1

Fourth example, select only name and address of the client with ID = 1

Select name and Address with ID = 1

SELECT tells the database that you are going to do a select, followed by the fields name separated by comma, or * for all fields, after as you can see is the word FROM followed by the table_name and the clause WHERE with the conditions of the select. You can give an "alias" to the table and fields, as a nickname, I will show it for you in my next post. The clause WHERE has some operators:

Basic operators

These are the basic operators, but there are others as LIKE that checks if a field contains a string.

WHERE Name LIKE ‘RAPHAEL%’:  Returns all clients that the name starts with Raphael.

WHERE Name LIKE ‘%FERNANDES’:  Returns all the clients that the name ends with Fernandes.


WHERE Name LIKE ‘%LIMA%’: Returns all clients that the name contains Lima.

Also we have the IN operator that checks if a value is any of the values in the clause.

WHERE ClientID IN (1,3,5): Returns the clients with ClientID equals 1,3 and 5.

The operator BETWEEN checks if a value is between 2 values.

WHERE ClientID BETWEEN 1 AND 3: Returns the clients with ClientID equals 1,2 and 3.

The WHERE clause may have more than one condition, using the operators AND and OR.

AND: All the conditions should be true.

WHERE ClientID = 1 AND Name LIKE 'Raphael%': It returns all the clients with the ID =  1 and Name starts with Raphael.

OR: Only one condition should be true.

WHERE ClientID = 1 OR Name LIKE 'Raphael%': It returns all the clients with the ID =  1 OR the clients that the Name starts with Raphael.

Update

Update as the name says, updates one or more records, let's update the address of the client with ID = 1:

Updating a record

Select in the row updated

The syntax of update: UPDATE table_name SET field = value WHERE conditions

Delete

Delete, as the name says, is the command to delete a row.

Deleting a row

Select *  from Client

The syntax: DELETE FROM table_name WHERE conditions

So, this is the end of the first part about database.

I hope you liked.

Minification and Bundle

Hello everybody! Today I am going to talk about Minification and Bundle.

Minification

When you do a request to server, a lot of bytes are transferred between client and server, but there is a way to reduce this transfers optimizing the requests.

This is possible with the use of Minification, Minification delete all useless spaces of the files to reduce file's size.

Normal Javascript File

Minified Javascrpt File

There are a lot of sites that does the minification in your files to get a better performance. Just take a look in internet to find these sites.

Bundle

Did you know, generally the Browsers can just download 6 Javascript or CSS files by time? But, did you know is possible put the files together in a single "package" for the browser download all of them in a single request? You can not mix Javascript files and CSS files in a single "package". 

What makes it possible is the use of Bundle.

It is very simple to configure a Bundle, you just need to open the folder App_Start of your project and then open the class BundleConfig.cs. Look the use of the namespace System.Web.Optimization, needed to optimize the files using Bundle.

Bundle Example

As you can see, the class has a single static method called RegisterBundles with a var of the BundleCollection type called bundles. In this var you will add all your file's "packages".When you call the Add method you need to use a parameter of the Bundle type, you can use ScriptBundle for Javascript files or StyleBundle for CSS files. After you do an instance of a new Bundle you need to use the virtual path of your bundle, like an ID, and after, you need to call the Include method, to include the files that will be in this "package".

We ca'n also look the existence of jquery-{version}.js and jquery.validade*. Jquery-{version} means all files starting with jquery- and having a version in name will be in the Bundle, like jquery-1.8.2.js. The jquery.validate means all files starting with jquery.validate will be in the Bundle.

Bundles are registered in Global.asax.cs file, this class executes every time your application is started.

Global.asax.cs

Look at the end of file, there is a line BundleTable.EnableOptimizations = true;

This line configure the load of the bundles, and tell the application to load minified files. This option is by default equals false, if you want to use this optimization, you need to set the property to true.

The use of bundles in Views, is possible with the helpers @Styles.Render for CSS files and @Scripts.Render() for Javascript files, both needs a parameter with the virtual path of the Bundle.

Bundles in View

Let's take a look in the Bundles loaded by the Browser:

Bundles in Browser

The bundles are loaded in a single request.