sql queries for oracle database. b) (8 points) Write the database schema for this ER diagram. c)

sql queries for oracle database.
 

Transcribed Image Text:

b) (8 points) Write the database schema for this ER diagram. c) (8 points) Write the CREATE TABLE statements for all tables identified for this database. The create statements have to work when ran against the Oracle database. The create statements have to be written in an order such that if executed in that order will not cause any error. Question 3) (40 points) (provide your answers of this in an SQL file) Given the following schema: songs(songid: int, title string, release: date) singers (singerid: int, name: string, city: string, state:string) singsin(singerid:int, songid:int) The primary keys are underlined in each relation. Relation singers contains information about singers. Relation songs contain information about songs. Relation singsin contains information about singers singing songs. Notes: SQL must run against the Oracle database we use in class. (Please run and test your queries against the Oracle DB. Create the tables, insert some data, and test your queries!!!) SQL queries that do not run against the Oracle db will receive 0 points In the .sql file, before each SQL statement you MUST include a comment line with the problem number the sql statement is for (e.g., before writing the SQL query for (c) add a comment line such as --Answer for c)). Remember that a comment line starts with two dash symbols. Any other additional comments can be written in comment lines. For CS430 students, each problem (a through h) carries 5 points possible. For cs630 students, each problem (a through j) carries 4 points possible. a) Write the SQL statements that create tables songs, singers and singsin. Don't forget to define the key constraints. b) Write the SQL query that extracts all the names of the singers from state MA. Sort the result by name in descending order. c) Write the SQL query that extracts information about the singers and the songs they sing. Each record in the result should contain the name of the singer, the title of the song and the release date of the song. d) Write the SQL query that finds how many singers from Boston, MA are in the database. e) Write the SQL that extracts information about singers whose name starts with letter A. Sort the result by the state of the singers, in an ascending order. f) Write the SQL that extracts the name, city and state for all singers who played in a song that has a title that contains the word joy. The query should be case insensitive with regards to the case of the letters from the title of the song. g) Write an SQL query to extract the name, city and state of singers that sang a song that was released before Sept 1, 2021. h) Write the SQL to extracts the name and state of singers that sang some songs released in year 2020. The results should contain no duplicates. i) [CS630 only] Write the SQL query to extract the id, name and city of singers that are from MA and sang some songs released between Jan 1, 2020 and July 31, 2022. Sort the result by the name of the singers in descending order. j) [CS630 only] Write the SQL to extract the number of unique singers that sang some song released after Dec 10, 2021. b) (8 points) Write the database schema for this ER diagram. c) (8 points) Write the CREATE TABLE statements for all tables identified for this database. The create statements have to work when ran against the Oracle database. The create statements have to be written in an order such that if executed in that order will not cause any error. Question 3) (40 points) (provide your answers of this in an SQL file) Given the following schema: songs(songid: int, title string, release: date) singers (singerid: int, name: string, city: string, state:string) singsin(singerid:int, songid:int) The primary keys are underlined in each relation. Relation singers contains information about singers. Relation songs contain information about songs. Relation singsin contains information about singers singing songs. Notes: SQL must run against the Oracle database we use in class. (Please run and test your queries against the Oracle DB. Create the tables, insert some data, and test your queries!!!) SQL queries that do not run against the Oracle db will receive 0 points In the .sql file, before each SQL statement you MUST include a comment line with the problem number the sql statement is for (e.g., before writing the SQL query for (c) add a comment line such as --Answer for c)). Remember that a comment line starts with two dash symbols. Any other additional comments can be written in comment lines. For CS430 students, each problem (a through h) carries 5 points possible. For cs630 students, each problem (a through j) carries 4 points possible. a) Write the SQL statements that create tables songs, singers and singsin. Don't forget to define the key constraints. b) Write the SQL query that extracts all the names of the singers from state MA. Sort the result by name in descending order. c) Write the SQL query that extracts information about the singers and the songs they sing. Each record in the result should contain the name of the singer, the title of the song and the release date of the song. d) Write the SQL query that finds how many singers from Boston, MA are in the database. e) Write the SQL that extracts information about singers whose name starts with letter A. Sort the result by the state of the singers, in an ascending order. f) Write the SQL that extracts the name, city and state for all singers who played in a song that has a title that contains the word joy. The query should be case insensitive with regards to the case of the letters from the title of the song. g) Write an SQL query to extract the name, city and state of singers that sang a song that was released before Sept 1, 2021. h) Write the SQL to extracts the name and state of singers that sang some songs released in year 2020. The results should contain no duplicates. i) [CS630 only] Write the SQL query to extract the id, name and city of singers that are from MA and sang some songs released between Jan 1, 2020 and July 31, 2022. Sort the result by the name of the singers in descending order. j) [CS630 only] Write the SQL to extract the number of unique singers that sang some song released after Dec 10, 2021.