Assignment_4 SQL Queries:Solution

Description

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 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	|

|	22	|
|	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