12.Find the active vendors. Hint: Find the ids of the vendors that have active i

Question

12.Find the active vendors. Hint: Find the ids of the vendors that have active invoices i.e. who exist in table vendors. Each vendor should be reported once.

13. Using the table VENDORS find vendor contact first and last name for those records whose Vendor-Contact_First_name starts with ‘C’. Report the results:

1.In ascending alphabetical order of contact first name and then ascending alphabetical order of contact last name

2.In ascending alphabetical order of contact first name and then descending alphabetical order of contact last name

3.In ascending alphabetical order of contact last name and then ascending alphabetical order of contact first name

   Compare the results of a and b and the results of a and c.

14. Report the first names of all customers and their fax. If their fax is empty then report 9999999999. Hint: Use NVL function

15.Report the First name and last name of customers, however you should only report the first letter of the first name and the entire last name. Example: Instead of: “Bob Squarepants” You should report “B Squarepants” Hint: Use the SUBSTR function

16. Report the First name and last name of customers, however you should only report the first letter of the first name accompanied by a dot and the entire last name. Example: Instead of: “Bob Squarepants” You should report “B. Squarepants” Hint: Use the CONCAT function

Explanation / Answer

--12.Find the active vendors. Hint: Find the ids of the vendors that have active invoices i.e.
--who exist in table vendors. Each vendor should be reported once.
SELECT vendor_Id FROM invoices WHERE vendor_Id IN (SELECT vendor_Id FROM vendors);

--13. Using the table VENDORS find vendor contact first and last name for those records
-- whose Vendor-Contact_First_name starts with ‘C’. Report the results:
SELECT Contact_First_name,Contact_Last_name FROM VENDORS WHERE Contact_First_name LIKE 'C%';

--1.In ascending alphabetical order of contact first name and then ascending alphabetical order of contact last name
SELECT Contact_First_name,Contact_Last_name FROM VENDORS WHERE Contact_First_name LIKE 'C%'
ORDER BY Contact_First_name ASC,Contact_Last_name ASC;

--2.In ascending alphabetical order of contact first name and then descending alphabetical order of contact last name
SELECT Contact_First_name,Contact_Last_name FROM VENDORS WHERE Contact_First_name LIKE 'C%'
ORDER BY Contact_First_name ASC,Contact_Last_name DESC;

--3.In ascending alphabetical order of contact last name and then ascending alphabetical order of contact first name
SELECT Contact_First_name,Contact_Last_name FROM VENDORS WHERE Contact_First_name LIKE 'C%'
ORDER BY Contact_Last_name ASC,Contact_First_name ASC;

--14. Report the first names of all customers and their fax.
--If their fax is empty then report 9999999999. Hint: Use NVL function
SELECT first_name,ISNULL(fax,9999999999) AS fax FROM customers;

--15.Report the First name and last name of customers,
-- however you should only report the first letter of the first name
-- and the entire last name.
--Example: Instead of: “Bob Squarepants” You should report “B Squarepants” Hint: Use the SUBSTR function
SELECT CONCAT(SUBSTR(first_name,1,1),' ',Last_name) FROM customers;

--16. Report the First name and last name of customers,
--however you should only report the first letter
--of the first name accompanied by a dot and the entire last name. Example:
--Instead of: “Bob Squarepants” You should report “B. Squarepants” Hint: Use the CONCAT function
SELECT CONCAT(SUBSTR(first_name,1,1),'. ',Last_name) FROM customers;

Note: All column names and table names has been assumed Please change names as per DB if required

Related Questions

Navigate

• Browse (All)
• Browse 1
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.