Assignment 4 SQL Queries

CS 122A Course Assignment 4
SQL Queries

SQL Queries (100 points)
For answering the following questions, use the script provided to you. The script contains the schema and a few
tuples for tables that you can use. It is important to follow good indent style. For example, every clause must be
on a different line, and a nested SQL query must be written as a nested block (e.g., if..else statement in
programming). If the grader can not easily read the query, he/she will deduct all points. Express in SQL
(MySQL) all the following queries. Your query should execute in MySQL and generate the desired result. If
your query doesn’t execute, points will be deducted. Please make sure you go through instructions and
deliverables described at the end of the assignment and follow them.
The order of the results is not important
1. Find names and school names of the faculty members whose research area is ‘RA1’
+----------+---------------------+
| Name | school_name |
+----------+---------------------+
| Jaylon | Nursing: Doctorate |
| Virginia | Biological Sciences |
| Kayleigh | Social Work |
| Myron | Computer Science |
| Alf | Public Health |
| Carlo | Political Science |
+----------+---------------------+
2. Find the names of all buildings with at least 1 compost bin
+-------------+
| NAME |
+-------------+
| Building 19 |
| Building 82 |
| Building 17 |
| Building 05 |
| Building 02 |
| Building 68 |
| Building 67 |
| Building 39 |
| Building 08 |
| Building 24 |
| Building 64 |
| Building 56 |
| Building 30 |
| Building 88 |
| Building 20 |
+-------------+
3. Find the distinct waste_bin_id and location of bins whose load sensor records a weight to be higher than
the capacity of the bin after the last collection time which was ‘2019-10-26 13:00:00’
+--------------+------+------+
| waste_bin_id | x | y |
+--------------+------+------+
| 43 | 4866 | 966 |
| 85 | 8009 | 6644 |
| 95 | 3078 | 9798 |
| 84 | 2022 | 8246 |
| 29 | 8804 | 3589 |
| 16 | 5511 | 9138 |
| 81 | 3214 | 6923 |
| 56 | 5904 | 9388 |
| 110 | 5192 | 802 |
| 131 | 7295 | 8428 |
| 19 | 4178 | 2249 |
| 17 | 2689 | 6546 |
| 125 | 9031 | 9311 |
| 147 | 2962 | 574 |
| 79 | 2935 | 657 |
| 89 | 2200 | 8791 |
| 60 | 3003 | 615 |
| 133 | 2690 | 6547 |
| 96 | 6160 | 403 |
| 36 | 3969 | 3575 |
| 13 | 5397 | 3514 |
| 139 | 9729 | 2616 |
| 113 | 9355 | 9907 |
| 144 | 5837 | 9019 |
| 46 | 1084 | 9813 |
| 15 | 3933 | 6348 |
+--------------+------+------+
4. Find names of users that used a waste bin between ‘2019-10-26 14:00:00’ and ‘2019-10-26 15:00:00’
+---------+
| user_id |
+---------+
| 86 |
| 109 |
| 158 |
| 100 |
| 181 |
| 38 |
| 3 |
| 39 |
| 120 |
| 21 |
| 193 |
| 30 |
| 199 |
| 176 |
| 14 |
| 178 |
| 18 |
| 94 |
| 169 |
| 20 |
| 65 |
| 162 |
| 88 |
| 7 |
| 125 |
| 112 |
| 103 |
| 31 |
| 107 |
| 142 |
| 196 |
| 111 |
| 173 |
| 183 |
+---------+
5. Find the Inside bins (bins inside the buildings) that are used by Visitors between ‘2019-10-26 14:00:00’
and ‘2019-10-26 15:00:00’
+--------------+
| waste_bin_id |
+--------------+
| 143 |
| 51 |
| 149 |
| 150 |
| 35 |
| 28 |
+--------------+
6. Find the distinct names of all the students who used a recycling bin incorrectly (put a wrong item in the
bin) at least once between ‘2019-10-26 14:00:00’ and ‘2019-10-26 15:00:00’ . Remember that
ObjectRecognitionSensor records a trash_type which can be used to determine if someone incorrectly throws a
wrong type of trash in a waste bin.
+-----------+
| NAME |
+-----------+
| Junior |
| Gunner |
| Damian |
| Donnie |
| Darien |
| Billie |
| Blair |
| Chester |
| Eloisa |
| Maxine |
| Justyn |
| Lucile |
| Loy |
| Reba |
| Mina |
| Aditya |
| Leanne |
| Rosario |
| Blanche |
| Michelle |
| Florine |
| Martin |
| Cora |
| Sadye |
| Ray |
| Paolo |
| Catharine |
| Victor |
| Ova |
| Rowland |
| Antonette |
| Alexandro |
| Magnus |
| Freida |
| Jack |
| Erling |
| Ubaldo |
| David |
| Jeffry |
| Jody |
| Stewart |
| Chaya |
| Sherwood |
| Paris |
| Nico |
| Adelbert |
| Brianne |
| Keely |
+-----------+
7. Find the users who had more than 100 landfill disposal events
+---------+
| user_id |
+---------+
| 1 |
| 5 |
| 16 |
| 18 |
| 25 |
| 26 |
| 38 |
| 44 |
| 53 |
| 55 |
| 61 |
| 62 |
| 76 |
| 81 |
| 100 |
| 101 |
| 104 |
| 106 |
| 107 |
| 109 |
| 111 |
| 112 |
| 114 |
| 119 |
| 137 |
| 139 |
| 140 |
| 148 |
| 151 |
| 154 |
| 158 |
| 165 |
| 169 |
| 178 |
| 181 |
| 183 |
| 191 |
| 193 |
| 194 |
| 196 |
+---------+
8. Find the users who have never used any recycling bin.
+---------+
| user_id |
+---------+
| 8 |
| 13 |
| 17 |
| 2
2 | | 27 | | 28 | | 33 | | 34 | | 35 | | 37 | | 46 | | 49 | | 56 | | 60 | | 63 | | 66 | | 67 | | 70 | | 71 | | 73 | | 79 | | 82 | | 84 | | 87 | | 91 | | 93 | | 95 | | 97 | | 113 | | 117 | | 118 | | 122 | | 123 | | 127 | | 128 | | 129 | | 132 | | 135 | | 141 | | 147 | | 149 | | 150 | | 153 | | 157 | | 164 | | 166 |
| 167 |
| 168 |
| 171 |
| 174 |
| 177 |
| 182 |
| 184 |
| 189 |
| 190 |
| 192 |
+---------+
9. Select number of waste disposal events per building for a month (2019-10-01 13:00:00’ and
‘2019-10-31 15:00:00’)
+-------------+----------+
| name | count(*) |
+-------------+----------+
| Building 02 | 324 |
| Building 05 | 4193 |
| Building 08 | 1650 |
| Building 12 | 2257 |
| Building 17 | 1317 |
| Building 19 | 1582 |
| Building 20 | 292 |
| Building 24 | 962 |
| Building 30 | 1655 |
| Building 39 | 1269 |
| Building 41 | 661 |
| Building 46 | 1933 |
| Building 52 | 966 |
| Building 55 | 634 |
| Building 56 | 318 |
| Building 65 | 932 |
| Building 68 | 641 |
| Building 82 | 2579 |
| Building 83 | 614 |
| Building 88 | 691 |
+-------------+----------+
10. Print top 10 users - user_id and their rank - in ascending order of their ranks where rank is given by the
total compost trash disposed by them. (partial credit for just printing the top 10 user_id)
+---------+------+
| user_id | Rank |
+---------+------+
| 55 | 1 |
| 61 | 2 |
| 140 | 3 |
| 183 | 4 |
| 194 | 5 |
| 151 | 6 |
| 165 | 7 |
| 193 | 8 |
| 100 | 9 |
| 169 | 10 |
+---------+------+
Deliverables
Your assignment has to be submitted to gradescope. Only one team member has to submit following three
files :
1. SQL script containing the SQL statements - which can be executed on MySQL command line processor.
The name of the sql file should be last names of each team member placed together. For example if
Edgar Codd, Donald Chamberlin and Peter Chen were teammates, they would submit:
codd_chamberlin_chen_assignment4_script.sql.
2. A PDF file named with your queries and result of each query. The name of the pdf file should be last
names of each team member placed together. For example if Edgar Codd, Donald Chamberlin and Peter
Chen were teammates, they would submit codd_chamberlin_chen_assignment4.pdf.
3. The file containing containing the results to the SQL queries which has to be created following the
instructions given under Creating the result of a SQL file.
Instructions
Loading the data
As a first step you have to load the database with data we have provided in the file schema_data.sql. To do so
follow these instructions.
1. Open the terminal
2. Create the database cs122a_test if it doesn’t exist already by executing create database
cs_122a_test
4. Type in mysql -u root cs122a_test < schema_data.sql. Use the full path to the file schema_data.sql
Alternatively you are already running mysql, you can execute the following commands to load the data
mysql use cs122a_test;
mysql source schema_data.sql
mysql \. schema_data.sql
More detailed instructions can be found here.
Creating the result of a SQL file
You need to create a TXT file to include your queries and its results. Points may be deducted if you don’t follow
the instructions.Please do not copy text and type each command. When copying text from a PDF file in
the terminal, it does not work sometimes.
1. Open a blank text file in a text editor and copy the following template into the file. Then, paste your SQL
statements after the “-- Paste ..” comment.
DROP DATABASE IF EXISTS `cs122a_test`;
CREATE DATABASE `cs122a_test`;
USE `cs122a_test`;
-- Paste your SQL statements in the below
2. Save it as “script.sql”.
3. You are going to use mysql command-line tool (http://dev.mysql.com/doc/refman/5.0/en/mysql.html) to
execute your script and generate an output. Execute the following command to load your SQL script and
generate an output. Here, we assume that “script.sql” is located in your home directory. The result file name
should be script_output.txt. Do not convert it to other format such as DOC or PDF. In the command prompt (or
terminal), execute the following command (not after executing mysql). In case you cannot run below command,
please run script.sql and copy the output to script_output.txt file.
mysql --force --comments -v -v -u root < yourHomePath/script.sql yourHomePath/script_output.txt
Do not omit any options. Especially the option –v is repeated twice on purpose. If the root account has a
password, use the following command.
mysql --force --comments -v -v -u root -pYOURPASSWORD < yourHomePath/script.sql
yourHomePath/script_output.txt
* OS Specific instructions -
Windows
1. Put your script in a folder that your account can access. (e.g., d:\)
2. Open a command prompt (cmd) and go to the MySQL folder.
cd C:\Program Files (x86)\MySQL\MySQL Server 8.0\bin
3. Execute the following command.
mysql --force --comments -v -v -u root < d:\script.sql d:\script_output.txt
OS X
1. Put your script in your home directory. (e.g., /Users/youraccount)
2. Open a terminal and execute the following command.
/usr/local/mysql/bin/mysql --force --comments -v -v -u root < /Users/youraccount/script.sql
/Users/youraccount/script_output.txt
If you have a problem to execute mysql tool, here is the default location that you can find it. It might be a good
idea to move script.sql to a folder that your account has an access like the above.
OS X: /usr/local/mysql/bin/mysql
Windows: C:\Program Files (x86)\MySQL\MySQL Server 8.0\bin
Refer to the following guideline for the mysql command-line tool.
For OS X | For Windows