SQL (Structured
Query Language)
SQL
stands for "Structured Query Language" often pronounced as “sequel”. SQL is a
database computer language designed for the storing, retrieval and management
of data in relational database. SQL is an ANSI (American National Standards
Institute) standard.
Unlike
most programming languages, SQL is unique in that it is not procedural but
declarative in nature. This means that when using this language one states what
data is desired and not how to get that data.
SQL
is the standard language for Relation Database System. All relational database
management systems like MySQL, MS Access, Oracle, Sybase, Informix, postgres
and SQL Server uses SQL as standard database language.
What Can SQL do?
• SQL
can execute queries against a database
• SQL
can retrieve data from a database
• SQL
can insert, update and delete records from a database
• SQL
can create new databases
• SQL
can create new tables in a database
• SQL
can create stored procedures in a database
• SQL
can create views in a database
• SQL
can set permissions on tables, procedures, and views
Advantage of SQL
•
High Speed: SQL Queries can be used to retrieve large
amounts of records from a database quickly and efficiently.
•
Well Defined Standards Exist: SQL
databases use long-established standard, which is being adopted by ANSI &
ISO. Non-SQL databases do not adhere to any clear standard.
•
No Coding Required: Using standard SQL
it is easier to manage database systems without having to write substantial
amount of code.
•
Emergence of ORDBMS: Previously SQL
databases were synonymous with relational database. With the emergence of
Object Oriented DBMS, object storage capabilities are extended to relational
databases.
We all of know that There were are Advantage there are some Disadvantage
Disadvantage(s): very
specialized, very geeky
SQL Data Type
SQL
data type is an attribute that specifies type of data of any object. Each
column, variable and expression has related data type in SQL.
You
would use these data types while creating your tables. You would choose a
particular data type for a table column based on your requirement.
SQL
Server offers six categories of data types for your use:
SQL
Commands:
SQL
commands are instructions used to communicate with the database to perform
specific task that work with data. SQL commands can be used not only for
searching the database but also to perform various other functions like, for
example, you can create tables, add data to tables, or modify data, drop the
table, set permissions for users. SQL commands are grouped into four major
categories depending on their functionality:
•
Data
Definition Language (DDL) - These SQL commands are used for
creating, modifying, and dropping the structure of database objects. The
commands are CREATE, ALTER, DROP, RENAME, and TRUNCATE.
•
Data
Manipulation Language (DML) - These SQL commands are used for
storing, retrieving, modifying, and deleting data. These commands are SELECT,
INSERT, UPDATE, and DELETE.
•
Transaction
Control Language (TCL) - These SQL commands are used for
managing changes affecting the data. These commands are COMMIT, ROLLBACK, and
SAVEPOINT.
•
Data
Control Language (DCL) - These SQL commands are used for
providing security to database objects. These commands are GRANT and REVOKE.
SQL
SELECT Statement
The most
commonly used SQL command is SELECT statement. The SQL SELECT statement is used
to query or retrieve data from a table in the database. A query may retrieve
information from specified columns or from all of the columns in the table. To
create a simple SQL SELECT Statement, you must specify the column(s) name and
the table name. The whole query is called SQL SELECT Statement.
Syntax
of SQL SELECT Statement:
SELECT
column_list FROM table-name
[WHERE
Clause] [GROUP BY clause] [HAVING clause] [ORDER BY clause];
• table-name is the name of the table
from which the information is retrieved.
• column_list includes one or more
columns from which data is retrieved.
• The code within the brackets is
optional.
database
table student_details;
id first_name last_name age subject games
100 Shyam Prakash 10 Science Cricket
101 Mona Kumari 12 Maths Football
102 Stephen Fleming 09 Science Cricket
103 Shekar Gowda 18 Maths Badminton
104 Priya Chandra 15 Economics Chess
NOTE: The
database tables are used here for better explanation of SQL commands. In
reality, the tables can have different columns and different data.
For example,
consider the table student_details. To select the first name of all the
students the query would be like:
SELECT
first_name FROM student_details;
NOTE: The
commands are not case sensitive. The above SELECT statement can also be written
as "select first_name from students_details;"
We can also
retrieve data from more than one column.
For example, to
select first name and last name of all the students.
SELECT
first_name, last_name FROM student_details;
For show all
data of table
SELECT
* FROM student_details;
Aliases
SQL Aliases are
defined for columns and tables. Basically aliases is created to make the column
selected more readable.
For Example: To
select the first name of all the students, the query would be like:
Aliases for
columns:
SELECT
first_name AS Name FROM student_details;
or
SELECT
first_name Name FROM student_details;
In the above
query, the column first_name is given a alias as 'name'. So when the result is
displayed the column name appears as 'Name' instead of 'first_name'.
Output:
Name
-------------
Shyam
Mona
Stephen
SQL
Where clause
The
WHERE Clause is used when you want to retrieve specific information from a table
excluding other irrelevant data. For example, when you want to see the
information about students in class 10th only then you do not need the
information about the students in other class. Retrieving information about all
the students would increase the processing time for the query.
So
SQL offers a feature called WHERE clause, which we can use to restrict the data
that is retrieved. The condition you provide in the WHERE clause filters the
rows retrieved from the table and gives you only those rows which you expected
to see. WHERE clause can be used along
with SELECT, DELETE, UPDATE statements
Syntax
of SQL WHERE Clause:
WHERE {column or
expression} comparison-operator value
Syntax for a
WHERE clause with Select statement is:
SELECT
column_list FROM table-name WHERE condition;
• column or expression - Is the column
of a table or a expression
• comparison-operator - operators like
= < > etc.
• value - Any user value or a column
name for comparison
For Example: To
find the name of a student with id 100, the query would be like:
SELECT
first_name, last_name FROM student_details WHERE id = 100;
SELECT
first_name, last_name FROM student_details WHERE age >= 15;
SQL
Operators
There
are two type of Operators, namely Comparison Operators and Logical Operators.
These operators are used mainly in the WHERE clause, HAVING clause to filter
the data to be selected.
Comparison
Operators:
Comparison
operators are used to compare the column data with specific values in a
condition. Comparison Operators are also used along with the SELECT statement
to filter data based on specific conditions.
The below table
describes each comparison operator.
Comparison
Operators Description
= equal to
<>, != is not equal
to
< less than
> greater than
>= greater
than or equal to
<= less
than or equal to
Logical
Operators:
There are three
Logical Operators namely AND, OR and NOT
SQL Logical
Operators
There are three
Logical Operators namely, AND, OR, and NOT. These operators compare two
conditions at a time to determine whether a row can be selected for the output.
When retrieving data using a SELECT statement, you can use logical operators in
the WHERE clause, which allows you to combine more than one condition.
Logical Operators Description
OR For
the row to be selected at least one of the conditions must be true.
AND For
a row to be selected all the specified conditions must be true.
NOT For
a row to be selected the specified condition must be false.
"OR"
Logical Operator:
If you want to
select rows that satisfy at least one of the given conditions, you can use the
logical operator, OR.
For example: if
you want to find the names of students who are studying either Maths or
Science, the query would be like,
SELECT
first_name, last_name, subject FROM
student_details
WHERE
subject = 'Maths' OR subject = 'Science'
The
output would be something like,
first_name last_name subject
Mona Kumari Maths
Shekar Gowda Maths
Shyam Prakash Science
Stephen Fleming Science
"AND"
Logical Operator:
If you want to
select rows that must satisfy all the given conditions, you can use the logical
operator, AND.
For Example: To
find the names of the students between the age 10 to 15 years, the query would
be like:
SELECT
first_name, last_name, age FROM student_details
WHERE
age >= 10 AND age <= 15;
The output would
be something like,
first_name last_name age
Shyam Prakash 10
Mona Kumari 12
Shekar Gowda 15
"NOT"
Logical Operator:
If
you want to find rows that do not satisfy a condition, you can use the logical
operator, NOT. NOT results in the reverse of a condition. That is, if a
condition is satisfied, then the row is not returned.
For
example: If you want to find out the names of the students who do not play
football, the query would be like:
SELECT
first_name, last_name, games FROM student_details
WHERE
NOT games = 'Football'
The output would
be something like,
first_name last_name games
Shyam Prakash Cricket
Stephen Fleming Cricket
Shekar Gowda Badminton
Priya Chandra Chess
SQL
Comparison Keywords
There are other
comparison keywords available in sql which are used to enhance the search
capabilities of a sql query. They are
"IN", "BETWEEN...AND", "IS NULL",
"LIKE".
Comparison Operators Description
LIKE column value
is similar to specified character(s).
IN column
value is equal to any one of a specified set of values.
BETWEEN...AND column
value is between two values, including the end values specified in the range.
IS NULL column
value does not exist.
Comparsion
SQL
LIKE Operator
The
LIKE operator is used to list all rows in a table whose column values match a
specified pattern. It is useful when you want to search rows to match a
specific pattern, or when you do not know the entire value. For this purpose we
use a wildcard character '%'.
For
example: To select all the students whose name begins with 'S'
SELECT
first_name, last_name FROM student_details
WHERE
first_name LIKE ‘S%’;
The output would
be similar to:
first_name last_name
Stephen Fleming
Shekar Gowda
The above select
statement searches for all the rows where the first letter of the column
first_name is 'S' and rest of the letters in the name can be any character.
SQL
BETWEEN ... AND Operator
The operator
BETWEEN and AND, are used to compare data for a range of values.
For Example: to
find the names of the students between age 10 to 15 years, the query would be
like,
SELECT
first_name, last_name, age FROM student_details
WHERE
age BETWEEN 10 AND 15;
The output would
be similar to:
first_name last_name age
Shyam Prakash 10
Mona Kumari 12
Shekar Gowda 15
SQL
IN Operator:
The
IN operator is used when you want to compare a column with more than one value.
It is similar to an OR condition.
For
example: If you want to find the names of students who are studying either
Maths or Science, the query would be like,
SELECT
first_name, last_name, subject FROM student_details
WHERE
subject IN ('Maths', 'Science');
The output would
be similar to:
first_name last_name subject
Mona Kumari Maths
Shekar Gowda Maths
Shyam Prakash Science
Stephen Fleming Science
You can include
more subjects in the list like ('maths','science','history')
SQL
IS NULL Operator
A column value
is NULL if it does not exist. The IS NULL operator is used to display all the
rows for columns that do not have a value.
For Example: If
you want to find the names of students who do not participate in any games, the
query would be as given below
SELECT
first_name, last_name FROM student_details WHERE games IS NULL
There
would be no output as we have every student participate in a game in the table
student_details, else the names of the students who do not participate in any
games would be displayed.
SQL ORDER BY
The
ORDER BY clause is used in a SELECT statement to sort results either in
ascending or descending order. Oracle sorts query results in ascending order by
default.
Syntax for using
SQL ORDER BY clause to sort data is:
SELECT
column-list FROM table_name [WHERE
condition]
[ORDER
BY column1 [, column2, .. columnN] [DESC]];
database table
"employee";
id name dept age salary location
100 Ramesh Electrical 24 25000 Bangalore
101 Hrithik Electronics 28 35000 Bangalore
102 Harsha Aeronautics 28 35000 Mysore
103 Soumya Electronics 22 20000 Bangalore
104 Priya InfoTech 25 30000 Mangalore
For Example: If
you want to sort the employee table by salary of the employee, the sql query
would be.
SELECT
name, salary FROM employee ORDER BY salary;
The output would
be like
name salary
Soumya 20000
Ramesh 25000
Priya 30000
Hrithik 35000
Harsha 35000
The query first
sorts the result according to name and then displays it.
For descending
order
·
SELECT
* FROM employee ORDER BY salary desc
SQL
GROUP Functions
Group functions
are built-in SQL functions that operate on groups of rows and return one value
for the entire group. These functions
are: COUNT, MAX, MIN, AVG, SUM, DISTINCT
SQL
COUNT ():
This function returns the number of rows in the table that satisfies the
condition specified in the WHERE condition. If the WHERE condition is not
specified, then the query returns the total number of rows in the table.
For Example: If
you want the number of employees in a particular department, the query would
be:
SELECT
COUNT (*) FROM employee WHERE dept = 'Electronics';
The output would
be '2' rows.
If you want the
total number of employees in all the department, the query would take the form:
SELECT
COUNT (*) FROM employee;
The output would
be '5' rows.
SQL DISTINCT(): This
function is used to select the distinct rows.
For
Example: If you want to select all distinct department names from employee
table, the query would be:
SELECT DISTINCT dept FROM employee;
To
get the count of employees with unique name, the query would be:
SELECT COUNT (DISTINCT name) FROM
employee;
SQL MAX(): This function
is used to get the maximum value from a column.
To
get the maximum salary drawn by an employee, the query would be:
SELECT MAX (salary) FROM employee;
SQL MIN(): This function
is used to get the minimum value from a column.
To
get the minimum salary drawn by an employee, he query would be:
SELECT MIN (salary) FROM employee;
SQL AVG(): This function
is used to get the average value of a numeric column.
To
get the average salary, the query would be
SELECT AVG (salary) FROM employee;
SQL SUM(): This function
is used to get the sum of a numeric column
To
get the total salary given out to the employees,
SELECT SUM (salary) FROM employee;
SQL
GROUP BY Clause
The
SQL GROUP BY Clause is used along with the group functions to retrieve data
grouped according to one or more columns.
For
Example: If you want to know the total amount of salary spent on each
department, the query would be:
SELECT
dept, SUM (salary) FROM employee GROUP BY dept;
The output would
be like:
dept salary
Electrical 25000
Electronics 55000
Aeronautics 35000
InfoTech 30000
NOTE: The group
by clause should contain all the columns in the select list expect those used
along with the group functions.
SQL
HAVING Clause
Having
clause is used to filter data based on the group functions. This is similar to
WHERE condition but is used with group functions. Group functions cannot be
used in WHERE Clause but can be used in HAVING clause.
SQL
HAVING Clause Example
If you want to
select the department that has total salary paid for its employees more than
25000, the sql query would be like;
SELECT
dept, SUM (salary)FROM employee GROUP BY dept HAVING
SUM
(salary) > 25000
The output would
be like:
dept salary
Electronics 55000
Aeronautics 35000
InfoTech 30000
When WHERE,
GROUP BY and HAVING clauses are used together in a SELECT statement, the WHERE
clause is processed first, then the rows that are returned after the WHERE
clause is executed are grouped based on the GROUP BY clause.
Finally, any
conditions on the group functions in the HAVING clauses are applied to the
grouped rows before the final output is displayed.
SQL
INSERT Statement
The INSERT
Statement is used to add new rows of data to a table.
We can insert
data to a table in two ways,
1) Inserting the
data directly to a table.
Syntax
for SQL INSERT is:
INSERT
INTO TABLE_NAME [ (col1, col2, col3,...colN)] VALUES (value1, value2,
value3,...valueN);
•
col1, col2,...colN -- the names of the
columns in the table into which you want to insert data.
While
inserting a row, if you are adding value for all the columns of the table you
need not specify the column(s) name in the sql query. But you need to make sure
the order of the values is in the same order as the columns in the table. The
sql insert query will be as follows
INSERT
INTO TABLE_NAME VALUES (value1, value2, value3,...valueN);
For
Example: If
you want to insert a row to the employee table, the query would be like,
INSERT
INTO employee (id, name, dept, age, salary location) VALUES (105, 'Srinath',
'Aeronautics', 27, 33000);
IMPORTANT
NOTE:
•
When adding a new row, you should ensure
the datatype of the value and the column matches
•
You follow the integrity constraints, if
any, defined for the table.
SQL
UPDATE Statement
The
UPDATE Statement is used to modify the existing rows in a table.
The
Syntax for SQL UPDATE Command is:
UPDATE table_name SET column_name1 =
value1, column_name2 = value2, ...
[WHERE condition]
•
table_name - the table name which has to
be updated.
•
column_name1, column_name2.. - the
columns that gets changed.
•
value1, value2... - are the new values.
NOTE:
In the Update statement, WHERE clause identifies the rows that get affected. If
you do not include the WHERE clause, column values for all the rows get
affected.
For
Example: To update the location of an employee, the sql update query would be
like,
UPDATE
employee SET location ='Mysore' WHERE id = 101;
To change the
salaries of all the employees, the query would be,
UPDATE
employee SET salary = salary + (salary * 0.2);
SQL
Delete Statement
The DELETE
Statement is used to delete rows from a table.
The
Syntax of a SQL DELETE statement is:
DELETE
FROM table_name [WHERE condition];
• table_name -- the table name which
has to be updated.
NOTE:
The WHERE clause in the sql delete command is optional and it identifies the
rows in the column that gets deleted. If you do not include the WHERE clause
all the rows in the table is deleted, so be careful while writing a DELETE
query without WHERE clause.
For Example: To
delete an employee with id 100 from the employee table, the sql delete query
would be like,
DELETE
FROM employee WHERE id = 100;
To delete all
the rows from the employee table, the query would be like,
DELETE
FROM employee;
SQL TRUNCATE
Statement
The SQL TRUNCATE
command is used to delete all the rows from the table and free the space
containing the table.
Syntax to
TRUNCATE a table:
TRUNCATE
TABLE table_name;
For Example: To
delete all the rows from employee table, the query would be like,
TRUNCATE TABLE
employee;
Difference
between DELETE and TRUNCATE Statements:
DELETE
Statement:
This command deletes only the rows from the table based on the condition given
in the where clause or deletes all the rows from the table if no condition is
specified. But it does not free the space containing the table.
TRUNCATE
statement:
This command is used to delete all the rows from the table and free the space
containing the table.
SQL
DROP Statement:
The SQL DROP
command is used to remove an object 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 command. When a table is dropped all the references to the table
will not be valid.
Syntax to drop a
sql table structure:
DROP
TABLE table_name;
For Example: To
drop the table employee, the query would be like
DROP
TABLE employee;
Difference
between DROP and TRUNCATE Statement:
If a
table is dropped, all the relationships with other tables will no longer be
valid, the integrity constraints will be dropped, grant or access privileges on
the table will also be dropped, if want use the table again it has to be
recreated with the integrity constraints, access privileges and the
relationships with other tables should be established again. But, if a table is
truncated, the table structure remains the same, therefore any of the above
problems will not exist.
SQL
CREATE TABLE Statement
The CREATE TABLE
Statement is used to create tables to store data. Integrity Constraints like
primary key, unique key, foreign key can be defined for the columns while
creating the table. The integrity constraints can be defined at column level or
table level. The implementation and the syntax of the CREATE Statements differs
for different RDBMS.
The Syntax for
the CREATE TABLE Statement is:
CREATE
TABLE table_name (column_name1 datatype, column_name2 datatype, ...
column_nameN datatype );
•
table_name - is the name of the table.
•
column_name1, column_name2.... - is the
name of the columns
•
datatype - is the datatype for the
column like char, date, number etc.
For Example: If
you want to create the employee table, the statement would be like,
CREATE
TABLE employee ( id number(5), name char(20), dept char(10),
age
number(2), salary number(10), location char(10) );
To
See the Structure of Table
•
sp_help tablename
or
•
EXEC sp_help tablename
SQL
ALTER TABLE Statement
The SQL ALTER
TABLE command is used to modify the definition (structure) of a table by
modifying the definition of its columns. The ALTER command is used to perform
the following functions.
•
Add, drop, modify table columns
•
Add and drop constraints
•
Enable and Disable constraints
Syntax
to add a column
ALTER TABLE
table_name ADD column_name datatype;
For Example: To
add a column "experience" to the employee table, the query would be
like
·
ALTER
TABLE emp ADD dept varchar(20);
Syntax
to drop a column
ALTER
TABLE table_name DROP COLUMN column_name;
For Example: To
drop the column "location" from the employee table, the query would
be like
·
ALTER
TABLE emp DROP COLUMN dept
Syntax
To Modify A Column
ALTER
TABLE table_name ALTER COLUMN column_name datatype;
For Example: To
modify the column salary in the employee table, the query would be like.
·
ALTER
TABLE emp ALTER COLUMN dept varchar(20);
SQL
RENAME Command
The SQL RENAME
command is used to change the name of the table or a database object.
If you change
the object's name any reference to the old name will be affected. You have to
manually change the old name to the new name in every reference.
Syntax to rename
a table
RENAME
old_table_name To new_table_name;
For Example: To
change the name of the table myemp to emp, the query would be like
sp_RENAME
myemp, emp;
SQL
Integrity Constraints
Integrity
Constraints are used to apply business rules for the database tables.
The constraints
available in SQL are Foreign Key, Not Null, Unique, Check.
Constraints
can be defined in two ways
•
The constraints can be specified
immediately after the column definition. This is called column-level
definition.
•
The constraints can be specified after
all the columns are defined. This is called table-level definition.
1)
SQL Primary key:
This constraint
defines a column or combination of columns which uniquely identifies each row
in the table.
Syntax to define
a Primary key at column level:
column
name datatype [CONSTRAINT constraint_name] PRIMARY KEY
Syntax to define
a Primary key at table level:
[CONSTRAINT
constraint_name] PRIMARY KEY (column_name1,column_name2,..)
•
column_name1, column_name2 are the names
of the columns which define the primary Key.
•
The syntax within the bracket i.e.
[CONSTRAINT constraint_name] is optional.
For Example: To
create an employee table with Primary Key constraint, the query would be like.
Primary Key at
column level:
CREATE TABLE
employee ( id number(5) PRIMARY KEY, name char(20),
dept char(10),
age number(2), salary number(10), location char(10) );
or
CREATE
TABLE employee ( id number(5) CONSTRAINT emp_id_pk PRIMARY KEY, name
char(20),dept char(10),age number(2),salary number(10),location char(10));
SQL Foreign key or Referential Integrity :
This
constraint identifies any column referencing the PRIMARY KEY in another table.
It establishes a relationship between two columns in the same table or between
different tables. For a column to be defined as a Foreign Key, it should be a
defined as a Primary Key in the table which it is referring. One or more
columns can be defined as Foreign key.
Syntax to define
a Foreign key at column level:
[CONSTRAINT
constraint_name] REFERENCES Referenced_Table_name(column_name)
Syntax to define
a Foreign key at table level:
[CONSTRAINT
constraint_name] FOREIGN KEY(column_name) REFERENCES referenced_table_name(column_name);
For Example:
1)
Lets use the "product" table and "order_items".
Foreign Key at
column level:
CREATE
TABLE product ( product_id number(5) CONSTRAINT pd_id_pk PRIMARY KEY,
product_name char(20),supplier_name char(20),unit_price number(10));
CREATE
TABLE order_items ( order_id number(5) CONSTRAINT od_id_pk PRIMARY KEY, product_id
number(5) CONSTRAINT pd_id_fk REFERENCES, product(product_id),product_name
char(20),supplier_name char(20),unit_price number(10));
SQL
Not Null Constraint :
This constraint
ensures all rows in the table contain a definite value for the column which is
specified as not null. Which means a null value is not allowed.
Syntax to define
a Not Null constraint:
[CONSTRAINT
constraint name] NOT NULL
For Example: To
create a employee table with Null value, the query would be like
CREATE
TABLE employee ( id number(5),name char(20) CONSTRAINT nm_nn NOT NULL, dept
char(10), age number(2), salary number(10),location char(10) );
4)
SQL Unique Key:
This constraint
ensures that a column or a group of columns in each row have a distinct value.
A column(s) can have a null value but the values cannot be duplicated.
Syntax to define
a Unique key at column level:
[CONSTRAINT
constraint_name] UNIQUE
Syntax to define
a Unique key at table level:
[CONSTRAINT
constraint_name] UNIQUE(column_name)
For Example: To
create an employee table with Unique key, the query would be like,
Unique Key at
column level:
CREATE
TABLE employee ( id number(5) PRIMARY KEY, name char(20),
dept
char(10), age number(2), salary number(10), location char(10) UNIQUE );
or
CREATE
TABLE employee (id number(5) PRIMARY KEY, name char(20),dept char(10), age
number(2),salary number(10),location char(10) CONSTRAINT loc_un UNIQUE );
SQL
Check Constraint:
This constraint
defines a business rule on a column. All the rows must satisfy this rule. The
constraint can be applied for a single column or a group of columns.
Syntax to define
a Check constraint:
[CONSTRAINT
constraint_name] CHECK (condition)
For Example: In
the employee table to select the gender of a person, the query would be like
Check Constraint
at column level:
CREATE
TABLE employee ( id number(5) PRIMARY KEY, name char(20),
dept
char(10), age number(2), gender char(1) CHECK (gender in ('M','F')),
salary
number(10), location char(10) );
SQL
Joins
SQL Joins are
used to relate information in different tables. A Join condition is a part of
the sql query that retrieves rows from two or more tables. A SQL Join condition
is used in the SQL WHERE Clause of select, update, delete statements.
The Syntax for
joining two tables is:
SELECT
col1, col2, col3... FROM table_name1, table_name2 WHERE table_name1.col2 =
table_name2.col1;
If a sql join
condition is omitted or if it is invalid the join operation will result in a
Cartesian product. The Cartesian product returns a number of rows equal to the
product of all rows in all the tables being joined. For example, if the first
table has 20 rows and the second table has 10 rows, the result will be 20 * 10,
or 200 rows. This query takes a long time to execute.
Lets use the
below two tables to explain the sql join conditions.
database
table "product";
product_id product_name supplier_name unit_price
100 Camera Nikon 300
101 Television Onida 100
102 Refrigerator Vediocon 150
103 Ipod Apple 75
104 Mobile Nokia 50
database table
"order_items";
order_id product_id total_units customer
5100 104 30 Infosys
5101 102 5 Satyam
5102 103 25 Wipro
5103 101 10 TCS
SQL
Joins can be classified into Equi join and Non Equi join.
1)
SQL Equi joins
It is
a simple sql join condition which uses the equal sign as the comparison
operator. Two types of equi joins are SQL Outer join and SQL Inner join.
For
example: You can get the information about a customer who purchased a product
and the quantity of product.
2) SQL Non equi joins
It is
a sql join condition which makes use of some comparison operator other than the
equal sign like >, <, >=, <=
1) SQL Equi Joins:
An
equi-join is further classified into two categories:
a)
SQL
Inner Join
b)
SQL
Outer Join
a) SQL Inner Join:
All
the rows returned by the sql query satisfy the sql join condition specified.
For
example: If you want to display the product information for each order the
query will be as given below. Since you are retrieving the data from two
tables, you need to identify the common column between these two tables, which
is theproduct_id.
The query for
this type of sql joins would be like,
SELECT order_id,
product_name, unit_price, supplier_name, total_units
FROM product,
order_items WHERE order_items.product_id = product.product_id;
The
columns must be referenced by the table name in the join condition, because
product_id is a column in both the tables and needs a way to be identified.
This avoids ambiguity in using the columns in the SQL SELECT statement.
The
number of join conditions is (n-1), if there are more than two tables joined in
a query where 'n' is the number of tables involved. The rule must be true to
avoid Cartesian product.
We can also use
aliases to reference the column name, then the above query would be like,
SELECT
o.order_id, p.product_name, p.unit_price, p.supplier_name, o.total_units FROM
product p, order_items o WHERE o.product_id = p.product_id;
b)
SQL Outer Join:
This
sql join condition returns all rows from both tables which satisfy the join
condition along with rows which do not satisfy the join condition from one of
the tables. The sql outer join operator in Oracle is ( + ) and is used on one
side of the join condition only.
The
syntax differs for different RDBMS implementation. Few of them represent the
join conditions as "sql left outer join", "sql right outer
join".
If
you want to display all the product data along with order items data, with null
values displayed for order items if a product has no order item, the sql query
for outer join would be as shown below:
SELECT
p.product_id, p.product_name, o.order_id, o.total_units
FROM
order_items o, product p WHERE o.product_id (+) = p.product_id;
The output would
be like,
product_id product_name order_id total_units
100 Camera
101 Television 5103 10
102 Refrigerator 5101 5
103 Ipod 5102 25
104 Mobile 5100 30
NOTE: If
the (+) operator is used in the left side of the join condition it is
equivalent to left outer join. If used on the right side of the join condition
it is equivalent to right outer join.
SQL
Self Join:
A Self Join is a
type of sql join which is used to join a table to itself, particularly when the
table has a FOREIGN KEY that references its own PRIMARY KEY. It is necessary to
ensure that the join statement defines an alias for both copies of the table to
avoid column ambiguity.
The below query
is an example of a self join,
SELECT
a.sales_person_id, a.name, a.manager_id, b.sales_person_id, b.name
FROM
sales_person a, sales_person b WHERE a.manager_id = b.sales_person_id;
2)
SQL Non Equi Join:
A Non Equi Join
is a SQL Join whose condition is established using all comparison operators
except the equal (=) operator. Like >=, <=, <, >
For example: If
you want to find the names of students who are not studying either Economics,
the sql query would be like, (lets use student_details table defined earlier.)
SELECT
first_name, last_name, subject FROM student_details WHERE subject !=
'Economics'
The output would
be something like,
first_name last_name subject
Mona Kumari Maths
Shekar Gowda Maths
Shyam Prakash Science
Stephen Fleming Science
SQL
Views
A
VIEW is a virtual table, through which a selective portion of the data from one
or more tables can be seen. Views do not contain data of their own. They are
used to restrict access to the database or to hide data complexity. A view is
stored as a SELECT statement in the database. DML operations on a view like
INSERT, UPDATE, DELETE affects the data in the original table upon which the
view is based.
The
Syntax to create a sql view is
CREATE VIEW
view_name AS SELECT column_list FROM table_name [WHERE condition];
•
view_name is the name of the VIEW.
•
The SELECT statement is used to define
the columns and rows that you want to display in the view.
For Example: to
create a view on the product table the sql query would be like
CREATE
VIEW view_product AS SELECT product_id, product_name FROM product;
SQL
Index
Index in sql is
created on existing tables to retrieve the rows quickly.
When there are
thousands of records in a table, retrieving information will take a long time.
Therefore indexes are created on columns which are accessed frequently, so that
the information can be retrieved quickly. Indexes can be created on a single
column or a group of columns. When a index is created, it first sorts the data
and then it assigns a ROWID for each row.
Syntax to create
Index:
CREATE INDEX
index_name ON table_name (column_name1,column_name2...);
Syntax to create
SQL unique Index:
CREATE UNIQUE
INDEX index_name
ON table_name
(column_name1,column_name2...);
•
index_name is the name of the INDEX.
•
table_name is the name of the table to
which the indexed column belongs.
•
column_name1, column_name2.. is the list
of columns which make up the
INDEX.
In Oracle there
are two types of SQL index namely, implicit and explicit.
Implicit
Indexes:
They are created
when a column is explicity defined with PRIMARY KEY, UNIQUE KEY Constraint.
Explicit
Indexes:
They are created
using the "create index.. " syntax.
NOTE:
•
Even though sql indexes are created to
access the rows in the table quickly, they slow down DML operations like
INSERT, UPDATE, DELETE on the table, because the indexes and tables both are
updated along when a DML operation is performed. So use indexes only on columns
which are used to search the table frequently.
•
It is not required to create indexes on
table which have less data.
•
In oracle database you can define up to
sixteen (16) columns in an INDEX.
DCL
DCL
commands are used to enforce database security in a multiple user database
environment. Two types of DCL commands are GRANT and REVOKE. Only Database Administrator's
or owner's of the database object can provide/remove privileges on a database
object.
SQL
GRANT Command
SQL GRANT is a
command used to provide access or privileges on the database objects to the
users.
The Syntax for
the GRANT command is:
GRANT
privilege_name ON object_name TO {user_name |PUBLIC |role_name}
[WITH GRANT
OPTION];
•
privilege_name is the access right or
privilege granted to the user. Some of the access rights are ALL, EXECUTE, and
SELECT.
•
object_name is the name of an database
object like TABLE, VIEW, STORED PROC and SEQUENCE.
•
user_name is the name of the user to
whom an access right is being granted.
•
user_name is the name of the user to
whom an access right is being granted.
•
PUBLIC is used to grant access rights to
all users.
•
ROLES are a set of privileges grouped
together.
•
WITH GRANT OPTION - allows a user to
grant access rights to other users.
For Example:
GRANT SELECT ON employee TO user1;This command grants a SELECT permission on
employee table to user1.You should use the WITH GRANT option carefully because
for example if you GRANT SELECT privilege on employee table to user1 using the
WITH GRANT option, then user1 can GRANT SELECT privilege on employee table to
another user, such as user2 etc. Later, if you REVOKE the SELECT privilege on
employee from user1, still user2 will have SELECT privilege on employee table.
SQL
REVOKE Command:
The REVOKE
command removes user access rights or privileges to the database objects.
The Syntax for
the REVOKE command is:
REVOKE
privilege_name ON object_name FROM {user_name |PUBLIC |role_name}
For Example:
REVOKE SELECT ON employee FROM user1;This command will REVOKE a SELECT
privilege on employee table from user1.When you REVOKE SELECT privilege on a
table from a user, the user will not be able to SELECT data from that table
anymore. However, if the user has received SELECT privileges on that table from
more than one users, he/she can SELECT from that table until everyone who
granted the permission revokes it. You cannot REVOKE privileges if they were
not initially granted by you.
Privileges
and Roles:
Privileges:
Privileges defines the access rights provided to a user on a database object.
There are two types of privileges.
1)
System privileges - This allows the user
to CREATE, ALTER, or DROP database objects.
2)
Object privileges - This allows the user
to EXECUTE, SELECT, INSERT, UPDATE, or DELETE data from database objects to
which the privileges apply.
Few CREATE
system privileges are listed below:
System Privileges Description
CREATE object allows
users to create the specified object in their own schema.
CREATE ANY
object allows users to create
the specified object in any schema.
The above rules
also apply for ALTER and DROP system privileges.
Few of the
object privileges are listed below:
Object Privileges Description
INSERT allows users to insert rows into a
table.
SELECT allows users to select data from a
database object.
UPDATE allows user to update data in a
table.
EXECUTE allows
user to execute a stored procedure or a function.
Roles: Roles are
a collection of privileges or access rights. When there are many users in a
database it becomes difficult to grant or revoke privileges to users.
Therefore, if you define roles, you can grant or revoke privileges to users,
thereby automatically granting or revoking privileges. You can either create
Roles or use the system roles pre-defined by oracle.
Some of the
privileges granted to the system roles are as given below:
System Role Privileges Granted to the Role
CONNECT CREATE TABLE, CREATE VIEW, CREATE SYNONYM,
CREATE SEQUENCE, CREATE SESSION etc.
RESOURCE CREATE PROCEDURE, CREATE SEQUENCE, CREATE
TABLE, CREATE TRIGGER etc. The primary usage of the RESOURCE role is to
restrict access to database objects.
DBA ALL SYSTEM PRIVILEGES
Creating
Roles:
The Syntax to
create a role is:
CREATE
ROLE role_name [IDENTIFIED BY password];
For Example: To
create a role called "developer" with password as "pwd",the
code will be as follows
CREATE
ROLE testing [IDENTIFIED BY pwd];
It's easier to
GRANT or REVOKE privileges to the users through a role rather than assigning a
privilege directly to every user. If a role is identified by a password, then,
when you GRANT or REVOKE privileges to the role, you definitely have to
identify it with the password.
We can GRANT or
REVOKE privilege to a role as below.
For example: To
grant CREATE TABLE privilege to a user by creating a testing role:
First, create a
testing Role
CREATE
ROLE testing
Second, grant a
CREATE TABLE privilege to the ROLE testing. You can add more privileges to the
ROLE.
GRANT
CREATE TABLE TO testing;
Third, grant the
role to a user.
GRANT
testing TO user1;
To revoke a
CREATE TABLE privilege from testing ROLE, you can write:
REVOKE CREATE
TABLE FROM testing;
The Syntax to drop
a role from the database is as below:
DROP
ROLE role_name;
For example: To
drop a role called developer, you can write:
DROP
ROLE testing;
Lab
‘To create a database
·
create database bank
‘To open/use databae
·
use bank
‘To create Table
·
create table deposit(empcode int,empname varchar(20),empcity
varchar(10))
‘To See the structure of
table
·
sp_help deposit
‘To Modify the Structure of
Table
‘To modify the column of
table
·
ALTER TABLE deposit ALTER COLUMN empcity varchar(20);
‘To Add a column into a
table
·
ALTER TABLE deposit ADD empdept varchar(20);
‘To remove a column from
table
·
ALTER TABLE deposit DROP COLUMN empdept
‘To insert data into table
using column name
·
insert into deposit (empcode,empname,empcity) values
(101,'Salik','Patna')
‘To insert data into table
without using column name
·
insert into deposit values (102,'Rashid','Gaya')
‘To insert data into
specific column of table
·
insert into deposit
(empcode,empname) values (103,'Kajal')
‘To show all the record of table
·
select * from deposit
No comments:
Post a Comment