SQL EASY TO LEARNING

          

 SQL was initially developed at IBM by Donald Chamberlin and Raymond F. Boyce in the early 1970s, initially called SEQUEL (Structured English QUEry Language), was designed to manipulate and retrieve data stored in IBM's original quasi-relational database management system.

In the late 1970s, Relational Software, Inc. (now Oracle Corporation) saw the potential of the concepts described by Codd, Chamberlin, and Boyce, and developed their own SQL-based RDBMS with aspirations of selling it to the U.S. Navy, Central Intelligence Agency, and other U.S. government agencies. In June 1979, Relational Software, Inc. introduced the first commercially available implementation of SQL, Oracle V2 (Version2) for VAX computers.

1970 -- Dr. Edgar F. "Ted" Codd of IBM is known as the father of relational databases.    He            described a relational model for databases.

1974 -- Structured Query Language appeared.

1978 -- IBM worked to develop Codd's ideas and released a product named System/R.

1986 -- IBM developed the first prototype of relational database and standardized by ANSI. The first relational database was released by Relational Software and its later becoming Oracle.

What is SQL?

SQL is a database computer language designed for the retrieval and management of data in relational database. SQL stands for Structured Query Language.

-SQL is a language that all commercial RDBMS implementations understand.

-SQL is a non-procedural language

-We would be discussing SQL with respect to oracle syntax

What is table?

The data in RDBMS is stored in database objects called tables. The table is a collection of related data entries and it consists of columns and rows.

• DDL (Data Definition Language)

 – Create

 – Alter

 – Drop

 – Truncate

• DML (Data Manipulation Language)

 – Insert

 – Update

 – Delete

 – Select

• DCL (Data Control Language)

 – Grant

 – Revoke

 – Commit

 – Rollback

SQL has three flavors of statements. The DDL, DML and DCL.

 DDL is Data Definition Language statements. Some examples:

·         CREATE - to create objects in the database

·         ALTER - alters the structure of the database

·         DROP - delete objects from the database

·         TRUNCATE - remove all records from a table, including all spaces allocated for the records are removed

·         COMMENT - add comments to the data dictionary

·         GRANT - gives user's access privileges to database

·         REVOKE - withdraw access privileges given with the GRANT command

DML is Data Manipulation Language statements. Some examples:

·         SELECT - retrieve data from the a database

·         INSERT - insert data into a table UPDATE - updates existing data within a table

·         DELETE - deletes all records from a table, the space for the records remain

·         CALL - call a PL/SQL or Java subprogram

·         EXPLAIN PLAN - explain access path to data

·         LOCK TABLE - control concurrency

 DCL is Data Control Language statements. Some examples:

·         COMMIT - save work done

·         SAVEPOINT - identify a point in a transaction to which you can later roll back

·         ROLLBACK - restore database to original since the last  COMMIT

·         SET TRANSACTION - Change transaction options like what rollback segment to use

Data types

·         Number

·         Char

·         Varchar2

·         Long

·         date

SQL supports various data types

·         Integers

·         Decimal numbers--- NUMBER, INTEGER  .

·         Number is an oracle data type. Integer is an ANSI data type. Integer is equivalent of     NUMBER (38)

·         The syntax for NUMBER is NUMBER (P, S) p is the precision and s is the scale.  P can range from 1 to 38 and s from -84 to 127

·         Floating point numbers---- FLOAT

·         Fixed length character strings---- CHAR (len)

·         Fixed length character data of length len bytes. This should be used for fixed length data.

·         Variable length character strings --- Varchar2(len)

·         Variable length character string having maximum length len bytes. We must specify the size

·         Dates-----DATE

QUERIES PRACTICE

CREATE SCHEMA

Schema is a database, Database can contain many tables.

CREATE SCHEMA softwarecompany ; 

-------------------------------------------------------------------------------------------------------------------------------

CREATE TABLE

The create table statement is used to create a new table. Here is the format of a simple create table statement.

CREATE TABLE softwarecompany.employee (

  Emp id INT NOT NULL,

  Emp name VARCHAR(45) NULL,

  Sal INT NULL,

  DOB DATE NULL,

  PRIMARY KEY (Emp id));

-------------------------------------------------------------------------------------------------------------------------------

DESC

DESC softwarecompany.employee;

 

SELECT

The select statement is used to query the database and retrieve selected data that match the criteria that you specify.

SELECT * FROM softwarecompany.employee;

-------------------------------------------------------------------------------------------------------------------------------

INSERT

Creates a record,

INSERT INTO `softwarecompany`.`employee` (`Emp id`, `Emp name`, `Sal`, `DOB`) VALUES ('111', 'prabagaran', '60000', '1988-12-26');

INSERT INTO `softwarecompany`.`employee` (`Emp id`, `Emp name`, `Sal`, `DOB`) VALUES ('112', 'akhila', '40000', '1992-04-21');

INSERT INTO `softwarecompany`.`employee` (`Emp id`, `Emp name`, `Sal`, `DOB`) VALUES ('113', 'john', '45000', '1947-01-18');

INSERT INTO `softwarecompany`.`employee` (`Emp id`, `Emp name`, `Sal`, `DOB`) VALUES ('114', 'Olivia', '35000', '1984-03-07');

INSERT INTO `softwarecompany`.`employee` (`Emp id`, `Emp name`, `Sal`, `DOB`) VALUES ('115', 'Michael', '80000', '1988-06-17');

INSERT INTO `softwarecompany`.`employee` (`Emp id`, `Emp name`, `Sal`, `DOB`) VALUES ('116', 'Daniel', '50000', '1999-05-02');

INSERT INTO `softwarecompany`.`employee` (`Emp id`, `Emp name`, `Sal`, `DOB`) VALUES ('117', 'Harper', '50000', '1990-08-15');

INSERT INTO `softwarecompany`.`employee` (`Emp id`, `Emp name`, `Sal`, `DOB`) VALUES ('118', 'Ella', '20000', '1980-12-12');

INSERT INTO `softwarecompany`.`employee` (`Emp id`, `Emp name`, `Sal`, `DOB`) VALUES ('119', 'Natalie', '30000', '1984-01-30');

INSERT INTO `softwarecompany`.`employee` (`Emp id`, `Emp name`, `Sal`, `DOB`) VALUES ('120', 'Scarlett', '100000', '1991-03-21');

-------------------------------------------------------------------------------------------------------------------------------

SELECT * FROM softwarecompany.employee;

 

-------------------------------------------------------------------------------------------------------------------------------

SELECT Empid , sal FROM softwarecompany.employee;

-------------------------------------------------------------------------------------------------------------------------------

WHERE:

SELECT * FROM softwarecompany.employee

WHERE Empid=117;

 

SELECT * FROM softwarecompany.employee

WHERE DOB='1999-05-02';

 

-------------------------------------------------------------------------------------------------------------------------------

SELECT * FROM softwarecompany.employee

WHERE SAL BETWEEN '30000' AND '40000';

 

-------------------------------------------------------------------------------------------------------------------------------

SELECT * FROM softwarecompany.employee

WHERE SAL IN(20000,30000,40000);

 

SELECT * FROM softwarecompany.employee

WHERE Empname IN('prabagaran','akhila');

 

-------------------------------------------------------------------------------------------------------------------------------

SELECT * FROM softwarecompany.employee

WHERE Empname  NOT IN('prabagaran','akhila');

 

-------------------------------------------------------------------------------------------------------------------------------

SELECT * FROM softwarecompany.employee

WHERE Empname LIKE  ' S% ';

 

SELECT * FROM softwarecompany.employee

WHERE Empname LIKE   '_a% ';

 

-------------------------------------------------------------------------------------------------------------------------------

SELECT * FROM softwarecompany.employee

WHERE Empname LIKE ‘__a% ';

 

-------------------------------------------------------------------------------------------------------------------------------

CONCATENATIONS

“||” >>----> pipe operations

“AS” >>---> Alias, give rename column heading

                        ->use for calculations

                                    ->’AS’ keyword

SELECT  Empname || Sal as EMP FROM softwarecompany.employee;

OR

SELECT concat(Empname," ",Sal)EMP FROM softwarecompany.employee;

 

-------------------------------------------------------------------------------------------------------------------------------

AND ,OR,NOT

SELECT * FROM softwarecompany.employee

WHERE Sal >=10000 AND Empname LIKE '%el%';

 

SELECT * FROM softwarecompany.employee

WHERE Sal >=40000 OR Empname LIKE '%el%';

 

-------------------------------------------------------------------------------------------------------------------------------

SELECT * FROM softwarecompany.employee

WHERE Empid NOT IN(111,112,113);

 

ORDER BY

ORDER BY is an optional clause which will allow you to display the results of your query in a sorted order (either ascending order or descending order) based on the columns that you specify to order by.

SELECT * FROM softwarecompany.employee

ORDER BY Empname ASC;

 

-------------------------------------------------------------------------------------------------------------------------------

SELECT * FROM softwarecompany.employee

ORDER BY Empname DESC;

 

-------------------------------------------------------------------------------------------------------------------------------

SELECT * FROM softwarecompany.employee

ORDER BY Sal;

 

AVG, MAX, MIN, SUM, COUNT

AVG

Returns the average value of a given column.

SELECT AVG(Sal) FROM softwarecompany.employee;

-------------------------------------------------------------------------------------------------------------------------------

MAX

Returns the largest value in a given column.

SELECT MAX(Sal)  FROM softwarecompany.employee;

-------------------------------------------------------------------------------------------------------------------------------

MIN

Returns the smallest value in a given column

SELECT MIN(Sal)  FROM softwarecompany.employee;

SUM

Returns the sum of the numeric values in a given column.

SELECT SUM(Sal) FROM softwarecompany.employee;

-------------------------------------------------------------------------------------------------------------------------------

COUNT

Returns the total number of values in a given column.

SELECT COUNT(DEPT) FROM softwarecompany.employee

WHERE DEPT='SW';

-------------------------------------------------------------------------------------------------------------------------------

UPDATE:

The update statement is used to update or change records that match a specified criteria. This is accomplished by carefully constructing a where clause.

UPDATE softwarecompany.employee

SET Sal=Sal+500

WHERE Empid=111;

 

-------------------------------------------------------------------------------------------------------------------------------

SELECT Sal+500 FROM softwarecompany.employee;

-------------------------------------------------------------------------------------------------------------------------------

ALTER TABLE

ALTER TABLE softwarecompany.employee

ADD DEPT VARCHAR(20);

 

-------------------------------------------------------------------------------------------------------------------------------

UPDATE `softwarecompany`.`employee` SET `DEPT`='SW' WHERE `Empid`='111';

UPDATE `softwarecompany`.`employee` SET `DEPT`='TE' WHERE `Empid`='112';

UPDATE `softwarecompany`.`employee` SET `DEPT`='TE' WHERE `Empid`='115';

UPDATE `softwarecompany`.`employee` SET `DEPT`='TE' WHERE `Empid`='118';

UPDATE `softwarecompany`.`employee` SET `DEPT`='SW' WHERE `Empid`='120';

UPDATE `softwarecompany`.`employee` SET `DEPT`='SW' WHERE `Empid`='114';

UPDATE `softwarecompany`.`employee` SET `DEPT`='SW' WHERE `Empid`='116';

UPDATE `softwarecompany`.`employee` SET `DEPT`='QW' WHERE `Empid`='113';

UPDATE `softwarecompany`.`employee` SET `DEPT`='WQ' WHERE `Empid`='117';

UPDATE `softwarecompany`.`employee` SET `DEPT`='WQ' WHERE `Empid`='119';

 

-------------------------------------------------------------------------------------------------------------------------------

DISTINCT

Avoid duplicate.

SELECT DISTINCT Sal FROM softwarecompany.employee;

 

-------------------------------------------------------------------------------------------------------------------------------

SELECT DISTINCT(DEPT) FROM softwarecompany.employee;

 

CREATE SECOND TABLE IN DB software company

           

CREATE TABLE `softwarecompany`.`sw` (

  `id` INT NOT NULL,

  `pos` VARCHAR(45) NULL,

  `sname` VARCHAR(45) NULL,

  PRIMARY KEY (`id`));

-------------------------------------------------------------------------------------------------------------------------------

INSERT INTO `softwarecompany`.`sw` (`id`, `pos`, `sname`) VALUES ('111', 'man', 'a');

INSERT INTO `softwarecompany`.`sw` (`id`, `pos`, `sname`) VALUES ('112', 'emp', 'b');

INSERT INTO `softwarecompany`.`sw` (`id`, `pos`, `sname`) VALUES ('113', 'emp', 'c');

INSERT INTO `softwarecompany`.`sw` (`id`, `pos`, `sname`) VALUES ('114', 'emp', 'd');

INSERT INTO `softwarecompany`.`sw` (`id`, `pos`, `sname`) VALUES ('115', 'mar', 'e');

INSERT INTO `softwarecompany`.`sw` (`id`, `pos`, `sname`) VALUES ('116', 'hr', 'f');

INSERT INTO `softwarecompany`.`sw` (`id`, `pos`, `sname`) VALUES ('117', 'eng', 'g');

INSERT INTO `softwarecompany`.`sw` (`id`, `pos`, `sname`) VALUES ('118', 'mar', 'h');

INSERT INTO `softwarecompany`.`sw` (`id`, `pos`, `sname`) VALUES ('119', 'emp', 'i');

INSERT INTO `softwarecompany`.`sw` (`id`, `pos`, `sname`) VALUES ('120', 'hr', 'j');

-------------------------------------------------------------------------------------------------------------------------------

SELECT * FROM softwarecompany.sw;

 

-------------------------------------------------------------------------------------------------------------------------------

JOIN

Data from More Than One Table by Using Joins.

SELECT * FROM softwarecompany.employee, softwarecompany.sw

 where Empid=id;

 

-------------------------------------------------------------------------------------------------------------------------------

 

 

Aliases

SELECT Empname as Name, pos as position FROM softwarecompany.employee,softwarecompany.sw

 where Empid=id;

 

-------------------------------------------------------------------------------------------------------------------------------

INNER JOIN:

SELECT Empid,Sal,pos FROM softwarecompany.employee

INNER JOIN softwarecompany.sw

 where Empid=id;

 

-------------------------------------------------------------------------------------------------------------------------------

UNION, UNION ALL:

SELECT Empname,Empid FROM softwarecompany.employee

UNION ALL

SELECT sname,pos FROM softwarecompany.sw;

 

-------------------------------------------------------------------------------------------------------------------------------

DELETE

Deletes records.

DELETE FROM softwarecompany.employee

WHERE Empid=120;

 

Try Your Self

SQL All Syntax:

SELECT

SELECT column_name,column_name
FROM table_name;

Or

SELECT * FROM table_name;

-------------------------------------------------------------------------------------------------------------------------------

SELECT DISTINCT

SELECT DISTINCT column_name,column_name
FROM table_name;

-------------------------------------------------------------------------------------------------------------------------------

WHERE

SELECT column_name,column_name
FROM table_name
WHERE column_name operator value;

-------------------------------------------------------------------------------------------------------------------------------

ORDER BY

SELECT column_name, column_name
FROM table_name
ORDER BY column_name ASC|DESC, column_name ASC|DESC;

INSERT INTO

INSERT INTO table_name
VALUES (value1, value2, value3...);

Or

INSERT INTO table_name (column1,column2,column3,...)
VALUES (value1,value2,value3,...);

-------------------------------------------------------------------------------------------------------------------------------

UPDATE

UPDATE table_name
SET column1=value1,column2=value2,...
WHERE some_column=some_value;

-------------------------------------------------------------------------------------------------------------------------------

DELETE

DELETE FROM table_name
WHERE some_column=some_value;

-------------------------------------------------------------------------------------------------------------------------------

LIKE

SELECT column_name(s)
FROM table_name
WHERE column_name LIKE pattern;

-------------------------------------------------------------------------------------------------------------------------------

IN

SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1,value2,...);

-------------------------------------------------------------------------------------------------------------------------------

BETWEEN

SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;

-------------------------------------------------------------------------------------------------------------------------------

Alias

SELECT column_name AS alias_name
FROM table_name;

Or

SELECT column_name(s)
FROM table_name AS alias_name;

-------------------------------------------------------------------------------------------------------------------------------

 

INNER JOIN

SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name=table2.column_name;

Or

SELECT column_name(s)
FROM table1
JOIN table2
ON table1.column_name=table2.column_name;

-------------------------------------------------------------------------------------------------------------------------------

LEFT JOIN

SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name=table2.column_name;

Or

SELECT column_name(s)
FROM table1
LEFT OUTER JOIN table2
ON table1.column_name=table2.column_name;

-------------------------------------------------------------------------------------------------------------------------------

RIGHT JOIN

SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name=table2.column_name;

Or

SELECT column_name(s)
FROM table1
RIGHT OUTER JOIN table2
ON table1.column_name=table2.column_name;

-------------------------------------------------------------------------------------------------------------------------------

FULL OUTER JOIN

SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2
ON table1.column_name=table2.column_name;

-------------------------------------------------------------------------------------------------------------------------------

UNION

SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;

-------------------------------------------------------------------------------------------------------------------------------

UNION ALL

SELECT column_name(s) FROM table1
UNION ALL
SELECT column_name(s) FROM table2;

-------------------------------------------------------------------------------------------------------------------------------

SELECT INTO

SELECT *
INTO newtable [IN externaldb]
FROM table1;

Or

SELECT column_name(s)
INTO newtable [IN externaldb]
FROM table1;

-------------------------------------------------------------------------------------------------------------------------------

INSERT INTO SELECT

INSERT INTO table2
SELECT * FROM table1;

Or

INSERT INTO table2
(column_name(s))
SELECT column_name(s)
FROM table1;

-------------------------------------------------------------------------------------------------------------------------------

CREATE DATABASE

CREATE DATABASE dbname;

Or

CREATE SCHEMA dbname;

-------------------------------------------------------------------------------------------------------------------------------

CREATE TABLE

CREATE TABLE table_name
(
column_name1 data_type(size),
column_name2 data_type(size),
column_name3 data_type(size),
....
);

-------------------------------------------------------------------------------------------------------------------------------

DROP

DROP INDEX index_name ON table_name;

DROP INDEX table_name.index_name;

DROP INDEX index_name;

DROP TABLE table_name;

DROP DATABASE database_name;

-------------------------------------------------------------------------------------------------------------------------------

 

TRUNCATE TABLE

TRUNCATE TABLE table_name;

-------------------------------------------------------------------------------------------------------------------------------

ALTER TABLE

ALTER TABLE table_name
ADD column_name datatype;

ALTER TABLE table_name
DROP COLUMN column_name;

ALTER TABLE table_name
MODIFY column_name datatype;

ALTER TABLE table_name
MODIFY COLUMN column_name datatype;

ALTER TABLE table_name
ALTER COLUMN column_name datatype;

-------------------------------------------------------------------------------------------------------------------------------

AUTO INCREMENT

CREATE TABLE Persons
(
ID int NOT NULL AUTO_INCREMENT,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
PRIMARY KEY (ID)
)

-------------------------------------------------------------------------------------------------------------------------------

CREATE VIEW

CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition

-------------------------------------------------------------------------------------------------------------------------------

AVG()

SELECT AVG(column_name) FROM table_name

-------------------------------------------------------------------------------------------------------------------------------

COUNT

SELECT COUNT(*) FROM table_name;

SELECT COUNT(DISTINCT column_name) FROM table_name;

-------------------------------------------------------------------------------------------------------------------------------

FIRST()

SELECT FIRST(column_name) FROM table_name;

SELECT column_name FROM table_name
ORDER BY column_name ASC
LIMIT 1;

SELECT column_name FROM table_name
WHERE ROWNUM <=1
ORDER BY column_name ASC;

-------------------------------------------------------------------------------------------------------------------------------

LAST()

SELECT LAST(column_name) FROM table_name;

-------------------------------------------------------------------------------------------------------------------------------

MAX()

SELECT MAX(column_name) FROM table_name;

-------------------------------------------------------------------------------------------------------------------------------

MIN()

SELECT MIN(column_name) FROM table_name;

-------------------------------------------------------------------------------------------------------------------------------

SUM()

SELECT SUM(column_name) FROM table_name;

-------------------------------------------------------------------------------------------------------------------------------

GROUP BY

SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name;

-------------------------------------------------------------------------------------------------------------------------------

HAVING

SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name
HAVING aggregate_function(column_name) operator value;

-------------------------------------------------------------------------------------------------------------------------------

UCASE()

SELECT UCASE(column_name) FROM table_name;

SELECT UPPER(column_name) FROM table_name;

-------------------------------------------------------------------------------------------------------------------------------

LCASE()

SELECT LCASE(column_name) FROM table_name;

SELECT LOWER(column_name) FROM table_name;

-------------------------------------------------------------------------------------------------------------------------------

MID()

SELECT MID(column_name,start,length) AS some_name FROM table_name;

LEN()

SELECT LEN(column_name) FROM table_name;

-------------------------------------------------------------------------------------------------------------------------------

ROUND()

SELECT ROUND(column_name,decimals) FROM table_name;

-------------------------------------------------------------------------------------------------------------------------------

NOW()

SELECT NOW() FROM table_name;

-------------------------------------------------------------------------------------------------------------------------------

FORMAT()

SELECT FORMAT(column_name,format) FROM table_name;

-------------------------------------------------------------------------------------------------------------------------------