1 / 29

Chapter Zero

Chapter Zero. Objectives: Review Introduction to SQL. SQL Name:. User Identifiers: 1-30 characters Start with an alphabet Followed by alphabet, digit, _ Unique Not reserved Not case sensitive. Data Types:. Oracle Data Types: CHAR(size) VARCHAR2(max_size) NUMBER(n,d) DATE.

oprah-spence
Download Presentation

Chapter Zero

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Chapter Zero Objectives: Review Introduction to SQL

  2. SQL Name: User Identifiers: 1-30 characters Start with an alphabet Followed by alphabet, digit, _ Unique Not reserved Not case sensitive

  3. Data Types: Oracle Data Types: • CHAR(size) • VARCHAR2(max_size) • NUMBER(n,d) • DATE

  4. Display a Structure of a Table: DESCRIBE Students; Name Null? Type ---------------------------------------------------------- FNAME VARCHAR2(40) • LNAME VARCHAR2(40) ID NUMBER(9) • GPA NUMBER(3,2) • B_Date DATE • MAJOR CHAR(4) • MINOR CHAR(4)

  5. General Format SELECT fieldnames FROM relation [ WHERE condition] [ GROUP BY group_field ] [ HAVING condition] [ ORDER BY fieldname] ;

  6. SELECT: SELECT * FROM students ; SELECT lname , major, gpe FROM students ;

  7. SELECT: SELECT salary , name , salary/12 Monthly_Salary FROM faculty; SELECT name , salary/12 AS Monthly_Salary FROM faculty; SELECT name , salary/12 “Monthly Salary” FROM faculty;

  8. Column Format */ Example of column format*/ COLumn salary FORMAT 999999.99; COLumn salary FORMAT 999,999.99; COLumn salary FORMAT $999,999.99; SELECT salary FROM employee;

  9. Column Format SELECT salary FROM employee; COLUMN name FORMAT A5; COLUMN name FORMAT A5 WRApped; COLUMN name FORMAT A5 TRUncated; COLUMN salary/12 ALIAS m_salry; COLUMN m_salry FORMAT 99,999.99; CLEAR COLUMNS;

  10. SELECT: SELECT DISTINCT major FROM students; SELECT f_mame , l_name , major FROM students; SELECT f_mame || l_name , major FROM students; /* what is the output? /*

  11. SELECT: SELECT f_mame || ‘ ‘ || l_name , gpa FROM students; SELECT f_mame || ‘ ‘ || l_name , gpa FROM students WHERE major=‘COSC’;

  12. SELECT: SELECT f_mame || ‘ ‘ || l_name , gpa FROM students WHERE major = ‘COSC’ OR major = ‘MATH’; SELECT f_mame || ‘ ‘ || l_name , gpa FROM students WHERE major = ‘COSC’ AND minor = ‘MATH’ ;

  13. SELECT: SELECT f_mame || ‘ ‘ || l_name , gpa FROM students WHERE major IS NULL; SELECT l_mame , gpa FROM students WHERE major IS NOT NULL;

  14. SELECT: SELECT name, NVL(major, ‘unknown’) FROM student; SELECT name, NVL(GPA, 0.0) FROM student;

  15. SELECT: SELECT name , salary , dept FROM faculty WHERE salary BETWEEN 20000 AND 50000; SELECT name , salary , dept FROM faculty WHERE salary NOTBETWEEN 20000 AND 50000;

  16. SELECT: SELECT name , salary , dept FROM faculty WHERE name BETWEEN ‘L’ AND ‘O’;

  17. SELECT: SELECT name FROM Student WHERE major IN (‘COSC’, ‘MATH’); SELECT name FROM Student WHERE id NOT IN (1111 , 4322 , 4567 , 6543);

  18. SELECT: SELECT * FROM students WHERE lname LIKE ‘_ED’; SELECT lname , major FROM students WHERE lname LIKE ‘%ED’;

  19. SINGLE ROW FUNCTION: CHARACTER FUNCTION • LOWER(‘ITEC 454 Courses’) • UPPER (‘itec 454 Courses’) • INTCAP (‘itec 454 Courses’) • LENGTH (‘itec 454 Courses’) • INSTR (‘itec 454 Courses’, ‘C’) • LTRIM (‘itec 454 Courses’, ‘itec’) • RTRIM (‘itec 454 Courses’, ‘i') • SUBSTR (‘itec 454 Courses’, 6, 3) • LPAD (‘itec 454 Courses’, ‘--->’) • RPAD (‘itec 454 Courses’, ‘<---’)

  20. SINGLE ROW FUNCTION: NUMBER FUNCTION • ROUND(123.163, 2) • ROUND(123.163, -2) • TRUNC(123.163, 2) • MOD(3093) • POWER(2, 3)

  21. DATE FUNCTION • MONTH_BETWEEN(SYSDATE, b_date) • ADD_MONTHS(b_date, 3) • ROUND(b_date, ‘MONTH’) • ROUND(b_date, ‘YEAR’)

  22. CONVERT DATATYPE FUNCTION • TO_CHAR(b_date, ‘DD-MM-YY’) • FORMAT: • YY • YYY • YYYY • YEAR • MM • MONTH • DD • DY

  23. CONVERT DATATYPE FUNCTION • FORMAT • DAY • MI • SS • HH

  24. AGGREGATE FUNCTION • MAX • MIN • SUM • COUNT • DISTINCT

  25. ORDERING • SELECT name, gpaFROM studentsWHERE major=‘COSC’ORDER BY name • ASC • DESC

  26. GROUPING • SELECT dept, AVG(salary)FROM facultyGROUP BY dept; • SELECT major, COUNT(*)FROM studentWHERE major IN(‘COSC’, ‘MATH’, ‘VART’, ‘ACCT’, ‘ITEC’)GROUP BY majorHAVING COUNT(*)>20ORDER BY major;

  27. JOIN • SELECT nameFROM students, student_courseWHERE students.id=student_course.id; • SELECT *FROM students a, students bWHERE a.gpa > b.gpa AND a.lname = ‘SMITH’;

  28. JOIN • SELECT name, salaryFROM faculty, statusWHERE salary BETWEEN low_salary AND high_salary;

  29. OUTER JOIN • SELECT nameFROM students, student_courseWHERE student.id = student_course.id(+);

More Related