CSCI115-Assignment 2 Solved

This assignment is related to implementation of simple SELECT statements, SELECT statement with GROUP BY and HAVING clauses, SELECT statements that join and antijoin relational tables, nested SELECT statements with set membership operation, and nested SELECT statements with EXISTS/NOT EXISTS clauses.  

 

             

Tasks 
Task 1
Download a file solution1.sql and insert into the file the implementations of the following queries as SELECT statements of SQL.

Your implementation must directly follow a comment with a specification of a subtask.

 

(1)     Find the pub names which located at KING ST. or OXFORD ST..

 

(2)     Find the pub names that serve WHISKY, or VODKA, or COGNAC. Display each pub name only once.

 

(3)     Find the drinkers who have ordered drinks in January 2020. Display each drinker only once.

 

(4)     Find the drinkers who have ordered the drink WHITE WINE at LONG JOHN.

Display each drinker only once.

 

(5)     Find the drink and the rating of drinks that liked.  The results must be displayed in the descending order of the ratings, and for all drinks that have the same rating the results must be displayed in the ascending order of drinks. Display each pair only once.

 

(6)     Find the drink and the lowest price of each drink served in pubs.  

 

(7)     Find the drinker and the total number of drinks ordered by each drinker in the first three months of 2020.  

 

(8)     Find the drink and the total amount of ratings of drinks for each drink. Do not display a drink if it hasn’t been rated.

 

(9)     Find the pub and the total number of drinks served in each pub that has more than three types of drinks.

 

(10) Find the pub, the drink, and the price of pubs that drinks contain a letter E.  

When ready process a script file solution1.sql with SELECT statements.

To create a report from processing of SELECT statements open a Terminal window and start the command line interface mysql in the following way: mysql -u csit115 -p -v -c 

Next, process SQL script solution1.sql and save a report in a file solution1.rpt. Note, that when started with the options -v and -c the command line interface includes both listing of SELECT statements processed and the comments included in the original version of a file solution1.sql.

Deliverables
A file solution1.rpt with a report from processing of SQL script solution1.sql. The report must be created with the command line interface mysql, the report MUST NOT include any errors, and the report must list all SQL statements processed and all comments included in the original (downloaded) version of solution1.sql. Marks will be deducted for the missing comments. Submission of a file with a different name and/or different extension and/or different type scores no marks.

                                                                                                                                                                


 

Task 2  
Download a file solution2.sql and insert into the file the implementations of the following queries as SELECT statements of SQL.

 

Your implementation must directly follow a comment with a specification of a subtask.

 

The queries listed below must be implemented as SELECT statements with JOIN or LEFT / RIGHT OUTER JOIN operation.

 

(1)     Find the distinct drinkers who have ordered drinks at a pub on VICTORIA AVE. in March 2020.

 

(2)     Find the drinker and total number of times that the drinker rated drinks for all drinkers. Include drinkers who haven’t rated a drink.

 

(3)     Find the drinker and total amount of money spent on drinks for all drinkers in February 2020. Include drinkers who haven’t ordered a drink in this period. 

 

(4)     Find the drinkers who haven’t ordered any drinks so far.

 

(5)     Find the drinkers who haven’t order any drinks in April 2020.

 

(6)     Find the drinker and the drink liked by the drinker but the drinker hasn’t order the drink so far. Sort the drinker and the drink pairs in the ascending order of drinkers and drinks. 

 

When ready process a script file solution2.sql with SELECT statements.

To create a report from processing of SELECT statements open a Terminal window and start the command line interface mysql in the following way: mysql -u csit115 -p -v -c 

Next, process SQL script solution2.sql and save a report in a file solution2.rpt. Note, that when started with the options -v and -c the command line interface includes both listing of SELECT statements processed and the comments included in the original version of a file solution2.sql.

Deliverables
A file solution2.rpt with a report from processing of SQL script solution2.sql. The report must be created with the command line interface mysql, the report MUST NOT include any errors, and the report must list all SQL statements processed and all comments included in the original (downloaded) version of solution2.sql. Marks will be deducted for the missing comments. Submission of a file with a different name and/or different extension and/or different type scores no marks.

                                                                                                                                                 


Task 3
Download a file solution3.sql and insert into the file the implementations of the following queries as SELECT statements of SQL.

 

Your implementation must directly follow a comment with a specification of a subtask.

 

The queries listed below must be implemented as nested SELECT statements with IN/NOT IN set membership operation.

 

(1)     Find the distinct drinkers who have ordered drinks at a pub on VICTORIA AVE. in March 2020.

 

(2)     Find the drinker and the drink liked by the drinker but the drinker hasn’t order the drink so far. Sort the drinker and the drink pairs in the ascending order of drinkers and drinks. 

 

The queries listed below must be implemented as nested queries with EXISTS/NOT EXISTS clauses.

 

(3)     Find the distinct drinkers who have ordered drinks at a pub on VICTORIA AVE. in March 2020.

 

(4)     Find the drinker and the drink liked by the drinker but the drinker hasn’t order the drink so far. Sort the drinker and the drink pairs in the ascending order of drinkers and drinks.

 

A query listed below must be implemented with a set algebra operation.

 

(5) Find the distinct drinkers who like either BEER or RED WINE.

 

A query listed below must be implemented as a nested query.

 

(6) Find the distinct drinkers that ordered both VODKA and WHISKY.

 

When ready process a script file solution3.sql with SELECT statements.

To create a report from processing of SELECT statements open a Terminal window and start the command line interface mysql in the following way: mysql -u csit115 -p -v -c 

Next, process SQL script solution3.sql and save a report in a file solution3.rpt. Note, that when started with the options -v and -c the command line interface includes both listing of SELECT statements processed and the comments included in the original version of a file solution3.sql