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 in the database.

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.

DATENAME

Syntax

DATENAME ( datepart , date )

Arguments

datepart

Is the parameter that specifies the part of the date to return. The table lists dateparts and abbreviations recognized by Microsoft® SQL Server™.

Datepart	Abbreviations
year	yy, yyyy
quarter	qq, q
month	mm, m
dayofyear	dy, y
day	dd, d
week	wk, ww
weekday	dw
hour	hh
minute	mi, n
second	ss, s
millisecond	ms

The weekday (dw) datepart returns the day of the week (Sunday, Monday, and so on).

Is an expression that returns a datetime or smalldatetime value, or a character string in a date format. Use the datetime data type for dates after January 1, 1753. Store as character data for earlier dates. When entering datetime values, always enclose them in quotation marks. Because smalldatetime is accurate only to the minute, when a smalldatetime value is used, seconds and milliseconds are always 0. For more information about specifying dates, see datetime and smalldatetime. For more information about specifying time values, see Time Formats.

If you specify only the last two digits of the year, values less than or equal to the last two digits of the value of the two digit year cutoff configuration option are in the same century as the cutoff year. Values greater than the last two digits of the value of this option are in the century that precedes the cutoff year. For example, if two digit year cutoff is 2049 (default), 49 is interpreted as 2049 and 2050 is interpreted as 1950. To avoid ambiguity, use four-digit years.

Return Types

nvarchar

Remarks

SQL Server automatically converts between character and datetime values as necessary, for example, when you compare a character value with a datetime value.

Examples

This example extracts the month name from the date returned by GETDATE.

SELECT DATENAME(month, getdate()) AS 'Month Name' 

Here is the result set:

Month Name                     
------------------------------ 
February