For each city, list number of customers from the city, who have placed order(s). Cities are listed in ascending alphabetical order. Use equal join for this query.
CREATE TABLE customer ( cust_id number(11,0) not null, cust_name varchar2(25) not null, street varchar2(30), city varchar2(20), state varchar2(2), zipcode varchar2(5), CONSTRAINT customer_pk PRIMARY KEY (cust_id) ); CREATE TABLE ordertable ( order_id number(11,0) not null, order_date date, cust_id number(11,0), CONSTRAINT order_pk PRIMARY KEY (order_id), CONSTRAINT order_fk FOREIGN KEY (cust_id) REFERENCES customer (cust_id)); CREATE TABLE product ( product_id number(11,0) not null, product_name varchar2(50), product_price number(6,2), CONSTRAINT product_pk PRIMARY KEY (product_id)); CREATE TABLE orderline ( order_id number(11,0) not null, product_id number(11,0) not null, quantity number(11,0), CONSTRAINT orderline_pk PRIMARY KEY (order_id, product_id), CONSTRAINT orderline_fk1 FOREIGN KEY (order_id) REFERENCES ordertable (order_id), CONSTRAINT orderline_fk2 FOREIGN KEY (product_id) REFERENCES product (product_id));
Answer: select city,count(city) from customer c, ordertable o where c.cust_id = o.cust_id group by city order by city asc;
This query will display for each city, list number of customers from the city, who have placed order(s). Cities are listed in ascending alphabetical order
For each city, list number of customers from the city, who have placed order(s). Cities are...
Using the below Relational Design from Oracle SQL data modeler, provide sql script for the following queries (note please be sure to include "PART3.Table_name" when pulling info from specific tables) Query 6: How many orders are there by customer and what is the total sales and total profit sorted by customer’s total number of orders? Query 15: What is the total number of orders by shipping class? Query16: What are the monthly total sales from 2013 to 2014? Query 17:...
An online company sells hundreds of office supply products on its e-Commerce store. It has asked you to design and implement a sales order processing system which will be used by users in our Operations department to process sales orders. Here is an overview of the sales order process. Customers make purchases by placing orders. Each customer has a customer number and profile (such as name, shipping address). To simplify the matter, each time an order is placed, only one...
I NEED TO WRITE THE FOLLOWING QUERIES IN MYSQL (13)Next, grant to a user admin the read privileges on the complete descriptions of the customers who submitted no orders. For example, these are the customers who registered themselves and submitted no orders so far. The granted privilege cannot be propagated to the other users. 0.3 (14)Next, grant to a user admin the read privileges on information about the total number of orders submitted by each customer. Note, that some customers...
Display all customers. If a customer has placed any orders, also display the highest..... Tables: CREATE TABLE Sales.Customers ( CustomerId INT NOT NULL IDENTITY, CustomerName NVARCHAR(50) NOT NULL, StreetAddress NVARCHAR(50) NULL, City NVARCHAR(20) NULL, [State] NVARCHAR(20) NULL, PostalCode NVARCHAR(10) NULL, Country NVARCHAR(20) NULL, Contact NVARCHAR(50) NULL, Email NVARCHAR(50) NULL, CONSTRAINT PK_Customers PRIMARY KEY(CustomerId) ); CREATE TABLE HR.Employees ( EmployeeId INT NOT NULL IDENTITY, FirstName NVARCHAR(50) NOT NULL, LastName NVARCHAR(50) NOT NULL, BirthDate DATE NOT NULL, HireDate DATE NOT NULL, HomeAddress...
SQL Command - Create a view called S LIST that lists the Cardholder Number, last name, first name and due date for all cardholders who have not returned a book. Use the CREATE OR REPLACE VIEW AS ... command. TABLE CARD HOLDERS Cardholder Numberint NOT NULL CONSTRAINT CH PK PRIMARY KEY, First_Name varchar(10) NULL, LastName varchar(15) NULL, Address varchar(20) NULL, varchar(15) NULL, State char(2) NULL, Zip_Code char(5) NULL) City TABLE BOOKS CHECKED OUT Cardholder_Numberint NOT NULL, Book Numberint NOT NULL,...
SQL Query Question: I have a database with the tables below, data has also been aded into these tables, I have 2 tasks: 1. Add a column to a relational table POSITIONS to store information about the total number of skills needed by each advertised position. A name of the column is up to you. Assume that no more than 9 skills are needed for each position. Next, use a single UPDATE statement to set the values in the new...
-- Schema definition create table Customer ( cid smallint not null, name varchar(20), city varchar(15), constraint customer_pk primary key (cid) ); create table Club ( club varchar(15) not null, desc varchar(50), constraint club_pk primary key (club) ); create table Member ( club varchar(15) not null, cid smallint not null, constraint member_pk primary key (club, cid), constraint mem_fk_club foreign key (club) references Club, constraint mem_fk_cust...
Create a procedure to update the sales history table following the requirements below. A table creation script is provided below. a) Call the procedure: UPDATE_SALES_HISTORY (The procedure name is important for our scripts to test and grade your code; please do not rename it (otherwise our scripts will not run and your score will be 0). b) The procedure takes 2 parameters: (4-digit-year, 2-digit-month). Both parameters will be numeric, e.g., (2019, 11) will denote 2019 (year) and November (month). The...
I am using oracle sql developer to create some queries to generated reports and it is not working. I am not sure how to use count, group by, and order by. Help me fix my query and explain to me how you did, so can do it next time. Also, I have to convert this query to a stored procedure, so can you help me do it too? Here is my query: SELECT COUNT(GUEST.FIRSTNAME), GUEST.FIRSTNAME, GUEST.LASTNAME, GUEST.GUESTTYPE, RESERVATION.RESERVATIONDATE, GUEST.EMAIL, FROM...