intorduction sql

Prepared BY: Mitali Sonar

(Assistant Professor)

Intorduction SQL

04/20/231Prepared By: Mitali Sonar (Assistant Professor)

What is database?Collection of information/ meaningful dataEx:- postal address

Values in this fields are dataAddress book is database

Building name

Flat name

Road name

Area

State

pin code

04/20/232 Prepared By: Mitali Sonar (Assistant Professor)

What is DBMS?Software that designs, manages databaseDBMS allows insert, update, delete and

processing of data in databaseEx:-

OracleIngresDbaseMS. Access


What is relational DBMSDBMS based on relational modelStores data in form of related data/tablesDbase can Be viewed in many different waysEx:-

OracleMicrosoft SQL serverSybase SQL ServerDB2MySQL


DBMS Vs. RDBMSDBMS RDBMS

Relationship between two tables or files are maintained programmatically

Relationship between tables or files are maintained at the time of their creation

Does not support client/ server architecture

Support client/ server architecture

No security of the data Multiple levels of the security•At O/S level•Command Level•Object level

Field, records, files Columns (attribute)Rows (tuple)Table (relation)


Introduction to SQLStructured Query Language

Provides interface to RDBMSLanguage for communication with oracle server

to access dataCategory/ component of SQL DDL commands (data definition language)

To create a database objects not dataDefine relation schema ,deleting relations and

modifying relational schemasEx:-

CREATE :- Used to create a db objectALTER :- alter structure of DBDROP:- Delete the object from DBTRUNCATE :- Remove all records from DB


Introduction to SQL (conti…)

04/20/23Prepared By: Mitali Sonar (Assistant Professor)7

DML commands (Data Manipulation Language)To manipulate data of a database objectsIncludes commands to insert tuples into , delete

tuples from and modify tuples in database. INSERT :- Insert data into tableUPDATE:-Updates existing data within tableDELETE :- deletes all records from table

DQL command (Data Query Language) To retrieve the data from a databaseGetting data from database and imposing ordering

upon itSELECT:- retrieve data from the DB

Introduction to SQL (conti…)


DCL commands (Data Control Language)Controlled access to data

GRANT - Used to give privilege to user on object REVOKE - Used to withdraw the privilege that has been

granted to the user. COMMIT:- save work doneROLLBACK :- restore the database to original state

since last commit

Basic data type


char(size) A fixed length character string with only 255 user specified

charactersData held is right padded with spaces to whatever size is

specifiedvarchar2(size)

A variable character length string with alphanumeric valuesmaximum length 4000 charactersInserted values are not contains white spaces

Date a calendar date containing a year, month and day of the

month DD-MON-YYYY

Number (P,S)Used to store a number (fixed or floating point). Max precision can be 38 digitsP – max length of the dataS- number of places to the right of decimal

TABLE fundamentals


Table holds user data & Similar to a spreadsheetConsist of rows (tuples) & columns (attribute)Each column have data type associated with it.Syntax :- CREATE TABLE table_name (col1 datatype(size), ….,

coln datatype(size) );

OUTPUT:-

Inserting data into tables


Loads the value passed by SQL insert command into columns specified

INSERT INTO tablename VALUES(<val 1>,<val 2>…….<valn>);

Viewing data in the tables


SELECT – is used to retrieve rows from one or more tables

All rows & all columnsSELECT * FROM tablename; Ex: - show all employee details

Viewing data in the tables (cont..)


Selected columns & all rowsSELECT Column1, Column2 FROM

tablename;



Selected Rows and all ColumnsSELECT * FROM tablename WHERE conditionEx:- Display details of employee who lives in andheri

area

Ex:- Display details of employee who live in vile parle area



Selected Rows and selected ColumnsSELECT Column1, Column2 FROM tablename

WHERE conditionEx:- list eno and ename who lives in vile parle area

Eliminate duplicate values


DISTINCTIt scans through the values of columns specified

and display only unique values among themSELECT DISTINCT column1 FROM tablename;

Sorting the data


ORDER BY :-retrieves rows from table either ascending or descending order

SELECT * FROM tablename ORDER BY colname (sortorder);

Ex:- show details of employee according to department name.

DELETE operation


Deletes rows from table that satisfies the condition provided by where clause

Removal of all rows form the table DELETE FROM tablename;

Removal of specific rows DELETE FROM tablename where Condition;

Update the content of table


Change or modify the data values in tableUpdating all rowsUPDATE tablename SET col1 = expression1;Ex:- update the department name to research for all

employees

Update the content of table


Change or modify the data values in tableUpdating rows conditionallyUPDATE tablename SET col1 = expression1

WHERE condition;Ex:- update the address of employee sanket from

sion to navi mumbai

NEW TABLE

OLD TABLE

Modifying the structure of the tables


ALTER TABLE :-Changes the structure of existing table

Add, delete columns / changes the datatype of columns /rename the columns or table itself

Adding a new columnALTER TABLE tablename ADD (new_column _name

datatype(size));Ex:- Enter a new field called contactno in table

Modifying the structure of the tables(cont..)


Dropping a column from a tableALTER TABLE tablename DROP COLUMN column

name;Ex- drop the column contactno from the employee

table

Modifying the structure of the tables(cont..)


Modifying Existing columnsALTER TABLE tablename MODIFY (colnname

newdatatype (newsize));

Ex:- Change the employee name field to hold Maximum 30 characters

Restriction to ALTER TABLE


Following task can’t be performed using ALTER TABLE

Change the name of the table /columnsDecrease size of column

RENAME TABLESREANAME tablename to newtablename;Ex:- change the name of table to emp_data;RENAME employee_data TO emp_data;


TRUNCATING TABLESEmpties a table completelyDifferent from delete in following ways

Drop and recreates the table that is much faster than deleting

Truncate are not transaction-safe No. of deleted rows are not returned

TRUNCATE TABLE tablename;Ex:- clear all records from employee tableOutput-DESTROYING TABLESWhen table becomes obsolete and needs to be

discarded.Destroys a specific table DROP TABLE tablename;Ex:-DROP TABLE emp_data;O/P :- table dropped.

Examining objects created by users


Finding out tables created by userSELECT table_name FROM user_tables;Displaying the table structure

To display information about columns defined in a tableDESCRIBE tablename;

Data constraints


Set of rules that must be applied to data prior to being stored in DB

Ex:- no employee in sales department should have salary more than 10,000

If there is any violation between the constraint and the data action, the action is aborted by the constraint.

Once constraints is attached to table any SQL INSERT or UPDATE statement automatically causes these constraints to be applied to data before its being stored in table

2 typesI/O constraintBusiness rule constraints

Data constraints


In SQL, we have the following constraints:

NOT NULL

UNIQUE

PRIMARY KEY

FOREIGN KEY

CHECK

DEFAULT

I/O Constraint


PRIMARY KEY CONSTRAINTS

One or more column in table that uniquely identify each

tuple

No primary key column can contain NULL value

primary key column is a mandatory column

Data held across column must be unique.

Single column primary key is Simple key

A multi column primary key is a Composite primary key

Defined either in CREATE TABLE statement or in ALTER

TABLE statement

PRIMARY KEY CONSTRAINTS


Syntax : Column-name datatype(size) primary key

Ex: CREATE TABLE Employee(

ENO number(4) primary key,

ENAME varchar2(20));PRIMARY KEY (column-name1, column-name2)Ex: CREATE TABLE Employee(

ENO number(4),

ENAME varchar2(20),

CONSTRAINT pk_ENO primary key (ENO));EX:- ALTER TABLE Employee

ADD CONSTRAINT pk_ENO primary key (ENO));

Foreign key constraints


Represents relationship between two tablesIts value is derived from primary key of the other tableTable in which foreign key is defined is called

foreign tableTable in which primary key is defined is called

master tableSyntax :- REFERENCES tablename.columnnameFeaturesParent that is being referenced has to be unique Child may have duplicateParent record can be deleted provided no child existsMaster table cannot be updated if child record exists



create table branch(branch_name char(15),branch_city char(30),assetsnumber(12,2),primary key (branch_name ));

create table account(account_number char(10),branch_name char(15),balance integer,primary key (account_number), references branch (branch_name) );

Foreign key constraint


Insert/update operations in foreign A value cannot be inserted in foreign table if

corresponding value is not presented in master table



Delete operation on primary key table(master table)Oracle display error message when deleting a record

form master table and corresponding record exists in foreign key table

This will prevent the delete operation form execution


ON DELETE CASCADEIf a record is deleted in master table , all

corresponding records in foreign key table is also deleted.

create table "branch" (`"branch_name" char(15), "branch_city" char(30), "assets" number(12,2), constraint "branch_con" foreign key ("branch_name") references "account" ("branch_name") ON DELETE CASCADE ENABLE )


ON DELETE SET NULLIf a record is deleted from Master table, the

value held by foreign key table is set to NullThe records in foreign key table will not

deletedcreate table "branch" (

"branch_name" char(15), "branch_city" char(30), "assets" number(12,2), constraint "branch_con" foreign key ("branch_name") references "account" ("branch_name") ON DELETE SET NULL)

Unique key constraint


It permits multiple entries of NULL into columnIt will not allowed duplicate valueA table can have more than one unique key that

is not possible for primary key

ALTER TABLE customerADD CONSTRAINT Cust_con UNIQUE (cust_no);

BUSINESS RULE CONSTRAINT


Rules are applied to data prior the data is being inserted

This will ensure that data in table have integrityEx;- Rule ” no employee in company shall get a

salary less than 10000”Inserted to column or table using create table or

alter tableConstraints can be defined at

Column levelTable level


NULL VALUE CONCEPTSSome fields do not have values because information is not

available at time of data entryWill place NULL value that is different from blank(if data

type is character) or zero(if data type is number)Null can be inserted in columns of any data type If a column has NULL value than oracle ignore UNIQUE,

FOREIGN KEY, CHECK constraintsNOT NULL conceptEnsures that column cannot left emptyOnly be applied at column level If column is defined as not null, that column becomes a

mandatory column

CHECK CONSTRAINT


used to limit the value range that can be placed in a column.

Specified as logical expression that evaluates as TRUE or FALSE

CHECK constraint on a single column allows only certain values for this column.

CHECK constraint on a table can limit the values in certain columns based on values in

other columns in the row.CREATE TABLE Persons

(P_Id int NOT NULL,LastName varchar(255) NOT NULL,FirstName varchar(255),Address varchar(255),City varchar(255),CONSTRAINT chk_Person CHECK (P_Id>0 and City=‘Ahmedabad'))


Create table customer_master( cust_no varchar2(5), Fname varchar2(10), Lname

varchar2(10),CHECK (cust_no like ‘C%’),CHECK (Fname =UPPER (FNAME)));

If condition has false value error message will be displayed and processing stops there

Default Constraint


used to insert a default value into a column.The default value will be added to all new

records, if no other value is specified.

CREATE TABLE Persons(P_Id int NOT NULL,LastName varchar(255) NOT NULL,FirstName varchar(255),Address varchar(255),City varchar(255) DEFAULT ‘ahmedabad');

Create the tables described as follow


Table name: Product MasterColumn Name

Data Type

Size

Attribute

PRODUCT NO Varchar2 6 PRIMARY KEY/ first letter must start with ‘P’

DESCRIPTION Varchar2 15 Not Null

PROFITPERCENT

Number 4,2 Not Null

SELLPRICE Number 8,2 Not Null, cannot be 0

COSTPRICE Number 8,2 Not null, cannot be 0

Computations done on table data


manipulates individual data items and returns a result.

Ex:- display all employee’s detail with their annual salary (salary *12)

Arithmetic operators/ Division (numbers and dates)

SELECT SAL / 10 FROM EMP;

* Multiplication SELECT SAL * 5 FROM EMP;+ Addition (numbers

and dates)SELECT SAL + 200 FROM EMP;

- Subtraction (numbers and dates)

SELECT SAL - 100 FROM EMP;


C_code

cname city areacountry

gradeopening_amt

receive_amt

payment_amt

outstanding_amt

C00013

Holmes

London

London

UK 26000.00

5000.00

7000.00

4000.00

C00001

Micheal

New York

New York

USA 23000.00

5000.00

2000.00

6000.00

Customer Table

SELECT cname, opening_amt, receive_amt,(opening_amt + receive_amt) FROM customer WHERE (opening_amt + receive_amt)>15000;

Renaming the column used with expression


SQL aliases are used to give a column in a table, a temporary name.

SELECT column_name AS alias_name FROM table_name;C_ID C_Name Contact

NameAddress City PostalC

odeCountry

1 Alfreds Futterkiste

Maria Anders

Obere Str. 57

Berlin 12209 Germany

2 Ana Trujillo Emparedados y helados

Ana Trujillo

Avda. de la Constitución 2222

México D.F.

05021 Mexico

3 Antonio Moreno Taquería

Antonio Moreno

Mataderos 2312

México D.F.

05023 Mexico


SELECT C_Name, Address+City+PostalCode+Country AS AddressFROM Customers;

Logical operators


Logical operators in SQL will return either true or false value.Logical

OperatorsDescription

OR At least one of the conditions must be true.

AND All the specified conditions must be true.

NOT Reversed the result of logical expression

"OR" Logical Operator:


find the details of students who are studying either Maths or Science, the query would be like,

SELECT * FROM studentdetails

WHERE sub1 = 'Maths' OR sub1= 'Science'

AND logical operator


Joins two or more conditions, and returns results only when all of the conditions are true

find the details of students, who got marks more than 70 and less than 100Select * from studentdetails Where Marks >70 and Marks<100;

NOT logical operator


Reversed the result of logical expressionFind rows that do not satisfy a conditionFind details of the students who are not

Studying MathsSELECT *

FROM studentdetails WHERE NOT sub1= ‘Maths' ;

Combining OR and AND Operators


• When the AND operator is combined with OR• Oracle server will evaluate the condition connected

by the AND first before any conditions connected with OR.

• Parenthesis must be used to force an order of operation.

Find out details of the students Who studying Maths or Science And marks >=70 SELECT *

FROM studentdetails where Sub1=‘Maths’ or sub1 =‘Science’ AND Marks>=70 SELECT *

FROM studentdetails where ( Sub1=‘Maths’ or sub1 =‘Science’) AND Marks>=70


Employee

Sales-master

List all employee who stay in city ‘Banglore’ or ‘Manglore’

List details of employee who are not inhabit in the state of ‘Maharashtra’

List the products whose selling price is more than 500 and less than 750.

Calculate a new selling price as basic_cost plus profit rate and rename this column in output as new_price

First name Last Name Address1 City State

P_no P_name Basic_cost Profit_rate Selling _Price

Range searching


IN and NOT IN operatortesting a condition by comparing it with a list of

fixed values.column value is equal to any one of a specified set

of valuesfind the names of students who are studying either

Maths or Science,SELECT * FROM studentdetails WHERE sub1 IN ('Maths', 'Science');


Find out details of students who got marks either 90 or 80 or 60

Select * from studentdetails where marks in (90,80,60);

Select * from studentdetails where marks not in (90,80,60);

BETWEEN and NOT BETWEEN operator


Tests an expression against a rangeIn between two valuesThis eliminates the need to use a more complex

WHERE clause involving the use of the AND logical operator.

Find out details of students who got marks in the range 70 to 90Select * from studentdetails where marks BETWEEN 70 AND 90 Select * from studentdetails where marks NOT BETWEEN 70 AND 90

LIKE AND NOT LIKE operator


used to search for data rows containing incomplete or partial character strings within a data column.

When one does not know the exact

value for the search conditionsFind the details of employee

Whose name start with ‘pa’Select * from employee

Where empname1 like ‘pa%’;

Select * from employee

Where empname1 not like ‘pa%’;

LIKE AND NOT LIKE operator


Symbol Represents

% match any string of any length

_ match on a single character

[ ] (brackets) any single character within a specified range

such as 'a' to 'd', inclusive [a-d] or a set of characters such as [aeiouy]

[^] (not brackets)

any single character not in the specified range or set. (e.g., [^a-f] )

LIKE '%en%' will search for every word that has the letters 'en' in it (Green, Benten)LIKE '_heryl' will search for every six-letter name ending with 'heryl' (Cheryl).


LIKE '[CK]ars[eo]n ' will search for every six-letter name begins with a 'C' or 'K' (not both of them) and Second last letter should be 'e' or 'o' (e.g., 'Carsen,' 'Karsen,' 'Carson,' and 'Karson‘)

LIKE '[P-T]inger' will search for all the names ending with 'inger‘ begin with any single letter ‘P' thru ‘T' (Singer,

Ringer).LIKE 'M[^c]%' will search for all the names

begin with 'M' not having 'c' as the second letter.

SQL GROUP Functions


built-in SQL functions that operate on groups of rows return one value for the entire group. COUNT, MAX, MIN, AVG, SUM, DISTINCTCOUNT ():Returns the number of rows in the table that satisfies the

condition specified in the WHERE condition. SELECT COUNT(marks) from

Studnetdetails Where marks>70SELECT COUNT(*) from

studentdetails.

SQL GROUP Functions


MAX(): used to get the maximum value from a column.

To get the maximum marks SELECT MAX(MARKS) from studentdetails

MIN(): is used to get the minimum value from a column.

To get the minimum marks SELECT MIN(MARKS) from studentdetails

SQL GROUP Functions


AVG(): The average value of a numeric column.To get the average marks, SELECT AVG (marks) FROM

studentdetail;

SUM(): sum of a numeric columnTo get the total marks given out to the students,SELECT SUM (marks) FROM studentdetail;

DISTINCT(): to select the distinct rows.SELECT distinct(marks) from studentdetail;


List out the names of students having ‘a’ as the second letter in their name

Count the total number of students.Determine max and min marks of the students.Count the number of students who got more than

50 marks in science subject

Mathematical functionsCEIL (or CEILING) and FLOOR round the numbersCEIL roundup to the nearest integer value FLOOR round down to the next least integer

value.SELECT CEIL(10.19) AS ceil_value,FLOOR(10.19) AS floor_valueOUTPUT ceil_value floor_value---------- ----------- 11 10


RAND Functionused to generate some random numbers at run

timeSELECT RAND() AS random_value

OUTPUTrandom_value ---------------0.4179369289595


ABS functionfunction returns the absolute value of an numeric

input argument.EXSELECT ABS(-10) as arg1, ABS(10) AS arg2OUTPUT arg1 arg2 ------ ------ 10 10


SQRT functionextracts the square root from the positive

numeric input argument. SELECT SQRT(100) AS sqrt_of_100Output

Sqrt of 10010

POWER functionSELECT POWER(10,2) AS power_valueOUTPUTPower_value100


ROUND Function rounds a number to a specific length or precisionSELECT ROUND(10.09 ,1)

positive_rounded_value, ROUND(10.09 ,-1)

negative_rounded_value

OUTPUTpositive_rounded_value

negative_rounded_value---------------------- ---------------------- 10.1 10


Character Functions

CharacterCharacterfunctionsfunctions

LOWERLOWER

UPPERUPPER

INITCAPINITCAP

CONCATCONCAT

SUBSTRSUBSTR

LENGTHLENGTH

INSTRINSTR

LPADLPAD

Case conversion Case conversion functionsfunctions

Character manipulationCharacter manipulationfunctionsfunctions

String function


LOWER()

Converts string into lowercase

Select LOWER(‘ABCD’) from dual

UPPER() Converts string into uppercase

Select UPPER(‘abc’) from dual

INITCAP()

Converts first letter of each word in uppercase

Select INITCAP(‘abcd’) from dual


LTRIM(string,trimlist) & RTRIM(string,trimlist) Returns string with the leftmost or rightmost characters

that match the characters in trimlist

SUBSTR(string,pos,len)Returns the substring of string which begins at pos and is

len characters long

ASCII()Return numeric value of left-most character


INSTR() Return the index of the first occurrence of substring

LENGTH(string)Return the length of a string in characters

LPAD(string, length, pad) & RPAD(string, length, pad)

Return the string argument, left-padded or right padded with the specified string


REPLACE(string,target,replacement)Returns string with all occurrences of target replaced

with replacement

REVERSE(str)Returns the string str with the order of the characters

reversed.

CONCAT(str1,str2)Returns the string that results from concatenating the

arguments.

Working with Dates

Oracle stores dates in an internal numeric format: century, year, month, day, hours, minutes, seconds.

The default date format is DD-MON-YY.SYSDATE is a function returning date and

time.DUAL is a dummy table used to view

SYSDATE.

Date Functions

Number of monthsbetween two dates

MONTHS_BETWEEN

ADD_MONTHS

NEXT_DAY

LAST_DAY

Add calendar months to date

Next day of the date specified

Last day of the month

Function Description

• MONTHS_BETWEEN ('01-SEP-95','11-JAN-94')MONTHS_BETWEEN ('01-SEP-95','11-JAN-94')

Using Date FunctionsUsing Date Functions

• ADD_MONTHS ('11-JAN-94',6)ADD_MONTHS ('11-JAN-94',6)

• NEXT_DAY ('01-SEP-95','FRIDAY') NEXT_DAY ('01-SEP-95','FRIDAY')

• LAST_DAY('01-SEP-95')LAST_DAY('01-SEP-95')

19.677419419.6774194

'11-JUL-94''11-JUL-94'

'08-SEP-95''08-SEP-95'

'30-SEP-95''30-SEP-95'

intorduction sql

Documents