SQL stands for “Structured Query Language” and can be pronounced as
“SQL” or “sequel. SQL commands used in SQL programming for SELECT Statement,
UPDATE Statement, INSERT INTO Statement, DELETE Statement, WHERE Clause, ORDER
BY Clause, GROUP BY Clause, ORDER Clause, Joins, Views, GROUP Functions,
Indexes etc.
Here are the list
which we mostly used in our daily database programming life in SQL Server
- SQL Statements
- SQL Clauses
1.
SELECT
a.
The most commonly used statement is SELECT. Which help
to retrieve data from SQL Server Tables (Database). We need to pass specific column
name or entire (*) result with this statement. Here are the example:
i.
SELECT column_name1, column_name2 FROM Table_Name (this
is used to get only column1 and column2 records from Table)
ii.
SELECT * FROM Table_Name (this is used for get all the
records from Table)
iii. For
example : (SELECT cName, vAddress FROM EmployeeTable)
2.
INSERT
a.
The insert statement is used to add record(s) into
table. Here are the ways how we can add records.
i.
INSERT INTO
Table_Name (column1, column2) VALUES (‘ved’,’pathak’)
ii.
INSERT INTO Table_Name VALUES (‘ved’,’pathak’)
iii. For
example : (INSERT INTO EmployeeTable(FName, LName) VALUES (‘ved’,’pathak’)
3.
UPDATE
a.
This is mostly used statement in SQL, if we want to
update (modify) existing records into our table(s), we use this statement.
i.
UPDATE Table_Name SET column1 = ‘new value’ WHERE
condition
ii. For
Example: UPDATE EmployeeTable SET FName = ‘ved R’ WHERE empId = 102
iii.
The above statement we used where clause because if we don’t
then all rows will be updated. You need to take precaution while updating the records.
Use where clause to update only those records which you want.
4.
DELETE
a.
This is one of also most usable statements in SQL; this
is used if you want to remove records from your table.
i.
DELETE FROM Table_Name WHERE condition
ii. For
Example: DELETE FROM EmployeeTable WHERE empId = 102
iii.
The above statement we used where clause because if we don’t
then it will remove all rows. You need to take precaution while deleting the records.
Use where clause to delete only those records which you want.
5.
CREATE
a.
This statement is used to create table in SQL Server.
i.
CREATE Table_Name (column_Id dataType
identity(seeds,increment) NULL | NOT NULL, column_Name dataType NULL| NOT NULL)
ii. CREATE
EmployeeTable (empId identity(100,1) NOT NULL, FName varchar(100) NOT NULL, LName varchar(100) NULL)
iii.
In the above syntax, I have written the code how to
generate the Table. There are few things you need to know identity - Unique value to increment with, NULL or NOT
NULL whether column value should be NULL or NOT NULL.
6.
ALTER
a.
This is to modify table structure after table creation
i.
ALTER Table Table_Name ADD Column DataType
ii.
To add the city column into Employee table, ALTER TABLE EmployeeTable ADD
City varchar(25)
7.
TRUNCATE
a.
TRUNCATE removes all rows from a table. The operation
cannot be rolled back and no triggers will be fired on action. TRUNCATE is
faster and doesn't use as much undo space as a DELETE.
i.
TRUNCATE Table Table_Name
ii.
TRUNCATE Table Employee_Table syntax will removed all
the records and empty the table.
8.
DROP
a.
Drop Statement is used to Drop the tables from the
database. It removes all the data from particular tables and tables itself.
b.
Be careful while using DROP Statement, It will removed
all relationship from other tables and no more usable in database.
c.
DROP TABLE Table_Name
d.
DROP Table Employee_Table
SQL Clause:
9.
WHERE
a.
Is commonly used to filter the record in SQL Server. If
you want to retrieve specific record(s) from the table, you can use WHERE
clause. It filters records as per your condition and retrieves only those.
b.
SELECT * FROM Table_Name WHERE column1 = ‘your condition’
c.
SELECT * from Employee_Table WHERE
FName = 'ved'
d.
The above statement will retrieve only those records
which is having ‘ved’ as First Name
10. ORDER
BY
a.
This statement is used to keep your records into order.
This statements sort your records in Ascending or Descending order by specified
column name.
b.
SELECT * FROM Table_Name ORDER BY Column_Name ASC |
DESC
c.
SELECT * from Employee_Table ORDER
BY FName ASC
11. GROUP
BY
a.
The statement is used to along with the group functions
to retrieve data grouped according to one or more columns.
b.
SELECT column1, function FROM Table_Name Group By
colum1
c.
SELECT FName,
SUM (salary)FROM Employee_Table GROUP
BY FName
d.
The above statement will display sum of salary group by employee First Name
12. HAVING
a.
Having clause is used to filter data based on the group
functions. This is similar to WHERE condition but is used with group functions.
Group functions cannot be used in WHERE Clause but can be used in HAVING clause
b.
SELECT column1, column2 FROM Table_Name GROUP BY
column1, column2 HAVING SUM(salary) > 20000
c.
SELECT FName,
Salary FROM EmployeeMaster GROUP BY empName, salary HAVING SUM(Salary) > 20000
d.
The above statement will retrieve the records whose
employee having sum of the salary greater than 20000
NOTE: Thease all are only basics statements
which is widely used in our daily life in SQL Server. There are also many more
like JOINS, CONSTRAINTS, RULES, INDEXES, VIEWS Sub Queries Functions etc. We
will discuss later on thease topics. Meanwhile you can enjoy your SQL
Programming.
Comments