Introduction to SQL ( Structured Query Language ) and SQL Sublanguage

Introduction to SQL (Structured Query Language) and SQL Sublanguage

SQL (Structured Query Language) is a programming language that is used to manage and manipulate data stored in relational databases. It is the standard language for working with relational databases and is widely used in a variety of industries and applications.

SQL (Structured Query Language) is used to create, modify, and query databases, and it allows users to perform a wide range of tasks, including:

  1. Creating and modifying database structures, such as tables and indexes
  2. Inserting, updating, and deleting data in a database
  3. Retrieving specific data from a database using queries
  4. Setting permissions on tables and other database objects
  5. Generating reports and aggregating data from multiple tables

SQL (Structured Query Language) has a number of important features that make it well-suited for working with databases, including:

  1. A simple and easy-to-learn syntax: SQL has a relatively simple syntax, making it easy for users to learn and use.

  2. Support for a wide range of data types: SQL supports a wide range of data types, including numbers, strings, and dates, and it allows users to define custom data types as well.

  3. Powerful query capabilities: SQL allows users to write complex queries that can retrieve, aggregate, and manipulate data from multiple tables and sources.

  4. Portability: SQL is a standardized language that is supported by many different database management systems (DBMS). This means that SQL queries written for one DBMS can often be used on another DBMS with minimal modification.

SQL (Structured Query Language) is an important tool for working with databases and is widely used in a variety of industries and applications. It is a powerful and versatile language that is essential for anyone working with data management and analysis.

SQL (Structured Query Language) Sublanguage

Data Definition Language

SQL DDL (Data Definition Language) is a sublanguage of SQL (Structured Query Language) that is used to define the database schema. It consists of a set of commands that allow you to create, modify, and delete database objects such as tables, indices, and users.

Some common SQL DDL commands include:

  • CREATE: used to create a new database object such as a table or index.

  • ALTER: used to modify an existing database object. For example, you can use ALTER TABLE to add or delete columns in a table, or ALTER INDEX to rebuild an index.

  • DROP: used to delete a database object.

  • TRUNCATE: used to delete all data from a table, but unlike DROP, it preserves the structure of the table for future use.

Here are some examples of how these commands can be used:

-- create a new table named "employees" with three columns: "id", "name", and "department"

CREATE TABLE employees (

  id INTEGER PRIMARY KEY,

  name VARCHAR(255),

  department VARCHAR(255)

);


-- add a new column named "salary" to the "employees" table

ALTER TABLE employees ADD COLUMN salary INTEGER;


-- delete the "salary" column from the "employees" table

ALTER TABLE employees DROP COLUMN salary;


-- delete the "employees" table and all its data

DROP TABLE employees;


-- delete all data from the "employees" table, but preserve the structure

TRUNCATE TABLE employees;

SQL DDL is an important part of working with databases, as it allows you to define the structure of your data and make changes as needed.


Data Modification Language

SQL DML (Data Modification Language) is a sublanguage of SQL (Structured Query Language) that is used to manipulate data in a database. It consists of a set of commands that allow you to insert, update, and delete data from database tables.

Some common SQL DML commands include:

  • SELECT: used to retrieve data from a database table.

  • INSERT INTO: used to insert new rows into a table.

  • UPDATE: used to modify existing data in a table.

  • DELETE FROM: used to delete rows from a table.

Here are some examples of how these commands can be used:

-- retrieve all rows from the "employees" table

SELECT * FROM employees;


-- insert a new row into the "employees" table

INSERT INTO employees (id, name, department) VALUES (1, 'John', 'IT');


-- update the "name" and "department" columns for the row with id=1

UPDATE employees SET name='Jane', department='Marketing' WHERE id=1;


-- delete the row with id=1 from the "employees" table

DELETE FROM employees WHERE id=1;

SQL DML is an important part of working with databases, as it allows you to modify the data stored in your tables and retrieve it for various purposes.


Data Control Language

SQL DCL (Data Control Language) is a sublanguage of SQL (Structured Query Language) that is used to control access to data in a database. It consists of a set of commands that allow you to grant or revoke permissions to users or roles to perform certain actions on the database.

Some common SQL DCL commands include:

  • GRANT: used to give permissions to users or roles to perform certain actions on the database.

  • REVOKE: used to revoke permissions that have been granted to users or roles.

Here are some examples of how these commands can be used:

-- grant the "SELECT" and "INSERT" permissions to the user "john" on the " employees" table

GRANT SELECT, INSERT ON employees TO john;


-- revoke the "SELECT" and "INSERT" permissions from the user "john" on the

 "employees" table

REVOKE SELECT, INSERT ON employees FROM john;

SQL DCL is an important part of working with databases, as it allows you to control access to data and ensure that only authorized users can perform certain actions on the database.


Next to Learn - SQL Database Objects

Next Post Previous Post
No Comment
Add Comment
comment url