S2023_Assignment 2

docx

School

University of Phoenix *

*We aren’t endorsed by this school

Course

101

Subject

Computer Science

Date

Dec 6, 2023

Type

docx

Pages

2

Uploaded by CaptainIron12583

Report
COSC 4120 Spring 2022 Name COSC 4120 Database Applications Project – Assignment 2 Use this page as the cover page for your turning in project related assignments. 1. Record following activities on the project and provide any issues you may have experienced. ACTIVITIES: Sample Database Objects and Data ● Script file which includes: SQL for creating and dropping tables REGION, CUSTOMER, ADDRESS, ADDRESS TYPE ● SQL for sequences that are used with the above new tables ● SQL for triggers that are used with the above new tables 2. Run the CREATE and INSERT scripts. Clear any possible error. Test the drop script 3. Write the following SQLs a. List of customers for each region, with region name and customer name. SELECT C.CUSTOMERNAME, C.CP_NAME, R.REGIONNAME FROM REGION R JOIN CUSTOMER C ON R.REGIONID = C.REGIONID; b. List of customer name, address, and address type code for Midwest. SELECT C.CUSTOMERNAME, C.CP_NAME, A.STREET_ADDRESS, A.CITY, A.STATE, A.ZIP, AT.TYPE_CODE FROM ADDRESSTYPE AT JOIN ADDRESS A JOIN CUSTOMER C JOIN REGION R ON R.REGIONID = C.REGIONID ON C.CUSTOMERID = A.CUSTOMERID ON A.TYPEID = AT.TYPEID WHERE R.REGIONNAME = 'MidWest'; c. List all regions with the count of their customers. SELECT R.REGIONNAME, COUNT(*) AS "Number of Customers" FROM REGION R JOIN CUSTOMER C ON C.REGIONID = R.REGIONID
COSC 4120 Spring 2022 Name GROUP BY (R.REGIONNAME); d. What is the region with the highest number of customer. A sorted list is not the answer! SELECT REGIONNAME AS "Region with Highest Number of Customers" FROM (SELECT R.REGIONNAME, COUNT(*) AS "Number of Customers" FROM REGION R JOIN CUSTOMER C ON C.REGIONID = R.REGIONID GROUP BY (R.REGIONNAME)) WHERE "Number of Customers" = (SELECT MAX(COUNT(*)) FROM REGION R JOIN CUSTOMER C ON C.REGIONID = R.REGIONID GROUP BY R.REGIONNAME); 4. Attach the script files you created.
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

Related Documents

CSCO 220 Modules 9 and 10 Lab Packet-QoS and Network Management.docx
Lab 16 (Port Security) Tesah Capers.doc
RA 2 Counting Lines & Words (Fall 2023).pdf
Lab 2 Part 2 W21 Regression_GladysVillafuerte_301264680.docx.docx
Lab 3 Part 1 Neural Networks_GladysVillafuerte_301264680.docx
CIS 236 Final Exam.pdf
Module 6 Discussion.docx
Module 8 Reflection Activity.docx
Burge-Irby,April 1.3.pdf
RQ - OVERFITTING copy.pdf
TET1 Task 2.docx
COMP 229 Project Peer Evaluation Form.docx
Recommended textbooks for you
Text book image
Fundamentals of Information Systems
Computer Science
ISBN:9781337097536
Author:Ralph Stair, George Reynolds
Publisher:Cengage Learning
Text book image
A Guide to SQL
Computer Science
ISBN:9781111527273
Author:Philip J. Pratt
Publisher:Course Technology Ptr
Text book image
Database Systems: Design, Implementation, & Manag...
Computer Science
ISBN:9781305627482
Author:Carlos Coronel, Steven Morris
Publisher:Cengage Learning
Text book image
Oracle 12c: SQL
Computer Science
ISBN:9781305251038
Author:Joan Casteel
Publisher:Cengage Learning
Text book image
Database Systems: Design, Implementation, & Manag...
Computer Science
ISBN:9781285196145
Author:Steven, Steven Morris, Carlos Coronel, Carlos, Coronel, Carlos; Morris, Carlos Coronel and Steven Morris, Carlos Coronel; Steven Morris, Steven Morris; Carlos Coronel
Publisher:Cengage Learning
Text book image
Principles of Information Systems (MindTap Course...
Computer Science
ISBN:9781305971776
Author:Ralph Stair, George Reynolds
Publisher:Cengage Learning
SEE MORE TEXTBOOKS
Recommended textbooks for you
Text book image
Fundamentals of Information Systems
Computer Science
ISBN:9781337097536
Author:Ralph Stair, George Reynolds
Publisher:Cengage Learning
Text book image
A Guide to SQL
Computer Science
ISBN:9781111527273
Author:Philip J. Pratt
Publisher:Course Technology Ptr
Text book image
Database Systems: Design, Implementation, & Manag...
Computer Science
ISBN:9781305627482
Author:Carlos Coronel, Steven Morris
Publisher:Cengage Learning
Text book image
Oracle 12c: SQL
Computer Science
ISBN:9781305251038
Author:Joan Casteel
Publisher:Cengage Learning
Text book image
Database Systems: Design, Implementation, & Manag...
Computer Science
ISBN:9781285196145
Author:Steven, Steven Morris, Carlos Coronel, Carlos, Coronel, Carlos; Morris, Carlos Coronel and Steven Morris, Carlos Coronel; Steven Morris, Steven Morris; Carlos Coronel
Publisher:Cengage Learning
Text book image
Principles of Information Systems (MindTap Course...
Computer Science
ISBN:9781305971776
Author:Ralph Stair, George Reynolds
Publisher:Cengage Learning
SEE MORE TEXTBOOKS