1. 5 points) For each customer including those who have made no purchases, list their cid, cname and number of purchases they have made, without using any outer join operator. That is, give a query that gives the same answer as the following but uses no outer joins.

      SELECT cid, cname, COUNT(pid)
      FROM (SELECT * FROM Customer LEFT OUTER JOIN Purchase USING (cid)) AS TEMP
      GROUP BY cid, cname;

   Queries using an outer join will get zero points.

 

 

CS 4320/5320 Homework 1Fall 2016Due September 18, 2016 at 11:59 pmThis assignment is out of 100 points and counts for 10% of your overall grade.Before you start this assignment, make sure you haveread the course policies document (availablein CMS) and completed the CMS quiz on the course policies. You must complete this quizto pass the course.We assume you are familiar with the course policies. Therefore, we don’t repeat infoabout group work, late submissions, academic integrity etc in the homework instructions.To complete the code portions of this assignment, you will need to install both MySQL(http://dev.mysql.com/downloads/mysql/) and PostgreSQL (http://www.postgresql.org/download/orhttp://postgresapp.com/). By default we will grade your code on the current release (stable) versionsof the respective platforms.Using the right DBMS:Question 1 will be graded on MySQL, question 3 on PostgreSQL. Please besure your queries run on the correct system. In particular, if you write queries for Question 1 that usePostgreSQL-only functionality, you are disregarding explicit instructions and will get zero points; you willnot receive any consideration under the breaking automation policy.1SQL and Relational Algebra queries (42 points)Consider the schema given by the following SQLCREATEstatements. (We will provide these statements toyou as a .txt file so you can avoid any typos while pasting them in.)CREATE TABLE Customer (cid INTEGER PRIMARY KEY,cname VARCHAR(20) NOT NULL);CREATE TABLE Product(pid INTEGER PRIMARY KEY,pname VARCHAR(20) NOT NULL,msrp INTEGER NOT NULL);CREATE TABLE Purchase (cid INTEGER,pid INTEGER,date DATE,price INTEGER NOT NULL,PRIMARY KEY (cid, pid, date),FOREIGN KEY (cid) REFERENCES Customer(cid),FOREIGN KEY (pid) REFERENCES Product(pid));1

Attachments:

• 1495182845-4320_hw1.pdf