by 
Today I was looking for a specific paper out of the huge bulk of my notes and I have found notes about SQL paper which I wrote them when I was in level one. Therefore, I organized the notes and prepare a short review of basic SQL queries here.
I hope that it will be useful for the novice users.
The first query is creating database which is like,
CREATE DATABASE [database_name];
For dropping database drop command is used like,
DROP DATABASE Mydatabase;
Creating table could be like the following,
CREATE TABLE [table-name] (field1 datatype [Notnull], field2 datatpye, ...);
For example,
CREATE TABLE Bills (Name char(30), Amount number, Account_ID number);
Not null example,
CREATE TABLE Bills (Name char(30) NotNull, Amount number, Amount_ID NotNull);
Now if you want to remove the table you can use drop like,
DROP TABLE Bills;
Now imagine that you create your table but you realized that you have made mistake while you were creating it.
You can correct/alter table easily like below.
ALTER TABLE [table_name] ADD|MODIFY [column_name] [datatype];
For example,
ALTER TABLE Bills MODIFY Name char(40);
ALTER TABLE Bills ADD Comments char(50);
On the other hand a short list of the most used data type listed below with their description.
Char: Alpha numeric data with a length between 1 and 255 characters.Date: Included as a part of the date are century, year, month, day, hour, minute, and second.Long: Variable length alpha numeric strings up to 2 gigabytes.Number: Numeric, positive, negative fixed or floating point data.Raw: Binary data up to 255 bytes.Raw_ID: Hexa decimal string representing the unique address of a raw in a table.Varchar2: Alphanumeric data that is variable length; this field must be between 1 and 2,000 characters long.
For inserting data into your tables, you can use Insert command like following example,
INSERT INTO [table-name] VALUES (...,...,...);
INSERT INTO Bills VALUES ('Simon',2500,124675);
Example of insert with specifying the exact columns.
INSERT INTO [table-name] (col1,col2,...) VALUES (value1,value2,...);
INSERT INTO Room(Name,Status) VALUES ('Room5',2);
Copying data from one table into another table with using insert is like below.
INSERT INTO tmp_tbl1 SELECT * FROM MyTable;
You also able to update data of certain row of the table with using update command like below.
UPDATE [table_name] SET [colum1=value1],.... WHERE [condition];
Deleting a specific row is like,
DELETE FROM [table_name] WHERE [condition];
DELETE FROM room WHERE 1=1;
The above example delete all data from room table.
Select command could be like below,
SELECT [field(s)] FROM [table-name] WHERE [condition];
For Instance,
SELECT * FROM Bills WHERE 1 = 1;
Bear in mind that in the above example where condition is redundant and I added there to show the complete structure. This means that if you remove the condition in this example still the query is correct.
Another example,
SELECT Amount, Account_ID FROM Bills WHERE Name = 'Jacob';
By default string selection is case sensitive if you want to select without case sensitive you can use LIKE instead of =such as below,
SELECT ID FROM tbl_room WHERE roomnumber LIKE 'Nelson' AND roomstatus = 1;
Hint: You also can apply more than one condition with using aggregation operations such as and, or, etc.
The following example listed the information of employee who are NOT located in London.
SELECT FirstName, LastName, City FROM Employees WHERE City <> 'London';
Another example of select,
SELECT FirstName, LastName FROM Employees WHERE (FirstName LIKE 'J%') AND (FirstName LIKE 'N%');
The above statement returns all first name which is started with J and N.
In addition easily you can reverse condition with adding NOT such as following example,
SELECT FirstName, LastName FROM Employees WHERE (FirstName NOT LIKE 'J%') AND (FirstName NOT LIKE 'N%');
Furthermore, you are able to sort the output based on a certain column. Look at the example,
SELECT FirstName, LastName, City FROM Employees ORDER BY LastName;
In the example the output will be sorted based on LastName of the employees. The sort order by default is ascending, means that from lowest to highest value. You can change to descending like below,
SELECT FirstName, LastName, City FROM Employees ORDER BY LastName DESC;
You can sort it based on more than one column for instance based on FirstName and LastName.
SELECT FirstName, LastName, City FROM Employees ORDER BY FirstName, LastName DESC;
You also can join different tables to each other and get the output of them easily like below.
SELECT [fields of different tables] FROM table1 aname, table2 aname, ... WHERE [conditions];
For instance,
SELECT r.ID, r.name, a.State FROM room r, avail a WHERE r.status=a.id;
Hint: SQL is not case sensitive so there is no difference between upper case or lower case except in selecting based on string data in database. I wrote keywords in capital letters for better understanding and used lower case for the variables, table name, etc.
In this post I did not mention about Primary key and Foreign key because I did not want to make a chaos in the mind of the reader with the concepts of the keys.
I will write about the keys and also more about joins in another post.
