Subqueries are queries embedded in queries. They are used to retrieve data from one table based on data in another table. They generally are used when tables have some kind of relationship. For example, in the NorthWind database, the Orders table has a CustomerID field, which references a customer in the Customers table. Retrieving the CustomerID for a specific order is pretty straightforward. Code Sample 1 /* Find the CustomerID of the company that placed order 10290. */ SELECT CustomerID FROM Orders WHERE OrderID = 10290; Code Explanation This will return COMMI, which is very likely meaningless to the people reading the report.
The next query uses a subquery to return a meaningful result. 1 Code Sample 2 — Find the name of the company that placed order 10290. SELECT CompanyName FROM Customers WHERE CustomerID = (SELECT CustomerID FROM Orders WHERE OrderID = 10290); Code Explanation The above code returns Comercio Mineiro, which is a lot more useful than COMMI. The subquery can contain any valid SELECT statement, but it must return a single column with the expected number of results. For example, if the subquery returns only one result, then the main query can check for equality, inequality, greater than, less than, etc.
On the other hand, if the subquery returns more than one record, the main query must check to see if a field value is (or is NOT) IN the set of values returned. Code Sample 3 — Find the Companies that placed orders in 1997 /****************************** The query below will work in SQL Server ******************************/ SELECT CompanyName FROM Customers WHERE CustomerID IN (SELECT CustomerID FROM Orders WHERE OrderDate BETWEEN ‘1-Jan-1997′ AND ’31-Dec-1997’); Code Explanation The above SELECT statement will return the following results: 2 3 EXERCISE 1… Subqueries Duration: 20 to 30 minutes.
The Business plan on Financial Ratios and Stock Return: Evidence on selected Plantation Companies in Malaysia
UNIVERSITI MALAYSIA SARAWAK (UNIMAS) SEMESTER 2 2012/2013 FACULTY OF ECONOMICS AND BUSINESS (FEB) EBF 3183 FINANCE SEMINAR (Group ASSIGNMENT) Financial Ratios and Stock Return: Evidence on selected Plantation Companies in Malaysia NAME:VICTORIA AK JUTI 28578 VENOSHNI A/P MANOGARAN 28577 PHUA WEE WEE 27952 TEOH CHIEN NI 28513 LING LING26752 GROUP:1 PROGRAMME:FINANCE Financial Ratio and Stock ...
In this exercise, you will practice writing subqueries. 1. Create a report that shows the product name and supplier id for all products supplied by Exotic Liquids, Grandma Kelly’s Homestead, and Tokyo Traders. o You will need to escape the apostrophe in “Grandma Kelly’s Homestead. ” To do so, place another apostrophe in front of it. For example, o o SELECT * FROM Suppliers WHERE CompanyName=’Grandma Kelly”s Homestead’; 2. Create a report that shows all products by name that are in the Seafood category. 3. Create a report that shows all companies by name that sell products in CategoryID 8. 4.
Create a report that shows all companies by name that sell products in the Seafood category. Joins How can we find out… • • • Which products are provided by which suppliers? Which customers placed which orders? Which customers are buying which products? Such reports require data from multiple tables. Enter joins. Syntax SELECT table1. column, table2. column FROM table1 JOIN table2 ON (table1. column=table2. column) WHERE conditions Creating a report that returns the employee id and order id from the Orders table is not difficult. 4 Code Sample 4 — Find the EmployeeID and OrderID for all orders SELECT EmployeeID, OrderID.
FROM Orders;
But this isn’t very useful as we cannot tell who the employee is that got this order. The next sample shows how we can use a join to make the report more useful. Code Sample 5 — Create a report showing employee orders. SELECT Employees. EmployeeID, Employees. FirstName, Employees. LastName, Orders. OrderID, Orders. OrderDate FROM Employees JOIN Orders ON (Employees. EmployeeID = Orders. EmployeeID) ORDER BY Orders. OrderDate; Code Explanation The above SELECT statement will return the following results: 5 Table names are used as prefixes of the column names to identify the table in which to find the column.
The Review on Employee Engagement Sheme
Chapter 1.INTRODUCTION 1.1 Concept of employee engagement 1.1.1 Defining Engagement One of the challenges of defining engagement is the lack of a universal definition of employee engagement, as a research focus on employees’ work engagement is relatively new. More often than not, definitions of engagement include cognitive, emotional, and behavioral components. The cognitive aspect of engagement ...
Although this is only required when the column name exists in both tables, it is always a good idea to include the prefixes as it makes the code more efficient and easier to read. Table Aliases Using full table names as prefixes can make SQL queries unnecessarily wordy. Table aliases can make the code a little more concise. The example below, which is identical in functionality to the query above, illustrates the use of table aliases. 6 Code Sample 6 — Create a report showing employee orders using Aliases. SELECT e. EmployeeID, e. FirstName, e. LastName, o. OrderID, o. OrderDate FROM Employees e JOIN Orders o ON (e. EmployeeID = o. EmployeeID) ORDER BY o. OrderDate; Multi-table Joins Multi-table joins can get very complex and may also take a long time to process, but the syntax is relatively straightforward. Syntax SELECT table1. column, table2. column, table3. column FROM table1 JOIN table2 ON (table1. column=table2. column) JOIN table3 ON (table2. column=table3. column) WHERE conditions Note that, to join with a table, that table must be in the FROM clause or must already be joined with the table in the FROM clause. Consider the following. SELECT table1. column, table2. column, table3. column FROM table1
The above code would break because it attempts to join table3 with table2 before table2 has been joined with table1. Code Sample 7 /* Create a report showing the Order ID, the name of the company that placed the order, and the first and last name of the associated employee. Only show orders placed after January 1, 1998 that shipped after they were required. Sort by Company Name. */ 7 /****************************** This query will work in SQL Server ******************************/ SELECT o.
OrderID, c. CompanyName, e. FirstName, e. LastName FROM Orders o JOIN Employees e ON (e. EmployeeID = o. EmployeeID) JOIN Customers c ON (c. CustomerID = o. CustomerID) WHERE o. ShippedDate > o. RequiredDate AND o. OrderDate > ‘1-Jan-1998’ ORDER BY c. CompanyName; Code Explanation The above SELECT statement will return the following results: 8 EXERCISE 2… Using Joins Duration: 25 to 40 minutes. In this exercise, you will practice using joins. 1. Create a report that shows the order ids and the associated employee names for orders that shipped after the required date. It should return the following.
The Essay on This Paper Talks About How The Employer employee Relationship Has Evolved
This paper talks about how the employer-employee relationship has evolved over a period of time and how it is changing in the days of recession. This relationship is mostly governed by the decisions an organization takes about its employees and how the leader of the organization win the trust of their people in the bad times. Gone are the days when a person would join an organization and continue ...
There should be 37 rows returned. 9 2. Create a report that shows the total quantity of products (from the OrderDetails table) ordered. Only show records for products for which the quantity ordered is fewer than 200. The report should return the following 5 rows. 3. Create a report that shows the total number of orders by Customer since December 31, 1996. The report should only return rows for which the NumOrders is greater than 15. The report should return the following 5 rows. 4. Create a report that shows the company name, order id, and total price of all products of which NorthWind has sold more than $10,000 worth.
There is no need for a GROUP BY clause in this report. 10 Outer Joins So far, all the joins we have worked with are inner joins, meaning that rows are only returned that have matches in both tables. For example, when doing an inner join between the Employees table and the Orders table, only employees that have matching orders and orders that have matching employees will be returned. As a point of comparison, let’s first look at another inner join. Code Sample 8 /* Create a report that shows the number of employees and customers from each city that has employees in it. */
