Select Clause
You can use the Select and Select Distinct clauses to run queries to analyze data in a table.
For example, the following query returns all data in table1:
SELECT * from table 1
The following query returns distinct data values from the columns col 1 and col 2 in table 2.
SELECT DISTINCT col 1, col 2 from table 2
You can use joins, aggregate functions, the Where clause with options, and other clauses with the Select and Select Distinct clauses.
You can run queries with table names and column names that contain spaces or dashes. You must enter the table name or column name within '\"'. Use the following syntax:
SELECT \"<column-name>\" from \"<table name>\"
Alias
You can use aliases when you run a query with the Select and Select Distinct clauses.
For example:
SELECT <alias_name>.<column_name> FROM <table_name> <alias_name>
SELECT <column_name> as <alias_name> from <table_name>
SELECT e.dept_id FROM Employee e
SELECT emp_name as name from TDWEMP
SELECT e.dept_id, e.name FROM Employee e WHERE e.name like 'J%'
Joins
You can use joins when you run a query with the Select and Select Distinct clauses.
You can use the following types of joins in a query:
- Inner Join
- Use the following syntax to perform an inner join:
- For example:
SELECT suppliers.supplier_id, suppliers.supplier_name, orders.order_date
FROM suppliers,
INNER JOIN orders
ON suppliers.supplier_id = orders.supplier_id
- Left Outer Join
- Use the following syntax to perform a left outer join:
- For example:
SELECT suppliers.supplier_id, suppliers.supplier_name, orders.order_date
FROM suppliers
LEFT OUTER JOIN orders
ON suppliers.supplier_id = orders.supplier_id
- Right Outer Join
- Use the following syntax to perform a right outer join:
- For example:
SELECT orders.order_id, orders.order_date, suppliers.supplier_name
FROM suppliers
RIGHT OUTER JOIN orders
ON suppliers.supplier_id = orders.supplier_id
- Full Outer Join
- Use the following syntax to perform a full outer join:
- For example:
SELECT suppliers.supplier_id, suppliers.supplier_name, orders.order_date
FROM suppliers
FULL OUTER JOIN orders
ON suppliers.supplier_id = orders.supplier_id
Aggregate Functions
You can use aggregate functions when you run a query with the Select and Select Distinct clauses.
You can use the following types of aggregate functions in a query:
- AVG
- Use the following syntax:
SELECT AVG(<column_name>) FROM <table_name>
- For example:
SELECT AVG(SAL) FROM TDWEMP
- COUNT
- Use the following syntax:
SELECT COUNT(<column_name>) FROM <table_name>
- For example:
SELECT COUNT(*) FROM TDWEMP
- MAX
- Use the following syntax:
SELECT MAX(<column_name>) FROM <table_name>
- For example:
SELECT MAX(SAL) FROM TDWEMP
- MIN
- Use the following syntax:
SELECT MIN(<column_name>) FROM <table_name>
- For example:
SELECT MIN(SAL) FROM TDWEMP
- SUM
- Use the following syntax:
SELECT SUM(<column_name> FROM <table_name>
- For example:
SELECT SUM(SAL) FROM TDWEMP
Where Clause
You can use the Where clause to specify filter criteria in a query with the Select and Select Distinct clauses.
You can run queries that use parentheses with the Where clause. For example:
SELECT * from Employee where NAME ='Mary' OR (NAME='Jessica' AND DEPT_ID=1)
You can use the Where clause with the following conditions in a query:
- Single condition
- Use the following syntax:
SELECT * FROM <table_name> WHERE <column_name> = <column_value>
- For example:
SELECT * FROM customers WHERE state = 'California'
- And
- Use the following syntax:
SELECT * FROM <table_name> WHERE <column_name> = <column_value> AND <column_value> <operator> <column_value>
- For example:
SELECT * FROM customers WHERE state = 'California' AND company_name = 'Informatica'
- OR
- Use the following syntax:
SELECT * FROM <table_name> WHERE <column_name> = <column_value> OR <column_name> <operator> <column_value>
- For example:
SELECT * FROM customers WHERE state = 'California' OR company_name = 'Informatica'
- Combining OR and AND
- Use the following syntax:
SELECT * FROM <table_name> WHERE <column_name> = <column_value> OR <column_name> <operator> <column_value> AND <column_name> <operator> <column_value>
- For example:
SELECT * FROM customers WHERE state = 'California' OR available_credit > 500 and revenue < 90
- Joining and where
- Use the following syntax:
SELECT columns FROM <table 1> LEFT OUTER JOIN <table 2> ON <table 1>.<col 1> = <table 2>.<col 1> WHERE <table 1>.<col 1> <operator> <column_value>
- For example:
SELECT suppliers.supplier_id, suppliers.supplier_name, orders.order_date FROM suppliers LEFT OUTER JOIN orders ON suppliers.supplier_id = orders.supplier_id WHERE supplier.supplier_id > 10
- Exists
- Use the following syntax:
SELECT DISTINCT <col 1> FROM <table 2> WHERE EXISTS (SELECT * FROM <table 2> WHERE <table 1>.<col 1> <operator> <table 1>.<col 1>)
- For example:
SELECT DISTINCT store_type FROM stores WHERE EXISTS (SELECT * FROM cities_stores WHERE cities_stores.store_type = stores.store_type)
- In
- Use the following syntax:
SELECT * FROM <table 1> WHERE <col 1> IN (SELECT <col 1> FROM <table 2>)
- For example:
SELECT * FROM Employee WHERE dept_id IN (SELECT DEPT_ID FROM TDWDEPT)
- Like
- Use the following syntax:
SELECT * FROM <table 1> WHERE <col 1> LIKE <value>
- You can use characters or wildcards '%' and '_'. For example:
SELECT * FROM Employee WHERE name LIKE 'J%'
- Is Null or Is Not Null
- Use the following syntax:
SELECT * FROM <table 1> WHERE <col 1> IS NULL
- For example:
SELECT * FROM Employee WHERE ssn IS NULL
- Between
- Use the following syntax:
SELECT * FROM <table 1> WHERE <col 1> BETWEEN <value> AND <value>
- For example:
SELECT * FROM Employee WHERE delt_id BETWEEN 2 AND 3
- Not
- Use the following syntax:
SELECT * FROM <table 1> WHERE <col 1> NOT <CONDITION> <value>
- For example:
SELECT DISTINCT * from STATE s WHERE NOT EXISTS (SELECT ct.state_id from city ct where ct.state_id = s.state_id)
SELECT * FROM Employee WHERE dept_id NOT IN (SELECT DEPT_ID FROM TDWDEPT)
SELECT * FROM Employee WHERE name NOT LIKE 'J%'
SELECT * FROM Employee WHERE delt_id NOT BETWEEN 2 AND 3
Additional Clauses
You can use the following clauses when you run a query with the Select clause:
- Group by
- Use the following syntax:
SELECT <col 1> FROM <table 1> GROUP BY <col 1>
- For example:
SELECT name FROM Employee GROUP BY NAME
- Having
- Use the following syntax:
SELECT <col 1> FROM <table 1> GROUP BY <col 1> HAVING <col 1> LIKE <value>
- For example:
SELECT name FROM Employee GROUP BY NAME HAVING name like 'J%'
- Order by
- Use the following syntax. You can use the optional modifiers ASC and DESC:
SELECT <col 1> FROM <table 1> ORDER BY <col 1> ASC
- For example:
SELECT dept_id, name FROM Employee ORDER BY dept_id ASC