SQL 2 - Detailed Syllabus

SQL 2 is one of the subjects for Semester 5 of B.Sc.-IT and following is the detailed syllabus for the subject :

1) Introduction to SQL: The SQL language, role of SQL, SQL features & benefits
Microsoft commitment (ODBC & ADO) , Internet database access , Java integration
(JDBC)

2) SQL & DBMS: Brief history of SQL , SQL Standards (ANSI / ISO Standards,
other SQL standards, ODBC & the SQL access group) , SQL & networking (centralized architecture, file server architecture , client/server architecture, multi-tier architecture)

3) RDBMS: Data models (File management systems, hierarchical databases,
network databases), relational data model (Keys, tables, relationships), Codd's 12 rules

4) SQL Basics: statements, names (table & column names), data types , constants
(numeric, string, date & time, symbolic constants), expressions , built-in functions, missing data (NULL values)

5) Simple queries: The SELECT statement , query results, simple queries, duplicate rows, row selection, search conditions, sorting query results, rules for single table query processing

6) Multi-table queries : Simple joins , Non equi-joins, SQL considerations for multi table queries (table aliases, qualified column names, all column selections , self joins) , multi table query performance , the structure of the join (table multiplication, rules for multi-table query processing) , outer joins.

7) Summary Queries : column functions, grouped queries, group search conditions.

8) Sub queries & query expressions : using sub queries, sub query search
conditions, sub queries & joins, nested sub queries , correlated sub queries, sub queries in the HAVING clause, advanced queries in SQL2 .

9) Database updates : adding data to the database , deleting data from the
database, modifying data in the database

10) Data integrity : meaning of data integrity, required data, simple validity checking (column check constraints, domains), entity integrity (other uniqueness constraints, uniqueness & NULL values) , referential integrity (referential integrity problems, delete & update rules, cascaded deletes & updates, referential cycles, foreign keys & NULL values) , trigger advantages & disadvantages, triggers & SQL standards.

11) Transaction Processing : Meaning (COMMIT, ROLLBACK), transaction log,
transaction & multi user processing, locking (locking levels, shared & exclusive locks, deadlocks, advanced locking techniques)

12) Creating a database : DDL, creating database , table definitions, constraint
definition, aliases & synonyms, indexes, managing other database objects, database structures (single database architecture, multi- database architecture, multi-location database architecture)

13) Views : meaning, creating a view (horizontal, vertical, row/column subset,
grouped , joined views), updating a view, dropping a view

14 ) SQL security : SQL security concepts (user-ids, security objects, privileges) , views & SQL security, granting privileges , revoking privileges