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

CREATE TABLE employees8(employee_id NUMBER(10) PRIMARY KEY,first_name VARCHAR(25) UNIQUE,last_name VARCHAR(25) UNIQUE,job_id NUMBER(3),salary NUMBER(6) NOT NULL,department_name VARCHAR2(20));CREATE TABLE jobs8(job_id NUMBER(3) PRIMARY KEY,job_title VARCHAR2(25),min_salary NUMBER(4)check(min_salary >=2900),max_salary NUMBER(6)check(max_salary<=100000)); CREATE TABLE departments8(department_id NUMBER(2) PRIMARY KEY,department_name VARCHAR2(30),location_name VARCHAR2(15) DEFAULT 'Istanbul'); how can I do this question according to these 3 tables ? Create a complex view that joining with three tables.
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
Table Name: SUPPLIERS (.5) FIELD NAME DATA TYPE CONSTRAINT DESCRIPTION SID NUMBER(5) PRIMARY KEY Supplier ID SNAME VARCHAR2(30) NOT NULL Supplier Name STYPE VARCHAR2(30) WS, DS Supplier Type LOCATION VARCHAR2(30) Location FIELD NAME DATA TYPE CONSTRAINT DESCRIPTION PID NUMBER (5) PRIMARY KEY Product ID DESC VARCHAR2(30) NOT NULL Description PRICE NUMBER(5) Price SID NUMBER(5) FOREIGN KEY Supplier ID Insert the given data in SUPPLIERS and PRODUCTS tables: SUPPLIERS SID SNAME STYPE LOCATION 1 Lulu DS Salalah 2 C4 DS Saadah 3 Smile WS Awqad PRODUCTS PID DESC PRICE SID QTY 801 Pencil 3 1 10 802 A4 6 2 15 803 Ballpen 4 2 804 Marker 2 3 20
In Structure Query Information Select all columns from the jobs table using an asterisk.2. Select only job title, min salary, and max salary columns from the jobs table.3. Select all columns from the d_partners table by writing out all of the columns inthe select statement.4. Using the d_partners table, display the id, and then concatenate the first_nameand last_name together with a space in between the two columns. Give theconcatenated column an alias of NAME. Be sure to use the optional ASkeyword.5. Using the job_grades table, show the grade level, and then subtract thelowest_sal from the highest_sal to show the amount of variance there is for eachgrade level. Give the calculated column an alias of Salary_Variance. Do notuse the optional AS keyword.6. Using the employees table, show an unduplicated list of department_id’s.7. Change the SELECT statement you built for question 4, adding a single columncalled Partner_Information that concatenates the partner’s…
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,…
question 5 please use the picture below to answer the following question Assuming all other tables are already created, write a SQL statement to create the following tables and the given constraint(s).
Q19) List all product rows (including the ones that have no matching vendors) and all matchingvendor rowsQ20) 2What product(s) have the highest inventory value (P_QOH * P_PRICE)Q21)?What products have a price that exceeds the average product priceQ22) 2What is the average price for each sale codeQ23) ?What is the average product price
1) List all the functional dependencies of the above table. (Note, this requires you to state all the functional dependencies, not just count the number). 2) What normal form(s) is the table currently in? Justify your answer.