...Project 6 Required Queries: * Create a query that includes at least two INNER JOINS (i.e. spans at least three tables), a search criteria, and orders the resulting data. * >select M.MID, M.MFName * >from MEMBER as M * >inner join LINEITEM as LI on LI.MID=M.MID * >inner join ITEM as I on I.IID=LI.IID * >inner join DVD as D on D.IID=I.IID; * This shows which members have checked out DVDs * Create a query that includes a calculation (e.g. average, sum, etc.). * >Select M.MID, M.MFName, count(B.IID) as “Books Checked Out” * >from MEMBER as M * >inner join LINEITEM as LI on LI.MID=M.MID * >inner join ITEM as I on I.IID=LI.IID * >inner join BOOK as B on B.IID=I.IID; * This query shows how many books a member has checked out * Create a query that includes at least one RIGHT or LEFT outer join. * >select MEMBER.MID, MEMBER.MFName, LIBRARIAN.LFName * >from MEMBER * >left outer join LINEITEM on LINEITEM.MID=MEMBER.MID * >left outer join LIBRARIAN on LINEITEM.LID=LIBRARIAN.LID * >order by MEMBER.MID; * This query uses a left join to show the relations between which librarians have checked out which members * Create a query that uses a GROUP BY to perform a calculation on information spanning at least two tables. * Create a query that includes a GROUP BY, but selects items from...
Words: 289 - Pages: 2
...Write the following queries in SQL: Display a list of all instructors, showing their ID, name, and the number of sections that they have taught. Make sure to show the number of sections as 0 for instructors who have not taught any section. Your query should use an outer join, and should not use scalar subqueries. By using the university schema provided by db-book.com the following queries were done on the university database. The first query uses an outer join which works similar to the join operation but it keeps the rows that don’t match between the two tables that would be lost in a join operation. There are three forms of outer join: a) Left outer join displays the results from the left table even if the condition does not find any matching record in the right table. b) Right outer join will displays the results from the right table regardless if there is matching data in the left table. c) Full outer join will retain all rows from both tables, regardless if the data matches or not. The group by clause when used in a select statement collects data from multiple records and groups the results into one or more columns. The below is the query using left outer join operation: select ID, name, count(sec_id) as Number_of_sections from instructor natural left outer join teaches group by ID, name; The next query was written by using a scalar subquery without using an outer join operation. A scalar subquery is where the output of a subquery returns only...
Words: 414 - Pages: 2
...CREATING QUERIES Lesson 9 Introduction The real power of an Access 2007 database is in the ability to pull data for quick analysis, which is what happens when you run a query. Queries allow you to retrieve information from one or more tables based on a set of search conditions you define. Access 2007 will display your results in their very own table that you can analyze and manipulate further. This lesson will explain how to plan a query using a three-question planning process. You will learn how to use the Query Design command to run the query, as well as how to modify the query to hide fields or other information in your query results. Finally, it will show you how to save the query for later use. Using Queries Queries retrieve information from one or more tables based on a set of search conditions that you set up and then combine that information in a way that is easy for you to analyze. If you have used an Advanced Filter in Access 2007, then you have already run a very basic query on only one table. If you want to pull data from more than one table, though, you will need to use either the Query Design command or the Query Wizard. Before using the Access 2007 query tools, it is important to plan out the query using a logical process. Otherwise, you may not get the results you expect. Planning a Query There are three questions you need to answer when you are planning a query: What do you want the results to look like? Identify every field or bit of information...
Words: 2519 - Pages: 11
...Citi Bank India Credit Cards: Strategy for Profitable Growth By shailanchal | Studymode.com Journal of Services Research Volume 11 Number 2 October 2011 - March 2012 IMPACT OF RESOURCES, CAPABILITIES AND TECHNOLOGY ON MARKET ORIENTATION OF INDIAN B2B FIRMS The Journal of IIMT Dr. Atanu Adhikari Indian Institute of Management Kozhikode Kerala, India Prof. Manpreet Singh Gill faculty of marketing with L.G.C Ludhiana, India Journal of Services Research, Volume 11, Number 2 (October 2011 - March 2012) ©2011 by Institute for International Management and Technology. All Rights Reserved. Market orientation in B2B industry, both in developed as well as developing countries, acts as the implementation part of marketing. In very limited number of earlier studies conducted in emerging economies, market orientation has been considered as a part of firm capabilities, or as a part of firm resources or how it leads to different types of learning or innovations in organizations. None of the studies so far have tried to find out how firm's resources, capabilities and technology would adversely affect market orientation. In the present study we have treated market orientation differently from firm's resources and capabilities and have tried to find out extent to which the firm's resources, capabilities and technology would contribute to firm's market orientation. From data of 215 Indian B2B companies operating in capability, resource and technology intensive industries...
Words: 9127 - Pages: 37
...Distributed Query Scheduling Service: An Architecture and Its Implementation Ling Liu and Calton Pu Oregon Graduate Institute Department of Computer Science & Engineering P.O.Box 91000 Portland Oregon 97291-1000 USA flingliu,caltong@cse.ogi.edu Kirill Richine University of Alberta Department of Computer Science GSB615, Edmonton T6G2H1 AB, Canada kirill@cs.ualberta.ca Abstract We present the systematic design and development of a distributed query scheduling service (DQS) in the context of DIOM, a distributed and interoperable query mediation system 26]. DQS consists of an extensible architecture for distributed query processing, a three-phase optimization algorithm for generating e cient query execution schedules, and a prototype implementation. Functionally, two important execution models of distributed queries, namely moving query to data or moving data to query, are supported and combined into a uni ed framework, allowing the data sources with limited search and ltering capabilities to be incorporated through wrappers into the distributed query scheduling process. Algorithmically, conventional optimization factors (such as join order) are considered separately from and re ned by distributed system factors (such as data distribution, execution location, heterogeneous host capabilities), allowing for stepwise re nement through three optimization phases: compilation, parallelization, site selection and execution. A subset of DQS algorithms has been...
Words: 16962 - Pages: 68
...It is difficult to achieve high query performance using XML data repositories, since queries are answered by traversing many individual element-to-element links requiring multiple index lookups [23] . In the case of XML data, queries are even more complex because they may contain regular path expressions [24]. Thus additional flexibility is needed in order to traverse data whose structure is irregular or partially unknown to the user. Another option for managing semistructured data is to store and query it with a relational database [22]. In the database community many researchers argue that the relational (and objectrelational) model, due to its maturity and widespread usage, is still the best option [25]. XML query processing is much more complicated than traditional query processing methods because of the structure of XML [1]. A path expression specifies patterns of selection predicates on multiple elements related by a tree structure named Query Tree Pattern (QTP). Consequently, In order to process an XML query, all occurrences of its related QTP should be distinguished in the XML document. This is an expensive task when huge XML documents are attended. The well known query processing method termed as Structural Join is described in [2]. In Structural Join, query is decomposed into some binary join operations. Thus, a huge volume of intermediate results are produced in this method. The Holistic twig join approach [3] do not decompose the query into its binary Parent-Child (P-C)...
Words: 3269 - Pages: 14
...structure of each file, the type and storage format of each data item, and various constraints on the data. Program-data Independence – The structure of data files is stored in the DBMS catalog separately from the access programs. User view (external or view level) – includes a number of external schemas that describes the part of the database that a particular user group is interested in and hides the rest of the database from that user group. DBA – The chief administrator of the database. End user * Casual – occasionally accesses the DB, but they may need different information each time. They use a sophisticated database query language to specify their requests and are typically middle or high-level managers or other occasional browsers (Larry). * Naïve or Parametric end users – Constantly query database, using standard types of queries and updates called canned transactions that have been carefully programmed and tested. (Jada) * Sophisticated end users – engineers,...
Words: 727 - Pages: 3
...ISBN:0849315484 Chapter 1 (Database Design Using Entity-Relationship Diagrams) * Checkpoint 1.1, Problem 1; pp. 13-14 * Checkpoint 1.3, Problem 1, 3; pp. 25 Chapter 2 (Database Design Using Entity-Relationship Diagrams) * Checkpoint 2.1, Problems 1, 3, 5, 7; pp. 39 * Checkpoint 2.3, Problems 1, 3; pp. 61 Chapter 3 (Database Design Using Entity-Relationship Diagrams) * Checkpoint 3.1, Problems 1, 3, 5; pp. 78 Chapter 4 (Database Design Using Entity-Relationship Diagrams) * Checkpoint 4.1, Problems 1, 3, 5; pp. 99 * Checkpoint 4.3, Problems 1, 3; pp. 117 Part II. SQL A Guide to SQL (2008) by Philip J. Pratt and Mary Z. Last Use only Oracle for this part. Please show the complete question, query used, and print the query results. Do the exercises from the Henry Books Database. Chapter 1 (A Guide to SQL, Henry Books) * Exercises - odd numbers Chapter 2 (A Guide to SQL, Henry Books) * Exercises - odd numbers Chapter 3 (A Guide to SQL, Henry Books) * Exercises - odd numbers Chapter 4 (A Guide to SQL, Henry Books) * Exercises - odd numbers Part III. Database System Fundamentals Fundamentals of Database Systems (2011) by Ramez Elmasri; Shamkant B. Navathe Sixth Edition Addison-Wesley Chapter 1 (Fundamentals of Database Systems) * Review Questions 1.1, 1.2, 1.3, 1.4, 1.5, 1.6, 1.7; pp. 27-28 Chapter 2 (Fundamentals of Database Systems) * Review Questions 2.1, 2.2, 2.3, 2.4, 2.5, 2.6, 2.7, 2.8, 2...
Words: 352 - Pages: 2
...Assignment 1: SQL Query Design Diane King June 24, 2014 IT526-01 SQL Query Design Pr4ofessor: Jon McKeeby, DSc. Kaplan University Part1. Install SQL Express Part2. Questions. a. Each CHAR and VARCHAR store non binary strings characters as the maximum length allowed. The difference between the two is the maximum length allowed and how each handle trailing spaces (My SQL, n.d.). The CHAR data type is a fixed length typed and VARCHAR is a variable length data type. When the CHAR data type is used the length of strings allowed in each column definition should be from 0-225. As an example in the SQL database there is a dbo.table where tables hold a type column defined as (CHAR 12). This requires 12 characters for each value. My SQL states that values shorter than the designated length are padded spaces to that length when they are stored; and trailing spaces are removed from CHAR values when they are retrieved, so retrieved values may not be the same length as when stored (para, 2). The VARCHAR maximum numbers are up to 65-535. Any string stored in a VARCHAR column takes the number of characters required “plus one or two bites to record the strings length” (My SQL, n.d.). b. The Query gave the error: Msg 207, Level 16, State 1, Line 3 Invalid column name 'YearHired'. c. The Query returned a error on both therefore, I’m unsure. Msg 207, Level 16, State 1, Line 5 Invalid column name 'FileName'. Msg 207, Level 16, State 1, Line 4 Invalid column name 'filetable'...
Words: 515 - Pages: 3
...In SQL, a join is used to compare and combine rows from two or more table. Inner Join: An inner join returns records only those records are common in both tables. Outer Join An outer join returns a set of records (or rows) that include what an inner join would return but also includes other rows for which no corresponding match is found in the other table. There are three types of outer joins: * Left Outer Join (or Left Join) * Right Outer Join (or Right Join) * Full Outer Join (or Full Join) Each of these outer joins refers to the part of the data that is being compared, combined, and returned. Sometimes nulls will be produced in this process as some data is shared while other data is not. Left Outer Join A left outer join will return all the data in Table 1 and all the shared data (so, the inner part of the Venn diagram example), but only corresponding data from Table 2, which is the right join. Right Outer Join A right outer join returns Table 2's data and all the shared data, but only corresponding data from Table 1, which is the left join. Full Outer Join A full outer join, or full join, which is not supported by the popular MySQL database management system, combines and returns all data from two or more tables, regardless of whether there is shared information. Think of a full join as simply duplicating all the specified information, but in one table, rather than multiple tables. Where matching data is missing, nulls will be produced. These...
Words: 288 - Pages: 2
...1. 2. 3. 4. Problem Solving Using Access 2007 LEARN IN G OUTCO ME S Describe the process of using the Query Wizard using Access. Describe the process of using the Design view for creating a query using Access. Describe the process of adding a calculated field to a query using Access. Describe the process of using aggregate functions to calculate totals in queries using Access. 5. Describe how to format results displayed in calculated fields using Access. Introduction A query is a tool for extracting, combining, and displaying data from one or more tables, according to criteria you specify. For example, in a book inventory database, you could create a query to view a list of all hardcover books with more than 500 pages that you purchased in the past five months. In a query, you can sort information, summarize data (display totals, averages, counts, and so on), display the results of calculations on data, and choose exactly which fields are shown. You can view the results of a query in a tabular format, or you can view the query’s data through a form or on a report (which is covered in Plug-In T8, “Decision Making Using Access 2007”). In this plug-in, you will learn how to use the Query Wizard and Query-By-Example (QBE) tool to solve problems using Microsoft Access 2007. Creating Simple Queries Use the Query Wizard to create a select query. A select query displays data from a table or tables, based on the fields that you select, but it does not sort or filter the...
Words: 1224 - Pages: 5
...in the answer to a query in a particular way, you ____ the records.Answer | | | | Selected Answer: | sort | | | | | | | | To create a parameter query, enter a parameter rather than a specific value as a criterion in the design grid. The parameter must be enclosed in _____.Answer | | | | | Selected Answer: | square brackets [ ] | | | | | | | | To indicate grouping in Access, select _____ as the entry in the Total row for the field to be used for grouping.Answer | | | | | Selected Answer: | Group By | | | | | | | | | | | | To find the largest amount paid amount for a client, select _____ as the entry in the Total row for the Amount Paid column.Answer | | | | | Selected Answer: | MAX | | | | | | | | To include calculated fields in queries, enter a name for the calculated field, a(n) _____, and then the expression in one of the columns in the Field row.Answer | | | | | Selected Answer: | colon (:) | | | | | | | | To create a new query in Design view, click Create on the Ribbon to display the Create tab and then click the ____ button to create a new query.Answer | | | | | Selected Answer: | Query Design | | | | | | | | To create a crosstab, click the ____ button on the Create tab.Answer | | | | | Selected Answer: | Query Wizard | | | | | | | | To export data from a query to Excel, select the query in the Navigation...
Words: 270 - Pages: 2
...some clients are new, not all will have events. Alex typically plans three types of events: Product Rollout, Press Conference, and Celebratory Function. Some of the caterers will work only some types of events, while others will work all types of events. Additionally, the different caterers charge different rates for different events… Alex has supplied you with an excel file with all the information currently available. Requirements ERD: Import the Access 1 Spreadsheet Excel file. Use the Wizard to create tables and relationships. Please take a “screen shot” of your tables and relationships from the relationship view from Access (this is a form of your ERD – Entity Relationship Diagram) and paste it into a ppt file. QUERIES: Create queries that provide the following information using Access 2010. Also, generate the SQL statements using Access 2010 (Please try to understand what the SQL statements are doing). Which clients hold Product Rollouts? Which...
Words: 713 - Pages: 3
...HP Universal CMDB Software Version: UCMDB 10.10, CP 13.00 Universal Discovery Content Guide - HP Integrations Document Release Date: November 2013 Software Release Date: November 2013 Legal Notices Warranty The only warranties for HP products and services are set forth in the express warranty statements accompanying such products and services. Nothing herein should be construed as constituting an additional warranty. HP shall not be liable for technical or editorial errors or omissions contained herein. The information contained herein is subject to change without notice. Restricted Rights Legend Confidential computer software. Valid license from HP required for possession, use or copying. Consistent with FAR 12.211 and 12.212, Commercial Computer Software, Computer Software Documentation, and Technical Data for Commercial Items are licensed to the U.S. Government under vendor's standard commercial license. Copyright Notice © Copyright 1996 - 2013 Hewlett-Packard Development Company, L.P. Trademark Notices Adobe® and Acrobat® are trademarks of Adobe Systems Incorporated. AMD and the AMD Arrow symbol are trademarks of Advanced Micro Devices, Inc. Google™ and Google Maps™ are trademarks of Google Inc. Intel®, Itanium®, Pentium®, and Intel® Xeon® are trademarks of Intel Corporation in the U.S. and other countries. Java and Oracle are registered trademarks of Oracle Corporation and/or its affiliates. Microsoft®, Windows®, Windows NT®, Windows® XP, and Windows...
Words: 15907 - Pages: 64
... JoinKeyword is used in SQL queries for joining two or more tables. Minimum required condition for joining table, is(n-1) where n, is number of tables. A table can also join to itself known as, Self Join. Types of Join The following are the types of JOIN that we can use in SQL. * Inner * Outer * Left * Right Cross JOIN or Cartesian Product This type of JOIN returns the cartesian product of rows from the tables in Join. It will return a table which consists of records which combines each row from the first table with each row of the second table. Cross JOIN Syntax is, SELECT column-name-list from table-name1 CROSS JOIN table-name2; Example of Cross JOIN The class table, ID | NAME | 1 | abhi | 2 | adam | 4 | alex | The class_info table, ID | Address | 1 | DELHI | 2 | MUMBAI | 3 | CHENNAI | Cross JOIN query will be, SELECT * from class, cross JOIN class_info; The result table will look like, ID | NAME | ID | Address | 1 | abhi | 1 | DELHI | 2 | adam | 1 | DELHI | 4 | alex | 1 | DELHI | 1 | abhi | 2 | MUMBAI | 2 | adam | 2 | MUMBAI | 4 | alex | 2 | MUMBAI | 1 | abhi | 3 | CHENNAI | 2 | adam | 3 | CHENNAI | 4 | alex | 3 | CHENNAI | INNER Join or EQUI Join This is a simple JOIN in which the result is based on matched data as per the equality condition specified in the query. Inner Join Syntax is...
Words: 1005 - Pages: 5
