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
|
|  Yüklə 164.5 Kb.
|
|
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;
------------------------------ ---------- 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 ------------------------------ Vera A Farmiga Samuel Henry
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';
------------------------------ ---------- ---------- ------- 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;
------------------------- ------------------------------ Catherine Winder Catherine Winder
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 -------------------------------------------------- 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 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
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.
Product Join The simplest form of join , that term is a synonym called as Cartesian product or “Product” SQL Query: Movie CROSS JOIN StarsIn;
TITLE YEAR LENGTH INCOLOR ------------------------------ ---------- ---------- ------- STUDIONAME PRODUCERC# ------------------------------------------------------------ ---------- MOVIETITLE MOVIEYEAR STARNAME ------------------------- ---------- ------------------------------ Up 2009 Vera A Farmiga
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).
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> 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;
------------------------- -------------------------------------------------- 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)
Get the total number of Moviestars. SQL> SELECT COUNT(*) from Moviestar;
---------- 12 SQL> SELECT COUNT(Distinct Name) from Moviestar; COUNT(DISTINCTNAME) ------------------- 11
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
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;
------------------------------------------------------------ ----------- 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
Get the Computing length of Movies of Each Procducers SQL> SELECT name, SUM (length) FROM MovieExec, Movie WHERE producerC# = cert# GROUP BY name;
------------------------- ----------- 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;
------------------------- ----------- Peter Jackson 200 Danny Boyle 242 Catherine Winder 121
SELECT [DISTINCT] field(s) FROM table(s) [WHERE predicate] [GROUP BY field(s) [HAVING predicate]] [ORDER BY field(s)];
Get all MovieExec Columns whose names begin with the letter ‘J’. SQL> SELECT MovieExec.* FROM MovieExec WHERE MovieExec.NAME LIKE '%';
------------------------- -------------------------------------------------- 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"
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';
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;
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
------------------------- ---------- ----------------------- 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
------------------------- ---------- ----------------------- Source Code 2010 Vera A Farmiga
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');
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 :
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)
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 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[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 - |