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 subquery 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 :
    • 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
      • 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  ' '