Meenakshi Arora

SQL
Meenakshi Arora
SQL
It has 9 commands common to all RDBMS
CREATE, DROP, ALTER for Tables. INSERT, UPDATE, DELETE for records. GRANT, REVOKE for permission. SELECT for query.
SQL was initiated by IBM and is now controlled by ANSI (American National Standard Institute). Also pronounced as SEQUEL. It is command language for communication with ORACLE/MSSQL Server. When an SQL statement is entered it is stored in the part of memory called SQL Buffer and remains there until a new SQL statement is entered.
Features of SQL
It can be used by a range of users including those with little or no programming experience. It is a non-procedural language. It reduces the amount of time required for creating and maintaining system. It is English like language
Rules of SQL
SQL statements starts with a verb (SQL action word) eg. SELECT. Each verb is followed by number of clauses eg. FROM, WHERE, HAVING. A space separates clause eg. DROP TABLE. Comma (,)separates parameters without clause. A semicolon (;) is used to end SQL statement. Characters and date literals must be enclosed in single quotes. Numeric values can be represented by simple values 0.39, -34, 01991. Scientific notations 2E5 = 2 * 105
SQL Delimiters
Addition + Subtraction Multiplication * /**/ Division / Relational =,>,< Expression or List () Terminator ; Item Separator , Character String delimiter Quote Identifier Exponential ** Concatenation || Comment -- ,
DDL (Data Definition Language)
Components of SQL
DML (Data Manipulation Language) DCL (Data Control Language)
CREATE, ALTER, DROP, TRUNCATE, COMMENT. INSERT, UPDATE, DELETE, CALL, LOCK TABLE COMMIT- Save the work. SAVE POINT Identify a point in a transaction to which you can later on rollback. ROLLBACK restore database to the original since the last COMMIT. SET TRANSACTION Change transaction option like what rollback segment to use. GRANT/ REVOKE Grant or take back permission to or from the users.
DQL (Data Query Language) SELECT retrieve data from the database
Data types
CHAR (size) store characters maximum upto 255. VARCHAR (size) / VARCHAR2(size) store alphanumeric data maximum upto 2000 / 4000. DATE To represent Date and Time DD-MM-YY. NUMBER(P,S) Precision (P) maximum length of data , Scale (S) determines number of places to the right of decimal. LONG Variable length character strings containing upto 2 GB. CLOB/ BLOB Character Large Objects/ Binary Large objects to store images, pictures, sounds, video, txt files etc.
CREATE
Syntax CREATE TABLE <Table name> (<columnName1> <DataType>(<size>), (<columnName2> <DataType>(<size>)); Example:
create table bank_sys ( branch_no varchar2(10), name varchar2(20));

Output- Table Created
INSERT
Syntax INSERT into Table (<column1>, <column2>) values (data1 , data2); INSERT into Table_name VALUES ( value1, value2,value3,..) Example:
Output- 1 Row Created
insert into Bank_sys (Branch_no, name) values ('1018' , 'Pune');
EXAMPLE
insert into Bank_sys values (1018 , 'Pune');

insert into Bank_sys (Branch_no, name) values ('1018' , 'Pune');

Syntax SELECT * from Tablename; Example:
SELECT
select * from Bank_sys;
SELECT columnname from Tablename; Example:
select Branch_no from Bank_sys;
SELECT * from Tablename where <condition>; Example:
select * from Bank_sys where Branch_no = '1011';
Eliminating Duplicate rows with SELECT

Syntax SELECT DISTINCT * from <Tablename> Example:
select distinct * from Bank_sys ;
Syntax SELECT DISTINCT <columnname1>, <columnname2> from <Tablename> Example:
select distinct Branch_no from Bank_sys
Example of select
P_Id 1 2 3 LastName Hansen Svendson Pettersen FirstName Ola Tove Kari Address Timoteivn 10 Borgvn 23 Storgt 20 City Sandnes Sandnes Stavanger
Persons table We want to select the content of the columns named last name and first name From the persons table Queries :- Select LastName, FirstName from persons
LastName
Hansen Svendson Pettersen
FirstName
Ola Tove Kari
SELECT DISTINCT Example

Persons table
Now we want to select only distinct values from the column named City From the table persons Queries:- Select Distinct City from Persons
City Sandnes Stavanger
Where clause example

Syntax:SELECT column_name From table_name Where column_name operator value
Select *from Persons Where City= sandnes
P_Id 1 2
LastName Hansen Svendson
FirstName Ola Tove
Address Timoteivn 10 Borgvn 23
City Sandnes Sandnes
Operators Allowed in the WHERE Clause

Operator = <>, != > < Equal Not equal Greater than Less than Description
>=
<= BETWEEN LIKE
Greater than or equal

Less than or equal Between an inclusive range Search for a pattern
IN
If you know the exact value you want to return for at least one of the columns
Sorting Data in Table

Syntax SELECT * from <Tablename> order by <columnname1> , <columnname2> <[sort order]>; Example:
select * from Bank_sys order by name ; select * from Bank_sys order by name desc; select * from Bank_sys order by name asc;
Example:The Persons table SELECT * FROM Persons ORDER BY LastName

P_Id 1 4 3 LastName Hansen Nilsen Pettersen FirstName Ola Tom Kari Address Timoteivn 10 Vingvn 23 Storgt 20 City Sandnes Stavanger Stavanger
Svendson
Tove
Borgvn 23
Sandnes
Example:The Persons table SELECT * FROM Persons ORDER BY LastName DESC

P_Id 2 3 4 1 LastName Svendson Pettersen Nilsen Hansen FirstName Tove Kari Tom Ola Address Borgvn 23 Storgt 20 Vingvn 23 Timoteivn 10 City Sandnes Stavanger Stavanger Sandnes
AND & OR Operators

AND operator displays a record if both the first condition and second condition is true. OR operator displays a record if either the first condition or the second condition true.
AND Operator EXAMPLE

The Persons table SELECT * FROM Persons WHERE FirstName='Tove' AND LastName='Svendson'
P_Id 2 LastName Svendson FirstName Address Tove Borgvn 23 City Sandnes
OR Operator Example
SELECT * FROM Persons WHERE FirstName='Tove OR FirstName='Ola'
P_Id 1 2 LastName Hansen Svendson FirstName Ola Tove Address Timoteivn 10 Borgvn 23 City Sandnes Sandnes
Combining AND & OR

SELECT * FROM Persons WHERE LastName='Svendson' AND (FirstName='Tove' OR FirstName='Ola')
P_Id 2
LastName Svendson
FirstName Tove
Address Borgvn 23
City Sandnes
DELETE
Syntax DELETE FROM <TableName> Example:
Delete from Bank_sys;
Syntax DELETE FROM <TableName> where <condition> Example:
Delete from Bank_sys where branch_no = 1011;
UPDATE table
Syntax UPDATE <TableName> SET <columnName1> = <Expression1> , <columnName2> = <Expression2>; Example:
UPDATE Bank_sys set name = Mumbai;
Syntax UPDATE <TableName> SET <columnName1> = <Expression1> , <columnName2> = <Expression2> where <condition>; Example:
UPDATE Bank_sys set name = Head Office where name = Delhi;
Update persons table

P_Id 1 2 3 4 5 LastName gupta sharma suri gupta bedi
Example
FirstName sumit gitika neena rohit kiran Address 54, ashok vihar 1234/8, rohini 678/14, gurgaon 126/21, noida City Delhi Delhi Haryana Uttar pradesh
Query:Update persons Set Address= 54/9 Ashoka appartment, rohini, City= delhi Where lastname = bedi and firstname= kiran
output
P_Id 1 LastName gupta FirstName sumit Address 54, ashok vihar 1234/8, rohini 678/14, gurgaon City Delhi
2 3
sharma suri
gitika neena
Delhi Haryana
4
5
gupta
bedi
rohit
kiran
126/21, noida
54/9 Ashoka appartment, rohini
Uttar pradesh
Delhi
Syntax ALTER Table <Tablename> ADD (<newcolumnname> <Datatype> (<size>), <newcolumn2> <Datatype2>(size)); Example:
ALTER Table
ALTER TABLE Bank_sys add (address varchar(40));
Syntax ALTER TABLE <Tablename> DROP COLUMN <Columnname>; Example:
ALTER TABLE Bank_sys DROP COLUMN name;
Syntax ALTER TABLE <Tablename> MODIFY ( <Columnname> <Newdatatype> (<size>)); Example:
ALTER TABLE Bank_sys Modify (Branch_no varchar(15));
Problem
Create table
Create the following table Client- client_no, name, Address, City, State. Product_master- p_no, pname, sellprice, costprice. Sales_Master- salesman_name, salesman_no, address, city, state, remarks, salary.
Query
Find out names of all the clients. Retrieve the entire content of the client table. Retrieve the list of name, city and state of all the clients. Find the name of salesman who have salary equal to 3000 Rupees. Find the name of clients who are located in Mumbai.
Example
Display all details of student. List name of student whose name begin with r. List details of student who having highest marks in class. List details of student whose name that have A and M. Display name and marks of student.

Meenakshi Arora

Uploaded by

Copyright:

Available Formats

Meenakshi Arora

Uploaded by

Document Information

Original Description:

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Meenakshi Arora

Uploaded by

Copyright:

Available Formats

SQL

DDL (Data Definition Language)

DML (Data Manipulation Language) DCL (Data Control Language)

create table bank_sys ( branch_no varchar2(10), name varchar2(20));

Output- 1 Row Created

insert into Bank_sys (Branch_no, name) values ('1018' , 'Pune');

insert into Bank_sys values (1018 , 'Pune');

insert into Bank_sys (Branch_no, name) values ('1018' , 'Pune');

Syntax SELECT * from Tablename; Example:

select * from Bank_sys;

SELECT columnname from Tablename; Example:

select Branch_no from Bank_sys;

SELECT * from Tablename where <condition>; Example:

select * from Bank_sys where Branch_no = '1011';

Eliminating Duplicate rows with SELECT

select distinct * from Bank_sys ;

Syntax SELECT DISTINCT <columnname1>, <columnname2> from <Tablename> Example:

select distinct Branch_no from Bank_sys

SELECT DISTINCT Example

City Sandnes Stavanger

Where clause example

Select *from Persons Where City= sandnes

LastName Hansen Svendson

FirstName Ola Tove

Address Timoteivn 10 Borgvn 23

City Sandnes Sandnes

Operators Allowed in the WHERE Clause

Greater than or equal

Sorting Data in Table

Example:The Persons table SELECT * FROM Persons ORDER BY LastName

Example:The Persons table SELECT * FROM Persons ORDER BY LastName DESC

AND & OR Operators

AND Operator EXAMPLE

Combining AND & OR

Delete from Bank_sys;

Syntax DELETE FROM <TableName> where <condition> Example:

Delete from Bank_sys where branch_no = 1011;

UPDATE Bank_sys set name = Mumbai;

UPDATE Bank_sys set name = Head Office where name = Delhi;

Update persons table

ALTER TABLE Bank_sys add (address varchar(40));

Syntax ALTER TABLE <Tablename> DROP COLUMN <Columnname>; Example:

ALTER TABLE Bank_sys DROP COLUMN name;

Syntax ALTER TABLE <Tablename> MODIFY ( <Columnname> <Newdatatype> (<size>)); Example:

ALTER TABLE Bank_sys Modify (Branch_no varchar(15));

You might also like