What is SQL????
SQL stands for Structured Query Language
SQL lets you access and manipulate databases
SQL was invented in the 1970s based on the relational data model. It
was initially known as the structured English query language (SEQUEL).
The term was later shortened to SQL.
SQL is domain specific language
SQL is declarative language (What to do)
SQL became a standard of the American National Standards Institute
(ANSI) in 1986, and of the International Organization for
Standardization (ISO) in 1987
Why SQL
Features of SQL
SQL Commands
SQL commands are instructions. It is used to communicate with
the database. It is also used to perform specific tasks, functions,
and queries of data.
SQL can perform various tasks like create a table, add data to
tables, drop the table, modify the table, set permission for users.
Data Definition Language (DDL)
•DDL changes the structure of the table like creating a table, deleting a table,
altering a table, etc.
•All the command of DDL are auto-committed that means it permanently save
all the changes in the database.
•CREATE It is used to create a new table in the database.
•DROP: It is used to delete both the structure and record
stored in the table.
•ALTER: It is used to alter the structure of the database. This
change could be either to modify the characteristics of an
existing attribute or probably to add a new attribute.
•TRUNCATE: It is used to delete all the rows from the table and
free the space containing the table.
Data Manipulation Language
•DML commands are used to modify the database. It is responsible
for all form of changes in the database.
•The command of DML is not auto-committed that means it can't
permanently save all the changes in the database. They can be
rollback.
•SELECT: Retrieve data from the database
• INSERT: The INSERT statement is a SQL query. It is used to insert
data into the row of a table.
•UPDATE: This command is used to update or modify the value of a
column in the table.
•DELETE: It is used to remove one or more row from a table.
Data Control Language
DCL commands are used to grant and take back authority from any
database user.
• Grant: It is used to give user access privileges to a database.
Example
GRANT SELECT, UPDATE ON MY_TABLE TO SOME_USER, ANOTHER_USER;
•Revoke: It is used to take back permissions from the user.
Example
REVOKE SELECT, UPDATE ON MY_TABLE FROM USER1, USER2;
Transaction Control Language
•Transaction Control Language commands are used to manage
transactions in the database. These are used to manage the
changes made by DML-statements. It also allows statements to be
grouped together into logical transactions.
•Commit: Commit command is used to save all the transactions to the
database.
Syntax:
COMMIT;
•Rollback: Rollback command is used to undo transactions that have not
already been saved to the database.
Syntax:
ROLLBACK;
•SAVEPOINT: Save point command is used to temporarily save a transaction
so that you can rollback to that point whenever necessary.
Once a database is created, you can check it in the list of databases
with the following SQL command: SHOW DATABASES;
[Link]
Numeric Data Types
•INT / INTEGER: Stores whole numbers. The size may vary, but typically
it stores numbers ranging from -2,147,483,648 to 2,147,483,647.
•SMALLINT: A smaller integer, typically ranging from -32,768 to 32,767.
•BIGINT: A larger integer type for very large numbers, ranging from -
9,223,372,036,854,775,808 to 9,223,372,036,854,775,807.
•DECIMAL(p, s) or NUMERIC(p, s): Stores fixed-point numbers where p is
precision (total number of digits) and s is scale (number of digits after
the decimal point).
•FLOAT / REAL / DOUBLE PRECISION: Stores floating-point numbers,
which are numbers with a decimal point and an exponent.
Character String Data Types
•CHAR(n): Fixed-length character string, where n specifies the number
of characters. Useful when you know all entries will be the same
length.
•VARCHAR(n) : Variable-length character string. n specifies the
maximum number of characters.
•TEXT or CLOB (Character Large Object): Stores large amounts of text
data.
Binary Data Types
•BINARY(n): Fixed-length binary data.
•VARBINARY(n): Variable-length binary data.
•BLOB (Binary Large Object): Stores large binary data such as images,
videos, or other multimedia files.
Date and Time Data Types
•DATE: Stores date values (year, month, day).
•TIME: Stores time of day values (hours, minutes, seconds).
•DATETIME / TIMESTAMP: Stores both date and time values.
•SMALLDATETIME: Stores date and time but with less precision.
•INTERVAL: Stores a span of time. Often used in more advanced
queries to calculate durations.
Boolean Data Type
•BOOLEAN or BIT: Stores TRUE or FALSE values. In some systems, this might be stored as 0
(false) and 1 (true).