Database Administration Management Analysis System Assignment

Assignment Task

Questions

1. Draw an ER diagram for a restaurant management system. Demonstrate a minimum of 4 entities, their relationships, cardinalities and specialization/generalization.

2. Describe the fundamental differences between Data Definition Language (DDL) and Data Manipulation Language (DML) commands. Provide examples of each type of command and explain their respective roles in database administration and data analysis.

3. Consider two tables, "Orders" and "Customers," in a relational database. Write an SQL query to perform an inner join between these tables, retrieving information about customers who have placed orders.

4. Given a scenario where you have a MySQL database containing sales data, write an SQL query to calculate the total sales revenue for a specific product category within a specified time frame. Explain the logic behind your query.

5. In a bookstore database, there's a Books table with columns book_id, title, author, and price. Write an SQL query to retrieve the titles and prices of books published by the author "John Doe" with a price greater than $20.

6. Consider a database for a school with a Students table containing columns student_id, student_name, grade, and age. Write an SQL query to fetch the names and ages of students who are in the 9th grade and are older than 15 years.

Create a database named "Hospital" and design the following tables:

1. Write the SQL statements to create the above tables and establish the necessary relationships between them.

2. Write a SQL query to retrieve the names of all the doctors who specialize in Cardiology.

3. Write a SQL query to calculate the average age of all the patients.

4. Write a SQL query to delete all the appointments from the "Appointments" table for which the appointment date is in the past.

This Management has been solved by our PHD Experts at My Uni Paper.