Question

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 could drop their orders and at the moment they have no submitted orders. For such customers the total number of submitted orders is equal to zero. The privileges must provide read access to a customer code and the total number of orders submitted by a customer. The privileges cannot be propagated to the other users. 0.3

The database is

/* ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ */
/* ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ */

CREATE TABLE CATEGORY
(
CATEGORY_NAME    VARCHAR(30)   NOT NULL,
DESCRIPTION    VARCHAR(2000),
PICTURE        VARCHAR(255),
CONSTRAINT PK_CATEGORY PRIMARY KEY (CATEGORY_NAME)
);

/* COMMENT ON COLUMN CATEGORY.CATEGORY_NAME IS 'Name of food category.';
COMMENT ON COLUMN CATEGORY.PICTURE IS 'A picture representing the food category.';               */

/* ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ */

CREATE TABLE CUSTOMER
(
CUSTOMER_CODE    VARCHAR(5)   NOT NULL,
COMPANY_NAME    VARCHAR(40)   NOT NULL,
CONTACT_NAME    VARCHAR(30),
CONTACT_TITLE    VARCHAR(30),
ADDRESS        VARCHAR(60),
CITY        VARCHAR(15),
REGION        VARCHAR(15),
POSTAL_CODE    VARCHAR(10),
COUNTRY        VARCHAR(15),
PHONE        VARCHAR(24),
FAX        VARCHAR(24),
CONSTRAINT PK_CUSTOMER PRIMARY KEY (CUSTOMER_CODE)
);

/* COMMENT ON COLUMN CUSTOMER.CUSTOMER_CODE IS 'Unique five-character code based on customer name.';
COMMENT ON COLUMN CUSTOMER.ADDRESS IS 'Street or post-office box.';
COMMENT ON COLUMN CUSTOMER.REGION IS 'State or province.';
COMMENT ON COLUMN CUSTOMER.PHONE IS 'Phone number includes country code or area code.';
COMMENT ON COLUMN CUSTOMER.FAX IS 'Phone number includes country code or area code.'; */

/* ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ */

CREATE TABLE EMPLOYEE
(
EMPLOYEE_ID       DECIMAL(9)   NOT NULL,
LASTNAME        VARCHAR(20)    NOT NULL,
FIRSTNAME        VARCHAR(10)    NOT NULL,
TITLE        VARCHAR(30),
TITLE_OF_COURTESY    VARCHAR(25),
BIRTHDATE        DATE,
HIREDATE        DATE,
ADDRESS        VARCHAR(60),
CITY        VARCHAR(15),
REGION        VARCHAR(15),
POSTAL_CODE    VARCHAR(10),
COUNTRY        VARCHAR(15),
HOME_PHONE        VARCHAR(24),
EXTENSION        VARCHAR(4),
PHOTO        VARCHAR(255),
NOTES        VARCHAR(2000),
REPORTS_TO        DECIMAL(9),
CONSTRAINT PK_EMPLOYEE PRIMARY KEY (EMPLOYEE_ID)
);

/* COMMENT ON COLUMN EMPLOYEE.EMPLOYEE_ID IS 'Number assigned to new employee.';
COMMENT ON COLUMN EMPLOYEE.TITLE IS 'Employee''s title.';
COMMENT ON COLUMN EMPLOYEE.TITLE_OF_COURTESY IS 'Title used in salutations.';
COMMENT ON COLUMN EMPLOYEE.ADDRESS IS 'Street or post-office box.';
COMMENT ON COLUMN EMPLOYEE.REGION IS 'State or province.';
COMMENT ON COLUMN EMPLOYEE.HOME_PHONE IS 'Phone number includes country code or area code.';
COMMENT ON COLUMN EMPLOYEE.EXTENSION IS 'Internal telephone extension number.';
COMMENT ON COLUMN EMPLOYEE.PHOTO IS 'Picture of employee.';
COMMENT ON COLUMN EMPLOYEE.NOTES IS 'General information about employee''s background.';
COMMENT ON COLUMN EMPLOYEE.REPORTS_TO IS 'Employee''s supervisor.';                       */

/* ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ */

CREATE TABLE SUPPLIER
(
COMPANY_NAME    VARCHAR(40)   NOT NULL,
CONTACT_NAME    VARCHAR(30),
CONTACT_TITLE    VARCHAR(30),
ADDRESS        VARCHAR(60),
CITY        VARCHAR(15),
REGION        VARCHAR(15),
POSTAL_CODE    VARCHAR(10),
COUNTRY        VARCHAR(15),
PHONE        VARCHAR(24),
FAX        VARCHAR(24),
HOME_PAGE        VARCHAR(500),
CONSTRAINT PK_SUPPLIER PRIMARY KEY (COMPANY_NAME)
);

/* COMMENT ON COLUMN SUPPLIER.COMPANY_NAME IS 'Name of supplying company.';
COMMENT ON COLUMN SUPPLIER.ADDRESS IS 'Street or post-office box.';
COMMENT ON COLUMN SUPPLIER.REGION IS 'State or province.';
COMMENT ON COLUMN SUPPLIER.PHONE IS 'Phone number includes country code or area code.';
COMMENT ON COLUMN SUPPLIER.FAX IS 'Phone number includes country code or area code.';
COMMENT ON COLUMN SUPPLIER.HOME_PAGE IS 'Supplier''s home page on World Wide Web.';               */

/* ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ */
CREATE TABLE SHIPPER
(
COMPANY_NAME    VARCHAR(40)   NOT NULL,
PHONE        VARCHAR(24),
CONSTRAINT PK_SHIPPER PRIMARY KEY (COMPANY_NAME),
CONSTRAINT CK_SHIPPER UNIQUE (PHONE)
);

/* COMMENT ON COLUMN SHIPPER.COMPANY_NAME IS 'Name of shipping company.';
COMMENT ON COLUMN SHIPPER.PHONE IS 'Phone number includes country code or area code.' */

/* ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ */

CREATE TABLE PRODUCT
(
PRODUCT_NAME    VARCHAR(40)    NOT NULL,
SUPPLIER_NAME   VARCHAR(40)    NOT NULL,
CATEGORY_NAME   VARCHAR(30)   NOT NULL,
QUANTITY_PER_UNIT    VARCHAR(20),
UNIT_PRICE        DECIMAL(10,2)   NOT NULL   DEFAULT 0,
UNITS_IN_STOCK    DECIMAL(9)    NOT NULL    DEFAULT 0,
UNITS_ON_ORDER    DECIMAL(9)    NOT NULL   DEFAULT 0,
REORDER_LEVEL    DECIMAL(9)    NOT NULL    DEFAULT 0,
DISCONTINUED    CHAR(1)       NOT NULL   DEFAULT 'N',
CONSTRAINT PK_PRODUCT PRIMARY KEY (PRODUCT_NAME),
CONSTRAINT FK_CATEGORY_NAME FOREIGN KEY (CATEGORY_NAME) REFERENCES CATEGORY(CATEGORY_NAME),
CONSTRAINT FK_SUPPLIER_NAME FOREIGN KEY (SUPPLIER_NAME) REFERENCES SUPPLIER(COMPANY_NAME),
CONSTRAINT CK_PRODUCT_UNIT_PRICE CHECK (UNIT_PRICE >= 0),
CONSTRAINT CK_PRODUCT_UNITS_IN_STOCK CHECK (UNITS_IN_STOCK >= 0),
CONSTRAINT CK_PRODUCT_UNITS_ON_ORDER CHECK (UNITS_ON_ORDER >= 0),
CONSTRAINT CK_PRODUCT_REORDER_LEVEL CHECK (REORDER_LEVEL >= 0),
CONSTRAINT CK_PRODUCT_DISCONTINUED CHECK (DISCONTINUED in ('Y','N'))
);
  
/* COMMENT ON COLUMN PRODUCT.SUPPLIER_ IS 'Same entry as in Suppliers table.';
COMMENT ON COLUMN PRODUCT.CATEGORY_ID IS 'Same entry as in Categories table.';
COMMENT ON COLUMN PRODUCT.QUANTITY_PER_UNIT IS '(e.g., 24-count case, 1-liter bottle).';
COMMENT ON COLUMN PRODUCT.REORDER_LEVEL IS 'Minimum units to maintain in stock.';
COMMENT ON COLUMN PRODUCT.DISCONTINUED IS 'Yes means item is no longer available.';               */

/* ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ */
CREATE TABLE ORDERS
(
ORDER_ID       DECIMAL(9)   NOT NULL,
CUSTOMER_CODE    VARCHAR(5)    NOT NULL,
EMPLOYEE_ID    DECIMAL(9)    NOT NULL,
ORDER_DATE        DATE       NOT NULL,
REQUIRED_DATE    DATE,
SHIPPED_DATE    DATE,
SHIP_VIA        VARCHAR(40),
FREIGHT        DECIMAL(10,2)           DEFAULT 0,
SHIP_NAME        VARCHAR(40),
SHIP_ADDRESS    VARCHAR(60),
SHIP_CITY        VARCHAR(15),
SHIP_REGION    VARCHAR(15),
SHIP_POSTAL_CODE    VARCHAR(10),
SHIP_COUNTRY    VARCHAR(15),
CONSTRAINT PK_ORDERS PRIMARY KEY (ORDER_ID),
CONSTRAINT FK_CUSTOMER_CODE FOREIGN KEY (CUSTOMER_CODE) REFERENCES CUSTOMER(CUSTOMER_CODE),
CONSTRAINT FK_EMPLOYEE_ID FOREIGN KEY (EMPLOYEE_ID) REFERENCES EMPLOYEE(EMPLOYEE_ID),
CONSTRAINT FK_SHIP_VIA FOREIGN KEY (SHIP_VIA) REFERENCES SHIPPER(COMPANY_NAME)
);

/* COMMENT ON COLUMN ORDERS.ORDER_ID IS 'Unique order number.';
COMMENT ON COLUMN ORDERS.CUSTOMER_CODE IS 'Same entry as in Customers table.';
COMMENT ON COLUMN ORDERS.EMPLOYEE_ID IS 'Same entry as in Employees table.';
COMMENT ON COLUMN ORDERS.SHIP_VIA IS 'Same as Company name in Shippers table.';
COMMENT ON COLUMN ORDERS.SHIP_NAME IS 'Name of person or company to receive the shipment.';
COMMENT ON COLUMN ORDERS.SHIP_ADDRESS IS 'Street address only -- no post-office box allowed.';
COMMENT ON COLUMN ORDERS.SHIP_REGION IS 'State or province.';                       */

/* ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ */

CREATE TABLE ORDER_DETAIL
(
ORDER_ID       DECIMAL(9)   NOT NULL,
PRODUCT_NAME    VARCHAR(40)    NOT NULL,
UNIT_PRICE        DECIMAL(10,2)   NOT NULL   DEFAULT 0,
QUANTITY        DECIMAL(9)   NOT NULL     DEFAULT 1 ,
DISCOUNT        DECIMAL(4,2)   NOT NULL     DEFAULT 0,
CONSTRAINT PK_ORDER_DETAIL PRIMARY KEY (ORDER_ID, PRODUCT_NAME),
CONSTRAINT FK_ORDER_ID FOREIGN KEY (ORDER_ID) REFERENCES ORDERS (ORDER_ID),
CONSTRAINT FK_PRODUCT_NAME FOREIGN KEY (PRODUCT_NAME) REFERENCES PRODUCT (PRODUCT_NAME),
CONSTRAINT CK_ORDER_DETAIL_UNIT_PRICE CHECK (UNIT_PRICE >= 0),
CONSTRAINT CK_ORDER_DETAIL_QUANTITY CHECK (QUANTITY > 0),
CONSTRAINT CK_ORDER_DETAIL_DISCOUNT CHECK (DISCOUNT between 0 and 1)
);

/* COMMENT ON COLUMN ORDER_DETAIL.UNIT_PRICE IS 'Unit price of product';
COMMENT ON COLUMN ORDER_DETAIL.QUANTITY IS 'Quantity ordered.';
COMMENT ON COLUMN ORDER_DETAIL.QUANTITY IS 'Discount applied.';                       */

/* ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ */
/* ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ */

Answer 1

//MYSQL DOESNT ALLOW CONDITIONS IN GRANT SOUSED A VIEW ON WHICH PRIVILEGES WILL BE GRANTED

//somehost REFERS TO WHATEVER HOSTNAME YOU ARE USING.

//YOURDATABASENAME IS THE NAME OF YOUR DATABASE.SUBSTITUTE THESE VALUES.

13)CREATE VIEW NO_ORDERS_VIEW AS SELECT * FROM CUSTOMER WHERE CUSTOMER.CUSTOMER_CODE NOT IN(SELECT CUSTOMER_CODE FROM ORDERS);

GRANT SELECT ON YOURDATABASENAME.NO_ORDERS_VIEW TO USER_ADMIN@'somehost';

14)CREATE VIEW ORDER_COUNT_VIEW AS SELECT CUSTOMER_CODE,COUNT(DISTINCT(ORDER_ID)) FROM ORDERS GROUP BY CUSTOMER_CODE);

GRANT SELECT ON YOURDATABASENAME.ORDER_COUNT_VIEW TO USER_ADMIN@'somehost';