Ultimate Engineering Study Guide - Questions & Answers

Part 1 Basic Selects:1. Use the Students table and display the city and first name for each student, ordered bycity and first name in alphabetical order Marks 12. Use the Staff table and display the first/last names of all staff who have a first name thatstarts with the letter D and a last name that starts with the letter K Marks 1Part 2 Joins:1. Use the Faculty and Staff tables to display the first and last names of everyone who iscurrently On Leave Marks 12. Use the Students, Student_Schedules and Classes tables to display the first and lastnames of everyone currently taking a course with duration > 140. Marks 13. Use the Faculty and Faculty_Classes tables to display the StaffID of all people whoarent teaching a course. Marks 1Part 3 Unions:1. Use the Staff and Students tables to display the first and last names of all staff andstudents who live in Long Beach Marks 1Part 4 Grouping:1. Use the Students and Student_Schedules tables to display each students name as wellas the number of courses they are taking. Marks 12. Use the Students and Student_Schedules tables to display each students average gradeMarks 13. Use the Students and the Student_Schedules tables to display the first and last names ofall students who are taking more than 2 classes Marks 1Part 5 Database Creation:1. Create a new database in your phpmyadmin called "MusicDB"4. Your database needs to store the following data: Artist first name, artist last name,album name, album year, Total Sale create a normalized database that does this. Makesure you have primary/foreign keys. Marks 12. Show your professor your database diagram in PHPMyAdminTotal Marks: 10