Assignment 13: Rough Draft

In this session, I learned about some of the different queries you can do in SQL. I learned to use SELECT, FROM, WHERE, DELETE, UPDATE, and other queries that can help you see which fields you want to see the results. The SELECT and FROM you would use to select a field, and the FROM would tell you what table to select that field from. I also learned how you can see certain results (like only people from the USA) and you could use the WHERE query to see only customers from the USA or the area you choose. Another thing that I learned about was the DELETE and UPDATE. If you added another shipper per se, using the INSERT INTO, but you wanted to delete it, you could use the DELETE command to delete that specific one. If you made a mistake or wanted to update the name only, you could use the UPDATE command and it would update a specific field. This can be useful in the future if I need to use SQL for any kind of data. Since I want to go into animation, I’m not sure where I would use it, but I could see myself using it in the future for something if I created a website of my own and needed to keep track of form info or something.

SQL Activity 2

1. To select all customers:
   SELECT * FROM [Customers]
2. To select only customer ID and customer name:
   SELECT CustomerID, CustomerName FROM [Customers]
3. To select customer name, address, city, and postal code for all customers from the United Kingdom:
   SELECT CustomerName, Address, City, PostalCode FROM [Customers]
   WHERE Country = 'UK'
4. To select contact name and customer name for all customers, sorted alphabetically by contact name:
   SELECT ContactName, CustomerName FROM [Customers]
   ORDER BY ContactName
5. To count the total number of customers:
   SELECT COUNT(*) FROM [Customers]
6. To count the number of customers from each country:
   SELECT COUNT(*) FROM [Customers]
   GROUP BY Country
7. To count the number of customers from each country and sort the list in descending order by count and ascending order by country in case of a tie:
   SELECT Country, COUNT(*) FROM [Customers]
   GROUP BY Country
   ORDER BY COUNT(*) DESC, Country ASC
8. To count the number of customers from each country and sort the list in descending order by count and ascending order by country in case of a tie, listing only those countries with more than 10 customers:
   SELECT Country, COUNT(*) FROM [Customers]
   GROUP BY Country
   HAVING COUNT(*) > 10
   ORDER BY COUNT(*) DESC, Country ASC

SQL Activity 3

1. To select customer name, order ID, and order date for all customers:
   SELECT CustomerName, OrderID, OrderDate
   FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
2. To select customer name, order number, and order date for all customers, renaming the OrderID field as Order Number:
   SELECT CustomerName, OrderID AS 'Order Number', OrderDate
   FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
3. To select customer name, order number, and order date for all customers, sorted by customer name and order number:
   SELECT CustomerName, OrderID AS 'Order Number', OrderDate
   FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
   ORDER BY CustomerName, OrderID
4. To select order number, order date, product name, and quantity ordered for all customers:
   SELECT Orders.OrderID AS 'Order Number', OrderDate, ProductName AS 'Product Name', Quantity
   FROM Orders INNER JOIN OrderDetails ON Orders.OrderID = OrderDetails.OrderID INNER JOIN Products ON OrderDetails.ProductID = Products.ProductID
5. To select order number, order date, product name, quantity ordered, and extended price (quantity * price) for all customers:
   SELECT Orders.OrderID AS 'Order Number', OrderDate, ProductName AS 'Product Name', Quantity, (Quantity * Price) AS 'Extended Price'
   FROM Orders INNER JOIN OrderDetails ON Orders.OrderID = OrderDetails.OrderID INNER JOIN Products ON OrderDetails.ProductID = Products.ProductID
6. To select order number, order date, product name, quantity ordered, and extended price for customer 2:
   SELECT Orders.OrderID AS 'Order Number', OrderDate, ProductName AS 'Product Name', Quantity, (Quantity * Price) AS 'Extended Price'
   FROM Orders INNER JOIN OrderDetails ON Orders.OrderID = OrderDetails.OrderID INNER JOIN Products ON OrderDetails.ProductID = Products.ProductID
   WHERE CustomerID = 2
7. To select order number, order date, product name, quantity ordered, and extended price for customer 'Around the Horn':
   SELECT Orders.OrderID AS 'Order Number', OrderDate, ProductName AS 'Product Name', Quantity, (Quantity * Price) AS 'Extended Price'
   FROM Orders INNER JOIN OrderDetails ON Orders.OrderID = OrderDetails.OrderID INNER JOIN Products ON OrderDetails.ProductID = Products.ProductID INNER JOIN Customers ON Customers.CustomerID = Orders.CustomerID
   WHERE CustomerName = 'Around the Horn'

SQL Activity 4

1. To add a new shipper with ID 4, name 'On Time Delivery', and phone '(503) 555 0123':
   INSERT INTO Shippers (ShipperID, ShipperName, Phone)
   VALUES (4, 'On Time Delivery', '(503) 555 0123')
2. To increase prices on all products by 1:
   UPDATE Products
   SET Price = Price + 1
3. To reduce prices on all products by 1:
   UPDATE Products
   SET Price = Price - 1
4. To change the new shipper's name from 'On Time Delivery' to 'On-Time Delivery':
   UPDATE Shippers
   SET ShipperName = 'On-Time Delivery'
   WHERE ShipperID = 4
5. To delete the new shipper:
   DELETE FROM Shippers
   WHERE ShipperID = 4

Return to Home Page

Return to Assignment List Page