• SQL (Structured
Query Language) is used to perform operations on the records stored in the
database such as updating records, deleting records, creating and modifying
tables, views, etc.
• SQL
is just a query language; it is not a database. To perform SQL queries, you
need to install any database, for example, Oracle, MySQL, MongoDB, PostGre SQL,
SQL Server, DB2, etc
.
What is SQL
·
SQL
stands for Structured Query Language.
·
It
is designed for managing data in a relational database management system
(RDBMS).
·
It
is pronounced as S-Q-L or sometime See-Qwell.
·
SQL
is a database language, it is used for database creation, deletion, fetching
rows, and modifying rows, etc.
·
SQL
is based on relational algebra and tuple relational calculus.
·
All
DBMS like MySQL, Oracle, MS Access, Sybase, Informix, Postgres, and SQL Server
use SQL as standard database language.
Why SQL
is required
·
SQL
is required:
·
To
create new databases, tables and views
·
To
insert records in a database
·
To
update records in a database
·
To
delete records from a database
·
To
retrieve data from a database
SQL Data
Types
• Data types are used to represent
the nature of the data that can be stored in the database table. For example,
in a particular column of a table, if we want to store a string type of data
then we will have to declare a string data type of this column.
• CHAR(Size):- It is used to specify a fixed
length string that can contain numbers, letters, and special characters. Its
size can be 0 to 255 characters. Default is 1.
• VARCHAR(Size):- It is used to specify a variable
length string that can contain numbers, letters, and characters. Its size can
be from 0 to 65535 characters.
CREATE
TABLE
• SQL CREATE TABLE statement is
used to create table in a database.
CREATE TABLE STUDENTS ( ID INT NOT NULL, NAME VARCHAR (20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR (25), PRIMARY KEY (ID) );
• DROP TABLE
• A SQL DROP TABLE statement is
used to delete a table definition and all data from a table.
• This is very important to know
that once a table is deleted all the information available in the table is lost
forever, so we have to be very careful when using this command.
• SQL>DROP TABLE STUDENTS;
• SQL> DESC STUDENTS;
• DELETE TABLE
• The DELETE statement is used to
delete rows from a table. If you want to remove a specific row from a table you
should use WHERE condition.
• DELETE FROM table_name [WHERE condition];
• But if you do not specify the
WHERE condition it will remove all the rows from the table.
• DELETE FROM table_name;
• Difference between DELETE and TRUNCATE statements
• There is a slight difference b/w
delete and truncate statement. The DELETE statement only
deletes the rows from the table based on the condition defined by WHERE clause
or delete all the rows from the table when condition is not specified.
• But it does not free the space
containing by the table.
• The TRUNCATE statement: it
is used to delete all the rows from the table and free the containing
space.
• RENAME TABLE
SQL RENAME TABLE syntax
is used to change the name of a table. Sometimes, we choose non-meaningful name
for the table. So it is required to be changed.
ALTER TABLE table_name RENAME TO new_table_name;
TRUNCATE
TABLE Vs DROP TABLE
• Drop table command can also be
used to delete complete table but it deletes table structure too. TRUNCATE
TABLE doesn't delete the structure of the table.
• ALTER TABLE
• The ALTER TABLE statement is used
to add, modify or delete columns in an existing table. It is also used to
rename a table.
• You can also use SQL ALTER TABLE
command to add and drop various constraints on an existing table.
• SQL ALTER TABLE Add Column
• If you want to add columns in SQL
table, the SQL alter table syntax is given below:
• ALTER TABLE table_name ADD column_name column-definition;
• SQL ALTER TABLE Modify Column
• If you want to modify an existing
column in SQL table, syntax is given below:
• ALTER TABLE table_name MODIFY column_name column_type;
• SQL ALTER TABLE DROP Column
• The syntax of alter table drop
column is given below:
• ALTER TABLE table_name DROP COLUMN column_name;
• SQL ALTER TABLE RENAME Column
The
syntax of alter table rename column is given below:
ALTER TABLE table_name RENAME COLUMN old_name to new_name;
SQL INSERT INTO
VALUE
Single record:-
INSERT INTO STUDENTS (ROLL_NO, NAME, AGE, CITY) VALUES (1, ABHIRAM, 22, ALLAHABAD);
Multiple Record:-
INSERT INTO student (ID, NAME) SELECT 1, 'ARMAAN' UNION ALL SELECT 2, 'BILLY' UNION ALL SELECT 3, 'CHARLIE';
Integrity
Constraints
SQL PRIMARY KEY
•A column or columns is called primary key (PK) that uniquely
identifies each row in the table.
•If you want to create a primary key,
you should define a PRIMARY KEY constraint when you create or modify a table.
When
multiple columns are used as a primary key, it is known as composite primary key
SQL primary key for one column:
The following SQL command creates a PRIMARY KEY on the "S_Id"
column when the "students" table is created.
CREATE TABLE students
( S_Id int NOT NULL, LastName varchar (255) NOT NULL, FirstName varchar (255),
Address varchar (255), City varchar (255), PRIMARY KEY (S_Id) ) ;
SQL
FOREIGN KEY
In
the relational databases, a foreign key is a field or a column that is used to
establish a link between two tables.
In
simple words you can say that, a foreign key in one table used to point primary
key in another table.
Let
us take an example to explain it:
Here
are two tables first one is students table and second is orders table.
Here
orders are given by students.
First table:
S_Id
|
LastName
|
FirstName
|
CITY
|
1
|
MAURYA
|
AJEET
|
ALLAHABAD
|
2
|
JAISWAL
|
RATAN
|
GHAZIABAD
|
3
|
ARORA
|
SAUMYA
|
MODINAGAR
|
Second table:
O_Id
|
OrderNo
|
S_Id
|
1
|
99586465
|
2
|
2
|
78466588
|
2
|
3
|
22354846
|
3
|
4
|
57698656
|
1
|
- The "S_Id" column in
the "Students" table is the PRIMARY KEY in the
"Students" table.
- The "S_Id" column in
the "Orders" table is a FOREIGN KEY in the "Orders"
table.
The
foreign key constraint is generally prevents action that destroy links between
tables. It also prevents invalid data to enter in foreign key column.
Difference
between primary key and foreign key in SQL:
These are some important difference between
primary key and foreign key in SQL-
Primary key cannot be null on the other hand
foreign key can be null.
Primary key is always unique while foreign key
can be duplicated.
Primary key uniquely identify a record in a
table while foreign key is a field in a table that is primary key in another
table.
There is only one primary key in the table on
the other hand we can have more than one foreign key in the table.
Unique Key
in SQL
A unique key is a set of one or more than one
fields/columns of a table that uniquely identify a record in a database table.
You can say that it is little like primary key
but it can accept only one null value and it cannot have duplicate values.
The unique key and primary key both provide a
guarantee for uniqueness for a column or a set of columns.
There is an automatically
defined unique key constraint within a primary key constraint.
There may be many unique key constraints for one
table, but only one PRIMARY KEY constraint for one table.
SQL UNIQUE KEY constraint on CREATE TABLE:
If you want to create a UNIQUE constraint on the
"S_Id" column when the "students" table is created, use the
following SQL syntax:
CREATE TABLE students ( S_Id int NOT NULL UNIQUE, LastName varchar (255) NOT NULL, FirstName varchar (255), City varchar (255) ) ;
SQL - NOT NULL Constraint
By
default, a column can hold NULL values. If you do not want a column to have a
NULL value, then you need to define such a constraint on this column specifying
that NULL is now not allowed for that column.
A NULL is not the same as no data,
rather, it represents unknown data.
Example
For example, the following SQL
query creates a new table called CUSTOMERS and adds five columns, three of
which, are ID NAME and AGE, In this we specify not to accept NULLs –
CREATE TABLE students
( S_Id int NOT NULL, LastName varchar (255) NOT NULL, FirstName varchar (255), Address varchar (255), City varchar (255), PRIMARY KEY (S_Id) ) ;
SQL
CHECK Constraint
The CHECK constraint is used to limit the value
range that can be placed in a column.
If you define a CHECK constraint on a single
column it allows only certain values for this column.
If you define a CHECK constraint on a table it
can limit the values in certain columns based on values in other columns in the
row.
The following SQL creates
a CHECK constraint on the "Age" column when the "Persons"
table is created. The CHECK constraint ensures that you can not have any person
below 18 years:
CREATE TABLE Persons ( ID int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Age int CHECK (Age>=18));
SQL
DEFAULT Constraint
The DEFAULT constraint is used to provide a
default value for a column.
The default value will be added to all new
records IF no other value is specified.
CREATE TABLE Persons (ID int NOT NULL,LastName varchar(255) NOT NULL,
FirstName varchar(255), Age int,City varchar(255) DEFAULT 'Sandnes');
SQL DROP TABLEFirstName varchar(255), Age int,City varchar(255) DEFAULT 'Sandnes');
A SQL DROP TABLE statement is used to delete a table
definition and all data from a table.
This is very important to know that once a table is
deleted all the information available in the table is lost forever, so we have
to be very careful when using this command.
Let's see the syntax to drop the table from the
database.
DROP TABLE "table_name";
SQL>DROP TABLE STUDENTS;
Now, use the following command to check whether
table exists or not.
SQL> DESC STUDENTS;
SQL CREATE VIEW Statement
In SQL, a view is a virtual table based on the
result-set of an SQL statement.
A view contains rows and columns, just like a real
table. The fields in a view are fields from one or more real tables in the
database.
You can add SQL functions, WHERE, and JOIN
statements to a view and present the data as if the data were coming from one
single table.
CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;
When should indexes be created –
A column contains a wide range of values
A column does not contain a large number of null
values
One or more columns are frequently used together in
a where clause or a join condition
When should indexes be avoided –
The table is small
The columns are not often used as a condition in the
query
The column is updated frequently
Removing an Index –
To remove an
index from the data dictionary by using the DROP INDEX command.
DROP INDEX index;
•SQL UPDATE
•The SQL commands (UPDATE and DELETE) are used to modify
the data that is already in the database. The SQL DELETE command uses a WHERE
clause.
•SQL UPDATE statement is used to change the data of
the records held by tables. Which rows is to be update, it is decided by a
condition. To specify condition, we use WHERE clause.
•UPDATE table_name
SET column_name = expression
WHERE conditions
•UPDATE students
SET User_Name = ‘abc‘ WHERE
Student_Id = '3' ;
•UPDATE students
SET student_id = 001 WHERE student_name = 'AJEET';
•DCL (Data Control Language) :
•A Data Control Language is a syntax similar to a
computer programming language used to control access to data stored in a
database (Authorization)
•It is used to control privileges in Database. To
perform any operation in the database, such as for creating tables, sequences
or views, a user needs privileges. Privileges are of two types,
•System: This includes permissions for creating
session, table, etc and all types of other system privileges.
•Object: This includes permissions for any command
or query to perform any operation on the database tables.
•GRANT: allow specified users to perform specified
tasks. And Used to provide any user
access privileges or other privileges for the database.
• REVOKE: cancel previously granted or denied
permissions and Used to take back permissions from any user
•TCL (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 permanently save
any transaction into the database.
•ROLLBACK: This command restores the database to
last committed state. It is also used with savepoint command to jump to a
savepoint in a transaction.
SAVEPOINT: Savepoint command is used to temporarily
save a transaction so that you can rollback to that point whenever necessary
•SQL SELECT
•The most commonly used SQL command is SELECT
statement. It is used to query the database and retrieve selected data that
follow the conditions we want.
•In simple words, we can say that the select
statement used to query or retrieve data from a table in the database.
•SELECT expressions
FROM tables WHERE conditions;
•Optional clauses in SELECT statement
•There are some optional clauses in SELECT
statement:
•[WHERE Clause] : It specifies which rows to
retrieve.
•[GROUP BY Clause] : Groups rows that share a
property so that the aggregate function can be applied to each group.
•[HAVING Clause] : It selects among the groups
defined by the GROUP BY clause.
•[ORDER BY Clause] : It specifies an order in which
to return the rows
•SELECT first_name FROM student_details;
•SELECT first_name, last_name FROM
student_details;
•We can also use clauses like WHERE, GROUP BY,
HAVING, ORDER BY with SELECT statement.
•SQL ORDER BY Clause
•The SQL ORDER BY clause is used for sorting data in
ascending and descending order based on one or more columns.
•Some databases sort query results in ascending
order by default.
•SELECT expressions
FROM tables WHERE conditions ORDER BY expression [ASC | DESC];
•SELECT * FROM CUSTOMERS ORDER BY NAME, SALARY;
•SQL WHERE LIKE Statement
•WHERE LIKE determines if a character string matches
a pattern.
•Use WHERE LIKE when only a fragment of a text value
is known.
•WHERE LIKE supports two wildcard match options: %
and _.
•List all products with names that start with 'Ca‘
•SELECT Id, ProductName, UnitPrice, Package FROM
Product WHERE ProductName LIKE 'Ca%'
•List all products that start with 'Cha' or 'Chan'
and have one more character.
SELECT Id, ProductName, UnitPrice, Package FROM
Product WHERE ProductName LIKE 'Cha_' OR ProductName LIKE 'Chan_'
UNIONS
CLAUSE
•The
SQL UNION clause/operator is used to combine the results of two or more SELECT
statements without returning any duplicate rows.
•To
use this UNION clause, each SELECT statement must have
•The
same number of columns selected
•The
same number of column expressions
•The
same data type and
•Have
them in the same order
•But
they need not have to be in the same length.
•Syntax
•SELECT
column1 [, column2 ] FROM table1 [, table2 ] [WHERE condition] UNION SELECT
column1 [, column2 ] FROM table1 [, table2 ] [WHERE condition]
Example:-
•Employees:-id
,first_name , last_name , salary
•Customer:-
id ,first_name , last_name , city
•SELECT
first_name, last_name FROM employees
UNION SELECT first_name,
last_name FROM customers ;
•The
SQL INTERSECT clause/operator is used to combine two SELECT statements, but
returns rows only from the first SELECT statement that are identical to a row
in the second SELECT statement. This means INTERSECT returns only common rows
returned by the two SELECT statements.
•Just
as with the UNION operator, the same rules apply when using the INTERSECT
operator. MySQL does not support the INTERSECT operator.
•Syntax
SELECT
column1 , column2 FROM table1 ,
table2 [WHERE condition] INTERSECT
SELECT
column1 , column2 FROM table1 ,
table2 [WHERE condition]
SELECT * FROM
employees Intersect SELECT * FROM customers
•The
SQL EXCEPT clause/operator is used to combine two SELECT statements and returns
rows from the first SELECT statement that are not returned by the second SELECT
statement. This means EXCEPT returns only rows, which are not available in the
second SELECT statement.
SELECT
column1 , column2 FROM table1
,table2 [WHERE condition]
EXCEPT
SELECT column1 , column2 FROM table1 , table2 ] [WHERE condition]
SELECT * FROM
employees Except SELECT * FROM customers ;
•Aggregate Functions
These functions return a single value after calculating from a group of values. Following are some frequently used Aggregate functions.
• AVG()
•Average returns average value after calculating from values in a numeric column.
•Its general Syntax is,
•SELECT AVG(column_name) from table_name
•SELECT avg(salary) from Emp;
•COUNT()
•Count returns the number of rows present in the table either based on some condition or without condition.
•Its general Syntax is,
•SELECT COUNT(column_name) from table-name
•SELECT COUNT(name) from Emp where salary = 8000;
•Example of COUNT(distinct)
•SELECT COUNT(distinct salary) from emp;
•FIRST()
•First function returns first value of a selected column
•Syntax for FIRST function is,
•SELECT FIRST(column_name) from table-name
•SELECT FIRST(salary) from Emp;
•LAST()
•LAST return the return last value from selected column
•Syntax of LAST function is,
•SELECT LAST(column_name) from table-name
•SELECT LAST(salary) from emp;
• MAX()
•MAX function returns maximum value from selected column of the table.
•Syntax of MAX function is,
•SELECT MAX(column_name) from table-name
•SELECT MAX(salary) from emp;
• MIN()
•MIN function returns minimum value from a selected column of the table.
•Syntax for MIN function is,
oSELECT MIN(column_name) from table-name
oSELECT MIN(salary) from emp;
• SUM()
•SUM function returns total sum of a selected columns numeric values.
•Syntax for SUM is,
•SELECT SUM(column_name) from table-name
•SELECT SUM(salary) from emp;
•DCL Command
•DCL defines two commands,
•Grant : Gives user access privileges to database.
•Revoke : Take back permissions from user.
•To Grant all privilege to a User
•grant sysdba to username To Grant permission to Create any Table
•grant create any table to username To Grant permission to Drop any Table
•grant drop any table to username To take back Permissions
•revoke create table from username
These functions return a single value after calculating from a group of values. Following are some frequently used Aggregate functions.
• AVG()
•Average returns average value after calculating from values in a numeric column.
•Its general Syntax is,
•SELECT AVG(column_name) from table_name
•SELECT avg(salary) from Emp;
•COUNT()
•Count returns the number of rows present in the table either based on some condition or without condition.
•Its general Syntax is,
•SELECT COUNT(column_name) from table-name
•SELECT COUNT(name) from Emp where salary = 8000;
•Example of COUNT(distinct)
•SELECT COUNT(distinct salary) from emp;
•FIRST()
•First function returns first value of a selected column
•Syntax for FIRST function is,
•SELECT FIRST(column_name) from table-name
•SELECT FIRST(salary) from Emp;
•LAST()
•LAST return the return last value from selected column
•Syntax of LAST function is,
•SELECT LAST(column_name) from table-name
•SELECT LAST(salary) from emp;
• MAX()
•MAX function returns maximum value from selected column of the table.
•Syntax of MAX function is,
•SELECT MAX(column_name) from table-name
•SELECT MAX(salary) from emp;
• MIN()
•MIN function returns minimum value from a selected column of the table.
•Syntax for MIN function is,
oSELECT MIN(column_name) from table-name
oSELECT MIN(salary) from emp;
• SUM()
•SUM function returns total sum of a selected columns numeric values.
•Syntax for SUM is,
•SELECT SUM(column_name) from table-name
•SELECT SUM(salary) from emp;
•DCL Command
•DCL defines two commands,
•Grant : Gives user access privileges to database.
•Revoke : Take back permissions from user.
•To Grant all privilege to a User
•grant sysdba to username To Grant permission to Create any Table
•grant create any table to username To Grant permission to Drop any Table
•grant drop any table to username To take back Permissions
•revoke create table from username
