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

(i) Create: To create tables in the database.
(ii) AIter: Alters the structure of the database.
(iii) Drop: Delete tables from database.
(iv) Truncate: Remove all records from a table, also release the space occupied by those records.

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

(i) DDL - Data Definition Language
(ii) DML - Data Manipulation Language
(iii) WAMP - Windows, Apache, MySQL, PHP

Oracle, MySQL, MS SQL Server, IBM DB2 and Microsoft Access are RDBMS packages.

SQL	MYSQL
Structured Query Language is a language used for accessing database	MySQL is a database | accessing databases. management system, like SQL Server, Oracle, Informix,postgres,etc.
SQL is a DBMS	MySQL is a RDBMS

 

Command Description Component Insert Inserts data into a table DML Create To create tables in the database DML  

Table Constraints	column constraints
Table constraint is apply to a group of one or more columns.	Column constraint can be apply only to individual columns.

S.No	Unique Key Constraint	Primary Key Constraint
(i)	This constraint ensures that no two rows have the same value in the specified columns.	This constraint declares a field as a Primary Key which helps to uniquely identify a record.
(ii)	The UNIQUE constraint can be Create applied only to fields that have also been declared as NOT NULL.	The Primary Key does not allow NULL values.

SELECT * FROM Student WHERE Age <= 18 ORDER BY Name;
 

(i) DDL - Data Definition Language
(ii) DML - Data Manipulation Language
(iii) DCL - Data Control Language
(iv) TCL - Transaction Control Language
(v) DQL - Data Query Language

One of the most important tasks when working with SQL is to generate Queries and retrieve data. A Query is a command given to get a desired result from the database table. The SELECT command is used to query or retrieve data from a table in the database. It is used to retrieve a subset of records from one or more tables. The SELECT command can be used in various forms:
Syntax of SELECT command:
SELECT < column-list > FROM < table-name > ;
(i) Table-name is the name of the table from which the information is retrieved.
(ii) Column-list includes one or more columns from which data is retrieved.

Table Constraint: When the constraint is applied to a group of fields of the table, it is known as Table constraint. The table constraint is normally given at the end of the table definition. Let us take a new table namely Student with the following fields Admno, Firstname, Lastname, Gender, Age, Place:
Create Table Student 1
(
Admno integer NOT NULL,
Firstname char(20),
Lastname char(20),
Gender char(1),
Age integer,
Place char(10),
PRIMARY KEY (Firstname, Lastname) ⟶ Table constraint
);
In the above example, the two fields, Firstname and Lastname are defined as Primary key which is a Table constraint.

The SQL provides a predetermined set of commands 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.

(i) INSERT:
INSERT INTO <table-name>
[column-list] VALUES (values);
(ii) DELETE:
DELETE FROM table-name WHERE condition;
(iii) UPDATE:
UPDATE <table-name > SET column-name = value, column-name = value, ... WHERE condition;

(i) The data in RDBMS, is stored in database objects, called Tables. A table is a collection of related data entries and it consist of rows and columns.
(ii) A field is a column in a table that is designed to maintain specific related information about every record in the table. It is a vertical entity that contains all information associated with a specific field in a table. The fields in a student table may be of the type AdmnNo, StudName, StudAge, StudClass, Place etc
(iii) A Record is a row, which is a collection of related fields or columns that exist in a table. A record is a horizontal entity in a table which represents the details of a particular student in a student table

(i) The DISTINCT keyword is used along with the SELECT command to eliminate duplicate rows in the table. This helps to eliminate redundant data.

(ii) For Example: SELECT DISTINCT Place FROM Student;
    Will display the following data as follows:
    Place
    Chennai
    Banglore
    Delhi

 

(i) The SAVEPOINT command is used to temporarily save a transaction so that you can rollback to the point whenever required.
(ii) The different states of our table can be saved at any time using different names and the rollback to that state can be done using the ROLLBACK command.
(iii) SAVEPOINT savepoint_name:
      UPDATE student SET Name = 'Mini' WHERE Admno=105;
      SAVEPOINT A;

ALTER TABLE <table-name> ADD <column- name><data type><size>;
Example: ALTER TABLE Student MODIFY Address char (25)

 

 

Constraint:
Constraints are used to limit the type of data that can go into a table. This ensures the accuracy and reliability of the data in the database. Constraints could be either on a column level or a table level. Constraint is a condition applicable on a field or set of fields.
Primary key constraint:
(i) This constraint declares a field as a Primary key which helps to uniquely identify a record.
(ii) It is similar to unique constraint except that only one field of a table can be set as primary key.
(iii) The primary key does not allow NULL values.
 

TCL commands:
(i) COMMIT command: The COMMIT command is used to permanently save any transaction to the database. When any DML commands like INSERT. UPDATE. DELETE commands are used. the changes made by these commands are not permanent. It is marked permanent only after the COMMIT command is given from the SQL prompt. Once the COMMIT command is given. the changes made cannot be rolled back. The COMMIT command is used as COMMIT;
(ii) ROLLBACK command:
The ROLLBACK command restores the database to the last committed. state. It is used with SAVEPOINT command to jump to a particular savepoint location. The syntax for the ROLLBACK command is: ROLLBACK TO save point name;
(iii) SAVEPOINT command:
The SAVEPOINT command is used to temporarily save a transaction so that you can rollback to the point whenever required. The different states of our table can be saved at anytime using different names and the rollback to that state can be done using the ROLLBACK command.
SAVEPOINT savepoint_name;

(i) TRUNCATE command: The TRUNCATE command is used to delete all the rows from the table. the structure remains and the space is freed from the table. The syntax for TRUNCATE command is:
TRUNCATE TABLE table-name;
For example to delete all the records of the student table and delete the table the SQL statement is given as follows:
TRUNCATE TABLE Student;
The table Student is removed and the space is freed.
(ii) DROP TABLE command: The DROP TABLE command is used to remove a table from the database. If you drop a table. all the rows in the table is deleted and the table structure is removed from the database. Once a table is dropped we cannot get it back. so be careful while using DROP TABLE command. But there is a condition for dropping a table; it must be an empty table.
Remove all the rows of the table using DELETE command. The DELETE command is already explained.
To delete all rows, the command is given as;
DELETE * FROM Student;
Once all the rows are deleted. the table can be deleted by DROP TABLE command in the following way:
DROP TABLE table-name;
For example to delete the Student table:
DROP TABLE Student;

The various processing skills of SQL are:
(i) Data Definition Language (DDL): The SQL DDL provides commands for defining relation schemas (structure), deleting relations, creating indexes and modifying relation schemas.
(ii) Data Manipulation Language (DML): The SQL DML includes commands to insert, delete, and modify tuples in the database.
(iii) Embedded Data Manipulation Language: The embedded form of SQL is used in high level programming languages.
(iv) View Definition: The SQL also includes commands for defining views of tables.
(v) Authorization: The SQL includes commands for access rights to relations and views of tables.
(vi) Integrity: The SQL provides forms for integrity checking using condition.
(vii) Transaction control: The SQL includes commands for file transactions and control over transaction processing.

Table constraint:
Table constraint apply to a group of one or more columns. The Syntax tor a table created with Constraint is given as below:
CREATE TABLE <table_name>
(<column name> <datatype>[<size>]
<column constraint>,
(<column name> <data type> [<size>]
<column constraint).....
<table constraint>(<column name>, [<column name>..........])............);
Following is an example for employee table with "NOT NULL' column constraint. This constraint enforces a field to always contain a value.
CREATE TABLE employee (No int NOT NULL PRIMARY KEY,
NAME varchar(50), Age int (10), Address varchar(50)
PhoneNo int(10), Pincode int (6) PRIMARY KEY (NAME, PhoneNo);
The above command creates a table "employee" in which the field No of integer type is defined NOT NULL, which means this field must have values. The field NAME is of char type. The fields NAME, PhoneNo, have table constraint.

1. SELECT * FROM student WHERE gender = Male GROUP BY gender;
2. SELECT * FROM student WHERE Age > 18 ORDER BY DESC;

SQL commands are divided into five categories:

Data Definition Language:
(i) The Data Definition Language (DDL) consist of SQL statements used to define the database structure or schema.
(ii) It simply deals with descriptions of the database schema and is used to create and modify the structure of database objects in databases.
(iii) The DDL provides a set of definitions to specify the storage structure and access methods used by the database system.
DDL performs the following functions:
(i) It should identify the type of data division such as data item, segment, record and database file.
(ii) It gives a unique name to each data item type, record type, file type and data base.
(iii) It should specify the proper data type.
(iv) It should define the size of the data item.
(v) It may define the range of values that a data item may use.
(vi) It may specify privacy locks for preventing unauthorized data entry.
Commands:

CREATE	To create tables in the database.
ALTER	Alters the structure of the database
DROP	Data tables from database.
TRUNCATE	Remove all records from a table, also release the space occupied bv those records.

Data Manipulation Language:
This is a computer programming language used for adding, removing and modifying data in a database. This language comprises the SQL-data change statements, which modify stored data but not the schema of the database table
Data Manipulation includes
(i) Insertion of new information into the database
(ii) Retrieval of information stored in a database
(iii) Deletion of information from the database.
(iv) Modification of data stored in the database
The DML is basically of two types:
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.
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:
(i) A Data Control Language (DCL) is a programming language used to control the access of data stored in a database. It is used for controlling privileges in the database (Authorization).
(ii) The privileges are required for performing all the database operations such as creating sequences, views of tables etc.
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:
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.
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:
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

It displays the records from the table.

Constraints ensure database integrity, therefore known as database integrity constraints. The different type of constraints are:

1. Unique Constraint:
(i) This constraint ensures that no two rows have the same value in the specified columns. For example UNIQUE constraint applied on Admno of student table ensures that no two students have the same admission number and the constraint can be used as:
CREATE TABLE Student:
(
Admno integer NOT NULL UNIQUE, 
Name char (20) NOT NULL,
Gender char (1),
Age integer,
Place char (10),
);
(ii) The UNIQUE constraint can be applied only to fields that have also been declared as NOT NULL.
(iii) When two constraints are applied on a single field, it is known as multiple constraints. In the above Multiple constraints NOT NULL and UNIQUE are applied on a single field Admno, the constraints are separated by a space and the end of the field definition a comma(,) is added. By adding these two constraints the field Admno must take some value ie. will not be NULL and should not be duplicated.
2. Primary Key Constraint:
(i) This constraint declares a field as a Primary key which helps to uniquely identify a record. It is similar to unique constraint except that only one field of a table can be set as primary key.
(ii) The primary key does not allow ULI values and therefore a field declared as primary key must have the NOT NULL constraint.
Example:
CREATE TABLE Student:
(
Admno integer NOT NULL PRIMARY KEY,
Name char(20)NOT NULL,
Gender char(1),
Age integer,
Place char(10),
);
3. Default Constraint:
(i) The DEFAULT constraint is used to assign a default value for the field. When no value is given for the specified field having DEFAULT constraint, automatically the default value will be assigned to the field.
Example:
CREATE TABLE Student:
(
Admno integer NOT NULL PRIMARY KEY,
Name char(20)NOT NULL, .
Gender char(1),
Age integer DEFAULT = "17':
Place char(10),
);
4. Check Constraint:
(i) This constraint helps to set a limit value placed for a field. When we define a check constraint on a single column, it allows only the restricted values on that field. Example showing check constraint in the student table:
CREATE TABLE Students
(
Admno integer NOT NULL PRIMARY KEY
Name char(20)NOT NULL,
Gender char(1),
Age integer (CHECK< = 19), 
Place char(10),
);
5. Table Constraint:
(i) When the constraint is applied to a group of fields of the table, it is known as Table constraint. The table constraint is normally given at the end of the table definition.
CREATE TABLE Student
(
Admno integer NOT NULL,
Firstname char(20),
Lastname char(20),
Gender char(1),
Age integer,
Place char(10),
PRIMARY KEY (Firstname, Lastname) 
);