SQL Subquery

Subquery or Inner query or Nested query is a query in a query. A subquery is usually added in the WHERE Clause of the sql statement. Most of the time, a subquery is used when you know how to search for a value using a SELECT statement, but do not know the exact value.

Subqueries are an alternate way of returning data from multiple tables.

Subqueries can be used with the following sql statements along with the comparision operators like =, <, >, >=, <= etc.

• SELECT
• INSERT
• UPDATE
• DELETE

Subquery Example:

1) Usually, a subquery should return only one record, but sometimes it can also return multiple records when used with operators like IN, NOT IN in the where clause. The query would be like,

SELECT first_name, last_name, subject 
FROM student_details 
WHERE games NOT IN ('Cricket', 'Football'); 

The output would be similar to:

first_name	last_name	subject
-------------	-------------	----------
Shekar	Gowda	Badminton
Priya	Chandra	Chess

2) Lets consider the student_details table which we have used earlier. If you know the name of the students who are studying science subject, you can get their id's by using this query below,

SELECT id, first_name 
FROM student_details 
WHERE first_name IN ('Rahul', 'Stephen'); 

but, if you do not know their names, then to get their id's you need to write the query in this manner,

SELECT id, first_name 
FROM student_details 
WHERE first_name IN (SELECT first_name 
FROM student_details 
WHERE subject= 'Science'); 

Output:

id	first_name
--------	-------------
100	Rahul
102	Stephen

In the above sql statement, first the inner query is processed first and then the outer query is processed.

3) Subquery can be used with INSERT statement to add rows of data from one or more tables to another table. Lets try to group all the students who study Maths in a table 'maths_group'.

INSERT INTO maths_group(id, name) 
SELECT id, first_name || ' ' || last_name 
FROM student_details WHERE subject= 'Maths' 

4) A subquery can be used in the SELECT statement as follows. Lets use the product and order_items table defined in the sql_joins section.

select p.product_name, p.supplier_name, (select order_id from order_items where product_id = 101) as order_id from product p where p.product_id = 101

product_name	supplier_name	order_id
------------------	------------------	----------
Television	Onida	5103

Correlated Subquery

A query is called correlated subquery when both the inner query and the outer query are interdependent. For every row processed by the inner query, the outer query is processed as well. The inner query depends on the outer query before it can be processed.

SELECT p.product_name FROM product p 
WHERE p.product_id = (SELECT o.product_id FROM order_items o 
WHERE o.product_id = p.product_id); 

NOTE:
1) You can nest as many queries you want but it is recommended not to nest more than 16 subqueries in oracle.
2) If a subquery is not dependent on the outer query it is called a non-correlated subquery.

SQL Delete Statement

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 DROP 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.

SQL UPDATE Statement

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); 

SQL INSERT Statement

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);

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.

SQL Alias

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

SQL Select

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.

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 code within the brackets is optional.

database table student_details;

• id	first_name	last_name	age	subject	games
  100	Rahul	Sharma	10	Science	Cricket
  101	Anjali	Bhagwat	12	Maths	Football
  102	Stephen	Fleming	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
  ---------------------------------Rahul Sharma
  Anjali Bhagwat
  Stephen Fleming
  Shekar Gowda
  Priya Chandra
  You can also provide aliases as below.
  SELECT first_name + ' ' + last_name AS emp_name FROM employee;
  Output:
  emp_name
  -------------Rahul Sharma
  Anjali Bhagwat
  Stephen Fleming
  Shekar Gowda
  Priya Chandra