Data Base and SQL Notes

 Data Base and SQL Notes

 
 

SQL Interview Que.

SQL Functions:


  • SQL has many built-in functions for performing calculations on data.
  • Modifications of DATES and CHARACTERS to Display.
  • DML Operations using Functions.


The Different SQL Functions are following bellow.

What is LOWER Function in SQL?

  • Convert all characters of a string to lower case.
  • It Returns same data type of the string(i.e, char or varchar2).
Example:

SELECT 'TECH BLOG' String,LOWER('TECH BLOG') lower FROM dual;

STRING LOWER
TECH BLOG tech blog

SELECT Ename, LOWER(Ename) Ename,deptno FROM emp WHERE deptno=10;

ENAME LOWER DEPTNO
KING king 10
CLARK clark 10
MILLER miller 10

What is UPPER Function in SQL?

  • Convert all characters of a string to lower case.
  • It Returns same data type of the string(i.e, char or varchar2).
Example:

SELECT 'tech blog' String,UPPER('tech blog') Upper  FROM dual;

STRING UPPPER
tech blog TECH BLOG

SELECT Itemname,UPPER(Itemname)UPPER,Itemcost FROM Item;

ITEMNAME UPPER ITEMCOST
samsung SAMSUNG 2000
motorola MOTOROLA 4000
nokia NOKIA 3500

What is INITCAP Function  in SQL?

  • Convert the first character of a string to Upper case.
  • It Returns same data type of the string(i.e, char or varchar2).
Example:
SELECT 'techblog' String,INITCAP('techblog') INITCAP  FROM dual;
STRING INITCAP
techblog Techblog
SELECT Ename,INITCAP(Ename) INITCAP,deptno  FROM Emp 
where deptno=20;

ENAME INITCAP DEPTNO
JONES Jones 20
SCOTT Scott 20
FORD Ford 20
SMITH Smith 20
ADAMS Adams 20

What is CONCAT Function  in SQL?

  • Concat Function is used to concatenate strings together.
Example:

SELECT 'Tech'  string1,'Blog' string2,CONCAT('Tech','Blog') concat FROM dual;

STRING1 STRING2 CONCAT
Tech Blog TechBlog

SELECT Ename, Job,CONCAT(ENAME,JOB) concat FROM emp 
WHERE deptno=10;

ENAME JOB CONCAT
KING PRESIDENT KINGPRESIDENT
CLARK MANAGER CLARKMANAGER
MILLER CLERK MILLERCLERK


What is WHERE Clause in SQL?


The WHERE Clause explanation is following below,

  • WHERE clause is used to filter the results. 
  • WHERE clause is used to specify that only certain rows of the table are displayed,based on the criteria described in that Where clause
  • Apply conditions in a SELECT, INSERT, UPDATE, or DELETE statement.

Syntax:
WHERE Conditions;

Here, Conditions must be met for records to be selected.

Example:

SELECT ename,sal,job,deptno FROM emp WHERE sal=3000;


ENAME SAL JOB DEPTNO
SCOTT 3000 ANALYST 20
FORD 3000 ANALYST 20

  • Here,Filtering  the records who are getting 3000 salary.

What is ORDER BY Clause in SQL?


The ORDER BY Clause explanation is following below,
  • Sort the records in the result set for a SELECT statement.
  • ORDER BY is an optional clause which will allow you to display the results of your query in a sorted order(i.e, Ascending or Descending order).

Example:

SELECT ename,sal,job,deptno FROM emp WHERE deptno=10 
ORDER BY   ename desc;

ENAME SAL JOB DEPTNO
MILLER 1300 CLERK 10
KING 5000 PRESIDENT 10
CLARK 2450 MANAGER 10
  • Here,arranging the records in descending order based upon ename.



What is LIKE Operator in SQL?


LIKE Operator:

  • To Search for a specified pattern in a column.
  • To compare a value to similar values wildcard operators.
  • Wildcards are %,_.
  • Negation of this operator is NOT LIKE.
Example:

SELECT Ename,Sal,Job,Deptno FROM Emp  WHERE ENAME LIKE '%K%';

ENAMESALJOBDEPTNO
KING5000PRESIDENT10
BLAKE2850MANAGER30
CLARK2450MANAGER10
SELECT Ename,Sal,Job,Deptno FROM Emp  WHERE ENAME  LIKE 'A_%';
ENAMESALJOBDEPTNO
ALLEN1600SALESMAN30
ADAMS1100CLERK20
NOT LIKE operator:
  • Negation of the LIKE Operator.
Example:

SELECT Ename,Sal,Job,Deptno FROM Emp  WHERE ENAME NOT LIKE '%A%';


ENAMESALJOBDEPTNO
KING5000PRESIDENT10
JONES2975MANAGER20
SCOTT3000ANALYST20
FORD3000ANALYST20
SMITH800CLERK20
TURNER1500SALESMAN30
MILLER1300CLERK10






What is IN Operator in SQL?


IN Operator:
  • This Operator Used for to test the Values in a Specified list.
  • It is used upon any data type.
  • Negation of this operator is NOT IN.

Example:

SELECT Ename,Sal,Job,Deptno FROM Emp  WHERE Sal IN(3000,5000);
ENAMESALJOBDEPTNO
KING5000PRESIDENT10
SCOTT3000ANALYST20
FORD3000ANALYST20
SELECT Ename,Sal,Job,Deptno FROM Emp  WHERE ENAME IN('KING','ALLEN');
ENAMESALJOBDEPTNO
KING5000PRESIDENT10
ALLEN1600SALESMAN30

NOT IN Operator:

  • Negation of the IN Operator.
Example:

SELECT Ename,Sal,Job,Deptno FROM Emp  WHERE Sal 
NOT IN(3000,5000,1250,1500,1400,1300,1200,1100,2975);

ENAMESALJOBDEPTNO
BLAKE2850MANAGER30
CLARK2450MANAGER10
SMITH800CLERK20
ALLEN1600SALESMAN30
JAMES950CLERK30

What is BETWEEN Operator in SQL?


BETWEEN Operator:

  • This Operator Used for Display the rows based upon the Range Values.
  • Limit value should be declared first.
Example:

SELECT Ename,Sal,Job,Deptno FROM Emp 
WHERE Sal BETWEEN 1000 and 2000;

ENAME SAL JOB DEPTNO
ALLEN 1600 SALESMAN 30
WARD 1250 SALESMAN 30
MARTIN 1250 SALESMAN 30
TURNER 1500 SALESMAN 30
ADAMS 1100 CLERK 20
MILLER 1300 CLERK 10


What are Operators in SQL?

The  SQL Operators are following below.

Comparison Operators:

Comparison Operators are using in Such Conditions that One Expression Compare with Another.

 The following comparison Operators are
  • Equality Operator -> = 
  • Not Equality Operator -> !=
  • Greater than Operator -> >
  • Less than Operator ->  <
  • Greater Than or Equal to Operator ->  >=
  • Less than Or Equal to Operator ->  <=

Here, Some Examples for Comparison Operators

 1) SELECT Ename,Sal,Job,Deptno FROM Emp WHERE deptno=20;
ENAME SAL JOB
DEPTNO
JONES 2975
MANAGER
 20
SCOTT 3000
ANALYST
 20
FORD 3000
ANALYST
 20
SMITH 800
CLERK
 20
ADAMS 1100
CLERK
 20
  • It Returns that employees who are in 20 department.
2) SELECT Ename,Sal,Job,Deptno FROM emp where deptno !=20;
ENAME SAL JOB
DEPTNO
KING 5000 PRESIDENT 10
BLAKE 2850 MANAGER 30
CLARK 2450 MANAGER 10
ALLEN 1600 SALESMAN 30
WARD 1250 SALESMAN 30
MARTIN 1250 SALESMAN 30
TURNER 1500 SALESMAN 30
JAMES 950 CLERK 30
MILLER 1300 CLERK 10
  • It Returns that employees who are not  in 20 department.
3) SELECT Ename,Sal,Job,Deptno FROM emp where Sal>2985;
ENAME SAL JOB
DEPTNO
KING 5000 PRESIDENT 10
SCOTT 3000 ANALYST 20
FORD 3000 ANALYST 20
  • It Returns that employees,Which Salary more than 2985.
4)SELECT Ename,Sal,Job,Deptno FROM emp where Sal<1000;
ENAME SAL JOB DEPTNO
SMITH 800 CLERK 20
JAMES 950 CLERK 30
  • It Returns that employees, which Salary less than 1000.
5)SELECT Ename,Sal,Job,Deptno FROM emp where Sal>=2975;
ENAME SAL JOB DEPTNO
KING 5000 PRESIDENT 10
JONES 2975 MANAGER 20
SCOTT 3000 ANALYST 20
FORD 3000 ANALYST 20


  • It Returns that employees, which Salary Greater than or equal to 2975.

    •  6)SELECT Ename,Sal,Job,Deptno FROM emp where Sal<=1100;

    ENAME SAL JOB DEPTNO
    SMITH 800 CLERK 20
    ADAMS 1100 CLERK 20
    JAMES 950 CLERK 30
    • It Returns that employees, which Salary less than or equal to 1100.

    Logical Operators:


     Logical Operators are using for compare two conditions at a time to determine whether  a row can be selected for the output.

     Logical Operators are AND,OR,NOT.

     AND Operator:
    For a row to be selected all Specified Conditions must be True.
    Example:

     SELECT Ename,Sal,Job,Deptno FROM Emp 
    Where Deptno=20 AND Job='CLERK';
    ENAME SAL JOB DEPTNO
    SMITH 800 CLERK 20
    ADAMS 1100 CLERK 20


    OR Operator:
    For a row to be selected at least one of the conditions must be true.

     Example:

     SELECT Ename,Sal,Job,Deptno FROM Emp 
    Where Deptno=20 OR Job='CLERK';
    ENAME SAL JOB DEPTNO
    JONES 2975 MANAGER 20
    SCOTT 3000 ANALYST 20
    FORD 3000 ANALYST 20
    SMITH 800 CLERK 20
    ADAMS 1100 CLERK 20
    JAMES 950 CLERK 30
    MILLER 1300 CLERK 10

    NOT Operator:
    For a row to be selected the Specified Conditions must be False.

     Example:

     SELECT Ename,Sal,Job,Deptno FROM Emp Where NOT JOB='CLERK' AND NOT JOB='MANAGER';


    ENAME SAL JOB DEPTNO
    KING 5000 PRESIDENT 10
    SCOTT 3000 ANALYST 20
    FORD 3000 ANALYST 20
    ALLEN 1600 SALESMAN 30
    WARD 1250 SALESMAN 30
    MARTIN 1250 SALESMAN 30
    TURNER 1500 SALESMAN 30





    What are SQL DataTypes?

    Mainly we are using following data types.Those are 
    1)CHAR
    2)VARCHAR/VARCHAR2
    3)NUMBER
    4)BLOB/CLOB
    Explanation is following below,

    1)CHAR:

    CHAR should be used for storing fix length character strings. String values will be space/blank padded before stored on disk. If this type is used to store variable length strings, it will waste a lot of disk space.

    2)VARCHAR/VARCHAR2:
    VARCHAR behaves exactly the same as VARCHAR2. However, this type should not be used as it is reserved for future usage.
    VARCHAR2 is used to store variable length character strings. The string value's length will be stored on disk with the value itself.
    3)NUMBER:

    Oracle provides the NUMBER data type to store integers (negative, positive, floating) of up to 38 digits of precision. The NUMBER data type can store numbers.

    4)BLOB/CLOB:
    BLOB : Variable-length binary large object string that can be up to 2GB (2,147,483,647) long. Primarily intended to hold non-traditional data, such as voice or mixed media. BLOB strings are not associated with a character set, as with FOR BIT DATA strings.

    CLOB : Variable-length character large object string that can be up to 2GB (2,147,483,647) long. A CLOB can store single-byte character strings or multibyte, character-based data. A CLOB is considered a character string.


    How to Create Table?


     Create Table TABLE NAME
    (
      column1 data type(Size),
      column2 data type(Size),
      column3 data type(Size),
      column4 data type(Size)
    );

     Note:- 

    • Table name Should be Unique i.e, Not exist in the database
    • Table name should be less than or equal to  30 characters only.

    Example:- 

    Create table SHOPPING
    (
      website varchar2(20),
      Item varchar2(20),
      HighCost Number(10),
      Lowcost Number(10)
    );

    Table Basics:

  • A relational database system contains one or more objects called tables.
  • The data or information for the database are stored in these tables.
  • Tables are uniquely identified by their names
  • Columns contain the column name, data type, and any other attributes for the column.
  • Rows contain the records or data for the columns. Here is a sample table called "SHOPPING".
    • WEBSITE, ITEM, high cost, and low cost are the columns. The rows contain the data for this table:
    SHOPPING
    WEBSITE ITEM High COST Low COST
    Flipkart Motorola 6999 5999
    Amazon Samsung 40000 7000
    Amazon HTC 42000 12000
    SnapDeal Nokia 45000 1600
    Flipkart IPhone 50000 20000

    What is SQL?

    SQL (pronounced "ess-que-el") stands for Structured Query Language. SQL is used to communicate with a database. According to ANSI (American National Standards Institute), it is the standard language for relational database management systems. 
    SQL statements are used to perform tasks such as update data on a database, or retrieve data from a database.
    Some common relational database management systems that use SQL are: Oracle, Sybase, Microsoft SQL Server, Access, Ingres, etc.

    Friday, 12 June 2015

    What is RDBMS? 

    RDBMS stands for Relational Database Management System. It organizes data into related rows and columns.

    Features:-
    • It stores data in tables.
    • Tables have rows and column. 
    • These tables are created using SQL. 
    • And data from these tables are also retrieved us

    What is DBMS?


     A database management system (DBMS) is a system that stores and retrieves information in a database. It is used to help you organize your data according to a subject, so that it is easy to track and verify your data, and you can store information about how different subjects are related, so that it makes it easy to bring related data together.

    What is Database?

    A database is a collection of data that is related to a particular topic or purpose. As an example, employee records in a filing cabinet, a collection of sales leads in a notebook, are examples of collections of data or databases.


     
     
     

     

    No comments:

    Post a Comment

    Subscribe to: Posts (Atom)