display the name, salary and age employees whose names contain'a'.give SQL query using like operator ?
Answers
QUERYING THE DATABASE: QUERIES and VIEWS
Query: Statement that allows data retrieval
View: A virtual table; a saved query (the SELECT statement, not the result)
SELECT statement (DML)
- retrieves a limited set of data from one or more tables using criteria specified in the WHERE clause
- often used to perform calculations on the data selected
- the result set is displayed as a table (columns and rows)
Single-table example (review):
Current Product List: all data comes from the Products table
SYNTAX
SELECT column list
FROM tablename
WHERE criteria
ORDER BY column list
Select from two tables: Example
Run the Orders Query (Orders Qry on the Query list): It lists all orders for all customers, without going into line items (order details), by retrieving related data from the Orders and Customers tables.
orders query result set
Note the number of rows and columns; several columns are repeated more often than strictly necessary.
Use the drop-down list next to the View button (circled above) to switch to SQL view. This is the SQL statement, separated into logical sections for ease of interpretation:
SELECT
Orders.OrderID, Orders.CustomerID, Orders.EmployeeID, Orders.OrderDate, Orders.RequiredDate, Orders.ShippedDate, Orders.ShipVia, Orders.Freight, Orders.ShipName, Orders.ShipAddress, Orders.ShipCity, Orders.ShipRegion, Orders.ShipPostalCode, Orders.ShipCountry,
Customers.CompanyName, Customers.Address, Customers.City, Customers.Region, Customers.PostalCode, Customers.Country
FROM Customers
INNER JOIN Orders
ON Customers.CustomerID = Orders.CustomerID;
Note: The table names need not be repeated unless the same column names exist in both tables. The table names are only required in the FROM, JOIN, and ON clauses, and in the latter, only because the relating column, CustomerID, has the same name in both tables.
The query syntax shown above follows ANSI (American National Standards Institute) rules and should work in the latest versions of all relational databases. Older syntax includes the join condition in the WHERE clause (theta style). Note the number of rows and columns in the result set for the Orders Query and try the same example (with fewer columns), using the older style and table aliases, as follows:
SELECT o.OrderID, o.EmployeeID, o.OrderDate, o.RequiredDate, o.ShippedDate, o.ShipVia, o.Freight, c.CompanyName, c.Address, c.City, c.Region, c.PostalCode, c.Country
FROM Customers c, Orders o
WHERE c.CustomerID = o.CustomerID;
Note for MS Access users: Compare this query in design view with the ANSI style query. MS Access runs the query correctly but cannot represent it in the usual way In the graphical query interface.
JOIN OPERATOR
The JOIN operator specifies how to relate tables in the query. The JOIN operator is one of the set operations available in relational databases.
The following join types of join are available in most relational databases:
INNER
OUTER (LEFT. RIGHT, FULL)
CROSS
Joins may be represented as Venn diagrams, as shown below along with other common set operations:
join types
Result of applying these joins in a query:
INNER JOIN: Select only those rows that have values in common in the columns specified in the ON clause.
LEFT, RIGHT, or FULL OUTER JOIN: Select all rows from the table on the left (or right, or both) regardless of whether the other table has values in common and (usually) enter NULL where data is missing. (Note: FULL OUTER JOIN not implemented in Access.)
CROSS JOIN (not illustrated - not exactly a set operation): Select all possible combinations of rows and columns from both tables (Cartesian product). Not available in Access but can "happen" by not specifying relationships between tables or not setting up the appropriate joins in a query. (Not A Good Thing - the query may run for a very long time and produce a huge, not very useful result set.)
Access uses the ANSI (American National Standards Institute) style, with the JOIN and ON keywords. Access, MySQL, and Oracle all use similar syntax, with more join types and options and other set operations in MySQL and Oracle (CROSS JOIN, FULL OUTER JOIN, INTERSECT, MINUS).
Select from two tables: More examples
Alphabetical List of Products: Lists products that have not been discontinued and the product category, using all columns from Products (Products.*) and one from Categories:
SELECT Products.*, Categories.CategoryName
FROM Categories
INNER JOIN Products
ON Categories.CategoryID=Products.CategoryID