OracleBasics – Learning-Inz

Oracle Basics :

Oracle Basics and Rules - Lesson 1

Oracle :
- Oracle is a Relational Data Base Management System – RDBMS
Some Rules :
- Clauses : Different functions to perform
- User : which can access your Database
- Select : Data Gather
  - Example : Select first_name,last_name,salary,salary*12 from employees;
  - Example : Select first_name,last_name,salary,salary*12+100 from employees;
- From : Chose table
- Where : Get selective Data
- Group by : it can group Duplicate entries
- Having : clause is used in combination with the GROUP BY clause to restrict the groups of returned rows to only those whose the condition is TRUE.
- Order By : can make data in order by columns
- * : All rows and columns
- Tab : Tabs are an effective way to navigate users between pages of an application
- Cat : is a synonym for USER_CATALOG . See Also: “USER_CATALOG”
- Desc : This is use with ORDER BY keyword to sort the result-set in descending order
- Asc : This is use with ORDER BY keyword to sort the result-set in ascending order
- Row : row is a collection of fields that make up a record.
  - The cells in a row run horizontally, and together, contain all data for that record.
  - A row can contain as many fields as required, each one defined in a different column.
  - Diagram :
- Column : Column is a vertical group of fields within a table.
  - Each column is assigned a data type and other constraints which determine the type of value that can be stored in that column.
  - Diagram :
- Cell : cell is a part of a table where a row and column intersect.
  - A cell is designed to hold a specified portion of the data within a record.
  - A cell is sometimes referred as a field.
  - Diagram :
- Field : field is often used to refer to the individual cells within a row or column. However, it can also refer to the whole column itself.
  - However, some experts argue that fields and columns are two different things:
```
Columns are not fields. 
Fields get meaning from the application reading them — and may have several meanings depending on the apps.
Fields are sequential within a record and do not have data types, constraints or defaults.
```

Oracle Basic Alias / Concatenate /Aggregate - Lesson 2

Alias in SQL :
- Alias is called a correlation name.
- Example : Select Employee ‘Employee Name’ from employee;
Concatenate Join Two Columns in SQL :
- Example : Select first_name||’ ‘||last_name as Name from employees;
Aggregate in SQL :
- SUM :
  - Example : SELECT SUM(SALARY) FROM EMPLOYEES;
- MAX :
  - Example : SELECT MAX(SALARY) FROM EMPLOYEES;
- MIN :
  - Example : SELECT MIN(SALARY) FROM EMPLOYEES;
- COUNT :
  - Example : SELECT COUNT(SALARY) FROM EMPLOYEES;
- AVG :
  - Example : SELECT AVG(SALARY) FROM EMPLOYEES;

Oracle Basic Joins - Lesson 3

Joins :
- (INNER) JOIN : Returns records that have matching values in both tables
  - Example : SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate FROM Orders a ,Customers b Where a.CustomerID = b.CustomerID;
- LEFT (OUTER) JOIN : Return all records from the left table, and the matched records from the right table
  - Example : SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate FROM Orders a ,Customers b Where a.CustomerID *= b.CustomerID;
- RIGHT (OUTER) JOIN : Return all records from the right table, and the matched records from the left table
  - Example : SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate FROM Orders a ,Customers b Where a.CustomerID =* b.CustomerID;
- FULL (OUTER) JOIN : Return all records when there is a match in either left or right table
  - Example : SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate FROM Orders a ,Customers b Where a.CustomerID *=* b.CustomerID;
- SELF JOIN : is join table with it self
  - Example : SELECT A.CustomerName , B.CustomerName, A.City FROM Customers A, Customers B
    Where A.CustomerID <> B.CustomerID AND A.City = B.City ORDER BY A.City;

Oracle Basic Operators - Lesson 4

Operators:
- SQL Arithmetic Operators :
  - + : Addition of any Different values
    - Example : Select 7 + 3
  - – : Subtraction of any Different values
    - Example : Select 7 – 3
  - * : Multiply of any Different values
    - Example : Select 7 * 3
  - / : Divide of any Different values
    - Example : Select 7 / 3
  - % : Modulo of any Different values
    - Example : Select 3 % 45
- SQL Bitwise Operators :
  - & : Bitwise AND
  - | : Bitwise OR
  - ^ : Bitwise exclusive OR
- SQL Comparison Operators :
  - = : Equal to Operator
  - > : Greater then Operator
  - < : Less then Operator
  - >= : Grater then Equal to Operator
  - <= : Less then Equal to Operator
  - <> : != : Not equal to Operator
- SQL Compound Operators :
  - += : Add equals
  - -+ : subtract equals
  - *= : Multiply equals
  - /= : Divide equals
  - %= : Modulo equals
  - &= : Bitwise AND equals
  - ^-= : Bitwise exclusive equals
  - |*= : Bitwise OR equals
- SQL Logical Operators :
  - All : TRUE if all the sub–query values match the condition
```
 SELECT * FROM Docs WHERE price > ALL (SELECT Price FROM Docs WHERE Price > 500);
```
  - AND : TRUE if all the conditions separated by AND is TRUE
```
 SELECT * FROM City WHERE City = "Pakistan" AND Country = "Quetta";
```
  - ANY : TRUE if any of the sub-query values match the condition
```
 SELECT * FROM Docs WHERE Price > ANY (SELECT Price FROM Docs WHERE Price > 50);
```
  - BETWEEN : TRUE if the operand is within the range of comparisons
```
 SELECT * FROM Docs WHERE Price BETWEEN 50 AND 60;
```
  - EXISTS : TRUE if the sub-query returns one or more records
```
 SELECT * FROM Docs WHERE EXISTS (SELECT Price FROM Docs WHERE Price > 50);
```
  - IN : TRUE if the operand is equal to one of a list of expressions
```
 SELECT * FROM Docs WHERE doc IN (7,5);
```
  - LIKE : TRUE if the operand matches a pattern
```
 SELECT * FROM Docs WHERE Doc LIKE '%latter%';    -- Shows center of description
 SELECT * FROM Docs WHERE Doc LIKE '%latter';     -- Shows last of description
 SELECT * FROM Docs WHERE Doc LIKE 'latter%';     -- Shows first of description
 SELECT first_name,last_name,salary from employees where first_name like '_a%';  -- Starts from 'a'
```
  - NOT : Displays a record if the condition is NOT TRUE
```
 SELECT * FROM Docs WHERE doc NOT LIKE 'latter%';
```
  - OR : TRUE if any of the conditions separated by OR is TRUE
```
 SELECT * FROM Country WHERE City = "Pakistan" OR Country = "Quetta";
```
  - XOR :For logical XOR, use the ANY keyword and NOT ALL.
```
WHERE 5 > ANY (SELECT foo) AND NOT (5 > ALL (SELECT foo));
```
  - NAND/NOR:You can just combine Not + And / Not + Or
```
create table test( v1 bit, v2 bit ); 
insert into test values ( false, false), (false,true), (true,true), (true,false);

select v1, v2, not (v1 and v2) "nand", not (v1 or v2) "nor"
from test
```
  - SOME : TRUE if any of the sub-query values meet the condition
```
 SELECT * FROM Docs WHERE Price > SOME (SELECT Price FROM Docs WHERE Price > 20);
```

Oracle Basic Keys / Relationships - Lesson 5

Keys :

Primary Key : Primary key is uniquely identify a record in database. It can not accept null values, duplicate values. Only one Candidate Key can be Primary Key.
- Example : Create Primary Key
```
 Alter Table Person Add Constraint PK_Person Primary Key (id,LastName);
```
- Example : Drop Primary Key
```
 Alter Table Person DROP CONSTRAINT PK_Person;
```
- Example : Disable Primary Key
```
 ALTER TABLE Person DISABLE CONSTRAINT PK_Person;
```
- Example : ENABLE Primary Key
```
 ALTER TABLE Person ENABLE CONSTRAINT PK_Person;
```

Foreign Key : Foreign Key is working as a Primary key in another table. It can accept multiple null, duplicate values.

Example : Create Foreign Key

Create Foreign Key (OrderID int NOT NULL PRIMARY KEY,
OrderNumber int NOT NULL,PersonID int FOREIGN KEY REFERENCES Persons(PersonID)
);

Example : Add / Alter Foreign Key

 Alter table orders add FOREIGN KEY (PersonId) References Persons(PersonId)

Example : Drop Foreign Key

 Alter Table Orders DROP CONSTRAINT FK_PersonOrder;

Composite / Compound Key : More then 1 Primary Key is Called Composite key, multiple Primary keys in one table is also known as Composite Key.
- Example : Create Composite Key
```
 Alter Table Person Add Constraint PK_Person Primary Key (id,LastName);
```
- Example : Drop Composite Key
```
 Alter Table Person DROP CONSTRAINT PK_Person;
```
Unique Key : Unique key is to create a field / Column in database which cannot be Repeat. Each column have one value at a time if its implemented Unique key.
- Example : Create Unique Key
```
 Create table #temp(id int unique not null,name char(30),fname char(30)
```
Super Key :
- A super key is a combination of columns that uniquely identifies any row within a relational database management system (RDBMS) table.
Candidate Key :
- A candidate key is a closely related concept where the super key is reduced to the minimum number of columns required to uniquely identify each row.
Alternate Key :
- Contain one or more columns whose combined values uniquely identify every row in a table. Foreign keys – Contain one or more columns whose values match a primary or alternate key in some other table.

Relationship :

One to One Relationship :

This Relationship is defined between two tables where both table should connected with each other based on only one matching row
A row in table A can have only one matching row in table B, and vice versa.
Diagram :

Example : To create One-to-One Relationship

 CREATE TABLE Person
(
Pk_Person_Id INT IDENTITY PRIMARY KEY,
Name CHAR(100),
EmailId CHAR(100),
);

CREATE TABLE PassportDetails
(
Pk_Passport_Id INT PRIMARY KEY,
Passport_Number CHAR(100),
Fk_Person_Id INT UNIQUE FOREIGN KEY REFERENCES dbo.Person(Pk_Person_Id)
);

INSERT INTO Person VALUES ('Niraj','v.a@emails.com');
INSERT INTO Person VALUES ('Vishwanath','v.v@emails.com');
INSERT INTO Person VALUES ('Chetan','c.v@emails.com');
GO

INSERT INTO PassportDetails VALUES (101, 'C3031R33', 1);
INSERT INTO PassportDetails VALUES (102, 'VRDK5695', 2);
INSERT INTO PassportDetails VALUES (103, 'A4DEK33D', 3);
GO

SELECT * FROM Person
SELECT * FROM PassportDetails;

One to Many Relationship : This is the most commonly used type of relationship. Consider an e-commerce website:

The One-to-Many relationship is defined as a relationship between two tables where a row from one table can have multiple matching rows in another table. This relationship can be created using Primary key-Foreign key relationship.
Customers can make many orders.
Orders can contain many items.
Items can have descriptions in many languages.
Diagram :

Example : To create One to Many Relationship:

CREATE TABLE Book
(
Pk_Book_Id INT PRIMARY KEY,
Name VARCHAR(255),
ISBN VARCHAR(255)
);

CREATE TABLE Author
(
Pk_Author_Id INT PRIMARY KEY,
FullName     VARCHAR(255),
MobileNo     CHAR(10),
Fk_Book_Id   INT FOREIGN KEY REFERENCES Book(Pk_Book_Id)
);

INSERT INTO Book VALUES (1, 'Let is Snow', 'ISBN3030303');
INSERT INTO Book VALUES (2, 'Three Cups of Tea','ISBN638242');
GO

INSERT INTO Author VALUES(100,'John Green','30303',1);
INSERT INTO Author VALUES(101,'Maureen Johnson','4343',1);
INSERT INTO Author VALUES(102,'Lauren Myracle','76665',1);
INSERT INTO Author VALUES(103,'Greg Mortenson','6434',2);
INSERT INTO Author VALUES(104,'David Oliver Relin','72322',2);
GO

SELECT * FROM Book;
SELECT * FROM Author;

Create two Tables (Table A & Table B) with the Primary Key on both the tables.
Create a Foreign key in Table B which references the Primary key of Table A.

Many to Many Relationship :

In a many-to-many relationship, a row in table A can have many matching rows in table B, and vice versa.
In some cases, you may need multiple instances on both sides of the relationship. For example, each order can contain multiple items. And each item can also be in multiple orders.
For these relationships, we need to create an extra table.
Diagram:

Example:

 CREATE TABLE employee (
employee_id INTEGER PRIMARY KEY,
employee_name VARCHAR(100) NOT NULL
);

CREATE TABLE company (
company_id INTEGER PRIMARY KEY,
company_name VARCHAR(300) NOT NULL
);

CREATE TABLE company_employee (
employee_id INTEGER NOT NULL,
company_id INTEGER NOT NULL,
work_hour_start TIME NOT NULL,
work_hour_end TIME NOT NULL,
FOREIGN KEY (employee_id) REFERENCES employee (employee_id) ON DELETE RESTRICT ON UPDATE CASCADE,
FOREIGN KEY (company_id) REFERENCES company (company_id) ON DELETE RESTRICT ON UPDATE CASCADE,
PRIMARY KEY (employee_id, company_id, work_hour_start, work_hour_end)
);

Oracle Basic Indexes - Lesson 6

Oracle Basic Methods () - Lesson 7

LEFT() : the LEFT() function returns the left most characters from a string. The number of characters returned is determined by the second argument.

LEFT(str,len)

Where str is the string that contains the sub-string you need to return, and len is the number of characters from the left you want returned.

Example :

1 - SELECT LEFT('Forest', 2) AS Result;
 Result = Fo

2 - SELECT ArtistName AS Original, 
    LEFT(ArtistName, 3) AS Modified
FROM Artists
LIMIT 5;

Result = 
Original         | Modified 
-----------------------------
Iron Maiden      | Iro
AC/DC            | AC/
Allan Holdsworth | All
Buddy Rich       | Bud
Devin Townsend   | Dev

3 - SELECT LEFT('Forest', NULL) AS Result;
Result = NULL

RIGHT(): the Right() function returns the right most characters from a string. The number of characters returned is determined by the second argument.

SELECT RIGHT('Forest', 2) AS Result;
Result =  st

SELECT RIGHT('Forest ', 2) AS Result;
Result = t

SELECT RIGHT(TRIM('Forest '), 2) AS Result;
Result = st

SELECT RIGHT(NULL, 5) AS Result;
Result = NULL

UPPER() : Use to show all characters in Upper case :

SELECT UPPER('usman') AS Name,
UPPER('UsMaN') AS Name 
Result = USMAN,USMAN

LOWER() : Use to show all characters in Lower case :

SELECT Lower('usman') AS Name,
Lower('UsMaN') AS Name
Result = usman,usman

ISNULL() : This Function is use to replace NULL value to Given Value:

SELECT ISNULL('Usman','Name Not Found')AS 'NAME'  
SELECT ISNULL(NULL,'Name Not Found')AS 'NAME'
Result:
 NAME           
 -------------- 
 Name Not Found

CREATE TABLE #usm(
NAME CHAR(30) NULL,
FNAME CHAR(30) NULL,
)
INSERT #USM SELECT 'USMAN','ARSHAD'
INSERT #USM SELECT '','MUGHAL'
INSERT #USM SELECT NULL,'MUGHAL'

SELECT ISNULL(NAME,'NO NAME EXISTS'),FNAME FROM #USM
Result :
 NAME                   FNAME                          
 --------------         ------------------------------ 
 USMAN                  ARSHAD                         
                        NO NAME                        
 NO NAME EXISTS         NO NAME

IDENTITY() : Set Serial number / identity Column in Select Query using temp table
```
SELECT ID=identity(5), * INTO #temp FROM Employee
SELECT * FROM #temp
```
GETDATE() : Use to Get Existing Date and Time.
```
Example :
SELECT GETDATE()
```
DATEPART()
DATEADD()
HEXTOINT() : This function is use to Convert HEXA value to a INT Value
```
Example :
SELECT HEXTOINT('F')
Result :
15
```
INTTOHEX() : This function is use to Convert INT value to HEXA Value
```
Example :
SELECT INTTOHEX(15)
Result :
F
```
AVG() : An aggregate function, finds the average of the values in a column. avg can only be used on numeric (integer, floating point, or money) datatypes. Null values are ignored in calculating averages.
```
Example :
SELECT AVG(Salary) from EmpSalary
Result :
94411878.94987359560156
```
SUSER_ID() : Returns the server user’s ID number from the syslogins table
```
Example :
SELECT SUSER_ID()
Result :
6480
```
SUSER_NAME() : Returns the server user’s NAME number from the syslogins table
```
Example :
SELECT SUSER_NAME()
Result :
usmanmughal
```

DATALENGTH() : Returns the length of expression in bytes. datalength finds the actual length of the data stored in each row. datalength is useful on varchar, univarhcar, varbinary, text and image datatypes, since these datatypes can store variable lengths (and do not store trailing blanks).

Example :
SELECT DATALENGTH(UserName) AS Username_Length,UserName from User
Result :
Username_Length      UserName             
 ------------------  -------------------- 
 13                  usman@abc.com        
 20                  ssss@aaaaaaaaaaa.com 
 20                  fffff@abcabcabca.com 
 20                  zzzzz@abcabcabca.com

HOST_NAME() : a system function, returns the current host computer name of the client process (not the Server process)
```
Example :
SELECT HOST_NAME()
Result :
HOT11111
```
HOST_ID() : Returns the client computer’s operating system process ID for the current Adaptive Server client
```
Example :
SELECT HOST_ID()
Result :
108978
```
ASEHOSTNAME() : Returns the physical or virtual host on which Adaptive Server is running.Only can run by sa role
```
Example :
SELECT ASEHOSTNAME()
Result :
DATAHOST
```

DB_NAME() : Return you DataBase Name

Example :
SELECT DB_NAME()
Result :
MyDataBase

Oracle Basic - Problems

how to check last database Table updated in sybase

Example :
SELECT name,id,crdate,expdate FROM sysobjects WHERE type = 'U' and name='Employee'

How to get only first name from full name in sql

Example :
Declare @FirstName CHAR(30),@LastName CHAR(30)
SELECT @FirstName = SUBSTRING(Title, 1, CHARINDEX(' ', Title) - 1),
@LastName = SUBSTRING(Title, CHARINDEX(' ', Title) + 1, LEN(Title) - CHARINDEX(' ', Title))
FROM table WHERE id = 6
SELECT @FirstName,@LastName

How to check ShowPlan and fmtOnly on Database:

Example :
Set Showplan on
go
Set fmtonly on
go
Exec ProcedureName '2-Feb-2015','28-Feb-2015','Processed'
go
Set showplan off
go
Set fmtonly off
go

Get list of stored procedures using a particular table column

Select distinct sysobjects.name , case 
 when sysobjects.type = 'TR' then 'TRIGGER' 
 when sysobjects.type = 'P' then 'PROCEDURE' 
 when sysobjects.type = 'V' then 'VIEW' 
 else 'UNKNOWN' end type
from sysobjects inner join syscomments
on sysobjects.id = syscomments.id
where syscomments.text like '%pan%'

Remove the first characters of a specific column in a table or Value?

select RIGHT('usman', LEN('usman') - 1)

How do I check if a SQL Server text column is empty?

SELECT * FROM tablename WHERE columnname not LIKE  ' '

Leave a Reply Cancel reply