Tasks 10-12 Task 10: List all the tables contained within the system catalog, but only display the first 10 records with a TABLE_TYPE of SYSTEM VIEW. Task 11: List all the columns contained within the system catalog, but only display the first 11 records that are in the KimTay TABLE_SCHEMA. Task 12: List all the views contained within the system catalog, but only display the first 12 records.
I am having trouble with Tasks 10-12
Task 10: List all the tables contained within the system catalog, but only display the first 10 records with a TABLE_TYPE of SYSTEM VIEW.
Task 11: List all the columns contained within the system catalog, but only display the first 11 records that are in the KimTay TABLE_SCHEMA.
Task 12: List all the views contained within the system catalog, but only display the first 12 records.
I have done Tasks 1-9
Here is the formula for Tasks 1-9
TASK 1.
CREATE VIEW MAJOR_CUSTOMER AS
SELECT CUST_ID,FIRST_NAME,LAST_NAME, BALANCE, CREDIT_LIMIT, REP_ID
FROM CUSTOMER
WHERE CREDIT_LIMIT <= 500 ;
NEW QUERY
SHOW FULL TABLES WHERE table_type = 'VIEW' and tables_in_KimTay = 'MAJOR_CUSTOMER'
TASK 2.
SELECT CUST_ID,FIRST_NAME,LAST_NAME
FROM MAJOR_CUSTOMER
WHERE BALANCE > CREDIT_LIMIT ;
TASK 3.
SELECT CUST_ID,FIRST_NAME,LAST_NAME
FROM CUSTOMER
WHERE CREDIT_LIMIT <= 500 ;
TASK 4.
CREATE VIEW ITEM_INVOICE AS
SELECT C.ITEM_ID AS ITEM_ID, DESCRIPTION, PRICE, C.INVOICE_NUM AS INVOICE_NUM,
INVOICE_DATE, QUANTITY, QUOTED_PRICE
FROM CUSTOMER A INNER JOIN INVOICES B ON A.CUST_ID = B.CUST_ID
INNER JOIN INVOICE_LINE C ON C.INVOICE_NUM = B.INVOICE_NUM
INNER JOIN ITEM D ON D.ITEM_ID = C.ITEM_ID ;
NEW QUERY
SHOW FULL TABLES WHERE table_type = 'VIEW' AND tables_in_KimTay = 'ITEM_INVOICE'
TASK 5
SELECT ITEM_ID, DESCRIPTION, INVOICE_NUM, QUOTED_PRICE
FROM ITEM_INVOICE WHERE QUOTED_PRICE > 100;
TASK 6
SELECT C.ITEM_ID AS ITEM_ID, DESCRIPTION, PRICE, C.INVOICE_NUM AS INVOICE_NUM,
INVOICE_DATE, QUANTITY, QUOTED_PRICE
FROM CUSTOMER A INNER JOIN INVOICES B ON A.CUST_ID = B.CUST_ID
INNER JOIN INVOICE_LINE C ON C.INVOICE_NUM = B.INVOICE_NUM
INNER JOIN ITEM D ON D.ITEM_ID = C.ITEM_ID
WHERE QUOTED_PRICE > 100 ;
TASK 7.
CREATE VIEW INVOICE_TOTAL AS
SELECT C.INVOICE_NUM AS INVOICE_NUM, SUM(QUANTITY * QUOTED_PRICE ) AS TOTAL_AMOUNT
FROM CUSTOMER A INNER JOIN INVOICES B ON A.CUST_ID = B.CUST_ID
INNER JOIN INVOICE_LINE C ON C.INVOICE_NUM = B.INVOICE_NUM
INNER JOIN ITEM D ON D.ITEM_ID = C.ITEM_ID
GROUP BY C.INVOICE_NUM ORDER BY C.INVOICE_NUM;
NEW QUERY
SHOW FULL TABLES WHERE table_type = 'VIEW' and tables_in_KimTay = 'INVOICE_TOTAL'
TASK 8
SELECT INVOICE_NUM, TOTAL_AMOUNT
FROM INVOICE_TOTAL
WHERE TOTAL_AMOUNT > 250 ;
TASK 9
SELECT INVOICE_NUM, SUM((QUANTITY * QUOTED_PRICE)) TOTAL FROM INVOICE_LINE
GROUP BY INVOICE_NUM
HAVING SUM((QUANTITY * QUOTED_PRICE)) > 250;
Trending now
This is a popular solution!
Step by step
Solved in 3 steps
Task 10
Task 10: List all the tables contained within the system catalog, but only display the first 10 records with a TABLE_TYPE of SYSTEM VIEW.
Results
by Bartleby Expert
