Please help me double check my answers: For QUERIES 1-6 : · What each query is r
ID: 3918593 • Letter: P
Question
Please help me double check my answers:
For QUERIES 1-6 :
· What each query is requesting from the database.
· Include descriptions for what ALL of the following SQL keywords are doing in each query they are present in: SELECT, DISTINCT, FROM, WHERE, and ORDER BY
· Include a description of how the two tables are being joined together.
QUERY 1
SELECT invoice_number, vendor_name
FROM vendors INNER JOIN invoices
ON vendors. Vendor_id = invoices.vendor _id
ORDER BY invoice_number
QUERY 2
SELECT invoice_number, vendor_name, invoice_due_date, invoice_total - payment_total – credit_total AS balance_due
FROM vendors v JOIN invoices i ON v. vendor_id = i.vendor_id
WHERE invoice_total – payment_total – credit_total > 0
ORDER BY invoice_due_due DESC
QUERY 3
SELECT invoice_number, line-item_amount, line_item_description
FROM invoices JOIN invoice_line_items line_items ON invoices.invoice_id = line_items.invoice_id
QUERY 4
SELECT vendor_name, customer_name, custiner_furst_name, vendor_state AS state , vendor_city AS city
FROM vendors v
JOIN om.customers c
ON v.vendor_zip-code = c. customer_zip
ORDER BY state, city
QUERY 5
SELECT customer_first_name, customer_last_name
FROM customer c JOIN employees e
ON c. customer_first_name = e. first_name
AND c. customer_last_name = e.last_name
QUERY 6
SELECT DISTINCT v1.vendor_name, v1.vendor_city, v1.vendor_state
FROM vendors v1 JOIN vendors v2
ON v1. Vendor_city = v2. Vendor_city AND
v1.vendor_state = v2vendor_state AND
v. vendor_name < > v2.vendor_name
ORDER BY v1.vendor_state, v1.vendor_city
Explanation / Answer
SELECT - used to extract the rows specifie after it upto the keyword...
FROM - used to specify which table you want to use to extract those rows you specified.
DISTINCT - used to remove repeated rows.
WHERE - used to filter the table with condition you specify after it.
ORDER BY - sort the table rows according to the column[s] you specify after it.
QUERY 1
[SELECT invoice_number, vendor_name
FROM vendors]
Extracting invoice_number and vendor_name from table vendors
[INNER JOIN invoices
ON vendors. Vendor_id = invoices.vendor _id]
joining two tables invoices and vendors on one condition that Vendor_id in both tables is same
[ORDER BY invoice_number]
This part will sort the resultant table rows according to the column_name you specify after ORDER BY, therefore in this case your table rows will be sorted according to the invoice_number (as this column indicates that it's number field then it will be sorted in accesding order)
Query 2
[SELECT invoice_number, vendor_name, invoice_due_date, invoice_total - payment_total – credit_total AS balance_due FROM vendors v]
Extracting invoice_number, vendor_name, invoice_due_date, {invoice_total - payment_total - credit_total AS balance_due} from vendors table and you're using alias "v" for vendors (vendors.vendor_name is same as v.vendor_name), we are just defining a variable for vendors so that you can write less code.
1. {invoice_total - payment_total - credit_total AS balance_due} - It's a Arithmetic expression that invoice_total (subract) payment_total(subtract) - credit_total and the result of this expression will be a single number and to display that number we are creating an column with name balance_due so resultant value will be display in this column.
[JOIN invoices i ON v. vendor_id = i.vendor_id]
Joining two tables vendors and invoices and displaying those rows where vendor_id in both tables is same. and INNER JOIN and JOIN works same because INNER keyword is optional as all joins are considered to be INNER joins.
[WHERE invoice_total – payment_total – credit_total > 0]
This is the where clause, it will filter the rows according to the values in the column balance_due as we have used that column to store the above expression value now the filter will be to display those values which are greater than 0 in the balance_due column.
[ORDER BY invoice_due_due DESC]
as you know the use of ORDER BY, it will sort the table according to the column you specify after it and DESC specify that sort should be in Decsending order.
Query 3
[SELECT invoice_number, line-item_amount, line_item_description FROM invoices]
Extracting the columns invoice_number, line-item_amount, line_item_description from the table invoices.
[JOIN invoice_line_items line_items ON invoices.invoice_id = line_items.invoice_id]
I assume that you can figure out the result of this part of this query
still joining three tables here and displaying those rows which satisfies the expression after ON keyword.
Query 4
[SELECT vendor_name, customer_name, custiner_furst_name, vendor_state AS state , vendor_city AS city FROM vendors v]
Extracting vendor_name, customer_name, custiner_furst__name, vendor_state (and column name will be change to State), vendor_city (column name will be city) from table vendors with the alias "v".
[JOIN om.customers c ON v.vendor_zip-code = c. customer_zip]
Joining two tables and customers and vendors and (om.customers c means if and only if om is the database name then this query will run else it will give an error) because to access tables you can also use <databaseName>.<TableName> <alias>, joining on the condition specified.
[ORDER BY state, city]
Table rows will be sorted according to the pair of state and city.
Query 5
[SELECT customer_first_name, customer_last_name FROM customer c]
Extracting customer_first_name, customer_last_name from table customer with the alias "c".
[JOIN employees e ON c. customer_first_name = e. first_name AND c. customer_last_name = e.last_name]
Joining two tables with two conditions and they both needs to be true to display the rows. and also here alias for customer table is used as "e".
Query 6
[SELECT DISTINCT v1.vendor_name, v1.vendor_city, v1.vendor_state FROM vendors v1]
Extracting the columns from the table vendors with the alias spicified "v1" and here DISTINCT will be used to remove the repeated rows.
[JOIN vendors v2 ON v1. Vendor_city = v2. Vendor_city AND v1.vendor_state = v2vendor_state AND v. vendor_name < > v2.vendor_name]
Joining the same table together with the different alias "v2" so you can create the clone of the same table with the conditions v1. Vendor_city = v2. Vendor_city AND v1.vendor_state = v2vendor_state AND
v. vendor_name < > v2.vendor_name - and there < > specifies the not equals sign.
[ORDER BY v1.vendor_state, v1.vendor_city]
Table rows will be sorted according to the pair of v1.vendor_state and v1.vendor_city.
How tables joined together
tables are joined together by the condition you specify which will check the column values in both tables and checks them whether they satisfies the condition or not. There are different kinds of JOINS: (INNER) JOIN, LEFT (OUTER) JOIN, RIGHT (OUTER) JOIN, FULL OUTER JOIN. The (keywords) is optional.
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.