Table of Contents
First of all let’s have a look at the full form of SQL, SQL refers to Sequential Query Language which is used to access databases, by using SQL statements we can access the data from MySQL, SQL Server, Microsoft Access and Oracle etc. SQL takes the significant rule in dynamic website development which interacts with their database often.
Below is the list of statement, clauses and conditions which can be used together to access the data from database.
“select” statement:
In SQL select, statement is used to get the information from the table. It has 6 clauses and they are-
- Select – Which Column of data to get
- From – From which Table
- Where – Which rows of data to get
- Group by – Grouping
- Having – Having
- Order By – Sorts the column
Example 1: SELECT * FROM adressbook WHERE name=’Krishna’
By using the above statement we can retrieve the entire row (because we are using ‘*’) which contains the name ‘Krishna’ from the adressbook table. Here we are only using SELECT, FROM and WHERE clauses.
Example 2: SELECT * FROM employeeinfo WHERE salary>5000 ORDER BY firstname
Here we retrieve the entire rows (as we are using ‘*’) from employeeinfo table whose salary is more than 5000 and we get the result in sorted format with employees first name as we are using ORDER BY firstname clause.
Use of “AS” clause:
AS clause is used to change the column header of the result table and it is also used to create new column.
Example1: SELECT firstname AS Name FROM addressbook
Here the firstname column is retrieved from the addressbook table and the column header is changed to “Name” as we used “AS” clause.
Example2: SELECT “Dr.” as Degree, firstname FROM addressbook WHERE profession=”Doctor”
Here the “Dr.” prefix is added to every row of first column and the column name is named as “Degree” (because we have used “AS” clause). Further the first name of addressbook is retrieved whose profession is “Doctor”.
SELECT DISTINCT:
SELECT DISTINCT clause is used to get the unique data from the specified column.
Example: SELECT DISTINCT customer_id FROM addressbook
Here the duplicate customer ids are eliminated.
WHERE Clause:
Where clause is used to choose which rows of data is to be retrieved. Here we can mention the conditions to retrieve the rows such as SELECT * FROM addressbook WHERE name=’Krishna’.
Notes:
- “%” wildcard should be used while using LIKE condition.
- “#” wildcard should be used for Date data type.
LIKE Condition:
Like condition is used to find the patterns in the data. Patterns are specified by using wildcards such as “*”,”?” and “#”.
Example: SELECT * FROM addressbook WHERE name like ‘*Kr*’
ORDER BY Clause:
Order By clause is used to order the rows in ascending or descending order. By default it is set to ascending order. We have to use “desc” keyword along with “Order By” clause to make it descending.
Example1: SELECT firstname, lastname FROM addressbook ORDER BY firstname
Example2: SELECT firstname, lastname FROM addressbook ORDER BY lastname DESC, firstname
INSERT Clause:
INSERT clause is the second important clause in SQL. It is used to insert the row in the table. It consists of 2 clauses, those are INSERT INTO and VALUES or INSERT INTO and SELECT.
Example1: INSERT INTO Customer (Id, FirstName, LastName)
VALUES (100, ‘Ram’, ‘Krishna’)
Example2: INSERT INTO addressbook (firstname, lastname, phone)
SELECT ‘Ram’,’ ‘Gopal’, 0123456789
UPDATE Clause:
UPDATE clause is used to update the row in the table. It consists of 3 clauses and those are UPDATE, SET and WHERE.
Example:
UPDATE addressbook
SET lastname=’Govardhan’
WHERE phone=0123456789
Here the lastname cell content is changed to ‘Govardhan’ where the phone is ‘0123456789’
DELETE Clause:
DELETE clause is used to DELETE the row in the table. It consists of 2 clauses, those are DELETE and WHERE.
Example:
DELETE addressbook
WHERE phone=01234
Here the row which contains the phone number ‘01234’ will be deleted.
