Join Related SQL Query Help
4. JOIN related SQL Query -
EMPLOYEE TABLE
EMP_ID |
FIRST_NAME |
LAST_NAME |
GENDER |
DEPARTMENT |
JOINING_DATE |
SALARY |
1001 |
ANDREW |
CENCINI |
MALE |
IT |
08-01-2014 |
300000 |
1002 |
STEVEN |
THROPE |
MALE |
HR |
10-02-2014 |
400000 |
1003 |
ANNE |
HELLUNG-LARSEN |
FEMALE |
FINANCE |
20-03-2014 |
500000 |
1004 |
DENE |
SENA |
FEMALE |
ACCOUNT |
30-04-2014 |
200000 |
ASSIGNMENT TABLE
ASSIGN_ID |
EMP_ID |
ASSIGNMENT_NAME |
ASSIGN_DATE |
DUE_DATE |
11 |
1001 |
MENAGEMENT |
01-02-2014 |
01-03-2014 |
22 |
1001 |
HR SYSTEM |
11-02-2014 |
11-04-2014 |
33 |
1002 |
MENAGEMENT |
01-03-2014 |
01-04-2014 |
44 |
1003 |
FINANCE |
01-03-2014 |
01-04-2014 |
55 |
1003 |
DBMS |
11-04-2014 |
11-05-2014 |
66 |
1004 |
DB |
10-04-2014 |
10-06-2014 |
1. get employee name, project name order by firstname from "EMPLOYEE" and "ASSIGNMENT" for those employee which have assigned project already.
SELECT FIRST_NAME, ASSIGNMENT_NAME FROM EMPLOYEE A INNER JOIN ASSIGNMENT B ON A.EMP_ID=B.ASSIGN_ID ORDER BY FIRST_NAME
2. order by first_name "EMPLOYEE" and "ASSIGNMENT" for all employee even they have not assigned assignment.
SELECT FIRST_NAME, ASSIGNMENT_NAME FROM EMPLOYEE A LEFT JOIN ASSIGNMENT B ON A.EMP_ID=B.ASSIGN_ID ORDER BY FIRST_NAME
3. get all assignment name even they have not matching any employeeid, in left table, order by firstname from employee and assignment.
SELECT FIRSTNAME, ASSIGNMENTNAME FROM EMPLOYEE A RIGHT OUTER JOIN ASSIGNMENT B ON A.EMP_ID=B.ASSIGN_ID ORDER BY FIRSTNAME
4. get complete record from both table, if no match found in any table then show null.
SELECT FIRSTNAME, ASSIGNMENT_NAME FROM EMPLOYEE A FULL OUTER JOIN ASSIGNMENT B ON A.EMP_ID=B.ASSIGN_ID ORDER BY FIRST_NAME
5. query to get the employee name who has not assigned any assignment, and display “no project assigned”
SELECT FIRST_NAME, ISNULL(ASSIGNMENT_NAME-‘NO ASSIGNMENT ASSIGNED’) AS ASSIGNMENT FROM EMPLOYEE A LEFT OUTER JOIN ASSIGNMENT B ON A.EMP_ID = B.ASSIGN_ID WHERE ASSIGNMENT_NAME IS NULL
Topics in database
- MYSQL Homework Help
- SQL Homework Help
- Advance SQL Homework Help
- SQL Functions Homework Help
- Big Data Homework Help
- Complex Query In Sql Help
- Basic SQL Query Help
- Attributes in Entity Homework Help
- String Related sql Query Help
- Join Related sql Query Help
- Small Tricky sql Query Help
- Authorization: SQL Recursion Help
- Database and data science techniques Help
- Database Languages Assignment Help
- Database Design Help
- Database System Architectures Design
- Entity Relationship Model Understanding
- Higher-Level Design: UML Diagram Help
- Implementation Of Atomicity And Durability
- Object-Based Databases Homework Help
- Oracle 10g/11g
- Parallel And Distributed Databases
- Query Optimization Technique
- Relational Databases Homework Help
- Serializability And Recoverability
- SQL Join
- SQL Queries And Updates
- XML And Relational Algebra Homework Help
- XML Queries And Transformations
- Data Mining
- Oracle Data warehouse
- Relational Model Online Help
- SQL And Advanced SQL Learning Help
Testimonials
Very affordable projects!! And that to submit before deadlines. Thanks for helping me in my database project and raising my grades. I have been able to secure good marks in my internal assessment only because of you. Read More
Urgenthomework helped me with finance homework problems and taught math portion of my course as well. Initially, I used a tutor that taught me math course I felt that as if I was not getting the help I needed. With the help of Urgenthomework, I got precisely where I was weak: