SQL stands for Structured Query Language. It is a domain-specific programming language designed for managing and manipulating relational databases. SQL is used to communicate with and manage the data stored in a relational database management system (RDBMS).
Here are some key aspects of SQL:
1. **Data Query Language (DQL):** SQL allows users to query the database to retrieve specific information using SELECT statements. This is known as the Data Query Language.
**Data Definition Language (DDL):** SQL includes statements like CREATE, ALTER, and DROP, which are used to define and manage the structure of the database, including tables, indexes, and constraints.
**Data Manipulation Language (DML):** SQL provides statements like INSERT, UPDATE, and DELETE to manipulate the data stored in the database.
**Data Control Language (DCL):** SQL includes statements such as GRANT and REVOKE to control access to the database, allowing or restricting certain operations for different users.
**Transaction Control Language (TCL):** SQL supports transactions, and statements like COMMIT, ROLLBACK, and SAVEPOINT are used to control the transactions in a database.
SQL is a standardized language, but different database management systems may have their own variations and extensions of SQL. Popular relational database systems that use SQL include MySQL, PostgreSQL, SQLite, Microsoft SQL Server, and Oracle Database.
SQL can be broadly categorized into three main parts:
1. **Data CONTROL Language (DCL):**
- **Purpose:** DQL is used to retrieve data from the database.
- **Key Command:** `SELECT` statement is the primary command for DQL.
- **Example:**
```sql
SELECT column1, column2 FROM table_name WHERE condition;
```
2. **Data Definition Language (DDL):**
- **Purpose:** DDL is used to define the structure of the database, including creating, altering, and deleting database objects.
- **Key Commands:**
- `CREATE` is used to create a new database object (e.g., table).
- `ALTER` is used to modify the structure of an existing database object.
- `DROP` is used to delete a database object.
- **Example:**
```sql
CREATE TABLE table_name (
column1 datatype,
column2 datatype,
...
);
```
3. **Data Manipulation Language (DML):**
- **Purpose:** DML is used to manipulate data stored in the database, such as inserting, updating, and deleting records.
- **Key Commands:**
- `INSERT` is used to add new records to a table.
- `UPDATE` is used to modify existing records in a table.
- `DELETE` is used to remove records from a table.
- **Examples:**
```sql
INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...);
```
```sql
UPDATE table_name SET column1 = value1 WHERE condition;
```
```sql
DELETE FROM table_name WHERE condition;
```
These three parts collectively provide a comprehensive set of functionalities for working with relational databases using SQL. Additionally, SQL includes other components like Data Control Language (DCL) and Transaction Control Language (TCL) for managing access and transactions, respectively.
example for better understand:
Q-1: Employee (employee-name, street, city)
Works (employee-name, company-name, salary)
Company (company-name, city)
Manages (employee-name, manager-name)
schema for a relational database with four tables: `Employee`, `Works`, `Company`, and `Manages`. Each table has a set of attributes (columns) that define the information stored in the database. Here's a brief overview of each table:
1. **Employee Table:**
- `employee-name`: Name of the employee.
- `street`: Street address of the employee.
- `city`: City where the employee resides.
```sql
CREATE TABLE Employee (
employee_name VARCHAR(255),
street VARCHAR(255),
city VARCHAR(255),
PRIMARY KEY (employee_name)
);
```
2. **Works Table:**
- `employee-name`: Name of the employee (foreign key referencing `Employee` table).
- `company-name`: Name of the company where the employee works (foreign key referencing `Company` table).
- `salary`: Salary of the employee at that company.
```sql
CREATE TABLE Works (
employee_name VARCHAR(255),
company_name VARCHAR(255),
salary DECIMAL(10, 2),
PRIMARY KEY (employee_name, company_name),
FOREIGN KEY (employee_name) REFERENCES Employee(employee_name),
FOREIGN KEY (company_name) REFERENCES Company(company_name)
);
```
3. **Company Table:**
- `company-name`: Name of the company.
- `city`: City where the company is located.
```sql
CREATE TABLE Company (
company_name VARCHAR(255),
city VARCHAR(255),
PRIMARY KEY (company_name)
);
```
4. **Manages Table:**
- `employee-name`: Name of the employee (foreign key referencing `Employee` table).
- `manager-name`: Name of the manager (employee) who manages the employee.
```sql
CREATE TABLE Manages (
employee_name VARCHAR(255),
manager_name VARCHAR(255),
PRIMARY KEY (employee_name),
FOREIGN KEY (employee_name) REFERENCES Employee(employee_name),
FOREIGN KEY (manager_name) REFERENCES Employee(employee_name)
);
```
Note: The data types (e.g., `VARCHAR(255)`, `DECIMAL(10, 2)`) and constraints (e.g., `PRIMARY KEY`, `FOREIGN KEY`) are just placeholders and may need adjustment based on the specific requirements of your database system. Also, the foreign key references assume a one-to-many relationship between employees and companies, and between employees and managers. Adjust the schema based on your specific use case and business rules.
if question is
Find the names of employees who do not work for First Bank Corporation using sql
To find the names of employees who do not work for "First Bank Corporation," you can use a SQL query with the `NOT EXISTS` or `NOT IN` clause. Here's an example using the `NOT EXISTS` approach:
```sql
SELECT Employee.employee_name
FROM Employee
WHERE NOT EXISTS (
SELECT 1
FROM Works
WHERE Works.employee_name = Employee.employee_name
AND Works.company_name = 'First Bank Corporation'
);
This query selects the names of employees from the `Employee` table where there is no corresponding record in the `Works` table indicating that the employee works for "First Bank Corporation."
Adjust the table and column names as needed based on your actual database schema. Additionally, ensure that the company name is written exactly as it appears in the data to avoid case sensitivity issues.
0 Comments