ZuoTi.Pro
Question

Code in sql/oracle 8. What departments have no employees and what employees have no departments. Show the result of the question through ONLY one query/SELECT statement. Do not write two separate sele...

Code in sql/oracle

8. What departments have no employees and what employees have no departments. Show the result of the question through ONLY one query/SELECT statement. Do not write two separate select statements.

engineering   Computer-Science   
0 0
Add a comment Improve this question Transcribed image text
Next > < Previous
Sort answers by oldest

Homework Answers

Answer #1

To get results we can use full outer join. Full outer join. Full outer join gets all data from left table and matching data of right table and also remaining data of right table. Below is query

SELECT d.department_no,e.employee_id FROM Department d FULL OUTER JOIN employees e ON d.department_id=e.department_id WHERE e.employee_id IS NULL or d.department_id IS NULL;

--department id null provide employees having no dept.

-- employees id null provide department having no employee

0 0
Add a comment
Know the answer?
Add Answer to:
Code in sql/oracle 8. What departments have no employees and what employees have no departments. Show the result of the question through ONLY one query/SELECT statement. Do not write two separate sele...
Your Answer:

Post as a guest

Your Name:

What's your source?

Earn Coins

Coins can be redeemed for fabulous gifts.

Log In

Sign Up

Not the answer you're looking for? Ask your own homework help question. Our experts will answer your question WITHIN MINUTES for Free.
Similar Homework Help Questions

  • WRITE A SQL QUERY SQL SERVER Write a SELECT statement that returns one column from the...

    WRITE A SQL QUERY SQL SERVER Write a SELECT statement that returns one column from the Customers table named FullName that joins the LastName and FirstName columns. --       Format this column with the last name, a comma, a space, and the first name like this: --       Doe, John --       Sort the result set by last name in ascending sequence. --       Return only the contacts whose last name begins with a letter from M to Z. --  ...

  • BONUS QUESTION: Suppose you have two tables, EMPLOYEES with primary key employee_ID, and DEPARTMENTS with primary...

    BONUS QUESTION: Suppose you have two tables, EMPLOYEES with primary key employee_ID, and DEPARTMENTS with primary key department_ID. Assume each department may have many employees, but each employee is only assigned one department. What would be the correct SQL syntax for retrieving data from both tables? (HINT: You must find out what is the foreign key) A. SELECT employee_ID, employee_name, department_ID, department_location FROM Employees JOIN Departments ON Employees.employee_ID = Departments.employee_ID; B. SELECT employee_ID, employee_name, department_ID, department_location FROM Employees JOIN Departments...

  • Question 3. SQL (5 points). In addition to the lecture notes, you should also study by yourself t...

    Question 3. SQL (5 points). In addition to the lecture notes, you should also study by yourself the SQL Plus tutorial on Canvas (the Oracle section) and other resources for Oracle syntax and useful functions. The relational schema for the Academics database is as follows: DEPARTMENT(deptnum, descrip, instname, deptname, state, postcode) ACADEMIC(acnum, deptnum, famname, givename, initials, title) PAPER(panum, title) AUTHOR(panum*. acnum) FIELD(fieldnum, id, title) INTEREST(fieldnum* acnum*, descrip) Some notes on the Academics database An academic department belongs to one institution...

  • Oracle 12c: SQL   Joan Casteel ISBN: 978-1-305-25103-8 Chapter 12 Case Study: City Jail Make sure you...

    Oracle 12c: SQL   Joan Casteel ISBN: 978-1-305-25103-8 Chapter 12 Case Study: City Jail Make sure you have run the CityJail_8.sql script from Chapter 8. This script makes all database objects available for completing this case study. The city's Crime Analysis unit has submitted the following data requests. Provide the SQL statements using subqueries to satisfy the requests. Test the statements and show execution results. Use a sql sub-query statement to answer the following: (Please provide correct answer for questions 6...

  • What happens when you try to compile and run the following code? String query = "INSERT...

    What happens when you try to compile and run the following code? String query = "INSERT INTO Invoices (InvoiceDate InvoiceTotal) "              + "VALUES ('2/10/01', '443.55')"; Statement statement = connection.createStatement(); statement.executeUpdate(query); a. A SQLException is thrown at runtime because the executeUpdate method can’t accept a String object. b. A SQLException is thrown at runtime because the SQL statement is coded incorrectly. c. An error occurs at compile time because the SQL statement is coded incorrectly. d. This code compiles and...

  • For the questions below, write the SQL statement and also answer the question if requested. You n...

    Thumbs up for the Correct answer with all steps For the questions below, write the SQL statement and also answer the question if requested. You need to show the execution result of each SQL statement. 1. Create a sequence object (named with SalespersonIDSeq) which starts with 11, is increased by 1 with nocycle 2. Insert two new salesperson records below into Salesperson _T table. number generated using SalespersonIDSeq object For SalespersonID, use a sequence <SalespersonName: Brandon Young, SalespersonCity: Fort Wayne,...

  • (Using Oracle SQL) How do I find the primary key of this table? And then how...

    (Using Oracle SQL) How do I find the primary key of this table? And then how do I consequently drop the primary key from the table? Edit: I was already give this code and it did not work select constraint_name,constraint_type from user_constraint where table_name='CUSTOMER'; Q7 (10 Points) Use ONE SQL statement to find out the name of the primary key constraint defined on Customer table. (Your SQL should return something like SYS_C0021715, numbers on your account may differ.) Then use...

  • I am using Oracle SQL and am new to it. I have seven tables, one of...

    I am using Oracle SQL and am new to it. I have seven tables, one of them is a subtable of two of the others. I need to do the following queries: 1. List all Patients and what Bed they are assigned to 2. List all patients who had Treatments and what Treatment they received 3. List all patients who had tests and what Test they had 4. List the employees (doctors, nurses, etc.) who assisted each patient. 5. List...

  • Top of Form Q1 Which of the following is a valid aggregate function in SQL? Check...

    Top of Form Q1 Which of the following is a valid aggregate function in SQL? Check only those that apply. Question 1 options: AVG LEAST COUNT MIN MOST SUM Q2 Which of the following aggregate functions will only operate on a numeric value expression? Check only those that apply: Question 2 options: MIN MAX AVG COUNT SUM Q3 When applying DISTINCT to the value being calculated in an aggregate function like COUNT, what is the effect? SELECT COUNT(DISTINCT StreetName) FROM...

  • SQL: The schema name should be mxxws, where mxx is your MySQL user name. To refresh...

    SQL: The schema name should be mxxws, where mxx is your MySQL user name. To refresh your memory – here is the schema for that database: Create the rest of the contact management database by writing the statements that do each of the following items below. For each one: (1) execute them on your copy of the database, and (2) write the statements in the space: Statement to create the Contact table (one statement) Statement to create the Employee table...

ADVERTISEMENT
Free Homework Help App
Download From Google Play
Scan Your Homework
to Get Instant Free Answers
Need Online Homework Help?
Ask a Question
Get Answers For Free
Most questions answered within 3 hours.
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT