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

Your Laravel week in review ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏

‍ ‍ ‍ Livewire v3, Laravel Tailwind Merge, Cross-Origin Resource Sharing, and more! – №467 Week 467 ‍

‍ Livewire v3 will be launched July 20th Caleb Porzio just announced that Laravel Livewire v3 will officially be released on July 20th, at Laracon US! Read more Sponsor ‍ ‍ ‍ Diving into Cross-Origin Resource Sharing Learn how to harness the power of Laravel CORS in this tutorial. Discover what it is and unlock its potential for seamless cross-origin resource sharing. Read more ‍ Laravel Tailwind Merge Laravel Tailwind Merge is a package that automatically resolves Tailwind CSS class conflicts in Laravel Blade files. Read more ‍ Lemon Squeezy for Laravel 1.0 is Here The Lemon Squeezy for Laravel package released v1.0. Learn about this exciting package to make subscription billing, payments, and license keys a breeze! Read more ‍ ? Download the Response of an HTTP Request in Laravel Marcel Pociot shared a tip on using the Laravel HTTP client method sink() to write a response to a file Read more ‍ JetBrains announced a bundle for Laravel developers: PhpStorm + Laravel Idea plugin JetBrains, the company behind PhpStorm, has exciting news for Laravel developers. They have introduced a special bundle offer that includes PhpStorm and the Laravel Idea plugin at a 50% discount. Read more ‍ Laravel Jobs ‍ The official Laravel job board connecting the best jobs with top talent. ‍ View all ‍ ‍ Now hiring ‍ Senior Product Engineer – Laravel Remote / European TZs Laravel Frontend Developer (React Experience) Remote (EU preferred) Full Stack Laravel Engineer w/ VueJS Experience – USA ONLY Remote, USA ONLY Full-stack developer (Laravel, Vue) Austria / Remote (Europe only) Laravel Developer (with optional frontend) Remote Backend Developer Remote, Europe only Software Developer Atlanta, GA or Remote- US Based Senior Laravel Developer Remote – Working Hours in USA Eastern Time Zone ‍ Post a job on LaraJobs and it’ll be featured here next week. ‍ Community Links Laraflash github‍.com How to Add Google reCAPTCHA v3 to Your Laravel Form laracoding‍.com You might be sending random people your users’ data prinsfrank‍.nl Event-driven architectures creates resilient and fail-tolerant software josepostiga‍.com Laravel Users Table: Change Primary Key ID to UUID – in 3 Steps laraveldaily‍.com https://github.com/rmunate/LaravelHelpers github‍.com How To Bulk Insert Using Laravel Eloquent (With Timestamps) laracoding‍.com How to Get Raw SQL Query From Laravel Query Builder or Model laracoding‍.com ? Parse large JSONs from any source in a memory-efficient way github‍.com Bytepoint – Optimize uploaded images on the fly in Laravel github‍.com VPhoneInput: international phone number input for Vuetify 3 npmjs‍.com Introducing laravel-backup-restore stefanzweifel‍.dev Migrate passwords from a legacy PHP application to Laravel leopoletto‍.com Ignition – the default Laravel error page – now offers AI solutions out of the box flareapp‍.io Laravel File Uploads: Save Filename in DB with Folder and URL? laraveldaily‍.com Avoid global variables in JavaScript advanced-inertia‍.com Laravel, React, and Inertia-SSR on Fly.io fly‍.io Hydrator released v3 ✅ github‍.com Looking at the PHP Code to Make Your Data Chat-able with Laravel, OpenAI API alnutile‍.medium‍.com "Expression #1 of SELECT list is not in GROUP BY": Two Fixes in Laravel laraveldaily‍.com Iterate files and directories in PHP – Fast tips inspector‍.dev Use ray()->trace() to figure out where a call came from myray‍.app Save Time and Effort with This Custom Command for Laravel Project Installation jose‍.jimenez‍.dev Visualising Laravel and Horizon metrics using Prometheus and Grafana freek‍.dev Laravel MockAPI github‍.com Laravel Eloquent spatial package github‍.com Containerizing Laravel Application using Docker hibit‍.dev Short-lived database transactions josepostiga‍.com Add your link ‍ The Archives Last Month Laravel Precognition Updates are Here Laravel Precognition was overhauled and ships with a fresh perspective on predicting the outcome of a future HTTP request. … Use 1Password to Authenticate with Forge and Vapor Securely To authenticate with Laravel Forge and Laravel Vapor CLIs, you can use 1Password and take advantage of the community shell … Observability vs monitoring Is observability the same as monitoring? What is the difference between observation and monitoring? What is the right tool for … Feature Tests powered by database seeders David Hemphill at Laracon AU talking about how to use feature tests with database seeders. … Laravel Octane Adds Support for Roadrunner v3 The Laravel team release Octane v2 with support for Roadrunner v3! … Last Year Skip Webpack when Testing Learn how to skip Webpack during your tests or your CI pipeline … Validate Your App on the Frontend With Laravel Dry Run Requests The Laravel Dry Requests connects server-side validation with the frontend form UX. … Laravel Pint The long awaited hype train is finally over, Laravels latest open source CLI app has been released and we got … Laravel 9.18 Released The Laravel team released 9.18 jam-packed with amazing features and improvements. Let’s look at the standout new features now available … Getting started with Laravel Scout and Meilisearch We have all needed to add some sort of search to our applications before, this tutorial will walk you through … Running PHPStan on max with Laravel Over the last few years static analysis in PHP, and more specifically Laravel, has become more and more popular. Follow … Two Years Ago Laravel 8.48 Released The Laravel team released 8.47 with an on-demand filesystem disk, the ability to prune failed queue jobs, and the latest … Laravel Google Fonts Package Laravel Google Fonts is a package by Spatie to manage self-hosted Google Fonts in Laravel apps. … PHPInsights v2 is Here PHPInsights just released v2 with the ability to automatically fix proposed insights, PHP 8 support, faster analysis, and more! … Shipping Docker – Learn how to use Docker in development, testing, and production. Shipping Docker is a comprehensive course in the many uses of Docker. From playing to developing, testing to deploying, it … 5 Tips for the Laravel Service Container 5 Things to Consider When Dealing with the Service Container in Laravel … Tailwind CSS 2.2 is Now Here With a New CLI and JIT Features Tailwind v2.2 was just released with a brand-new Tailwind CLI tool, a ton of new features using the JIT engine, …

Web Design Instructor and other roles are available
͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏ ͏

Web Design Instructor CDI College · Pointe-Claire, QC (On-site) 2 connections Business Instructor NorQuest College · Edmonton, AB (On-site) 2 school alumni +30% headcount growth over 2 years 3D Modeling Instructor for Concept Design NPC College of Arts and Design · Richmond, BC (On-site) Actively recruiting Easy Apply Sessional Instructor – BTMA 331 LEC 02 & LEC 03, Fall 2023, Haskayne School of Business University of Calgary · Calgary, AB (On-site) Actively recruiting Concept Design/digital painting Instructor NPC College of Arts and Design · Richmond, BC (On-site) Actively recruiting Easy Apply See all jobs