Assignment 1

Basic Assignments

1. Select All Rows

   • Write a query to fetch all rows from the user2 table.

2. Filter by City

   • Retrieve all users from the user2 table who live in New York.

3. Filter by Hobby

   • Retrieve all users whose hobby is Reading.

4. Count Rows

   • Write a query to count the total number of users in the user2 table.

5. Sort by Name

   • Write a query to fetch all rows, sorting them alphabetically by the name column.

---

Intermediate Assignments

1. Unique Cities

   • Write a query to find all unique cities in the user2 table.

2. Group by Hobby

   • Write a query to group users by their hobbies and count how many users have each hobby.

3. Add a New Column

   • Write a query to add a new column age to the user2 table.

4. Update Hobby

   • Write a query to update the hobby of a user named Alice to Painting.

5. Delete a Row

   • Write a query to delete the row where the user has the name John.

---

Advanced Assignments

1. Insert Data

   • Insert three new rows into the user2 table with values:

     • ('Alice', 'Boston', 'Dancing')

     • ('Bob', 'Seattle', 'Cooking')

     • ('Charlie', 'San Francisco', 'Traveling')

2. Join with Another Table

   • Create another table user_details with columns: name, email, phone. Write a query to join user2 and user_details on the name column.

3. Find Most Common Hobby

   • Write a query to find the most common hobby in the user2 table.

4. Conditional Aggregation

   • Write a query to count how many users live in New York and how many live in San Francisco.

5. Subquery

   • Write a query to fetch users whose city is the same as the user named Alice.

---

Bonus Assignments

1. Create a View

   • Create a view user_hobby_count that displays each hobby and the number of users with that hobby.

2. Add Constraints

   • Alter the user2 table to add a constraint where the name column cannot have duplicate values.

3. Find Hobby for Each City

   • Write a query to list each city and the hobbies of users from that city.

4. Case Statement

   • Write a query that displays a new column category where:

     • If the hobby is Reading, the category is Intellectual.

     • If the hobby is Dancing, the category is Artistic.

     • Otherwise, the category is Other.

5. Backup and Restore

   • Write a query to copy the contents of the user2 table into another table user2_backup.