DBMS Lab SQL Fundamentals Lab 1 PDF

DBMS Lab SQL Fundamentals – Lab 1  Lab Rules You MUST attend in your section. Please commit to the lab start time. No attendance exceptions from TAs. Attendance exceptions only signed from doctors. 2  Required installations Oracle...

DBMS Lab SQL Fundamentals – Lab 1  Lab Rules You MUST attend in your section. Please commit to the lab start time. No attendance exceptions from TAs. Attendance exceptions only signed from doctors. 2  Required installations Oracle 11g INSTALLATION: – Refer to “ Installation – Database.ppt” for database installation – Refer to “Installation - Forms&Reports.ppt” for developer installation 3 Structured Query Language (SQL)  SQL, PL/SQL, and SQL Developer SQL: Structured Query Language, What to do - NOT - How to do. PL/SQL: Procedural Language SQL, a complete language that contains loops, if conditions, variables, cursors, procedures and functions…etc. SQL Developer: An execution environment to write SQL and PL/SQL (the program itself). 5 Data Retrieval Command (DRC)  Basic SELECT Statement SELECT *|{[DISTINCT] column|expression [alias],...} FROM table; In its simplest form, a SELECT statement must include the following: SELECT identifies what columns FROM identifies which table 7  Selecting All Columns SELECT * FROM departments; 8  Selecting Specific Columns SELECT department_id, location_id FROM departments; In the SELECT clause, specify the columns that you want, in the order in which you want them to appear in the output. 9  Using Arithmetic Operators SELECT last_name, salary, salary + 300 FROM employees; … Note that the resultant calculated column SALARY+300 is not a new column in the EMPLOYEES table; it is for display only. By default, the name of a new column comes from the calculation “salary+300” 10  Operator Precedence * / + - Multiplication and division take priority over addition and subtraction. Operators from the same priority are evaluated from left to right. Parentheses are used to enforce prioritized evaluation and to clarify statements. 11  Operator Precedence SELECT last_name, salary, 12*salary+100 FROM employees; … SELECT last_name, salary, salary+100*12 FROM employees; 12  Using Parentheses SELECT last_name, salary, 12*(salary+100) FROM employees; … You can override the rules of precedence by using parentheses to specify the order in which operators are executed. 13  Defining a Null Value A null is a value that is unavailable, unassigned, unknown, or inapplicable. A null is not the same as zero or a blank space. SELECT last_name, job_id, salary, commission_pct FROM employees; … … 14  Null Values in Arithmetic Expressions Arithmetic expressions containing a null value evaluate to null. SELECT last_name, 12*salary*commission_pct FROM employees; … … 15  Defining a Column Alias A column alias: Renames a column heading Is useful with calculations Immediately follows the column name The optional AS keyword may be used between the column name and alias Requires double quotation marks if it contains spaces or special characters (such as # or $), or is case sensitive 16  Using Column Aliases SELECT last_name AS name, commission_pct comm FROM employees; … SELECT last_name "Name", salary*12 "Annual Salary" FROM employees; … 17  Duplicate Rows The default display of queries is all rows, including duplicate rows. SELECT department_id FROM employees; … 18  Eliminating Duplicate Rows Eliminate duplicate rows by using the DISTINCT keyword in the SELECT clause. SELECT DISTINCT department_id FROM employees; 19  Eliminating Duplicate Rows You can specify multiple columns after the DISTINCT qualifier. The DISTINCT qualifier affects all the selected columns, and the result is every distinct combination of the columns. You can not specify columns before the DISTINCT qualifier. 20 Restricting Data  Limiting the Rows Selected Restrict the rows returned by using the WHERE clause. SELECT *|{[DISTINCT] column|expression [alias],...} FROM table [WHERE condition(s)]; The WHERE clause follows the FROM clause. It consists of three elements: – Column name – Comparison operator – Column name, constant, or list of values 22  Using the WHERE Clause A WHERE clause contains a condition that must be met. If the condition is true, the row meeting the condition is returned. SELECT employee_id, last_name, job_id, department_id FROM employees WHERE department_id = 90 ; = Equal , < Less than , > Greater than , Not equal 23  Character Strings and Dates Character strings and date values are enclosed in single quotation marks. Character values are case sensitive. Date values are format sensitive. SELECT last_name, job_id, department_id FROM employees WHERE last_name = 'Whalen'; 24  Using Comparison Conditions SELECT last_name, salary FROM employees WHERE salary =10000 AND job_id LIKE '%MAN%'; 33  Using the OR Operator OR requires either condition to be true SELECT employee_id, last_name, job_id, salary FROM employees WHERE salary >= 10000 OR job_id LIKE '%MAN%'; 34  Using the NOT Operator SELECT last_name, job_id FROM employees WHERE job_id NOT IN ('IT_PROG', 'ST_CLERK', 'SA_REP'); 35 Sorting Data  The ORDER BY Clause The ORDER BY clause is last in SELECT statement. The default sort order is ascending. You can sort by column name, expressions or aliases. Null values are displayed: – Last for ascending order – First for descending order SELECT last_name, job_id, department_id, hire_date FROM employees ORDER BY hire_date; 37  Sorting in Descending Order The sort order can be reversed by using DESC. SELECT last_name, job_id, department_id, hire_date FROM employees ORDER BY hire_date DESC ; … 38  Sorting by Multiple Columns The order of ORDER BY clause list is order of sort. SELECT last_name, job_id, department_id, hire_date FROM employees ORDER BY department_id, last_name; You can order by position, e.g. 2nd column in select clause. SELECT last_name, job_id, department_id, hire_date FROM employees ORDER BY 2; You can sort by a column that is not in the SELECT list. 39 Thank You