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, …);
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];
ALTER TABLE Bills MODIFY Name char(40);
ALTER TABKE 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];
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.
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 deafult 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];
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.
send your idea and information to firstname.lastname@example.org