SQL HELP from these tables A. Write an outer join using at least 2 tables and sp

Question

SQL HELP from these tables

A. Write an outer join using at least 2 tables and specify a restriction. Make sure the data supports this. You may have to manipulate existing data.  

B. Use NVL2, SOUNDEX, or CASE in a query.

C. Write a sub-query with 2 inner queries.  

C ID |$ C LASTS: C FIRST )? C MI C STATES. C ZIP |?C DPHONE C EPHONE 3.C USERID |?C PASSWORD )? C BIRTHDATE: C ADDRESS 10-DEC-67 9815 Circle Dr Tallahassee FL 14-AUG-58 172 Alto Park 12-APR-60 C_CITY 1 Graham Neal 2 Sanchez Myra 3 Smith Lisa 4 Phelp Paul(null) 18-JAN-81 994 Kirkman Rd. Northpoint NY 5 Lewis Sheila A 6 James Thomas E 32308 9045551897 904558599 grahamn barbiecar 42180 4185551791 4185556643 sanchezmt qwert5 51875 3075557841 3075559852 smithlm joshua5 11795 4825554788 4825558219 phelpp hold98er 37812 3525554972 352555181l lewissa 125pass 87195 7615553485 7615553319 jamest nokstell Seattle WA 850 East Main Santa AnaCA 30-AUG-78 01-JUN-73 195 College Blvd. Newton 348 Rice Lane GA Radcliff WY

Explanation / Answer

Below are the queries written based on the tables shown in the question

The above two tables names are considered as

Based on these table names the sql queries are created

Question 1

A. Write an outer join using at least 2 tables and specify a restriction. Make sure the data supports this. You may have to manipulate existing data.

SQL CODE

Select

C.CLNAME As CustomerName,

C.CLAddress As CustomerAddress,

O.O_Date As OrderDate,

O.O_METHPMT AS PaymentMethod

FROM CUSTOMERS as C

LEFT OUTER JOIN orders AS O

ON O.C_ID = C.CLLD

In the above code i'm making the left out join with Customers and Orders table by comparing with the Customer Id, so that the data returns as it preserves the unmatched rows from the left table (Customers), joining them with a NULL row in the shape of the right table (Orders)

Question 2

B. Use NVL2, SOUNDEX, or CASE in a query.

SQL CODE

In this i have used CASE, suppose let assume that in the table orders the column O_METHPMT stores only the characters e.g; CC for Credit Card payment, so while writing the select query we can use CASE WHEN to get the abbreviated form using the short characters

Select
C.CLNAME AS CustomerName,
O.O_Id As OrderId,
(CASE O.O_METHPMT
WHEN 'CC' THEN 'Credit Card'
WHEN 'C' THEN 'Cash'  
WHEN 'N' THEN 'Online Netbanking'
ELSE 'None'
END) As PaymentMethod
FROM customers C
INNER JOIN orders As O ON O.C_ID = C.CLID

In the above code it returns the payment method by checking the word character in column O_METHPMT

Question 3

C. Write a sub-query with 2 inner queries.

SQL CODE

If a requirement ask us to get only the orders which made recently then we can use of sub query to get the result

Select
O.O_Id As OrderId,
(CASE O.O_METHPMT
WHEN 'CC' THEN 'Credit Card'
WHEN 'C' THEN 'Cash'  
WHEN 'N' THEN 'Online Netbanking'
ELSE 'None'
END) As PaymentMethod,
O.O_DATE AS OrderedDate
FROM orders As O
WHERE O.O_DATE = (SELECT MAX(O_DATE) FROM orders)

The highlighted text in bold shows the sub query that used to get the maxmium of date which means the order which made recently

Related Questions

Navigate

• Browse (All)
• Browse S
• Subjects

---

---

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