SQL is the abbreviation of Structured Query Language. It is the language of the database. SQL is used to interact with the database. Query means request sent to the DBMS to retrieve some information from the database.
we can perform following operations in
database:
Create New Table
Insertion of data into tables
Retrieval of data from tables
Updating of existing data into tables
Delete the data form database.
Delete The Tables
There are three type of SQL commands.
These command are used creation, deletion
and modification of tables in database.
For example CREATE TABLE, ALTER TABLE, DROP TABLE, RENAME etc.
These commands handles different transactions on the table. For example INSERT, UPDATE, DELETE commands.
These commands deals only with the data dictionary rather than actual data stored by the user. The changes include creation of new users, granting privileges to them, creation of roles, Tuning databases and performing backup and recovery, etc.
Select command is used to retrieve the data form SQL database and return a set of record in shape of table. Select command is used with FROM keyword. We can also use WHERE, HAVING, DISTINCT ORDER BY clause which are discuses in detail in next tutorials. The syntax of select command is :
SELCT column1, column2..... FROM table_name;
There are three ways use of select command:
Retrieve one column
Retrieve multiple columns
Retrieve all columns
For example the following table student information store the record:
|
name |
Marks |
Result |
Grade |
|
Mr. Muneer |
500 |
Pass |
A |
|
Mr. Naweed |
300 |
Pass |
B |
|
Mr.tauqeer |
450 |
Pass |
A |
|
Mr. Jalil |
200 |
Fail |
Fail |
Select One Column
To select a single column, we specify the column name between SELECT and FROM as follows:
SELECT name FROM Store_Information;
Result:
|
name |
|
Mr. Muneer |
|
Mr. Naweed |
|
Mr.tauqeer |
|
Mr. Jalil |
Select Multiple Columns
We can use the SELECT statement to retrieve more than one column. To select name and marks columns from studentinfo table, we use the following SQL command:
SELECT nam, marks FROM studentinfo;
Result:
|
name |
Marks |
|
Mr. Muneer |
500 |
|
Mr. Naweed |
300 |
|
Mr.tauqeer |
450 |
|
Mr. Jalil |
200 |
Select All Columns
when you want to access all the column form any given table then you can use " * "' instead of column name.
SELECT * FROM Store_Information;
Result:
|
name |
Marks |
Result |
Grade |
|
Mr. Muneer |
500 |
Pass |
A |
|
Mr. Naweed |
300 |
Pass |
B |
|
Mr.tauqeer |
450 |
Pass |
A |
|
Mr. Jalil |
200 |
Fail |
Fail |
WHERE Clause
WHERE clause select the record conditionally from SQL database. WHERE keyword use with different statement in SQL for example WHERE with SELECT Statement, Delete from statement, Updatestatement etc.
or example if we have a table "student" with following record:
|
Name |
Marks |
Result |
Grade |
|
Mr. Muneer |
500 |
Pass |
A |
|
Mr. Naweed |
300 |
Pass |
B |
|
Mr.tauqeer |
450 |
Pass |
A |
|
Mr. Jalil |
200 |
Fail |
Fail |
The above table contain the student result. if we want to display those record whose result is Pass then we use the following statement.
SELECT * FROM student WHERE Result='pass';
where
student is table name and result is column name and pass are the parameter
The above statement display the following records:
|
Name |
Marks |
Result |
Grade |
|
Mr. Muneer |
500 |
Pass |
A |
|
Mr. Naweed |
300 |
Pass |
B |
|
Mr.tauqeer |
450 |
Pass |
A |
we can also use Like operator , > operator , != operator , between operator and in operator and < operator with WHERE clause.
We explain these operator in next tutorial:
IN Operator
IN operator in SQL return the entire record exact match with given values. For example if we want to match more then one value in our condition for example from table below if we want to select those record who's city is Karachi or Lahore. So we have two way to perform this search:
|
client_ID |
city |
order_amount |
|
A001 |
Karachi |
50000 |
|
A002 |
Lahore |
20000 |
|
B001 |
Karachi |
30000 |
|
B002 |
Islamabad |
90000 |
One way is we use simple OR operator using compound statement
SELECT * FROM client_info WHERE city='Karachi' OR city='Lahore' ;
Second way is using IN operator
SELECT * FROM client_info WHERE city IN ('Karachi' , 'Lahore') ;
IN operator match the values from given list.
Between Operator
When we use between operator then it compare all the records which match with our given range after BETWEEN operator.
For example if want to select those record form table below where order-amount is from 20000 to 50000
then we use following statement:
SELECT * FROM client_info WHERE order_amount between 20000 and 50000 ;
Actual Table
|
client_ID |
city |
order_amount |
|
A001 |
Karachi |
50000 |
|
A002 |
Lahore |
20000 |
|
B001 |
Karachi |
30000 |
|
B002 |
Islamabad |
10000 |
|
B004 |
Karachi |
15000 |
The above statement display the following record.
Selected record with BETWEEN Operator
|
client_ID |
city |
order_amount |
|
A001 |
Karachi |
50000 |
|
A002 |
Lahore |
20000 |
|
B001 |
Karachi |
30000 |
Wildcard
In SQl wildcards are used with LIKE operator which are substitute for any other character or characters . In SQL we can use two wildcards with LIKE operator which are percentage symbol ( %, ) and underscore symbol ( _ ) . Where percentage symbol ( % ) is substitutes for zero or more characters and Underscore symbol ( _ ) are used as substitutes of any single character.
If we want to search the record with name of the person but we not have exact name , we only know the part of name like that the second name is ' Bloch ' then we use LIKE operator with % wildcard before the name:
For example from table given below:
Actual Table
|
client_name |
city |
order_amount |
|
Syed Tauqeer |
Karachi |
50000 |
|
Muhammad Zia Ahmed |
Lahore |
20000 |
|
Noman Bloch |
Karachi |
30000 |
|
Imran Hadir |
Islamabad |
10000 |
|
Muneer Bloch |
Karachi |
15000 |
|
Muhammad Zia Khan |
Islamabad |
30000 |
SELECT * FROM client_info WHERE client_name like '%Bloch'
This statement show that second part of name or last part of name is Bloch and before the Bloch we did not know
The above statement display the following record.
Selected record with BETWEEN Operator
|
client_name |
city |
order_amount |
|
Noman Bloch |
Karachi |
30000 |
|
Muneer Bloch |
Karachi |
15000 |
we can also use :
SELECT * FROM client_info WHERE client_name like 'Syed%'
This statement show that First part of name is Syed and after the Syed we did not know. in simple word we asking to SQl that retune those record whose client-name start form Syed. So its return all those record which start form Syed. In our table only one record will be selected
|
client_name |
city |
order_amount |
|
Syed Tauqeer |
Karachi |
50000 |
another example is :
SELECT * FROM client_info WHERE client_name like '%Zia%'
So we are asking to search those record where client_name have this text Zia. Before or after any thing is possible. So in the result following two records are display:
|
client_name |
city |
order_amount |
|
Muhammad Zia Ahmed |
Lahore |
20000 |
|
Muhammad Zia Khan |
Islamabad |
30000 |
we can also use the underscore ( _ ) wildcards for example:
ELECT * FROM client_info WHERE city like '_ahore'
So we are asking to search those record where city name have _ahore. only one character at the place of Underscore is missing. So in the result following one record is display :
|
client_name |
city |
order_amount |
|
Muhammad Zia Ahmed |
Lahore |
20000 |
Like Operator
LIKE operator is pattern matching In SQL. LIKE means Not Exactly just match some pattern for example when we use IN or BETWEEN or in Equal To (=) relational operator, then the Complete value compare if its match the whole value which is given in Condition then Its return other wise not. But in like operator we just provide a part of text and ask to match this part of text with whole text.
We use percentage % and underscore _ wildcards with like operator. Where % is substitutes for zero or more characters and _ are used as substitutes of any single character.
For example from table given below:
Actual Table
|
client_name |
city |
order_amount |
|
Syed Tauqeer |
Karachi |
50000 |
|
Muhammad Zia Ahmed |
Lahore |
20000 |
|
Noman Bloch |
Karachi |
30000 |
|
Imran Hadir |
Islamabad |
10000 |
|
Muneer Bloch |
Karachi |
15000 |
|
Muhammad Zia Khan |
Islamabad |
30000 |
SELECT * FROM client_info WHERE client_name like '%Bloch'
This statement show that second part of name or last part of name is Bloch and before the Bloch we did not know
The above statement display the following record.
Selected record with BETWEEN Operator
|
client_name |
city |
order_amount |
|
Noman Bloch |
Karachi |
30000 |
|
Muneer Bloch |
Karachi |
15000 |
Order By Clause
So far, we have seen how to get data out of a table using SELECT and WHERE commands. Often, however, we need to list the output in a particular order. This could be in ascending order, in descending order, or could be based on either numerical value or text value. In such cases, we can use the ORDER BY keyword to achieve our goal.
The syntax for an ORDER BY statement is as follows:
SELECT "column_name"
FROM "table_name"
[WHERE "condition"]
ORDER BY "column_name" [ASC, DESC];
The [ ] means that the WHERE statement is optional. However, if a WHERE clause exists, it comes before the ORDER BY clause. ASC means that the results will be shown in ascending order, and DESC means that the results will be shown in descending order. If neither is specified, the default is ASC.
It is possible to order by more than one column. In this case, the ORDER BY clause above becomes
ORDER BY "column_name1" [ASC, DESC], "column_name2" [ASC, DESC]
In SQL INSERT INTO Command add the new record (row) in a table. You can insert the data with the help of INSERT INTO command in two ways.
Syntax is :
INSERT INTO table-name (Column1 , Cooumn2........)
Values(value1, value2...........) ;
if you are going to insert the data in all field then column name are optional.
For example:
if you have table Name "student " with the following column:
Rno (integre)
Name (VARCHAR (20))
Address ( (VARCHAR (20))
INSERT INTO Student
VALUES(102, 'Saleem', 'DIKHAN')
You can also insert the data in to a table from an other table. In other word copy the data from one table to an other table. For this purpose SQL provide a command INSERT INTO SELECT statement. You can copy all column information and also selected column information. You can also use the WHERE Clause to copy the specific records.
Syntax is :
INSERT INTO new-table-name (Column1 , Cooumn2........)
SELECT (Column1 , Cooumn2........)
FROM source-table-name ;
You can also use where clause for insert some specific record.
INSERT INTO TABLE_B (Name , Marks)
SELECT (Name, Marks)
FROM TABLE_A ;
if you are going to insert all the filed then filed name are optional.
UPDATE command is used to modify the the table data (Records) in SQL. UPDATE command used withSET Key word. For example if you have a table with some record and you want to make some changes in that records, then you can use UPDATE command with SET keyword. With UPDATE command you can also use the WHERE clause for make changes on some specific recode.
Syntax is :
UPDATE Table-Name
SET Coulm1=vlaue1 ,column2=value2......
WHERE Condition ;
For example:
UPDATE Table-A
SET City='Lahore' ;
DELETE command is used to remove the record form specified table. You can delete all record at a time and also Remove a specific record with the help of WHERE clause. The FROM keyword is used with delete command.
Syntax is :
DELETE FROM Table-Name
WHERE column-name=Condition ;
For example :
DELETE FROM Table-A ;
You can also use
DELETE * FROM Table-A ;
But if you want to delete some specific record then use DELETE FROM statement with WHERE clause.
For example:
DELETE FROM Table-A
WHERE City = 'Karachi' ;
This command is used to delete Table, constraint,
views etc from the database.
For Example Drop Table , Delete The Table From Database.
DROP TABLE COMMAND
This command is used to delete Table from the
relational database.
SYNTAX
DROP TABLE
Table name
EXAMPLE
To delete the table T1.
DROP TABLE T1TRUNCATE TABLE
This command is used for Changing the structure of table, Views, sequence etc in database.
ALTER TABLE COMMAND
This command is used to change the structure of the existing table for example changing the column width, data type.
The following are the restriction for changing the
structure of the
• The Table should be empty
• The name of the column can not be changed
• The name of the Table can not be changed
We can perform two types of operations on the
table to change the structure
1. To change the data type and width of the Column of the Table we use ALTER
TABLE command with MODIFY clause.
2. To add the new Columns in the Table we use ALTER TABLE Command with ADD
clause.
<h2 class="myh1">
SYNTAX
ALTER TABLE Table name
MODIFY (Column1, Column2, Column2 …..)
Where
Table name is the name of the Table whose
structure is to be changed
Column is the name of the Column whose structure is to be changed. Column name
always written along with its complete attribute. such that data type, size,
Null or Not Null etc.
EXAMPLE
If we want to change the data type of the Column ACC of Table FEES to CHAR and
size 4.
ALTER TABLE FEES
MODIFY (ACC CHAR(4) )
Add the new Columns in the Table
We can also add new columns in the existing table; these columns will be inserted to the Right side of the Table
Syntax
ALTER TABLE Table Name
ADD (Column1, Column 2, ……)
EXAMPLE
If we want to add new Column Ph in Table Student of data type Character and size
20.
ALTER TABLE FEES
ADD (Ph char(20))
The RENAME statement renames an existing table or
index.
Syntax:
RENAME TABLE old Table Name TO New
Table Name
For example:
RENAME TABLE Student TO exam
The COMMIT statement terminates a unit of work and
commits the database changes that were made by that unit of work.
Syntax:
COMMIT WORK
Example:
COMMIT WORK
ROLLBACK statement
The ROLLBACK statement is used to back out of the database changes that were made within a unit of work or a save point.
Syntax:
ROLLBACK WORK
Example:
ROLLBACK
WORK
The SQL functions are used to manipulate the
data items and return a result. The functions follow the format:
Function_Name (Argument1, Argument2, …………….)
Where an argument is user-supplied variable or
constant. The structure of function accepts zero or more then one arguments.
Following are the some most important function, which are used in SQL.
SQL aggregate functions return a single value,
calculated from values in a column.
Useful aggregate functions:
1) AVG() - Returns the average value
2) COUNT() - Returns the number of rows
3) FIRST() - Returns the first value
4) LAST() - Returns the last value
5) MAX() - Returns the largest value
6) MIN() - Returns the smallest value
7) SUM() - Returns the sum
SQL scalar functions return a single value,
based on the input value.
1) Useful scalar functions:
2) UCASE() - Converts a field to upper case
3) LCASE() - Converts a field to lower case
4) MID() - Extract characters from a text field
5) LEN() - Returns the length of a text field
6) ROUND() - Rounds a numeric field to the number of decimals specified
7) NOW() - Returns the current system date and time
8) FORMAT() - Formats how a field is to be displayed