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.
I am trying to work on Task 11.
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.
My query for Tasks 1-10...
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'
NEW QUERY
SELECT * FROM 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 ITEM.ITEM_ID, ITEM.DESCRIPTION, ITEM.PRICE,
INVOICES.INVOICE_NUM, INVOICES.INVOICE_DATE,
INVOICE_LINE.QUANTITY, INVOICE_LINE.QUOTED_PRICE
FROM INVOICES, INVOICE_LINE, ITEM
WHERE INVOICES.INVOICE_NUM = INVOICE_LINE.INVOICE_NUM
AND INVOICE_LINE.ITEM_ID = ITEM.ITEM_ID
AND INVOICE_LINE.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 INVOICES.INVOICE_NUM, SUM(QUANTITY*QUOTED_PRICE) AS TOTAL
FROM INVOICES, INVOICE_LINE
WHERE INVOICES.INVOICE_NUM = INVOICE_LINE.INVOICE_NUM
GROUP BY INVOICES.INVOICE_NUM
HAVING SUM(QUANTITY*QUOTED_PRICE) > 250
TASK 10
SELECT * FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = "SYSTEM VIEW"
LIMIT 10
Trending now
This is a popular solution!
Step by step
Solved in 2 steps
