Concept explainers
Consider the below two tables for all the “UNION”, “INTERSECT”, and “MINUS” operations.
Consider two tables:
Table creation:
CREATE TABLE EMPLOYEE(NAME VARCHAR(15));
CREATE TABLE EMPLOYEE_1(NAME VARCHAR(15));
Inserting values:
INSERT INTO EMPLOYEE VALUES("Alice Cordoza");
INSERT INTO EMPLOYEE VALUES("John Cretchakoy");
INSERT INTO EMPLOYEE VALUES("Anne McDonald");
INSERT INTO EMPLOYEE_1 VALUES("John Cretchakoy");
INSERT INTO EMPLOYEE_1 VALUES("Mary Chen");
Explanation of Solution
INTERSECTset operator:
The INTERSECT set operator is used to combine the output of two or more than two queries and produce a result. The produced result contains the values (rows) that are common in both the tables.
Syntax: QUERYINTERSECTQUERY;
INTERSECT query:
SELECT * FROM EMPLOYEE INTERSECT SELECT * FROM EMPLOYEE_1;
Explanation of Solution
MINUS set operator:
The MINUS set operator is used to combine the output of two or more than two queries and produce a result. The produced result contains the values (rows) that appear in the first table but not in the second table. The word “EXCEPT” can also be used in the place of “MINUS”.
Syntax: QUERYMINUSQUERY;
MINUS query 1:
SELECT * FROM EMPLOYEE MINUS SELECT ...
Explanation of Solution
Restrictions on the table:
In order to perform any of the operations like “UNION”, “INTERSECT”, and “MINUS” operations, the tables must be union-compatible...
Want to see the full answer?
Check out a sample textbook solutionChapter 5 Solutions
A Guide to SQL
- What are the advantages of using table-valued parameters in SQL functions?arrow_forwardI keep getting errors for this code in SQL but i cant seem to fix it, assuming the names for the files are correct is there any obvious errors?arrow_forwardWhat are the performance implications of using the CROSS APPLY operator with user-defined functions in SQL?arrow_forward
- You have the following tables: MANAGERS (MANAGER_ID, LAST_NAME, FIRST_NAME, DEPARTMENT) ACCOUNTS (ACCOUNT_NUMBER, MANAGER_ID, CUST_ID, BALANCE) CUSTOMERS (CUST_ID, LAST_NAME, FIRST_NAME) Write a SQL statement that lists account number, balance, MANAGER’s last name, CUSTOMER ID, and CUSTOMER’s last name for every account in the ACCOUNTS table.arrow_forwardWrite PL/SQL or T-SQL procedures to accomplish the following tasks: A: obtain the name and credit limit of the customer whose number currently is stored in I_CUSTOMER_NUM. Place these valuse in the variables I_CUSTOMER_NAME and I_CREDIT_LIMIT, respectively. Output the content of I_CUSTOMER_NAME and I_CREDIT_LIMIT. B: obtain the order date, customer number and name for the order whose number currently is stored in I_ORDER_NUM. place these values in the variables I_ORDER_DATE, I_CUSTOMER_NUM and I_CUSTOMER_NAME , respectively. output the contents of I_ORDER_DATE, I_CUSTOMER_NUM , and I_CUSTOMER_NAME. C: add a row to the ORDERS table. D: change the date of the whole number is stored in I_ORDER_DATE to the date currently found in I_ORDER_DATE. E: delete the order whose number is stored in I_ORDER_NUM.arrow_forwardHow do I calculate the min, max, avg, and total hospital bed count from a Hospital Table for the whole state, by rural areas, and urban areas in SQL Python?arrow_forward
- What is a PL/SQL function? Write the general syntax of it.arrow_forwardUsing the Online SQL Editor, answer the following questions with screenshots. Link: https://www.programiz.com/sql/online-compiler/ Question 1: Show (in one table) customer id and first name from Customers table together with shipping id and status from Shippings table while assigning them, within the SQL code, the aliases CT and ST, respectively. *CT for Customers table and corresponding columns*ST for Shippings table and corresponding columns Question 2: Show customer id, item, and amount from Orders table together with the last name and age from Customers table whose age is 25 and above. Question 3: Create a new table named Suppliers, with the following columns: Supplier id, first name, last name, age, and country. Limit first name and last name to 40 characters, country to 15 characters, and age to 3 characters.arrow_forwardWhat is the difference between scalar function and vector function in SQL DATABASE? Note: Question is based on SQL DATABASE.arrow_forward
- Write any five PL/SQL programs.arrow_forwardWhat role does the CHOOSE function play in simplifying conditional logic in SQL?arrow_forwardFill the blanks with the suitable PL/SQL statements that correct each of the errors that appear in the given PL/SQL program. (display as -----------) Fill the blanks with the suitable PL/SQL statements that correct each of the errors that appear in the given PL/SQL program. DECLARE CURSOR cursor1 IS SELECT * FROM Students; line cursor1 %type; --------------- BEGIN OPEN cursor1; LOOP SELECT cursor1 inside line; --------------- DBMS_OUTPUT.PUT_LINE('first name= '|| line.fname || 'last name= '||line.lname); EXIT WHEN Cursor %FOUND; ------------------------ END LOOP; CLOSE cursor1; END;arrow_forward
- A Guide to SQLComputer ScienceISBN:9781111527273Author:Philip J. PrattPublisher:Course Technology Ptr