Please help me with the following Queries: FOR QUERIES 1-4 : - What each query i
ID: 3918794 • Letter: P
Question
Please help me with the following Queries:
FOR QUERIES 1-4:
- 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, FROM, and ORDER BY
- Include a description of how the tables are being joined together.
QUERY 1
SELECT vendor_name, invoice_number, invoice_total
FROM vendors LEFT JOIN invoices
ON vendors,vendor_id = invoices.vendor_id
ORDER BY vendor_name
QUERY 2
SELECT deparmtnet_name, d.department_number, last_name
FROM departments d
LEFT JOIN employees e
ON d.department_number =e.department_number
ORDERS BY department_name
QUERY 3
SELECT department_name, last_name, project_number
FROM departments d
LEFT JOIN employees e
ON d.department_number =e.department_number
LEFT JOIN projects p
ON e.employee-id = p. employee_id
ORDER BY department_name, last_name
QUERY 4
SELECT department_name, last_name, project_name
FROM departments d
JOIN employees e
ON d. department_number = e.department_number
LEFT JOIN projects p
ON e.employees_id = p.employee_id
ORDER BY department_name, last_name
FOR QUERIES 5-7
- 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, FROM, WHERE, and ORDER BY
- Include a description of how the tables are being unioned together.
QUERY 5
SELECT `Active` AS source, invoice_number, invoice_date, invoice_total
FROM paid_invoices
WHERE invoice_date>= `2014-06-01’
UNION
SELECT `Paid’ AS source, invoice_number, invoice_date, invoice_total
FROM paid_invoices
WHERE invoice_date>= `2014-06-01’
ORDER BY invoice_total DESC
QUERY 6
SELECT `Active` AS source, invoice_number, invoice_date, invoice_total
FROM paid_invoices
WHERE invoice_total – payment_total – credit_total> 0
UNION
SELECT ‘Paid’ AS source, invoice_number, invoice_date, invoice_total
FROM invoices
WHERE invoice_total – payment_total – credit_total<= 0
ORDER BY invoice_total DESC
QUERY 7
Select invoice_number, vendor_name, ‘33% Payment’ AS payment_type, invoice_total AS total, invoice_total * 0.333 AS payment
FROMinvoivces JOIN vendors
ON invoices.vendor_id = vendors.vendor_id
WHERE invoice_total> 10000
UNION
Select invoice_number, vendor_name, ‘50% Payment’ AS payment_type, invoice_total AS total,invoice_total * 0.5 AS payment
FROMinvoivces JOIN vendors
ON invoices.vendor_id=vendors.vendor_id
WHERE invoice_total BETWEEN 500 AND 10000
UNION
Select invoice_number, vendor_name, ‘Full Payment’ AS payment_type, invoice_total AS total,invoice_total AS payment
FROM invoivces JOIN vendors
ON invoices.vendor_id =vendors.vendor_id
WHERE invoice_total< 500
ORDER BY payment_type, vendor_name, invoice_number
Explanation / Answer
Answers :
---------
1. Keywords:
1. SELECT - This keyword is used to retrieve data from one or more tables or views.
Syntax : SELECT <select-list> FROM <table-name>
(SELECT and FROM are keywords)
<select-list> may has '*' (wild card), columns, pseudo columns, Literals, Expressions, Functions.
1. A Pseudo column is logical column which is attached to a table by Oracle.
2. A Literal is a constant value it can be directly represented in a SQL statement.
3. Functions are used to manipulate data stored in tables.
4. An Expression is the combination of values, columns, pseduocloumns, functions and operators.
2. FROM - Represents from table(s)/view(s).
3. ORDER BY - When a query is executed the rows will be displayed in the default order determined by Oracle.
Using ORDER BY clause we can display the rows in a different order using the values of one or more columns.
ORDER BY [asc] or [desc]
Order by defalut is ascending (asc).
4. JOINS - Oracle performs a join operation whenever we specify two or more table names in the FROM clause of a SELECT statement.
To perform the join of two tables we must have atleast one common column with compatible datatypes and similar meaning for the common column values.
The LEFT JOIN keyword returns all records from the left table (table1), and the matched records from the right table (table2). The result is NULL from the right side, if there is no match.
QUERY 1
SELECT vendor_name, invoice_number, invoice_total
FROM vendors LEFT JOIN invoices
ON vendors,vendor_id = invoices.vendor_id
ORDER BY vendor_name
Answer:
-----
Will get the results from Vendors and Invoices tables only when Vendors.primaryKey(id) = Invoices.foreignKey(vendor_id) matches
Ex:
Vendors
id | vendor_name
---|------------
1 | John
2 | Smith
3 | Allen
Invoies
id | invoice_number | inovice_total | vendor_id(foreignKey)
---|-----------------|---------------|----------------------
1 | 100 | 1000 | 2
2 | 200 | 2000 | 1
3 | 300 | 3000 | 3
Result : (ORDER BY vendor_name -- by default asc - ascending.
vendor_name | invoice_number | invoice_total
------------|----------------|-------------
Allen | 300 | 3000
John | 200 | 2000
Smith | 100 | 1000
=====================================================================================================================================================================================
QUERY 2
SELECT deparmtnet_name, d.department_number, last_name
FROM departments d
LEFT JOIN employees e
ON d.department_number =e.department_number
ORDERS BY department_name
Answer:
------
Here we are joining Two tables namely 'deparments' and 'employees'
departments table has primaryKey - department_number
employees table has primaryKey - employee_id
employees table has foreignKey - department_number.
When department_number(pk - primaryKey) of 'departments' table matches with the department_number(fk - foreignKey) of 'employees' table, then will get result of these columns
department_name('departments' table), depatment_number('departments' table), last_name(''departments' table) with ORDER BY department_name is ascending order.
======================================================================================================================================================================================
QUERY 3
SELECT department_name, last_name, project_number
FROM departments d
LEFT JOIN employees e
ON d.department_number =e.department_number
LEFT JOIN projects p
ON e.employee-id = p. employee_id
ORDER BY department_name, last_name
Answer:
-------
Here we are joinging 3 tables namely 'deparments', 'employees' and 'projects'
departments table has primaryKey - department_number
employees table has primaryKey - employee_id
employees table has foreignKey - department_number.
projects table has primaryKey - projects_id
projects table has foreignKey - employee_id.
When department_number(pk - primaryKey) of 'departments' table matches with the department_number(fk - foreignKey) of 'employees' table
and employee_id(pk) of 'employees' table matches with employee_id(fk) of 'projects' table,
then will get result of these columns
department_name('departments' table), last_name('employees' table), project_number('projects' table) with ORDER BY department_name, last_name is ascending order.
======================================================================================================================================================================================
QUERY 4
SELECT department_name, last_name, project_name
FROM departments d
JOIN employees e
ON d. department_number = e.department_number
LEFT JOIN projects p
ON e.employees_id = p.employee_id
ORDER BY department_name, last_name
Answer:
-------
Here we are joinging 3 tables namely 'deparments', 'employees' and 'projects'
departments table has primaryKey - department_number
employees table has primaryKey - employee_id
employees table has foreignKey - department_number.
projects table has primaryKey - projects_id
projects table has foreignKey - employee_id.
When department_number(pk - primaryKey) of 'departments' table matches with the department_number(fk - foreignKey) of 'employees' table
and employee_id(pk) of 'employees' table matches with employee_id(fk) of 'projects' table,
then will get result of these columns
department_name('departments' table), last_name('employees' table), project_name('projects' table) with ORDER BY department_name, last_name is ascending order.