GENERAL OVERVIEW


The lab course aims to make students able to apply their knowledge in developing and using relational databases. The labs cover efficient use of SQL for complicated tasks and teach a database language. The main topics of the laboratory applications are: SQL Queries (both simple & advanced), use of triggers, stored procedures and functions for efficient and more secure implementations of database applications. Upon completion of the course, the students learn how to design and develop database applications using one of the major DBMSs.

These labs are not just for students who require extra help; they are an integral part of the course and attendance is mandatory. 

To be successful, you will need to devote significant time outside of Labs to studying, practicing skills and solving assignment problems.




LAB TOPIC OUTLINE
The tentative Lab topics are listed below. Not all topics will be covered in the same degree of detail and the sequence may differ somewhat from the list.
LAB WEEK
TOPIC(S)
Week: ONE
Introduction




Project: Group Formation
Part A
Create database tables
Describe the data types that can be used when
specifying column definition
Table naming rules & Fields Datatypes
Part B
Insert rows into the created table
Create Department Table
Execute a basic SELECT statement



Week: TWO
Getting familiar with queries




Project: Brief on Project
Part A
Basic SELECT Statement
Selecting All Columns, Specific Columns
Arithmetic Expressions, Using Arithmetic Operators, Parenthesis
Defining a Column Alias
Part B
Eliminating Duplicate Rows
Displaying Table Structure
Concatenation Operator




Week: THREE
Formatting data
Part A
 Limiting the Rows Selected
Restricting with Character Strings and Dates
Comparison Conditions
Other Comparison Conditions,
Part B
Using the LIKE Condition
Using the NULL Conditions
Logical Conditions




Project proposal submission deadline 2/
Week: FOUR
Displaying Data from Multiple Tables
Part A
Obtaining Data from Multiple Tables
Generating a Cartesian Product
Retrieving Records with Equijoins
Joining a Table to Itself
Creating Joins with the ON Clause
Part B
Creating Three-Way Joins with the ON Clause
LEFT OUTER JOIN
RIGHT OUTER JOIN
FULL OUTER JOIN
Additional Conditions




Project:  Requirements Analysis, Features & Relationships 2
Week: FIVE
Aggregating Data Using Group Functions
Part A
Types of Group Functions
Using the AVG and SUM Functions
Using the MIN and MAX Functions
Using the COUNT Function
Using the GROUP BY Clause
Part B
Using the GROUP BY Clause on Multiple Columns
Illegal Queries Using Group Functions
Excluding Group Results: The HAVING Clause
Nesting Group Functions




Project: Submission of ER Diagram & Relations Discussion Su2
Week: SIX
Subqueries
Part A
Using a Subquery to Solve a Problem
Subquery Syntax
Single-Row Subqueries
Executing Single-Row Subqueries
Using Group Functions in a Subquery
Part B
Single-row operator with multiple-row subquery
Multiple-Row Subqueries
Using the ANY Operator
Using the ALL Operator






Project: Submission of ER Diagram & Relations Submission Deadline
Week: Seven
Manipulating Data
Part A
Copying Rows from Another Table
Updating Rows in a Table
Updating Rows Based on Another Table
Example of Merging Rows
Part B
The ALTER TABLE Statement
Adding a Column
Modifying a Column
Dropping a Column
Changing the Name of an Object
Truncating a Table
Add PRIMARY KEY/ FOREIGN KEY constraints
CREATE VIEW






Week: Eight
Controlling User Access
Part A
Creating Users
Granting System Privileges
What is a role?
Creating and Granting Privileges to a Role
Changing Password
Granting Object Privileges
Using WITH GRANT OPTION and PUBLIC key
Part B




Project:  Query Processing/ Frontend demonstration2




Week: Nine


Part A & Session-2
Functions, Triggers


Project: Work on Project 2




Week: Ten
Revision Session, Project Update




Week: Eleven
Lab Final Exam




Week Twelve
Project: Presentation & Submission Pre2

