CMU MySQL Programming Exercise for A Library Database Code

THIS IS THE TASK! ( I have attached the ERD for this problem and a query for tables)

our task is to write a minimum of 5 queries that will access the data in your tables. The queries should provide meaningful information. Each query must return results, you may need to insert additional rows into your tables to get results. No rows returned will result in 0 points for the question, even if the query is correct. At a minimum you should have

1). A query to retrieve data based on a particular search criteria,
2). a query that joins data from two or more tables,
3.) a query that will delete or update a record from a table based on a particular attribute or set of attributes
4.) a query that uses group by and having
5.) a query that has a subquery

For each of the 5 queries include a 1) statement about what the query does, 2) the SQL statement, and a screen shot of the results, 3) the name of the group member who wrote the query. Attach these in a single file to the group discussion forum.

You will be graded on the following:

• You have 5 queries that meet the requirements.
• The queries run without errors.
• You have included screen shots.

THIS IS WHAT THE TUTOR NEEDS TO GO OFF OF/FIX

query for the tables

CREATE TABLE LIBRARYS (

Library_ID NUMBER PRIMARY KEY,

Library_Name VARCHAR(255) UNIQUE ,

Library_Location VARCHAR(255)

);

/* Creates a table for the LIBRARIAN */

CREATE TABLE LIBRARIAN (

Librarian_ID NUMBER PRIMARY KEY,

FOREIGN KEY Library_ID INT REFERENCES LIBRARYS (Library_ID),

Librarian_Lname VARCHAR(255),

Employee_Fname VARCHAR(255),

);

/* Creates a table for the MEMBERS */

CREATE TABLE MEMBERS (

Member_ID NUMBER PRIMARY KEY,

Member_FName VARCHAR(255),

Member_LName VARCHAR(255),

FOREIGN KEY Library_ID INT REFERENCES LIBRARYS (Library_ID),

);

/* Creates a table for the BOOK */

CREATE TABLE BOOK (

Book_ISBN NUMBER PRIMARY KEY,

Book_Title VARCHAR(255),

FOREIGN KEY Author_Name VARCHAR REFERENCES AUTHOR (Author_Name)

);

/* Creates a table for the BOOKCOPY */

CREATE TABLE BOOKCOPY (

Book_ISBN NUMBER PRIMARY KEY,

FOREIGN KEY Member_ID REFERENCES MEMBERS (Member_ID),

FOREIGN KEY Library_ID REFERENCES LIBRARYS (Library_ID),

FOREIGN KEY Author_Name VARCHAR REFERENCES AUTHOR (Author_Name),

FOREIGN KEY Book_CopyOut BOOLEAN REFERENCES BOOKCHECKOUT (Book_CopyOut)

);

/* Creates a table for the BOOKCHECKOUT */

CREATE TABLE BOOKCHECKOUT (

Book_CopyOut BOOLEAN PRIMARY KEY,

);

/* Creates a table for the AUTHOR */

CREATE TABLE AUTHOR (

Author_Name VARCHAR(255) PRIMARY KEY,

FOREIGN KEY Book_ISBN REFERENCES BOOK (Book_ISBN)

);