SQL Basics A primary key is a column or set of columns that uniquely identifies the rest of the data in any given row A foreign key is key is a column in a table where that column is a primary key of another table, which means that any data in a foreign key column must have corresponding data in the other table where that column is the primary key Data inconsistency: inconsistency: A Data field with same name but info does not match. (Occurs when Data Redundancy exists) Data dependency: When application depends on Data structure and has no flexibility. Data Redundancy: When a data item exists in several files (duplication) (Eliminated if using Normalized data structure) Data Independence: Data structures are defined separately from application programs. Relations: Two-dimensional tables of data values = Table Atomic: Values cannot be broken down any further. Domain: Values for attributes are drawn drawn from a domain. Atomic set of attributes. Ex: Date, City, etc. Candidate Key: Several keys that act as a subject for primary key. Concatenated key: Combination of attributes (from candidate keys) that forms the primary key. Alternate Keys: Candidate keys not chosen to be part of primary key. Entity integrity: No part of the the primary key can be missing. "NOT NULL" Referential Integrity:A Integrity:A foreign key must have applicable primary key in other table. Data Warehousing The Fundamentals Def: Single, complete and consistent consistent store of data obtained from from various sources. It is usually made of relational databases. It consists of: - A set of programs that extract data from an operational environment. - A database that maintains data warehouse data, - Systems that provide data to users. Functions: The main function of a data warehouse is to give end-users faster, easier, and more direct access to corporate data. Characteristics: - Data Warehouses are offline systems. Their information is not live and it is not continuously updated. - One of the big advantages of a warehouse implementation is its ability to store historical data. Codd Rules In 1985 Codd proposed an informal set of twelve rules by which a database could be evaluated to see how "relational" it is. Very few commercial commercial databases exist which meet meet or satisfy all twelve rules. The 12 rules are based on the following foundation rule. Rule 0: For any system that is advertised as, or claims to be, a relational database management system, that system must be able to manage databases databases entirely through through its relational capabilities. In other words, the DBMS should not have to rely on non-relational methods in order to manage its data. The other twelve rules are all implied in Rule 0, but it is easier to check for the other twelve individually than for this general rule. Rule 1: The information rule All information in a relational database is represented explicitly at the logical level and in exactly one way - by values in tables. This includes data about the database itself. Data about the database itself is kept in a data dictionary. Rule 2: The guaranteed access rule. Each and every datum (atomic value) in a relational database is guaranteed to be logically accessible by resorting to a combination of table name, primary key value, and column value. If a database conforms to rule 2, every every atomic value should be be easily retrievable. An atomic value is the smallest unit unit of value value in a relat relation ional al databa database. se. In a relati relationa onall databa database, se, an ato atomic mic value can always always be
retrieved if you know the column or attribute name, the table it is stored in, and the primary key's value. Rule 3: Systematic treatment of null values Null values (distinct from the empty character string or a string of blank characters and distinct from zero zero or any other other number number)) are suppo supporte rted d in a fully fully relati relationa onall DBMS DBMS for repres represent enting ing missin missing g information in a systematic way, independent of data type. A null value can mean that data is not there there is not known, or is irrelev irrelevant. ant. The null value value represent represents s empty databas database e fields. fields. There There is no value for that that field. It is different from zero or blank. A primary field should never have and empty field. This protects the integrity of the database. database. Rule 4: Query language The database description is represented at the logical level in the same way as ordinary data, so that authorized users can apply the same relational language to its interrogation as they apply to the regular data. In a relational database the the same query language is used on on the data dictionary as is used on the application database. Rule Rule 5: The compr comprehe ehensi nsive ve data data sub langua language ge rule rule A relati relationa onall system system may suppor supportt severa severall languages and various various modes of of terminal use (for example, example, the fill-in-the-blanks mode). mode). However, there must be one language whose statements are expressible, per some well defined syntax, as character character strings strings and that is comprehe comprehensive nsive in supporting supporting all the following following items: Data definition, definition, view definition, data manipulation, integrity constraints, authorization, transaction boundaries. There are often many different ways of interacting with the database, for example QBE (Query By Example) or SQL (for more sophisticated queries) Rule 6: View updating rule All views that are theoreticall theoretically y updateable updateable are also updateable updateable by the system. A view is a "virtual "virtual table" in a database. With a relational DBMS, any change that that a user makes to a view view should ideally also be made in the base table from which the view is derived. Rule 7: High-level insert, update and delete The capability of handling a base relation or a derived relation as a single operand applies not only to the retrieval retrieval of data but also to the insertio insertion, n, update update and deletion deletion of data. This means means that one command in a relational database should be able to carry out an operation on one or more rows in either a base relation or a view. Rule 8: Logical integrity Application programs and terminal activities remain logically unimpaired whenever any changes are made in either storage representation or access methods. Ex: moving tables to different disk drives, changing the order of rows in the table, reorganizing database files. In a relational environment the DBMS decides decides how to access a piece piece of data. Rule 9: Data independence Application programs and terminal activities remain logically unimpaired when information-preserving changes of any kind that theoretically theoretically permit unimpairement unimpairement are made to the base base tables. Relational tables may have to be expanded or restructured. New tables may also have to be added to the database. Expansion of a table may involve involve adding columns columns to existing tables. tables. The addition of a new column to a table in a relational database should not affect programs that use that table. Rule 10: Integrity constraints Integrity constraints specific to a particular relational database must be definable in the relational data sublanguage and stored stored in the catalog, not in the application programs. programs. No data should be stored stored in a relational database that has not been been defined beforehand. beforehand. Integrity controls must must exist to protect the consistency of the database database from unauthorized users. users. Two integrity constraints constraints exist: Entity integrity and referential integrity. integrity. Entity integrity states that no no part of the primary primary key can be be missing. The key is said to be "not null". Referential integrity relates to the use of foreign keys. A foreign key is an attribute or group of of attributes that matches the primary primary key of another table. table. If a table has a foreign key to represent a relationship, then the related table must have a matching primary key.
Rule 11: Extension of rule 8. A relational DBMS has distribution independence. independence. Distribution independence independence means that application application programs and terminal activities remain unaffected when data distribution is first introduced, when data is redistributed. Rule 8 requires that data should remain unaffected by the ways in which it is stored. Rule 11 requires that independence should still hold when data is distributed across different locations. Rule 12: Integrity constraints in the high-level language of the RDBMS. If a relational system has a low-level (single-record-at-a-time) language, that low-level language cannot be used to subvert or bypass the integrity rules and constraints expressed in the higher-level relational language (multiplerecords-at-a-time). This rule guarantees the integrity integrity constraints contained in the high-level language of the DBMS. DBMS. In some some cas case, e, you want want to use a one-re one-reco cord rd-at-at-a-t a-time ime procedu procedure. re. A proce procedur dural al language such as C, Cobol, or Fortran Fortran is used for this. These procedural procedural languages cannot bypass the DBMS. Data Handling Techniques, Techniques, DML, DDL, DCL DML DML (Da (Data ta Ma Man nipul ipulat atio ion n Lang Langua uag ge) DDL (Data Definition Language) DCL (Data Control Language)
[SELECT, INSERT, UPDATE, DELETE ] [CREATE, DROP ] [GRANT REVOKE (DBA)] (DBA)]
SELECT Statement examples: Eg: SELECT discount, stor_id AS bookstore, discount FROM discounts Eg: OTHER alias examples: SELECT discount, stor_id bookstore, discount FROM discounts Eg: SELECT discount, bookstore = stor_id, discount FROM discounts Eg: With Text: SELECT 'The answer is:' discount, stor_id, discount FROM discounts Eg: With Math: SELECT discount, stor_id, discount, discount*1.75 AS 'UK VAT' FROM discounts Eg:
Without repeat: SELECT DISTINCT state FROM stores
Eg: The WHERE clause: SELECT title FROM titles WHERE title_id='MC2222' Eg: Using the BETWEEN BETWEEN Statement (BETWEEN is inclusive!): SELECT title_id, qty FROM sales
WHERE qty BETWEEN 10 AND 30
Eg: Using the IN statement: SELECT pub_name FROM publishers WHERE state IN ('NH', 'MA') Eg: The NOT statement: SELECT pub_name FROM publishers WHERE NOT state ='CA' Eg: The ORDER clause (ASC (ASC - ascending [default], [default], DESC - descending) descending) SELECT au_laname, au_fname FROM authors ORDER BY au_laname DESC Eg: Text matching (% is a wildcard for a string string of zero or many characters): characters): SELECT title_id, title FROM titles WHERE title LIKE '%ook%' Eg: Text matching (_ is a wildcard for exactly exactly one character): character): SELECT title_id, title FROM titles WHERE title LIKE '_ook%' Eg: Text matching ([] is a range wildcard), wildcard), any titles that start with a,c,d or f: SELECT title_id, title FROM titles WHERE title LIKE '[acdf]%' Eg: Text matching ([^] is the NOT range wildcard), wildcard), any titles that does not start start with a,b,c,d or e: SELECT title_id, title FROM titles WHERE title LIKE '[^a-e]%' UNION, UNION ALL, INTERSECT, MINUS (To see info from two different tables with same data definition, NO duplication) With UNION if the tables are seen as ensembles, then the intersection between the tables is listed only once Eg: SELECT stor_id, title_id, qty FROM sales_america UNION SELECT stor_id, title_id, qty FROM sales_europe (To see info from two different tables with same data definition, WITH duplication) With UNION ALL If the tables are seen as ensembles, ensembles, then the intersection between between the tables is listed twice Eg: SELECT stor_id, title_id, qty FROM sales_america UNION ALL SELECT stor_id, title_id, qty FROM sales_europe (To see info from two different tables with same data definition, duplication only) With INTERSECT If the tables are seen as ensembles, then only the intersection between the tables is listed
Eg: SELECT stor_id, title_id, qty FROM sales_america INTERSECT SELECT stor_id, title_id, qty FROM sales_europe (To see info from two different tables with same data definition, duplication extracted) With MINUS If the tables are seen as ensembles, then only the data from the first table is listed less the common data and the data from the second table Eg: SELECT stor_id, title_id, qty FROM sales_america MINUS SELECT stor_id, title_id, qty FROM sales_europe INSERT Eg: INSERT INTO products(prod_id, products(prod_id, description) VALUES(34,'pants') Eg: INSERT INTO highprice(prod_id,prod_code,pr highprice(prod_id,prod_code,price) ice) SELECT prod_id, prod_code, price --(have to have same columns) FROM products WHERE price > 20.00 UPDATE Eg: UPDATE stocks SET qty = 0 WHERE warehouse_id = 10 DELETE Eg: DELETE FROM warehouse WHERE location = 'Chicago' ORDER BY Eg: SELECT * FROM products WHERE prod_code = 'H' ORDER BY price DESC --ASC is the default Comparison, range, patterns, IN, BETWEEN operators Comparison operators: = equal to <> not equal to > greater than < less than >= greater than or equal <= less than or equal Range operators:
BETWEEN:
Eg: SELECT product_id FROM products WHERE price BETWEEN 3 AND 20 Set membership operator: IN Eg: SELECT product_id FROM products WHERE product_code IN ('H','E') Patt Patter ern n matc matchi hing ng operat erato or: LIKE LIKE Eg: SELECT product_id, description FROM products WHERE description LIKE 'Pipe%' OR WHERE description LIKE '%Pipe%' OR WHERE description LIKE 'Pipe 2_mm%' % _
wildcard for several characters (percent sign) wildcard for one character(underscore character(underscore sign)
Logical Operators: IS NULL Eg: SELECT product_id, price FROM products WHERE price IS NULL --null is not equal to zero BETWEEN, IN, LIKE, IS NULL can all be negated by the NOT operator. NOT BETWEEN NOT IN NOT LIKE IS NOT NULL Arithmetic Expressions: Expressions: Arithmetic Expressions: Expressions: + - * / Used to generate virtual/temporary column in query results Eg: SELECT description, price, (price*1.05)new_price FROM products --note: name of new column must follow the parentheses Logical Connectives: Logical Co Connectives: AND OR Eg: SELECT product_id, price FROM products WHERE price>10 OR product_id=10 --note: In a query query the AND is satisfied first using IN( , , ) has the same result as using OR
Aggregate Functions: SUM () gives the total of all the rows, satisfying any conditions, of the given column, where the given column is numeric. AVG () gives the average of the given column. MAX () gives the largest figure in the given column. MIN () gives the smallest figure in the given column. COUNT(*) gives the number of rows satisfying the conditions. Note : Takes entire column of data and produces a single data item that summarizes the column. They are: AVG( ) SUM( ) COUNT( ) MAX( ) MIN( ) Eg: SELECT COUNT(*) FROM stocks --COUNT(*) will count nulls Eg: SELECT COUNT(DISTINCT region) FROM warehouses --Use of word DISTINCT eliminate duplicate in count Eg: SELECT COUNT(Qty) FROM stocks --Does not count nulls Eg: SELECT COUNT(*), MAX(price), MIN(price), AVG(price) FROM products GROUP BY Eg: SELECT prod_code, AVG(price) FROM products GROUP BY prod_code -- note: the column to GROUP BY has to be in the SELECT column HAVING (WHERE clause of the GROUP BY) -- The GROUP BY clause (with aggregate fctn): Eg: SELECT state, COUNT(*) AS 'Total' FROM authors GROUP BY state -- The HAVING clause (with aggregate fctn): Eg: SELECT state, COUNT(*) AS 'Total' FROM authors GROUP BY state HAVING COUNT(*) > 1 JOIN Example of an equi-join (When there is an exact match between the columns)
Eg: SELECT products.prod_id, products.prod_id, description --(full path name avoids ambiguity if FROM products, stocks -- column name is similar in two tables) WHERE products.prod_id= products.prod_id= stocks.prod_id stocks.prod_id --(joint statement) AND qty = 5 An example of a natural join follows. It produces a product of every combination of the two tables!!! Not used frequently since it returns useless information Eg: SELECT * FROM products, stocks An example of a join on more than two tables follows: Eg: SELECT description, qty, location FROM products, stocks, warehouses WHERE products.prod_id= stocks.prod_id AND stocks.warehouse_id = warehouses.warehouse_id warehouses.warehouse_id AND qty < 5 ALIAS Eg: SELECT description, qty, location FROM products products p, stocks stocks s, warehous warehouses es w WHERE p.prod_id= s.prod_id AND s.warehouse_id = w.warehouse_id AND qty < 5
-- <- alias declarat declaration ion
Self-join are used in a multi-table query involving a relationship a table has with itself. Eg: SELECT right.location FROM warehouses left, warehouses right WHERE left.region= right.region AND left.warehouse_id <> right.warehouse_id An outer-join is needed when a value in a joining column in one table has no matching value in the joined table. Eg: SELECT location, region_name FROM region r FULL OUTER JOIN warehouses w ON r.region_id = w.region_id Subquery Eg: SELECT prod_id, description FROM products WHERE product_id IN(SELECT prod_id FROM stocks WHERE qty > 50) Inner Join statements (rows must be present in each tables that match on the join condition): Eg: SELECT p.pub_id, pub_name, title FROM publishers AS p INNER JOIN titles AS t
ON p.pub_id = t.pub_id WHERE type = 'business' Eg: SELECT stor_name, title, ord_date, qty FROM stores INNER JOIN sales ON stores.stor_id = sales.stor_id INNER JOIN titles ON sales.title_id = titles.tles_id WHERE type = 'popular_comp' 'popular_comp' Left and right outer join (Left joins return rows with left table column info that have matching condition but null info on column from the right side table) : Eg: SELECT titles.title_id, title, ord_date, qty FROM titles LEFT OUTER JOIN sales ON titles.title_id = sales.title_id VIEW Eg: CREATE VIEW James_view AS SELECT prod_id, description FROM products WHERE price < 17.50;
DDL (Data De finition Language) Language) CREATEalso using UNIQUE, NOT NULL, ASSERTION, DOMAIN, CHECK... ALTER used to add a column, add/delete primary/foreign keys, add/drop a uniqueness or Check constraint DRO DROP When When usin using g REST RESTRI RIC CT Drop Drop will ill fail fail if tab table le has has objects ects depen epend denci encie es CREATE VIEW CREATE INDEX The catalog holds information about roles and privileges. It has information on VIEWS. Eg: DROP TABLE customer Eg: To create a table: CREATE TABLE title ( Au_id ID, Title_id TID, au_ord TINYINT NULL, typer INT NULL ) Eg:
To create a temporary table (visible to present connection, will be deleted when logout) CREATE TABLE #temp1 ( Au_id ID, Title_id TID )
Eg: To create a global temporary table (visible to all connections on server, will be deleted when all logout) CREATE TABLE ##temp1 (Au_id ID, Title_id TID) Eg: To To create a view CREATE VIEW myfirstview AS SELECT fname, lname, address FROM customers Eg: To remove a view from the database DROP VIEW myfirstview Eg: To change a view ALTER VIEW myfirstview (store_name, qty_sold, date_sold, title) AS SELECT store, qty, date, title_name FROM stores INNER JOIN sales ON sales.stor_id = stores.stor_id DCL (Data Control Language) GRANT priviledge TO username IDENTIFIED BY password; CREA CREATE TE SYNO SYNONY NYM M Used Used to shor shorte ten n use user/ r/ow owne nerr of of a tab table le.. CREA CREATE TE PUBL PUBLIC IC SYNO SYNONY NYM M ... FOR FOR ... ; Publ Public ic syno synony nyms ms can can be crea create ted d by the the DBA. DBA. COMMIT and ROLLBACK An explict transaction is a group of statements that must all succeed or must all fail. to turn ON the ANSI SQL-92 behaviour use: SET IMPLICIT_TRANSACTION ON --ANSI SQL-92 COMMIT WORK ROLLBACK WORK COMMIT; --makes changes made to some database systems permanent (since the last COMMIT; known as a transaction) ROLLBACK; --Takes back any changes to the database that you have made, back to the last time you gave a Commit command...beware! Some software uses automatic committing on systems that use the transaction features, so the Rollback command may not work. Mathematical Functions: ABS(X) Absolute value-converts value-converts negative negative numbers to positive, or leaves leaves positive numbers alone alone CEIL(X) X is a decimal value lue that will be rounded up. FLOOR(X) X is a decimal value that will be rounded down. GREATEST(X,Y) Returns the largest of the two values. LEAST(X,Y) Returns the smallest of the two values. MOD(X,Y) Returns the remainder of X / Y. POWER(X,Y) Returns X to the power of Y. ROUND(X,Y) Rounds X to to Y d ec ecimal pl places. If If Y i s om omitted, X i s ro rounded to to th the ne nearest integer. SIGN(X) Returns a minus if X < 0, else a plus. SQRT(X) Returns the square root of X.
Character Functions LEFT LEFT(< (, >,X) X) RIGHT( RIGHT(, g>,X) X) UPPER() LOWE LOWER( R() >) INIT INITCA CAP( P( g>)) LENG LENGTH TH(< () >) | || ing>
Retu Return rns s the the left leftmo most st X cha chara ract cter ers s of the the str strin ing. g. Return Returns s the the rightm rightmost ost X char charact acters ers of the string string.. Converts the string to all uppercase letters. Conv Conver erts ts the the stri string ng to all all low lower erca case se lett letter ers. s. Conv Conver erts ts the the str strin ing g to to ini initi tial al caps caps.. Retu Return rns s the the numb number er of of char charac acte ters rs in in the the stri string ng.. Combines Combines the two strings strings of text text into one, concatena concatenated ted string, string, where where the first string is immediately followed by the second
LPAD(,X ing>,X,'*') ,'*') Pads the string string on on the left with with the * (or whatever whatever chara character cter is inside inside the quotes), to make the string X characters long. RPAD(,X ing>,X,'*') ,'*') Pads the string string on the the right with with the * (or whatever whatever characte characterr is inside the the quotes), to make the string X characters long. SUBSTR(,X,Y) SUBSTR(,X,Y) Extracts Y letters from the string string beginning at position X. NVL(,) The Null value value function function will will substi substitut tute e > for any NULLs NULLs for in the . If the current value of is not NULL, NVL has no effect.
Command / ? [Keyword] @[@ @[@] [F [Filen ilena ame] me] [Pa [Parramet ameter er list list]] ACC[EPT] Variable [DEF[AULT] Value] [PROMPT Text | NOPR[OMPT]] CL[EAR] [SCR[EEN]] CL[EAR] SQL COL[UMN] [Column] [Format] CON[NECT] [username/password@database] DEF[INE] [Variable] [ = Text] DESC[RIBE] Object DISC[CONNECT] EDIT EXEC[UTE] Procedure EXIT GET [Filename] HOST [Command] HELP [Keyword] INFO PAUSE [Message] PRI[NT] [Variable] PROMPT [Text] QUIT R[UN]
Meaning Executes the SQL buffer Provides SQL help on the keyword Runs Runs the the sp specifie ified d co command mand file file,, pas passi sing ng the the sp specif ecifie ied d parameters Allows the user to enter the value of a substitution variable Clears the screen Clears the SQL buffer Defines the format of a column, displays the format of a column, or displays all column formats Connects to the database with the speciffied user Defines a substitution variable, displays a variable, or displays all substitution variables. Gives a description of the specified object Disconnects from the database Displays a text editor to edit the SQL buffer Executes the specified procedure Quits a running script or closes the Command Window Loads a command file into the editor Executes the host command Provides SQL help on the keyword Displays information about the connection Displays the message and pauses until the user presses return Displays the value of the bind variable, or all bind variables Displays the specified text Quits a running script or closes the Command Window Executes the SQL buffer
REM[ARK] [Text] SET AUTOP[RINT] [ON | OFF]
A comment line Determines if bind variables are automatically displayed after executing a SQL statement or PL/SQL block. SET SET CON[ CON[CA CAT] T] [Ch [Char arac acte terr | ON ON | OFF] OFF] Dete Determ rmin ines es the the cha chara ract cter er tha thatt term termina inate tes s a sub subst stit itut utio ion n variable reference (default = .) SET SET DEF[I DEF[INE NE]] [Ch [Char arac acte terr | ON ON | OFF OFF]] Dete Determ rmin ines es the the char charac acte terr that that star starts ts a subs substi titu tuti tion on variable reference (default = &) SET ECHO [ON | OFF] Determines if executed commands in a script are displayed SET SET ESC ESC[A [APE PE]] [Ch [Char arac acte terr | ON | OFF] OFF] Dete Determ rmin ines es the the cha chara ract cter er that that esca escape pes s the the char charac acte terr that starts a substitution variable reference (default = \) SET FEED[BACK] [ON | OFF] Determines if the number of affected rows of a SQL statement is displayed SET HEA[DING] [ON | OFF] Determines if headings are displayed above the columns of a result set SET LONG [Width] Determines the maximum display width of a long column SET PAGES[IZE] [Size] Determines the number of lines that are displayed for a result set, before the headings are repeated SET SERVEROUT SERVEROUT[PUT] [PUT] [ON [ON | OFF] [SIZE n] Determines Determines if output output of calls calls to dbms_outp dbms_output.pu ut.put_line t_line is displayed, and what the size of the output buffer is SET TERM[OUT] [ON | OFF] Determines if output of executed SQL statements is displayed SET TIMI[NG] [ON | OFF] Determines if timing information about executed SQL statements is displayed SET VER[IFY] [ON | OFF] Determines if substitution variables are displayed when used in a SQL statement or PL/SQL block SHO[W] REL[EASE] Displays Oracle release information for the current connection SHO[W] SQLCODE Displays the result code of the executed SQL statement SHO[W] USER Displays the username of the current connection SPO[OL] [Filename | OFF] Starts or stops spooling STA[ STA[RT RT]] [Fi [File lena name me]] [Par [Param amet eter er list list]] Runs Runs the the spe speci cifi fied ed comm comman and d fil file, e, pass passin ing g the the spe speci cifi fied ed parameters STORE SET [Filename] Stores the values of all options in the filename. You can execute this file later to restore these options. UNDEF[INE] Variable Undefines the given substitution variable VAR[I AR[IA ABLE] BLE] [Var [Varia iabl ble] e] [Dat [Datat aty ype] pe] Defi Define nes s a bind bind varia ariab ble, le, dis disp play lays a bind ind var varia iab ble, le, or or displays all bind variables.
