STI IT2003 Advanced SQL PDF

IT2003 Advanced SQL OrderID OrderDate ItemID CustomerID...

IT2003 Advanced SQL OrderID OrderDate ItemID CustomerID OR-1 2019-08-14 IT-5 Cust-3 A. Common Table Expression OR-2 2019-12-04 IT-2 Cust-6 a. Common Table Expression (CTE) - specif ies a temporary named OR-3 2017-08-20 IT-7 Cust-2 result set. OR-4 2018-06-12 IT-10 Cust-4  Can be def ined using the WITH operator OR-5 2017-02-28 IT-5 Cust-9  Allows users to def ine tables that can be used in a particular query OR-6 2019-05-21 IT-9 Cust-10  Can be ref erenced within another SELECT, INSERT, UPDATE, OR-7 2017-12-27 IT-2 Cust-1 or DELETE statement OR-8 2017-02-10 IT-4 Cust-5  Clauses like ORDER BY, INTO, and OPTION cannot be used OR-9 2018-09-12 IT-10 Cust-3 in CTE queries. OR-10 2018-01-17 IT-3 Cust-4 Table 1. Orders o Types of CTE Output: a. Non-Recursive CTE  Doesn’t use repeated procedural loops/recursion OrderID CustomerID OrderDate  Easier to understand OR-3 Cust-2 2017-08-20 b. Recursive CTE OR-5 Cust-9 2017-02-28  Uses recursion OR-7 Cust-1 2017-12-27  Usef ul when working with hierarchical data because the CTE continues to execute until the query returns OR-8 Cust-5 2017-02-10 the entire hierarchy Explanation: Assume that we have a table (see table 1) named Orders having a 1. We def ined Simple_CTE as the name of common table columns OrderID, OrderDate, Cust_ID, ItemID. expression. The CTE returns a result set that consists of three columns: OrderID, Cust_ID, OrderDate. Next, using non-recursive queries, we will select the data of 2. We constructed a query that retrieves all the data f rom table customers (excluding ItemID column) who ordered in 2017. Orders, excluding ItemID column. (Note: We can give a temporary name or alias to a specific column or table without using the “AS” keyword.) -- Non-recursive 3. We get our dataset f rom Simple_CTE and select only the rows ;WITH Simple_CTE (OrderID, Cust_ID, OrderDate) whose year is 2017. AS ( Now, we will create a simple recursive query to display the SELECT O.OrderID, O.CustomerID, O.OrderDate row number f rom 1 to 10 FROM Orders O ) SELECT * FROM Simple_CTE WHERE YEAR(Orderdate) = 2017 02 Handout 1 *Property of STI  [email protected] Page 1 of 7  IT2003 Another example of Recursive CTE: -- Recursive Assume that we have a table (see f igure 2) named ClassOfficers ;WITH Recursive_CTE having columns OfficerID, OfficerName, Position, and Reporting. AS ( In the column Reporting, each row represents to whom OfficerID SELECT 1 AS RowNo --Anchor part they are reporting. Since Chin was the president, her reporting UNION ALL value is null because she got the highest-class position. SELECT RowNo + 1 --Recursive part OfficerID OfficerName Position Reporting FROM Recursive_CTE 201 Chin President NULL WHERE RowNo < 10 --terminator 207 Jeremiah Vice President 201 ) 209 Baldo Secretary 201 SELECT * FROM Recursive_CTE --Statement using CTE 212 JunJun Treasurer 209 223 Bernadette Spokesperson 209 Table 2. Officers Output: Next, using recursive queries, we will display the reporting structure of all class of f icers. ;WITH ReportingStructure(Reporting, OfficerID, OfficerName, Position, PosLevel) AS ( -- Anchor part SELECT Reporting, OfficerID, OfficerName, Position, 0 AS PosLevel FROM Officers WHERE Reporting IS NULL UNION ALL Explanation: -- Recursive part 1. We def ined Recursive_CTE as the name of common table SELECT O.Reporting, O.OfficerID, O.OfficerName, expression. O.Position, R.PosLevel + 1 2. We constructed a query that will def ine a temporary column FROM Officers O named RowNo and set its value as 1. INNER JOIN ReportingStructure R 3. In the anchor part, we display the base value of RowNo. ON R.OfficerID = O.Reporting 4. By using UNION ALL, we execute the recursive part repeatedly until the row satisf ied the condition in the WHERE clause. ) 5. Using the SELECT statement, we retrieve the data f rom SELECT Reporting, OfficerID, OfficerName, Recursive_CTE. Position, PosLevel FROM ReportingStructure; 02 Handout 1 *Property of STI  [email protected] Page 2 of 7  IT2003 Output: d. In IN operator – allows users to match one item f rom any of those Reporting OfficerID OfficerName Position PosLevel in the list. NULL 201 Chin President 0 e. In ALL and ANY operator o ANY – returns true if any of the subquery values satisf y the 201 207 Jeremiah Vice President 1 condition. 201 209 Baldo Secretary 1 o ALL – returns true if all the subquery values meet the condition. 209 212 JunJun Treasurer 2 f. Correlated Subqueries – are used to select data f rom a table 209 223 Bernadette Spokesperson 2 ref erenced in the outer query.  Cannot be executed independently as a simple subquery Explanation:  A correlated subquery is executed repeatedly, once f or each 1. The ReportingStructure CTE returns a result set that consists of five (5) row evaluated by the outer query. columns: Reporting, OfficerID, OfficerName, Position, PosLevel.  Also known as a repeating subquery 2. In the anchor part, we retrieve the f our columns f rom the Officers table g. In EXISTS operator – used to check whether a subquery produces and returns the base result set of PosLevel as 0. This is the highest- any rows of query results. ranking of f icer as she does not report to a higher position.  Commonly used with correlated subqueries 3. In the recursive query, it returns the other of f icers in the anchor part Subqueries Examples: result set. This self -ref erring is achieved by a JOIN statement between Assume that we have three (3) tables named Customers, Items, and Orders Officers table and ReportingStructure CTE. (see table 3, 4, and 5). 4. Then, we increment the PosLevel every time the statement loops CustomerID Cust_Name Address through the hierarchy. Cust-1 Badang Taytay 5. The termination condition is met through a JOIN statement where it Cust-2 Hanzo Angono stops its recursion when all rows are returned f rom Officers table. Cust-3 Lilia Cainta 6. Af ter def ining the WITH clause, we create a SELECT statement that retrieves the data f rom CTE. Cust-4 Layla Angono Cust-5 Lesley Taytay B. Subqueries – A query (SELECT statement) inside another query. Cust-6 Balmond Cainta  Expressed inside parentheses Cust-7 Chou Pililia  The f irst query in the SQL statement is known as the outer query.  Query inside the SQL statement is known as the inner query. Cust-8 Eudora Cardona  The inner query is executed f irst. Cust-9 Miya Pililia  The output of an inner query is used as the input f or the outer query. Cust-10 Cecilion Cardona Table 3. Customers a. In WHERE clause – work as part of the row selection process. ItemID ItemName Price  A subquery of ten f ound in the WHERE clause IT-1 Samsung S9 32000.00  Also called nested subqueries. IT-2 Samsung S10 40000.00 b. In FROM clause – returns a temporary or virtual table.  Usef ul in data warehousing application IT-3 Huawei P30 29000.00  Also called an inline view or derived table IT-4 Huawei Mate30 31000.00 c. In SELECT clause – a subquery that is nested in the list of another IT-5 Realme 6 9999.99 SELECT statement. IT-6 Realme 6 Pro 11000.00 02 Handout 1 *Property of STI  [email protected] Page 3 of 7  IT2003 IT-7 Xiaomi Note 9 8000.00 SELECT I.ItemID, I.ItemName, I.Price IT-8 Xiaomi Note 10 11000.00 FROM (SELECT Price FROM Items WHERE ItemName = 'LG IT-9 LG V40 15000.00 V40') AS TEMP_table, Items I IT-10 LG V50 23000.00 Table 4. Items WHERE I.Price > TEMP_table.Price OrderID OrderDate ItemID CustomerID OR-1 2019-08-14 IT-5 Cust-3 Output: OR-2 2019-12-04 IT-2 Cust-6 ItemID ItemName Price OR-3 2017-08-20 IT-7 Cust-2 IT-1 Samsung S9 32,000.00 OR-4 2018-06-12 IT-10 Cust-4 IT-2 Samsung S10 40,000.00 OR-5 2017-02-28 IT-5 Cust-9 IT-3 Huawei P30 29,000.00 OR-6 2019-05-21 IT-9 Cust-10 IT-4 Huawei Mate30 31,000.00 OR-7 2017-12-27 IT-2 Cust-1 IT-10 LG V50 23,000.00 OR-8 2017-02-10 IT-4 Cust-5 OR-9 2018-09-12 IT-10 Cust-3 c. SELECT clause  Using CONCAT and SUBSTRING f unction, change the structure of OR-10 2018-01-17 IT-3 Cust-4 Customer ID and display them as “NewID-##”. Table 5. Orders SELECT TOP 5 (SELECT CONCAT('NewID', a. WHERE clause (SELECT SUBSTRING(CustomerID, 5, 3))))  Display the items that have a price lower than or equal to LG V40. AS 'New ID Format', Cust_Name FROM Customers SELECT * –TOP keyword limits the returning rows FROM Items WHERE Price < (SELECT Price FROM Items Output: WHERE ItemName = 'LG V40') New ID Format Cust_Name NewID-1 Badang Output: NewID-2 Hanzo ItemID ItemName Price NewID-3 Lilia IT-5 Realme 6 9,999.99 NewID-4 Layla IT-6 Realme 6 Pro 11,000.00 NewID-5 Lesley IT-7 Xiaomi Note 9 8,000.00 IT-8 Xiaomi Note 10 11,000.00 d. IN operator  Display the customers who purchased a gadget with a brand b. FROM clause name of Huawei and Samsung.  Display the items that have a price higher than LG V40. 02 Handout 1 *Property of STI  [email protected] Page 4 of 7  IT2003  Using ALL operator, display the items that have a price higher than SELECT C.Cust_Name, C.Address, I.ItemName, LG V40 AND Huawei Mate30. YEAR(O.OrderDate) AS 'Year of sales' SELECT ItemName, Price FROM Orders O FROM Items JOIN Customers C ON O.CustomerID = C.CustomerID WHERE Price > ALL JOIN Items I ON I.ItemID = O.ItemID (SELECT Price FROM Items WHERE ItemName IN ('LG V40', 'HUAWEI MATE30')) WHERE I.ItemID GROUP BY ItemName, Price IN (SELECT I.ItemID FROM Items Output: WHERE I.ItemName LIKE '%SAMSUNG%' OR ItemName Price I.ItemName LIKE '%HUAWEI%') Samsung S10 40000.00 AND YEAR(O.OrderDate) >= 2018 Samsung S9 32000.00 Output: f. Correlated Subquery Cust_Name Address ItemName Year of sales  Display the list of customers who bought gadgets in 2018. Layla Angono Huawei P30 2018 Balmond Cainta Samsung S10 2019 SELECT (SELECT Cust_Name FROM Customers C WHERE C.CustomerID = O.CustomerID) AS Cust_Name, e. ALL and ANY YEAR(O.OrderDate) AS 'Year bought'  Using ANY operator, display the items that have a price higher FROM Orders O than LG V40 OR Huawei Mate30. WHERE YEAR(O.OrderDate) = 2018 SELECT ItemName, Price Output: FROM Items Cust_Name Year bought WHERE Price > ANY Layla 2018 (SELECT Price FROM Items WHERE ItemName IN ('LG V40', Lilia 2018 'HUAWEI MATE30')) Layla 2018 GROUP BY ItemName, Price g. EXISTS operator Output:  Display the list of customers who bought gadgets in 2017. ItemName Price Huawei Mate30 31000.00 Huawei P30 29000.00 LG V50 23000.00 Samsung S10 40000.00 Samsung S9 32000.00 02 Handout 1 *Property of STI  [email protected] Page 5 of 7  IT2003 SELECT C.CustomerID, C.Cust_Name SELECT * FROM orders_report WHERE Address IN FROM Customers C ('Taytay','Angono') WHERE EXISTS Output: (SELECT O.CustomerID FROM Orders O OrderID Cust_Name Address ItemName OrderDate WHERE O.CustomerID = C.CustomerID AND OR-7 Badang Taytay Samsung S10 2017-12-27 YEAR(O.OrderDate) = 2017) OR-10 Layla Angono Huawei P30 2018-01-17 Output: OR-8 Lesley Taytay Huawei Mate30 2017-02-10 CustomerID Cust_Name OR-3 Hanzo Angono Xiaomi Note 9 2017-08-20 Cust-1 Badang OR-4 Layla Angono LG V50 2018-06-12 Cust-2 Hanzo For updating the view. See the example below. Cust-5 Lesley Cust-9 Miya ALTER VIEW orders_report AS C. Views SELECT o.OrderID, O.OrderDate Views – a virtual table that is constructed f rom other tables or views FROM Orders O and saved as an object in the database For deleting a view. See the example below.  Has no data of its own, but obtains data f rom tables or other views DROP VIEW orders_report  Cannot include the f ollowing: 1. ORDER BY clause 2. A ref erence to a temporary table or a table variable. D. Index Using tables Customers, Orders, Items (see table 3, 4, and 5). We will create a View that retrieves specif ic columns from three tables. Index – used to speed up searches/queries, resulting in high perf ormance CREATE VIEW orders_report o Factors to consider creating an index: AS 1. Frequency of search – creating an index to a particular column that is f requently searched can give perf ormance SELECT o.OrderID, c.Cust_Name, c.Address, i.ItemName ,O. benef its. OrderDate 2. Size of table – putting an index on a relatively large table FROM Orders O that contains a great number of rows can improve JOIN Customers C ON C.CustomerID = O.CustomerID perf ormance. JOIN Items I ON I.ItemID = O.ItemID 3. Number of updates – a database that is f requently updated should have f ewer indexes as it slows the perf ormance of From the newly created view, we will select the rows whose inserts, updates, and deletes. customer’s address is Taytay and Angono. 4. Space considerations – create an index only if necessary, because indexes take up spaces within the database. 02 Handout 1 *Property of STI  [email protected] Page 6 of 7  IT2003 Example: EssentialSQL. (n.d.). Recursive CTEs explained. Retrieved from o Single-Column Indexes – based on only one table column. https://www.essentialsql.com/recursive-ctes-explained/ Ex. MariaDB. (n.d.). Subqueries in a FROM clause. Retrieved from https://mariadb.com/kb/en/subqueries-in-a-f rom-clause/ CREATE INDEX ix_CustomerID Microsof t. (n.d.). CREATE VIEW (Transact-SQL). Retrieved from ON Customers (CustomerID) https://docs.microsoft.com/en-us/sql/t-sql/statements/create-view- o Unique Indexes – does not allow any duplicate values to be transact-sql?view=sql-server-ver15 inserted into the table. SQLservertutorial.net (n.d.). SQL server subquery. Retrieved from Ex. https://www.sqlservertutorial.net/sql-server-basics/sql-server- subquery/ CREATE UNIQUE INDEX ix_ItemID Techonthenet (n.d.). SQL Server: Subqueries. Retrieved from ON Items (ItemID) https://www.techonthenet.com/sql_server/subqueries.php W3resource (n.d.). SQL subqueries. Retrieved from o Composite Indexes – based on two or more columns of a https://www.w3resource.com/sql/subqueries/understanding-sql- table. subqueries.php Ex. CREATE INDEX ix_OrderRecords ON Orders (OrderID, OrderDate, CustomerID, ItemID) o Dropping Index – deleting an index can be done using the DROP command. Ex. DROP INDEX Orders.ix_OrderRecords REFERENCES Coronel, C. and Morris, S. (2018). Database systems design, implementation, & management (13th ed.). Cengage Learning. Elmasri, R. & Navathe, S. (2016). Fundamentals of database systems (7th ed.). Pearson Higher Education. Kroenke, D. & Auer, D. Database processing: Fundamentals, design, and implementation (12th ed.). Pearson Higher Education. Silberschatz A., Korth H.F., & Sudarshan, S. (2019). Database system concepts (7th ed.). McGraw-Hill Education. 02 Handout 1 *Property of STI  [email protected] Page 7 of 7