RyanGoh's Eportfolio

11. Subquery

A subquery is a SQL query within a query. Subqueries are nested queries that provide data to the enclosing query. Subqueries can return individual values or a list of records Subqueries must be enclosed with parenthesis

Substring

CREATE TABLE t ( a TEXT, b TEXT );

INSERT INTO t VALUES ( 'NY0123', 'US4567' );

INSERT INTO t VALUES ( 'AZ9437', 'GB1234' );

INSERT INTO t VALUES ( 'CA1279', 'FR5678' );

SELECT * FROM t;

SELECT a, b, SUBSTR(a, 1, 2) AS State, SUBSTR(a, 3) AS SCode,

SUBSTR(b, 1, 2) AS Country, SUBSTR(b, 3) AS CCode FROM t;

Subselect

CREATE TABLE t ( a TEXT, b TEXT );

INSERT INTO t VALUES ( 'NY0123', 'US4567' );

INSERT INTO t VALUES ( 'AZ9437', 'GB1234' );

INSERT INTO t VALUES ( 'CA1279', 'FR5678' );

SELECT * FROM t;

SELECT co.Name, ss.CCode FROM (

SELECT SUBSTR(a, 1, 2) AS State, SUBSTR(a, 3) AS SCode,

SUBSTR(b, 1, 2) AS Country, SUBSTR(b, 3) AS CCode FROM t

) AS ss

JOIN Country AS co

ON co.Code2 = ss.Country

;

Google Sites

Report abuse