SQL (Structured Query Language) is used to modify and access data or information from a storage area called database.
This beginner sql tutorial website teaches you the basics of SQL and how to write SQL queries. I will be sharing my
knowledge on SQL and help you learn SQL better. The sql concepts discussed in this tutorial can be applied to most of
database systems. The syntax used to explain the concepts is similar to the one used in Oracle database.
Some Of the most important Sql Commands are as follwes:
SQL SELECT Statement
The most commonly used SQL command is SELECT statement. The SQL SELECT statement is used to query or retrieve data
from a table in the database. A query may retrieve information from specified columns or from all of the columns in the
table. To create a simple SQL SELECT Statement, you must specify the column(s) name and the table name. The whole
query is called SQL SELECT Statement.
Note: SQL is not case sensitive. SELECT is the same as select.
Syntax of SQL SELECT Statement:
SELECT column_list FROM table-name
[WHERE Clause]
[GROUP BY clause]
[HAVING clause]
[ORDER BY clause];
Table-name is the name of the table from which the information is retrieved.
column_list includes one or more columns from which data is retrieved.
The SQL SELECT Statement
The SELECT statement is used to select data from a database.
The result is stored in a result table, called the result-set.
SELECT * Example
Now we want to select all the columns from the "Employees" table.
SQL> select * from employees;
Now we want to select the content of the columns named
"Employee_id", "LastName" and "FirstName" from the table above.
SQL> select employee_id,first_name,last_name from employees;
Where Clause:-
SQL HAVING Clause
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.
For Example: If you want to select the department that has total salary paid for its employees more than 25000, the sql
query would be like;
SELECT departmet_id, SUM (salary)
FROM employees
GROUP BY departmets
HAVING SUM(salary) > 25000
When WHERE, GROUP BY and HAVING clauses are used together in a SELECT statement,
the WHERE clause is processed first, then the rows that are returned after the WHERE clause is executed are grouped
based on the GROUP BY clause. Finally, any conditions on the group functions in the HAVING clause are applied to the
grouped rows before the final output is displayed.
SQL ORDER BY
The ORDER BY clause is used in a SELECT statement to sort results either in ascending or descending order. Oracle sorts
query results in ascending order by default.
For Example: If you want to sort the employee table by salary of the employee, the sql query would be.
SELECT first_name, salary FROM employee ORDER BY salary;
The query first sorts the result according to name and then displays it.
You can also use more than one column in the ORDER BY clause.
he query first sorts the result according to name and then displays it.
You can also use more than one column in the ORDER BY clause.
Alter Table :-
SQL ALTER TABLE Statement
The SQL ALTER TABLE command is used to modify the definition (structure)
of a table by modifying the definition of its columns. The ALTER
command is
used to perform the following functions.
1) Add, drop, modify table columns
2) Add and drop constraints
3) Enable and Disable constraints
Syntax to add a column
ALTER TABLE table_name ADD column_name datatype;
For Example: To add a column "experience" to the employee table, the query would be like
ALTER TABLE employees ADD experience number(3);
Syntax to drop a column
ALTER TABLE table_name DROP column_name;
For Example: To drop the column "location" from the employees table,
the query would be like
ALTER TABLE employees DROP location;
Syntax to modify a column
ALTER TABLE table_name MODIFY column_name datatype;
For Example: To modify the column salary in the employees table, the query would be like
ALTER TABLE employees MODIFY salary number(15,2);
SQL RENAME Command
The SQL RENAME command is used to change the name of the table or a database object.
If you change the object's name any reference to the old name will be
affected. You have to manually change the old name to the new name in
every reference.
Syntax to rename a table
RENAME old_table_name To new_table_name;
For Example: To change the name of the table employees to my_employee, the query would be like
RENAME employees TO my_emloyee;
The SQL SELECT DISTINCT Statement
In a table, some of the columns may contain duplicate values. This is not a problem, however,
sometimes you will want to list only the different (distinct) values in a table.
The DISTINCT keyword can be used to return only distinct (different) values.
SQL SELECT DISTINCT Syntax
SELECT DISTINCT column_name(s)
FROM table_name
SELECT DISTINCT City FROM emp_info;
SQL WHERE Clause
The WHERE clause is used to extract only those records that fulfill a specified criterion
SELECT * FROM Emp_info
WHERE City='Mumbai'
Use Quotes Around Text Fields
SQL uses single quotes around text values (most database systems will also accept double quotes).
However, numeric values should not be enclosed in quotes....
Display the last name and department
number of all employees in departments 20 and 50 in
alphabetical order by name.
SELECT last_name, department_id
FROM employees
WHERE department_id IN (20, 50)
ORDER BY last_name;
