slide1
Download
Skip this Video
Download Presentation
SQL

Loading in 2 Seconds...

play fullscreen
1 / 29

SQL - PowerPoint PPT Presentation


  • 94 Views
  • Uploaded on

SQL. Rohit Khokher. INTRODUCTION.

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about ' SQL' - fernando-castillo


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
slide1

SQL

Rohit Khokher

introduction

INTRODUCTION

Structured Query Language (SQL) is the standard command set that allows the users to interact with the relational database management systems. All task related to relational data management – creating tables, querying the database for information, modifying the data in database ,deleting them ,granting access to users and so on-can be easily performed using SQL.

advantages
ADVANTAGES
  • SQL is coded with embedded data . Programmer needs to simply specify what data is required and not how to retrieve it unlike procedural language.
  • Applications written in SQL can be easily ported across system.
  • SQL as a language provides a greater degree of abstraction and is independent of the way it is implemented internally. A query return same result whether optimizing has been done with indexes or not.
slide4
Language is simple , easy to learn ,can also handle complex situations.
  • Expected results are well defined and language has sound theoretical base and there is no ambiguity about the way a query will interrupt the data and produce the result.
  • SQL can also be used to define data structures , control access to the data , delete , insert and modify occurence of the data.
characteristics
CHARACTERISTICS
  • SQL has rules for embedding SQL statements into a general purpose programming language such as java or COBOL.
  • SQL is a non procedural language .
  • SQL is not case sensitive.
  • SQL has statements for data definition, query and update. Hence it is both a DDL and a DML.
  • SQL usage by its very nature is extremely flexible. There is no need to start SQL statements in a particular column or be finished in a single line.
sql commands
SQL COMMANDS

SQL

TCS

DDL

DML

DAS

DCL

DQL

sql commands1
SQL COMMANDS
  • SQL statements can broadly be classified into the following categories :-
  • Data Definition Language (DDL):- The commands used are CREATE, ALTER and DROP. The data definition statements are CREATE TABLE, CREATE VIEW, CREATE INDEX, ALTER TABLE , DROP TABLE , DROP VIEW and DROP INDEX.
  • Data Manipulation Language (DML):- SQL provides three data manipulation statements –INSERT, UPDATE , DELETE.
slide8
Data Query Language (DQL):- SQL has only one data query statement ,i.e. SELECT. It enables the users to query one or more tables to get the desired information.
  • Data Control Language (DCL):- The DBA has the authority to give and take the privileges to a specific user. The DCL commands are GRANT and REVOKE.
slide9
Data Administration Statements (DAS):- Two data administration commands are START AUDIT and STOP AUDIT. Note that data administration is different from database administration .Database administration is the overall administration of the database and data administration is only a subset of that.
  • Transaction Control Statements (TCS):- Some of the TCS are COMMIT , ROLLBACK , SAVEPOINT and SET TRANSACTION.
data types
DATA TYPES
  • Char (n):- A fixed character length string with user specified length.
  • Varchar (n):- A variable character length string with user specified maximum length n.
  • Int :- An integer.
  • Small Integer :- A small integer.
  • Numeric (p, d):- A fixed point number with user defined precision.
  • Real , double precision:- Floating point and double precision floating point numbers with machine dependent precision.
slide11
Float (n) :- A floating point number, with precision of at least n digits.
  • Date :- A calendar date containing a ( four digits) year , month and day of the month.
  • Time:- The time of day , in hours, minutes and seconds e.g. Time ’09:30:00’.
  • Number:-Number is used to store numbers (fixed or floating point).
creation of a table
Creation Of A Table
  • Create table Client_master
  • (
  • client_no varchar (06) ,
  • name varchar (20),
  • city varchar (15),
  • pincode number (06),
  • bal_due number (10,2)
  • );

 desc client_master /*to show all the columns*/

inserting values
Inserting Values
  • Insert into client_master values (‘0001’,’Amit’,’Mumbai’,400054,1500);
  • insert into client_master values (‘0002’,’Vandana’, ‘Chennai’,780001,0);
  • RETRIEVING OF DATA FROM TABLE :-SELECT * from client_master; /* to show all the rows of a table*/
client master
Client_master

Bal_due

Client_no

Name

City

Pincode

Amit

Mumbai

400054

15000

0001

780001

0

Chennai

Vandana

0002

5000

400057

Mumbai

Pramada

0003

0

400056

Mumbai

0004

Basu

100001

2000

Delhi

0005

Ravi

0

Mumbai

400050

0006

Rupa

queries
QUERIES
  • Find out the names of all the clients.

SQL> SELECT name

2 from client_master;

  • List all the clients who are located in ‘Mumbai’.

SQL> SELECT *

2 from client_master where

3 city= ‘Mumbai’ ; OR

SQL> SELECT *

2 from client_master where

3 city in (‘Mumbai’) ; OR

slide16
SQL> SELECT *

2 from client_master where

3 city like ‘M%’ ;

  • Display the information for client no 0001 and 0002.

SQL> SELECT *

2 from client_master where

3 client_no =0001 OR client_no=0002;

slide17
Delete the record with client 0001 from the client_master table.

SQL> DELETE from client_master

2 where client_no=0001;

Change the city of client_no 0005 to Mumbai.

SQL> UPDATE client_master

2 SET city=‘Mumbai’ where

3 client_no=0005;

slide18
Sorting of data in table:-

SQL> SELECT *

2 from client_master

3 ORDER BY name;

Grouping while selecting :-

SQL> SELECT *

2 from client_master

3 GROUP BY city;

aggregate functions
AGGREGATE FUNCTIONS

COUNT (*):- produces the number of rows on non NULL field values that the query selected. Ex:-

SQL> SELECT COUNT (*)

2 from client_master;

SUM():- produces the arithmetic sum of all selected values of a given field. Ex:-

SQL> SELECT SUM (bal_due)

2 from client_master;

  • AVG():- produces the average (mean) of all selected values of a given field. Ex:-
slide20
SQL> SELECT AVG (bal_due)

2 from client_master;

MAX():- produces the largest of all selected values of a given field.

MIN():- produces the smallest of all selected values of a given field. Ex:-

SQL> SELECT MAX (bal_due),

2 MIN (bal_due) from

3 client_master;

structure of table
STRUCTURE OF TABLE

PRIMARY KEY:-

SQL> ALTER TABLE client_master

2 ADD PRIMARY KEY (client_no);

  • FOREIGN KEY:-

SQL> ALTER TABLE department

2 FOREIGN KEY (dept_no)

3 references client_master;

Adding new columns:-

SQL> ALTER TABLE client_master

2 ADD ( address varchar(20));

  • UNIQUE:-

SQL> ALTER TABLE client_master

2 MODIFY ( pincode varchar(06));

slide22
NOT NULL:-

SQL> ALTER TABLE client_master

2 MODIFY ( client_no varchar(06));

  • DROP STATEMENT:-

SQL> DROP TABLE client_master;

  • Modifying existing table:-

SQL> ALTER TABLE client_master

2 MODIFY ( client_no varchar(10));

CHECK:-

SQL> ALTER TABLE student

2 ADD CHECK (marks>50);

joins
JOINS

Self Join:-

SQL> SELECT s.name ,t.name ,

2 s.courseid from student a,

3 student b where

4 a.courseid= b.courseid;

Left/Right outer join:-

SQL> SELECT course.courseid, cname ,

2 name from student LEFT/RIGHT

3 OUTER JOIN course on

4 student.courseid = course.courseid;

  • Cartesian Product:-

SQL> SELECT, student.*, course.*

2 from student , course;

set operations
SET OPERATIONS
  • Union:-

SQL> SELECT name, city from

2 student UNION SELECT sname,

3 city from head;

  • Difference:-

SQL> SELECT name from student

2 MINUS SELECT from head;

  • Intersect:-

SQL> SELECT name from

2 student INTERSECT SELECT sname from

3 head;

slide25
VIEW

You can present logical subsets or combinations of data by creating views of tables . A view is a logical table based on a table or another view. A view contains no data of its own but is like a window through which data from tables can be viewed or changed. The view is stored as a SELECT statement in the data dictionary.

  • Uses:-

:- To restrict data access.

:- To make complex queries easy.

:- To provide data independence.

:- To present different views of the same data.

slide26
SYNTAX:-

CREATE VIEW viewname as

SELECT columnname, columnname

from tablename;

Ex:-

SQL> CREATE VIEW c_view

2 SELECT name, city

3 from client_master;

Removing a View:-

SQL> DROP VIEW c_view ;

indexes
INDEXES

An index is an ordered list of a column or group of columns in a table. An index created on the single column of the table is called simple index. When multiple table columns are included in the index it is called composite index.

SYNTAX(SIMPLE):-

CREATE INDEX index_name

ON table name (column name);

Ex:-

SQL> CREATE INDEX client_index

2 ON client_master ( pincode);

slide28
Composite Index:-

CREATE INDEX index_name

ON table name (column name, column

name);

Ex:-

SQL> CREATE INDEX client_cindex

2 ON client_master (client_no, name);

Unique Index:-

Ex:-

SQL> CREATE UNIQUESTION INDEX c_index

2 ON client_master (state);

slide29
Dropping Indexes:- An index can be dropped by using DROP INDEX.

SYNTAX:-

DROP INDEX indexname;

Ex:-

SQL> DROP INDEX client_index;

ad