CS425 Database Organization - Assignment 2: SQL
Homework Assignment 2
Question 2.1.1
Write the SQL statements for all the five relations:Agent, Managers, Purchaser, Products, and Sales. Note: (1) An appropriate data type should be used for each attribute, and (2) All the foreign keys should be created.
Question 2.1.2
Write an SQL statement that adds a constraint to the Agent relation to make sure that the salary attribute cannot be NULL, and the value of this attribute has to be between 60,000 and 300,000. Furthermore, the default value for this attribute should be 90,000.
Question 2.2.1
Write an SQL query that returns the products (pid and pname) which are sold by the direct manager(s) of Agent ‘02’ and the quantity for any purchaser (not aggregated) is greater than 250. Hint: relation Managers stores the “managing” information in the company (e.g., the Agent with Agent-id1 is the direct manager of the Agent with Agent-id2 ).
Question 2.2.2
Write an SQL query that returns the IDs of all the products, with the total ordered quantity in ‘Boston’ greater than the total ordered quantity in ‘Orlando’.
Question 2.2.3
Write an SQL query that returns the name and unit price of all the products (pname and unit_price) which are never ordered by ‘Toyota’.
Question 2.2.4
Write an SQL query that returns all the Agents and their average sales amount (of all the purchaser) for each of the products (query result: Agent-id, pid and avg_sales).
Question 2.2.5
Write an SQL query that returns all the Agents and the total number of Agents directly managed by each Agent (if no one is directly managed, then return 0).
Hint: relation Managers stores the “managing” information in the company (e.g., the agent with Agent-id1 is the direct manager of the agent with Agent-id2 ).
Question 2.2.6
Write an SQL query that returns the number of products where the average ordered quantity (of all the Agents and Purchasers) is lower than 400.
Question 2.2.7
Write an SQL query that returns the products names and the name of the purchasers with the name of thier companies(s) who ordered the lowest quantity (from all the Agents). Query result: pname and pch-name.
Question 2.2.8
Write an SQL query which returns all the Agents’ IDs and their managed Agent’ IDs.
Hint: the result should include not only directly managed agents but also indirectly managed agents.
Question 2.3.1
Delete all the purchasers without placing any order.
Question 2.3.2
We assume that a new product TV is added to the warehouse (available for sale). The unit price is $300. Add the information to the Product relation. Assume that pid is automatically maintained by the system
Question 2.3.3
Update the unit_price in the Products relation according to this rule: • if it is negative, set it to 0 • if it is larger than 8,000, then set it to 3,200 • if it is NULL, set it to 5,000 • if none of the above applies do not change the unit_price Note that we expect you to write a single statement that implements this.
