ASSIGNMENT: DAF, Lesson 3: • Scenario 3-1: Using the SELECT Command, page 59 (mo

Question

ASSIGNMENT: DAF, Lesson 3: • Scenario 3-1: Using the SELECT Command, page 59 (modified as follows): o 1. What command would you use to display all records from the Customers table? o 2. What command would you use to display the CustomerName and ContactName fields from the Customers table? o 3. What command would you use to display all records from the Customers table and have it sorted alphabetically by the CustomerName column? o 4. What command would you use to display all records from the Suppliers table that have the Country of Germany? o 5. What command would you use to display the Supplier ID, Supplier Name, Contact Name and Phone from Suppliers where the Supplier ID is greater than 10? o 6. What command would you use to display the Customer Name, Contact Name, City and Country from the Customer table where the City is London and the Country is UK? • Scenario 3-2: Deleting Data from Tables, page 59 (modified as follows): o 1. What command would you use to remove all records from the Products table where the Price is less than 10.00? o 2. What command would you use to delete all records from the Customers table where the Country is Venezuela? o 3. What command would you use to delete all records from the Suppliers table where the Country is not USA? o 4. What command would you use to delete the Shippers table? • Scenario 3-3: Using SELECT and JOIN Statements, page 59 (modified as follows): o 1. What command would you use to join the Products and Suppliers table to retrieve the Product Name, Supplier Name, and Supplier Phone Number for all products in the Products table? o 2. What command would you use to join the Orders and Shippers table to retrieve the Id and Date of the order along with the Name and Phone Number of the shipper? o 3. What command would you use to display the product name, the category name, and description for all products in the products table? • Scenario 3-4: Manipulating Data using INSERT and UPDATE, page 60 (modified as follows): ** If you modified the W3Schools database while completing the previous assignments, click ‘Restore Database’. o 1. What command would you use to add the following row to the Suppliers table? Panera Bread, Tom Smith, 312 West Main Street, Indianapolis, 46205, USA, 765-543-2123 o 2. What command would you use to add the following row to the Shippers table? 4 , Overnight Deliveries, (765)464-2457 o 3. What command would you use to update the phone number for Speedy Express to (260)435-1234 in the Shippers table? In this lab assignment, you will respond to several scenarios in the Database Administration Fundamentals textbook. Type your responses and include any additional documentation required in this document.

this is from the book is called database administration fundamentals! thanks Tony

Explanation / Answer

Assumption:

As you mentioned above tables are refering w3school databases which are nothing but northwind databases

https://northwinddatabase.codeplex.com/

So sql queries are based on this.

Using the SELECT Command

What command would you use to display all records from the Customers table?

Ans:Select * from Customers;

2. What command would you use to display the CustomerName and ContactName fields from the Customers       table?

Ans:Select CustomerName,ContactName from Customers;

3. What command would you use to display all records from the Customers table and have it sorted alphabetically by the CustomerName column?

Ans:Select * from Customers ORDER BY CustomerName asc;

4. What command would you use to display all records from the Suppliers table that have the Country of Germany?

Ans:Select * from Suppliers where Country='Germany';

5. What command would you use to display the Supplier ID, Supplier Name, Contact Name and Phone from Suppliers where the SupplierID is greater than 10?

Ans:Select SupplierID,CompanyName,ContactName,Phone from Suppliers where SupplierID>10;

6. What command would you use to display the Customer Name, Contact Name, City and Country from the Customer table where the City is London and the Country is UK?

Ans:Select CustomerName,ContactName,City,Country from Customers where City='London' and Country ='UK';

Scenario 3-2: Deleting Data from Tables

1. What command would you use to remove all records from the Products table where the Price is less than 10.00?

Ans:Delete from Products where UnitPrice<10.00;

2. What command would you use to delete all records from the Customers table where the Country is Venezuela?

Ans:Delete from Customers where Country='Venezuela';

3. What command would you use to delete all records from the Suppliers table where the Country is not USA?

Ans:Delete from Suppliers where Country NOT IN('USA');

4. What command would you use to delete the Shippers table?

Ans:Delete from shippers; //It deletes the records from the shippers table.

Scenario 3-3: Using SELECT and JOIN

1. What command would you use to join the Products and Suppliers table to retrieve the Product Name, Supplier Name, and Supplier Phone Number for all products in the Products table?

Ans:SELECT Products.ProductName,Suppliers.SupplierName,Suppliers.Phone FROM Products
LEFT JOIN Suppliers ON Products.SupplierID=Suppliers.SupplierID;

Note:Return all rows from the left table, and the matched rows from the right table

2. What command would you use to join the Orders and Shippers table to retrieve the Id and Date of the order along with the Name and Phone Number of the shipper?

Ans:Select Orders.OrderID,Orders.OrderDate,Shippers.ShipperName,Shippers.Phone from Orders INNER JOIN Shippers on Orders.ShipperID=Shippers.ShipperID;

Note:Returns all rows when there is at least one match in BOTH tables

3. What command would you use to display the product name, the category name, and description for all products in the products table?

Ans:Select Products.ProductName,Categories.CategoryName,Categories.Description from Products RIGHT JOIN Categories on Categories.CategoryID=Products.CategoryID;

Note:Return all rows from the right table, and the matched rows from the left table

Scenario 3-4: Manipulating Data using INSERT and UPDATE

1. What command would you use to add the following row to the Suppliers table? Panera Bread, Tom Smith, 312 West Main Street, Indianapolis, 46205, USA, 765-543-2123?

Ans:INSERT INTO Suppliers (CompanyName, ContactName, Address, City, PostalCode, Country,Phone)
VALUES ('Panera Bread',Tom Smith', 312 West Main Street','Indianapolis','46205','USA','765-543-2123');

2. What command would you use to add the following row to the Shippers table? 4 , Overnight Deliveries, (765)464-2457?

Ans:INSERT INTO Shippers (ShipperID, CompanyName, Phone)VALUES ('4','Overnight Deliveries','(765)464-2457');

3. What command would you use to update the phone number for Speedy Express to (260)435-1234 in the Shippers table?

Ans:UPDATE Shippers SET Phone='(260)435-1234' where CompanyName='Speedy Express';

Related Questions

Navigate

• Browse (All)
• Browse A
• Subjects