SQL
Structured Query Language (SQL) is a language that provides an interface to relation database systems. SQL was developed by IBM in the 1970, for use in system R and is a defacto standard, as well as an ISO and ANSI standard.
- To deal with the above database objects, we need a programming language and that programming language is known as SQL.
Three subordinate languages of SQL are
Data Definition Language (DDL)
It ncludes the commands as
- CREATE To create tables in the database.
- ALTER To modify the existing table structure.
- DROP To drop the table with table structure.
- Data Manipulation Language (DML) It is used to insert, delete, update data and perform queries on these tables. Some of the DML commands are given below.
- INSERT To insert data into the table.
- SELECT To retrieve data from the table.
- UDPDATE To update existing data in the table.
- DELETE To delete data from the table.
Data Control Language (DCL)
It is used to control user’s access to the database objects. Some of the DCL commands are
DCL Commands are
- GRANT Used to grant select/insert/delete access.
- REVOKE Used to revoke the provided access.
Transaction Control Language (TCL)
It is used to manage changes affecting the data.
- COMMIT To save the work done, such as inserting or updating or, deleting data to/from the table.
- ROLLBACK To restore database to the original state, since last commit.
- SOL Data Types SQL data types specify the type, size and forma,
data/information that can be stored in columns and variables.
Key Points
- SQL is a special-purpose programming language designed for managing data held in a Relational
- Database Management Systems (RDBMS).
- SQL based upon relational algebra and tuple relational calculus, SQL consist, of a data definition language and a data manipulation language.
Various Data Types in SQL
- Data Time time-stamp
- Char Big int Integer
- Decimal Small int Double
There are so many other data types also.
Database Constraints
These are user defined that let us restrict the behaviours of column. We create constraints when we define a table with a SQL CREATE state
Inline Constraint
A constraint defined on the same line as its column.
Out of Line Constraint
A constraint defined on it’s own line in a CREATE statement. This constraint must reference the column that they constrain.
Key Points
- The most common operation in SQL is the query, which is performed with the declarative SELECT statement.
- SELECT retrieves data from one or more tables, or expressions.
- Standard SELECT statements have no persistent effects on the
- Queries allow the user to describe desired data, leaving the Database Management System (DBMS) responsible for planning, optimizing, and performing the physical operations necessary to produce that result as it chooses.
Default Constraint
It is used to insert a default value into a column, if no other value is specified at the time of insertion.
Syntax.
CREATE TABLE Empl oyee
{
Emp_i d i nt NOT NULL,
Last__Name varchar (250).
City varchar (50)DEFAULT `BANGALURU’
}
DDL Commands
CREATE TABLE < Tabl e _Name>
{
Col umn_name 1 < data_type >,
Col umn_name 2 < data_type >
}
- 2. ALTER TABLE < Table_Name >
AL TER Column < Col umn_Name> SET NOT NULL
- RENAME < object_type >object_name >to <new_name >
- DROP TABLE <Tabl e_Name>
DML Commands
SELECT Ai, A2, A3, , An what to return
FROM R1, R2, R3, , Rm, relations or table
WHERE condition filter condition i.e., on what basis, we want to restrict outcome/result.
If we want to write the above SQL script in the form of relational calculu we use the following syntax
{ПAI…An (σcondition (R1 x R2 x…x Rm))}
Comparison operators which we can use in filter condition are (=, > ,<, > =, < = , < >) ‘< >’means not equal to.
INSERT Statement
Used to add row (s) to the tables in a database.
INSERT INTO Employee (F_Name, L_Name)VALUES (‘Atal’,’Bihari’)
Key Points
- Data values can be added either in every column in a row or in same columns in a row by specifying the columns and their data.
- All the columns that are not listed in the column list in INSERT statement, will receive NULL.
……………………………………………………………… ………..
UPDATE Statement
It is used to modify/update or change existing data in single row, group’ rows or all the rows in a table.
Example UPDATE Employee An example of selective update
which will update some rows In
a table.
SET City = LUCKNOW ‘
WHERE Emp…Id BETWEEN
9 AND 15;
UPDATE Employee SET City = LUCKNOW’ ; Example of global update which will update city column for all the rows.
DELETE Statement
This used to delete rows from a table.
e.g.,
DELETE Employee WHERE Example of selective delete
Emp_Id=7;
This command will delete all the
DELETE Employee
rows from Employee table.
ORDER BY Clause
This clause is used to sort the result of a query in a specific order (ascending or descending)
By default sorting order is ascending.
SELECT Emp_Id , Emp_Name , City FROM Empl oyee
WHERE City ‘ LUC KNOW ‘
ORDER BY Emp_Id DESC ;
GROUP BY Clause
is used to divide the result set into groups. Grouping can be done by a column name or by the results of computed columns when using numeric data types.
- The HAVING clause can be used to set conditions for the GROUP BY
- HAVING clause is similar to the WHERE clause, but having puts conditions on groups.
- WHERE clause places conditions on rows.
- WHERE clause can’t include aggregate function, while HAVING conditions can do so.
e.g.,
SELECT Emp_Id, AVG (Salary)
FROM Empl oyee
GROUP BY Emp_Id
HAVING AVG (Salary) > 25000;
Aggregate Functions
| Function | Desciption |
| Sum() | It returns total sum of the values in a column.
|
| AVG() | It returns average of the values in a column.
|
| COUNT() | Provides number of non-null values in a column |
| MIN() and MAX() | Provides lowest and highest value respectively in a column |
| COUNT(*) | Counts total number of rows in a table.
|