SQL Training Complex Queries

1. SQL Training Complex Queries

2. Review – Conversion Functions

3. Conversion Functions Conversion functions convert a value from one datatype to another. The first datatype is the input datatype and the last datatype is the output datatype. TO_CHAR, date conversionSyntax TO_CHAR(date [, fmt] ) TO_DATE, text to Internal DateSyntax TO_DATE(text [, fmt] ) SELECT * FROM Country WHERE CurrencyDate Between to_date('10/25/2009', 'MM/DD/YYYY') and to_date('12/27/2009','MM/DD/YYYY') 6 rows selected.

4. Conversion Functions TO_CHAR, number conversionSyntax TO_CHAR(n [, fmt ]) Converts n of NUMBER datatype to a value of VARCHAR2 datatype, using the optional number format fmt. If you omit fmt, n is converted to a VARCHAR2 value exactly long enough to hold its significant digits. Select to_Char (PlacedDate, 'mm/dd/yyyy') as OrderDate From Orders; Select to_Char(ProductPrice * OrderQty,'$999,999.99') as ExtPrice From OrderDetail; TO_NUMBER Syntax TO_NUMBER(char [,fmt] ) Converts char, a value of CHAR or VARCHAR2 datatype containing a number in the format specified by the optional format model fmt, to a value of NUMBER datatype.

5. Subqueries

6. Select – Sub Select Problem: Which Users have an hourly rate greater then the average hourly rate? 1. Find the average hourly rate 2. Find the Users with a hourly greater than the average hourly rate. What do we need to do first? Inner Query runs first. 2 SELECT UserID, userFirstName, userLastName, hourlyRate FROM User WHERE hourlyRate > (SELECT avg(hourlyRate) FROM User) ORDER BY hourlyRate desc; 1 22 Rows

7. Select – Sub Select Problem: Which city has the most customers? Find out how many customers is in each city SELECT billingcity, count (customerID) as Count_Customers FROM Customer GROUP BY billingcity HAVING count(customerID) =( SELECT max(num_customers) as max_customers FROM (SELECT billingcity, count(customerid) as num_customers FROM Customer GROUP BY billingCity) ); 1 What is the max number from step 1 2 Find the city with the number of customers from step 2 3

8. Select – Sub Select Problem: Which customer uses the most products? SELECT c.customername, count(distinct spu.subscribedProductID) as count_products FROM Customer c, Users u, SubscribedProductUsers spu WHERE c.customerID = u.customerID and u.userID = spu.userID GROUP BY c.customerName HAVING count(distinct spu.subscribedProductID) = ( SELECT max(num_prod) FROM (SELECT c.customerName, count(distinct spu.subscribedProductID) as num_prod FROM Customer c, Users u, SubscribedProductUsers spu WHERE c.customerID = u.customerID and u.userID = spu.userid GROUP BYc.customerName) ) Find out how many pieces of equipment each customer has 1 What is the maximum number from step 1? (return only this value) 2 Find the customer with the number of pieces of equipment from step 3

9. Select – Sub Select Retrieve all products from the Product Table that have a ProductPrice greater than the Average ProductPrice amount. Order the report in ascending sequence on ProductPrice. SELECT ProductID, ProductDescription, ProductPrice FROM Product WHERE ProductPrice > (SELECT AVG(ProductPrice) FROM Product) ORDER BY ProductPrice; 45 rows selected.

10. Select – Multi-level Sub Select Retrieve a list of all Vendors that supply parts used to build ProductCode HG5000-04B (ProductID = 35). Solve this query using a Sub-Select (without joining tables). SELECT VendorName FROM Vendor WHERE VendorID IN (Select VendorID from VendorComponent where ComponentID IN (Select ComponentID from Manifest where ProductID = 35)); Could this be solved using a Join instead of a Sub-Select? 29 rows selected.

11. Correlated SubQuery – Not Exists Problem: Get a list of all Provinces that do not have Users. A “normal” Join would find all the Provinces that have Customers. However, a Correlated SubQuery with a Not Exists option finds just the opposite, Provinces without Customers.

12. Correlated SubQuery – Not Exists Get a list of all Provinces that do not have Users. Correlated: The inner query references the outer query. 1 SELECT provinceID, provinceName FROM Province WHERE NOT EXISTS (SELECT * FROM Users WHERE Province.provinceID = Users.provinceid); NOT EXISTS: Looks for records where there is NOT a match 2 32 Rows

13. Correlated SubQuery – Not Exists Get a list of all Provinces that do not have Vendors. SELECT ProvinceName FROM Province WHERE NOT EXISTS (SELECT * FROM Vendor WHERE Vendor.ProvinceID = Province.ProvinceID) ORDER BY ProvinceName; Not Exists returns a True if no rows are found, and a False if 1 or more rows are found, in the subquery How a correlated subquery works: Select the row from outer query. Determine the value of the correlated column(s). For each record in the outer query, the inner query is executed. Note: A “normal” Join would find all the Provinces that have Vendors. This is because of the Where clause: Where Province.ProvinceID = Vendor.ProvinceID. However, this Correlated SubQuery with a Not Exists option finds just the opposite, Provinces without Vendors.

14. Self Join Retrieve a list of all Vendors that are in the same Province as VendorID = 2 (BICC Ltd). SELECT V1.VendorID, V1.VendorName, V1. City, V1.PostalCode FROM Vendor V1, Vendor V2 Where V1.ProvinceID = V2.ProvinceID and V2.VendorID = 2;

15. Views

16. Create View Sometimes it is beneficial to save the query in a format that is reusable. Views allow us to do just that. A view: Lets you hide logic that creates a SQL Can be used in SQL’s just like a table Does NOT contain data Views can be classified as Retrieval Only or Update Updatable Views can contain only simple Select syntax: (No Joins, No SubSelects, No Groups, No Calculations, No Unions)

17. Create View – Ease of Use CREATE VIEW v_countryview AS SELECT ProvinceName, RegionName, CountryName FROM Region , Province, Country WHERE Province.RegionID = Region.RegionID and Region.CountryID = Country.CountryID; You can query the view just as you would a table. SELECT * FROMv_countryview WHERE productTypeName = 'Media'; To delete a view: DROP VIEW v_countryview;

18. Create View – Extra Levels of Security Users granted access to this Table can see the salary information One-To-Many Create View Person_View As Select PersonID, LastName, FirstName, Phone, StartDate From Person, PersonStatus Where Person.PersonID = PersonStatus.PersonID; Select * from Person_View; Users granted access to this View cannot see salary information

19. Union Query

20. Union Varchar 15 Integer Decimal (9,2) Date Select Union Select Varchar 30 Integer Decimal (7,2) Date Union Select Varchar 10 Integer Decimal (5,2) Date The Union statement requires the same number of columns, and corresponding columns must have the same data type.

21. Union Select ProvinceID, ProvinceName, ' ' As VendorName, ' ' As VendorLastName, ' ' As VendorFirstName from Province Union Select ProvinceID, ' ', VendorName, VendorLastName, VendorFirstName from Vendor order by 1,3; Note: the Order By clause in a Union refers to the total SQL Statement. 210 rows selected

22. Union What does this SQL statement Produce? SELECT MIN(ProductPrice) As LowestPrice FROM Product UNION SELECT MIN(ProductPrice) FROM Product WHERE ProductPrice > (SELECT MIN(ProductPrice) from Product) ORDER BY 1

23. Workshop

24. Common Error Messages ORA-00918: column ambiguously defined If a column appears in multiple tables, you must qualify the fieldname with the table name. ORA-00933: SQL command not properly ended Look for missing single quotes around strings OR missing ‘and’ between Where clause statements OR Missing keywords such as Where, From, Group By, Order By ORA-00904: “String Expression": invalid identifier Make sure you are using single quotes and not double quotes. ORA-00904: “CUSTOMER"."CUSTOMERID": invalid identifier Make sure the table you are referencing is in the From clause OR Make sure the field you are referencing is in one of the tables in your From clause OR make sure you have spelled the table and/or field name correctly.

25. Workshop • Write the SQL to answer the business question. • Only include the columns shown in the picture. • Your answer should match the data shown. • In some cases, only the first and last rows will be shown due to the size constraints of the page. • Note: Oracle does not always print the entire column name – look at the Database Design for column names.

26. Problem 1 – Using Date Functions How many orders were placed in 2007? Show the report by month. Hint: Use the Placeddate as the order date.

27. Problem 2 – Simple Subselect Problem: Display a list of all Vendors who sell at least one component for more than the average component price. Order the report in ascending sequence on VendorName. 11 rows selected.

28. Problem 3 – NOT EXISTS Problem: Display the ProvinceID, ProvinceName and ProvinceAbbreviation for rows in the Province table that are not assigned to any Vendor in the Vendor table. Only include provinces that have an ProvinceAbbreviation. Order the report in ascending sequence on ProvinceID column. 29 rows selected

29. Problem 4 – Union Problem: Display all of the extended prices (requiredqty * vendorprice) for the components that are used to make each homegen model. For each homegen, calculate a subtotal that sums the values of all of it’s extended prices. At the end of the report, generate a grand total (equal to the sum of the subtotals). Order the report by ProductCode descending. Are other sorting options required? 2521 rows selected

30. Problem 4 – Create View Problem: Create a view named v_vendor that will display the following report when this SQL command is issued: SELECT * FROM V_VENDOR WHERE STATE_CODE = ‘CA’ ORDER BY VENDORNAME 4 rows selected.