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.