Answered: 3. List all product categories and…

Engineering

Computer Science

3. List all product categories and number of products sold for each category PRODUCT_CATEGORY QTY_SOLD

Oracle 12c: SQL

3rd Edition

ISBN:9781305251038

Author:Joan Casteel

Publisher:Joan Casteel

Chapter4: Constraints

Section: Chapter Questions

Problem 7RQ: When must you define constraints at the table level rather than the column level?

See similar textbooks

Similar questions

Examine this product table's column definitions:pid number primary keypname varchar2(50)You must display column headings as shown:Product_ID Product NameWhich SELECT statement does this? SELECT pid Product_ID, pname Product Name FROM product; SELECT pid Product_ID, pname "Product Name" FROM product; SELECT pid @Product_ID, pname @"Product Name" FROM product; SELECT pid AS Product_ID, pname AS Product Name FROM product
1. Use the following table to answer the questions below: Table name: Products Column Data Type Length Constraint Role Name Stores the ID of a product P ID P_Name Primary key Number Stores the name of a Varchar2 25 product P_entryDate Stores the entry date of a product Date Cannot be null P_Expiry Stores the expiry date of a product Date P_Еxpiry date should be greater than P_entryDate P_Price Stores the price of a product Number 3,2 Cannot be null a. Write a SQL query that displays the mumber of products that will expire in the year 2021 b. Write in SQL a query that displays the ids of the most expensive products c. Create a view named ProductDetails' to hold all the details of products whose names start with 'DELL' and their prices are between 200 and 500 Omani Rials
Product_id Product_name Category Mfg_date Exp_Dte Product_price 01 Dairy Milk Choclate Junk 2,Aug, 2019 2aug, 2020 80 02 Lipton Tea Bags Not junk 2,jan 2019 2, jan 2020 160 03 Kurkure junk 2, april 2919 2, april, 2021 30 04 Shezan Juice Junk 3,Aug, 2019 3,aug, 2020 30 06 Olpers Milk Not junk 3,april, 2018 3,april, 2020 350 Canteen_table 1: Write query to create canteen_table. Put check on category values as only “junk” and “Not junk”. Change colum name of product_price to unit_price. Display products manufactured between 1, jan 2019 to 1, May 2019. Display count of only junk food items in canteen_table with alias name as “junk Food Count”. Display Count Of both Junk food and not junk food products whose count is greater then 1.
5. In the OrderItems table, display a count of the number of products where item_price is greater than or equal to $6, grouped by prod_id, but only for those groups having 3 or more of a given prod_id. Display the count with the alias shown. Sort by prod_id in ascending order. BRO3 1 rows returned in 0.03 seconds PROD ID Download 4 NUM PROD ID
Select all the staff full names with the customer full names of the consignments they have worked on. Include staff that have not worked on any consignments
- The HR department needs a report to display employee information as shown from the job_history table. The months worked is the number of months from the start date to the end date. Only list those employees whose months worked is an even number. Order by months worked descending
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 Display the Product ID, Description, and Quantity (QTY) of all the products in the PRODUCTS table whose PID is from 801 to 802 and whose quantity is less than 15.
Table name: Products Column Role Data Type Length Constraint Name P ID Stores the ID of a product Number 8. Primary key P Name Stores the name of a product Varchar2 25 P_entryDate Stores the entry date of a product Stores the expiry date of a product Date Cannot be null P_Expiry P Expiry date should be greater than P entryDate Date P Price Stores the price of a product Number 3,2 Cannot be null a) Write in SQL a command that creates the table Products according to the above description (2 marks). b) Write in SQL a statement that adds to the table Products a new column named supplierID of type Number(5). This column should be defined as a foreign key that relates the table Products to the table Supplier (1.5 marks). c) Write in SQL a query that lists all the details of products arranged in descending order according to the entry date (1.5 marks).
For each different job_id group in the employee table, show the average salary and job_id for the group whose average salary is greater than 6000 and order records according the average salary attribute
Data normalisation
NOTE: Use the attached .SQL to create tables and insert data. Relational Diagram Database name: Ch07_ConstructCo ASSIGNMENT V ASSIGN NUM Table name: EMPLOYEE EMP_NUM EMP_LNAME EMP_FNAME EMP_INITIAL EMP_HIREDATE JOB_CODE EMP_YEARS JOB V JOB CODE JOB DESCRIPTION JOB CHG HOUR JOB_LAST_UPDATE ASSIGN DATE PROJ_NUM EMP NUM 08-Nov-00 502 12-Jul-89 501 01-Dec-96 503 12 23 16 101 News John G 102 Senior Arbough Ramoras David ASSIGN JOR 103 June E ASSIGN_CHG HR ASSIGN_HOURS 104 Anne 15-Nov-87 501 25 19 8 19 105 Johnson Alice K 01-Feb-93 502 ASSIGN_CHARGE EMPLOYEE 9 EMP NUM EMP LNAME EMP FNAME EMP INITIAL PROJECT 9 PROI NUM PROJ NAME PROJ_VALUE PROI_BALANCE EMP NUM 106 Smithfield William 22-Jun-04 500 10-Oct-93 500 107 Alonzo Maria 22-Aug-91 501 18-Jul-97 501 108 Washington Smith Ralph Larry Gerald B 21 109 15 EMP HIREDATE 110 Olenko A 11-Dec-95 505 17 JOB CODE EMP VEARS 04-Apr-91 506 23-Oct-94 507 111 Wabash Geoff B 21 112 Smithson Darlene 18 16 19 M 113 Delbet Annelise Joenbrood 15-Nov-96 508…
Q9) find the least expensive priceQ10) List the UNIQUE v_code of vendors that provide productsQ11) find the most expensive priceQ12) List the number of products by vendor with the average price, include only the rows withaverage price below 10.00Q13) List all vendor rows (including the ones that have no matching products) and all matchingproduct rows

SEE MORE QUESTIONS

Recommended textbooks for you

Oracle 12c: SQL

Computer Science

ISBN:

9781305251038

Author:

Joan Casteel

Publisher:

Cengage Learning

Np Ms Office 365/Excel 2016 I Ntermed

Computer Science

ISBN:

9781337508841

Author:

Carey

Publisher:

Cengage

A Guide to SQL

Computer Science

ISBN:

9781111527273

Author:

Philip J. Pratt

Publisher:

Course Technology Ptr

Database Systems: Design, Implementation, & Manag…

Computer Science

ISBN:

9781305627482

Author:

Carlos Coronel, Steven Morris

Publisher:

Cengage Learning

Database Systems: Design, Implementation, & Manag…

Computer Science

ISBN:

9781285196145

Author:

Steven, Steven Morris, Carlos Coronel, Carlos, Coronel, Carlos; Morris, Carlos Coronel and Steven Morris, Carlos Coronel; Steven Morris, Steven Morris; Carlos Coronel

Publisher:

Cengage Learning

CMPTR

Computer Science

ISBN:

9781337681872

Author:

PINARD

Publisher:

Cengage

Oracle 12c: SQL

Computer Science

ISBN:

9781305251038

Author:

Joan Casteel

Publisher:

Cengage Learning

Np Ms Office 365/Excel 2016 I Ntermed

Computer Science

ISBN:

9781337508841

Author:

Carey

Publisher:

Cengage

A Guide to SQL

Computer Science

ISBN:

9781111527273

Author:

Philip J. Pratt

Publisher:

Course Technology Ptr

Database Systems: Design, Implementation, & Manag…

Computer Science

ISBN:

9781305627482

Author:

Carlos Coronel, Steven Morris

Publisher:

Cengage Learning

Database Systems: Design, Implementation, & Manag…

Computer Science

ISBN:

9781285196145

Author:

Steven, Steven Morris, Carlos Coronel, Carlos, Coronel, Carlos; Morris, Carlos Coronel and Steven Morris, Carlos Coronel; Steven Morris, Steven Morris; Carlos Coronel

Publisher:

Cengage Learning

CMPTR

Computer Science

ISBN:

9781337681872

Author:

PINARD

Publisher:

Cengage

SEE MORE TEXTBOOKS