MS SQL Server
Dynamic SQl, T-SQL

• Mostly:

• Dynamic SQL

• Stored Procedure

• Trigger

• Cursor

• Function

• Sayed Ahmed

What are the Most Important
Most Used

• Design ERD

• Convert ERD to database

• Normalization

• Indexing

• SQL

• Stored Procedure

• Dynamic SQL

• These will come, not too frequent

• Function, User Defined Data Types, Temporary Table

• Trigger, Cursor

• SSRS, SSIS, SSAS

• ML in SQL Server (in advanced level)

• May not be important initially

Today

• More on

• Dynamic SQL

• Stored Procedure

• Cursor

Basics of Stored Procedure

• By this time, we are supposed to know

• https://www.sqlservertutorial.net/sql-server-stored-procedures/basic-sql-server-stored-procedures/

• https://www.sqlservertutorial.net/sql-server-stored-procedures/sql-server-stored-procedure-parameters/

• https://www.sqlservertutorial.net/sql-server-stored-procedures/variables/

• Although these topics will come back

• In our discussion today

From Northwind Database

• Write a dynamic SQL

• That will Query the Products table and

• Show all data

• Ref:

• https://www.sqlservertutorial.net/sql-server-stored-procedures/sql-server-dynamic-sql/

Dynamic SQL

• DECLARE    @table NVARCHAR(128), @sql NVARCHAR(MAX);

• SET @table = N’Products’

• SET @sql = N’SELECT * FROM ‘ + @table;

• EXEC sp_executesql @sql;

Convert the previous to a stored procedure

Using dynamic SQL to query from any table    of Northwind Database

Write a stored Procedure for it
table name as the parameter

Create or ALTER      Procedure    [dbo].[usp_query] ( @table NVARCHAR(128))
AS
BEGIN

Some Topics to Know and Understand
to prepare
for a DBMS related Job Interview

ACID

• ACID: https://www.geeksforgeeks.org/acid-properties-in-dbms

Database Normalization

• 1NF: No repeating groups possible for a cell, PK identified, dependencies mapped

• 2NF: No Partial Dependence. Non key attributes must have to depend on the full key

• 3NF: No transitive dependency. non key attributes must have to depend on the primary key, not on any other attributes. if there is another candidate key, still that is in 3NF

• BCNF: 3NF satisfied but multiple candidate key exist then BCNF required

• 4NF: Multivalued Dependency exist. Need to remove that.

• 5NF: Do not have Join dependency

4NF: Normalization

• Fourth Normal Form

• Multivalued Dependency exist

• Attributes independent of each other but depends on the same attribute

• No row should show multivalued dependency

• Solution: usually break into multiple tables

• https://bohr.wlu.ca/cp363/notes/theory/20_4nf.php

• Definition: Multivalued Dependency:

• https://www.javatpoint.com/dbms-multivalued-dependency

• 5NF: is already in 4NF. and if contains join dependency.

• Remove join dependency, break into new tables, but lossless break

• https://www.javatpoint.com/dbms-fifth-normal-form

• Table: lecturer, semester, course.

• We cannot create semester without knowing other attributes.

• Not in 5NF

• Solution: usually break into multiple tables

Normalization

• Normalization: Determinant

• https://mix.sumdu.edu.ua/textbooks/11961/472103/index.htm

Read Misc.: PL/SQL, T-SQL

• PL/SQL Tutorial:

• https://www.tutorialspoint.com/plsql/index.htm

• Oracle vs SQL Server

• https://www.cybrary.it/blog/database-differences-microsoft-sql-server-vs-oracle-database

• Oracle Vs MS SQL Server

• https://www.guru99.com/oracle-vs-sql-server.html

• https://www.guru99.com/oracle-vs-sql-server.html

• T-SQL vs PL/SQL:

• https://hevodata.com/learn/t-sql-vs-p-l-sql

Cursors, Triggers, Functions, Stored Procedures

• An INSTEAD OF trigger

• is an SQL trigger that is processed “instead of” an SQL UPDATE, DELETE or INSERT statement. Unlike SQL BEFORE and AFTER triggers, an INSTEAD OF trigger can be defined only on a view, not a table.

• https://www.ibm.com/docs/en/i/7.2?topic=triggers-instead-sql

• Implicit Cursor:

• https://docs.oracle.com/en/database/oracle/oracle-database/19/lnpls/implicit-cursor-attribute.html [may have wrong info]

• https://www.mygreatlearning.com/plsql/tutorials/pl-sql-cursors

• Function vs Stored Procedure:

• https://www.tutorialspoint.com/what-are-the-differences-between-stored-procedures-and-functions

Oracle Misc.

• Oracle Three Tier:

• Application Architecture:

• https://docs.oracle.com/cd/E18727-01/doc.121/e12841/T120505T120508.htm

• Oracle Datastore

• https://www.oracle.com/webfolder/technetwork/data-quality/edqhelp/content/concepts/data_stores.htm

Oracle Admin and Policies

• Oracle Tutorials:

• https://www.oracletutorial.com/oracle-administration/

• Database Admin Policy:

• https://study.com/academy/lesson/database-security-policies-examples-and-creation.html

• Establish Security Policies:

• https://docs.oracle.com/cd/A58617_01/server.804/a58397/ch19.htm

• The critical elements for creating a database environment are (1) data administration, (2) data-planning and modeling methodology, (3) database technology and management, and (4) users.

• https://docs.microfocus.com/OpsCx/10.11/Content/policies/database/db_policies.htm

• Policies in Oracle:

• https://docs.oracle.com/en-us/iaas/Content/Identity/Concepts/policies.htm

• Common Policies:

• https://docs.oracle.com/en-us/iaas/Content/Identity/Concepts/commonpolicies.htm#top

Misc. Oracle

• Oracle: SQL Plus

• https://docs.oracle.com/cd/B14117_01/server.101/b12170/qstart.htm

• Start Working with Oracle:

• https://docs.oracle.com/en/database/oracle/oracle-database/21/

MongoDB Misc.

• Tutorial:

• https://www.tutorialspoint.com/mongodb/mongodb_interview_questions.htm

• Read the Q & A, the online Quiz

• BSON:

• https://www.mongodb.com/basics/bson

• Explain:

• https://www.mongodb.com/docs/manual/reference/command/explain/

Sayed Ahmed
Professor, Software Engineer, Data Scientist, and ML EngineerPart-time Professor and Course Facilitator at Colleges in Ontario, Canada
Extensive experience in Software Development and Engineering
Significant experience in Teaching.
Taught in Universities, Colleges, and Training Institutes
Master of Engineering (MEng.) in Electrical and Computer Engineering (McMaster University) (Changed Ph.D. Studies to MEng)
MSc in Data Science and Analytics (TMU/Ryerson)
MSc in Computer Science (U of Manitoba)
BSc. Engineering in Computer Science and Engineering (BUET).
(Session: 1994-95 to 1996-97 Class: 1996 to 2001)

Linkedin: https://ca.linkedin.com/in/sayedjustetc