DataBase – a collection of information related to specific entity (e.g. organization, bank, college, library etc.) DBMS – (Database management system) – a application through which we can control the database i.e. we can store data into database, update the data and delete also we can retrieve the data from database. For doing these operation DBMS support a language called Structured Query language (SQL). SQL accomplishes many powerful tasks with a mere seven statements: SELECT, UPDATE, INSERT, DELETE, CREATE, ALTER, and DROP. Each of these seven statements fall into one of two categories: DML (Data Manipulation Language) statements or DDL (Data Definition Language) statements. These statements are summarized as follows:
SELECT Enables you to select one or more columns from one or more tables in the database. The results can be simply viewed, or can be used as the basis for a form or report, or, in the case of embedded SQL, can be processed as a temporary table (called a "recordset" in Access, called a "cursor" in other DBMSs). UPDATE Enables you to update the data in one or more columns of one or more rows in a table. INSERT Enables you to append rows to an existing table.
DELETE Enables you to delete one or more rows from an existing table.
CREATE Enables you to create a new table, or to create a new index on an existing table. ALTER Enables you to modify the structure of an existing table (by adding or deleting columns). DROP Enables you to delete a table
Consider the Following DataBase: NWINDVBP.MDB
Retrieve all columns and all rows from the Employees table.
Retrieve the Product ID, product name, and unit price from all rows of the Products table: Example 3:
What is the name for product ID 19?
Which customers are from Mexico? (Show Customer ID, company name, contact name, and contact title in the results.) Example 5:
Which employees were hired on October 17, 1993? (Show employee first and last name, and title in the results.) Example 6:
Which meat/poultry products (CategoryID = 6) have less than 10 units in stock? (Show product ID, product name, category ID, and units in stock in the results.) Example 7:
Which items in the Product table are classified as meat/poultry or have less than 10 units in stock? (Show product ID, product name, category ID, and units in stock in the results.) Example 8:
Show the product name, units in stock, category ID, and unit price for seafood products (category ID = 8) that have at least 100 units in stock, or for any item whose unit price is greater than $50. Example 9:
Show the part product name, units in stock, category ID, and unit price for products that have at least 100 units in stock, and that are either classified as seafood (category ID = 8) or have a unit price greater than $50. Example 10:
Show the company name, contact name, and country for all non-US suppliers. Example 11:
Show the Product ID, product name, and unit price for all products that have a unit price between $10 and $15 (inclusive): Example 12:
Show the Order ID, Customer ID, and order date for orders that occurred between February 1 and February 7, 1995. Example 13:
Show company name, contact name, and title for customers whose contact person is considered a "manager". Example 14:
Show company name and address information for all suppliers located in France, Germany, and Italy. Example 15-a:
Show the category ID, unit price, and product name for all products, sorted by unit price (low to high). Example 15-b:
Show the category ID, unit price, and product name for all products, sorted by unit price (high to low). Example 16:
Show the category ID, unit price, and product name for all products, sorted by unit price (high to low) WITHIN category ID. Example 17:
How many products are classified as beverages (category ID 1)? Example 18:
How many order lines do we have, and what is...