Mod_8 -Assignment
docx
keyboard_arrow_up
School
Kennesaw State University *
*We aren’t endorsed by this school
Course
3410
Subject
Information Systems
Date
Dec 6, 2023
Type
docx
Pages
3
Uploaded by ColonelRose5100
Mod_8 – Web Server and Implementing SQL Queries
Assignment
Using Microsoft Access and SQL Script statement to answer the following questions.
Deliverable
Please deliver .acc (
SQL view in Access
) and .sql file and copy and paste your scripts
below.
Submit the short Answer of the 4-8 questions
Submit compressed .zip via d2l assignment dropbox
Inventory Table
Column Name
Type
Key
Required
Remarks
WarehouseID
Integer
Primary Key
Foreign Key
Yes
WAREHOUSE
SKU
Integer
Primary Key
Foreign Key
Yes
SKU_DATA
SKU_Description
Character (35)
No
Yes
QuantityOnHand
Integer
No
No
QuantityOnOrder
Integer
No
No
For Microsoft Access, you can come up with your own Warehouse_ID, TotalItemsOnHand
numbers, etc., or use the below table.
1. - Write an SQL statement to display the WarehouseID and the sum of QuantityOn-Hand
grouped by WarehouseID. Name the sum TotalItemsOnHand and display the results in
descending order of TotalItemsOnHand.
(Provide the SELECT Script statement and screenshot)
SELECT WarehouseID, SUM(QuantityOnHand) AS TotalItemsOnHand
FROM INVENTORY
Group By WarehouseID
Order By TotalltemsONHand DESC;
2.
- Write an SQL statement to display the WarehouseID and the sum of QuantityOn-Hand
grouped by WarehouseID. Omit all SKU items that have three or more items on hand from the
sum, name the sum TotalItemsOnHandLT3, and display the results in descending order of
TotalItemsOnHandLT3.
(Provide the SELECT Script statement and screenshot)
SELECT WarehouseID, SUM(QuantityOnHand) AS TotalItemsOnHandLT3
FROM INVENTORY WHERE QuantityOnHand<3
Group BY WarehouseID
ORDER BY TotalItemsOnHandLT3 DESC;
3.
- Write an SQL statement to display the WarehouseID and the sum of Quantity OnHand
grouped by WarehouseID. Omit all SKU items that have three or more items on hand from the
sum and name the sum TotalItemsOnHandLT3.
Show the WarehouseID only for warehouses having fewer than two SKUs in their
TotalItemsOnHandLT3. Display the results in descending order of TotalItemsOnHandLT3.
(Provide
the SELECT Script statement Script and screenshot)
SELECT WarehouseID, SUM(QuantityOnHand) AS TotalItemsOnHandLT3
FROM INVENTORY
WHERE QuantityOnHand <=2 and WarehouseID >2
GROUP BY WarehouseID
HAVING COUNT(SKU) <2
ORDER BY WarehouseID DESC;
Answer following questions:
4.
Explain how ODBC, OLE DB, and ADO are related.
ODBC is standard for relational database and OLE DB provides standards for relational and other
databases.
ADO was developed to provide easier access to data
5. What role does the DBMS driver serve? Who supplies it?
Driver is interface between application and data source supplied DriverManager
6. What does JDBC stand for and its driver types?
Java Database Connectivity
Type 1 JBDC-OBDB Bridge to provide API that interfaces OBDC driver
Type 2 java API that connects to native-library of a DBMS product. Must reside on same machine or DBMS
handles the intermachine communication
Type 3 JAVA API that connects DBMS independent network protocol.
Type 4 JAVA API that connects DBMS independent network protocol.
7. What is Hypertext Markup Language (HTML), and what function does it serve?
Hypertext means you can include links to other objects, maps, pictures, audio, video files in web page. This is
used to create webpage to be displayed in web browser.
Uses HTML syntax rules and document tags to create specific onscreen displays
8. What is the World Wide Web Consortium (W3C)? W3C is international standards for developing standards for
world wide web.
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