SQL Database

What is SQL?

SQL stands for "Structured Query Language". It is standardized programming language specifically designed for managing and manipulating relational databases. It is used to perform variety of tasks such as querying, inserting, updating and deleting data, as well as managing database structures like creating modifying and deleting tabled. It is a language used to communicate with database like MYSQL, Microsoft SQL Server, DB2 etc.

What is database?

Database is organized collection of structured data for easy access of management. It is usually a collection of one or more tables and tables contain data rows and columns.

Key concepts of a Database

1. Data Organization:

Database organize data into tables, which are similar to spreadsheets. In the database each table contains rows (records) and columns (fields), where each column represents a specific attribute of the data and each raw represents a single record.

2. Database Management System(DBMS):

A DBMS is software that interacts with the database to manage and control data access, storage, and retrieval. Examples of DBMSs include MySQL, PostgreSQL, Oracle, Microsoft SQL Server, and SQLite.

3. Schema:

The schema of a database defines its structure, including the tables, fields, relationships, and constraints. It acts as a blueprint for how data is organized within the database.

EX: IN a company there can be a database with 2 schemas such as employee schema and customer schema. When person logged with employee account that person is able to see only table details of employee schema. Same as if a person logged with customer account that person is able to see only customer details.

4. Queries:

Queries are used to interact with the database, allowing users to retrieve, insert, update, or delete data. SQL (Structured Query Language) is the standard language used to perform these operations.

EX: SELECT * FROM customer;

5. Data Integrity:

Databases enforce rules and constraints to ensure the accuracy and consistency of data. For example, primary keys ensure that each record in a table is unique, and foreign keys enforce relationships between tables.

6. Relationships:

Data in different tables can be related to each other. Common types of relationships include:

One-to-One: Each record in one table corresponds to one record in another table.

One-to-Many: A single record in one table can relate to multiple records in another table.

Many-to-Many: Records in one table can relate to multiple records in another table and vice versa.

7. Indexes:

Indexes are special structures that improve the speed of data retrieval operations on a database table. They work like an index in a book, allowing the DBMS to find data quickly without scanning the entire table.

Applications of Databases:

Business Systems: Managing customer information, inventory, transactions, and employee records.

· Websites: Storing user data, content management, and e-commerce transactions.

· Scientific Research: Managing experimental data, research results, and computational models.

· Healthcare: Managing patient records, treatment plans, and medical histories.

Financial Services: Handling transactions, accounts, and customer data.

SQL keywords, identifiers, operators, constants and clauses

SQL Keywords

SQL keywords are reserved words that have specific meanings in SQL.

Common SQL Keywords:

SELECT: Retrieves data from one or more tables.

INSERT: Adds new records to a table.

UPDATE: Modifies existing records in a table.

DELETE: Removes records from a table.

CREATE: Creates a new database object, such as a table or view.

ALTER: Modifies an existing database object.

DROP: Deletes a database object.

WHERE: Filters records based on specific conditions.

JOIN: Combines rows from two or more tables based on a related column.

GROUP BY: Groups rows that have the same values in specified columns into summary rows.

ORDER BY: Sorts the result set by one or more columns.

HAVING: Filters groups based on specific conditions.

DISTINCT: Removes duplicate rows from the result set.

AS: Renames a column or table in the result set.

IN: Checks if a value exists within a set of values.

LIKE: Searches for a specified pattern in a column.

NULL: Represents missing or unknown data.

SQL Identifiers

Identifiers are the name given by database designer or system users to database objects such as Tables, Columns, Indexes.

Examples of SQL Identifiers:

Table Names: Employees, Orders, Products

Column Names: EmployeeID, FirstName, OrderDate

Database Names: SalesDB, HRDatabase

Aliases: E (alias for Employees table)

Rules for Identifiers:

Must begin with a letter (A-Z, a-z) or an underscore (_).

Can include letters, digits (0-9), and underscores.

Cannot be a SQL keyword (e.g., SELECT, WHERE).

May be case-sensitive depending on the DBMS.

SQL Operators

SQL operators are symbols or keywords that specify the action to be performed in a query. They are used in conditions and expressions.

Common SQL Operators:

Arithmetic Operators:

+ (Addition)

- (Subtraction)

* (Multiplication)

/ (Division)

% (Modulus)

Comparison Operators:

= (Equal to)

<> or != (Not equal to)

> (Greater than)

< (Less than)

>= (Greater than or equal to)

<= (Less than or equal to)

Logical Operators:

AND (Returns true if both conditions are true)

OR (Returns true if at least one condition is true)

NOT (Reverses the result of a condition)

Bitwise Operators: (Used with integer types)

& (Bitwise AND)

| (Bitwise OR)

^ (Bitwise XOR)

Other Operators:

BETWEEN (Checks if a value is within a range)

IN (Checks if a value matches any value in a list)

LIKE (Searches for a specified pattern)

IS NULL (Checks if a value is NULL)

SQL Constants

Constants are fixed values used in SQL queries. They can be of different data types such as numeric, string, and date.

Types of SQL Constants:

Numeric Constants:

Examples: 100, -50, 3.14

String Constants:

Examples: 'John Doe', 'New York', '2024-08-14'

Date/Time Constants:

Examples: '2024-08-14', '12:30:00'

Boolean Constants:

Examples: TRUE, FALSE

NULL

Represents missing or undefined data.

SQL Clauses

SQL clauses are components of SQL statements that specify conditions or actions to be applied to the data.

Common SQL Clauses

SELECT: Specifies the columns to retrieve.

Example: SELECT FirstName, LastName FROM Employees;

FROM: Specifies the table(s) from which to retrieve data.

Example: FROM Employees

WHERE: Filters records based on conditions.

Example: WHERE Salary > 50000

GROUP BY: Groups rows with the same values in specified columns.

Example: GROUP BY Department

ORDER BY: Sorts the result set.

Example: ORDER BY LastName ASC

HAVING: Filters groups after GROUP BY.

Example: HAVING COUNT(*) > 10

JOIN: Combines rows from multiple tables.

Example: INNER JOIN Orders ON Employees.EmployeeID = Orders.EmployeeID

LIMIT: Restricts the number of rows returned.

Example: LIMIT 10

Data Definition Language (DDL) vs Data Manipulation Language(DML)

Data Definition Language (DDL)

· DDL is used to define, modify, and manage the structure of database objects such as tables, indexes, views, and schemas.

· DDL commands primarily deal with the structure of the database rather than the data within it.

· Changes made by DDL commands are usually immediate and cannot be rolled back (depending on the DBMS).

· DDL commands affect the schema of the database.

Key DDL Commands

1. CREATE: Creates new database objects like tables, indexes, or views.

Example:

2. ALTER: Modifies the structure of an existing database object.

Example:

3. DROP: Deletes existing database objects, such as tables, indexes, or views.

4. TRUNCATE: Removes all records from a table, but does not delete the table itself.

Example:

5. RENAME: Renames an existing database object

Data Manipulation Language(DML)

· DML is used to manipulate the data stored within the database objects, such as inserting, updating, deleting, and querying data.

· DML commands interact directly with the data stored in the database.

· DML operations can be part of a transaction and can usually be rolled back if needed.

· DML commands focus on the manipulation of data within the database tables.

Key DML Commands:

1. SELECT: Retrieves data from one or more tables.

2. INSERT: Adds new records to a table

3. UPDATE: Modifies existing records in a table

4. DELETE: Removes records from a table

Create Database and Tables

1. Show all Database:

2. Create a Database:

3. Use the created Database: Before creating tables, you need to select the database you just created.

4. Create Tables: create tables within the CompanyDB database.

Insert Data into tables

First Method

· Insert into <tablename> values (value1, value2, value3);

Need to add all the values related to the table

Second method

· Insert into <tablename> (column1, column2, column3, column4….)

Values (value1, value2, value3, value4,…);

· If need not to add any not null value we are able add data without adding that value for that when we adding data we should not add column name which we no need to add data.

EX:

Select Statement in SQL

“Select” Statement allows to query and retrieve data from one or more tables in a database.

1. Selecting all columns from table

· To retrieve all columns from a table, you can use the * wildcard.

2. Selecting specific columns.

3. Using the “Where” Clause

· The WHERE clause filters the results based on a specified condition.

4. Using “ORDER BY” for Sorting

· You can sort the results using the ORDER BY clause, either in ascending (ASC) or descending (DESC) order.

5. Using ‘DISTINCT’ to Remove Duplicates

· The DISTINCT keyword ensures that only unique values are returned

AND Operator in SQL

· The AND operator in SQL is used in the WHERE clause to combine two or more conditions. When multiple conditions are combined with AND, all of the conditions must be true for a row to be included in the result set.

· The AND operator can be combined with other logical operators like “OR” and comparison operators like ‘=’, ‘>’, ‘<’, ‘>=’, ‘<=’, ‘!=’, ‘LIKE’, etc.

Example Usage

Let’s consider a table named Employees with the following columns:

EmployeeID, FirstName, LastName, DepartmentID, HireDate, Salary;

1. Basic Use of AND

Suppose you want to retrieve all employees who work in the Sales department (where DepartmentID is 1) and have a salary greater than $50,000.

2. Combining Multiple Conditions

If you want to find employees who work in the Marketing department (where DepartmentID is 2), have a salary greater than $50,000, and were hired after January 1, 2021, you would us

OR Operator in SQL

· The OR operator in SQL is used in the WHERE clause to combine two or more conditions. When multiple conditions are combined with OR, at least one of the conditions must be true for a row to be included in the result set.

· Syntax:

Example

Consider the following table:

1. Basic Use of ‘OR’

Suppose you want to retrieve all employees who work in the Sales department (DepartmentID = 1) or have a salary greater than $70,000.

2. Combining Multiple Conditions with ‘OR’

You can use OR to combine more than two conditions. For example, if you want to find employees who either work in the Sales department (DepartmentID = 1), the Marketing department (DepartmentID = 2), or were hired after January 1, 2023:

3. Using ‘OR’ with ‘AND’

If you want to find employees who either work in the Sales department or who were hired after January 1, 2022 and have a salary greater than $55,000:

‘IN’ and ‘NOT IN’ Operators in SQL

· These operators used to record where the specified column’s value matches or not matches with any value in a given list of subquery(Column).

IN Operator

· The IN operator is used to filter records where the specified column's value matches any value in a given list or subquery.

· Syntax

EX:

Consider the table below

Ø If you want to find employees who work in the Sales (DepartmentID = 1) or Marketing (DepartmentID = 2) departments, you can use the IN operator:

Ø Using IN with Subquery:

NOT IN Operator

· The NOT IN operator is the opposite of IN. It is used to filter records where the specified column's value does not match any value in a given list or subquery.

· Syntax

EX:

Ø If you want to find employees who do not work in the Sales or Marketing departments, you can use the NOT IN operator:

Using NOT IN with Subquery:

EXISTS and NOT EXISTS Operators in SQL

EXISTS operator

· The EXISTS operator is used to check if a subquery returns any rows. If the subquery returns one or more rows, the EXISTS operator returns TRUE; otherwise, it returns FALSE.

· Syntax:

EX:

Consider two tables

Suppose you want to find all employees who belong to departments that have a DepartmentID in the Departments table:

NOT EXISTS Operator

· The NOT EXISTS operator is the opposite of EXISTS. It returns TRUE if the subquery returns no rows and FALSE if the subquery returns one or more rows.

· Syntax:

EX:

Suppose you want to find all employees who do not belong to any department listed in the Departments table:

Comparison Operators in SQL

1. “=”: Equal to

2. “<>”, “!=”: Not equal to

3. “>” : Greater than

4. “<” : Less than

5. “>=” : Greater than or equal

6. “<=” : Less than or equal

7. Between

8. Like

· LIKE Operator is used with WHERE clause to search for a specific pattern in a string

· Here use Wildcards for match characters.

· Wildcards (%) and (_) are often used wit like operator.

Ø Percentage Sign (%): Represents zero, one or multiple characters.

Ø The Underscore (_): Represents Single Character

· Examples for Wildcards:

Ø Like ‘n%’ -: starts with ‘n’ and contains with other characters after ‘n’

Ø Like ‘a___’ -: starts with ‘a’ and contains other 3 characters

Ø Like ‘%k’ -: ends with ‘k’

Ø Like ‘_m%’ -: starts with any other character, second character is ‘m’ and rest are any different characters

IS NULL and NOT NULL in SQL

IS NULL:

A field in SQL tables can have NULL or no values if they are defined as optional

· If the field is defined as optional then while inserting/ updating a new record it is possible to add data without any value to this field as its defined as optional.