Learning SQL - SQL Query, SQL Statements, Learn SQL Basics

SQL CREATE

noreply@blogger.com (Anonymous) — Sat, 07 Jan 2012 17:35:00 +0000

The CREATE TABLE Statement is used to create tables to store data. Integrity Constraints like primary key, unique key, foreign key can be defined for the columns while creating the table. The integrity constraints can be defined at column level or table level. The implementation and the syntax of the CREATE Statements differs for different RDBMS.

The Syntax for the CREATE TABLE Statement is:

 CREATE TABLE table_name  

(column_name1 datatype,  

column_name2 datatype,  

... column_nameN datatype 

);

table_name - is the name of the table.
column_name1, column_name2.... - is the name of the columns
datatype - is the datatype for the column like char, date, number etc.

For Example: If you want to create the employee table, the statement would be like,

 CREATE TABLE employee 

( id number(5), 

name char(20), 

dept  char(10), 

age number(2), 

salary number(10), 

location char(10) 

);

In Oracle database, the datatype for an integer column is represented as "number". In Sybase it is represented as "int". Oracle provides another way of creating a table.

 CREATE TABLE temp_employee 

SELECT * FROM employee

In the above statement, temp_employee table is created with the same number of columns and datatype as employee table.

<< Previous Chapter Next Chapter >>

SQL Delete

noreply@blogger.com (Anonymous) — Sat, 07 Jan 2012 17:32:00 +0000

The DELETE Statement is used to delete rows from a table.

The Syntax of a SQL DELETE statement is:
DELETE FROM table_name [WHERE condition];

table_name -- the table name which has to be updated.

NOTE: The WHERE clause in the sql delete command is optional and it identifies the rows in the column that gets deleted. If you do not include the WHERE clause all the rows in the table is deleted, so be careful while writing a DELETE query without WHERE clause.

For Example: To delete an employee with id 100 from the employee table, the sql delete query would be like,
DELETE FROM employee WHERE id = 100;

To delete all the rows from the employee table, the query would be like,
DELETE FROM employee;

SQL TRUNCATE Statement

The SQL TRUNCATE command is used to delete all the rows from the table and free the space containing the table.

Syntax to TRUNCATE a table:

TRUNCATE TABLE table_name;

For Example: To delete all the rows from employee table, the query would be like,
TRUNCATE TABLE employee;

Difference between DELETE and TRUNCATE Statements:

DELETE Statement: This command deletes only the rows from the table based on the condition given in the where clause or deletes all the rows from the table if no condition is specified. But it does not free the space containing the table.

TRUNCATE statement: This command is used to delete all the rows from the table and free the space containing the table.

SQL DROP Statement:

The SQL DROP command is used to remove an object from the database. If you drop a table, all the rows in the table is deleted and the table structure is removed from the database. Once a table is dropped we cannot get it back, so be careful while using RENAME command. When a table is dropped all the references to the table will not be valid.

Syntax to drop a sql table structure:
DROP TABLE table_name;

For Example: To drop the table employee, the query would be like
DROP TABLE employee;

Difference between DROP and TRUNCATE Statement:

If a table is dropped, all the relationships with other tables will no longer be valid, the integrity constraints will be dropped, grant or access privileges on the table will also be dropped, if want use the table again it has to be recreated with the integrity constraints, access privileges and the relationships with other tables should be established again. But, if a table is truncated, the table structure remains the same, therefore any of the above problems will not exist.

<< Previous Chapter Next Chapter >>

SQL UPDATE

noreply@blogger.com (Anonymous) — Sat, 07 Jan 2012 17:24:00 +0000

The UPDATE Statement is used to modify the existing rows in a table.

The Syntax for SQL UPDATE Command is:

 UPDATE table_name  

SET column_name1 = value1,  

column_name2 = value2, ...  

[WHERE condition]

table_name - the table name which has to be updated.
column_name1, column_name2.. - the columns that gets changed.
value1, value2... - are the new values.

NOTE: In the Update statement, WHERE clause identifies the rows that get affected. If you do not include the WHERE clause, column values for all the rows get affected.

For Example: To update the location of an employee, the sql update query would be like,

 UPDATE employee 

SET location ='Mysore' 

WHERE id = 101;

To change the salaries of all the employees, the query would be,

  UPDATE employee 

SET salary = salary + (salary * 0.2);

<< Previous Chapter Next Chapter >>

SQL INSERT

noreply@blogger.com (Anonymous) — Sat, 07 Jan 2012 17:20:00 +0000

The INSERT Statement is used to add new rows of data to a table. We can insert data to a table in two ways,

1) Inserting the data directly to a table.

Syntax for SQL INSERT is:

 INSERT INTO TABLE_NAME 

[ (col1, col2, col3,...colN)]  

VALUES (value1, value2, value3,...valueN);

col1, col2,...colN -- the names of the columns in the table into which you want to insert data.

While inserting a row, if you are adding value for all the columns of the table you need not specify the column(s) name in the sql query. But you need to make sure the order of the values is in the same order as the columns in the table. The sql insert query will be as follows

 INSERT INTO TABLE_NAME 

VALUES (value1, value2, value3,...valueN);

For Example: If you want to insert a row to the employee table, the query would be like,
INSERT INTO employee (id, name, dept, age, salary location) VALUES (105, 'Srinath', 'Aeronautics', 27, 33000);

NOTE: When adding a row, only the characters or date values should be enclosed with single quotes.
If you are inserting data to all the columns, the column names can be omitted. The above insert statement can also be written as,

 INSERT INTO employee  

VALUES (105, 'Srinath', 'Aeronautics', 27, 33000);

2) Inserting data to a table through a select statement.

Syntax for SQL INSERT is:

 INSERT INTO table_name 

[(column1, column2, ... columnN)] 

SELECT column1, column2, ...columnN 

FROM table_name [WHERE condition];

For Example: To insert a row into the employee table from a temporary table, the sql insert query would be like,

 INSERT INTO employee (id, name, dept, age, salary location) SELECT emp_id, emp_name, dept, age, salary, location  

FROM temp_employee;

If you are inserting data to all the columns, the above insert statement can also be written as,

 INSERT INTO employee  

SELECT * FROM temp_employee;

NOTE: We have assumed the temp_employee table has columns emp_id, emp_name, dept, age, salary, location in the above given order and the same datatype.

IMPORTANT NOTE:
1) When adding a new row, you should ensure the datatype of the value and the column matches
2) You follow the integrity constraints, if any, defined for the table.

<< Previous Chapter Next Chapter >>

SQL HAVING

noreply@blogger.com (Anonymous) — Sat, 07 Jan 2012 17:15:00 +0000

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 dept, SUM (salary) 

FROM employee 

GROUP BY dept 

HAVING SUM (salary) > 25000

The output would be like:

dept	salary
-------------	-------------
Electronics	55000
Aeronautics	35000
InfoTech	30000

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.

<< Previous Chapter Next Chapter >>

SQL GROUP BY

noreply@blogger.com (Anonymous) — Sat, 07 Jan 2012 13:08:00 +0000

The SQL GROUP BY Clause is used along with the group functions to retrieve data grouped according to one or more columns.

For Example: If you want to know the total amount of salary spent on each department, the query would be:

 SELECT dept, SUM (salary) 

FROM employee 

GROUP BY dept;

The output would be like:

dept	salary
----------------	--------------
Electrical	25000
Electronics	55000
Aeronautics	35000
InfoTech	30000

NOTE: The group by clause should contain all the columns in the select list expect those used along with the group functions.

 SELECT location, dept, SUM (salary) 

FROM employee 

GROUP BY location, dept;

The output would be like:

location	dept	salary
-------------	---------------	-----------
Bangalore	Electrical	25000
Bangalore	Electronics	55000
Mysore	Aeronautics	35000
Mangalore	InfoTech	30000

<< Previous Chapter Next Chapter >>

SQL GROUP Functions

noreply@blogger.com (Anonymous) — Sat, 07 Jan 2012 13:05:00 +0000

Group functions are built-in SQL functions that operate on groups of rows and return one value for the entire group. These functions are: COUNT, MAX, MIN, AVG, SUM, DISTINCT

SQL COUNT (): This function returns the number of rows in the table that satisfies the condition specified in the WHERE condition. If the WHERE condition is not specified, then the query returns the total number of rows in the table.

For Example: If you want the number of employees in a particular department, the query would be:

 SELECT COUNT (*) FROM employee 

WHERE dept = 'Electronics';

The output would be '2' rows.

If you want the total number of employees in all the department, the query would take the form:
SELECT COUNT (*) FROM employee;

The output would be '5' rows.

SQL DISTINCT(): This function is used to select the distinct rows.

For Example: If you want to select all distinct department names from employee table, the query would be:
SELECT DISTINCT dept FROM employee;

To get the count of employees with unique name, the query would be:
SELECT COUNT (DISTINCT name) FROM employee;

SQL MAX(): This function is used to get the maximum value from a column.
To get the maximum salary drawn by an employee, the query would be:
SELECT MAX (salary) FROM employee;

SQL MIN(): This function is used to get the minimum value from a column.
To get the minimum salary drawn by an employee, he query would be:
SELECT MIN (salary) FROM employee;

SQL AVG(): This function is used to get the average value of a numeric column.
To get the average salary, the query would be
SELECT AVG (salary) FROM employee;

SQL SUM(): This function is used to get the sum of a numeric column
To get the total salary given out to the employees,
SELECT SUM (salary) FROM employee;

<< Previous Chapter Next Chapter >>

SQL ORDER BY

noreply@blogger.com (Anonymous) — Sat, 07 Jan 2012 13:02:00 +0000

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.

Syntax for using SQL ORDER BY clause to sort data is:

 SELECT column-list 

FROM table_name [WHERE condition] 

[ORDER BY column1 [, column2, .. columnN] [DESC]];

database table "employee";

id	name	dept	age	salary	location
100	Ramesh	Electrical	24	25000	Bangalore
101	Hrithik	Electronics	28	35000	Bangalore
102	Harsha	Aeronautics	28	35000	Mysore
103	Soumya	Electronics	22	20000	Bangalore
104	Priya	InfoTech	25	30000	Mangalore

For Example: If you want to sort the employee table by salary of the employee, the sql query would be.
SELECT name, salary FROM employee ORDER BY salary;

The output would be like

name	salary
----------	----------
Soumya	20000
Ramesh	25000
Priya	30000
Hrithik	35000
Harsha	35000

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. If you want to sort the employee table by the name and salary, the query would be like,
SELECT name, salary FROM employee ORDER BY name, salary;

The output would be like:

name	salary
-------------	-------------
Soumya	20000
Ramesh	25000
Priya	30000
Harsha	35000
Hrithik	35000

NOTE: The columns specified in ORDER BY clause should be one of the columns selected in the SELECT column list.

You can represent the columns in the ORDER BY clause by specifying the position of a column in the SELECT list, instead of writing the column name. The above query can also be written as given below,
SELECT name, salary FROM employee ORDER BY 1, 2;

By default, the ORDER BY Clause sorts data in ascending order. If you want to sort the data in descending order, you must explicitly specify it as shown below.

 SELECT name, salary 

FROM employee 

ORDER BY name, salary DESC;

The above query sorts only the column 'salary' in descending order and the column 'name' by ascending order.
If you want to select both name and salary in descending order, the query would be as given below.

 SELECT name, salary 

FROM employee 

ORDER BY name DESC, salary DESC;

How to use expressions in the ORDER BY Clause?

Expressions in the ORDER BY clause of a SELECT statement.

For example: If you want to display employee name, current salary, and a 20% increase in the salary for only those employees for whom the percentage increase in salary is greater than 30000 and in descending order of the increased price, the SELECT statement can be written as shown below

 SELECT name, salary, salary*1.2 AS new_salary  

FROM employee  

WHERE salary*1.2 > 30000 

ORDER BY new_salary DESC;

The output for the above query is as follows.

name	salary	new_salary
----------	----------	-------------
Hrithik	35000	37000
Harsha	35000	37000
Priya	30000	36000

NOTE: Aliases defined in the SELECT Statement can be used in ORDER BY Clause.

<< Previous Chapter Next Chapter >>

SQL LIKE, IN, BETWEEN.....

noreply@blogger.com (Anonymous) — Sat, 07 Jan 2012 12:54:00 +0000

SQL Comparison Keywords

There are other comparison keywords available in sql which are used to enhance the search capabilities of a sql query. They are "IN", "BETWEEN...AND", "IS NULL", "LIKE".

Comparision Operators	Description
LIKE	column value is similar to specified character(s).
IN	column value is equal to any one of a specified set of values.
BETWEEN...AND	column value is between two values, including the end values specified in the range.
IS NULL	column value does not exist.

SQL LIKE Operator

The LIKE operator is used to list all rows in a table whose column values match a specified pattern. It is useful when you want to search rows to match a specific pattern, or when you do not know the entire value. For this purpose we use a wildcard character '%'.

For example: To select all the students whose name begins with 'S'

 SELECT first_name, last_name 

FROM student_details 

WHERE first_name LIKE 'S%';

The output would be similar to:

first_name	last_name
-------------	-------------
Stephen	Fleming
Shekar	Gowda

The above select statement searches for all the rows where the first letter of the column first_name is 'S' and rest of the letters in the name can be any character.

There is another wildcard character you can use with LIKE operator. It is the underscore character, ' _ ' . In a search string, the underscore signifies a single character.

For example: to display all the names with 'a' second character,

 SELECT first_name, last_name 

FROM student_details 

WHERE first_name LIKE '_a%';

The output would be similar to:

first_name	last_name
-------------	-------------
Rahul	Sharma

NOTE:Each underscore act as a placeholder for only one character. So you can use more than one underscore. Eg: ' __i% '-this has two underscores towards the left, 'S__j%' - this has two underscores between character 'S' and 'i'.

SQL BETWEEN ... AND Operator

The operator BETWEEN and AND, are used to compare data for a range of values.

For Example: to find the names of the students between age 10 to 15 years, the query would be like,

 SELECT first_name, last_name, age 

FROM student_details 

WHERE age BETWEEN 10 AND 15;

The output would be similar to:

first_name	last_name	age
-------------	-------------	------
Rahul	Sharma	10
Anajali	Bhagwat	12
Shekar	Gowda	15

SQL IN Operator:

The IN operator is used when you want to compare a column with more than one value. It is similar to an OR condition.

For example: If you want to find the names of students who are studying either Maths or Science, the query would be like,

 SELECT first_name, last_name, subject 

FROM student_details 

WHERE subject IN ('Maths', 'Science');

The output would be similar to:

first_name	last_name	subject
-------------	-------------	----------
Anajali	Bhagwat	Maths
Shekar	Gowda	Maths
Rahul	Sharma	Science
Stephen	Fleming	Science

You can include more subjects in the list like ('maths','science','history')

NOTE: The data used to compare is case sensitive.

SQL IS NULL Operator

A column value is NULL if it does not exist. The IS NULL operator is used to display all the rows for columns that do not have a value.

For Example: If you want to find the names of students who do not participate in any games, the query would be as given below

 SELECT first_name, last_name 

FROM student_details 

WHERE games IS NULL

There would be no output as we have every student participate in a game in the table student_details, else the names of the students who do not participate in any games would be displayed.

<< Previous Chapter Next Chapter >>

SQL Logical Operators

noreply@blogger.com (Anonymous) — Sat, 07 Jan 2012 12:48:00 +0000

There are three Logical Operators namely, AND, OR, and NOT. These operators compare two conditions at a time to determine whether a row can be selected for the output. When retrieving data using a SELECT statement, you can use logical operators in the WHERE clause, which allows you to combine more than one condition.

Logical Operators	Description
OR	For the row to be selected at least one of the conditions must be true.
AND	For a row to be selected all the specified conditions must be true.
NOT	For a row to be selected the specified condition must be false.

"OR" Logical Operator:

If you want to select rows that satisfy at least one of the given conditions, you can use the logical operator, OR.

For example: if you want to find the names of students who are studying either Maths or Science, the query would be like,

 SELECT first_name, last_name, subject 

FROM student_details 

WHERE subject = 'Maths' OR subject = 'Science'

The output would be something like,

first_name	last_name	subject
-------------	-------------	----------
Anajali	Bhagwat	Maths
Shekar	Gowda	Maths
Rahul	Sharma	Science
Stephen	Fleming	Science

The following table describes how logical "OR" operator selects a row.

Column1 Satisfied?	Column2 Satisfied?	Row Selected
YES	YES	YES
YES	NO	YES
NO	YES	YES
NO	NO	NO

"AND" Logical Operator:

If you want to select rows that must satisfy all the given conditions, you can use the logical operator, AND.
For Example: To find the names of the students between the age 10 to 15 years, the query would be like:

 SELECT first_name, last_name, age 

FROM student_details 

WHERE age >= 10 AND age <= 15;

The output would be something like,

first_name	last_name	age
-------------	-------------	------
Rahul	Sharma	10
Anajali	Bhagwat	12
Shekar	Gowda	15

The following table describes how logical "AND" operator selects a row.

Column1 Satisfied?	Column2 Satisfied?	Row Selected
YES	YES	YES
YES	NO	NO
NO	YES	NO
NO	NO	NO

"NOT" Logical Operator:

If you want to find rows that do not satisfy a condition, you can use the logical operator, NOT. NOT results in the reverse of a condition. That is, if a condition is satisfied, then the row is not returned.

For example: If you want to find out the names of the students who do not play football, the query would be like:

 SELECT first_name, last_name, games 

FROM student_details 

WHERE NOT games = 'Football'

The output would be something like,

first_name	last_name	games
----------------	----------------	-----------
Rahul	Sharma	Cricket
Stephen	Fleming	Cricket
Shekar	Gowda	Badminton
Priya	Chandra	Chess

The following table describes how logical "NOT" operator selects a row.

Column1 Satisfied?	NOT Column1 Satisfied?	Row Selected
YES	NO	NO
NO	YES	YES

Nested Logical Operators:
You can use multiple logical operators in an SQL statement. When you combine the logical operators in a SELECT statement, the order in which the statement is processed is
1) NOT
2) AND
3) OR

For example: If you want to select the names of the students who age is between 10 and 15 years, or those who do not play football, the

 SELECT statement would be

SELECT first_name, last_name, age, games 

FROM student_details 

WHERE age >= 10 AND age <= 15 

OR NOT games = 'Football'

The output would be something like,

first_name	last_name	age	games
-------------	-------------	--------	------------
Rahul	Sharma	10	Cricket
Priya	Chandra	15	Chess

In this case, the filter works as follows:
Condition 1: All the students you do not play football are selected.
Condition 2: All the students whose are aged between 10 and 15 are selected.
Condition 3: Finally the result is, the rows which satisfy atleast one of the above conditions is returned.

NOTE: The order in which you phrase the condition is important, if the order changes you are likely to get a different result.

<< Previous Chapter Next Chapter >>

SQL Operators

noreply@blogger.com (Anonymous) — Sat, 07 Jan 2012 12:43:00 +0000

There are two type of Operators, namely Comparison Operators and Logical Operators. These operators are used mainly in the WHERE clause, HAVING clause to filter the data to be selected.

Comparison Operators:

Comparison operators are used to compare the column data with specific values in a condition.
Comparison Operators are also used along with the SELECT statement to filter data based on specific conditions.

The below table describes each comparison operator.

Comparison Operators	Description
=	equal to
<>, !=	is not equal to
<	less than
>	greater than
>=	greater than or equal to
<=	less than or equal to

Logical Operators:

There are three Logical Operators namely AND, OR and NOT.
Logical operators are discussed in detail in the next section.

<< Previous Chapter Next Chapter >>

SQL WHERE

noreply@blogger.com (Anonymous) — Sat, 07 Jan 2012 12:31:00 +0000

SQL WHERE Clause

The WHERE Clause is used when you want to retrieve specific information from a table excluding other irrelevant data. For example, when you want to see the information about students in class 10th only then you do need the information about the students in other class. Retrieving information about all the students would increase the processing time for the query.

So SQL offers a feature called WHERE clause, which we can use to restrict the data that is retrieved. The condition you provide in the WHERE clause filters the rows retrieved from the table and gives you only those rows which you expected to see. WHERE clause can be used along with SELECT, DELETE, UPDATE statements.

Syntax of SQL WHERE Clause:

  WHERE {column or expression} comparison-operator value



Syntax for a WHERE clause with Select statement is:



SELECT column_list FROM table-name 

WHERE condition;

column or expression - Is the column of a table or a expression
comparison-operator - operators like = < > etc.
value - Any user value or a column name for comparison

For Example: To find the name of a student with id 100, the query would be like:

  SELECT first_name, last_name FROM student_details 

WHERE id = 100;

Comparison Operators and Logical Operators are used in WHERE Clause. These operators are discussed in the next chapter.

NOTE: Aliases defined for the columns in the SELECT statement cannot be used in the WHERE clause to set conditions. Only aliases created for tables can be used to reference the columns in the table.

How to use expressions in the WHERE Clause?

Expressions can also be used in the WHERE clause of the SELECT statement.

For example: Lets consider the employee table. If you want to display employee name, current salary, and a 20% increase in the salary for only those products where the percentage increase in salary is greater than 30000, the SELECT statement can be written as shown below:

  SELECT name, salary, salary*1.2 AS new_salary FROM employee 

WHERE salary*1.2 > 30000;

Output:

name	salary	new_salary
-----------	----------	----------------
Hrithik	35000	37000
Harsha	35000	37000
Priya	30000	360000

NOTE: Aliases defined in the SELECT Statement can be used in WHERE Clause.

<< Previous Chapter Next Chapter >>

SQL Alias

noreply@blogger.com (Anonymous) — Sat, 07 Jan 2012 06:02:00 +0000

SQL Aliases are defined for columns and tables. Basically aliases is created to make the column selected more readable. For Example: To select the first name of all the students, the query would be like:

Aliases for columns:

  SELECT first_name AS Name FROM student_details;

or

SELECT first_name Name FROM student_details;

In the above query, the column first_name is given a alias as 'name'. So when the result is displayed the column name appears as 'Name' instead of 'first_name'.

Output:
Name
-------------
Rahul Sharma
Anjali Bhagwat
Stephen Fleming
Shekar Gowda
Priya Chandra

Aliases for tables:

  SELECT s.first_name FROM student_details s;

In the above query, alias 's' is defined for the table student_details and the column first_name is selected from the table.
Aliases is more useful when

There are more than one tables involved in a query,
Functions are used in the query,
The column names are big or not readable,
More than one columns are combined together

<< Previous Chapter Next Chapter >>

SQL SELECT

noreply@blogger.com (Anonymous) — Sat, 07 Jan 2012 05:57:00 +0000

One of the most or one must say The most commonly used SQL command is the 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 a specified column 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 query as a whole is called SQL SELECT Statement.

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 you wish to retrieve the information.
column_list includes one or more columns from which the data is retrieved.
The code within the brackets is optional.

database table student_details;

id	first_name	last_name	age	subject	games
100	Saurabh	Sharma	10	Science	Cricket
101	Anjali	Bhagwat	12	Maths	Football
102	Stephen	James	09	Science	Cricket
103	Shekar	Gowda	18	Maths	Badminton
104	Priya	Chandra	15	Economics	Chess

NOTE: These database tables are used here for better explanation of SQL commands. In reality, the tables can have different columns and different data.
For example, consider the table student_details. To select the first name of all the students the query would be like:
SELECT first_name FROM student_details;

NOTE: The commands are not case sensitive. The above SELECT statement can also be written as "select first_name from students_details;"
You can also retrieve data from more than one column. For example, to select first name and last name of all the students.
SELECT first_name, last_name FROM student_details;

You can also use clauses like WHERE, GROUP BY, HAVING, ORDER BY with SELECT statement. We will discuss these commands in coming chapters.

NOTE: In a SQL SELECT statement only SELECT and FROM statements are mandatory. Other clauses like WHERE, ORDER BY, GROUP BY, HAVING are optional.

How to use expressions in SQL SELECT Statement?

Expressions combine many arithmetic operators, they can be used in SELECT, WHERE and ORDER BY Clauses of the SQL SELECT Statement.

Here we will explain how to use expressions in the SQL SELECT Statement. About using expressions in WHERE and ORDER BY clause, they will be explained in their respective sections.

The operators are evaluated in a specific order of precedence, when more than one arithmetic operator is used in an expression. The order of evaluation is: parentheses, division, multiplication, addition, and subtraction. The evaluation is performed from the left to the right of the expression.

For example: If we want to display the first and last name of an employee combined together, the SQL Select Statement would be like
SELECT first_name || ' ' || last_name FROM employee;

Output:
first_name || ' ' || last_name
--------------------------------- Saurabh Sharma
Anjali Bhagwat
Stephen James
Shekar Gowda
Priya Chandra

You can also provide aliases as below.
SELECT first_name || ' ' || last_name AS emp_name FROM employee;

Output:
emp_name
-------------
Saurabh Sharma
Anjali Bhagwat
Stephen James
Shekar Gowda
Priya Chandra

<< Previous Chapter Next Chapter >>

SQL Commands

noreply@blogger.com (Anonymous) — Sat, 07 Jan 2012 05:38:00 +0000

SQL commands are instructions used to communicate with the database to perform specific task that work with data. SQL commands can be used not only for searching the database but also to perform various other functions like, for example, you can create tables, add data to tables, or modify data, drop the table, set permissions for users. SQL commands are grouped into four major categories depending on their functionality:

Data Definition Language (DDL) - These SQL commands are used for creating, modifying, and dropping the structure of database objects. The commands are CREATE, ALTER, DROP, RENAME, and TRUNCATE.
Data Manipulation Language (DML) - These SQL commands are used for storing, retrieving, modifying, and deleting data. These commands are SELECT, INSERT, UPDATE, and DELETE.
Transaction Control Language (TCL) - These SQL commands are used for managing changes affecting the data. These commands are COMMIT, ROLLBACK, and SAVEPOINT.
Data Control Language (DCL) - These SQL commands are used for providing security to database objects. These commands are GRANT and REVOKE.

Next Chapter >>