Assignment 4
docx
keyboard_arrow_up
School
William Rainey Harper College *
*We aren’t endorsed by this school
Course
CIS-101
Subject
Information Systems
Date
Feb 20, 2024
Type
docx
Pages
3
Uploaded by Nimrah.I
Assignment #4
1.
Download a copy of the
Database Examples/Northwind
database for your selected database application.
2.
Review the E-R diagram in your database application to verify that it matches the
Database Examples/Northwind
example. Note the primary keys, foreign keys, and relationships. Then write SQL queries to determine results for each of the following.
3.
Select the CustomerName and OrderID for all customers who ordered 'Aniseed Syrup', sorted in alphabetical order.
SELECT customers.customername, orders.orderid
FROM products
INNER JOIN ((customers INNER JOIN orders on customers.customerid=orders.customerid)
INNER JOIN orderdetails on orders.orderid=orderdetails.orderid)
ON products.productid=orderdetails.productid
WHERE products.productname = 'Aniseed syrup'
ORDER BY customers.customername ASC;
4.
Select the ProductName, Quantity, Price, and ExtendedPrice (Quantity * Price) for orders 10344 and 10345.
SELECT products.productname, orderdetails.quantity, products.price, quantity*price as Extendedprice
FROM products
INNER JOIN (orders INNER JOIN orderdetails on orders.orderid=orderdetails.orderid)
on products.productid=orderdetails.productid
WHERE (orders.orderid=10344) OR (orders.orderid=10345);
5.
Select the first and last names and current age of all employees having a birthday in September.
SELECT employees.firstname, employees.lastname, Int((Date()-[Employees]![BirthDate])/365.2425) AS Age
FROM employees
WHERE
employees.birthdate like '9/*/****';
6.
Select the OrderID, count of products ordered, and total order cost for orders 10344 and 10345.
7.
Select the total number of orders and total cost of orders shipped by 'Speedy Express' in February 1997.
8.
Insert 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')
9.
Update products to increase prices on all products by 1 ($1.00).
UPDATE Products
SET prices = (prices + $1.00)
10. Update products to reduce prices on all products by 1 (-$1.00).
UPDATE Products
SET prices = ( price -1.00 )
11. Update the new shipper's name from 'On Time Delivery' to 'On-Time Delivery'.
UPDATE Shippers
SET ShipperName = ‘ On-Time Delivery ‘
WHERE ShipperName = ‘ On Time Delivery ‘
12. Delete the new shipper.
DELETE FROM Shippers
WHERE Shippers = ‘ new shipper ‘
This week’s assignment was a little challenging for me. There were some new commands that I needed to understand; I used some of them efficiently while some of them were a little hard. I would have enjoyed doing this assignment if I knew how the commands needed to be used correctly. But, overall, I understood the other commands by watching all the videos posted by my classmates on the discussion
boards. This would help health care analytics use current and historical data to gain insights, macro and micro, and support decision-making at both the patient and business levels. Also, it makes storing large volumes of patient information a breeze and does a much better job than essential spreadsheet software in this respect.
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help