CREATE TABLE PRODUCT (PRODUCT_ID INTEGER PRIMARY KEY, "NAME" TEXT NOT NULL, PRICE REAL NOT NULL); CREATE TABLE CATEGORY (CATEGORY_ID INTEGER PRIMARY KEY, "NAME" TEXT NOT NULL); CREATE TABLE PRODUCT_CATEGORY (PRODUCT_ID INTEGER, CATEGORY_ID INTEGER, PRIMARY KEY (PRODUCT_ID, CATEGORY_ID), FOREIGN KEY (PRODUCT_ID) REFERENCES PRODUCT (PRODUCT_ID), FOREIGN KEY (CATEGORY_ID) REFERENCES CATEGORY (CATEGORY_ID)); Please write a SQL SELECT statement (a query) which will return the PRODUCT_ID and PRICE of the second highest priced product for *every category*. If there is a tie for the second highest price in a category, all of the tied products should be returned. The columns in your query results should be: CATEGORY_ID, PRODUCT_ID, PRICE

CREATE TABLE PRODUCT (PRODUCT_ID INTEGER PRIMARY KEY, "NAME" TEXT NOT NULL, PRICE REAL NOT NULL); CREATE TABLE CATEGORY (CATEGORY_ID INTEGER PRIMARY KEY, "NAME" TEXT NOT NULL); CREATE TABLE PRODUCT_CATEGORY (PRODUCT_ID INTEGER, CATEGORY_ID INTEGER, PRIMARY KEY (PRODUCT_ID, CATEGORY_ID), FOREIGN KEY (PRODUCT_ID) REFERENCES PRODUCT (PRODUCT_ID), FOREIGN KEY (CATEGORY_ID) REFERENCES CATEGORY (CATEGORY_ID)); Please write a SQL SELECT statement (a query) which will return the PRODUCT_ID and PRICE of the second highest priced product for every category. If there is a tie for the second highest price in a category, all of the tied products should be returned. The columns in your query results should be: CATEGORY_ID, PRODUCT_ID, PRICE

Database System Concepts

7th Edition

ISBN:9780078022159

Author:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Publisher:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Chapter1: Introduction

Section: Chapter Questions

Problem 1PE

See similar textbooks

Similar questions

Please provide step-by-step in Microsoft Access for the following problem. Create a new query in Query Design View based on the Consultant table with the following instructions to test the new custom function, the Travel function, as used within a query: Use LastName, Reside, and Salary fields from the Consultant Create a new calculated field named TravelExpense that uses the new Travel function with the Reside field for the CountryValue argument and the Salary field for the SalaryValue Save the query with the name TravelCalculation and then display it in Datasheet View to test your new custom function and calculated field. Close the TravelCalculation query.
8. Using the Henry Books database, list the book code and title of every book that has thetype SFI or that has the publisher code PE. Insert your snip of the query and resultset here:9. Using the Henry Books database, list the book title and book code for every book publishedby Putnam Publishing Group that has a book price greater than $15. Insert your snip of thequery and resultset here:10. Using the Henry Books database, list the sum of books by branch. The branch numberand number of books should display. Order by branch number. Insert your snip of the queryand resultset here:
Using the Winners table from Figure 12-2, write a parameter query that selects theActor and Actress fields for the year provided by the user.
CREATE TABLE sales ( SalesNumber INT(10) AUTO_INCREMENT PRIMARY KEY, SalesDate DATE, SalesTotal DECIMAL(10,2)); CREATE TABLE products ( number INT(11) AUTO_INCREMENT PRIMARY KEY, prodid VARCHAR(20) UNIQUE, prodname VARCHAR(30), price DECIMAL(10,2), onhand INT(11), CONSTRAINT fk_salesdetails_products FOREIGN KEY (prodid) REFERENCES salesdetails(prodid) ON DELETE RESTRICT); CREATE TABLE salesdetails ( number INT(10) AUTO_INCREMENT PRIMARY KEY, SalesNumber INT(10), prodid VARCHAR(20), price DECIMAL(7,2), qty INT(10), CONSTRAINT fk_salesdetails_sales FOREIGN KEY (SalesNumber) REFERENCES sales(SalesNumber) ON DELETE CASCADE, CONSTRAINT fk_salesdetails_products FOREIGN KEY (prodid) REFERENCES products(prodid) ON DELETE CASCADE); Using the above SQL and the schema diagram, create an ER diagram detailing all the fields of the tables and the relationship amount the tables.
Practice Question 6
Create formulas that provide this information as follows: In cell H8, create a formula using the DCOUNT function to count the number of projects with funding amounts less than $5,000, using the Lookup table (range A1:E23) as the database, "Funding" as the field, and the range G6:G7 as the criteria. In cell H13, create a formula using the DAVERAGE function to average the funding amounts for Commercial projects, using the Lookup table (range A1:E23) as the database, "Funding" as the field, and the range G11:G12 as the criteria.
Task 6: The Car Maintenance team considered that the available maintenance tasks should also have the price information in the database. Alter the MAINTENANCE_TYPES table to include a new column named MAINTENANCE_PRICE of type FLOAT. ANSWER IN MYSQL PLEASE
For this problem create a (temporary) table called instructor_course_nums. Write a procedure that accepts an instructor ID as input. The procedure calculates the total number of course sections taught by that instructor, and adds a tuple to the temporary table consisting of the instructors ID number, name, and total courses taught - call these attributes: ID, name, and tot_courses. If the instructor already has an entry in the table,then the procedure makes sure the total number of courses taught in the temporary table is up-to-date. You must name your procedure: Written in PostgreSQL
PLZ HELP WITH THE FOLLOWING: make the following work select MOVIE_NAME, MOVIE_YEAR from MOVIE where MOVIE_COST > (select a.MOVIE_COST from MOVIE a, PRICE b where a.PRICE_CODE = b.PRICE_CODE and b.PRICE_DESC ="weekly Special"); CREATE TABLE MEMBERSHIP ( MEM_NUM CHAR(3) CONSTRAINT MEMBER_MEMNUM_PK PRIMARY KEY, MEM_FNAME VARCHAR(30) NOT NULL, MEM_LNAME VARCHAR(30) NOT NULL, MEM_STREET VARCHAR(30), MEM_CITY VARCHAR(10), MEM_STATE CHAR(2), MEM_ZIP CHAR(5), MEM_BALANCE NUMBER(2) ); CREATE TABLE RENTAL ( RENT_NUM CHAR(4) CONSTRAINT RENTAL_RENTNUM_PK PRIMARY KEY, RENT_DATE DATE, MEM_NUM CHAR(3), CONSTRAINT RENTAL_MEMNUM_FK FOREIGN KEY (MEM_NUM) REFERENCES MEMBERSHIP ); CREATE TABLE PRICE ( PRICE_CODE CHAR(1) CONSTRAINT PRICE_PRICECODE_PK PRIMARY KEY, PRICE_DESC VARCHAR(20), PRICE_RENTFEE NUMBER (3, 1), PRICE_DAILYATFEE NUMBER(3, 1) ); CREATE TABLE MOVIE ( MOVIE_NUM CHAR(4) CONSTRAINT MOVIE_MOVIENUM_PK PRIMARY KEY, MOVIE_NAME VARCHAR(30) NOT NULL,…