 Write procedure for creating database in MS-Access.

Creating a database in Microsoft Access is a straightforward process. Here’s a step-by-step guide to help you set
up a new database:
Open Microsoft Access
Start Microsoft Access: Open the application from your Start menu or desktop shortcut.
Create a New Database

 Select “New Blank Database”: On the Access start screen, choose the "Blank database" option. This is
usually located in the "New" tab.
 Name Your Database: In the "File Name" field, enter a name for your database. Make sure the name is
descriptive and relevant to the data you'll be storing.
 Choose a Location: Click on the folder icon to browse and select where you want to save the database
file.
 Create the Database: Click on the "Create" button to generate the new database.

Create Tables
Tables are the basic building blocks of a database where data is stored.
 Open Table Design View:
o Go to the "Create" tab on the Ribbon.
o Click "Table Design" to open a new table in design view.
 Define Fields:
o Field Name: Enter a name for the field.
o Data Type: Choose the type of data this field will hold (e.g., Text, Number, Date/Time,
Currency, etc.).
o Field Properties: Configure additional settings like Field Size, Format, Input Mask, and
Validation Rules as needed.
 Set Primary Key:
o Select the field that will act as the primary key (a unique identifier for each record).
o Click the "Primary Key" button in the Design tab to set it.
 Save Table:
o Click "Save" (or press Ctrl + S).
o Provide a name for the table and click "OK".
Enter Data into Tables
 Open Table Datasheet View:
o Go to the "Tables" section on the left pane.
o Double-click on the table you created to open it in datasheet view.
 Add Records:
o Enter data directly into the rows of the table.
Create Queries
Queries are used to retrieve and manipulate data based on specific criteria.
 Create a Query:
o Go to the "Create" tab.
o Click "Query Design" to open the Query Design View.
o Add tables or queries to the design grid.
o Drag and drop fields to the grid to specify the data you want to retrieve.
o Set criteria for filtering data.
o Click "Run" to execute the query.
 Save Query:
o Click "Save" and provide a name for your query.
  Create tables in MS ACCESS using different ways.

Creating tables in MS Access can be accomplished in several ways, each suited to different needs and
preferences. Below are various methods to create tables in MS Access:
1. Using the Table Design View
1. Open MS Access and go to the database where you want to create the table.
2. Click on the "Create" tab on the Ribbon.
3. Select "Table Design" from the Tables group. This opens a blank table in Design View.
4. Define your fields:
o Enter a field name in the first column.
o Select the data type from the dropdown in the second column (e.g., Short Text, Number,
Date/Time).
o Set properties for each field as needed (e.g., field size, format).
5. Set a primary key if needed by selecting the field and clicking the "Primary Key" button on the Ribbon.
6. Save the table by clicking on the "Save" icon or pressing Ctrl+S. Provide a name for your table.
Using Templates
1. Open MS Access and go to your database.
2. Click on the "Create" tab on the Ribbon.
3. Select "Table Templates" from the Tables group.
4. Choose a template that fits your needs (e.g., Contacts, Tasks).
5. Modify the template as needed and save it as a new table.

Importing Data to Create a Table

1. Open MS Access and go to your database.

2. Click on the "External Data" tab on the Ribbon.
3. Choose the data source (e.g., Excel, Text File, Access Database).
4. Follow the import wizard:
o Browse and select the file you want to import.
o Choose how you want to import the data (e.g., append to an existing table, create a new table).
o Map fields if necessary and complete the import process.
5. Review the new table created from the imported data.

Creating Tables Using SQL

1. Open MS Access and go to your database.

2. Click on the "Create" tab on the Ribbon.
3. Select "Query Design" from the Queries group.
4. Switch to SQL View by clicking on "SQL View" in the Ribbon.
5. Enter SQL code to create your table.

Assignment:

1. Create table Using SQL Company with columns Comp_Id, Comp_Name, NoOfEmployees,
Annual_TurnOver.
Insert 10 records in a table using insert query.

2. Create table Using Templates (Events) CollegeEvents,

 Insert 10 records in a table using insert query.

 Perform Import data operation in MS ACCESS.

Importing data into MS Access is a straightforward process that allows you to bring data from various
sources into your Access database. Here’s a step-by-step guide to performing an import operation in MS
Access:

Prepare Your Data Source

Before you start, ensure that your data source (such as an Excel file, CSV file, or another Access database) is
properly formatted and accessible.

Open Your MS Access Database

1. Launch MS Access.
2. Open the database where you want to import the data.

3. Choose the Import Source

o Go to the "External Data" Tab:
o Select the Import Source:
Excel: Click on the “Excel” button if you are importing from an Excel spreadsheet.
 OR

Text File: Click on the “Text File” button if you are importing from a CSV or text file.
 OR
o Access Database: Click on the “Access” button if you are importing from another Access database.
 OR

o Other Sources: For other types of data sources, such as XML or SQL Server, click on the appropriate
button in the “Import & Link” group.

4. Import Data

For Excel or Text File:

Browse for the File:
o Click the “Browse” button and locate the file you want to import. Select the file and click
“Open”.
2. Choose Import Options:
o For Excel, you may be asked if your data has headers. Check the appropriate option.
o For text files, specify the delimiter (comma, tab, etc.) and the text qualifier (usually quotes).
3. Select Import Destination:
o Choose whether to import data into a new table or append it to an existing table. Select the
appropriate option.
o If importing into a new table, MS Access will create the table for you based on the data structure.

4. Map Fields:
o If importing into an existing table, map the fields from your data source to the fields in the
existing table. Ensure that the data types match.
5. Review Data:
o Review the data preview to ensure it looks correct. Make any necessary adjustments.
6. Complete the Import:
o Click “Finish” to start the import process.
o You may be prompted to save the import steps for future use. Choose to save or not based on
your preference.
  Perform export data operation in MS ACCESS.

Open Your Access Database:

 Launch Microsoft Access.
 Open the database file (.accdb or .mdb) that contains the data you want to export.

2. Select the Data to Export:

3. Choose the Export Option:

 Go to the "External Data" tab in the Ribbon.
4. Select the File Format:
 In the "Export" group, choose the format you want to export to. Common formats include:
o Excel: For exporting to an Excel workbook.

o Text File: For exporting to a .txt or .csv file.

o PDF or XPS: For exporting to a PDF or XPS document.

o XML: For exporting to an XML file.

Example: To export to Excel:

 Click on "Excel" in the "Export" group.

  Create queries in MS ACCESS for selection, projection, Cartesian product
 Selection
“selection” operation denotes fetching particular rows from a database table that fulfill some
given condition or conditions.

1. Open Your Access Database:

2. Go to the "Create" Tab:

3. Choose the Query Design Option:

4. Choose SQL View

5. Write Select Query with where clause

Query:

Select Columns: SELECT column1, column2, ... FROM table_name

where condition;

To Select all: Select * from table_name where condition;

SELECT DISTINCT Statement: SELECT DISTINCT column1, column2, ...

FROM table_name where condition;

Count All : SELECT COUNT(*) FROM table_name where

condition;

 Projection
projection operation is a fundamental idea used to retrieve precise columns or attributes from a relation or
table. It lets users choose and show simplest the attributes of a hobby even discarding the others. The
projection operation is usually utilized in SQL queries to manipulate and extract records from databases
correctly.

The projection operation on a table simply creates another table by copying the table columns (names and
body parts) from the first table to remove duplicate rows.

Select Columns: SELECT column1, column2, ... FROM table_name;

To Select all: Select * from table_name;

SELECT DISTINCT Statement: SELECT DISTINCT column1, column2, ...

FROM table_name;

ORDER By Clause: SELECT column1, column2, ...

FROM table_name
ORDER BY column1, column2, ... ASC|DESC;

 Cartesian

 The Cartesian product in SQL is the result of combining each row from one table with every row from
another table. It is a type of set operation that creates a new table by taking the cross-product of the rows
from two input tables.

 The Cartesian product is denoted by the CROSS JOIN clause in SQL. When two tables are joined using
the CROSS JOIN operator, the resulting table will have a number of rows equal to the product of the
number of rows in the original tables. This can lead to a very large result set if the input tables are large.
An SQL Cross Join is a basic type of inner join that is used to retrieve the Cartesian product (or cross
product) of two individual tables. That means, this join will combine each row of the first table with
each row of second table

Example:
 As you can see, we considered two table columns: Hair Style and Hair Type. Each of these columns
contain some records that need to be matched. Hence, using cross join, we combine each record in the
"Hair Style" column with all records in the "Hair Type" column. The resultant table obtained is
considered as the Cartesian product or Joined table.

Syntax:

SELECT column_name(s) FROM table1 CROSS JOIN table2 CROSS JOIN table3 CROSS JOIN table4 .... ....
CROSS JOIN tableN;

Assignment:
 created a table named CUSTOMERS, which contains the personal details of customers including their
ID, name, age, address and salary
 insert 10 values into this table CUSTOMERS using the INSERT statement
 create another table ORDERS, containing the details of orders with columns Order Id, Order Date,
Customer Id, Amount.
 insert 5 values into this table ORDERS using the INSERT statement
 created another table named ORDER_RANGE with columns Serial No. and Order Range.
 insert 3 values into this table ORDER_RANGE using the INSERT statement

 execute the following Cross Join query on these tables given above, the cross join combines each row
in CUSTOMERS table ,ORDERS table and ORDER_RANGE table.
Query: “SELECT ID, NAME, AMOUNT, DATE, ORDER_RANGE FROM CUSTOMERS
CROSS JOIN ORDERS CROSS JOIN ORDER_RANGE;”