Create Queries in SQL Statements A) Join Dealership and Employee tables and disp
ID: 3741778 • Letter: C
Question
Create Queries in SQL Statements
A) Join Dealership and Employee tables and display all columns. You must use INNER JOIN and ON syntax for this query.
B) Join Dealership and Employee tables and display all columns. Include dealerships with no employees. You must use LEFT JOIN and ON syntax for this query.
C) Join Dealership and Employee tables and display all columns. Include employees with no dealerships. You must use RIGHT JOIN and ON syntax for this query.
D) Join Dealership and Employee tables and display all columns. Include dealerships with no employees and employees with no dealerships. Your query needs to return data that is the result of a FULL OUTER JOIN, but note that there is no such syntax in Access, so you need to come up with an Access SQL work around.
E) Join Customer, Relationship, and Dealership tables and display all columns. You must use INNER JOIN and ON syntax for this query.
***Use DISTINCT when necessary, but do not specify DISTINCT when it’s not needed.
Relationships Invoice Purchaser Employee Vehicle Customer Dealership 9 dstate RelationshipExplanation / Answer
A)
Here, you are told to do Inner Join on the Tables Dealership and Employee.
To display all the columns use SELECT * keyword. And use INNER JOIN keyword to perform inner join. Use the primary keys of both the table to create the Join on.
SELECT *
FROM Dealership
INNER JOIN Employee ON Dealership.dcode = Employee.ecode;
B)
Here, you are told to do Left Join on the Tables Dealership and Employee.
To display all the columns use SELECT * keyword. And use LEFT JOIN keyword to perform left join. Use the primary keys of both the table to create the Join on.
SELECT *
FROM Dealership
LEFT JOIN Employee ON Dealership.dcode = Employee.ecode;
C)
Here, you are told to do Right Join on the Tables Dealership and Employee.
To display all the columns use SELECT * keyword. And use RIGHT JOIN keyword to perform right join. Use the primary keys of both the table to create the Join on.
SELECT *
FROM Dealership
RIGHT JOIN Employee ON Dealership.dcode = Employee.code;
D)
Here, you are told to do Full outer Join on the Tables Dealership and Employee.
To display all the columns use SELECT * keyword. And use FULL OUTER JOIN keyword to perform full outer join. Use the primary keys of both the table to create the Join on.
SELECT *
FROM Dealership
FULL OUTER JOIN Employee ON Dealership.dcode = Employee.ecode;
E)
Now, for doing an inner join on 3 tables, you need to use a nested sub-query. First Create an Inner Join on the Tables Dealership and Relationship, and then do an inner join on the result of that query with the table Customer. Since, Relationship table connect sthe tabled Dealership and Customer.
SELECT *
FROM((Relationship INNER JOIN on Dealership.dcode = Realtionship.rcode )
INNER JOIN Customer ON Relationship.rcid = Customer.cid );