sql || overview and detailed information about sql

SQLSQL

By:-Gourav Kottawar 1

• What is SQL?• SQL stands for Structured Query Language • SQL lets you access and manipulate databases • SQL is an ANSI (American National Standards

Institute) standard


• What Can SQL do?• SQL can execute queries against a database • SQL can retrieve data from a database • SQL can insert records in a database • SQL can update records in a database • SQL can delete records from a database • SQL can create new databases • SQL can create new tables in a database • SQL can create stored procedures in a database • SQL can create views in a database • SQL can set permissions on tables, procedures,

and views


• SELECT * FROM Persons ;

• SQL is not case sensitive, Semicolon after SQL Statements.

• Some database systems require a semicolon at the end of each SQL statement.


• SQL DML and DDL• SQL can be divided into two parts: The Data

Manipulation Language (DML) and the Data Definition Language (DDL).

• The query and update commands form the DML part of SQL:

• SELECT - extracts data from a database • UPDATE - updates data in a database • DELETE - deletes data from a database • INSERT INTO - inserts new data into a

database


• The DDL part of SQL permits database tables to be created or deleted. It also define indexes (keys), specify links between tables, and impose constraints between tables. The most important DDL statements in SQL are:

• CREATE TABLE - creates a new table • ALTER TABLE - modifies a table • DROP TABLE - deletes a table


• SQL SELECT Syntax• SELECT column_name(s)

FROM table_name

• SELECT * FROM table_name

• And

• ‘*’ Also called as Global Extraction Opearator


P_Id LastName FirstName Address City

1 Hansen OlaTimoteivn 10

Sandnes

2 Svendson Tove Borgvn 23 Sandnes

3 Pettersen Kari Storgt 20 Stavanger


• SELECT LastName,FirstName FROM Persons• The result-set will look like this:

By:-Gourav Kottawar

LastName FirstNameHansen OlaSvendson TovePettersen Kari

9

• The SQL SELECT DISTINCT Statement

• In a table, some of the columns may contain duplicate values. This is not a problem, however, sometimes you will want to list only the different (distinct) values in a table.

• The DISTINCT keyword can be used to return only distinct (different) values.


• SELECT DISTINCT column_name(s)FROM table_name

• SELECT DISTINCT Example• The "Persons" table:

SELECT DISTINCT City FROM Persons• The result-set will look like this:• City• Sandnes• Stavanger


• CLAUSES IN SQL The WHERE Clause

• SELECT column_name(s)FROM table_nameWHERE column_name operator value


• SELECT * FROM PersonsWHERE City='Sandnes'

• The result-set will look like this:

By:-Gourav Kottawar



Sandnes


13

• This is correct:

SELECT * FROM Persons WHERE FirstName='Tove‘ ;

This is wrong:

SELECT * FROM Persons WHERE FirstName=Tove


• This is correct:

SELECT * FROM Persons WHERE Year=1965

This is wrong:

SELECT * FROM Persons WHERE Year='1965'


• Operators Allowed in the WHERE Clause• With the WHERE clause, the following operators can be

used:

By:-Gourav Kottawar

Operator Description

= Equal

<> Not equal

> Greater than

< Less than

>= Greater than or equal

<= Less than or equal

BETWEEN Between an inclusive range

LIKE Search for a pattern

IN If you know the exact value you want to return for at least one of the columns 16

• Now we want to select only the persons with the first name equal to "Tove" AND the last name equal to "Svendson":

• We use the following SELECT statement:

• SELECT * FROM PersonsWHERE FirstName='Tove'AND LastName='Svendson‘ ;



By:-Gourav Kottawar



18

• Now we want to select only the persons with the first name equal to "Tove" OR the first name equal to "Ola":We use the following SELECT statement:

• SELECT * FROM PersonsWHERE FirstName='Tove'OR FirstName='Ola‘ ;



1 Hansen Ola Timoteivn 10 Sandnes



• SELECT * FROM Persons WHERELastName='Svendson'AND (FirstName='Tove' OR FirstName='Ola');


By:-Gourav Kottawar

P_Id LastName FirstNameAddress City

2 Svendson Tove Borgvn 23Sandnes

21

• SQL IN Operator• The IN Operator• The IN operator allows you to specify multiple

values in a WHERE clause.• SQL IN Syntax:

• SELECT column_name(s)FROM table_nameWHERE column_name IN (value1,value2,...)


• IN Operator Example• The "Persons" table:• We use the following SELECT statement:• SELECT * FROM Persons

WHERE LastName IN ('Hansen','Pettersen')• The result-set will look like this:


_Id LastName FirstName Address City

1 Hansen Ola Timoteivn 10 Sandnes



• SQL BETWEEN Operator• The BETWEEN operator is used in a WHERE

clause to select a range of data between two values.

• The BETWEEN Operator• The BETWEEN operator selects range of data

between two values. The values can be numbers, text, or dates.


• SQL BETWEEN Syntax:SELECT column_name(s)FROM table_nameWHERE column_nameBETWEEN value1 AND value2


• SQL Aggregate Functions• SQL aggregate functions return a single value,

calculated from values in a column.• Useful aggregate functions:• AVG() - Returns the average value • COUNT() - Returns the number of rows • FIRST() - Returns the first value • LAST() - Returns the last value • MAX() - Returns the largest value • MIN() - Returns the smallest value • SUM() - Returns the sum


• SQL COUNT(*) Example

• SELECT COUNT(*) FROM Orders;


• The MAX() Function• The MAX() function returns the largest value

of the selected column.• SQL MAX() Syntax:SELECT

MAX(column_name) FROM table_name

• SELECT MAX(OrderPrice) AS LargestOrderPrice FROM Orders


• SQL MIN() Function • The MIN() function returns the smallest value

of the selected column.• SQL MIN() Syntax:SELECT MIN(column_name)

FROM table_name

• SELECT MIN(OrderPrice) AS SmallestOrderPrice FROM Orders


• The SUM() Function• The SUM() function returns the total sum of a

numeric column.• SQL SUM() Syntax: SELECT

SUM(column_name) FROM table_name

• SELECT SUM(OrderPrice) AS OrderTotal FROM Orders


• The UCASE() Function• The UCASE() function converts the value of a

field to uppercase.• SQL UCASE() Syntax:SELECT

UCASE(column_name) FROM table_name

• SELECT UCASE(LastName) , FirstName FROM Persons


• The LCASE() Function• The LCASE() function converts the value of a

field to lowercase.• SQL LCASE() Syntax:SELECT

LCASE(column_name) FROM table_name

• SELECT LCASE(LastName) , FirstName FROM SELECT LCASE(LastName) , FirstName FROM PersonsPersons


• The MID() Function• The MID() function is used to extract

characters from a text field.• SQL MID() Syntax:• SELECT MID(column_name,start[,length])

FROM table_name;

• SELECT MID(City,1,4) as SmallCity FROM Persons


• The LEN() Function• The LEN() function returns the length of the

value in a text field.• SQL LEN() Syntax: SELECT LEN(column_name)

FROM table_name

• SELECT LEN(Address) FROM Persons


• The ROUND() Function• The ROUND() function is used to round a

numeric field to the number of decimals specified.

• SQL ROUND() Syntax:SELECT ROUND(column_name,decimals) FROM table_name

• SELECT ProductName, ROUND(UnitPrice) FROM Products


By:-Gourav Kottawar

Query : Find the maximum salary, the minimum salary, andthe average salary among all employees.

SELECT MAX(SALARY), MIN(SALARY), AVG(SALARY)

FROM EMPLOYEE ;

Include COUNT, SUM, MAX, MIN, and AVG

37

• Query 11: Find the maximum salary, the minimum salary, and the average salary among employees who work for the 10 department.

• SELECT MAX(SALARY), MIN(SALARY), AVG(SALARY)

• FROM EMPLOYEE

• WHERE DNO =10;


STRING/CHAR FUNCTIONS

• SUBSTR()• TRIM()• LTRIM()• RTRIM()• REPLACE()• CONCAT()


SQL STRING FUNCTION

1.SUBSTR

• Table Geography region_name store_name East Boston East New York West Los Angeles West San Diego

• Example 1: • SELECT SUBSTR(store_name, 3)

FROM Geography WHERE store_name = 'Los Angeles';


• Result: • 's Angeles' • Example 2: • SELECT SUBSTR(store_name,2,4)

FROM Geography WHERE store_name = 'San Diego';

• Result: • 'an D'


• 2.LTRIM(str): Removes all white spaces from the beginning of the string.

• 3.RTRIM(str): Removes all white spaces at the end of the string.

• Example 1: • SELECT TRIM(' Sample ') from tablename; • Result: • 'Sample'


• Example 2: • SELECT LTRIM(' Sample '); • Result: • 'Sample '

• Example 3: • SELECT RTRIM(' Sample '); • Result: • ' Sample'


• 4. Length(str): Find the length of the string str.

By:-Gourav Kottawar

region_name store_name

East Boston

East New York

West Los Angeles

West San Diego

44

• Example 1: • SELECT Length(store_name)

FROM Geography WHERE store_name = 'Los Angeles';

• Result: • 11


• Example 2: • SELECT region_name, Length(region_name)

FROM Geography; • Result:

By:-Gourav Kottawar

region_nameLength(region_name)

East 4

East 4

West 4

West 446

• 5.Replace(str1, str2, str3): In str1, find where str2 occurs, and replace it with str3.

• SELECT REPLACE(region_name, 'ast', 'astern')FROM Geography;

By:-Gourav Kottawar

region_name

Eastern

Eastern

West

West

47

• 6. CONCAT • CONCAT(str1, str2, str3, ...): Concatenate str1,

str2, str3, and any other strings together. Please note the Oracle CONCAT() function only allows two arguments -- only two strings can be put together at a time using this function. However, it is possible to concatenate more than two strings at a time in Oracle using '||'.


• SELECT CONCAT(region_name,store_name) FROM Geography WHERE store_name = 'Boston';

• Result:

'EastBoston'

• SELECT region_name || ' ' || store_name FROM Geography WHERE store_name = 'Boston';

• Result:

'East Boston'


WILDCARD• There are times when we want to match on a

string pattern. To do that, we will need to employ the concept of wildcard. In SQL, there are two wildcards:

• % (percent sign) represents zero, one, or more characters.

• _ (underscore) represents exactly one character.

• Wildcards are used with the LIKE keyword in SQL


http://www.1keydata.com/sql/sqllike.html

• LIKE is another keyword that is used in the WHERE clause. Basically, LIKE allows you to do a search based on a pattern rather than specifying exactly what is desired (as in IN) or spell out a range (as in BETWEEN). The syntax is as follows:

• SELECT "column_name"FROM "table_name"WHERE "column_name" LIKE {PATTERN}


store_name Sales Date

LOS ANGELES $1500 Jan-05-1999

SAN DIEGO $250 Jan-07-1999

SAN FRANCISCO $300 Jan-08-1999

BOSTON $700 Jan-08-1999

By:-Gourav Kottawar

We want to find all stores whose name contains 'AN'.

52

• SELECT *FROM Store_InformationWHERE store_name LIKE '%AN%‘

•

By:-Gourav Kottawar


LOS ANGELES $1500 Jan-05-1999

SAN DIEGO $250 Jan-07-1999

SAN FRANCISCO

$300 Jan-08-1999

53

ORDER BY clause

• So far, we have seen how to get data out of a table using SELECT and WHERE commands. Often, however, we need to list the output in a particular order. This could be in ascending order, in descending order, or could be based on either numerical value or text value. In such cases, we can use the ORDER BY keyword to achieve our goal.


• The syntax for an ORDER BY statement is as follows:

• SELECT "column_name"FROM "table_name"[WHERE "condition"]ORDER BY "column_name" [ASC, DESC]


• The [] means that the WHERE statement is optional. However, if a WHERE clause exists, it comes before the ORDER BY clause. ASC means that the results will be shown in ascending order, and DESC means that the results will be shown in descending order. If neither is specified, the default is ASC.


• For example, we may wish to list the contents of Table Store_Information by dollar amount, in descending order:

By:-Gourav Kottawar


Los Angeles $1500 Jan-05-1999

Boston $700 Jan-08-1999

San Francisco $300 Jan-08-1999

San Diego $250 Jan-07-1999

57

• The GROUP BY Statement• The GROUP BY statement is used in

conjunction with the aggregate functions to group the result-set by one or more columns.

• SQL GROUP BY Syntax: • SELECT column_name,

aggregate_function(column_name)FROM table_nameWHERE column_name operator valueGROUP BY column_name


O_Id OrderDate OrderPrice Customer

1 2008/11/12 1000 Hansen

2 2008/10/23 1600 Nilsen

3 2008/09/02 700 Hansen

4 2008/09/03 300 Hansen

5 2008/08/30 2000 Jensen

6 2008/10/04 100 Nilsen


• Now we want to find the total sum (total order) of each customer.

• We will have to use the GROUP BY statement to group the customers.

• We use the following SQL statement:

• SELECT Customer,SUM(OrderPrice) FROM OrdersGROUP BY Customer


By:-Gourav Kottawar

Customer SUM(OrderPrice)Hansen 2000Nilsen 1700Jensen 2000

60

• We can also use the GROUP BY statement on more than one column, like this:

• SELECT Customer,OrderDate,SUM(OrderPrice)

FROM OrdersGROUP BY Customer,OrderDate


• SQL DELETE Syntax• DELETE FROM table_name

WHERE some_column=some_value• Note: Notice the WHERE clause in the DELETE

syntax. The WHERE clause specifies which record or records that should be deleted. If you omit the WHERE clause, all records will be deleted!




Sandnes



4 Nilsen Johan Bakken 2 Stavanger

5 Tjessem JakobNissestien 67

Sandnes


By:-Gourav Kottawar

Now we want to delete the person "Tjessem, Jakob" in the "Persons" table.

We use the following SQL statement:

DELETE FROM PersonsWHERE LastName='Tjessem' AND FirstName='Jakob'

64

• Delete All Rows• It is possible to delete all rows in a table

without deleting the table. This means that the table structure, attributes, and indexes will be intact:


• DELETE FROM table_name

or

DELETE * FROM table_name• Note: Be very careful when deleting records.

You cannot undo this statement!


ALTER TABLE –ADD COLUMN

alter table emp

add AGE number(2);

• Table altered.


ALTER TABLE- MODIFY

alter table emp

modify AGE varchar(3);


alter table emp

modify AGE number(2);

Varchar can be changed to number and vise versa, but some of the lower version will not support number to varchar.


• create table Emp_deatils(empno varchar(3),ename varchar(10));

• insert into Emp_details values('&empno','&ename');


• After INSERTING values into table it is not possible to MODIFY the contents of the table.

• For ex: Telephone number is entered(10) long, then we try to modify it by reducing the size to 8, At this case modify will not work.


• SQL> alter table emp_deatils

modify empno number(1);

Output:

modify empno number(1)

*

ERROR at line 2:

ORA-01439: column to be modified must be empty to change datatype


DROP TABLE• Helps in removing the rows from the table.• It is a DML command.• The description (structure) of the table is

removed.• The existence of the table is removed.

(Table cant be seen from in select * from tab).

Rollback though shows completed but table view doesn’t come back.

AS IT IS A DML COMMAND ROLLBACK WILL NOT WORK


• ALTER table tablename

DROP column colname

SQL> alter table emp

2 drop column age;

drop column age

*

ERROR at line 2: ORA-00905: missing keyword

ALTER-Drop column will work in Oracle 9i and above version. By:-Gourav Kottawar 74

SQL> drop table room;

Table dropped.

SQL> desc room; //description is not available

ERROR:

ORA-04043: object room does not exist


TRUNCATE COMMAND

• SQL> truncate table emp_details;

Table truncated.

• SQL> select * from emp_details;

no rows selected


• ROWS are REMOVED.• Description still remains• Table exists (select * from Tab)• Rollback shows ‘rollback completed ‘ but

as it is a DML command, ROLLBACK WILL NOT WORK cant resume back the contents.


• SQL> desc emp_deatils;• Name Null? Type• ------------------------------- -------- ----• EMPNO VARCHAR2(3)• ENAME VARCHAR2(10)


DELETE-Helps in deleting the contents

• Deletes the row.• IT is DDL commands.• Rollback works and will resume the

table contents.• Rollback works because it is DDL

command.


DROP TRUNCATE DELETE

Removes the row and table

Removes all rows and contents

Removes all rows and contents

No description exists(desc )

Description is present

Description is present

Entire table is removed(cant view table in select * from tab)

View of table exists

View of table exists

Part of DML query Part of DML query Part of DDL query

Cant be rollback Cant be rollback Can be rollback


• Adding Constraints to existing table.• Droping the existing table.


• SQL PRIMARY KEY Constraint on ALTER TABLE

• To create a PRIMARY KEY constraint on the "P_Id" column when the table is already created, use the following SQL:

• MySQL / SQL Server / Oracle / MS Access:• ALTER TABLE Persons

ADD PRIMARY KEY (P_Id)


• To allow naming of a PRIMARY KEY constraint, and for defining a PRIMARY KEY constraint on multiple columns, use the following SQL syntax:

• MySQL / SQL Server / Oracle / MS Access:• ALTER TABLE Persons

ADD CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName)


• To DROP a PRIMARY KEY Constraint• To drop a PRIMARY KEY constraint, use the

following SQL:• MySQL:• ALTER TABLE Persons

DROP PRIMARY KEY• SQL Server / Oracle / MS Access:• ALTER TABLE Persons

DROP CONSTRAINT pk_PersonID


• CREATE TABLE Persons(P_Id number NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255),Address varchar(255),City varchar(255),

CONSTRAINT uc_PersonID UNIQUE (P_Id,LastName) )


• SQL UNIQUE Constraint on ALTER TABLE• To create a UNIQUE constraint on the "P_Id"

column when the table is already created, use the following SQL:

• ALTER TABLE PersonsADD UNIQUE (P_Id)


• To allow naming of a UNIQUE constraint, and for defining a UNIQUE constraint on multiple columns, use the following SQL syntax:

• ALTER TABLE PersonsADD CONSTRAINT uc_PersonID UNIQUE

(P_Id , LastName)


• To DROP a UNIQUE Constraint• To drop a UNIQUE constraint, use the

following SQL:

• ALTER TABLE PersonsDROP CONSTRAINT uc_PersonID


• SQL FOREIGN KEY Constraint on ALTER TABLE• To create a FOREIGN KEY constraint on the

"P_Id" column when the "Orders" table is already created, use the following SQL:

• ALTER TABLE OrdersADD FOREIGN KEY (P_Id)REFERENCES Persons(P_Id)


• To allow naming of a FOREIGN KEY constraint, and for defining a FOREIGN KEY constraint on multiple columns, use the following SQL syntax:

• ALTER TABLE OrdersADD CONSTRAINT fk_PerOrdersFOREIGN KEY (P_Id)REFERENCES Persons(P_Id)


• To DROP a FOREIGN KEY Constraint• To drop a FOREIGN KEY constraint, use the

following SQL:

• MySQL:• ALTER TABLE Orders

DROP FOREIGN KEY fk_PerOrders• SQL Server / Oracle / MS Access:• ALTER TABLE Orders

DROP CONSTRAINT fk_PerOrders


• ADD CHECK Constarints• ALTER TABLE Persons

ADD CONSTRAINT chk_Person CHECK (P_Id>0 AND City='Sandnes')

• To DROP a CHECK Constraint• To drop a CHECK constraint, use the

following SQL:• SQL Server / Oracle / MS Access:• ALTER TABLE Persons

DROP CONSTRAINT chk_Person


• OVERVIEW

SQL SELECTSQL DISTINCTSQL WHERESQL AND ORSQL INSQL BETWEENSQL WildcardSQL LIKESQL ORDER BY


http://www.1keydata.com/sql/sqlselect.html

http://www.1keydata.com/sql/sqldistinct.html

http://www.1keydata.com/sql/sqlwhere.html

http://www.1keydata.com/sql/sqlandor.html

http://www.1keydata.com/sql/sqlin.html

http://www.1keydata.com/sql/sqlbetween.html

http://www.1keydata.com/sql/sql-wildcard.html

http://www.1keydata.com/sql/sqllike.html

http://www.1keydata.com/sql/sqlorderby.html

• SQL FunctionsSQL AverageSQL COUNTSQL MAXSQL MINSQL SUM

• SQL CONCATENATESQL SUBSTRINGSQL TRIMSQL LENGTHSQL REPLACE


http://www.1keydata.com/sql/sqlfunctions.html

http://www.1keydata.com/sql/sql-average.html

http://www.1keydata.com/sql/sqlcount.html

http://www.1keydata.com/sql/sql-max.html

http://www.1keydata.com/sql/sql-min.html

http://www.1keydata.com/sql/sql-sum.html

http://www.1keydata.com/sql/sql-concatenate.html

http://www.1keydata.com/sql/sql-substring.html

http://www.1keydata.com/sql/sql-trim.html

http://www.1keydata.com/sql/sql-length.html

• SQL GROUP BYSQL HAVINGSQL ALIASSQL AS

• SQL DATEADDSQL DATEDIFFSQL DATEPARTSQL GETDATESQL SYSDATE

• SQL JOINSQL OUTER JOINSQL SELECT UNIQUE


sql || overview and detailed information about sql

Career