What is SQL

SQL is Structured query language, which is database language for storing, retrieving and manipulating data stored in relational database.

SQL is standard language for RDMS(Relational Database Management System) like the dialects mentioned as below

  • SQL Server using T-SQL
  • Oracle using PL/SQL
  • IBM using DB2 etc.,

Advantages of SQL

  • Allows users to store data in rows and columns in database
  • Provide set of permissions on DB Objects like Tables, Views, Procedures etc.,
  • Allows to describe the data and embed within other languages
  • Users can create and drop databases and tables

History of SQL

  • 1970 – Edgar F Ted Codd of IBM known as father of RDBMS. He describes the relational model for database
  • 1974 – SQL has been official declared
  • 1978 – IBM worked and implemented the ideas of EF Codd’s and released product with name System/R
  • 1986 – IBM developed first prototype of relational database and standardized by ANSI. Later came to known as Oracle

SQL Architecture/Process

When we are trying to execute any kind of SQL query in any of RDBMS, system determines the best way to carry out the request.

Below are various components involved in process of executing SQL statement

  • Query Dispatcher
  • Optimization Engines
  • Classic Query Engine
  • SQL Query Engine etc.,

All non-SQL queries will be handled by classic query engine. SQL query engine won’t handle logical files

What is RDBMS

RDBMS stands for Relational Database Management System. RDBMS is the basis for SQL and for all relation database products like Oracle, MS SQL Server, DB2, MY SQL etc.,

RDBMS is a DBMS(Database management System) which is based on relational model functionalities as introduced by EF Codd

What is Table

Data stored in RDBMS stored in the form database Objects. Table is one of the database object, which is basically collection of related data entries. These entries can be stored in the form of rows and columns

 

What is Field?

Each table broken up into smaller entities called fields. Fields in Employee table consist of Emp_id, Ename, Sal etc.,

A field is a column that is designed to maintain particular information about every record in a table

What is Row or Record?

A Record usually called as Row in a table and we consider each horizontal row as a record

What is column?

A column is a vertical entity in a table that contains all information associated with a specific field in a table

Data Definition Language(DDL) commands are used for creating, modifying, and dropping the structure of database objects

  • Create
  • Alter
  • Rename
  • Drop
  • Truncate

CREATE
CREATE statement is used to create a new database, table, index or stored procedure.

Create database example:

CREATE DATABASE xx_test_db;

Create table example:

CREATE TABLE xx_user (
id NUMBER PRIMARY KEY,
first_name VARCHAR2(255) NOT NULL);

DROP
DROP statement allows you to remove database, table, index or stored procedure.

Drop database example:

DROP DATABASE xx_test_db;

Drop table example:

DROP TABLE xx_user;

ALTER
ALTER is used to modify existing database data structures (database, table).

Alter table example:

ALTER TABLE xx_user ADD COLUMN last_name VARCHAR2(255) NOT NULL;

RENAME
RENAME command is used to rename SQL table.

Rename table example:

RENAME TABLE xx_user TO xx_test_tab;

TRUNCATE
TRUNCATE operation is used to delete all table records.

Logically it’s the same as DELETE command.

Differences between DELETE and TRUNCATE commands are:

TRUNCATE is really faster
TRUNCATE cannot be rolled back
TRUNCATE command does not invoke ON DELETE triggers
Example:

TRUNCATE xx_new_user;

Data Manipulation Language(DML) commands are used for storing, retrieving, modifying, and deleting data

  • INSERT
  • UPDATE
  • DELETE

DML is a Data Manipulation Language, it’s used to build SQL queries to manipulate (insert, update, delete) data in the database.

This is DML commands list with examples:

INSERT

INSERT command is used to add new rows into the database table.

Example:

 
1
INSERT INTO xx_test_tab (first_name, last_name) VALUES (‘Raghu’, ‘Ravi’);
UPDATE

UPDATE statement modifies records into the table.

Example:

 
1
UPDATE xx_test_tab SET name = ‘Ram’ WHERE last_name = ‘Raghu’;
DELETE

DELETE query removes entries from the table.

Example:

 
1
DELETE FROM xx_test_tab WHERE first_name = ‘Ram’;
SELECT (Data Retrieval Command-DRL)

SELECT query is used to retrieve a data from SQL tables.

Example:

 
1
SELECT * FROM xx_test_tab;

 

Data Control Language(DCL) commands are used for providing security to database objects.

  • GRANT
  • REVOKE

DCL commands are responsible for access restrictions inside of the database.

Let’s take a look at DCL statements definitions.

GRANT

GRANT command gives permissions to SQL user account.

For example, I want to grant all privileges to ‘xx_test_db’ database for user ‘xx_user@localhost’.

Let’s create a user first:

Then I can grant all privileges using GRANT statement:

and we have to save changes using FLUSH command:

REVOKE

REVOKE statement is used to remove privileges from user accounts.

Example:

and save changes:

Transaction Control Language(TCL) commands are used for managing changes affecting the data in database

  • COMMIT
  • ROLLBACK
  • SAVEPOINT

TCL commands are used to manage transactions in SQL databases.

This is TCL commands list:

START TRANSACTION (BEGIN, BEGIN WORK)

START TRANSACTION is used to start a new SQL transaction.

BEGIN and BEGIN WORK are aliases for START TRANSACTION.

Example:

 
1
START TRANSACTION;

after that, you’re doing manipulations with a data (insert, update, delete) and at the end, you need to commit a transaction.

COMMIT

As a mentioned above COMMIT command finishes transaction and stores all changes made inside of a transaction.

Example:

 
1
2
3
START TRANSACTION;
INSERT INTO xx_test_tab (first_name, last_name) VALUES (‘Ramesh’, ‘Babu’);
COMMIT;
ROLLBACK

ROLLBACK statement reverts all changes made in the scope of transaction.

Example:

 
1
2
3
START TRANSACTION;
INSERT INTO xx_test_tab (first_name, last_name) VALUES (‘Rajesh’, ‘Babu’);
ROLLBACK;
SAVEPOINT

under progress ……………………..

Usually storing the data in variables can be defined using different data types. SQL provides different scalar data types as below

  • Numeric
  • Character
  • Boolean (TRUE/FALSE)
  • Date/Time
  • LOB(Large Object)
  • RowID

Numeric Datatypes

The following are the Numeric Datatypes in Oracle/PLSQL:

Data TypeOracle SizeExplanation
number(p,s)Precision can range from 1 to 38.Where p is the precision and s is the scale.
Scale can range from -84 to 127.For example, number(7,2) is a number that has 5 digits before the decimal and 2 digits after the decimal.
numeric(p,s)Precision can range from 1 to 38.Where p is the precision and s is the scale.
For example, numeric(7,2) is a number that has 5 digits before the decimal and 2 digits after the decimal.
float  
dec(p,s)Precision can range from 1 to 38.Where p is the precision and s is the scale.
For example, dec(3,1) is a number that has 2 digits before the decimal and 1 digit after the decimal.
decimal(p,s)Precision can range from 1 to 38.Where p is the precision and s is the scale.
For example, decimal(3,1) is a number that has 2 digits before the decimal and 1 digit after the decimal.
integer  
int  
smallint  
real  
double precision  

 

Character Datatypes

The following are the Character Datatypes in Oracle/PLSQL:

Data TypeOracle SizeExplanation
char(size)Maximum size of 2000 bytes.Where size is the number of characters to store. Fixed-length strings. Space padded.
nchar(size)Maximum size of 2000 bytes.Where size is the number of characters to store. Fixed-length NLS string Space padded.
nvarchar2(size)Maximum size of 4000 bytes.Where size is the number of characters to store. Variable-length NLS string.
varchar2(size)Maximum size of 4000 bytes.Where size is the number of characters to store. Variable-length string.
Maximum size of 32KB in PLSQL.
longMaximum size of 2GB.Variable-length strings. (backward compatible)
rawMaximum size of 2000 bytes.Variable-length binary strings
long rawMaximum size of 2GB.Variable-length binary strings. (backward compatible)

Date/Time Datatypes

The following are the Date/Time Datatypes in Oracle/PLSQL:

Data TypeOracle SizeExplanation
dateA date between Jan 1, 4712 BC and Dec 31, 9999 AD. 
timestamp (fractional seconds precision)fractional seconds precision must be a number between 0 and 9. (default is 6)Includes year, month, day, hour, minute, and seconds.
For example:
timestamp(6)
timestamp (fractional seconds precision) with time zonefractional seconds precision must be a number between 0 and 9. (default is 6)Includes year, month, day, hour, minute, and seconds; with a time zone displacement value.
For example:
timestamp(5) with time zone
timestamp (fractional seconds precision) with local time zonefractional seconds precision must be a number between 0 and 9. (default is 6)Includes year, month, day, hour, minute, and seconds; with a time zone expressed as the session time zone.
For example:
timestamp(4) with local time zone
interval yearyear precision is the number of digits in the year. (default is 2)Time period stored in years and months.
(year precision)For example:
to monthinterval year(4) to month
interval dayday precision must be a number between 0 and 9. (default is 2)Time period stored in days, hours, minutes, and seconds.
(day precision)fractional seconds precision must be a number between 0 and 9. (default is 6)For example:
to second (fractional seconds precision) interval day(2) to secon

 

Large Object (LOB) Datatypes

The following are the LOB Datatypes in Oracle/PLSQL:

Data TypeOracle SizeExplanation
bfileMaximum file size of 264-1 bytes.File locators that point to a binary file on the server file system (outside the database).
blobStore up to (4 gigabytes -1) * (the value of the CHUNK parameter of LOB storage).Stores unstructured binary large objects.
clobStore up to (4 gigabytes -1) * (the value of the CHUNK parameter of LOB storage) of character data.Stores single-byte and multi-byte character data.
nclobStore up to (4 gigabytes -1) * (the value of the CHUNK parameter of LOB storage) of character text data.Stores Unicode data

Rowid Datatypes

The following are the Rowid Datatypes in Oracle/PLSQL:

 

Data TypeOracle SizeExplanation
rowidThe format of the rowid is: BBBBBBB.RRRR.FFFFFFixed-length binary data. Every record in the database has a physical address or rowid.
Where BBBBBBB is the block in the database file;
RRRR is the row in the block;
FFFFF is the database file.
urowid(size) Universal rowid.
Where size is optional.

Oracle Constraints is a rule imposed on tables to restrict the values stored in the table. We have five different type of constraints in Oracle

  • NOT NULL
  • UNIQUE
  • PRIMARY KEY
  • FOREIGN KEY
  • CHECK

1. NOT Null Constraint

NOT NULL constraint checks the columns to be populated with non-null values. 

Example :-

Create table person
(
pno   integer,
pname varchar2(20) not null
);

pname column has a not null constraint.

SQL> desc person;
Name                                      Null?    Type
—————————————– ——– ————
PNO                                                NUMBER(38)
PNAME                    NOT NULL VARCHAR2(20)

Below insert statement will validate the above not null constraints

SQL> insert into person values(1,null);
insert into person values(1,null)
*
ERROR at line 1:
ORA-01400: cannot insert NULL into (“PERSON”.”PNAME”)

Where to Use not null constraint

Situations when column needs to populated always can use NOT NULL constraint.
For example for EMP table, empname column must be not null, but for salary it may not be.

2. UNIQUE Constraint

UNIQUE Constraint imposes each column will have distinct value within the table. Not that UNIQUE Constraint column can have null values. Null values are not considered for uniqueness. UNIQUE Constraint can be applied to composite(Two or more columns) keys also.

Example :-

Create table person
(
pno   integer,
pname varchar2(20) constraint uq_pname unique
);

SQL> insert into person values(1,’Bill’);

1 row created.

SQL> insert into person values(2,’Bill’);
insert into person values(2,’Bill’)
*
ERROR at line 1:
ORA-00001: unique constraint (UQ_PNAME) violated

When we are trying to insert PERSON table with duplicate pname it will raise an error. UNIQUE constraint column can accept null values. Not that Oracle creates a unique index automatically when UNIQUE constraint assigned to a column.

Where to use unique constraint

Unique constraints can be assigned to columns those have unique values or no values.

3. Primary Key Constraint

A primary key constraint combines a NOT NULL constraint and a UNIQUE constraint. A table can have only one PRIMARY KEY and it can be created for composite(Two or more columns) keys also.

Example :-

Create table person
(
pno   integer constraint pk_pno PRIMARY KEY,
pname varchar2(20)
);

Where to Use PRIMARY KEY constraint

Generally every table should have a PRIMARY KEY (If you are normalizing the design).

4. Foreign Key Constraint

Foreign Key Constraint used to relate two or more table and values in one table to match values in another table.

CREATE TABLE DEPT
(
DEPTNO   INTEGER PRIMARY KEY,
DEPTNAME VARCHAR2(20) NOT NULL,
LOCATION VARCHAR2(20)
);

CREATE TABLE EMP
(
EMPNO    INTEGER PRIMARY KEY,
EMPNAME  VARCHAR2(20) NOT NULL,
SALARY   NUMBER,
DEPTNO   INTEGER constraint fk_deptno references DEPT(deptno)
);

Where to Use FOREIGN KEY constraint

If you want to maintain a PARENT-CHILD relationship FOREIGN KEY constraints are ideal.

5. CHECK Constraint

CHECK Constraints are enforcing certain types of values in a column.

Create table person
(
pno    integer,
pname  varchar2(20),
status varchar2(20) constraint ch_status check( status in (‘ACTIVE’,’INACTIVE’))
);

Here status column have only two value, ‘ACTIVE’ or ‘INACTIVE’

 

Points to remember while using Constraints:

1. It is nice to have a primary key constraint for all tables.

2. It is better to create unique index rather than creating UNIQUE constraint

3. Too many constraints also difficult to maintain in future.

4. Do not create too many CHECK constraints

Note :

  1. Oracle constraints are integral part of table. Constraints help us to maintain the integrity of the database. Also helps to reduce the redundant data
  2. NOT NULL constraints must be declared inline. All other constraints can be declared either inline or out of line.

 

Under Progress………………….

Oracle Standard Built-In Functions

There are two types of standard functions in Oracle.
1) Single Row Functions: Single row or Scalar functions return a value for every row that is processed in a query.
2) Group Functions: These functions group the rows of data based on the values returned by the query. This is discussed in SQL GROUP Functions. The group functions are used to calculate aggregate values like total or average, which return just one total or one average value after processing a group of rows.

There are four types of single row functions. They are:
1) Numeric Functions: These are functions that accept numeric input and return numeric values.
2) Character or Text Functions: These are functions that accept character input and can return both character and number values.
3) Date Functions: These are functions that take values that are of datatype DATE as input and return values of datatype DATE, except for the MONTHS_BETWEEN function, which returns a number.
4) Conversion Functions: These are functions that help us to convert a value in one form to another form. For Example: a null value into an actual value, or a value from one datatype to another datatype like NVL, TO_CHAR, TO_NUMBER, TO_DATE etc.

You can combine more than one function together in an expression. This is known as nesting of functions.

What is a DUAL Table in Oracle?
This is a single row and single column dummy table provided by oracle. This is used to perform mathematical calculations without using a table.

Select * from DUAL

Output:

DUMMY
——-
X

Select 777 * 888 from Dual

Output:

777 * 888
———
689976

1) Numeric Functions:

Numeric functions are used to perform operations on numbers. They accept numeric values as input and return numeric values as output. Few of the Numeric functions are:

Function NameReturn Value
ABS (x)Absolute value of the number ‘x
CEIL (x)Integer value that is Greater than or equal to the number ‘x
FLOOR (x)Integer value that is Less than or equal to the number ‘x
TRUNC (x, y)Truncates value of number ‘x‘ up to ‘y‘ decimal places
ROUND (x, y)Rounded off value of the number ‘x‘ up to the number ‘y‘ decimal places

The following examples explains the usage of the above numeric functions

Function NameExamplesReturn Value
ABS (x)ABS (1)
ABS (-1)
1
-1
CEIL (x)CEIL (2.83)
CEIL (2.49)
CEIL (-1.6)
3
3
-1
FLOOR (x)FLOOR (2.83)
FLOOR (2.49)
FLOOR (-1.6)
2
2
-2
TRUNC (x, y)ROUND (125.456, 1)
ROUND (125.456, 0)
ROUND (124.456, -1)
125.4
125
120
ROUND (x, y)TRUNC (140.234, 2)
TRUNC (-54, 1)
TRUNC (5.7)
TRUNC (142, -1)
140.23
54
5
140

These functions can be used on database columns.

For Example: Let’s consider the product table used in sql joins. We can use ROUND to round off the unit_price to the nearest integer, if any product has prices in fraction.

SELECT ROUND (unit_price) FROM product;

2) Character or Text Functions:

Character or text functions are used to manipulate text strings. They accept strings or characters as input and can return both character and number values as output.

Few of the character or text functions are as given below:

Function NameReturn Value
LOWER (string_value)All the letters in ‘string_value’ is converted to lowercase.
UPPER (string_value)All the letters in ‘string_value’ is converted to uppercase.
INITCAP (string_value)All the letters in ‘string_value’ is converted to mixed case.
LTRIM (string_value, trim_text)All occurrences of ‘trim_text’ is removed from the left of ‘string_value’.
RTRIM (string_value, trim_text)All occurrences of ‘trim_text’ is removed from the right of ‘string_value’ .
TRIM (trim_text FROM string_value)All occurrences of ‘trim_text’ from the left and right of ‘string_value’ , ‘trim_text’ can also be only one character long .
SUBSTR (string_value, m, n)Returns ‘n’ number of characters from ‘string_value’ starting from the ‘m’ position.
LENGTH (string_value)Number of characters in ‘string_value’ in returned.
LPAD (string_value, n, pad_value)Returns ‘string_value’ left-padded with ‘pad_value’ . The length of the whole string will be of ‘n’ characters.
RPAD (string_value, n, pad_value)Returns ‘string_value’ right-padded with ‘pad_value’ . The length of the whole string will be of ‘n’ characters.

For Example, we can use the above UPPER() text function with the column value as follows.

SELECT UPPER (product_name) FROM product;

The following examples explains the usage of the above character or text functions

Function NameExamplesReturn Value
LOWER(string_value)LOWER(‘Good Morning’)good morning
UPPER(string_value)UPPER(‘Good Morning’)GOOD MORNING
INITCAP(string_value)INITCAP(‘GOOD MORNING’)Good Morning
LTRIM(string_value, trim_text)LTRIM (‘Good Morning’, ‘Good)Morning
RTRIM (string_value, trim_text)RTRIM (‘Good Morning’, ‘ Morning’)Good
TRIM (trim_text FROM string_value)TRIM (‘o’ FROM ‘Good Morning’)Gd Mrning
SUBSTR (string_value, m, n)SUBSTR (‘Good Morning’, 6, 7)Morning
LENGTH (string_value)LENGTH (‘Good Morning’)12
LPAD (string_value, n, pad_value)LPAD (‘Good’, 6, ‘*’)**Good
RPAD (string_value, n, pad_value)RPAD (‘Good’, 6, ‘*’)Good**

3) Date Functions:

These are functions that take values that are of datatype DATE as input and return values of datatypes DATE, except for the MONTHS_BETWEEN function, which returns a number as output.

Few date functions are as given below.

Function NameReturn Value
ADD_MONTHS (date, n)Returns a date value after adding ‘n’ months to the date ‘x’.
MONTHS_BETWEEN (x1, x2)Returns the number of months between dates x1 and x2.
ROUND (x, date_format)Returns the date ‘x’ rounded off to the nearest century, year, month, date, hour, minute, or second as specified by the ‘date_format’.
TRUNC (x, date_format)Returns the date ‘x’ lesser than or equal to the nearest century, year, month, date, hour, minute, or second as specified by the ‘date_format’.
NEXT_DAY (x, week_day)Returns the next date of the ‘week_day’ on or after the date ‘x’ occurs.
LAST_DAY (x)It is used to determine the number of days remaining in a month from the date ‘x’ specified.
SYSDATEReturns the systems current date and time.
NEW_TIME (x, zone1, zone2)Returns the date and time in zone2 if date ‘x’ represents the time in zone1.

The below table provides the examples for the above functions

Function NameExamplesReturn Value
ADD_MONTHS ( )ADD_MONTHS (’16-Sep-81′, 3)16-Dec-81
MONTHS_BETWEEN( )MONTHS_BETWEEN (’16-Sep-81′, ’16-Dec-81′)3
NEXT_DAY( )NEXT_DAY (’01-Jun-08′, ‘Wednesday’)04-JUN-08
LAST_DAY( )LAST_DAY (’01-Jun-08′)30-Jun-08
NEW_TIME( )NEW_TIME (’01-Jun-08′, ‘IST’, ‘EST’)31-May-08

4) Conversion Functions:

These are functions that help us to convert a value in one form to another form. For Ex: a null value into an actual value, or a value from one datatype to another datatype like NVL, TO_CHAR, TO_NUMBER, TO_DATE.

Few of the conversion functions available in oracle are:

Function NameReturn Value
TO_CHAR (x [,y])Converts Numeric and Date values to a character string value. It cannot be used for calculations since it is a string value.
TO_DATE (x [, date_format])Converts a valid Numeric and Character values to a Date value. Date is formatted to the format specified by ‘date_format’.
NVL (x, y)If ‘x’ is NULL, replace it with ‘y’‘x’ and ‘y’ must be of the same datatype.
DECODE (a, b, c, d, e, default_value)Checks the value of ‘a’, if a = b, then returns ‘c’. If a = d, then returns ‘e’. Else, returns default_value.

The below table provides the examples for the above functions

Function NameExamplesReturn Value
TO_CHAR ()TO_CHAR (3000, ‘$9999’)
TO_CHAR (SYSDATE, ‘Day, Month YYYY’)
$3000
Monday, June 2008
TO_DATE ()TO_DATE (’01-Jun-08′)01-Jun-08
NVL ()NVL (null, 1)1

Aggregate 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;

Under Progress………………….

Under Progress………………….

SQL Joins

Oracle join is used in queries to join two or more tables, columns or views based on the values of related columns of both the tables. For example, primary key of the first table and foreign keys of the second table are related columns to extract relevant data from database and again based on the requirements joins can be inner join, outer join, left outer join, right outer join, self join and all of these joins are supported in Oracle database.

Types of Joins in SQL are

  • Equi Joins
    • Cartesian Join
    • Inner-Joins
    • Outer Joins
    • Self Join
  • Non-Equi-Joins

Joins in SQL

The SQL 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.

SQL Joins Example

Lets use the below two tables to explain the sql join conditions.

Database table “product”;

product_idproduct_namesupplier_nameunit_price
100CameraNikon300
101TelevisionOnida100
102RefrigeratorVediocon150
103IpodApple75
104MobileNokia50

Database table “order_items”;

order_idproduct_idtotal_unitscustomer
510010430Infosys
51011025Satyam
510210325Wipro
510310110TCS

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:

Inner joins join the multiple tables and return those rows for which the join condition is true. The inner join is the most common join among the types of join.

SQL Inner Join 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 the product_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:

Another type of joins is an outer join which returns a result of an inner join plus all the rows from one table for which the join condition is not true. 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_idproduct_nameorder_idtotal_units
————-————-————-————-
100Camera  
101Television510310
102Refrigerator51015
103Ipod510225
104Mobile510030

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 >=, <=, <, >

SQL Non Equi Join 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_namelast_namesubject
————-————-————-
AnajaliBhagwatMaths
ShekarGowdaMaths
RahulSharmaScience
StephenFlemingScience

Cartesian Join

 

Cross join applies where the two tables have no join condition.the cross join return the Cartesian product of the two tables, Cartesian product where each row of one table combines with each row of the other table. Suppose table1 contains 100 rows and table2 contains 10 rows then the join result would contain 1000 rows.

Example;

SELECT employee.employee _id, employee.employee_name, department.department_name
FROM employee
CROSS JOIN department

This above cross JOIN example will return all rows of employee table combine with all rows of the department table.

Under Progress………………….

Oracle Views

View in Oracle database is actually a virtual table that is not physically stored in the database data dictionary and does not store any sort of data and is created generally by using join query with two or more tables which enables the view to hide the data complexity also providing security since actually we are not storing any data in the view physically so it actually restricts the access of various columns of a table to the user of the database. Views are created by a query joining one or more tables.

CREATE VIEW in Oracle

Let us see creating View in Oracle with the help of some examples mentioned below:

 

The syntax to create the view –

CREATE VIEW view name AS
SELECT column [ , column ] FROM table
WHERE condition;

View name – It specifies Oracle VIEW name that the user wants to create.

An oracle view visual representation is represented in the below diagram, as in the below diagram the red shaded area return as the result of the Oracle select query and which is stored as a view –

 

Query Examples

Let’s take an example to create a view. Here is an example, we are creating a simple view on a single table.

Suppliers table

Query Example:

CREATE VIEW empview AS
SELECT *
FROM employees;
WHERE employees.manager_id = 100;

This above Oracle INNER JOIN example will return all rows from the employee table and department table where the employee _id value in both the employee table and department table are matched.

Let’s understand the oracle view in detail with the help of some of the examples query:

We consider here the hr schema which is the oracle database sample schemas. The hr schema contains COUNTRIES, EMPLOYEES, DEPARTMENTS, JOB_HISTORY, JOBS, LOCATIONS, REGIONS tables, in which we are interested or require EMPLOYEES, DEPARTMENTS and LOCATIONS tables.

The description of these tables are :

Table EMPLOYEES

Table Employees

Table DEPARTMENTS

Table departments

Example #1

Create a view on selected columns

Here we create a view named employee view based on the employee’s table. The employee view having the employee id, employee full name which is the concatenation of first_name and last_name and employee phone_number-

Code:

CREATE VIEW employee view AS
SELECT
employee_id,
first_name || ' ' || last_name full name,
phone_number
FROM
employees;

We can now check the just created above Oracle VIEW by using this query –

Select * from employee view;

Example #2

Creating oracle view with columns alias

Code:

CREATE VIEW employee view AS
SELECT employee_id, first_name || '  ' || last_name "full name", FLOOR( months_between ( CURRENT_DATE, hire_date )/ 12 ) as years
FROM employees;

Select * from employee view;

Example #3

Delete view by using the Drop statement

Code:

Drop view employee view;

Advantages and Disadvantages of Views in an oracle

The advantages and disadvantages are given below:

Advantages of Views in an oracle

Below are the advantages:

  • The view can be created on selected data or column of the table, which restrict the view of a table and can hide some of the data or column in the tables.
  • The view creates to view the data without storing the data into the table.
  • View cab is created to Join two or more tables data and store it as one table or object.
  • A view cab is created to achieve security.
  • The view gives good performance to run complex queries or join queries.

Disadvantages of Views in an oracle

Below are the disadvantages:

  • DML operations cannot perform in view.
  • The view becomes inactive if the table is dropped on which it is created.
  • View occupies a memory space as it is an object.

Under Progress………………….

SQL Interview Questions

1) How to remove duplicates records from a table?

There are many ways to delete duplicates from a table

In the below query you can include the field that contains duplicate or the combination of the fields that are expected to have duplicates.

DELETE
FROM MyTable
WHERE (rowid, FieldWithDuplicates ) <> (SELECT max(rowid), FieldWithDuplicates
FROM MyTable
GROUP By FieldWithDuplicates)

The above query will fail to remove null duplicates.
The number of nulls in the FieldWithDuplicates will remain the same.
The below query will remove the null duplicates


DELETE
FROM MyTable a
where
a.rowid >
any (select b.rowid
from
MyTable b
where a. Duplicatecol = b.Duplicatecol or (a. Duplicatecol is null and b. Duplicatecol is null))
)
2) Say There are 1million records in table emp.

Select count(*) from emp; — It takes 100 secs

Now We run the following

delete from emp;
Select count(*) from emp;

Truncate Table emp;
Select count(*) from emp;

What will be the time taken to execute the select after delete and select after Truncate statement?

Answer:- Select after Delete will take 100 secs.
Select after Truncate will be in milli secs

3) Should a function return value?

Functions must return a value

4) What are the types of sql statements?

1. Data Definition Language (DDL) —> Define, alter or drop database objects
2. Data Manipulation Language (DML) —> Manipulate Data
3. Data Control Language (DCL) —> Grant/Revoke access to Objects
4. Transaction Control Language (TCL) —> Commit/Rollback Transactions
5. Data Retrieval Language —> Fetch data from database

5) Find the second maximum salary in a table employee

SELECT max(salary)
FROM employee
Where Salary < (Select max(salary) from employee)

5) Find the Fifth maximum salary in a table employee

SELECT salary
FROM (
SELECT salary
FROM employee
WHERE rownum<=5
ORDER BY salary desc
)
WHERE rownum<=1
ORDER BY salary asc

6) What is rownum?

Rownum is a Pseudo Column that denotes the row number of the record in a result set. Row number is 1 means its the first record of the result set.

7) How many rows are fetched, when the below query is executed?
SELECT *
FROM MyTable
WHERE rownum = 3;
(There are more Than 3 rows)

No Rows are retuned. The above query is trying to fetch only the third row. Third query will be fetched only after fetching first two rows. This condition never becomes true. So no rows are returned.