StudentShare
Contact Us
Sign In / Sign Up for FREE
Search
Go to advanced search...
Free

Employing XML, XQUERY, and SQL Queries Techniques - Assignment Example

Cite this document
Summary
"Employing XML, XQUERY, and SQL Queries Techniques" paper displays some details of all employees and their privilege levels, some details of orders whose unit price is greater than $20 but less than $50, and some details of orders whose unit price is less than $20…
Download full paper File format: .doc, available for editing
GRAB THE BEST PAPER95.1% of users find it useful

Extract of sample "Employing XML, XQUERY, and SQL Queries Techniques"

EMPLOYING XML, XQUERY ANF SQL QUERIES TECHNIQUES Customer inserts His/Her Name Customer inserts Name of Institution 17.03.2014 Sample SQL Statements and their XQuery equivalents 1. Display some details of all employees and their privilege levels SQL Command: SELECT Employees.ID, Employees.Last Name, Employees.First Name, Employees.E-mail Address, Employees.Job Title, Employees.Business Phone, Employees.Address, Employees.City, Employee Privileges.Privilege ID FROM Employees, Employee Privileges WHERE Employees.ID = Employee Privileges.Employee ID; XQuery: for $Employees in //Employees let $Employee Privileges := //Employee Privileges [Employee ID = $Employees/ID] return {$Employees/(ID | Last Name| First Name| E-mail Address | Job Title | Business Phone )} {$Employee Privileges/Privilege ID} Report: This is a simple query that simply picks records from two tables with no much filtration. In the case of SQL, a simple SQL command is given, which simply selects the specified fields from the two tables. For the case of XQuery, if only one field was to be shown, a single XPath would be sufficient to implement the whole action. A typical example would be: XPath: //Employees[ID = //Employees Privileges/Employee ID]/Last Name However, since data is to be selected from more than one node, the need for XQuery arises. The exact XQuery statement that can accomplish the above task would be: for $Employees in //Employees let $Employee Privileges in //Employee Privileges Where $Employee Privileges/Employee ID = $Employees/ID return {$Employees/(ID | Last Name| First Name| E-mail Address | Job Title | Business Phone )} {$Employee Privileges/Privilege ID} This is simplified to the sub-selection example like the one shown above. All the examples that follow involve complex inner joins, hence the approach will be to use sub-selections that are related to the above example. 2. Display some details of orders whose unit price is less than $20 SQL Command: SELECT Orders.Order ID, Orders.Employee ID, Orders.Customer ID, Orders.Order Date, Order Details.Unit Price FROM Orders, Order Details WHERE Orders.Order ID = Order Details.Order ID and Order Details.Unit Price < 20; XQuery: for $Orders in //Orders let $Order Details := //Order Details [Order ID = $Orders/ID][Unit Price 20 and Order Details.Unit Price 20][Unit Price 50; XQuery: for $Orders in //Orders let $Order Details := //Order Details [Order ID = $Orders/ID][Unit Price>50] return {$Orders/(Order ID | Employee ID| Customer ID| Order Date | } {$Order Details/Unit Price} Report: Here, the SQL command is selecting data from two tables, Orders and Order details. At the same time, it is checking in the order details table for orders whose unit prices are greater than 50. The comparison in both SQL and XQuery are similar as the < sign is used in both cases. Only one field is displayed from the Order Details table, hence no need for enclosing it into brackets, in the case of XQuery. 5. Display some details of orders shipped to New York SQL Command: SELECT Orders.Order ID, Orders.Employee ID, Orders.Customer ID, Orders.Order Date, Orders.Shipped Date, Orders.Ship Name, Orders.Ship City, Order Status.Status Name FROM Orders, Order Status WHERE Order Status.Status ID = Orders.Status ID and Orders.Ship City = “New York”; XQuery: for $Orders in //Orders let $Order Status := //Order Status [Status ID = $Orders/Status ID] [Ship City= ‘New York’] return {$Orders/(Order ID | Employee ID| Customer ID| Order Date | Shipped Date | Ship City} {$Order Status/Status Name} Report: The tables from which information is drawn are “Orders” and “Order Status”. In SQL, the condition is declared as Orders.Ship City = “New York” while for the case of XML, the expression [Ship City= ‘New York’] is used. 6. Display some details of orders shipped to Cities starting with letter M SQL Command: SELECT Orders.Order ID, Orders.Employee ID, Orders.Customer ID, Orders.Order Date, Orders.Shipped Date, Orders.Ship Name, Orders.Ship City, Order Status.Status Name FROM Orders, Order Status WHERE Order Status.Status ID = Orders.Status ID and Orders.Ship City like “M%”; XQuery: for $Orders in //Orders let $Order Status := //Order Status [Status ID = $Orders/Status ID] [starts-with(Ship City, “M”)] return {$Orders/(Order ID | Employee ID| Customer ID| Order Date | Shipped Date | Ship City} {$Order Status/Status Name} Report: The tables from which information is drawn are “Orders” and “Order Status”. In SQL, the condition is declared as Orders.Ship City like “M%” using the ‘like’ key word while for the case of XML, the expression [starts-with(Ship City, “M”)] is used depicting the use of ‘starts-with’ key word. 7. Display some details of orders and cities whose ship names contain “an” SQL Command: SELECT Orders.Order ID, Orders.Employee ID, Orders.Customer ID, Orders.Order Date, Orders.Shipped Date, Orders.Ship Name, Orders.Ship City, Order Status.Status Name FROM Orders, Order Status WHERE Order Status.Status ID = Orders.Status ID and Orders.Ship City like “%an%”; XQuery: for $Orders in //Orders let $Order Status := //Order Status [Status ID = $Orders/Status ID] [contains(Ship Name, “an”)] return {$Orders/(Order ID | Employee ID| Customer ID| Order Date | Shipped Date | Ship City} {$Order Status/Status Name} Report: The tables from which information is drawn are “Orders” and “Order Status”. In SQL, the condition is declared as Orders.Ship City like “%an%” using the ‘like’ key word while for the case of XML, the expression [contains(Ship Name, “an”)] is used depicting the use of ‘contains’ key word. 8. Display purchases that were conducted by sales managers SQL Command: SELECT Purchase Orders.Purchase Order ID, Purchase Orders.Supplier ID, Purchase orders.Created By, Purchase Orders.Submitted Date, Purchase Orders.Creation Date, Suppliers.Last Name, Suppliers.Job Title FROM Purchase Orders, Suppliers WHERE Purchase Orders.Supplier ID = Suppliers.ID and Suppliers.Job Title = “Sales Manager”; XQuery: for $Purchase Orders in //Purchase Orders let $Suppliers := //Suppliers [ID = $Purchase Orders/Supplier ID][Job Title= ‘Sales Manager’] return {$Purchase Orders/(Purchase Order ID | Supplier ID| Created By| Submitted Date | Creation Date)} {$Suppliers/(Last Name | Job Title)} Report: The tables from which information is drawn are “Purchase Orders” and “Suppliers”. In SQL, the condition is declared as Suppliers.Job Title = “Sales Manager” while for the case of XML, the expression [Job Title= ‘Sales Manager’] is used. 9. Display the Orders of Customers whose job title is that of a Purchasing Manager SQL Command: SELECT Customers.ID, Customers.Company, Customers.Last Name, Customers.First Name, Customers.Job Title, Orders.Order ID, Orders.Employee ID, Order Details.Order ID FROM Customers, Orders, Order Details WHERE Customers.ID = Orders. Customer ID and Orders.Order ID = Order Details.ID and Customers.Job Title = “Purchasing Manager”; XQuery: for $Customers in //Customers [Job Title= ‘Purchasing Manager’] let $Orders := //Orders [Customer ID = $Customers/ID] let $Order Details := //Order Details [ID = $Orders/Order ID] return {$Customers/( ID | Company | Last Name | First Name | Job Title |)} {$Orders/(Order ID | Employee ID)} {$Order Details / Order ID} Report: Here, in formation is extracted from three tables, namely, “Customers”, “Orders” and “Order Details”. In SQL, the condition is declared as Suppliers.Job Title = “Purchasing Manager” while for the case of XML, the expression [Job Title= ‘Purchasing Manager’] is used. 10. Display Products that were transacted on 3/24/2006 SQL Command: SELECT Products.ID, Products.Product Code, Products.Product Name, Product.Description, Inventory Transactions.Transaction Created Date FROM Products, Inventory Transactions WHERE Inventory Transactions. Product ID = Products.ID and Inventory Transactions.Transaction Created Date = “3/24/2006”; XQuery: for $Products in //Products let $Inventory Transactions := //Inventory Transactions [Product ID = $Products / ID][ Transaction Created Date = ‘3/24/2006’] return {$Products/( ID | Product Code | Product Name | Description | Job Title |)} {$Inventory Transaction / Transaction Created Date} Report: The tables from which information is drawn are “Products” and “Inventory Transaction”. In SQL, the condition is declared as Transactions.Transaction Created Date = “3/24/2006” while for the case of XML, the expression [ Transaction Created Date = ‘3/24/2006’] is used. 11. Display Employees who have a shipped status SQL Command: SELECT Employees.ID, Employees.Last Name, Employees.First Name, Employees.Job Title, Purchase Orders.Status FROM Employees, Purchase Orders, Purchase Order Status WHERE Purchase orders.Created By = Employees.ID and Purchase orders.Status ID = Purchase Order Status.Status ID and Purchase Orders.Status ID = 2; XQuery: for $Employees in //Employees let $Purchase Orders := //Purchase Orders [Created By = $Employees/ID][Status ID = 2] let $Purchase Order Status := //Purchase Order Status [Status ID = $Orders/Status ID] return {$Employees/( ID | Last Name | First Name | Job Title |)} {$Purchase Orders/Status} Report: Here, as opposed to the previous examples, information is drawn from three tables (“Employees”, “Purchase Orders” and “Purchase Order Status”), but only two are visible. Also, the status ID is used as the identifier, but it is not displayed in the selection. In SQL, the condition is declared as Purchase Orders.Status ID = 2 while for the case of XML, the expression [Status ID = 2] is used. 12. Display Employees who have an invoiced status SQL Command: SELECT Employees.ID, Employees.Last Name, Employees.First Name, Employees.Job Title, Purchase Orders.Status FROM Employees, Purchase Orders, Purchase Order Status WHERE Purchase orders.Created By = Employees.ID and Purchase orders.Status ID = Purchase Order Status.Status ID and Purchase Orders.Status ID = 1; XQuery: for $Employees in //Employees let $Purchase Orders := //Purchase Orders [Created By = $Employees/ID][Status ID = 1] let $Purchase Order Status := //Purchase Order Status [Status ID = $Orders/Status ID] return {$Employees/( ID | Last Name | First Name | Job Title |)} {$Purchase Orders/Status} Report: Just like in the above example, information is drawn from three tables (“Employees”, “Purchase Orders” and “Purchase Order Status”), but only two are visible. Also, the status ID is used as the identifier, but it is not displayed in the selection. In SQL, the condition is declared as Purchase Orders.Status ID = 1 while for the case of XML, the expression [Status ID = 1] is used. 13. Display some details of orders shipped to Cities ending with letter “l” SQL Command: SELECT Orders.Order ID, Orders.Employee ID, Orders.Customer ID, Orders.Order Date, Orders.Shipped Date, Orders.Ship Name, Orders.Ship City, Order Status.Status Name FROM Orders, Order Status WHERE Order Status.Status ID = Orders.Status ID and Orders.Ship City like “%l”; XQuery: for $Orders in //Orders let $Order Status := //Order Status [Status ID = $Orders/Status ID] [endss-with(Ship City, “l”)] return {$Orders/(Order ID | Employee ID| Customer ID| Order Date | Shipped Date | Ship City} {$Order Status/Status Name} Report: The tables from which information is drawn are “Orders” and “Order Status”. In SQL, the condition is declared as Orders.Ship City like “%l” using the ‘like’ key word while for the case of XML, the expression [endss-with(Ship City, “M”)] is used depicting the use of ‘ends-with’ key word. 14. Display the Orders of Customers whose job title is that of “Purchasing Manager” or “Owner” SQL Command: SELECT Customers.ID, Customers.Company, Customers.Last Name, Customers.First Name, Customers.Job Title, Orders.Order ID, Orders.Employee ID, Order Details.Order ID FROM Customers, Orders, Order Details WHERE Customers.ID = Orders. Customer ID and Orders.Order ID = Order Details.ID and Customers.Job Title in (“Purchasing Manager”, “Owner”); XQuery: for $Customers in //Customers [Job Title= (“Purchasing Manager”, “Owner”)] let $Orders := //Orders [Customer ID = $Customers/ID] let $Order Details := //Order Details [ID = $Orders/Order ID] return {$Customers/( ID | Company | Last Name | First Name | Job Title |)} {$Orders/(Order ID | Employee ID)} {$Order Details / Order ID} Report: This selection makes use of the “in” operator. For the above case, the in operator is used to display records from three tables, “Customers”, “Orders” and “Order Details” of which the job title of the employee is designated either “Purchasing Manager” or “Owner”. The declaration of this condition is different for SQL and XQuery. In SQL, it is used as Customers.Job Title in (“Purchasing Manager”, “Owner”), while in XQuery it is used as [Job Title= (“Purchasing Manager”, “Owner”)] 15. Display the Orders of Customers whose job title is neither “Purchasing Manager” nor “Owner” SQL Command: SELECT Customers.ID, Customers.Company, Customers.Last Name, Customers.First Name, Customers.Job Title, Orders.Order ID, Orders.Employee ID, Order Details.Order ID FROM Customers, Orders, Order Details WHERE Customers.ID = Orders. Customer ID and Orders.Order ID = Order Details.ID and Customers.Job Title not in (“Purchasing Manager”, “Owner”); XQuery: for $Customers in //Customers [Job Title != (“Purchasing Manager”, “Owner”)] let $Orders := //Orders [Customer ID = $Customers/ID] let $Order Details := //Order Details [ID = $Orders/Order ID] return {$Customers / ( ID | Company | Last Name | First Name | Job Title |)} {$Orders / (Order ID | Employee ID)} {$Order Details / Order ID} Report: This selection is the opposite of the above example because it uses the “not in” operator. For the above case, the in operator is used to display records from three tables, “Customers”, “Orders” and “Order Details” of which the job title of the employee is neither designated “Purchasing Manager” nor “Owner”. The declaration of this condition is different for SQL and XQuery. In SQL, it is used as Customers.Job Title not in (“Purchasing Manager”, “Owner”), while in XQuery it is used as [Job Title != (“Purchasing Manager”, “Owner”)] Comparative Analysis of SQL and XQuery From the above discussion, it is evident that both SQL and XQuery are seemingly able to perform the selection tasks efficiently. However, one remains to wonder what the need of another language is when the other one can still perform the same task. This boils down to the motivation of the W3C to come up with the XQuery language in addition to the already existent SQL language. For a number of years, there was a lot of uncertainty about the sufficiency of XML in comparison to other data formats to have a special query language. SQL, on the other hand, has been existent for quite some time, and it is renowned for its ability to retrieve complex information from relational databases. However, it was discovered, later on, that a totally new language would be necessary to handle XML data. As opposed to relational data, which is flat, in the sense that it is organized in terms of rows and columns, XML data is nested, meaning complications may arise, especially when the nesting is irregular. Though using foreign keys may be a good way to search data in relational databases, this may prove tricky in cases where the nesting level of object structures is indefinite. XML, however, allows one to search objects very easily, however irregular their position is in a document. Other reasons prompted the W3C workgroup to consider developing the new XML Query language was the need to have an effective semantic language as opposed to having an extension of the relational language. This was to provide an interfacing tool that would strike a compromise between the working of relational databases and nested databases. This needed to clearly indicate performance and scalability, especially when it comes to the volume of data and the complexness that may arise with the different types of queries. Bibliography BRUNDAGE, M. (2004). XQuery the XML query language. Boston, Addison-Wesley. http://proquest.safaribooksonline.com/0321165810. CHAMBERLIN, D., & KATZ, H. (2003). XQuery from the experts: a guide to the W3C XML query language. Boston, Mass. [u.a.], Addison-Wesley. FAWCETT, J., AYERS, D., & QUIN, L. R. E. (2012). Beginning XML, 5th Edition. New York, Wiley. http://www.books24x7.com/marc.asp?bookid=46607. HOPPE, A. (2008). Materialized view matching and compensation for SQL/XML and Xquery. Thesis (M.Sc.)--York University, 2008. HUNTER, D. (2007). Beginning XML. Indianapolis, IN, Wrox/Wiley Pub. KLEIN, S. (2006). Professional SQL Server 2005 XML. Indianapolis, Wiley Pub. MCGOVERN, J. (2004). XQuery kick start. Indianapolis, Ind, Sams. MELTON, J., & BUXTON, S. (2006). Querying XML XQuery, XPath, and SQL/XML in context. San Francisco, Calif, Morgan Kaufmann. http://site.ebrary.com/id/10229489. PAPAKONSTANTINOU, Y., & MANOLESCU, I. (2006). Querying XML with XQuery. New York ;London, Springer. RAY, E. T. (2003). Learning XML. Beijing, O'Reilly. http://proquest.safaribooksonline.com/0596004206#######. Appendix Below is the relationship diagram of the Northwind database on which the SQL and XQuery statements are to be based Read More
Cite this document
  • APA
  • MLA
  • CHICAGO
(Employing XML, XQUERY, and SQL Queries Techniques Assignment, n.d.)
Employing XML, XQUERY, and SQL Queries Techniques Assignment. https://studentshare.org/logic-programming/2051541-employing-xml-xquery-and-sql-queries-techniques
(Employing XML, XQUERY, and SQL Queries Techniques Assignment)
Employing XML, XQUERY, and SQL Queries Techniques Assignment. https://studentshare.org/logic-programming/2051541-employing-xml-xquery-and-sql-queries-techniques.
“Employing XML, XQUERY, and SQL Queries Techniques Assignment”. https://studentshare.org/logic-programming/2051541-employing-xml-xquery-and-sql-queries-techniques.
  • Cited: 0 times

CHECK THESE SAMPLES OF Employing XML, XQUERY, and SQL Queries Techniques

Advanced SQL Queries for the Pine Valley Furniture Company Database

In the parameters for the queries, we indicate that we would like to pick the top item from the field Advanced sql queries for the Pine Valley Furniture Company DatabaseQuestion 1: What are the IDs for all orders that include furniture produced by the product line 1?... In the parameters for the queries, we indicate that we would like to pick the top item from the field whose values have been sorted in ascending order.... Hence, we have the sql query above....
2 Pages (500 words) Term Paper

Static vs. Dynamic XML queries

Dynamic XML queries A dynamic document is one that is continually edited and up d.... A labeling scheme supporting solely static XML queries is not enough for XML to become a general standard for data representation and exchange; a labeling scheme that effectively supports dynamic XML trees is also necessary (BEHRENDS 2007).... However, dynamic queries are problematic and difficult to handle because they are require the updating of the labels of many nodes simultaneously with the updating of the original XML document to preserve the efficiency of the labeling scheme....
1 Pages (250 words) Essay

What is SQL View

For Instance to delete the current Structured Query Language Insert Insert Discuss how sql views are created, altered, and dropped, and then present the potential benefits of using an sql view to manipulate your data.... DROP VIEW [Current Student List]The benefits of using an sql view to manipulate my data are many.... The language of sql....
1 Pages (250 words) Essay

Association between data virtualization and SQL Joins

The query language establishes Data Virtualization and sql Insert Insert Data virtualization seeks to provide consumers of data with an access interface that encapsulates the location, the structure of the storage and the technology used in the storage.... he association between data virtualization and sql is in the use of the query language in implementing the latter concept (Corbin, Fuller, Jansen & McPherson, 2013).... queries also analyze the services in the server....
1 Pages (250 words) Research Paper

The Major Goal of LINQ to SQL: Relational Databases and the Programming Logic

is the addition of a data access method known as LINQ, which is meant to be an easy to use replacement for the sql technique for accessing data, not limited to, but including relational data as contained in a database repository such as sql Server, as well as data contained in XML format files....
10 Pages (2500 words) Essay

History of XML Programming

"History of xml Programming" examines the evolution of the WWW in terms of the need for a general-purpose markup language, draws conclusions about the power and advantages of xml that have made it the global standard for enterprise data exchange, and compares Data Type Definitions and xml Schemas.... According to (Tidwell 4) xml has the ability to do data interchange.... However, xml will make it easy and possible to send structured data across the internet ensuring nothing gets lost during translation....
6 Pages (1500 words) Assignment

Query Enhancement and Query Recommendations

There is a room for improvement, as few queries are labeled as no answers query.... hellip; The research is associated with executing queries for getting the desired and specific results from the search engine.... The research recommends URI presence and meaningfulness methods for establishing accuracy in executing queries.... OVERVIEWThe research describes a detailed characterization model based on the natural queries that are representing real-life scenarios along with a comparison to deploying world's most popular search engine mechanisms....
8 Pages (2000 words) Case Study

Using Rank Based KNN Queries Processing to Reduce Location Uncertainty in Wireless Sensor Networks

Randomized and exact techniques integrating efficient IO accessing and object pruning techniques were formulated to process queries modeled by uncertain regions or query points.... As the number of applications that employ Spatio-temporal data sets is increasing, it is very important to develop efficient query processing techniques for these applications.... The techniques used in and cannot apply in this paper as in, the technique just considers the distance in L1 and in the technique presume that there is a distribution of the distance between the object with uncertainty and a query point....
17 Pages (4250 words) Term Paper
sponsored ads
We use cookies to create the best experience for you. Keep on browsing if you are OK with that, or find out how to manage cookies.
Contact Us