De Anza logo
Credit- Degree applicable
Effective Quarter: Fall 2020

I. Catalog Information


CIS 64B
Introduction to SQL
4 1/2 Unit(s)
 

Advisory: EWRT 211 and READ 211, or ESL 272 and 273; CIS 64A.

Lec Hrs: 48.00
Lab Hrs: 18.00
Out of Class Hrs: 96.00
Total Student Learning Hrs: 162.00

Introduction to Oracle SQL (Structured Query Language), DML (Data Manipulation Language) processing techniques, DDL (Data Definition Language) techniques, selecting and sorting data, joins, SQL functions, Oracle objects, Oracle data processing concepts to maintain large database systems.


Student Learning Outcome Statements (SLO)

 

Design solutions for introductory level problems using appropriate design methodology incorporating interpreted database constructs.


 

Create algorithms, code, document, debug, and test introductory level SQL programs.


II. Course Objectives

A.Review the basic features of databases.
B.Demonstrate usage of basic SQL statements to restrict and sort data.
C.Demonstrate usage of single-row functions for retrieving from database.
D.Illustrate usage of joins to get data from multiple tables.
E.Explain and apply data aggregation and sub-queries to fetch data from database.
F.Demonstrate how formatting output works to produce readable reports.
G.Create Database Objects using a database schema.
H.Define database security policy and create different levels of user access and variables in database schema.
I.Use Control Structures to implement decision making constructs in RDBMS.
J.Describe how cursors are implemented in databases.
K.Demonstrate usage of database utilities used for importing and exporting data from databases.

III. Essential Student Materials

 None

IV. Essential College Facilities

 Access to a computer system with Oracle SQL Plus

V. Expanded Description: Content and Form

A.Review the basic features of databases.
1.Introduction to DBMS
2.Types of databases
3.Introduction to RDBMS
4.Relational database theory
5.Normalization theory
6.Designing relational databases
7.Introduction to object relational databases
B.Demonstrate usage of basic SQL statements to restrict and sort data.
1.SQL Plus
2.Select statements
3.Data types in SQL
4.Operators
a.Arithmetic operators
b.Comparison operators
c.Character operators
d.Concatenation operators
e.Logical operators
f.IN and BETWEEN operators
5.Clauses in SQL
a.WHERE
b.STARTING WITH
c.ORDER BY
d.GROUP BY
C.Demonstrate usage of single-row functions for retrieving from database.
1.Character
2.Number
3.Date
4.Conversion
5.General
D.Illustrate usage of joins to get data from multiple tables.
1.Equijoins
2.Non-Equijoins
3.Outer joins
4.Self joins
E.Explain and apply data aggregation and sub-queries to fetch data from database.
1.Group functions
a.COUNT
b.AVG
c.SUM
d.MAX/MIN
e.Nesting of Group functions
2.Sub queries
a.Single-row sub query
b.Multiple-column sub query
F.Demonstrate how formatting output works to produce readable reports.
1.Using Aliases
2.INSERT statements
3.UDATE statement
4.DELETE statement
G.Create Database Objects using a database schema.
1.CREATE TABLE
2.ALTER TABLE
3.DROP statement
4.RENAME statement
5.TRUNCATE statement
H.Define database security policy and create different levels of user access and variables in database schema.
1.Create User and Privileges
2.Grant Option and Revoke
I.Use Control Structures to implement decision making constructs in RDBMS.
1.Usage of DECODE
2.Introduction to temporary tables and records
J.Describe how cursors are implemented in databases.
1.Implicit Cursor concepts
2.Integration of cursors with DML and DDL operations
K.Demonstrate usage of database utilities used for importing and exporting data from databases.
1.SQL Loader
2.DB import/export utility.

VI. Assignments

A.Reading from text
B.Documenting, coding, testing and debugging six to ten programs guided with clearly documented design, covering the Lab Topics specified in X. below, half completed in the computer lab, half completed as homework

VII. Methods of Instruction

  Lecture and visual aids
Discussion of assigned reading
Discussion and problem solving performed in class
Quiz and examination review performed in class
Homework and extended projects
Collaborative learning and small group exercises
Collaborative projects
Other: Laboratory discussion sessions
Other: Laboratory experiences which involve students in designing, coding, and testing SQL programs.

VIII. Methods of Evaluating Objectives

A.Successful completion of programming assignments with output verifying program correctness; use of SQL, documentation, programming style, efficiency, and testing methods.
B.One or more examinations requiring programming demonstrating ability to develop a design and/or write code using specific SQL constructs.
C.A final examination requiring some programming demonstrating ability to develop an algorithm and write code. The code will involve select, insert, update, delete, create and alter statements.

IX. Texts and Supporting References

A.Examples of Primary Texts and References
1.*John L. Viescas. "SQL Queries for Mere Mortals: A Hands-On Guide to Data Manipulation in SQL (3rd Edition)", Addison-Wesley Professional, June 21, 2014
B.Examples of Supporting Texts and References
1.Pratt, Philip J. "A Guide to SQL, 9th Edition." Course Technology, 2014.

X. Lab Topics

A.Write code using SQL implementing simple queries to work with one table.
B.Write code using SQL implementing simple queries to work with more than one table.
C.Write code using SQL implementing data grouping and analysis
D.Write code using SQL implementing transaction concepts with usage in Insert, Update and Delete statements.
E.Write code using SQL implementing a database schema using Create and Alter statements
F.Design database security model for multi-user access