Learn - Sql-Queries

SQL Tutorial



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.