Tamilnadu Samacheer Kalvi 12th Computer Science Notes Chapter 12 Structured Query Language (SQL) Notes

Structured Query Language (SQL):

  • The Structured Query Language (SQL) is a standard programming language to access and manipulate databases.
  • SQL allows the user to create, retrieve, alter, and transfer information among databases.
  • It is a language designed for managing and accessing data in a Relational Data Base Management System (RDBMS).

Relational Data Base Management System (RDBMS):

  • RDBMS is a type of DBMS with a row- based table structure that connects related data elements and includes functions related to Create, Read, Update and Delete operations, collectively known as CRUD.
  • Oracle, MySQL, MS SQL Server, IBM DB2 and Microsoft Access are RDBMS packages.

Different categories of SQL commands:

  • DDL – Data Definition Language
  • DML – Data Manipulation Language
  • DCL – Data Control Language
  • TCL – Transaction Control Language
  • DQL – Data Query Language

Data types used in SQL:

  • char (Character)
  • varchar
  • dec (Decimal)
  • Numeric
  • int (Integer)
  • Smallint
  • Float .
  • Real
  • double

Predetermined set of commands of SQL:
The SQL provides a predetermined set of commands like Keywords, Commands, Clauses and Arguments to work on databases.

Keywords:

  • They have a special meaning in SQL.
  • They are understood as instructions .

Commands :
They are instructions given by the user to the database also known as statements

Clauses:
They begin with a keyword and consist of keyword and argument

Arguments:
They are the values given to make the clause complete.

Data Definition Language:

  • The Data Definition Language (DDL) consist of SQL statements used to define the database structure or schema.
  • It simply deals with descriptions of the database schema and is used to create and modify the structure of database objects in databases.
  • The DDL provides a set of definitions to specify the storage structure and access methods used by the database system.

DDL commands:
i) Create ii) Alter iii) Drop iv) Truncate

  • Create : To create tables in the database.
  • Alter : The ALTER command is used
    to alter the table structure like adding a column, renaming the existing column, change the data type of any column or size of the column or delete the column from the table.
  • Drop : Delete tables from database.
  • Truncate : Remove all records from a table, also release the space occupied by those records.

Data Manipulation Language:
Data Manipulation Language (DML) is a computer programming language used for adding (inserting), removing (deleting), and modifying (updating) data in a database.

Basic types of DML:

Procedural DML
Requires a user to specify what data is needed and how to get it.

Non-Procedural DML
Requires a user to specify what data is needed without specifying how to get it.

DML commands:

  • Insert: Inserts data into a table
  • Update : Updates the existing data within a table.
  • Delete : Deletes all records from a table, but not the space occupied by them.

Data Control Language (DCL):
Data Control Language (DCL) is a programming language used to control the access of data stored in a database.

DCL commands:

  • Grant Grants permission to one or more users to perform specific tasks
  • Revoke: Withdraws the access permission given by the GRANT statement.

Transactional Control Language (TCL):

  • Transactional Control Language (TCL) commands are used to manage transactions in the database.
  • These are used to manage the changes made to the data in a table by DML statements.

TCL commands:
Commit: Saves any transaction into the database permanently.

  • Roll back: Restores the database to last commit state.
  • Save point: Temporarily save a transaction so that you can rollback.

Data Query Language (DQL):

  • The Data Query Language consist of commands used to query or retrieve data from a database.
  • One such SQL command in Data Query Language is “Select ” which is used to display the records from the table.

WAMP:
WAMP stands for “Windows, Apache, MySQL and PHP” It is often used for web development and internal testing, but may be also used to serve live websites.

Constraint:
Constraint is a condition,applicable on a field or set of fields or set of fields.

The different types of constraints are:

  1. Unique Constraint
  2. Primary Key Constraint
  3. Default Constraint
  4. Check Constraint
  5. Table Constraint

Samacheer Kalvi 12th Computer Science Notes