BASIC SQL

SQL

It is known as structural query language. Database understands only query language. Using the sql we can communicate to oracle server. The Oracle Server supports ANSI standard SQL. SQL is the language used to communicate with the server to access, manipulate, and control data.

CLAUSES 

1.       Select

2.       From

3.       Where

4.       Group by

5.       Having

6.       Order by

Note:

·         We cannot interchange the position but we can switch the position.

·         Semicolon (;) is used to terminate the statement.

·         Row is case sensitive but column is not case sensitive.

·         Using comma (,) we can use many columns.

·         We don’t write db name in sql in some cases because we login to DB before execute the statement.

·         With the Oracle Server, you can store and manage information by using the SQL language and PL/SQL engine.

1.       SELECT

 A SELECT statement retrieves information from the database. Using a SELECT statement, you can do the following:

• Projection: choose the columns in a table that you want returned by your query.

• Selection: choose the rows in a table that you want returned by a query.

• Joining: You can bring data together that is stored in different tables.

              SYNTEX:

                   SELECT *| {[DISTINCT] column|expression [alias],...} FROM table;

              EXAMPLE:

·         Select name from emp;

·         Select * from emp;

·         Select name , salary from emp;

·         Select distinct name ,salary-100 from emp;

Note

·         Distinct is used for the different value rather than similar value. For example if there is table abhi having name column and there is two name similar like pawan it going to show only one pawan if we using distinct before column.

2.       From:

         FROM identifies which table. A FROM clause, which specifies the table containing the columns listed in the SELECT clause in the syntax.

              EXAMPLE:

·         Select name from emp;

·         Select * from emp;

3.       Where

          Restrict the rows returned by using the WHERE clause. A WHERE clause contains a condition that must be met, and it directly follows the FROM clause. If the condition is true, the row meeting the condition is returned. The WHERE clause can compare values in columns, literal values, arithmetic expressions, or functions. It consists of three elements:

• Column name

• Comparison condition

• Column name, constant, or list of value.

              Syntax:

                          SELECT *|{[DISTINCT] column|expression [alias],...} FROM table [WHERE condition(s)];

             Example:

·         Select * from emp where deptno = 10;

·         Select name, dept from emp where last_name = ‘Kumar’;

·         Select distinct name ,salary from emp where salary<=1000;

4.       Group by

Divide rows in a table into smaller groups by using the GROUP BY clause. You can then use        the group functions to return summary information for each group.

SYNTEX:

SELECT column, group_function (column) FROM table [WHERE condition] [GROUP BY group_by_expression] [ORDER BY column];

EXAMPLE:

·         SELECT department_id, AVG(salary) FROM employees GROUP BY department_id ;

·         SELECT department_id dept_id, job_id, SUM(salary) FROM employees GROUP BY department_id, job_id ;

Note:

·         If you include a group function in a SELECT clause, you cannot select individual results as well, unless the individual column appears in the GROUP BY clause. You receive an error message if you fail to include the column list in the GROUP BY clause.

·         Using a WHERE clause, you can exclude rows before dividing them into groups.

·         You must include the columns in the GROUP BY clause.

·         You cannot use a column alias in the GROUP BY clause.

·         By default, rows are sorted by ascending order of the columns included in the GROUP BY list.

·         You can override this by using the ORDER BY clause.

5.       Having

You use the HAVING clause to specify which groups are to be displayed, and thus, you further restrict the groups on the basis of aggregate information.

SYNTEX:

SELECT column, group_function FROM table [WHERE condition]

[GROUP BY group_by_expression] [HAVING group_condition] [ORDER BY column];

EXAMPLE:

·         SELECT department_id, MAX(salary) FROM employees GROUP BY department_id HAVING MAX(salary)>10000 ;

·         SELECT job_id, SUM(salary) PAYROLL FROM employees WHERE job_id NOT LIKE ’%REP%’ GROUP BY job_id HAVING SUM(salary) > 13000 ORDER BY SUM(salary);

Note:

We Use the HAVING clause to restrict groups:

1. Rows are grouped.

2. The group function is applied.

                             3. Groups matching the HAVING clause are displayed.

6.       Order by

 It used to sort rows with the ORDER BY clause. The ORDER BY clause comes last in the SELECT statement. It must be the last clause of the SQL statement. You can specify an expression, or an alias, or column position as the sort condition.

·         ASC: ascending order, default

·         DESC: descending order

              SYNTAX:

              SELECT *| {[DISTINCT] column|expression [alias],...} FROM table ORDER BY column|alias;

             EXAMPLE:

·         SELECT last_name, salary, hire_date FROM employees ORDER BY hire_date ;

·         Select * from emp order by salary desc;

·         Select name, salary, deptid from emp where salary >= 1000 order by deptid, salary asc;

Note

·         ASC is default in query.

·         Numeric values are displayed with the lowest values first—for example, 1–999.

·          Date values are displayed with the earliest value first—for example, 01-JAN-92 before 01-JAN-95.

·         Character values are displayed in alphabetical order—for example, A first and Z last.

·         Null values are displayed last for ascending sequences and first for descending sequences.

OPERATOR:

·         ARITHMETIC OPERATOR

1.       You can use arithmetic operators in any clause of a SQL statement except in the FROM clause.

2.        Arithmetic operator are +, -, *, /

3.       Bracket having high priority.

4.       Example:

Select last_name, salary, 12*salary+100 from emp;

·         LOGIC OPERATOR

1.       Logic operator are “AND ” “OR” “NOT”

2.       Select * from emp where salary =3000 and age =21;

·         COMPARISON OPERATOR

1.       Comparison operators are >, <, >=, <=, !=, =

2.       Select * from emp where sal>=1000;

·         CONCATENATION OPERATOR

1.       Concatenation operator are || is used to connect two column or strings

2.       Select name ,||’is’||mgr from emp;

·         SET OPERATOR

1.       Union (value not repeat)

2.       Union all (value repeat )

3.       Intersect (common value)

4.       Minus (not common in both)

5.       Example:

Select deptno from emp union/union all/ minus/intersect select deptno from emp2;

GENERAL OPERATOR:

·         IN

Fetch multiple values from the same column.

Example:

Select * from emp where sal in (2000, 3000, 4000);

·         BETWEEN

It used to find the value range from higher to the lower.

Example

Select * from emp where sal between 2000 and 6000;

·         LIKE

Search for the similar character.

Select ename from emp where ename like %K%;

‘%S’ its meaning ‘S’ at last;

‘S%’ its meaning ‘S’ at first;

‘%S%’ its meaning ‘S’ at any place;

For particular place S____A% start with S and end with A.

·         NULL

It used to find the null values.

Example:

Select * fro m emp where sal is NULL;

CATEGORIES OF SQL COMMAND

·         DDL (Data definition language used at column and table level auto commit )

1.       Create (Example: create table abc (name varchar (5)) ;)

2.       Drop     (Example: drop table abc; alter table abc drop column age ;)

3.       Rename (Example: alter table abc rename column sal to salary; rename table abc to xyz;)

4.       Truncate (Example: truncate table abc;)

5.       Alter (Example: alter table abc rename abcd;)

6.       Add (Example: alter table abc add column age number (5);)

7.       Modify (Example: )

·         DML (Data manipulation language)

1.       Update (Example: update emp set deptno=50 where sal=1000;)

2.       Insert (insert into abs values (‘kunal’ 1000, ‘m’);)

3.       Delete (Example: delete from xyz;)

4.       Merge (Example: see below)

·         DCL (Data control language)

1.       Grant (Example: grant connect, resource to abhi  )

2.       Revoke (Example: revoke connect to abhi)

3.       Commit (commit)

4.       Rollback (rollback)

DATA TYPE:

·         Character

1.       Char

·         Reserve memory

·         Store 1-2000 bytes

·         Ignore white spaces(don’t count whitespace)

2.       Varchar

·         Release memory

·         Store 2-4000 bytes

·         Don’t ignore white spaces

·         Number

o   It store 1-38 byte

·         Date

o   No specific size

FUNCTION:

1.       Single Row

·         Character manipulation 

1.       ASCII()

2.       CHR()

3.       LTRIM()

4.       RTRIM()

5.       LPAD()

6.       RPAD()

7.       SUBSTR()

8.       INSTR()

9.       LENGTH()

10.   CONCAT()

·         Case manipulation

1.       UPPER()

2.       LOWER()

3.       INITCAP()

2.       Multiple Row

1.       SUM()

2.       AVERAGE()

3.       COUNT()

4.       MIN()

5.       MAX()

3.       Number Function:

1.       SQRT()

2.       POWER()

3.       ABS()

4.       ROUND()

5.       TRUNC()

6.       MOD()

7.       SIGN()

4.       Date Function:

1.       SYSDATE()

2.       CURRENT_TIMESTAMP()

3.       LAST_DAY()

4.       NEXT_DAY()

5.       ADD_MONTHS()

6.       EXTRACT()

7.       MONTHS_BETWEEN()

JOINS:

When data from more than one table in the database is required, a join condition is used. Rows in one table can be joined to rows in another table according to common values existing in corresponding columns, that is, usually primary and foreign key columns.

1.       Cartesian product or cross join

Everything multiplied

Example:

Select ename, dname from emp, dept;

2.       Equijoins

               Equijoins are also called simple joins or inner joins.

               Example:

 SELECT employees.employee_id, employees.last_name, employees.department_id,      departments.department_id, departments.location_id FROM employees, departments

WHERE employees.department_id = departments.department_id

3.       Non equijoins

Example:

SELECT e.last_name, e.salary, j.grade_level FROM employees e, job_grades j WHERE e.salary BETWEEN j.lowest_sal AND j.highest_sal;

4.       Self joins

Example:

SELECT worker.last_name || ’ works for ’ || manager.last_name FROM employees worker, employees manager WHERE worker.manager_id = manager.employee_id ;

5.       Outer joins

Example:

SELECT table1.column, table2.column

FROM table1, table2 WHERE table1.column (+) = table2.column;

DATABASE OBJECTS:

1.       TABLE

It stores data. Tables can be created at any time, even while users are using the database. You do not need to specify the size of any table. The size is ultimately defined by the amount of space allocated to the database as a whole. Table structure can be modified online.

User Tables:

Are a collection of tables created and maintained by the user

Contain user information

Data Dictionary:

Is a collection of tables created and maintained by the Oracle Server

Contain database information

All data dictionary tables are owned by the SYS user.

Syntax:

     CREATE TABLE [schema.]table(column datatype [DEFAULT expr][, ...]);

Example:

Create table abc (name varchar (5));

Select * from userb.employee;

See the names of tables owned by the user.

SELECT table_name FROM user_tables ;

View distinct object types owned by the user.

SELECT DISTINCT object_type FROM user_objects ;

View tables, views, synonyms, and sequences owned by the user.

SELECT * FROM user_catalog ;

2.       VIEW

It is like a window. It is like a snap shot of table. It is subset of one or more than one table. It doesn’t store the memory. By default it is in read write mode. 

Example:

Create view a as select * from emp;

Select * from a;

Create view b as select e.name, d.deptno from emp e, dept d where e.deptno = d.deptno;

Select * from b;

Create view b as select e.name, d.deptno from emp e, dept d where e.deptno = d.deptno with read only;

Create or replace view b as select ename, job from emp;

Force view

It is view of the non existing table.

Create force view kunal as select * from bank;

Drop view s;

3.       INDEX

We used index mainly for the faster search. It has two type bitmap and btree. Btree is default. Btree has high cardinality but bitmap have low cardinality and has replication like male female. If we going to update using btree, it will easily update because it update only rows. Update store less memory.

Example

Create index a on emp (name);

 Create bitmap index b on emp (deptno); 

4.       SYNONYMS

It is database object having the similar meaning of the database object. It can be public as well as the private. Generally private is default.

Example:

Create synonyms s for scott.emp.ename;

Select * from s;

Drop synonyms s;  

5.       SEQUENCE

It is a database object that will generate the sequence number. It has two function nextval() and currval(). If sequence once created we can’t create sequence once more. Drop it and create once more.

Example:

Create sequence sq start with 1 increment by 1 maxvalue 100 cycle;

Update emp set srno = sq.nextval;

Select sq.nextval from dual;

Select sq.currval from dual;

CONSTRAINTS:

1.       NOT NULL

It used to check the value should not null while inserting. It is two type table label and column label. It is only constraints that we can modify.

Example:

Create table dark (name varchar (5) constraint y not null);çcolumn label

Create table dark (name varchar (5), constraint y not null);çTable label

2.       CHECK

It used to check the condition it is also table label as well as column label

Example:

Create table xyz (name varchar (4), age number (5) constraints x check (age>18));çcolumn

Create table xyz (name varchar (4), age number (5), sal number (10) constraints x check (age>18 and sal>1000));ç table label

3.       PRIMARY KEY

It is unique but can’t be null. It can be used only once in the table.

Example:

Create table xxx (name varchar (5) account_no number (10) constraints a primary key);

4.       UNIQUE KEY

It is unique and it can be null

Example:

Create table abhi (name varchar (5) age number (10) constraints y unique)

5.       FOREIGN KEY

It used to give the reference of the primary key.

Example:

Create table abc (name varchar (4), sal number (5), constraints x foreign key (name, sal) reference abc);

USER:

We can create user and we can provide the roles and privilege to required user on system and on object.

1.       Create user kamlesh identified by munna;

2.       Grant connect, resource, sysdba to kamlesh; (privilege on system)

3.       Grant update on scott.emp to kamlesh; (privilege on object)

4.       Grant connect, resource, sysdba to kamlesh with admin option;

5.       update on scott.emp to kamlesh with grant option;

ROLE:

It is combination of many privileges. We can give it to many users at the same time.

1.       Create role darknight;

2.       Grant resource, connect to darknight;

3.       Grant darknight to v1, v2, v3, .......v100;

MERGE:

We can do merging of two or more table in to a single table with some condition:

                Merge into abc a using (select salary, name from emp) e on (e.salary = a.salary) set when matched then update set a.name =e.name when not matched then insert (a.salary, a.name) values (e.salary, a.name);

COMPLEX QUERY:

It is combination of many query nested inside a single query.

EXAMPLE:

Select * from EMP where sal in (select sal from EMP where mgr=7698) and ename in (select ename from EMP where job in (select job from EMP where empno in (select empno from EMP where deptno in (select deptno from EMP where sal=3000 and hire_date like ‘%81%’))));

SOME TIPS:

1.       Line size:-it is total screen size. Default:80

2.       Set linesize 100

3.       Show linesize

4.       Page size :- it is length of page

5.       Show pagesize

6.       Set pagesize 30

7.       Forward slash (/) (it will give previous query)

8.       Describe statement describe the structure of a table

9.       Desc EMP;