SQL JOINS CODE IN MICROSOFT SQL SERVER, Lab Reports of Database Management Systems (DBMS)

Download SQL JOINS CODE IN MICROSOFT SQL SERVER and more Lab Reports Database Management Systems (DBMS) in PDF only on Docsity!

NUST

Military College of Signals,

Rawalpindi.

DATABASE SYSTEM

LAB # 8

BATCH-26C

Submitted By:

Aymen Fatima

Submitted To:

Sir Kabeer Ahmed

DEPARTMENT OF COMPUTER SOFTWARE ENGINEERING.

MILITARY COLLEGE OF SIGNALS.

EXPERIMENT 8 – JOIN INTRODUCTION

OBJECTIVE

1 CS 220 Database Systems – Sub Queries

To learn join algorithms in SQL

THEORY

SQL Joins:

The purpose of a join concept is to combine data spread across tables. A join is actually performed by the

‘where’ clause which combines specified rows of tables.

PROGRAMS

SQL Join Algorithms

Syntax:

• Select
• From
• Join
• On
• Where

Cross Join

• A cross join (also called a Cartesian join) is a join of tables without specifying the join condition.
• In this scenario, the query would return all possible combination of the tables in the SQL query.
• CROSS JOIN returns the Cartesian product of rows from tables in the join.
• In other words, it will produce rows which combine each row from the first table with each row from the

second table.

Cartesian product

• In mathematics, a Cartesian product (or product set) is the direct product of two sets.
• Specifically, the Cartesian product of two sets X (for example the points on an x-axis) and Y (for

example the points on a y-axis), denoted X × Y , is the set of all possible ordered pairs whose first

component is a member of X and whose second component is a member of Y (e.g., the whole of the x–y

plane):

Example:

• A = {1,2}; B = {3,4}
• A × B = {1,2} × {3,4} = {(1,3), (1,4), (2,3), (2,4)}

Example : Show all combination of customer and orderTbl (Cross Join)

SELECT * FROM Customer CROSS JOIN OrderTbl;

Inner join

• Inner join creates a new result table by taking
• Cartesian product
• filter (join predicate)
• An inner join in SQL returns rows where there is at least one match on both tables.

2 CS 220 Database Systems – Sub Queries

Example: Show all coustomer with order listing (Inner Join)

SELECT * FROM Customer INNER JOIN OrderTbl ON Customer.CustNo = OrderTbl.CustNo;

OR

SELECT * FROM Customer, OrderTbl where Customer.CustNo = OrderTbl.CustNo;

Types of Join

Composite join: This join has more than one condition on single ON clause.

Example:

SELECT * FROM Customer INNER JOIN OrderTbl ON Customer.CustNo = OrderTbl.CustNo and CustState='CO'

Equi join: This join has an only equality comparison on join predicate.

Example:

SELECT * FROM Customer INNER JOIN OrderTbl ON Customer.CustNo = OrderTbl.CustNo;

Non-equijoin: This join has other operator than equality on join predicate.

Example:

SELECT * FROM Customer INNER JOIN OrderTbl ON Customer.CustNo != OrderTbl.CustNo;

Multitable join: This join is used to join more than two tables. Processing starts from left to right.

Example:

SELECT Customer.CustNo, CustFirstName, OrderTbl.OrdNo, OrdDate, Product.ProdNo, ProdName FROM Customer INNER JOIN OrderTbl ON Customer.CustNo = OrderTbl.CustNo INNER JOIN OrdLine ON OrderTbl.OrdNo = OrdLine.OrdNo INNER JOIN Product ON OrdLine.ProdNo = Product.ProdNo

Summary

A SQL join clause combines records from two or more tables in a database. It creates a set that can be saved as

a table or used as it is. A JOIN is a means for combining fields from two tables by using values common to

each. ANSI-standard SQL specifies five types of JOIN: INNER, LEFT OUTER, RIGHT OUTER, FULL

OUTER and CROSS. As a special case, a table (base table, view, or joined table) can JOIN to itself in a self-

join.

Web Resources for Additional Studies

http://www.1keydata.com/

http://www.w3schools.com/

3 CS 220 Database Systems – Sub Queries

Lab# 8 EXCERCISE (15 marks)

Problem 8.1 : [2]

Display the all customer’s number, name and event status and its estimated cost.

Problem 8.2 : [3]

Display the list of events and assigned to which employee.

Problem 8.3 : [5]

Display events requested which are planed in year 2018 and has Football stadium facility.

select p.eventno from eventrequest p join facility f on f.facno=p.facno where year(datereq)= 2018 and f.facname='football stadium';

Problem 8.4 : [5]

Display the list Eventplan which has rate greater or equal to 15.

Schema Diagram to be used for Lab Exercises List of Tables used for Lab Exercises Customer custno custname address Internal contact phone city state zip C100 Faisal Naseer Box 352200 Yes Mary Manager 6857100 Boulder CO 80309 C101 Mubashir Nazir Box 352400 Yes Sally Supervisor 5431700 Boulder CO 80309 C103 Babar Nawaz Box 352020 Yes Bill Baseball 5431234 Boulder CO 80309 C104 Waheed Iqbal Box 351200 Yes Sue Softball 5434321 Boulder CO 80309 C105 Hashir Shahid 123 AnyStreet No Coach Bob 4441234 Louisville CO 80027 Employee

empno empname department email phone E100 Chuck Coordinator Administration [email protected] 3- E101 Mary Manager Football [email protected] 5- E102 Sally Supervisor Planning [email protected] 3- E103 Alan Administrator Administration [email protected] 3- Facility facno facname F100 Football stadium F101 Basketball arena F102 Baseball field F103 Recreation room Location locno facno locname L100 F100 Locker room L101 F100 Plaza L102 F100 Vehicle gate L103 F101 Locker room L104 F100 Ticket Booth L105 F101 Gate L106 F100 Pedestrian gate ResourceTbl resno resname rate R100 attendant $10. R101 police $15. R102 usher $10. R103 nurse $20. R104 janitor $15. R105 food service $10. EventRequest eventno dateheld datereq facno custno dateauth status estcost estaudience budno E100 25-Oct-2018 06-Jun-2018 F100 C100 08-Jun-2018 Approved $5,000.00 80000 B E101 26-Oct-2018 28-Jul-2018 F100 C100 Pending $5,000.00 80000 B E102 14-Sep-2018 28-Jul-2018 F100 C100 31-Jul-2018 Approved $5,000.00 80000 B E103 21-Sep-2018 28-Jul-2018 F100 C100 01-Aug-2018 Approved $5,000.00 80000 B E104 03-Dec-2018 28-Jul-2018 F101 C101 31-Jul-2018 Approved $2,000.00 12000 B E105 05-Dec-2018 28-Jul-2018 F101 C101 01-Aug-2018 Approved $2,000.00 10000 B E106 12-Dec-2018 28-Jul-2018 F101 C101 31-Jul-2018 Approved $2,000.00 10000 B E107 23-Nov-2018 28-Jul-2018 F100 C105 31-Jul-2018 Denied $10,000.00 5000

EventPlanLine

EventPlan

• P100 1 8:00 17:00 2 L100 R PlanNo LineNo TimeStart TimeEnd NumberFld LocNo ResNo
• P100 2 12:00 17:00 2 L101 R
• P100 3 7:00 16:30 1 L102 R
• P100 4 18:00 22:00 2 L100 R
• P101 1 18:00 20:00 2 L103 R
• P101 2 18:30 19:00 4 L105 R
• P101 3 19:00 20:00 2 L103 R
• P102 1 18:00 19:00 2 L103 R
• P102 2 18:00 21:00 4 L105 R
• P102 3 19:00 22:00 2 L103 R
• P103 1 18:00 21:00 2 L103 R
• P103 2 18:00 21:00 4 L105 R
• P103 3 19:00 22:00 2 L103 R
• P104 1 18:00 22:00 4 L101 R
• P104 2 18:00 22:00 4 L100 R
• P105 1 18:00 22:00 4 L101 R
• P105 2 18:00 22:00 4 L100 R
• P199 1 8:00 12:00 1 L100 R
• P349 1 12:00 15:30 1 L103 R
• P85 1 9:00 17:00 5 L100 R
• P85 2 8:00 17:00 2 L102 R
• P85 3 10:00 15:00 3 L104 R
• P95 1 8:00 17:00 4 L100 R
• P95 2 9:00 17:00 4 L102 R
• P95 3 10:00 15:00 4 L106 R
• P95 4 13:00 17:00 2 L100 R
• P95 5 13:00 17:00 2 L101 R
• P100 E100 25-Oct-2018 Standard operation Operation E planno eventno workdate notes activity empno
• P101 E104 03-Dec-2018 Watch for gate crashers Operation E
• P102 E105 05-Dec-2018 Standard operation Operation E
• P104 E101 26-Oct-2018 Standard cleanup Cleanup E P103 E106 12-Dec-2018 Watch for seat switching Operation
• P105 E100 25-Oct-2018 Light cleanup Cleanup E
• P199 E102 10-Dec-2018 Standard operation Operation E
• P299 E101 26-Oct-2018 Operation E
• P349 E106 12-Dec-2018 Cleanup E
• P85 E100 25-Oct-2018 Standard operation Setup E
• P95 E101 26-Oct-2018 Extra security Setup E