Table : PURCHASE_ORDER ORDER_NO AMOUNT ORDER DATE CUSTOMER_ID SALESMAN ID DELIVERY_ DATE 1001 300 05-03-2017 2001 3001 05-05-2017 1002 200.20 10-07-2017 2002 3003 10-10-2017 1003 600 12-06-2017 2005 3002 12-08-2017 1004 400.25 10-05-2017 2003 3004 10-07-2017 1005 500.10 03-08-2017 2004 3005 03-05-2017 1006 400 10-07-2017 2005 3006 10-10-2017 Table: PURCHASE DETAILS ORDER_NO PRODUCT_NAME PRICE QUANTITY 1001 KEYBOARD 56 100 1002 MONITOR 36 200 1003 MOUSE 6 00 1004 HARD DISK 58 300 1005 RAM 25 500 1007 CABINET 100 a) Write a SQL query to display PRODUCT_NAME, ORDER_DATE and find number of years between the DELIVERY_DATE from the CURRENT DATE. [Hint: Use ON Clause). b) Write a SQL query to display matched records from PURCHASE_ORDER table and all records from PURCHASE_DETAILS. c) Write an SQL query to display ORDER NO, PRODUCT_NAME from table ORDER_DETAILS of products where the price is less than average price of products. (Hint: Use Subqueryl-

Table : PURCHASE_ORDER ORDER_NO AMOUNT ORDER DATE CUSTOMER_ID SALESMAN ID DELIVERY_ DATE 1001 300 05-03-2017 2001 3001 05-05-2017 1002 200.20 10-07-2017 2002 3003 10-10-2017 1003 600 12-06-2017 2005 3002 12-08-2017 1004 400.25 10-05-2017 2003 3004 10-07-2017 1005 500.10 03-08-2017 2004 3005 03-05-2017 1006 400 10-07-2017 2005 3006 10-10-2017 Table: PURCHASE DETAILS ORDER_NO PRODUCT_NAME PRICE QUANTITY 1001 KEYBOARD 56 100 1002 MONITOR 36 200 1003 MOUSE 6 00 1004 HARD DISK 58 300 1005 RAM 25 500 1007 CABINET 100 a) Write a SQL query to display PRODUCT_NAME, ORDER_DATE and find number of years between the DELIVERY_DATE from the CURRENT DATE. [Hint: Use ON Clause). b) Write a SQL query to display matched records from PURCHASE_ORDER table and all records from PURCHASE_DETAILS. c) Write an SQL query to display ORDER NO, PRODUCT_NAME from table ORDER_DETAILS of products where the price is less than average price of products. (Hint: Use Subqueryl-

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

For each product, show the product ID and the number of orders placed.Show the results in decreasing order based on the number of times the product has been ordered, with NumOrders as the result column.
Create a table named company with these columns: Company_id varchar(255) Company_name varchar(255) default ‘x’ Hq_phone_number varchar(255) Primary key of company_id (please use constraint format) Unique key of Hq_phone_number (please use constraint format)
The DEPARTMENTS table has the following data: DEPARTMENT ID DEPARTMENT NAME MANAGER ID LOCATION_ID 10 Administration 200 1700 20 Marketing 201 1800 50 Shipping 124 1500 60 IT 103 1400 80 Sales 149 2500 What will be the value in the first row of the result set when the following query is executed? SELECT manager_id FROM departments ORDER BY location_id; O a. 201 O b. 149 O c. 103 O d. 200
Write Create Table SQL statements Work Session table The table must have a primary key. The table must have appropriate foreign key constraints. Foreign keys must have the same name, datatype and size of the primary key that they refer to The following columns datatypes and sizes and named constraints must be used Column DataType Constraint WorkYear number(4) Range 2019 - 2020 WorkWeek number(2) Range 1-52 WorkHours number(4,2) Range 0.5 – 99.99 BID NUMBER(4) ,AUTHID NUMBER(4)
SQL: Dog DataIn each question below, you will define a new table based on the following tables.CREATE TABLE parents ASSELECT "abraham" AS parent, "barack" AS child UNIONSELECT "abraham" , "clinton" UNIONSELECT "delano" , "herbert" UNIONSELECT "fillmore" , "abraham" UNIONSELECT "fillmore" , "delano" UNIONSELECT "fillmore" , "grover" UNIONSELECT "eisenhower" , "fillmore";CREATE TABLE dogs ASSELECT "abraham" AS name, "long" AS fur, 26 AS height UNIONSELECT "barack" , "short" , 52 UNIONSELECT "clinton" , "long" , 47 UNIONSELECT "delano" , "long" , 46 UNIONSELECT "eisenhower" , "short" , 35 UNIONSELECT "fillmore" , "curly" , 32 UNIONSELECT "grover" , "short" , 28 UNIONSELECT "herbert" , "curly" , 31;CREATE TABLE sizes ASSELECT "toy" AS size, 24 AS min, 28 AS max UNIONSELECT "mini" , 28 , 35 UNIONSELECT "medium" , 35 , 45 UNIONSELECT "standard" , 45 , 60;Q1: Size of DogsThe Fédération Cynologique Internationale classifies a standard poodle as over 45 cm and up to 60 cm.The sizes table…
Q5) What is the normalization of the following table, where the primary keys are underlined: Note: all the data in cells of the table are already atomic. OrderID Order Date 1006 1006 10/24/2010 1006 1007 10/24/2010 1007 10/24/2010 10/25/2010 10/25/2010 Customer Customer Customer ProductID Product ID Name Address Description 2 2 2 6 6 Value Furniture Value Furniture Value Furniture Plano, TX Plano, TX Plano, TX Furniture Boulder, Gallery CO Furniture Boulder, Gallery CO 7 5 4 11 Dining Table Writer's Desk Product Product Finish Standard Price 4-Dr Dresser Natural 800.00 Ash Cherry 325.00 Entertainment Natural 650.00 Center Maple 500.00 4 Entertainment Natural 650.00 Center Maple Ordered Quantity 2 2 1 4 3
help please Create a table named BRANCH. The table has the following structureBRANCH_NUM decimal(2,0) NOT NULL default '0'BRANCH_NAME char(50) default NULLBRANCH_LOCATION char(50) default NULLNUM_EMPLOYEES decimal(2,0) deault NULL PRIMARY KEY (`BRANCH_NUM`) (Please type answer no write by hand)
Databise Design Describe three SQL queries to provide information to the end users for this system. At least one must be a join. These can be real SQL statements or written in English Table Name Attribute Description Data Type Null Keys Sales Sales_ID Contains unique Sales ID INT No Primary key Product_Price Price of the Product VARCHAR(30) No Sales_Date Sales of the Date VARCHAR(30) No Quantity Quantity of product DATE No Personal_ID Personal_ID ofSales INT No Foreign key Cashier_ID Cashier_ID of Sales INT No Foreign key Table Name Attribute Description Data Type Null Keys Product Product ID ID of the Product INT No Primary key Product_name Name of the Product VARCHAR(50) No Brand_name Brand of the name VARCHAR(50) No Quantity Quantity VARCHAR(20) No Pharmaceutical_ID Pharmaceutical of the ID INT…
Product Order PID Description Quantity 300 Price Vendor OID Date 2012/5/23 2 CID Unique traders AT Cosmetics General traders Unique traders General traders Foodie Shop Shampoo 50 1 1 2 Soap 140 230 40 30 2012/5/24 3 Detergent 4 2016/4/23 4 Milk 14 33 3 2016/2/12 2016/1/10 4 Lemon Max 10 20 6 1 6. Chocolate 34 34 Invoice Customer OrderNo 1 ProductID Quantity CustomerID Name city 1 1 Ali Rawalpindi 1 3 1 Ahmad Islamabad 4 2 1 Umer Chakwal 2 6. 3 Каrim Islamabad 2 3 1 Shafig Islamabad 3 2 6. Waleed Rawat 4 Afzal Islamabad 4 3 4 1 Bilal Rawalpindi 4 4 Write SQL queries for the following statements (Use only Cartesian product and join) 1. Showname of customerand orderno who have placed any order and belong to "Islamabad". 2. Showname of customer and product description who placed order before 2016/2/23. 3. Show data of those customer who place two or more than two order in same day. 4. Showdata of those product that are purchased by those customer who neither belong 5. To"Rawat"nor…