SQL ASSESSMENT
Team Members Names: _______________________________
Using the same tables (userfields and elite) that you imported for class, answer the below questions. Make sure that you execute in MySQL all codes that you are asked to provide in this assessment. You should familiarize yourself with the userfields and elite tables, if you have not already done so, prior to trying to complete this work. The SQL Assessment is due by 11:59 PM on Friday, 3/12. Provide all answers (business questions/queries, SQL codes, explanations, etc.) in this document. Bold all answers and 1 person from each team should submit this completed document along with your MySQL saved script that you use to execute all of the SQL codes. You are to work in the same teams as the 1st SQL Assignment. No discussing across teams. You can however use your PowerPoint and notes.
Provide the business question /query for the below SQL code (1 point):
SELECT max(TotYrsElite)
FROM yelp.elitestatus_t;
Query -
Provide the business question/query for the below SQL code (1 point):
SELECT *
FROM userfields_t
WHERE NOT ReviewCount = 0;
Query -
Query: What are the total votes (cool, useful, funny) specifically for userids BwvlE7SguUtXP-InnJbjSw and Yap-E13OdirLr9w_X6mcCA. Provide 1 SQL Code/Statement that will output the totals for each of these users. Make sure the results display the userid and have the total votes show in a column named TotalVotes. (2 points)
https://www.techonthenet.com/sql/count.php
SELECT COUNT(*)
FROM userfields_T
[WHERE userID=’BwvlE7SguUtXP-InnJbjSw’; ‘Yap-E13OdirLr9w_X6mcCA’]
##[ORDER BY expression [ ASC | DESC ]];
Query: Provide the userids for any users that were elite in the years 2011-2013 in elite0 (make sure to have code that shows your work to see if you are correct). Provide SQL Code. *There are many different options for writing the code. Do not use ‘AND’ in your SQL code. Do not use the ‘OR’ in your SQL code. *Hint – So there must be some other command you can use. (1 point)
Based on our imported tables, create your own business question / query where something (whatever item you choose) needs to be in descending order (1 point). Then provide the SQL code for it (1 point).
Query: Which values for fans include more than 10 users? Provide SQL Code. (2 points)
Query: Provide 1 table with ALL the information available for all users (489 rows). Do it in 2 different ways – meaning provide 2 different sets of SQL statements/code. Provide SQL Code. (2 points, 1 point each).
Use the FROM and ON commands to create a statement that will provide the userids, review counts, and total years elite for all users regardless of whether that user exists in the elite table (note that because everyone does exist in the elite table it will show everyone, but if we had a different data set it might not be the case, so the code needs to be correct). Provide SQL Code. (1 point)
If you switched the order of the tables in your entered statement (specifically the FROM line) for the above question (question 8), how would this change your SQL statement? Show this by providing the new SQL statement. Provide SQL Code. (1 point)
What is wrong with the below SQL code? Explain and use proper terminology. Provide how it can be fixed. (1 point)
SELECT UserID, Fans, TotYrsElite
FROM userfields_t, elitestatus_t
WHERE elitestatus_t.UserID = userfields_t.UserID;
Explain -
Which users were elite in 2013 (make sure to show how you check to see if you are correct when outputting)? Provide SQL Code. (1 point)
List the different years in elite0 without any of the years repeating. Provide SQL Code. (1 point)
What records have empty / no data for elite0? Provide SQL Code. *Note that this one is not one that you will be able to enter into MySQL and get a proper result because of the way our current tables imported. The goal here is just to see do you know how to write the code (1 point)
List the records where the yelping since date has the year as 2012. Provide SQL Code. *Note – that this is a question from your SQL Assignment. While in that assignment, my goal was for you to use the LIKE command and most teams did, many of the answers were actually wrong (though I didn’t take off credit) because of something else in the statement. This time around I am looking for a full correct statement. *Hint – using just my slides without a wholistic understanding of why things are done that way will give you the wrong answer. Other information was provided in 1 of the weeks that would give you understanding for the right answer. (1 point)
Write your own subquery/nesting business question / query based on our data (1 point). Provide the subquery / nesting SQL code (2 points). *Note – our data may not actually make sense to do this, but it can be done with our data. My purpose is to see if you understand the type of question that calls for a nesting query and how to write the parts of the code.
Diagram the below business rules into a complete EERD (complete with ALL notation – relationships, cardinality, PKs, FKs, Constraints, etc.). (4 points)
A doctor’s office keeps information on the doctors, the patients, and the medicines
Each doctor, each patient, and each medicine would have their own id number and name. The ids identify each specific person or item. The doctor’s address, phone, and specialty are stored. The patient’s phone number, address, and insurance carrier are stored. Each medicine’s supplier and cost are stored.
A doctor may have many patients and though a patient can see many doctors in the doctor’s office, patients are assigned, in the database, to 1 specific doctor when they make an appointment for their 1st visit.
Patients may be prescribed multiple medications and a medication may be prescribed to multiple patients.
Medications are categorized as either generic or name brand, but cannot be both. Generic brands have information stored on the testing standard. Name brands do not have information stored on the testing standard, but they do collect information on the effectiveness percent in most patients.