Assessment item 5 Create and Query Databases using SQL Value: 15% Due date: 20-M

Question

Assessment item 5

Create and Query Databases using SQL

Value: 15%

Due date: 20-May-2017

Return date: 13-Jun-2017

Submission method options

Alternative submission method

Task

Write SQL statements to answer the following questions from the ‘assignment5’

Database: Answer all questions

List all the record/s in the vendor table that do not have a value for the attribute V_STATE.

Find the customers whose balance is greater than 250 but less than 400.

Create a query based on invoice table to show a 15% increase on tax in a new column.

Show the first and last names of the customers for whom we have issued invoices.

Show the CUS_CODE of the customers for whom we DO NOT have invoices.

Show the names of the customers for whom we DO NOT have invoices

Find out how many times a customer generated an invoice – make sure the counted column heading reads “Invoices_generated” .

List the names of the customers and how many times a customer generated an invoice – make sure the counted column heading reads “N_Of_Invoices” .

Find the customers whose last name is either Dunne, Orlando or Brown and live in area 615, sort in alphabetical order by the CUS_LNAME,.

Add a new attribute called ‘email_address’ to ‘emp’ table and use data type and length ‘VARCHAR-20’.

Add a new row/record to the `customer` table to include your actual student ID in the CUS_CODE, your lastname as the CUS_LNAME` and your firstname as CUS_FNAME. Add any other details in the other fields.

Delete all the vendors in the FL state.

Explanation / Answer

Since you have not given the schema of the table, I don't know the column names. You just need ot replace the column names with my queries.

select * from vendor
where V_STATE=null

select customers
from customer_table
where balance > 250 and balance < 400

select CUS_FNAME+" "+CUS_LNAME
from customers
where invoices=issue

select CUS_CODE
from customers
where invoices=null

select customer, count(invoice) as Invoices_generated
from customers
group by customer

select CUS_FNAME+" "+CUS_LNAME , count(invoice) as N_Of_Invoices
from customers
group by customer

select customerid
from customers
where CUS_LNAME in ('Dunne','Orlando','Brown') and area=615
order by CUS_LNAME
form

Related Questions

Navigate

• Browse (All)
• Browse A
• Subjects

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