SQL NOTES

SQL Commands
o SQL commands are instructions. It is used to communicate with the database. It is also used to perform specific tasks,
functions, and queries of data.
o SQL can perform various tasks like create a table, add data to tables, drop the table, modify the table, set permission
for users.
Types of SQL Commands
There are five types of SQL commands: DDL, DML, DCL, TCL, and DQL.

  1. Data Definition Language (DDL)
    o DDL changes the structure of the table like creating a table, deleting a table, altering a table, etc.
    o All the command of DDL are auto-committed that means it permanently save all the changes in the database.
    Here are some commands that come under DDL:
  2. o CREATE
  3. o ALTER
  4. o DROP
  5. o TRUNCATE
  6. a. CREATE It is used to create a new table in the database.
  7. Syntax:
  8. CREATE TABLE TABLE_NAME (COLUMN_NAME DATATYPES[,….]);
    Example:
  9. CREATE TABLE EMPLOYEE(Name VARCHAR2(20), Email VARCHAR2(100), DOB DATE);
    b. DROP: It is used to delete both the structure and record stored in the table.
    Syntax
  10. DROP TABLE ;
    Example
  11. DROP TABLE EMPLOYEE;
    c. ALTER: It is used to alter the structure of the database. This change could be either to modify the characteristics of an existing
    attribute or probably to add a new attribute.
    Syntax:
    To add a new column in the table
  12. ALTER TABLE table_name ADD column_name COLUMN-definition;
    To modify existing column in the table:
  13. ALTER TABLE MODIFY(COLUMN DEFINITION….);
    EXAMPLE
  14. ALTER TABLE STU_DETAILS ADD(ADDRESS VARCHAR2(20));
  15. ALTER TABLE STU_DETAILS MODIFY (NAME VARCHAR2(20));
    d. TRUNCATE: It is used to delete all the rows from the table and free the space containing the table.

SQL NOTES

SQL, or Structured Query Language, is the standard language used for managing and manipulating data in relational databases[1]. It allows users to create, read, update, and delete database records using concise commands.

Key Concepts

  • Tables: Organized collections of data with rows (records) and columns (fields).

A table is a fundamental, two-dimensional data structure consisting of columns (fields) and rows (records) that logically organizes data into a grid-like format, similar to a spreadsheet. Fields define the attributes or properties of the data, while each record contains the actual data for one specific entity or item, all displayed in the respective columns. For example, an employee table would have columns for ‘Name’ and ‘Employee ID’ (fields) and a row for each individual employee (record). 

Introduction to tables

Tables are essential objects in a database because they hold all the information or data. For example, a database for a business can have a Contacts table that stores the names of their suppliers, e-mail addresses, and telephone numbers. Because other database objects depend so heavily on tables, you should always start your design of a database by creating all of its tables and then creating any other objects. Before you create tables in Access, consider your requirements and determine all the tables that you might need. For an introduction to planning and designing a database,

Overview

A relational database like Access usually has several related tables. In a well-designed database, each table stores data about a particular subject, such as employees or products. A table has records (rows) and fields (columns). Fields have different types of data, such as text, numbers, dates, and hyperlinks.

Customers table in Access showing layout of records and fields
  1. A record: Contains specific data, like information about a particular employee or a product.
  2. A field: Contains data about one aspect of the table subject, such as first name or e-mail address.
  3. A field value: Each record has a field value. For example, Contoso, Ltd. or someone@example.com.

Top of Page

Table and field properties

Tables and fields also have properties that you can set to control their characteristics or behavior.

Table and field property sheets

1. Table properties

2. Field properties

In an Access database, table properties are attributes of a table that affect the appearance or behavior of the table as a whole. Table properties are set in the table’s property sheet, in Design view. For example, you can set a table’s Default View property to specify how the table is displayed by default.

A field property applies to a particular field in a table and defines one of the field’s characteristics or an aspect of the field’s behavior. You can set some field properties in Datasheet view. You can also set any field property in Design view by using the Field Properties pane.

Data types

Every field has a data type. A field’s data type indicates the kind of data that the field stores, such as large amounts of text or attached files.

Data type setting

A data type is a field property, but it differs from other field properties as follows:

  • You set a field’s data type in the table design grid, not in the Field Properties pane.
  • A field’s data type determines what other properties the field has.
  • You must set a field’s data type when you create the field.You can create a new field in Access by entering data in a new column in Datasheet view. When you create a field by entering data in Datasheet view, Access automatically assigns a data type for the field, based on the value that you enter. If no other data type is implied by your input, Access sets the data type to Text. If needed, you can change the data type by using the Ribbon.

Examples of automatic data type detection

The following table shows how automatic data type detection works in Datasheet view.

If you enter:Access creates a field with a data type of:JohnShort Texthttp://www.contoso.comYou can use any valid Internet protocol prefix. For example, http://, https://, and mailto: are valid prefixes.Hyperlink1Number, Long Integer50,000Number, Long Integer50,000.99Number, Double50000.389Number, Double12/67The date and time formats recognized are those of your user locale.Date/TimeDecember 31, 2016Date/Time10:50:23Date/Time10:50 amDate/Time17:50Date/Time$12.50The currency symbol recognized is that of your user locale.Currency21.75Number, Double123.00%Number, Double3.46E+03Number, Double

Table relationships

Although each table stores data about a different subject, tables in an Access database usually store data about subjects that are related to each other. For example, a database might contain:

  • A customers table that lists your company’s customers and their addresses.
  • A products table that lists the products that you sell, including prices and pictures for each item.
  • An orders table that tracks customer orders.

Keys

Fields that are part of a table relationship are called keys. A key usually consists of one field, but may consist of more than one field. There are two kinds of keys:

  • Primary key    A table can have only one primary key. A primary key consists of one or more fields that uniquely identify each record that you store in the table. Often, there is a unique identification number, such as an ID number, a serial number, or a code, that serves as a primary key. For example, you might have a Customers table where each customer has a unique customer ID number. The customer ID field is the primary key of the Customers table. When a primary key contains more than one field, it is usually composed of pre-existing fields that, taken together, provide unique values. For example, you might use a combination of last name, first name, and birth date as the primary key for a table about people. For more information, see adding or changing a table’s primary key.
  • Foreign key    A table can also have one or more foreign keys. A foreign key contains values that correspond to values in the primary key of another table. For example, you might have an Orders table in which each order has a customer ID number that corresponds to a record in a Customers table. The customer ID field is a foreign key of the Orders table.

The correspondence of values between key fields forms the basis of a table relationship. You use a table relationship to combine data from related tables. For example, suppose that you have a Customers table and an Orders table. In your Customers table, each record is identified by the primary key field, ID.

To associate each order with a customer, you add a foreign key field to the Orders table that corresponds to the ID field of the Customers table, and then create a relationship between the two keys. When you add a record to the Orders table, you use a value for customer ID that comes from the Customers table. Whenever you want to view any information about an order’s customer, you use the relationship to identify which data from the Customers table corresponds to which records in the Orders table.

An Access table relationship shown in the Relationships window

1. A primary key, identified by the key icon next to the field name.

2. A foreign key — note the absence of the key icon.

Do not add a field if you expect that each unique entity represented in the table might require more than value for the field. Continuing the preceding example, if you want to start tracking orders placed by your customers, you do not add a field to the table, because each customer will have more than one order. Instead, you create a new table to store orders, and then create a relationship between the two tables.

Benefits of using relationships

Keeping data separated in related tables produces the following benefits:

  • Consistency    Because each item of data is recorded only once, in one table, there is less opportunity for ambiguity or inconsistency. For example, you store a customer’s name only once, in a table about customers, rather than storing it repeatedly (and potentially inconsistently) in a table that contains order data.
  • Efficiency    Recording data in only one place means you use less disk space. Moreover, smaller tables tend to provide data more quickly than larger tables. Finally, if you don’t use separate tables for separate subjects, you will introduce null values (the absence of data) and redundancy into your tables, both of which can waste space and impede performance.
  • Comprehensibility    The design of a database is easier to understand if the subjects are properly separated into tables.

Components of a Table

  • Columns (Fields):These are the vertical units that hold a specific attribute or property of the data. For instance, in a table about products, a ‘Price’ column would represent the price of each product. 
  • Rows (Records):These are the horizontal units that contain a complete set of data for a single entity. Each row represents a unique record, like all the information for one employee or one product. 
  • Field Value:This is the actual data entered for a specific field within a particular record, such as the name “John Doe” in the ‘Name’ field for a specific employee record. 

Key Characteristics

  • Organization:Tables provide a structured and organized way to store related information, making it easy to manage, retrieve, and analyze large datasets. 
  • Schema:Tables enforce a schema, which defines the structure, data types, and constraints for the data, ensuring consistency and reliability. 
  • Fundamental Unit:In relational databases, tables are the basic unit for storing related data, allowing for efficient querying and complex relationships between different pieces of information. 
  • Queries: Instructions for retrieving or manipulating data, commonly written using SELECT, INSERT, UPDATE, and DELETE statements[1].

What Are Queries?
Queries are commands used to interact with a database. They allow users to retrieve, insert, update, or delete data stored in tables.

📋 Common SQL Query Types

Query TypePurposeExample
SELECTRetrieve data from one or more tablesSELECT * FROM employees;
INSERTAdd new data to a tableINSERT INTO employees (name, role) VALUES (‘Alice’, ‘Manager’);
UPDATEModify existing data in a tableUPDATE employees SET role = ‘Director’ WHERE name = ‘Alice’;
DELETERemove data from a tableDELETE FROM employees WHERE name = ‘Alice’;

🔍 Why Queries Matter

  • They form the backbone of data-driven applications
  • Enable dynamic reporting, data analysis, and automation
  • Used in web development, data science, enterprise systems, and more
  • Primary Key: The unique identifier for records in a table[1].

A Primary Key is a column (or a combination of columns) in a database table that uniquely identifies each row in that table. It ensures that no two rows have the same value in the primary key column(s).

📌 Key Characteristics

  • Uniqueness: Every value in the primary key must be unique.
  • Non-null: Primary keys cannot contain NULL values.
  • Immutable: Ideally, primary key values should not change over time.
  • Indexed: Most database systems automatically index primary keys for faster lookups.

🧱 Example

sql CREATE TABLE Students ( StudentID INT PRIMARY KEY, Name VARCHAR(100), Age INT );

Here, StudentID is the primary key, ensuring each student record is uniquely identifiable.

🧩 Composite Primary Key

Sometimes, a combination of columns is needed to uniquely identify a record:

sql CREATE TABLE Enrollments ( StudentID INT, CourseID INT, PRIMARY KEY (StudentID, CourseID) );

  • Foreign Key: A reference to the primary key in another table, used to link tables[1].

A Foreign Key is a column (or set of columns) in one table that references the Primary Key in another table. It creates a relationship between the two tables, allowing data to be connected across them.

🧱 Example

Suppose you have two tables:

`sql
CREATE TABLE Departments (
DeptID INT PRIMARY KEY,
DeptName VARCHAR(100)
);

CREATE TABLE Employees (
EmpID INT PRIMARY KEY,
EmpName VARCHAR(100),
DeptID INT,
FOREIGN KEY (DeptID) REFERENCES Departments(DeptID)
);
`

Here:

  • DeptID in Employees is a foreign key that links each employee to a department.
  • It references DeptID in the Departments table, which is the primary key there.

✅ Benefits of Foreign Keys

  • Enforces data consistency (e.g., you can’t assign an employee to a non-existent department).
  • Enables joins between tables for powerful queries.
  • Helps model real-world relationships in structured data.

Common SQL Commands

  • SELECT: Retrieves data from tables.
  • Example: SELECT name FROM employees;[1]
  • INSERT: Adds new data to tables.
  • Example: INSERT INTO employees (name, age) VALUES ('John', 30);[1]
  • UPDATE: Modifies existing data.
  • Example: UPDATE employees SET age = 31 WHERE name = 'John';[1]
  • DELETE: Removes data.
  • Example: DELETE FROM employees WHERE name = 'John';[1]

Important Clauses

  • WHERE: Filters data based on conditions.
  • ORDER BY: Sorts results by specified columns.
  • GROUP BY: Aggregates data and is often used with functions like SUM, COUNT, AVG[1].

Useful Functions

  • COUNT(): Returns the number of records.
  • SUM(): Sums up column values.
  • AVG(): Calculates the average.
  • MIN()/MAX(): Finds the smallest/largest value[1].

Examples

SELECT COUNT(*) FROM orders WHERE status = 'Pending';
SELECT AVG(salary) FROM employees;
SELECT name, SUM(sales) FROM sales_data GROUP BY name;

Joining Tables

  • INNER JOIN: Combines rows that have matching values.

An INNER JOIN returns rows from two or more tables only when there is a match between the joined columns. If no match is found, the row is excluded from the result.

🧱 Syntax Example

sql SELECT Employees.EmpName, Departments.DeptName FROM Employees INNER JOIN Departments ON Employees.DeptID = Departments.DeptID;

📊 What This Does

  • Combines data from Employees and Departments
  • Only includes rows where Employees.DeptID matches Departments.DeptID
  • Excludes employees who aren’t assigned to a department

✅ Use Cases

  • Fetching related data across tables (e.g., orders and customers)
  • Ensuring only valid, connected records are shown
  • Building reports with relational data
  • LEFT JOIN: Includes all rows from the left table, and matched rows from the right.

A LEFT JOIN returns all rows from the left table, and the matched rows from the right table. If there’s no match in the right table, the result will still include the left table’s row, but with NULL values for the right table’s columns.

🧱 Syntax Example

sql SELECT Employees.EmpName, Departments.DeptName FROM Employees LEFT JOIN Departments ON Employees.DeptID = Departments.DeptID;

📊 What This Does

  • Returns every employee, even if they aren’t assigned to a department.
  • If an employee has no matching DeptID in Departments, DeptName will be NULL.

✅ Use Cases

  • Finding unmatched records (e.g., employees without departments)
  • Generating complete lists with optional related data
  • Preserving all data from the primary (left) table

Example:

SELECT employees.name, departments.dept_name
FROM employees
INNER JOIN departments ON employees.dept_id = departments.id;

SQL is a powerful tool for handling structured data and is foundational for developers and analysts working with databases[1].

kamblenayan826

Leave a Reply

Your email address will not be published. Required fields are marked *