Retrieving Data From A Number of Tables (Relations) : Use Select From Where

Retrieving data from a number of tables (relations)
Some tales of a database are logically lined using primary keys and foreign keys.
For example DEPARTMENT tables and EMPLOYEE tables are logically linked using the
Primary Key (PK) and Foreign Key (FK).
DEPARTMENT
Dept_No Dept_Name Block_No

1 Administration A
2 Accounts E
3 IT HelpDesk A
4 Maintenance C
5 Council E
6 Welfare A
7 Social Science B
8 Computing C
9 Business D
10 Technology E
EMPLOYEE
Emp_No Emp_Fname Emp_Lname Emp_Salary Emp_City Dept_No

100 Don Dunlop 20000 Rotorua 1
101 Peter Huntly 23000 Tauranga 1
102 Sam Rodrigo 40000 Rotorua 3
103 Richard Bird 90000 Taupo 2
104 Malkeet Singh 56000 Taupo 4
105 Haren Korea 78000 Rotorua 5
106 Ram Malik 34000 Tauranga 6
107 Malan Brando 53000 Tauranga 7
108 Sanka Bradman 12000 Auckland 2
109 param Dunkan 120000 Auckland 2
Example: We shall list department name and first name of employee using Transact-SQL command.
Since these data are coming from two tables, we have to join them temporally within the
Transact-SQL.
The command is: This line joins two tables

use <Your database name>
temporally.
Select DEPARTMENT.Dept_Name, EMPLOYEE.Emp_Fname
From DEPARTMENT, EMPLOYEE
WHERE DEPARTMENT.Dept_No = EMPLOYEE.Dept_No;
PK FK
1
The temporary Teble created on the computer memory after joining tables.
DEPARTMEN DEPARTMEN DEPARTMEN EMPLOYE EMPLOYEE . EMPLOYEE . EMPLOYEE . EMPLOYE EMPLOYE

T .Dept_No T T .Block_No E Emp_Fname Emp_Lname Emp_Salary E E
.Dept_Name .Emp_No Emp_City Dept_No
The commend works without the words DEPARTMENT & EMPLOYEE
Select DEPARTMENT.Dept_Name, EMPLOYEE.Emp_Fname
WHERE DEPARTMENT.Dept_No = EMPLOYEE. Dept_No;
Just like this:
Select Dept_Name, Emp_Fname
But, If you are to list Primary key attribute and Foreign key attribute you need tables names.
Example:
Select DEPARTMENT .Dept_No, EMPLOYEE .Dept_No
NOTE: JOINING process in a database query is an expensive / time taking process. It involves
searching and matching values under the PK and FK. Tables with 500000 records might
take about 15 minutes to list the answer to a query. Therefore INDEX files are used to
support searching process.
2
QUESTION: Do we really have to join tables to get results?
Try this command:
Select Dept_Name, Emp_Fname

From DEPARTMENT, EMPLOYEE;
ORDER BY clause
We use ORDER BY clause to sort a list by an attribute.
Example:
Select Dept_No, Dept_Name

From DEPARTMENT
Order by Dept_Name; -- ascending order
Select Dept_No, Dept_Name

From DEPARTMENT
Order by Dept_Name desc; --Descending order
GROUP BY Clause
WE use GROUP BY clause on grouping and aggregate calculations.
Example
Select Dept_No, SUM(Emp_Salary)

From EMPLOYEE
Group by Dept_No;
SUM is a function ised for getting the totals.
Other Aggregate functions
AVG – is used to calculate average
COUNT - is used to count records
MIN – is used to get the minimum value
MAX – is used to get the maximum value
Use of LIKE predicate
The following command list the departments starting with the letter A.
select *
from DEPARTMENT
WHERE Dept_Name like 'A%'
Please Read
http://msdn.microsoft.com/en-us/library/ms173454.aspx
3
EXERCISES:
1. List all department names, employee names and their salaries.

2. List the names of employees who work in the “Accounts” department
3. List names of employees who live in Tauranga and their department names.
4. List the employees who work in the Block E.
5. List all department names and Employees names and their salaries sorted by their
salaries.
6. List the number employees living in Taupo.
7. List the maximum salary paid for an employee in Rotorua
8. List the department number and the total salary of employees in each department.
9. List the Block number and the total salary of employees in each block.
10. List the Block number and the total salary of employees in each block sorted by block
number.
11. List the department names starting with CO.
12. List the departments with more than two employees.
Write the Transact-SQL code to create the tables in the following ER Model. You must include
appropriate date types for the attributes and the constraints for PKs and FKs.
CUSTOMER(Cno, Cname, Address)
C_INVOICE (InvNo, Date, Cno)
PRODUCT (Pno, Pname, SalePrice)
LINE_ITEM(LineNo, InvNo, Pno, quantity)

Retrieving Data From A Number of Tables (Relations) : Use Select From Where

Uploaded by

Copyright:

Available Formats

Retrieving Data From A Number of Tables (Relations) : Use Select From Where

Uploaded by

Document Information

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Retrieving Data From A Number of Tables (Relations) : Use Select From Where

Uploaded by

Copyright:

Available Formats

Retrieving data from a number of tables (relations)

Dept_No Dept_Name Block_No

Emp_No Emp_Fname Emp_Lname Emp_Salary Emp_City Dept_No

The command is: This line joins two tables

DEPARTMEN DEPARTMEN DEPARTMEN EMPLOYE EMPLOYEE . EMPLOYEE . EMPLOYEE . EMPLOYE EMPLOYE

The commend works without the words DEPARTMENT & EMPLOYEE

Select DEPARTMENT.Dept_Name, EMPLOYEE.Emp_Fname

From DEPARTMENT, EMPLOYEE

WHERE DEPARTMENT.Dept_No = EMPLOYEE. Dept_No;

Just like this:

Select Dept_Name, Emp_Fname

From DEPARTMENT, EMPLOYEE

WHERE DEPARTMENT.Dept_No = EMPLOYEE. Dept_No;

Select DEPARTMENT .Dept_No, EMPLOYEE .Dept_No

From DEPARTMENT, EMPLOYEE

WHERE DEPARTMENT.Dept_No = EMPLOYEE. Dept_No;

Try this command:

Select Dept_Name, Emp_Fname

We use ORDER BY clause to sort a list by an attribute.

Select Dept_No, Dept_Name

Select Dept_No, Dept_Name

WE use GROUP BY clause on grouping and aggregate calculations.

Select Dept_No, SUM(Emp_Salary)

SUM is a function ised for getting the totals.

Other Aggregate functions

AVG – is used to calculate average

COUNT - is used to count records

MIN – is used to get the minimum value

MAX – is used to get the maximum value

Use of LIKE predicate

1. List all department names, employee names and their salaries.

CUSTOMER(Cno, Cname, Address)

C_INVOICE (InvNo, Date, Cno)

PRODUCT (Pno, Pname, SalePrice)

LINE_ITEM(LineNo, InvNo, Pno, quantity)

You might also like