SQL- Read from JOINED table

Question 1

How do you extract information from two tables use SQL ? Example

Answer 1

SELECT Table_Name.Column_Name( EMPLOYEES.FIRST_NAME, EMPLOYEES.LAST_NAME, DEPARTMENTS.DEPARTMENT_NAME)
FROM Table_Name1(DEPARTMENTS), Table_Name2(EMPLOYEES)
WHERE Table_Name1.Column_Name=Table_Name2.Column_Name(DEPARTMENTS.department_ID =
EMPLOYEES.department_ID)

Question 2

How do you extract information from two tables use SQL ? Steps

Answer 2

1) We start with specifying tables, where our data is located:

SELECT 	…..
FROM 	EMPLOYEES, DEPARTMENTS
WHERE	…… 2) We merge tables, using PK-FK relation: 

   SELECT 	…..
	FROM 	EMPLOYEES, DEPARTMENTS
	WHERE  	DEPARTMENTS.department_ID =
		   	EMPLOYEES.department_ID
3) Last step, after the tables are merged, choose what columns you need:  

SELECT EMPLOYEES.FIRST_NAME, EMPLOYEES.LAST_NAME, DEPARTMENTS.DEPARTMENT_NAME
FROM EMPLOYEES, DEPARTMENTS
WHERE DEPARTMENTS.department_ID =
EMPLOYEES.department_ID

Question 3

Commit

Answer 3

permanently update the dB changes

Question 4

Roll Back

Answer 4

undo your dB changes (transaction failed, etc)

Question 5

Dual Table

Answer 5

is a temporary table which contains
one column (dummy name) and a single row. 

Examples:

SELECT 1+1 FROM DUAL;

SELECT 'I am soooo tired now' FROM DUAL;

SELECT SYSDATE FROM DUAL;

SELECT USER FROM DUAL

Question 6

Constraint

Answer 6

used to define data integrity (restrict the values in dB)

• NOT NULL constraint - dB value can’t be null
• PK constraint - combines a NOT NULL and Unique constraints
• FK constraint - value in one table match values in another

Question 7

Alias

Answer 7

‘nickname’ for table and column names. Giving an alia we are NOT renaming the column or table.

For column:
SELECT AS alias_name
FROM

For Table:
SELECT
FROM alias_name

Example using Table Alias:
Using Table alias: 
SELECT e.FIRST_NAME, 				  	e.LAST_NAME, 				  	 	d.DEPARTMENT_NAME
FROM	EMPLOYEES   e, 
		DEPARTMENTS d
WHERE d.department_ID = e.department_ID

Question 8

Union

Answer 8

Combine results of 2 or more select statements (distinct only results)

SELECT column_name(s) FROM table_name1
UNION or UNION ALL
SELECT column_name(s) FROM table_name2

Question 9

UNION vs. JOIN

Answer 9

UNION combines the results of 2 or more queries into a single result set that includes all the rows that belong to all queries in the union

JOIN retrieve data from 2 or more tables based on logical relationship between the tables. Joins indicate how SQL use data from one table to select the rows in another table.