Great Deal! Get Instant $25 FREE in Account on First Order + 10% Cashback on Every Order Order Now

1) Write an SQL command that retrieves all the data from the CustDetails table. ____________________________________________________________________________...

1 answer below »

1) Write an SQL command that retrieves all the data from the CustDetails table.
____________________________________________________________________________
____________________________________________________________________________
2) Write an SQL command that retrieves only the first and last names from the CustDetails
table.
__________________________________________________________________________
____________________________________________________________________________
3) Write an SQL command that retrieves all the data from the CustDetails table sorting it by
the customer’s last name.
____________________________________________________________________________
____________________________________________________________________________
4) Write an SQL command that retrieves all data on only customers living in Eastway from the
CustDetails table.
____________________________________________________________________________
____________________________________________________________________________
5) Write an SQL command that retrieves all data on only the first four customers from the
CustDetails table.
____________________________________________________________________________
____________________________________________________________________________
6) Write an SQL command that retrieves all data on only customers with a last name beginning
with ‘S’ through ‘Z’ from the CustDetails table.
____________________________________________________________________________
____________________________________________________________________________
7) Write an SQL command that retrieves all data on only customers with the text ‘east’
anywhere in the suburb from the CustDetails table.
____________________________________________________________________________
____________________________________________________________________________
8) Write an SQL command that retrieves only unique last names from the CustDetails table.
_______________________________________ _____________________________________
____________________________________________________________________________
9) Write an SQL command that retrieves all the customer details from the CustDetails table if
their address details have been entered.
____________________________________________________________________________
____________________________________________________________________________
10) Write an SQL command that retrieves the total value of orders that have been delivered
from the OrderDetails table.
____________________________________________________________________________
____________________________________________________________________________
11) Write an SQL command that retrieves the count of orders in December and their total value
from the OrderDetails table.
____________________________________________________________________________
____________________________________________________________________________
12) Write an SQL command that retrieves all data for all orders sorted by customer number
from the OrderDetails table.
____________________________________________________________________________
____________________________________________________________________________
13) Write an SQL command that retrieves the Customers last name, first name, suburb and the
respective order values which have orders that have been delivered.
____________________________________________________________________________
____________________________________________________________________________
14) Write an SQL command that retrieves last name and first name of all customers and the
order numbers of orders they have placed.
____________________________________________________________________________
____________________________________________________________________________
15) Write an SQL command that uses a subquery to retrieve all customer data for customers
that live in a suburb other than where anyone with a last name of “Stevens” lives.
____________________________________________________________________________
____________________________________________________________________________
16) Write an SQL command that creates a database that has the following structure:
Table 2: Name: StaffDetails
Columns:
Column Name Data Type Key/Index nullable
StaffRef int Key No
LName nchar(25) No
FName nchar(25) No
Phone nchar(25) Yes
StartDate date No
____________________________________________________________________________
____________________________________________________________________________
17) Write an SQL command that inserts a record into the StaffDetails database for staff member
John Jacobs, reference 52 who started on 2/11/2009.
____________________________________________________________________________
____________________________________________________________________________
18) Write an SQL command that updates the OrderDetails table to show order reference 4 as
being delivered on 16/12/2009.
____________________________________________________________________________
____________________________________________________________________________
19) Write an SQL command that adds a column called “Email” to the StaffDetails table as we
also need email details (allow for 50 characters).
____________________________________________________________________________
____________________________________________________________________________
20) Write an SQL command that deletes order reference 9 as the customer has cancelled the
order.
____________________________________________________________________________
____________________________________________________________________________
21) Write an SQL command that creates a view called “Contacts” holding staff last name, first
name and phone number as we need a contact list.
____________________________________________________________________________
____________________________________________________________________________
22) Write an SQL command that deletes the view “Contacts

Answered Same DayNov 14, 2019Swinburne University of Technology

Solution

David answered on Nov 30 2019
55 Votes
T-SQL ASSIGNMENT
1. Write an SQL command that retrieves all the data from the CustDetails table
Ans: SELECT * FROM CustDetails
2. Write an SQL command that retrieves only the first and last names from the CustDetails table
Ans: SELECT FName,LName FROM CustDetails
3. Write an SQL command that retrieves all the data from the CustDetails table sorting it by the customer’s last name
Ans: SELECT * FROM CustDetails ORDER BY LName asc
4. Write an SQL command that retrieves all data on only customers living in Eastway from the CustDetails table.
Ans: SELECT * FROM CustDetails WHERE Subu
=”Eastway”
5. Write an SQL command that retrieves all data on only the first four customers from the CustDetails table.
Ans: SELECT * FROM CustDetails WHERE CustRef<5
6. Write an SQL command that retrieves all data on only customers with a last name beginning with ‘S’ through ‘Z’ from the CustDetails table.
Ans: SELECT * FROM CustDetails WHERE LName LIKE (S%,T%,U%,V%,W%,X%,Y%,Z%)
7. Write an SQL command that retrieves all data on only customers with the text ‘east’ anywhere in the subu
from the CustDetails table.
Ans: SELECT * FROM CustDetails WHERE Subu
LIKE (%east%)
8. Write an SQL command that retrieves only unique last names from the CustDetails table.
Ans: SELECT DISTINCT LName FROM CustDetails
9. Write an SQL command that retrieves all the customer details from the CustDetails table if their address details have been entered.
Ans: SELECT * FROM CustDetails WHERE Address IS NOT NULL
10. Write an SQL command that retrieves the total value of orders that have been delivered from the OrderDetails table.
Ans: SELECT SUM(Value) FROM OrderDetails
11. Write an SQL command that retrieves the count of orders in December and their total value from the OrderDetails table.
Ans: SELECT COUNT(OrderRef) FROM OrderDetails WHERE EXTRACT(MONTH FROM DateOrdered) = 12
SELECT SUM(Value) FROM OrderDetails WHERE EXTRACT(MONTH FROM DateOrdered) = 12
12. Write an SQL command that retrieves all data for all orders sorted by customer number from the OrderDetails...
SOLUTION.PDF

Answer To This Question Is Available To Download

Related Questions & Answers

More Questions »

Submit New Assignment

Copy and Paste Your Assignment Here