610 likes | 763 Views
Chapter 5. Structured Query Language (SQL) Hachim Haddouti. In this chapter, you will learn:. Basic commands and functions of SQL SQL for data manipulation (DML) How to use SQL to query a database to extract useful information
E N D
Chapter 5 Structured Query Language (SQL) Hachim Haddouti
In this chapter, you will learn: • Basic commands and functions of SQL • SQL for data manipulation (DML) • How to use SQL to query a database to extract useful information • How SQL is used for data administration (to create tables, indexes, and views) DDL • Advanced SQL features such as views, stored procedures, and triggers Hachim Haddouti and Rob & Coronel, Ch5
Introduction to SQL • Ideal database language • Create database and table structures • Perform basic data management chores (add, delete, and modify) • Perform complex queries to transform data into useful information • SQL is the ideal DB language • Data definition language • Data manipulation language • Non-procedural language (only what and not how) Hachim Haddouti and Rob & Coronel, Ch5
Good Reasons to Study SQL • ANSI standardization effort led to de facto query standard for relational database • Forms basis for present and future DBMS integration efforts • Becomes catalyst in development of distributed databases and database client/server architecture • Note: • SQL:as successor of the prototype language SEQUEL (IBM Almaden Research Center San Jose, around 1975). • 2 knowm version of SQL (SQL2 also called SQL-92, and SQL99 or SQL 3). • For a complete pecification of SQL see [ Melton93]. About 700 pages or www.ansi.org or A Guide to the SQL Standard [DATE97]. • But, some dialects (difference is minor) Hachim Haddouti and Rob & Coronel, Ch5
Data Definition Commands • Create database structure • Holds all tables and is a collection of physical files stored on disk • DBMS automatically creates tables to store metadata • Database administrator creates structure or schema • Logical group of tables or logical database • Groups tables by owner • Enforces security Hachim Haddouti and Rob & Coronel, Ch5
Creating Table Structure • Used to describe layout of a table • Tables store end-user data • May be based on data dictionary entries • Typical restrictions placed by DBMS • Names cannot exceed 18 characters • Names must start with a letter • Names can contain only letters, numbers, and underscores (_) • Names cannot contain spaces Hachim Haddouti and Rob & Coronel, Ch5
Typical Data Types • INTEGER • Numbers without a decimal point • SMALLINT • Uses less space than INTEGER (up to 6 digits) • DECIMAL(p,q) • P number of digits; q number of decimal places • CHAR(n) • Character string n places long (fixed length) • VARCHAR(n) • Variable Length string up to n characters long • DATE • Dates in DD-MON-YYYY or MM/DD/YYYY Hachim Haddouti and Rob & Coronel, Ch5
Creating Table Structure cont. CREATE TABLE <table name>(<attribute1 name and attribute1 characteristics,attribute2 name and attribute2 characteristics,attribute3 name and attribute3 characteristics,primary key designation,foreign key designation and foreign key requirement>); CREATE TABLE CUSTOMER (customer_ID INTEGER NOT NULL UNIQUE, customer_name char(25) NOT NULL, PRIMARY KEY (customer_id)); Hachim Haddouti and Rob & Coronel, Ch5
Creating Table Structure cont. CREATE TABLE EMPLOYEE ( EMP_NUM CHAR(3) NOT NULL, EMP_LNAME CHAR(15) NOT NULL, EMP_FNAME CHAR(15) NOT NULL, EMP_INITIAL CHAR(1), EMP_HIREDATE DATE NOT NULL, JOB_CODE CHAR(3), PRIMARY KEY (EMP_NUM), FOREIGN KEY (JOB_CODE) REFERENCES JOB); Note: Composite key! Hachim Haddouti and Rob & Coronel, Ch5
Using Domains • Domain is set of permissible values for a column • Definition requires: • Name • SQL common Data type • Default value • Domain constraint or condition CREATE DOMAIN <domain_name> AS DATA_TYPE[DEFAULT <default_value>] [CHECK (<condition>)] CREATE DOMAIN WEEK AS VARCHAR(15) CHECK ( VALUE IN (‘Monday’, ‘Tuesday’, … ‘Sunday’)); Note: in Oracle 8i use CREATE TYPE Hachim Haddouti and Rob & Coronel, Ch5
SQL Integrity Constraints • Adherence to entity integrity and referential integrity rules is crucial • Entity integrity enforced automatically if primary key specified in CREATE TABLE command sequence • Referential integrity can be enforced in specification of FOREIGN KEY CREATE TABLE EMPLOYEE (EMP_NUM CHAR(25) NOT NULL UNIQUE, ..., PRIMARY KEY (EMP_NUM)); CREATE TABLE link ( ..., JOBCODE Integer NOT NULL, FOREIGN KEY (JOBCODE) REFERENCES table2); Hachim Haddouti and Rob & Coronel, Ch5
Data Manipulation Commands Common SQL Commands Hachim Haddouti and Rob & Coronel, Ch5
Data Entry and Saving • Enters data into a table • Saves changes to disk INSERT INTO <table name> VALUES (attribute 1 value, attribute 2 value, … etc.); COMMIT <table names> ; Hachim Haddouti and Rob & Coronel, Ch5
Data Entry and Saving cont. INSERT INTO EMPLOYEE VALUES ('101', 'News', 'John', 'G', '11/8/94', '502'); INSERT INTO EMPLOYEE VALUES ('102', 'Senior', 'David', 'H', '7/12/87', '501'); Hachim Haddouti and Rob & Coronel, Ch5
Result of Create and Insert After many Inserts: Hachim Haddouti and Rob & Coronel, Ch5
Listing Table Contents and Other Commands • Allows table contents to be listed • UPDATE command makes data entry corrections • ROLLBACK command restores database back to previous condition if COMMIT hasn’t been used (undoes all updates performed by the transaction) • DELETE command removes table row SELECT <attribute names> FROM <table names>; Hachim Haddouti and Rob & Coronel, Ch5
Motivating example: Rollback • Transfer of money from one account to another involves two steps: • deduct from one account and credit to another • If one steps succeeds and the other fails, database is in an inconsistent state • Therefore, either both steps should succeed or neither should • If any step of a transaction fails, all work done by the transaction can be undone by rollback work. • Rollback of incomplete transactions is done automatically, in case of system failures Hachim Haddouti and Rob & Coronel, Ch5
Queries SELECT <column(s)>FROM <table name>WHERE <conditions>; • Creating partial listings of table contents Mathematical Operators Hachim Haddouti and Rob & Coronel, Ch5
Examples SELECT EMP_LNAME, EMP_FNAME, EMP_NUM, JOB_CODE FROM EMPLOYEE WHERE JOB_CODE = 500; • Mathematical operators • Mathematical operators on character attributes • Mathematical operators on dates SELECT EMP_LNAME, EMP_FNAME, EMP_NUM, EMP_INITIALFROM EMPLOYEEWHERE EMP_INITIAL 500 > ‘G’; SELECT EMP_LNAME, EMP_FNAME, EMP_NUM, EMP_HIREDATEFROM EMPLOYEEWHERE EMP_HIREDATE>= ‘01/20/2002’; Hachim Haddouti and Rob & Coronel, Ch5
Computed Columns • New columns can be created through valid computations or formulas • Formulas may contain mathematical operators • May contain attributes of any tables specified in FROM clause • Alias is alternate name given to table or column in SQL statement SELECT P_DESCRIPT,P_ONHAND,P_PRICE,P_ONHAND*P_PRICE AS TOTVALUE FROM PRODUCT; Hachim Haddouti and Rob & Coronel, Ch5
Operators SELECT * FROM PRODUCTWHERE V_CODE = 21344 OR V_CODE = 24288; • Logical: AND, OR, NOT • Rules of precedence • Conditions within parenthesis executed first • Boolean algebra • Special • BETWEEN - defines limits • IS NULL - checks for nulls • LIKE - checks for similar string • IN - checks for value in a set • EXISTS - opposite of IS NULL Hachim Haddouti and Rob & Coronel, Ch5
Examples of Special Operators • SELECT * FROM PRODUCT WHERE P_PRICE BETWEEN 50.000 AND 100.000; • SELECT P_CODE, P_DESCRIPT FROM PRODUCT WHERE P_MIN IS NULL • SELECT EMP_LNAME FROM EMPLOYEE WHERE EMP_LNAME LIKE ‘S%’ • SELECT EMP_LNAME FROM EMPLOYEE WHERE EMP_LNAME LIKE ‘Ha_him’ • SELECT EMP_LNAME FROM EMPLOYEE WHERE EMP_LNAME NOT LIKE ‘Ha_him’ • SELECT EMP_LNAME FROM EMPLOYEE WHERE UPPER(EMP_LNAME) LIKE ‘HA_HIM’ • Note: some other RDBMS use * and ? Instead of % and _ Hachim Haddouti and Rob & Coronel, Ch5
Advanced Data ManagementCommands • ALTER - changes table structure • ADD - adds column • MODIFY - changes column characteristics ALTER TABLE <table name>ADD (<column name> <new column characteristics>); ALTER TABLE <table name>MODIFY (<column name> <new column characteristics>); ALTER TABLE Employee MODIFY (Emp_Lname CHAR (35)); /* this will work only in the case that the column is empty*/ NOTE: Be careful by including NOT NULL clause in add a new column Hachim Haddouti and Rob & Coronel, Ch5
Advanced Data ManagementCommands cont. • Entering data into new column UPDATE PRODUCTSET P_SALECODE = ‘2’WHERE P_CODE = ‘1546-QQ2’; • Deleting Table Rows DELETE FROM EMPLOYEE WHERE EMP_NUM = ‘102’; Hachim Haddouti and Rob & Coronel, Ch5
Query Example • Assume: account (account-number, branch-name, balance) • Increase all accounts with balances over $10,000 by 6%, all other accounts receive 5%. • Write two update statements: • update accountset balance = balance 1.06where balance > 10000 • update accountset balance = balance 1.05where balance 10000 Hachim Haddouti and Rob & Coronel, Ch5
Advanced Data Management Commands (con’t.) • Dropping a column • Arithmetic operators and rules of precedence ALTER TABLE VENDORDROP COLUMN V_ORDER; Hachim Haddouti and Rob & Coronel, Ch5
Advanced Data Management Commands (con’t.) • Copying parts of tables • Deleting a table from database • DROP TABLE PART; • Primary and foreign key designation INSERT INTO <receiving table> <receiving table’s column names>SELECT <column names of the columns to be copied>FROM <contributing table name>; ALTER TABLE LINE ADD PRIMARY KEY (INV_NUMBER, LINE_NUMBER) ADD FOREIGN KEY (INV_NUMBER) REFERENCES INVOICE ADD FOREIGN KEY (PROD_CODE) REFERENCES PRODUCT; Hachim Haddouti and Rob & Coronel, Ch5
More Complex Queries and SQL Functions • Ordering a listing ( always last in command sequence) • Results ascending by default • Descending order uses DESC • Cascading order sequence ORDER BY <attributes> ORDER BY <attributes> DESC ORDER BY <attribute 1, attribute 2, ...> Hachim Haddouti and Rob & Coronel, Ch5
More Complex Queries and SQL Functions (con’t.) • Listing unique values • DISTINCT clause produces list of different values • Aggregate functions • Mathematical summaries SELECT DISTINCT V_CODE FROM PRODUCT; Num of non-null rows in a given column Min attribute value Max attribute value Sum of of all selected attribute values Arithmetic average for the give column Hachim Haddouti and Rob & Coronel, Ch5
Example Aggregate Function Operations SELECT COUNT(DISTINCT V_CODE)FROM PRODUCT; SELECT COUNT(DISTINCT V_CODE)FROM PRODUCTWHERE P_PRICE <= 10.00; • COUNT • MAX and MIN SELECT MIN(P_PRICE)FROM PRODUCT; SELECT P_CODE, P_DESCRIPT, P_PRICEFROM PRODUCTWHERE P_PRICE = MAX(P_PRICE); Hachim Haddouti and Rob & Coronel, Ch5
Example Aggregate Function Operations (con’t.) • SUM • AVG SELECT SUM(P_ONHAND * P_PRICE)FROM PRODUCT; SELECT P_DESCRIPT, P_ONHAND, P_PRICE, V_CODEFROM PRODUCTWHERE P_PRICE > (SELECT AVG(P_PRICE) FROM PRODUCT)ORDER BY P_PRICE DESC; NOTE: Except COUNT, all aggregations apply to a single attribute (eg. SELECT Count(*) FROM Purchase) Hachim Haddouti and Rob & Coronel, Ch5
Example Query • Assume: account (account-number, branch-name, balance) • Delete the record of all accounts with balances below the average at the bank. delete from accountwhere balance < (select avg (balance)from account) Hachim Haddouti and Rob & Coronel, Ch5
More Complex Queries and SQL Functions (con’t.) • Grouping data • Creates frequency distributions • Only valid when used with SQL arithmetic functions • HAVING clause operates like WHERE for grouping output , not to columns SELECT P_SALECODE, MIN(P_PRICE)FROM PRODUCT_2GROUP BY P_SALECODE; SELECT V_CODE,COUNT(DISTINCT(P_CODE)),AVG(P_PRICE) FROM PRODUCT_2 GROUP BY V_CODE HAVING AVG(P_PRICE) < 10; Hachim Haddouti and Rob & Coronel, Ch5
Query Example Assume the following table: Student (SID, Std_Name, STD_Address, STD_GPA) Is the following SQL statement correct? SELECT Std_Name, STD_GPAFROM Student GROUP BY STD_GPA; And this? COUNTS HOW MANY PRODCUT EACH VENDOR PROVIDES SELECT V_CODE, COUNT(DISTINCT P_CODE) FROM PRODUCT GROUP BY V_CODE; Hachim Haddouti and Rob & Coronel, Ch5
Query Example cont. Find the names of all students where the average GPA is more than 3.0. select Std_Name, avg (Std_GPA)from Student group by Std_Namehaving avg (Std_GPA) > 3 Hachim Haddouti and Rob & Coronel, Ch5
More Complex Queries and SQL Functions (con’t.) • Virtual tables: creating a view • CREATE VIEW command • Creates logical table existing only in virtual memory • Once a view is defined, the view name can be used to refer to the virtual relation that the view generates. • SQL indexes CREATE VIEW PRODUCT_3 ASSELECT P_DESCRIPT, P_ONHAND, P_PRICEFROM PRODUCTWHERE P_PRICE > 50.00; CREATE INDEX P_CODEXON PRODUCT(P_CODE); Hachim Haddouti and Rob & Coronel, Ch5
More Complex Queries and SQL Functions (con’t.) • Joining database tables • Data are retrieved from more than one table • Recursive queries joins a table to itself (List of all employees with their manager’s name?) SELECT PRODUCT.P_DESCRIPT, PRODUCT.P_PRICE, VENDOR.V_NAME, VENDOR.V_CONTACT, VENDOR.V_AREACODE, VENDOR.V_PHONEFROM PRODUCT, VENDORWHERE PRODUCT.V_CODE = VENDOR.V_CODE; SELECT A.EMP_NUM,A.EMP_LNAME,A.EMP_MGR, B.EMP_LNAME FROM EMP A, EMP B WHERE A.EMP_MGR=B.EMP_NUM ORDER BY A.EMP_MGR Hachim Haddouti and Rob & Coronel, Ch5
Loan_number Branch_name amount L-170 L-230 L-260 Downtown Redwood Perryridge 3000 4000 1700 Loan_number Branch_name amount Customer_name Loan_number L-170 L-230 Downtown Redwood 3000 4000 Jones Smith L-170 L-230 Customer_name Loan_number Jones Smith Hayes L-170 L-230 L-155 Query Example Relation loan Relation borrower SELECT * FROM loan L, borrower B WHEREL.loan_number = B.loan_number Hachim Haddouti and Rob & Coronel, Ch5
Nested Subqueries • SQL provides a mechanism for the nesting of subqueries. • A subquery is a select-from-where expression that is nested within another query. • A common use of subqueries is to perform tests for set membership, set comparisons, and set cardinality. Hachim Haddouti and Rob & Coronel, Ch5
Query Example • Find all customers who have both an account and a loan at the bank. select distinct customer-namefrom borrowerwhere customer-name in (select customer-namefromdepositor) • Find all customers who have a loan at the bank but do not have an account at the bank select distinct customer-namefrom borrowerwhere customer-name not in (select customer-namefrom depositor) Hachim Haddouti and Rob & Coronel, Ch5
Updatable Views • Common operation in production environments is use of batch routines to update master table attributes using transaction data • Overnight batch jobs • Not all views are updatable • Restrictions • GROUP BY expressions cannot be used • Cannot use set operators---UNION, INTERSECTION, etc. • Most restrictions based on use of JOINS or group operators in views Hachim Haddouti and Rob & Coronel, Ch5
Procedural SQL • SQL shortcomings • Doesn’t support execution of stored procedures based on logical condition (IF THEN, DO WHILE) • Fails to support looping operations • Solutions • Embedded SQL can be called from within procedural programming languages • Shared Code is isolated and used by all application programs. • Procedural SQL (PL/SQL) stored within the database, executed by DBMS, and invoked by the end user • Triggers • Stored procedures • PL/SQL functions (Oracle) Hachim Haddouti and Rob & Coronel, Ch5
Procedural SQL (con’t.) • Procedural SQL allows the use of procedural code and SQL statements that are stored within the database. • The procedural code is executed by the DBMS when it is invoked by the end user. • End users can use procedural SQL (PL/SQL) to create: • Triggers • Stored procedures • PL/SQL functions • SQL provides a module language • permits definition of procedures in SQL, with if-then-else statements, for and while loops, etc Hachim Haddouti and Rob & Coronel, Ch5
Triggers • Procedural SQL code invoked before or after data row is selected, inserted, or updated • Associated with a database table • Table may have multiple triggers • Executed as part of transaction • Can enforce particular constraints • Automate critical actions and provide warnings for remedial action • Can update values, insert records, and call procedures • Add processing power Hachim Haddouti and Rob & Coronel, Ch5
Triggers (con’t.) • An example (for oracle) CREATE OR REPLACE TRIGGER <trigger_name>[BEFORE/AFTER] [DELETE/INSERT/UPDATE OF <column_name] ON <table_name>[FOR EACH ROW]BEGIN PL/SQL instructions; ……………END; See a concret example on page 270; Hachim Haddouti and Rob & Coronel, Ch5
Stored Procedures • Named collection of procedural and SQL statements stored in database e.g. to represent multiple update transactions • Transmitted and executed as entire reduces network traffic and improve performance • permit external applications to operate on the database without knowing about internal details • Invoked by name Hachim Haddouti and Rob & Coronel, Ch5
Stored Procedures • Executed as unit • Invoked with EXEC or call CREATE OR REPLACE PROCEDURE procedure_name (argument IN/OUT data-type, etc) IS/AS BEGIN DECLARE variable name and data type PL/SQL or SQL statements;END; EXEC store_procedure_name (parameter, parameter, …) Hachim Haddouti and Rob & Coronel, Ch5
Embedded SQL, see also procedural SQL • The SQL standard defines embeddings of SQL in a variety of programming languages such as Cobol, Pascal, Fortran, C, and Java. • A language to which SQL queries are embedded is referred to as a host language, and the SQL structures permitted in the host language comprise embedded SQL. • EXEC SQL statement is used to identify embedded SQL request to the preprocessor EXEC SQL <embedded SQL statement > END-EXEC Note: this varies by language. E.g. the Java embedding uses # SQL {<embedded SQL statement > } ; Hachim Haddouti and Rob & Coronel, Ch5
Example Query From within a host language, find the names and cities of customers with more than the variable amount dollars in some account. • Specify the query in SQL and declare a cursor for it EXEC SQL declare c cursor for select customer-name, customer-cityfrom depositor D, customer C, account Awhere D.customer_name = C.customer_name and D.account_number = A.account_numberand account.balance > :amount END-EXEC Hachim Haddouti and Rob & Coronel, Ch5
Embedded SQL (Cont.) • The open statement causes the query to be evaluated EXEC SQL opencEND-EXEC • The fetch statement causes the values of one tuple in the query result to be placed on host language variables. EXEC SQL fetch c into :cn, :cc END-EXECRepeated calls to fetch get successive tuples in the query result • The close statement causes the database system to delete the temporary relation that holds the result of the query. EXEC SQL closec END-EXEC Note: above details vary with language. E.g. the Java embedding defines Java iterators to step through result tuples. Hachim Haddouti and Rob & Coronel, Ch5