database management essentials assignment

Database Management Essentials Assignment 2 Solutions - Construction of SQL queries with SELECT, JOIN, WHERE, ORDER BY AND GROUP BY using MySQL codes

Submitted by : Preetha Rajan

Email: [email protected]

Practice Problems

Question 1 List the customer number, the name, the phone number, and the city of customers.

Question 2 List the customer number, the name, the phone number, and the city of customers who reside in Colorado (State is CO)

Question 3 List all columns of the EventRequest table for events costing more than $4000. Order the result by the event date (DateHeld)

Question 4 List the event number, the event date (DateHeld), and the estimated audience number with approved status and audience greater than 9000 or with pending status and audience greater than 7000

Question 5 List the event number, event date (DateHeld), customer number and customer name of events placed in January 2018 by customers from Boulder

Table: 0 records

eventno dateheld custno custname ——– ——— ——- ———

Question 6 List the average number of resources used (NumberFld) by plan number. Include only location number L100

Question 7 List the average number of resources used (NumberFld) by plan number. Only include location number L100. Eliminate plans with less than two event lines containing location number L100.

Graded Problems

Question 1 List the city, state, and zip codes in the customer table. Your result should not have duplicates.

Question 2 List the name, department, phone number, and email address of employees with a phone number beginning with “3-”.

Question 3 List all columns of the resource table with a rate between $10 and $20. Sort the result by rate.

Question 4 List the event requests with a status of “Approved” or “Denied” and an authorized date in July 2018. Include the event number, authorization date, and status in the output.

Question 5 List the location number and name of locations that are part of the “Basketball arena”. Your WHERE clause should not have a condition involving the facility number compared to a constant (“F101”). Instead, you should use a condition on the FacName column for the value of “Basketball arena”.

Question 6 For each event plan, list the plan number, count of the event plan lines, and sum of the number of resources assigned. For example, plan number “P100” has 4 lines and 7 resources assigned. You only need to consider event plans that have at least one line

• Database Management Essentials Assignment 3 Solutions - Construction of SQL queries with SELECT, JOIN, WHERE, ORDER BY AND GROUP BY using MySQL codes
• by Preetha Rajan
• Last updated over 5 years ago
• Hide Comments (–) Share Hide Toolbars

Twitter Facebook Google+

Or copy & paste this link into an email or IM:

Database Management Essentials

Database Management Essentials provides the foundation you need for a career in database development, data warehousing, or business intelligence, as well as for the entire Data Warehousing for Business Intelligence specialization. In this course, you will create relational databases, write SQL statements to extract information to satisfy business reporting requests, create entity relationship diagrams (ERDs) to design databases, and analyze table designs for excessive redundancy. As you develop these skills, you will use either Oracle, MySQL, or PostgreSQL to execute SQL statements and a database diagramming tool such as the ER Assistant or Visual Paradigm to create ERDs. We’ve designed this course to ensure a common foundation for specialization learners. Everyone taking the course can jump right in with writing SQL statements in Oracle, MySQL, or PostgreSQL.

Two deals to help you save

What's inside

Good to know, save this course, reviews summary, database skills.

• Create a folder on your computer for the course.
• Download all of the course materials.
• Put all of the course materials in the folder.
• Find a book on database management.
• Read the book.
• Review your notes from database systems and SQL.
• Review data modeling techniques and concepts.
• Find some online tutorials on database management.
• Watch the tutorials.
• Try out the techniques that you learn in the tutorials.
• Find other students who are taking this course.
• Meet with the other students regularly to discuss the material.
• Work together on practice problems and assignments.
• Choose a database management system (DBMS) to use.
• Install the DBMS on your computer.
• Create a database and some tables.
• Write some SQL queries to retrieve data from the database.
• Create a new database.
• Design some tables for the database.
• Choose a database modeling tool.
• Install the database modeling tool on your computer.
• Create a new database model.
• Add tables, columns, and relationships to the database model.
• Choose a topic for your project.
• Design the database for your project.
• Create the database and tables.
• Populate the database with data.

Career center

Reading list.

Similar courses

• Relational Database Support for Data Warehouses
• Data Warehouse Concepts, Design, and Data Integration
• Business Intelligence Concepts, Tools, and Applications
• Design and Build a Data Warehouse for Business Intelligence Implementation
• Database Management Essentials (viewing)

What you'll learn

OpenCourser helps millions of learners each year. People visit us to learn workspace skills, ace their exams, and nurture their curiosity.

Our extensive catalog contains over 50,000 courses and twice as many books. Browse by search, by topic, or even by career interests. We'll match you to the right resources quickly.

Find this site helpful? Tell a friend about us.

We're supported by our community of learners. When you purchase or subscribe to courses and programs or purchase books, we may earn a commission from our partners.

Your purchases help us maintain our catalog and keep our servers humming without ads .

Thank you for supporting OpenCourser.

Database Management Essentials

• From www.coursera.org
• Free Access
• Fee-based Certificate
• 7 Sequences
• Intermediate Level
• Subtitles in Korean, Chinese

Their employees are learning daily with

Course details.

• Week 1 - Course Introduction Module 1 provides the context for Database Management Essentials. When you’re done, you’ll understand the objectives for the course and know what topics and assignments to expect. Keeping these course objectives in mind will help you succeed throughout the cou...
• Week 1 - Introduction to Databases and DBMSs We’ll launch into an exploration of databases and database technology and their impact on organizations in Module 2. We’ll investigate database characteristics, database technology features, including non-procedural access, two key processing environments, and...
• Week 2 - Relational Data Model and the CREATE TABLE Statement Now that you have the informational context for database features and environments, you’ll start building! In this module, you’ll learn relational data model terminology, integrity rules, and the CREATE TABLE statement. You’ll apply what you’ve learned in prac...
• Week 3 - Basic Query Formulation with SQL This module is all about acquiring query formulation skills. Now that you know the relational data model and have basic skills with the CREATE TABLE statement, we can cover basic syntax of the SQL SELECT statement and the join operator for combining tables. SE...
• Week 3 - Extended Query Formulation with SQL Now that you can identify and use the SELECT statement and the join operator, you’ll extend your problem solving skills in this module so you can gain confidence on more complex queries. You will work on retrieval problems with multiple tables and grouping. In...
• Week 4 - Notation for Entity Relationship Diagrams Module 6 represents another shift in your learning. In previous modules, you’ve created and populated tables and developed query formulation skills using the SQL SELECT statement. Now you’ll start to develop skills that allow you to create a database design to...
• Week 4 - ERD Rules and Problem Solving Module 7 builds on your knowledge of database development using basic ERD symbols and relationship variations. We’ll be practicing precise usage of ERD notation and basic problem solving skills. You will learn about diagram rules and work problems to help you ...
• Week 5 - Developing Business Data Models In Module 8, you’ll use your ERD notation skills and your ability to avoid diagram errors to develop ERDs that satisfy specific business data requirements. You will learn and practice powerful problem-solving skills as you analyze narrative statements and tran...
• Week 5 - Data Modeling Problems and Completion of an ERD Now that you have practiced data modeling techniques, you’ll get to wrestle with narrative problem analyses and transformations for generating alternative database designs in Module 9. At the end of this module, you’ll learn guidelines for documentation and de...
• Week 6 - Schema Conversion Modules 6 to 9 covered conceptual data modeling, emphasizing precise usage of ERD notation, analysis of narrative problems, and generation of alternative designs. Modules 10 and 11 cover logical database design, the next step in the database development proces...
• Week 7 - Normalization Concepts and Practice Module 11 covers normalization, the second part of the logical database design process. Normalization provides tools to remove unwanted redundancy in a table design. You’ll discover the motivation for normalization, constraints to reason about unwanted redunda...

Prerequisite

Instructors.

Michael Mannino Associate Professor Business School, University of Colorado Denver

Coursera is a digital company offering massive open online course founded by computer teachers Andrew Ng and Daphne Koller Stanford University, located in Mountain View, California. 

Coursera works with top universities and organizations to make some of their courses available online, and offers courses in many subjects, including: physics, engineering, humanities, medicine, biology, social sciences, mathematics, business, computer science, digital marketing, data science, and other subjects.

Get certified in

Navigation Menu

Search code, repositories, users, issues, pull requests..., provide feedback.

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly.

To see all available qualifiers, see our documentation .

• Notifications You must be signed in to change notification settings

My assignment solutions for Database Management Essentials Course on Coursera

bhaveshlohana/database-management-essentials

Folders and files, repository files navigation, database-management-essentials.