sql

SQL Basics?-TrickCode

basics of SQL,SQL Basics,Structured Query Language, SQL ,Inserting Records,Deleting Records,Updating Records,Querying Records,SELECT
Share it:

Before you deal with accessing and modifying databases, you first need to know the basics of SQL (Structured Query Language). This is the language used for querying databases. SQL is simple and easy to understand. SQL is not only used for querying data, it can pretty much do anything from creating, updating, and deleting databases and tables.

Creating Database and Tables

You can create a database using the following syntax. Please do note that SQL is case insensitive. So it doesn't matter if you write the keywords in lowercase.

CREATE DATABASE <Name of Database>;

The <Name of Database> is the name that you want to give to your database. Here’s an example.

CREATE DATABASE MyDatabase;

For us to store a Database, we need to create a table. We use the following syntax.

CREATE TABLE <Name of Table>
(
   <Column1> <Datatype>,
   <Column2> <Datatype>,
   .
   .
   .
   <ColumnN> <Datatype>

);

Again <Name of Table> is the name of the table we want to create. Inside the parentheses are a list of columns and their data type. The data types that you will commonly use are listed below


Data Type
Description
Example
int
     integer data
-3, 1, 12, 57
char(n)
     fixed-length string where n is the maximum length
‘hello’, ‘goodbye’
varchar(n)
     variable length string where n is the maximum length
‘hello’, ‘goodbye’
DateTime
     stores date and time
Jan 1, 2010 3:00PM
date
     stores date only
Jan 1, 2010
time
     stores time only
3:00PM
money     
       stores monetary data
123.45


You might notice that char(n) and varchar(n) seems to be the same. char(n) is fixed and once you give a size to it, it will take up memory depending on that size. If the size you have given is 100, and the string you store only has 3 characters, then char(n) will still use memory for 100 characters. varchar(n) is different. It only takes up what is needed. Also, note that character strings are enclosed inside single quotes.


Let’s create an example of the table that can store the FirstName, LastName, and Age of employees.

CREATE TABLE Employees

(
   EmployeeID int primary key,
   FirstName varchar(100),
   LastName varchar(100),
   Age int
);

Notice that we added the primary keyword after the data type of EmployeeID. This will signify that EmployeeID will be the primary key of the Employees table. A primary key is a value that identifies a row or record. The primary key must be unique for each of the records. We used EmployeeID as the primary key and not, for example, the FirstName, because multiple employees could have the same name therefore, that will conflict the rule that primary keys need to unique. The FirstName and LastName fields have a data type of varchar(100), which means the length has a maximum of 100 characters.

You can use the following SQL Command to Delete an existing table.

DROP TABLE Employees;

That will delete the Employees table and all of its records.

Inserting Records

We use the following syntax to insert records into a table.

INSERT INTO <Table Name> VALUES (<Value1>, <Value2>, ... , <ValueN>);

The <TableName> is the name of the table where we want to insert our data into. Inside the parenthesis after VALUES, you list all the values for each of the fields of the table. The order of data is important. You must follow the order defined when you created the Table. Let’s add some records to our Employee Table.

INSERT INTO Employees VALUES (1, "John", "Smith", 21);
INSERT INTO Employees VALUES (2, "Mark", "Mayer", 23);
INSERT INTO Employees VALUES (3, "Alvin", "Minsky", 27);

This will insert the 3 employees to our Employees table. Note the order of the parameters. Our table definition defines that the first parameter is the EmployeeID, the second is FirstName, LastName and then the last is the Age. Again, the EmployeeID must be unique because that is the primary key


Deleting Records


To delete a record, we used this syntax.
DELETE FROM <Table Name> WHERE <Condition>; 

The condition tells what record you want to delete. Most of the time, you use the primary key field to delete a record. As an example, Let’s delete John from our table.

DELETE FROM Employees WHERE EmployeeID = 1; 

The above statement reads as “Delete a record from the Employees table whos EmployeeID is equal to 1. Since John has an EmployeeID of 1, he is deleted from the table.

Updating Records

If you want to change the value of a field of an existing record, you can do that using the following syntax.

UPDATE <Table Name> SET <Column> = <Value> WHERE <Condition>; 


For example, if you want to change the FirstName of Mark, use the following code.

UPDATE Employees SET FirstName = 'Marco' WHERE EmployeedID = 2; 


We set the FirstName field of the employee with EmployeeID that is equal to 2 to ‘Marco’.

Querying Records

We can use the SELECT statements to query or get data from our database. The syntax is as follows:


SELECT <Column1>, <Column2>, ... <ColumnN> FROM <TableName>; For example, if we want to query all the FirstNames of the employee, we can write this code:

SELECT FirstName FROM Employees; FirstName John Mark Alvin You can select multiple columns just like this.

SELECT FirstName, LastName FROM Employees;
FirstName                          LastName John                      
 Smith Mark                      Mayer
 Alvin                                 Minsky

You can also use the WHERE keyword to only select those that meet the condition.

SELECT FirstName, LastName FROM Employees WHERE FirstName = 'John';

FirstName                          LastName
John                                       Smith

To Select Everything from the table, you can use the * character.
SELECT * FROM Employees
EmployeeID   FirstName  LastName   Age  
1                        John            Smith          21
2                        Mark           Mayer         23
3                        Alvin           Minsky         27

You can arrange the result by using the ORDER BY keyword.


SELECT FirstName, LastName FROM Employees ORDER BY FirstName

FirstName   LastName
Alvin               Minsky
John               Smith
Mark              Mayer

If you want to arrange the records in descending order, you can use the DESC keyword.


SELECT FirstName, LastName FROM Employees ORDER BY FirstName DESC

FirstName   LastName
Mark            Mayer
John             Smith
Alvin            Minsky

SQL alone is a very big language. There are many more not discussed in this lesson. Now that you have learned the basics of SQL, we can now proceed to how we can access databases from our application.


                                                                   Share this article with your friends
Share it:

sql

Post A Comment:

0 comments: