SQL- Read from JOINED table Flashcards Preview

Software Testing > SQL- Read from JOINED table > Flashcards

Flashcards in SQL- Read from JOINED table Deck (9)
Loading flashcards...
1
Q

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

A

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)

2
Q

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

A

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

3
Q

Commit

A

permanently update the dB changes

4
Q

Roll Back

A

undo your dB changes (transaction failed, etc)

5
Q

Dual Table

A
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
6
Q

Constraint

A

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
7
Q

Alias

A

‘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
8
Q

Union

A

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
9
Q

UNION vs. JOIN

A

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.