S Q L
(Structured Query Language )
SQL - Original spelled SEQUEL - was first defined by Chamberlain and others at the IBM Research Laboratory in San Jose, California.
A prototype implementation of the language was built at the IBM Sam Jose Laboratory, under the name "System R". Most relational systems built today are based on SQL, including DB2, ORACLE, Sybase, SQL server etc.
S Q L
(Structured Query Language )
SQL is a set-level language which is "nonprocedural", users specify what data they need but not how to retrieve it. In other words, the process of "navigating" around the physical database to locate the desired data is performed automatically by the system, not manually by the user
MODE OF USE :
1. Interactive
2.Application Programming
SQL - Data Manipulation
The DML of SQL includes the following statements :
SELECT DELETE
UPDATE INSERT
SELECT
General format :
SELECT [DISTINCT] field(s)
FROM table(s)
[WHERE predicate]
[GROUP BY field(s) [HAVING predicate]]
[ORDER BY field(s)];
Simple Queries
Example 1:
SELECT *
FROM MOVIE;
TITLE YEAR LENGTH INCOLOR
------------------------------ ---------- ---------- -------
STUDIONAME PRODUCERC#
------------------------------------------------------------ ----------
Avatar 2010 115 color
lightstorm Dune entertainment ingenious Film partmenrs Mov0900
Inception 2010 109 color
Warner bros Pictures Mov0901
Toy Story 3 2010 103 color
Pixar animations studio Mov0902
TITLE YEAR LENGTH INCOLOR
------------------------------ ---------- ---------- -------
STUDIONAME PRODUCERC#
------------------------------------------------------------ ----------
Lord of rings 2003 200 color
Saul Zantez Company Mov0903
StarWars 1980 121 color
Lucas Films Mov0904
Wall-e 2008 98 color
walt disney studio pixar animations Mov0905
TITLE YEAR LENGTH INCOLOR
------------------------------ ---------- ---------- -------
STUDIONAME PRODUCERC#
------------------------------------------------------------ ----------
the prestige 2009 130 color
New Market films syncopy films Mov0906
up 2009 96 color
Pixar animations studio Mov0907
Slumdog Millionaire 2008 121 color
celador Films Film4 Mov0908
TITLE YEAR LENGTH INCOLOR
------------------------------ ---------- ---------- -------
STUDIONAME PRODUCERC#
------------------------------------------------------------ ----------
The Wrestler 2008 109 color
wildBunch Saturn Films Mov0909
SELECT *
FROM MOVIEEXEC;
NAME
------------------------------
ADDRESS CERT# NETWORTH
-------------------------------------------------- ---------- ----------
James Cameron
7920 sunset Blvd, Los Angles,CA,USA Mov0900 237000000
Peter Jackson
346, vegel street Roslyn Palmeston N 4409,NZ Mov0903 94000000
Catherine Winder
458 N 5th Street, NY, USA Mov0904 11000000
NAME
------------------------------
ADDRESS CERT# NETWORTH
-------------------------------------------------- ---------- ----------
Danny Boyle
1148 Albert Embankment LONDON, SE1 7TP, UK Mov0908 15100000
SELECT *
FROM MOVIESTAR;
NAME ADDRESS
------------------------- --------------------------------------------------
GENDER BIRTHDATE
------ ---------
Samuel Henry England,UK
MALE 02-AUG-76
George Clooney Lexington Kentucky,US
MALE 06-MAY-61
Vera A Farmiga Passic County,New Jersey,US
FEMALE 06-AUG-73
NAME ADDRESS
------------------------- --------------------------------------------------
GENDER BIRTHDATE
------ ---------
Anil Kapoor Mumbai Maharasthra,INDIA
MALE 24-DEC-59
Viggo Peter Mortensen NewYork City,New York,US
MALE 20-OCT-58
Elijab Jordan Wood Cedar Rapids Iowa,US
MALE 28-JAN-81
NAME ADDRESS
------------------------- --------------------------------------------------
GENDER BIRTHDATE
------ ---------
Sean Astin Santa Monica,California,US
MALE 25-JAN-71
Leonardo Dicaprio Los Angles,California,US
MALE 11-NOV-74
Ellen Philpotts Halifax, Nova Scotia,CANADA
FEMALE 21-FEB-87
9 rows selected.
Example 2 :
Get to produce only the movie title and length from Movie Table.
SELECT TITLE,LENGTH
FROM Movie
WHERE studioName = ‘Lucas Films’ AND year = 1980;
SQL> Select title, length from Movie WHERE StudioName ='Lucas Films' AND year = 1980;
TITLE LENGTH
------------------------------ ----------
StarWars 121
In the Movie Schema Lets search for all movies with a possessive(S) in their titles.
SQL> Select title from Movie WHERE title LIKE 'S%';
TITLE
------------------------------
StarWars
Slumdog Millionaire
To Get all title Movie from the table.
SELECT TITLE
FROM MOVIE;
SQL> Select title from Movie;
TITLE
------------------------------
Avatar
Inception
Toy Story 3
Lord of rings
StarWars
Wall-e
the prestige
up
Slumdog Millionaire
The Wrestler
10 rows selected.
SQL does not eliminate duplicates from the result of a select statement. To achieve this, you should use the keyword
DISTINCT, as in;
SELECT DISTINCT STARNAME
FROM STARSIN;
SQL> select Distinct Starname from starsin;
STARNAME
------------------------------
Leonardo Dicaprio
Vera A Farmiga
Samuel Henry
Qualified Queries
Example 1 :
Get the name and address of the Moviestar and executive with same name and address.
SELECT MOVIESTAR.NAME, MOVIEEXEC.NAME
FROM MOVIESTAR< MOVIEEXEC
WHERE
MOVIESTAR.ADDRESS=MOVIEEXEC.ADDRESS
SQL> SELECT MovieStar.name, MovieExec.name FROM MovieStar, MovieExec WHERE MovieStar.address = MovieExec.address;
NAME NAME
------------------------- ------------------------------
Catherine Winder Catherine Winder
Example 2 :
To find out the Tuple from Movie where name ‘ Lord of Rings’ and its studio name is Saul Zantez Company.
We have to write a SQL Query to extract the Database
SELECT * FROM Movie
WHERE Title= ‘Lord of Rings’ AND studioName=“Saul Zentez Company’;
SQL> Select * from Movie Where Title='Lord of rings' and Studioname='Saul Zantez Company';
TITLE YEAR LENGTH INCOLOR
------------------------------ ---------- ---------- -------
STUDIONAME PRODUCERC#
------------------------------------------------------------ ----------
Lord of rings 2003 200 color
Saul Zantez Company Mov0903
Products and Joins in SQL
Suppose we want to know the Name of the producer of StarWars. to answer this question we need the following two relations from our running example:
Movie(Title, Year, Length, Incolor, StudioName, ProducerC#)
MovieExec (name, address, cert#, networth)
SQL> Select name from Movie, MovieExec WHERE title = 'StarWars' AND ProducerC# = Cert#;
NAME
------------------------------
Catherine Winder
Schema if we have to find the name and address of the Moviestar and executive with same name and address. We can use following Query (using dot operator)
SELECT MovieStar.name, MovieExec.name
FROM MovieStar, MovieExec
WHERE MovieStar.address = MovieExec.address;
SQL> SELECT MovieStar.name, MovieExec.name FROM MovieStar, MovieExec WHERE MovieStar.address = MovieExec.address;
NAME NAME
------------------------- ------------------------------
Catherine Winder Catherine Winder
Asked for a star and an executive sharing an address, we want to know about two stars who share an address.
SQL> SELECT Star1.name, Star2.name FROM Moviestar Star1, Moviestar Star2 WHERE Star1.address = Star2.address AND Star1.name < St
no rows selected
Nested Loops
A relation name that is not aliased is also a tuple variable ranging over the relation itself, “ Tuple variables and Relation names”. If there are several tuple variables, we may imagine nested loops, one for each tuple variable, in which the variables each range over the tuples of respective relations.
We produce a tuple consisting of the values of the expressions following SELECT; note that each term is given a value by the current assignment of tuples to tuple variables.
Example :- Nested Loops
Union, Intersection, and Difference of Queries
SQL provides operators that apply to the result of queries, provided those queries produce relations with the same list of attributes and attributes types
Union
SQL> (SELECT name, address FROM MovieStar) UNION (SELECT name, address FROM MovieExec);
NAME
------------------------------
ADDRESS
--------------------------------------------------
Anil Kapoor
Mumbai Maharasthra,INDIA
Catherine Winder
458 N 5th Street, NY, USA
Catherine Winder
458 N 5th Street, NY, USA
NAME
------------------------------
ADDRESS
--------------------------------------------------
Danny Boyle
1148 Albert Embankment LONDON, SE1 7TP, UK
Elijab Jordan Wood
Cedar Rapids Iowa,US
Ellen Philpotts
Halifax, Nova Scotia,CANADA
NAME
------------------------------
ADDRESS
--------------------------------------------------
George Clooney
Lexington Kentucky,US
James Cameron
7920 sunset Blvd, Los Angles,CA,USA
Leonardo Dicaprio
Los Angles,California,US
NAME
------------------------------
ADDRESS
--------------------------------------------------
Peter Jackson
346, vegel street Roslyn Palmeston N 4409,NZ
Samuel Henry
England,UK
Sean Astin
Santa Monica,California,US
NAME
------------------------------
ADDRESS
--------------------------------------------------
Vera A Farmiga
Passic County,New Jersey,US
Viggo Peter Mortensen
NewYork City,New York,US
catherine Winder
458 N 5th street, NY, USA
NAME
------------------------------
ADDRESS
--------------------------------------------------
catherine Winder
458 N 5th street,NY,USA
16 rows selected.
Intersection
Intersection Operation Select the common tuples in both the tables.
SQL> (SELECT name, address FROM MovieStar) Intersect (SELECT name, address FROM MovieExec);
No rows selected.
Suppose we wanted the names and addresses of all male movie stars Who are also movie executives with a net worth of $10,000,000. Using the following two relations:
MovieStar (name, address, gender, birthdate)
MovieExec(name, address, cert#, networth)
1) (SELECT name, address
2) FROM MovieStar
3) WHERE gender = 'MALE')
4) INTERSECT
5) (SELECT name, address
6) FROM MovieExec
7) WHERE networth > 1000000);
SQL> (SELECT name, address FROM MovieStar WHERE gender = 'FEMALE') INTERSECT (SELECT name, address FROM MovieExec WHERE networth > 1000000)
no rows selected
SubQueries
In SQL, one query can be used in various ways help in the evaluation of another. A query that is part of another is called a subquery.
Example :- The subquery is will select the producerC# from Movie where title is stars wars and then it will compare the producerC# with Cert# and select Name from MovieExec.
SQL> SELECT name FROM MovieExec WHERE cert# = (SELECT producerC# FROM Movie WHERE title = 'Lord of rings');
NAME
------------------------------
Peter Jackson
-
Movie(title, year, length, incolor, studioName, producerC#)
-
StarsIn (movieTitle, movieyear, starName)
-
MovieExec (name, address, cert#, networth)
SUBQuery of three Relations
SQL> SELECT name
2 FROM MovieExec
3 WHERE cert# IN
4 (SELECT producerC#
5 FROM Movie
6 WHERE(title,year)IN
7 (SELECT movietitle, movieyear
8 FROM StarsIn
9 WHERE starName = 'Samuel Henry'
10 )
11 );
no rows selected
For each of these tuples, the name of the producer is returned, giving us the set of producers of ‘Samuel Henry’ movies.
Example: - Subqueries in FROM Clause
SQL> 1 SELECT name
2 FROM MovieExec, (SELECT producerC#
3 FROM Movie, StarsIn
4 WHERE title = movieTitle AND
5 year = movieyear AND
6 starName = 'Leonardo Dicaprio'
7 ) Prod
8 WHERE cert# = Prod.producerC#;
no rows selected
The subquery joins Movie and starsIn and condition in WHERE clause and further more WHERE condition on outer selection is there which returns set of producers of the movies and alias is mentioned here as Prod.
JOIN Queries
We can perform various Join operation on two relation to get the required result from the SQL query.
-
There are different variants to perform these operations
-
Product
-
Natural Join
-
Theta Join
-
Outer Join
Product Join
The simplest form of join , that term is a synonym called as Cartesian product or “Product”
SQL Query: Movie CROSS JOIN StarsIn;
-
Movie(title, year, length, incolor, studioName, producerc#)
-
StarsIn(movieTitle, movieyear, starName)
TITLE YEAR LENGTH INCOLOR
------------------------------ ---------- ---------- -------
STUDIONAME PRODUCERC#
------------------------------------------------------------ ----------
MOVIETITLE MOVIEYEAR STARNAME
------------------------- ---------- ------------------------------
Up 2009 Vera A Farmiga
The Wrestler 2008 109 color
wildBunch Saturn Films Mov0909
Source Code 2010 Vera A Farmiga
250 rows selected.
We can have cross join among these relations and the final product can result in NINE- Columns of both the relations ( Movie and StarsIn).
Theta Join
-
Theta Join is obtained with the Keyword ON
-
We put Join between R and S the two relations with Keyword ON.
Natural Join
In the equijoin, if one of the two identical columns is eliminated, then what is left is called the natural join. Natural join is probably the single most useful form of join and is usually referred to as simply "Join"
SQL> select * from moviestar natural join movieexec;
NAME ADDRESS
------------------------- --------------------------------------------------
GENDER BIRTHDATE CERT# NETWORTH
------ --------- ---------- ----------
Catherine Winder 458 N 5th Street, NY, USA
FEMALE 04-NOV-87 Mov0904 11000000
Outer Join
Natural Full Outer Join
The outer join operator is a way to augment the result of the join by the dangling tuples, padded with NULL values
SQL> select * from moviestar natural full outer join movieexec;
NAME ADDRESS
------------------------- --------------------------------------------------
GENDER BIRTHDATE CERT# NETWORTH
------ --------- ---------- ----------
Samuel Henry England,UK
MALE 02-AUG-76
George Clooney Lexington Kentucky,US
MALE 06-MAY-61
Vera A Farmiga Passic County,New Jersey,US
FEMALE 06-AUG-73
NAME ADDRESS
------------------------- --------------------------------------------------
GENDER BIRTHDATE CERT# NETWORTH
------ --------- ---------- ----------
Anil Kapoor Mumbai Maharasthra,INDIA
MALE 24-DEC-59
Viggo Peter Mortensen NewYork City,New York,US
MALE 20-OCT-58
Elijab Jordan Wood Cedar Rapids Iowa,US
MALE 28-JAN-81
NAME ADDRESS
------------------------- --------------------------------------------------
GENDER BIRTHDATE CERT# NETWORTH
------ --------- ---------- ----------
Sean Astin Santa Monica,California,US
MALE 25-JAN-71
Leonardo Dicaprio Los Angles,California,US
MALE 11-NOV-74
Ellen Philpotts Halifax, Nova Scotia,CANADA
FEMALE 21-FEB-87
NAME ADDRESS
------------------------- --------------------------------------------------
GENDER BIRTHDATE CERT# NETWORTH
------ --------- ---------- ----------
catherine Winder 458 N 5th street,NY,USA
FEMALE 04-NOV-87
catherine Winder 458 N 5th street, NY, USA
FEMALE 04-NOV-87
Catherine Winder 458 N 5th Street, NY, USA
FEMALE 04-NOV-87 Mov0904 11000000
NAME ADDRESS
------------------------- --------------------------------------------------
GENDER BIRTHDATE CERT# NETWORTH
------ --------- ---------- ----------
Peter Jackson 346, vegel street Roslyn Palmeston N 4409,NZ
Mov0903 94000000
Danny Boyle 1148 Albert Embankment LONDON, SE1 7TP, UK
Mov0908 15100000
Danny Boyle 1148 Albert Embankment LONDON, SE1 7TP, UK
Mov0908 15100000
NAME ADDRESS
------------------------- --------------------------------------------------
GENDER BIRTHDATE CERT# NETWORTH
------ --------- ---------- ----------
James Cameron 7920 sunset Blvd, Los Angles,CA,USA
Mov0900 237000000
16 rows selected.
The resulted relation will include the schema with name & address plus all other attributes in the two relations.
Natural LEFT Outer Join
-
SQL also provides left and right outer join.
-
If we want to use them , then we have to use LEFT or RIGHT keyword in Query in place of FULL.
SQL> select * from moviestar natural left outer join movieexec;
NAME ADDRESS
------------------------- -------------------------------------------------
GENDER BIRTHDATE CERT# NETWORTH
------ --------- ---------- ----------
Catherine Winder 458 N 5th Street, NY, USA
FEMALE 04-NOV-87 Mov0904 11000000
Samuel Henry England,UK
MALE 02-AUG-76
Elijab Jordan Wood Cedar Rapids Iowa,US
MALE 28-JAN-81
NAME ADDRESS
------------------------- -------------------------------------------------
GENDER BIRTHDATE CERT# NETWORTH
------ --------- ---------- ----------
Anil Kapoor Mumbai Maharasthra,INDIA
MALE 24-DEC-59
Vera A Farmiga Passic County,New Jersey,US
FEMALE 06-AUG-73
catherine Winder 458 N 5th street,NY,USA
FEMALE 04-NOV-87
NAME ADDRESS
------------------------- -------------------------------------------------
GENDER BIRTHDATE CERT# NETWORTH
------ --------- ---------- ----------
catherine Winder 458 N 5th street, NY, USA
FEMALE 04-NOV-87
Viggo Peter Mortensen NewYork City,New York,US
MALE 20-OCT-58
George Clooney Lexington Kentucky,US
MALE 06-MAY-61
NAME ADDRESS
------------------------- -------------------------------------------------
GENDER BIRTHDATE CERT# NETWORTH
------ --------- ---------- ----------
Sean Astin Santa Monica,California,US
MALE 25-JAN-71
Ellen Philpotts Halifax, Nova Scotia,CANADA
FEMALE 21-FEB-87
Leonardo Dicaprio Los Angles,California,US
MALE 11-NOV-74
12 rows selected.
Natural RIGHT Outer Join
SQL> select * from moviestar natural right outer join movieexec;
NAME ADDRESS
------------------------- --------------------------------------------------
GENDER BIRTHDATE CERT# NETWORTH
------ --------- ---------- ----------
Catherine Winder 458 N 5th Street, NY, USA
FEMALE 04-NOV-87 Mov0904 11000000
James Cameron 7920 sunset Blvd, Los Angles,CA,USA
Mov0900 237000000
Danny Boyle 1148 Albert Embankment LONDON, SE1 7TP, UK
Mov0908 15100000
NAME ADDRESS
------------------------- --------------------------------------------------
GENDER BIRTHDATE CERT# NETWORTH
------ --------- ---------- ----------
Danny Boyle 1148 Albert Embankment LONDON, SE1 7TP, UK
Mov0908 15100000
Peter Jackson 346, vegel street Roslyn Palmeston N 4409,NZ
Mov0903 94000000
Moviestar NATURAL LEFT OUTER JOIN MovieExec;
This is result in first two tuples of the two Schema out of 6 tuples
MovieStar NATURAL RIGHT OUTER JOIN MovieExec;
This will yield to first and third tuples of the Full outer Join.
Built-In Functions
1 - COUNT number of values in the column
2 - SUM sum of the values in the column
3 - AVG average of the values in the column
4 - MAX largest value in the column
5 - MIN smallest value in the column
(Find the list for Oracle)
Example 1 :
Get the total number of Moviestars.
SQL> SELECT COUNT(*) from Moviestar;
COUNT(*)
----------
12
SQL> SELECT COUNT(Distinct Name) from Moviestar;
COUNT(DISTINCTNAME)
-------------------
11
Example 2 :
Get Title of Movie where length is greater than 120
SELECT TITLE
FROM MOVIE WHERE LENGTH > 120;
TITLE
------------------------------
Lord of rings
StarWars
the prestige
Slumdog Millionaire COUNT(*)
----------
4
SQL> Select ProducerC# From Movie Where Title='Lord of rings';
PRODUCERC#
----------
Mov0903
SQL> Select Title from Movie where length > 120;
|
Example 3 :
If we have to find the length of all movies for each studio. Then SQL query will be
SQL> SELECT studioName, SUM(length) FROM Movie GROUP BY studioName;
STUDIONAME SUM(LENGTH)
------------------------------------------------------------ -----------
lightstorm Dune entertainment ingenious Film partmenrs 115
New Market films syncopy films 130
Warner bros Pictures 109
celador Films Film4 121
Lucas Films 121
Saul Zantez Company 200
wildBunch Saturn Films 109
walt disney studio pixar animations 98
Pixar animations studio 199
9 rows selected.
Example 4 :
Get the Computing length of Movies of Each Procducers
SQL> SELECT name, SUM (length) FROM MovieExec, Movie WHERE producerC# = cert# GROUP BY name;
NAME SUM(LENGTH)
------------------------- -----------
Peter Jackson 200
Danny Boyle 242
Catherine Winder 121
James Cameron 115
Example 5 :
Get to print the total film length for only those producers who made at least one film prior to 2010.
SQL> SELECT name, SUM(length) FROM MovieExec, Movie WHERE producerC# = cert# GROUP BY name HAVING MIN(year) < 2010;
NAME SUM(LENGTH)
------------------------- -----------
Peter Jackson 200
Danny Boyle 242
Catherine Winder 121
General format :
SELECT [DISTINCT] field(s)
FROM table(s)
[WHERE predicate]
[GROUP BY field(s)
[HAVING predicate]]
[ORDER BY field(s)];
Advanced features
Example 6:
Get all MovieExec Columns whose names begin with the letter ‘J’.
SQL> SELECT MovieExec.* FROM MovieExec WHERE MovieExec.NAME LIKE '%';
SQL> SELECT MovieExec.* FROM MovieExec WHERE MovieExec.NAME LIKE 'J%';
NAME ADDRESS
------------------------- --------------------------------------------------
CERT# NETWORTH
---------- ----------
James Cameron 7920 sunset Blvd, Los Angles,CA,USA
Mov0900 237000000
In general, a "LIKE predicate" takes the form
column-name LIKE character-string-constant
Characters within that constant are interpreted as follows :
* The _ character ( break or underscore ) stands for any single character.
* The % character (percent) stands for any sequence of n characters ( where n may be zero )
* All other characters simply stand for themselves.
Example 7 :
ADDRESS LIKE '%California%'
will evaluate to true if address contains the sting "Berkeley" anywhere inside it.
SAM LIKE 'S_ _'
will evaluate to true if SAM is exactly three characters long and the first is an "S".
CITY NOT LIKE '%E%'
will evaluate to true if CITY does not contain an "E"
Null Value
1.Value unknown
2.Value inapplicable: For example spouse column for single man.
3.Value Withhold
Null Value
Update Operations
Update
General format :
UPDATE TABLE
SET field = expression, [field = expression]...
[WHERE predicate];
Example 8 :
Change Movieyear to 1998 from Starsin where movietitle is ‘Down to Bone’
SQL> Update Starsin set Movieyear=1998 where Movietitle='Down to Bone';
SQL> Update Starsin set Movieyear=1998 where Movietitle='Down to Bone';
1 row updated.
SQL> select * from Starsin;
MOVIETITLE MOVIEYEAR STARNAME
------------------------- ---------- ------------------------------
Blood Diamond 2006 Leonardo Dicaprio
The Quick and the Dead 1995 Leonardo Dicaprio
Titanic 1997 Leonardo Dicaprio
The Departed 2006 Leonardo Dicaprio
Body of lies 2008 Leonardo Dicaprio
Inception 2010 Leonardo Dicaprio
Somersault 2004 Samuel Henry
Macbeth 2006 Samuel Henry
Love my Way 2006 Samuel Henry
The Great Raid 2005 Samuel Henry
Terminator Salvation 2009 Samuel Henry
MOVIETITLE MOVIEYEAR STARNAME
------------------------- ---------- ------------------------------
Avatar 2009 Samuel Henry
Perseus 2010 Samuel Henry
Return to Paradise 1998 Vera A Farmiga
Autumn in New York 2000 Vera A Farmiga
Dust 2001 Vera A Farmiga
Down to Bone 1998 Vera A Farmiga
Mind the Gap 2004 Vera A Farmiga
Breaking and Entering 2005 Vera A Farmiga
The Departed 2006 Vera A Farmiga
Never Forever 2007 Vera A Farmiga
Nothing but the Truth 2008 Vera A Farmiga
MOVIETITLE MOVIEYEAR STARNAME
------------------------- ---------- ------------------------------
Orphan 2008 Vera A Farmiga
Up 2009 Vera A Farmiga
Source Code 2010 Vera A Farmiga
25 rows selected.
|
DELETE
General format :
DELETE
FROM table
[WHERE predicate];
Example 9 : Delete Movie Title ‘ Avatar’ from Movie
DELETE
FROM Movie
WHERE Title = 'Avatar';
What type of problems may arise due to this delete operation (if any)?
SQL> DELETE FROM Movie WHERE Title = 'StarWars';
DELETE FROM Movie WHERE Title = 'StarWars'
*
ERROR at line 1:
ORA-02292: integrity constraint (SYSTEM.SYS_C0011564) violated - child record
found
Example 10 :
Delete all Movies Released in year 1998.
SQL> Delete from Starsin where Movieyear=1998;
2 rows deleted.
SQL> select * from starsin;
MOVIETITLE MOVIEYEAR STARNAME
------------------------- ---------- -----------------------
Blood Diamond 2006 Leonardo Dicaprio
The Quick and the Dead 1995 Leonardo Dicaprio
Titanic 1997 Leonardo Dicaprio
The Departed 2006 Leonardo Dicaprio
Body of lies 2008 Leonardo Dicaprio
Inception 2010 Leonardo Dicaprio
Somersault 2004 Samuel Henry
Macbeth 2006 Samuel Henry
Love my Way 2006 Samuel Henry
The Great Raid 2005 Samuel Henry
Terminator Salvation 2009 Samuel Henry
MOVIETITLE MOVIEYEAR STARNAME
------------------------- ---------- -----------------------
Avatar 2009 Samuel Henry
Perseus 2010 Samuel Henry
Autumn in New York 2000 Vera A Farmiga
Dust 2001 Vera A Farmiga
Mind the Gap 2004 Vera A Farmiga
Breaking and Entering 2005 Vera A Farmiga
The Departed 2006 Vera A Farmiga
Never Forever 2007 Vera A Farmiga
Nothing but the Truth 2008 Vera A Farmiga
Orphan 2008 Vera A Farmiga
Up 2009 Vera A Farmiga
MOVIETITLE MOVIEYEAR STARNAME
------------------------- ---------- -----------------------
Source Code 2010 Vera A Farmiga
23 rows selected.
Insert
General format 1 :
INSERT
INTO table [(field [,field]...)]
VALUES (constant [,constant]...);
general format 2 :
INSERT
INTO table [(field[,field]×××××)]
SELECT FROM WHERE
Example 11 :
Add Values to the column of table Movie
SQL> insert into movie(title, year,length,incolor,Studioname,producerC#) values ('Inception',2010,109,'color','Warner bros Pictures','Mov0901');
1 row created.
SQL> insert into movie(title, year,length,incolor,Studioname,producerC#) values ('Toy Story 3',2010,103,'color','Pixar animations studio','Mov0902');
1 row created.
SQL> insert into movie(title, year,length,incolor,Studioname,producerC#) values ('Lord of rings',2003,200,'color','Saul Zantez Company','Mov0903');
1 row created.
SQL> insert into movie(title, year,length,incolor,Studioname,producerC#) values ('StarWars',1980,121,'color','Lucas Films','Mov0904');
1 row created.
SQL - Data Definition
From the user's point of view, the principal data definition language (DDL) statements are as follow :
CREATE TABLE DROP TABLE
CREATE VIEW DROP VIEW
CREATE INDEX DROP INDEX
ALTER TABLE
Base Tables
A base table is a named table that exists in its own right-unlike a view which does not exist in its own right, but is derived from one or more base tables. A view is an alternative way of looking at base tables.
Create Table
General format :
CREATE TABLE base-table-name
(column-definition [,column-definition]...)
Where a "column-definition" takes the form :
column-name data-type [NOT NULL]
Example :
Create Table Movie
Name Null? Type
--------------------------- -------- ----------------------------
TITLE NOT NULL CHAR(30)
YEAR NOT NULL NUMBER(4)
LENGTH NOT NULL NUMBER(3)
INCOLOR NOT NULL CHAR(7)
STUDIONAME NOT NULL VARCHAR2(60)
PRODUCERC# NOT NULL VARCHAR2(10)
SQL> Create table Movie(title char(30) Not null, year number(4) not null, length number(3) not null, incolor char(7) not null, studioname varchar2(60) n
ot null, producerC# varchar2(10) not null primary key);
Table created.
Data Types
SQL supports the following data types :
CHAR(n) fixed length character string of length n characters
VARCHAR(n) varying length character string of maximum length n characters
SMALLINT signed halfword binary integer
INTEGER signed fullword binary integer
DECIMAL(P[Q,]) signed packed decimal number of P digits precision, with assumed decimal point Q digits from the right
FLOAT signed doubleword floating point number
Alter Table
General format :
ALTER TABLE base-table-name
ADD column-name data type;
Example :
ALTER TABLE Movie
ADD Releasedate Date;
Notice that the value of this new field is set to null and that only the description of the table is changed in system catalog. An existing record is not physically changed until an update is performed.
Some systems support additional forms of ALTER TABLE. For example, the data type of a column may be changed from, say, SMALLINT to INTEGER.
Drop Table
General format :
DROP TABLE base-table-name;
The specified table is removed from the system catalog. All indexes and views defined on that base table are automatically dropped also.
Create Index
General format :
CREATE[UNIQUE] INDEX index-name
ON base - table - name (column - name[order] [,column - name[order]...)
[cluster] ;
Each "order' specification is either ASC (ascending) or DESC (descending). Default is ASC. The left-to-right sequence of naming columns in the CREATE INDEX statement corresponds to major-to-minor ordering. The UNIQUE option in CREATE INDEX specifies that no two records in the indexed base table will be allowed to take on the same value for the indexed field(s) at the same time. Notice that the only statements that refer to indexes are CREATE INDEX and DROP INDEX. The decision as to whether or not to use an index is make by the system optimizer, not the user.
Example :
CREATE INDEX MovieExec#
ON MovieExec (Cert# DESC);
Drop Index
General format :
DROP INDEX index-name;
SQL -
|