SQL Basics

SQL COMMAND

SQL stands for Structured Query Language. SQL is a standard computer language for accessing and manipulating databases. The entire application database that circulating in the market, both free and licensed, to adopt language to manage SQL database.

Some of the SQL command or the SQL command to know is:

• CREATE DATABASE, to create a database.
• DROP DATABASE to remove a database.
• CREATE TABLE, to create a table.
• Alter TABLE, to modify a table.
• DROP TABLE to remove a table.
• SELECT , to display data from the database.
• UPDATE, to modify the data from the database.
• INSERT INTO, to add data in the database.
• DELETE, to delete data from the database.

While the SQL command to have advanced quite a lot. Command-level information is useful to process data in a manner that is more complicated and complex.

CREATE DATABASE COMMAND

Before working with the database, you must first create a database that will be the object work for you.

Syntax:

CREATE DATABASE database_name

After creating a database with the command as above, does not automatically your database contains the table-table. Your database in a state of empty. You must create a table-like tables that will be described in the next section.

DROP DATABASE COMMAND

If you want to delete a database, use the following command:

DROP DATABASE database_name

If you use the above command, all database and table that is in it will be erased.

Note: for Access, you may not use the DROP DATABASE command. If you use this command, your application will be garbled. If you want to delete the database from Access, you can delete the mdb file that was created by Access.

CREATE TABLE COMMAND

Then you can create a table with the following command:

CREATE TABLE table_name ( column_name1 data_type, column_name2 data_type, ....... )

For example:

CREATE TABLE Person ( LastName varchar, FirstName varchar, Address varchar, Age int )

You can also limit the size of each field utnuk produce a database file that is smaller:

CREATE TABLE Person ( LastName varchar(30), FirstName varchar, Address varchar, Age int(3) )

For the data type, you can see again the first part of the material in this lecture.

ALTER TABLE COMMAND

Alter TABLE command can you use if you want to modify the structure of the table that you create. That is, if you want to add a new field or table that you have created, or you want to delete one or more fields that you have created, you can use this command.

The syntax is:

To add a new field: ALTER TABLE table_name ADD column_name datatype (size) To remove an existing field: ALTER TABLE table_name DROP COLUMN column_name

DROP TABLE COMMAND

To delete a table, such as the following command:

DROP TABLE table_name

Note: Please note that if you delete a table, then all existing file in it will be erased.

SELECT COMMAND

To display data from a database, you can use the SELECT command. Basic form of this command is:

SELECT column_name_1, column_name_2, dst FROM table_name

If you want to display all data in a table without selecting one of the field, then you can use the following command:

SELECT * FROM table_name

SELECT DISTINCT STATEMENT

SELECT DISTINCT Statement is used to display data from one or more of the field (column) from a table. However, the difference between SELECT with SELECT DISTINCT is located in the results.

Note the sample table orders the following:

Company	OrderNumber
Sega	3412
W3Schools	2312
Trio	4678
W3Schools	6798

If you use the SELECT command like this:

SELECT Company FROM Orders

then the result will be like this:

Company

Sega

W3Schools

Trio

W3Schools

But if you use the SELECT DISTINCT like this:

SELECT DISTINCT Company FROM Orders

The results will be like this:

Company

Sega

W3Schools

Trio

Note that the SELECT command, the data "W3Schools" 2 times will be shown that there is appropriate data, whereas with the command SELECT DISTINCT, then the data "W3Schools" is only shown 1 times. So the command SELECT DISTINCT will not produce data that is identical or twin.

WHERE CLAUSE

You can also add a condition in the SELECT command to display the data is more specific.

Basic syntax of the command is WHERE:

SELECT column FROM table WHERE column operator value

where the operator is:

Operator	Description
=	Equal
<> or !=	Not equal
>	Greater than
<	Less than
>=	Greater than or equal
<=	Less than or equal
BETWEEN	Between an inclusive range
LIKE	Search for a pattern

Note the PERSONS table below:

LastName	FirstName	Address	City	Year
Hansen	Ola	Timoteivn 10	Sandnes	1951
Svendson	Tove	Borgvn 23	Sandnes	1978
Svendson	Stale	Kaivn 18	Sandnes	1980
Pettersen	Kari	Storgt 20	Stavanger	1960

If you use the SELECT command with a WHERE attributes such as the following:

SELECT * FROM Persons WHERE City='Sandnes'

The results will be displayed like this:

LastName	FirstName	Address	City	Year
Hansen	Ola	Timoteivn 10	Sandnes	1951
Svendson	Tove	Borgvn 23	Sandnes	1978
Svendson	Stale	Kaivn 18	Sandnes	1980

Note that the displayed data is coming from the city "Sandnes" in accordance with the conditions provided in the command above. Pay attention to any posts in quotes ( ") is used, the following is true:

For a text value: The correct syntax: SELECT * FROM Persons WHERE FirstName='Tove' The wrong Syntax: SELECT * FROM Persons WHERE FirstName=Tove Value to a number: The correct syntax: SELECT * FROM Persons WHERE Year>1965 The wrong Syntax: SELECT * FROM Persons WHERE Year>'1965'

LIKE CLAUSE

LIKE statement you can add to the SELECT command to a condition that resembles something.

The syntax is:

SELECT column FROM table_name WHERE column LIKE conditions

In this command, use a "%" to indicate something that is not yet known.

Note the following example:

SELECT * FROM Persons WHERE FirstName LIKE 'O%'

The above command will display all the data that-firstname berawalan it with the letter "O".

SELECT * FROM Persons WHERE FirstName LIKE '%a'

While the above example will generate all the data that firstname is ending with the letter "a".

SELECT * FROM Persons WHERE FirstName LIKE '%la%'

While the above this will generate all the data that it has a firstname letter "la" in the middle.

ORDER BY STATEMENT

ORDER BY statement is used to sort the data view based on one or more fields in order from beginning to end (Ascending) or from the beginning to the end (descending).

Syntax is as follows:

SELECT column_1, column_2, dst FROM table_name ORDER BY column_1, column_2, dst ASC/DESC

Or you can also combine them with the other clause, such as WHERE and LIKE as follows:

SELECT column_1, column_2, dst FROM table WHERE column_2 LIKE conditions ORDER BY column_1 ASC

Note the sample table orders the following:

Company	OrderNumber
Sega	3412
ABC Shop	5678
W3Schools	2312
W3Schools	6798

Suppose you have a command like this:

SELECT Company, OrderNumber FROM Orders ORDER BY Company

The results will be like this:

Company	OrderNumber
ABC Shop	5678
Sega	3412
W3Schools	6798
W3Schools	2312

Note, that if you do not give a sign asc or desc, the results will be displayed asc (series from the beginning to end, or from small to large).

You can also combine several conditions at once. Note the following example:

SELECT Company, OrderNumber FROM Orders ORDER BY Company DESC, OrderNumber ASC

The result will be like this:

Company	OrderNumber
W3Schools	2312
W3Schools	6798
Sega	3412
ABC Shop	5678

Note that the results indicate that the company will be in the field in descending order, and in the field akan ordernumber in Ascending order.

AND dan OR STATEMENT

Statement AND or OR can be used to combine 2 different conditions to run in the WHERE command.

Note the sample table PERSONS following:

LastName	FirstName	Address	City
Hansen	Ola	Timoteivn 10	Sandnes
Svendson	Tove	Borgvn 23	Sandnes
Svendson	Stephen	Kaivn 18	Sandnes

Suppose you have a command like this:

SELECT * FROM Persons WHERE FirstName='Tove' AND LastName='Svendson'

The results will be like this:

LastName	FirstName	Address	City
Svendson	Tove	Borgvn 23	Sandnes

Or you have a command like this example:

SELECT * FROM Persons WHERE firstname='Tove' OR lastname='Svendson'

But the results will be displayed is like this:

LastName	FirstName	Address	City
Svendson	Tove	Borgvn 23	Sandnes
Svendson	Stephen	Kaivn 18	Sandnes

You can also combine AND and OR statements at the same time, for example:

SELECT * FROM Persons WHERE (FirstName='Tove' OR FirstName='Stephen') AND LastName='Svendson'

The results will be like this:

LastName	FirstName	Address	City
Svendson	Tove	Borgvn 23	Sandnes
Svendson	Stephen	Kaivn 18	Sandnes

UPDATE COMMAND

UPDATE command is used to modify data (records) that are in a table.

The syntax is:

UPDATE table_name SET column_name = new_value WHERE column_name = old_value

Note the sample table PERSONS following:

LastName	FirstName	Address	City
Nilsen	Fred	Kirkegt 56	Stavanger
Rasmussen	Ani	Storgt 67

Suppose you have a command like this:

UPDATE Person SET FirstName = 'Nina' WHERE LastName = 'Rasmussen'

So the result is like this:

LastName	FirstName	Address	City
Nilsen	Fred	Kirkegt 56	Stavanger
Rasmussen	Nina	Storgt 67

Note that the previous record was "Ani" was changed to "Nina" after the command is executed.

INSERT INTO COMMAND

INSERT INTO command is used to add new data on a table, not to modify the data in one field only.

The syntax is:

INSERT INTO table_name (column1, column2, dst) VALUES (column1_value, column2_value, dst)

Note the PERSONS table below:

LastName	FirstName	Address	City
Pettersen	Kari	Storgt 20	Stavanger

To add the new data, you can use the INSERT INTO. Following example:

INSERT INTO Persons (LastName, FirstName, Address, City) VALUES ('Hetland', 'Camilla', 'Hagabakka 24', 'Sandnes')

Or the command like this:

INSERT INTO Persons VALUES ('Hetland', 'Camilla', 'Hagabakka 24', 'Sandnes')

Note that because the contents of a text field, then the value that is given the quotation marks

( '') which signifies or text string. The result is as follows:

LastName	FirstName	Address	City
Pettersen	Kari	Storgt 20	Stavanger
Hetland	Camilla	Hagabakka 24	Sandnes

You can also add data in specific fields, but still create a new line (not to replace the old data).

Note the following table PERSONS:

LastName	FirstName	Address	City
Pettersen	Kari	Storgt 20	Stavanger
Hetland	Camilla	Hagabakka 24	Sandnes

Suppose you have a command like this:

INSERT INTO Persons (LastName, Address) VALUES ('Rasmussen', 'Storgt 67')

So the result is:

LastName	FirstName	Address	City
Pettersen	Kari	Storgt 20	Stavanger
Hetland	Camilla	Hagabakka 24	Sandnes
Rasmussen		Storgt 67

Note that the new data does not replace the position of the old data, but occupying a new line with 2 blank fields contents.

DELETE COMMAND

DELETE command is used to remove data from a table. Remove this function will be run under the given conditions. Therefore you need to remember to always provide a condition so that the erased data is completely the data you want to delete.

This can not only remove the content of a particular field only. The entire field in a series of rows will be deleted if it met specified conditions.

The syntax is:

DELETE FROM table_name WHERE column_name = value

Note the sample table PERSONS following:

LastName	FirstName	Address	City
Nilsen	Fred	Kirkegt 56	Stavanger
Rasmussen	Nina	Stien 12	Stavanger

Suppose you have a command like this:

DELETE FROM Person WHERE LastName = 'Rasmussen'

The results will be like this:

LastName	FirstName	Address	City
Nilsen	Fred	Kirkegt 56	Stavanger

Note that all data belonging Rasmussen will be deleted.

If you want to delete all the rows that have, you can use the following:

DELETE FROM table_name atau DELETE * FROM table_name

Then all data in the table that will be lost altogether.