SIT103-Lect2to6-SQL-Oracle-2004

SIT103-Lect2to6-SQL-Oracle-2004 - SQL and Oracle Structured...

Info iconThis preview shows pages 1–3. Sign up to view the full content.

View Full Document Right Arrow Icon
SQL and Oracle 1 1 SQL and Oracle SCC108 Lecture 2 2 S tructured Q uery L anguage ± A complete interface language for a database system. Data Definition Data Manipulation Data Extraction ie. Getting data from the database (queries). 3 Structure of SQL Database Interface DATA FILES SQL SQL USER 4GL Screens etc. Report Writer Application Programs 4 123 Smith Fred M Y 145 Smith Mary F Y 236 Nguyen Trung M N 378 Papadopo Harry M N Num Surname Given Sex Mem Table / Relation Columns / fields rows / tuples / records Values Primary Key 5 Relational / SQL Database Rules ± No ordering or table joining information is stored. ± All access to the database is through SQL. Both for users and programs! ± Multiple table operations involve joining tables on fly. 6 SQL Tables - STUDENT Name Null? Type --------------------------- -------- ---- STUDENT_NO NOT NULL CHAR(8) SURNAME VARCHAR2(30) GIVEN VARCHAR2(30) DOB DATE SEX CHAR(1) ADDR1 VARCHAR2(40) ADDR2 VARCHAR2(40) ADDR3 VARCHAR2(40) POSTCODE NUMERIC(4) PHONE VARCHAR2(20) PROGRAMME_CODE VARCHAR2(6) PROGRAMME_LOAD VARCHAR2(1) PROGRAMME Name Null? Type --------------------------- -------- ---- PROGRAMME_CODE NOT NULL VARCHAR2(6) PROGRAMME_NAME VARCHAR2(50) DEPT_NO NUMERIC
Background image of page 1

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
SQL and Oracle 2 7 SQL Table COURSE Name Null? Type ---------------------------- -------- ---- COURSE_CODE NOT NULL CHAR(5) COURSE_NAME VARCHAR2(50) DEPT_NO NUMERIC FEE NUMERIC(10,2) STUDENT_COURSE Name Null? Type ---------------------------- -------- ---- STUDENT_NO NOT NULL CHAR(8) COURSE_CODE NOT NULL CHAR(5) COURSE_PROGRAMME VARCHAR2(6) SEM_START NUMERIC(1) SEM_DURATION NUMERIC(1) RESULT VARCHAR2(4) 8 Some Simple Queries SELECT SURNAME, GIVEN FROM STUDENT; SELECT SURNAME, GIVEN, PROGRAMME_CODE FROM STUDENT; SURNAME GIVEN ----------------- -------------------- UNCHERNRAT ROSEMARY ANN SCRIVENER ROBERT CRAWFORD R BULSOOK JORGE SURNAME GIVEN PROGRAMME_CODE --------------- ------------------ -------------- UNCHERNRAT ROSEMARY ANN 167001 SCRIVENER ROBERT CRAWFORD 162010 BULSOOK JORGE 165701 FEKONJA TRUC TRUNG 165000 9 Some Simple Queries SELECT SEX FROM STUDENT; SELECT DISTINCT SEX FROM STUDENT; SEX - M M F M F F SEX - F M 10 Concantenation Queries SELECT SURNAME || ‘, ’ || GIVEN FROM STUDENT; SELECT ‘Student: ’ || SURNAME, ‘Programme: ’ || PROGRAMME_CODE FROM STUDENT; ± Note: Concatenation can be with fields and/or string literals. SURNAME||', '||GIVEN ----------------------------------- RAJOO, THINH MINH PHONGWATCHARARUK, JERZY MIROSLAW CHOW, SOW LIM ‘STUDENT:'||SURNAME 'PROGRAMME:'||PRO ----------------------------- --------------- Student: RAJOO Programme: 167402 Student: PHONGWATCHARARUK Programme: 161005 Student: CHOW Programme: 165000 11 Simple Sorting Queries ± Ascending order SELECT SURNAME, GIVEN FROM STUDENT
Background image of page 2
Image of page 3
This is the end of the preview. Sign up to access the rest of the document.

Page1 / 19

SIT103-Lect2to6-SQL-Oracle-2004 - SQL and Oracle Structured...

This preview shows document pages 1 - 3. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online